29 posts tagged with "sql-server"

For years I've had problems with SSIS deployments to production. In fact, I completely abandoned SSIS packages because it was so difficult to deploy to production (in SQL Server 2005): I always ended up with errors that would require me to hand-edit the file and hard-code paths. That, despite the fact that my config files have the database connection strings and file paths in them. And despite the fact that my packages usually work fine on the test server but fail in production. After years of this problem, it suddenly occurred to me that one piece in particular is probably at fault. But given that I do not have access to production such that I can investigate, it will always be a hypothesis: using a template that sets various properties with the help of variable expressions.

Microsoft's Books Online article on SQL Injection does a great job of reviewing the possible attacks against dynamic SQL statements (using EXEC or sp_executesql). I won't re-hash their discussion and suggestions. What I offer below is a sample remediation effort for this set of statements (the @Fields and @Values variables are actually stored procedure parameters):

DECLARE @Fields VARCHAR(1000), @VALUES VARCHAR(1000), @SQL NVARCHAR(2500);
SELECT @SQL = 'INSERT INTO MyTable (' + @Fields + ') VALUES (' + @Values + ')';
EXEC(@SQL);

"global temporary tables are visible to all sessions," says the MSDN documentation. I have occasionally wondered when this would be helpful. This week I finally found a use, albeit limited: I want a quick-and-dirty process that will e-mail a CSV file to me every day for a week. I wouldn't do this for a full on production system (without dwelling on it, it just strikes me as too error prone and inelegant), but for some testing I want to do over the course of a week, it makes sense.

I run a query that populates a temp table, and I want to e-mail the contents of the query using sp_send_dbmail. Again, the documentation tells something important: "Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query." This applies not just to a table variable, but also a local temp table (e.g. #temp). But a global temp table (e.g. ##temp) is a different story: works just fine. So there you go, quick and dirty way to e-mail yourself some query results when you don't want to just pass the raw query directly to the send command.

One should work hard to avoid Cursors in T-SQL — for instance, by using recursive common table expressions &ndsh; but when you need to call a separate stored procedure in a loop, you're stuck with them. I recently came across an excellent guide to the subject, Performance Tuning SQL Server Cursors, which includes the following enlightening piece of advice:

Problem​

SSIS 2005 (actually, Microsoft SQL Server Business Intelligence Studio) crashes every time you click on an OLE DB Source to edit it. (there are probably similar errors for OLE DB destinations).

Solution​

oddly enough, synchronize a few DLLs:

Version of the assemblies msmdlocal.dll and Msmgdsrv.dll must be the same of the ones installed into "%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\" and the ones installed into "%ProgramFiles%\Common Files\System\Ole DB" location.

If it is not same for any or both of these DLLs, then replace the ones in PrivateAssemblies with the ones from ole db.

Hat tip: Calculation tab not working — SQL Server 2005 Analysis Services

Problem​

You have an table with encrypted columns. In the ADO.Net Entity Framework, you can't directly write an update to this table, since it does not handle the encryption commands. Furthermore, the framework will not return meaningful information, since it cannot decrypt the data. Finding the solution to this has been an adventure.

Problem​

In a T-SQL script, an exception occurs while a cursor is open, resulting in the cursor never being closed. But, the exception handling wraps the entire script, not just the cursor, so there is no guarantee that the cursor will be open if/when the CATCH statement is reached.

Solution​

query the sys.syscursors view to see if the cursor(s) in question is still open:

We had a horribly pernicious timeout problem recently. It was occurring in a .Net 2.0 application written to retrieve reports from SQL Server 2005 Reporting Services via its web services interface. The query behind the report was a bit large, but typically would run under 30 seconds. We had judged that new indexes were out of the question because of their impact on the already slow program that loads the key data. All of the sudden, we started getting continuous timeouts. After a couple of days, the underlying query was up to 15 minutes execution time!

In the end multiple interacting problems had to be solved in order to get the reports running properly. For the past few weeks they've been running without a hitch.

Problem​

you have an application that needs to trigger some process via SQL Server but don't want your main process hung up waiting. So you decide to setup Service Broker in order to make an asynchronous call, with the receiving service doing your work for you. You've read all about it, and tried it out after hours, but it didn't work. What gives?

Problem​

You have a long-running query in SQL Server that is causing failures all over the place. Activity Monitor shows you the cause is blocking.

Causes: Blocking essentially means "you've locked a table, and now someone else is stuck waiting for you to unlock it." We all know (or should know) that transactions cause table locks, and thus blocking. That's one reason that transactions need to be short and sweet. But there is another source of blocking, one that can be a bit more subtle: long-running queries that are trying to read committed data only.