Skip to main content

28 posts tagged with "database"

View All Tags

SSIS Deployment Headache

· 3 min read

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.

Protecting Against SQL Injection in Dynamic SQL Statements

· 3 min read

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

DbMail - A use for global temp tables in SQL

· 2 min read

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

SSIS Crashes When Editing OLE DB Source

· One min read

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

Closing A Cursor in SQL Catch

· One min read

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:

Starting Out with Service Broker

· 2 min read

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?

(SQL) Blocking Giving You the Blues?

· 3 min read

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.

SSIS - 32 Bit ForEach File Enumerator Was Not Registered

· One min read

I've been having problems migrating some SSIS packages from localhost (32bit) to the server (Windows Server 2003 64bit, SQL Server 2005 Enterprise SP2). These packages have Excel data connectors, and hence must be run in 32 bit mode. When I try to run them with the 32bit dtexec, I get the "generic" error "the package failed to load" (0xC0010014). I stripped down the package until I found the source of the error: a ForEach container, using the File Enumerator with the directory set to a variable. When I remove the variable setting it works, when I add the variable expression it fails. Note: this does not fail in the 64bit dtexec.

safnet logo