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

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:

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.