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

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

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

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.


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 ...


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

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

You can query sys.dm_exec_sessions on the last_request_end_time column to find open yet "sleeping" connections. From this, you can find out the last SQL executed SELECT session_id, TEXT FROM sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST WHERE c.session_id = <suspicious one from sys.dm_exec_sessions>


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

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

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

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

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

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

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

A couple basic SQL Server checks: Is your database set to trustworthy? Is the assembly set to allow UNSAFE execution? If that seems to be working, there are a couple of tutorials out on the web that might help. If none of the tutorials out there help, then it sounds like a permissions issue. (Unfortunately, that gets tricky, as this thread discussing ...


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 ...


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 ...



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