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

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

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

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

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

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

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


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


3

Below is a literal translation based on your sample query and as such probably isn't the optimal solution: IF ISNULL(@firstName, N'') = N'' OR ISNULL(@lastName, N'') = N'' BEGIN SELECT COUNT(*) FROM [Services] s INNER JOIN .[Clients] c ON c.Client_Id = s.Client_Id INNER JOIN .[AdHoc_Referrals] r ...


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


3

It appears that this behavior is specific to connections using "Context Connection = true;". I have tried to get around this by writing out the try-catch-finally structure instead of using the using macro, but that had no effect. A Microsoft Connect bug was filed almost 3 months ago regarding this behavior. In that Connect bug it was speculated that THROW ...


3

A quick way to resolve your issue would be simply to return a Null column in each of the select statements. SELECT url, NULL AS Category, NULL AS SubCategory,'Web' As Type FROM Website WHERE (url='www.facebook.com') UNION ALL SELECT NULL, category, NULL AS SubCategory, 'Cat' As Type FROM Category WHERE (category='www.facebook.com') UNION ALL ...


3

Your query actually needs to use two properties of the CONTAINSTABLE function- <maximum distance> and <match_order>. Using CONTAINSTABLE(OpinionDocuments, HTML, 'NEAR((Foo, bar), 0, TRUE)') AS KEY_TBL Specifies to find "Foo" near "Bar" with 0 search objects between (<maximum distance> ", 0,") and in the order specified ...


3

Yes, you can do this, and in fact with just 2 data centers you really have no other choice. Essentially, whenever you do not have a primary you can still do a reconfig but you must pass in {force:true} to make it happen on a non-primary node. This is listed in the docs for the rs.reconfig() command and in detail (with examples) as part of a tutorial which ...



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