28 posts tagged with "database"

I was looking at a query today that has an inline expression in it, something similar to

CASE WHEN myTable.myColumn < 5 THEN NULL ELSE myTable.myColumn END

This particular piece of business logic is used in many different places (well, not literally, since I made up a fake example), so I created a UDF to simplify the queries that use this statement. Suppose that UDF is called dbo.fnNulifyMyColumn.

Problem​

In SSIS, you want to update a variable from a Script Component embedded in a Data Transform task. You get an error message reading "the collection of variables locked for read and write access is not available outside of postexecute". What's up?

Problem​

you have an SSIS package that you want to reuse, as a template, in a number of new packages.

Solution​

follow Microsoft's simple instructions.

That's reason number 1 to use stored procedures in your application code — they automatically sanitize your SQL (assuming you aren't dynamically executing statements inside the procedure).

Stephen's top 4 reasons for using stored procedures rather than inline SQL:

Problem​

In SQL Server Integration Services (SSIS), you're trying to import from a data dump from another database system that has different data types from SQL Server. While the conversions look pretty straigh forward, you get failures of the type "The value could not be converted because of a potential loss of data.". But despite the mismatch data types, you can't see anything wrong.

Situation: We have a stored procedure running a query whose WHERE clause is given as a parameter. No, that's not the problem in and of itself, at least not today. (Treat this as a non-negotiable requirement for now). Within that WHERE clause there might be a query against a field, call it myField. This field is a varchar and wildcards are not used. Platform: SQL Server 2005.

Problem​

myField has been changed to a varbinary field and holds an encrypted value — thus can no longer query directly against it. How do we make this work?

Problem​

You've transferred or run a bunch of stored procedure scripts, but you can't execute them. Reason - execute permission denied. You forgot to put a grant statement in your script.

Solution​

The trivial solution is, of course, GRANT EXECUTE ON {your proc name} TO PUBLIC. Slightly less trivial is to grant to a specific role, but most people needing this tip will only be using PUBLIC.

Wouldn't it be great to automate this for all stored procedures in the database? Well, here you go:

Problem​

On a Microsoft SQL Server 2005 installation with a linked server configured to "myserver" (which happens to be 2000), execution of a remote stored procedure (EXEC myserver.mydatabase.dbo.mysproc) fails with error:

Msg 7411, Level 16, State 1, Line 1
Server 'myserver' is not configured for RPC.