Hot answers tagged

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


4

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

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 SqlContext.Pipe.Send(...


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

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


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

Why doesn't SQL Server honor the default that I placed on my table type? Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows: DECLARE @Test dbo.MyObject_TableType; INSERT INTO @Test (Name) VALUES ('ObjectOne'); SELECT * FROM @Test; Returns: Name Index ObjectOne ...


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


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(''), TYPE).value(...


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

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

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 https://msdn.microsoft....


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

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

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

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

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


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

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

Yes you can. However, it depends on the internals of the System.Data.SqlClient assembly and so I would not recommend it. If it is truly required I would reassess if CLR is actually the correct way to do what you are trying to do. The SqlTransaction is actually only a wrapper class around the SqlInternalTransaction class. The SqlInternalTransaction class ...


1

XACT_STATE() is not something that SqlClient has any special insight on, much like transaction level. So, just query it with SqlCommand. // If you already have an open connection... public int GetXactState (SqlConnection connection) { using (SqlCommand command = new SqlCommand("Select XACT_STATE();", connection)) { return (int)command....


1

How can I grant execute permissions to sa user? You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If ...


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



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