Tag Info

Hot answers tagged

11

Have you tried just writing and see what happens? Do you have a known bottleneck? If you need to prevent your app being blocked then you one way would be to queue the writes to defer the database call. However, I'd expect the queue to clear in a second or 2: so do you need an queue if this is OK? Or you can spool to a staging table and then flush later? We ...


10

I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it ...


8

An nvarchar(max) value will be stored "in-row" if it is short enough. The default behaviour can be modified using sp_tableoption, "large value types out of row" option. I wouldn't bother. The DB engine will manage this efficiently by itself. As for design, there are several ways of doing this based on your model: Will you always have both English and ...


8

You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog. There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database: ALTER DATABASE [MyDatabase] SET TRUSTWORTHY ON; sp_configure 'clr enabled', ...


7

Unless I'm missing something, this would violate the Durability requirement from ACID (http://en.wikipedia.org/wiki/ACID). That is, if your application "writes" the data to RAM and then the server crashes, your data is lost. So, what you seek is either a non-database system that serves as a queue for eventual storage into a database or a database system ...


7

At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time. A couple of months back our data center got flooded - filling several servers ...


6

The only change required is in the connection string: remove the User ID=...; and Password=...;, and add Integrated Security=SSPI; instead. More information can be found here.


6

The assembly binary is stored as a blob in the database, so it's carried wherever the database goes. CLR is only enabled on the instance -- there are no database-specific settings for that. In any event, why are you trying to do this? (I'm not trying to be argumentative; I just want to hear the motives involved, because perhaps the problem could be solved ...


4

I ended up making a CLR function to convert it. I found this library, I then tweaked it a tiny bit to remove things I did not need like logging and Drawing methods, which allowed me to mark it as safe. I then just made this small class. using System.Data.SqlTypes; using Itenso.Rtf.Converter.Text; using Itenso.Rtf.Support; public partial class ...


4

If you want to restore a *.bak file onto a machine e.g. TESTSERVER, then that *.bak file needs to be on a local drive of TESTSERVER, or: on a share in the network that TESTSERVER can reach (a "public" share) So if you have the *.bak file on your local PC MyPC, you can try to do a restore from \\MyPC\C$\ShareFolder\Backupfile.bak and this might or might ...


4

Assuming a company can be "attended to" by more than one employee, and likewise for client, you would model like this: So both relations are 'many:many'. As gbn also said, adding "comma separated ids" would be something of a disaster in database design: you would regret it many times over.


4

I am assuming you are asking about alternatives to installing SQL CLR assemblies from Visual Studio. Having the code in Visual Studio is not required. Deploying CLR Database Objects on MSDN details the options, including SQL statements and deployment scripts.


3

You need to modify your connection strings to follow the below format for a Trusted Connection: Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; And then run your application in the security context of the domain user.


3

MS SQL Server has a fixed page size of 8KB. A row is never splitted over several pages, but several rows can share a single page. nvarchar(max) and other BLOB data may however be stored outside of the row/page. This means that for everything to fit into one row, the sum of all sizes must be less than 8K. If it doesn't, SQL Server will store the BLOBs ...


3

A varbinary value is of the form 0x01020304 for SQL Server to read. So your CSV would be col1,col2,varbinary foo,1,0x01020304 bar,2,0x988776655443 Edit May 2013, After testing, I can't get this to work with BULK INSERT with or without a format file. I will post something more when I get it working. I can't delete this answer


3

If there's a where clause in the DELETE statement, then the server still needs to perform a SELECT internally to the DELETE statement in order to delete only the specified records. You may be only issuing one DELETE statement, but that's a convenience of SQL. The Database software has to process that statement and perform the necessary logic to do the job ...


3

No, there's no way to do this. Any option that did what you want would be extremely confusing because there would need to be a way to turn it off within the string literal syntax. There are other considerations (like storage size) to be aware of when choosing data types, so a setting like this could be very dangerous to put in play. With regards to .NET ...


3

Here are some of the things you can try to get it to work: Compare Enlist=false in your connection strings Compare MultipleActiveResultSets=true in your connection strings User instance databases appear to work differently for some reason - we couldn't get it to work consistently with them - use full databases all of the time You can reuse the same ...


3

Your procedure is missing two OUT parameters: PROCEDURE myprocedure (myinput IN Varchar2, myretcursor OUT sys_refcursor, p_count OUT NUMBER, p_message OUT VARCHAR2) IS BEGIN OPEN myretcursor FOR SELECT * FROM MYTABLE; p_count := 123; p_message := 'message'; ...


3

You have a many-many (aka link or junction) table between the 2 EmployeeCompany Columns: EmployeeID CompanyID Keys: Primary key is (EmployeeID, CompanyID) Add a unique index (CompanyID, EmployeeID) EmployeeID has FK to Employeetable CompanyID has FK to Company table Do not store a CSV in a column: this is bad practice, can't enforce data integrity, ...


3

Strongly recommend you look into Table-Valued Parameters and treat your insert/update as a set (using MERGE probably) instead of calling a singleton stored procedure 25,000 times. You can skip a lot of steps here (in C# you can just stuff your 25K rows into a DataTable, for example, making it easy to pass to a stored procedure in a single call), and reduce ...


3

@Mark is absolutely right. AND has higher precedence, so what your query is really saying is: WHERE record_number >= 500 AND (column_a LIKE '%something%') OR column_c LIKE '%something else%' OR column_e LIKE ... What you want it to say is: WHERE record_number >= 500 AND column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ... ...


3

Remmber you need the following assemblies: Microsoft.SqlServer.ConnectionInfo Microsoft.SqlServer.Management.Sdk.Sfc Microsoft.SqlServer.Smo Microsoft.SqlServer.SmoExtended More info http://technet.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx using System; using System.IO; namespace ConsoleApplication2 { using ...


3

You may need to use SQL Server Configuration Manager to enable TCP/IP connections. The default is disabled, as below. You must enable TCP/IP to allow remote connections to the SQL Server machine.


3

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list. use simply vendor.location.Lat or location.Lat or define a table alias and use that. CREATE TABLE dbo.Vendor([location] geography) GO /*Works fine*/ CREATE VIEW V1 AS SELECT v.location.Lat AS latitude FROM dbo.vendor v GO /*Fails*/ CREATE VIEW V2 AS SELECT ...


2

I would suggest that instead of deleting the rows rather create a new table which will have the data you would like to keep in the table. Create appropriate constraints and index. Then drop the original table and then rename the new table. This would be best if you are deleting lots of data. Deleting batches is good when you delete only very few data as ...


2

Have you considered using SQL Servers built-in "Service Broker" Feature? It is designed to help developers implement asynchronous, queued systems.


2

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example: Error: 18456, Severity: 14, State: 8. The state will usually help narrow down the exact cause. Here is a list of the states and their meanings: Err Message 2 Invalid userid 5 Invalid ...


2

I'm not 100% sure about LINQ but i would say that the seperation is based on transaction control. You apply any number of updates, inserts or deletes. That are then held as a unit of work, then once you've finished your changes you simply commit them to the database as a single action or roll all of the changes back, maintaining the integrity of your ...



Only top voted, non community-wiki answers of a minimum length are eligible