Recently in SQL Category

Reusing Code in SSIS

December 13, 2007

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.

Your Default Database Is Offline - How Do You Login?

December 6, 2007

Problem: without thinking about the ramifications, you've taken your default database in SQL Server offline. When you try to login through SQL Server Management Studio (or Query Analyzer) you get an error message because the app couldn't switch to the default database. Obviously, one feels pretty dumb after doing this. Thankfully it happened to me in development, not production!

Solution: It turns out the solution is just as easy as the mistake: when you log in, click the options button and choose a different database. More detail at public class:ben harrell.

Sanitize Your Database Inputs!

October 10, 2007


From www.xkcd.org

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:

Ignoring SSIS Data Conversion Failures

August 17, 2007

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.

Replacing Ad Hoc Query Text When Fields Change

June 27, 2007

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?

Granting Execute Permission to All Stored Procedures

June 22, 2007

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:

Server 'myserver' is not configured for RPC

June 22, 2007

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.

About this Archive

This page is an archive of recent entries in the SQL category.

Linux is the previous category.

Windows is the next category.

Find recent content on the main index or look in the archives to find all content.

Categories

Pages

OpenID accepted here Learn more about OpenID
Powered by Movable Type 5.12