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


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


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


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

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

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

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


2

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


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

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

Looks like blocking in action. You should try to monitor blocking with tools such as sp_WhoIsActive or an Extended Events session. If you want to use Extended Events, there's a tool called ExtendedTSQLCollector that can help you automate monitoring and collection of the events you need. I wrote a blog post on how to use it to monitor blocking and ...


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

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


1

Why not just execute xp_logevent via SqlContext.Pipe.ExecuteAndSend() in your catch block?


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 appears the OP is conflating how the data should be stored in the database with how the data will be displayed and/or entered when taking classroom attendance. These are not necessarily the same thing. Display should not drive storage. The UI could display the data the way the OP shows above in a classroom attendance form and have a middle layer that ...


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


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

I've seen similar problems addressed by decoupling the app from the central server: Remote sites install a local SQL Server Express along with the app Apps talk to the local Express instance. Low latency, good availability. SQL Server Express uses Service Broker to deliver the updates to the central server Service Broker handles the network availability, ...


1

Create a set of staging tables in the target database. Write rows to these as they are generated, which seems to be one or two at a time. This can be inside a transaction. Once the whole batch (200 rows?) is in these staging tables use a stored procedure in the target database to move them en masse from the staging tables to the real ones.


1

You have used parametrized query, which is the bare minimum expectation to prevent on SQL-Injection. I would suggest some points you can take care of as a validation in C# code I don't see any server side validation on the text you are getting from TextBoxSearch.Text, you can check for some set of character that can be an attempt for SQL Injection. you ...


1

The answer is contained in http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx 1.To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a folder where the project is in: a.Map it to a member of the SSISDB database role -- public. (This ...


1

You can use an array constructor: select array( select zone from mytable) as areas;


1

What you are asking is to be able to create a report name dynamically, which is not something that can be programmed, on download, say, from Report Manager. You can (although I've never tested this) possibly do this through a subscription using @ParameterName @ReportName. citation from msdn It's about the best you can do for now, and there's a link on the ...


1

There are at least two drawback when using a query to check if URL exist: Sending an extra query to database, will include additional network delay to your overall response time and also more network traffic. no matter what value you insert, database will still check for duplicate key. therefore, the search on primary key is performed twice when two query ...


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



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