Hot answers tagged

12

There are several options. Here are the main ones that come to mind. SSIS - SQL Server Integration Services: This is SQL Servers ETL tool and you can move data from pretty much any source to pretty much any destination and it can be quite fast. BCP - Bulk Copy Program: This is a command line tool that comes with SQL Server. It's very good at moving text ...


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


11

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


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


9

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

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.


8

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


8

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I ...


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


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


6

You cannot redeclare variables, nor can you test to see if they have been declared (at least not that I have ever been able to find). BUT, you shouldn't need to do this anyway. If you know the variable names ahead of time, just declare them all at the beginning of the process. Then, use them throughout the script. For example: ...


6

Your answer will probably look something like this: // I don't know why you weren't using UTC time in your sample. That leaves you at the mercy // of your server's time zone which is rarely a good policy. If you must though you can // replace `UtcNow` with `Now`. var getQ = db.intrads.Where(s => s.Time >= DateTime.UtcNow.Date.AddHours(8)); I don't ...


6

Having a flag on the Stations table that indicates which station is local would be exactly how I'd handle this, presuming you don't have a billion stations. I'd likely call the column IsSourceStation or something, and make it a BIT value, that can accept NULL. I would mark the local station row as 1, and leave all other rows as NULL, since that won't take ...


5

Create a separate initializer that drops the database if it exists, then creates the database outside of any transaction handling code. If this fails for any reason, you could (probably) just fail the test right then and investigate why it failed. From MSDN: The CREATE DATABASE statement must run in autocommit mode (the default transaction management ...


5

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.


5

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


5

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


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

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


4

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.


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

When I try to drop a person that recently had a connection with SQL Server Why are you doing this? What drove the need to have to do this? It sounds like you're running into connection pooling. But connection pooling is there to help you, it's a great optimization. By clearing the pool you're wiping out this caching mechanism. My recommendation, ...


4

I do not believe that there is a specific problem with referencing tables, in C#, having names starting with @. I was able to create a table with a name of @TableName and was able to execute SELECT SUM(Col1) FROM [@TableName]; successfully. I used SqlConnection and SqlCommand, not EF or DataSet / DataTable. Are you sure that you are connecting to the ...


3

The problem is that you made a bad assumption about what value is returned by the SqlXml.ToString() method, and then in testing in Visual Studio, you injected a value but did not check to see what ToString() was actually returning (which is: "System.Data.SqlTypes.SqlXml"). Though you probably would have seen this had you placed the ...


3

All versions prior to 2016 (currently in CTP/beta) doesn't allow you to easily lock DBAs from the data. Column encryption that you employed stores the key in the DB and secures it using the key hierarchy within the database which roots itself in the service master key. That means you can't lock it away from DBAs. The only way out is to use extensible key ...


3

Max Vernon's answer is correct in all but: No need for NULL in a bit value. It should be non-null, default 0. Querying a field which could be NULL adds overhead to your writing/reading/code maintenance. Basically the "correct" way to query a bit field is WHERE flag <> 0, if you might have NULL in the bit field you can still ask WHERE flag <> 0 (the ...


3

srutzky's answer is the "symptomatic" solution (i.e. solve timeout problem by making SQLCommand timeout bigger). However, if this code runs synchronously (some user is waiting for these data), I would consider optimizing the procedure. 1) From what I see, you are using a lot of LEFT JOINs and inner queries, so you must ensure you have the right indexes on ...


3

Starting at the beginning: SQL Server uses statistics objects to generate cardinality estimates for the result set steps during the optimization phase. Cardinality estimates are used to generate a query plan, which is the logical plan by which SQL Server will physically access and retrieve the data in order to satisfy the request. Generally speaking, more ...


3

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd This did the trick for me: sqlcmd -S serverName\instanceName -i script.sql -o output.txt



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