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


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


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

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

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

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

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

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


2

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks. DECLARE @qry NVARCHAR(MAX) = 'SELECT ' + STUFF(( SELECT ', ' + QUOTENAME(c.name) FROM Report.InfoColumnOrder r JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info') WHERE u.UserId = @user ORDER BY r.SN FOR XML PATH(''), ...


2

i know it's been a while since the question but i found a possible solution. This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx The portion of code that we are interested is this: ...


2

This should be a simple matter of setting the SqlCommand.CommandTimeout property: command.CommandTimeout = 300; // 5 minutes This is the number of seconds to wait before throwing an error. The default is 30 seconds.


2

You can probably use a query with a single SELECT like this one: SELECT CAST(10001 AS varchar)) AS KdNr , CAST(2014 AS varchar)) AS LastYear , CAST(2015 AS varchar)) AS CurrentYear , LY1 = ROUND(SUM( CASE WHEN AuftragsKennung <> 4 AND MONTH(Datum_erfassung)=1 AND YEAR(Datum_erfassung)=2014 ...


1

You could use SELECT * FROM sys.databases d WHERE d.name = '<target_db_name>'; to obtain the existing settings for the database prior to running the transfer. Store the results in a DataSet so you can potentially use them later. If the database does not exist, you'll get no rows returned. If the database does exist, you could drop the existing ...


1

As the other two answers correctly state, Assemblies are loaded into a particular database and are not system-wide (though I'm fairly certain that the assembly_id value is unique system-wide). This means that they are backed up, and restored, with each database that they are loaded into. Also, the enabled / disabled setting of CLR Integration (via ...


1

You just need to add a WHERE clause in the statement, like: where Date >= '2016-03-05': select v.Date, count(v.id) as co from volunter as v where v.Date >= '2016-03-05' group by v.Date order by v.Date ; Details may differ depending on the DBMS you use. For SQL Server for example, the safe format for the date and datetime types is 'YYYYMMDD', so ...


1

I've seen some options in .NET be different than the default SSMS options. Specifically, ARITHABORT was ON in SSMS, but in .NET it was OFF (the default value in the DB was off, where I assume .NET was pulling this from). This caused SQL Server to choose a vastly different (and terrible) plan in the .NET code. Can you verify the options (ANSI_NULLS, ...


1

Try making your user account a member of the msdb database's SQLAgentReaderRole. The rights granted by that role membership are described here: https://msdn.microsoft.com/en-us/library/ms188283.aspx Look at the description of rights under SQLAgentReaderRole Permissions. You will see that it has rights to read the information on the jobs. This does not ...


1

What version are you running? 5.6 doc gives the details: innodb_flush_log_at_trx_commit can be set (1) on the commandline, (2) in my.cnf, or (3) via SET GLOBAL. sql_safe_updates can only be set via SET SESSION or SET GLOBAL. Note that SET GLOBAL does not impact anyone currently connected (including yourself). It will effect anyone connecting later, but ...


1

It seems to me that you're falling into an XY Problem. You want to see the data from lots of different databases, and have the mindset that you should only connect to a single database. That line of thinking logically lead you to the conclusion that you need to bring all the information into one central database. IMO your real problem is needing to see the ...


1

Compilation of comments @Sole DBA Guy pointed out: I'd say method 1 is what most DBAs will say is better of the two options because if you decide to add Banana, it's just another row for the first option but for the second option, it would be a new column. However, why does it need a loop to add the values? Is the loop to get the values for each fruit? ...


1

Unicode is a complex beast and database engines tend to be at best superficially aware of it. They certainly don't tend to know about sequence equivalences. I would suggest making sure that you store data in a consistent normalised form, and normalise search parameters in the same way before searching. I'm not really a c# person, but ...


1

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


1

It's not entirely clear what the wanted output is but based on the comments that you want a "lightweight db export" and that the solution needs to be dynamic and "to be able to apply it to different dbs and different schemata", I'll try to make an outline of a solution: Find all FKs in the db in question. Copy all tables to a new db - but without the FKs ...


1

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...


1

You can simply inherit the primary key from the Animals table in the Fish and Cattle table. This example uses syntax that is SQL Server specific, however you should be able to convert this into SQLLite syntax: CREATE TABLE dbo.Animals ( AnimalID INT NOT NULL CONSTRAINT PK_Animals PRIMARY KEY CLUSTERED , DateCreated DATETIME ); ...



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