29 posts tagged with "sql-server"

It was driving me nuts — my transforms just wouldn't apply. I tried working with some fake data, from my little-used O'Reilly XML in a Nutshell, and that worked fine. So why couldn't I transform my Reporting Services output? After banging my head against this one for a while, I finally decided to mess around with the root <Report ... /> node, first by removing all the extra elements (because my samples did not have any elements in the root node). Voilà, the transform now works. Why is that?

Problem​

periodically, one of my SSIS packages was throwing an error saying "The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission." The package in question then would hang, locking a file that it was trying to import.

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.

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.