Find instances of a string in all your stored procedures:

This is a great little trick I came across while we were moving from one server to another and had to change the prefix on all references to that SQL machine - but it work in any instance where you need to search through all your stored code:

USE <databasename>; go   SELECT routine_name               ,routine_definition FROM information_schema.routines WHERE routine_definition LIKE '%<my search string>%'   AND routine_type = 'PROCEDURE' ORDER BY routine_name;

SSRS - List User Parameter Selection

In SSRS,
How do I list the parameter the user selected in the parameter drop down?

Place a label on the header of your report and create an expression for the value:

 = "Days Selected " + Parameters!parm_Days_Past_Due.Label



What if multi-select is enabled for the parameter and the've chosen more than one value?
 ="Location Type= " + Join(Parameters!Location_Type.Label,",")

In this case, the join function treats the drop down label as an array object and concantonates all the values separated by a comma.  You can also use other characters to separate the list, but a comma is the most easily understood.

Worst Code Tester Ever!!!!

This one speaks for itself.............

But it worked yesterday! 

Your SQL window just returned the following error message:

OLE DB provider 'SQLNCLI10' for linked server 'XX01' returned data that does not match expected data length for column '[xx01].[dbname].[DBO].[tablename].fieldname'. The (maximum) expected data length is 30, while the returned data length is 35.

Funny thing is, when you run it outside of a stored procedure it works fine. 
What's up with that?


What you're probably looking at is compiled code you've written (stored procedure, function, etc) which accesses a view that has been recently altered.  When a non-schema bound view is created, the meta-data from what it returns is stored on any linked servers.  Sounds messy? It is, and also a fair argument against non-schema bound views, but just the same, we need to know how to deal with them.

As you will find in http://msdn.microsoft.com/en-us/library/ms187821.aspx, the answer is to refresh the view with the following syntax:
EXECUTE sp_refreshview 'viewname'

Now here's the really odd part...... you execute the refresh from the server where the view exists, not the server linked to it.  Go figure.  If anyone has a logical answer please fill us all in!

Cheers

Script to retrieve temp table definition

So you created a query using a select into to create a temp table.....

And now you you need to productionalize it.  First order; get rid of the select into that is tying up your TempDB.

But how to go back and find out the size and datatype of all those columns you just stuffed into the temp table.  A select into requires none of that, right?  Going to each of the individual tables and getting the definitions can be time consuming.  Except....


USE TEMPDB;

SELECT

    c.COLUMN_NAME

    ,c.DATA_TYPE

    ,c.CHARACTER_MAXIMUM_LENGTH

    ,c.NUMERIC_PRECISION

    ,c.NUMERIC_SCALE

FROM INFORMATION_SCHEMA.COLUMNS c

join INFORMATION_SCHEMA.TABLES t

    on c.TABLE_NAME = t.TABLE_NAME

where

    c.TABLE_NAME like '#your_table_name%'

ORDER BY

    c.TABLE_NAME

    ,c.ORDINAL_POSITION

 

 

Just a Bit - implicit conversions and the BIT datatype

Consider the following:

DECLARE @I int;
DECLARE @B bit;
DECLARE @False bit;
DECLARE @True bit;


SET @False = 'false';
SET @True = 'true';
SET @I = -5;

--this much we pretty much expect
select @False, @True

-- but what about this ?
WHILE @I < 5
BEGIN
SET @B = @I;
SELECT @I
, @b;
SET @I = @I + 1;
END

While the BIT datatype will throw errors

Jumbled Unreadable SSRS Report Details

In developing reports which have many columns, you may find that the detail lines become jumbled up and unreadable.  Note that the column headers are just fine though.

To save time and headache with your reports, always select your detail line(s) withing the IDE and be sure that the background color is not set to "clear".  In fact, giving them each a color is a good idea.  Note also that the default templates provided by MS always have a background color set.

I can't rationally explain why this matters, it's just one of those things to be written off as "magic".

Strip unwanted characters out of sql string or variant data

-- =============================================

-- Author:           blowers

-- Create date: 2012-05-11

-- Description:      function to strip out special (escape and other) characters from an input string

-- MODIFY @KEEP TO INCLUDE THE CHARACTERS YOU WISH TO KEEP

--     - in the example a-z, 1-9 and a period will be retained and all others removed

--     - go be the uber-dba and create a list of formats you might want to use (numeric only, alpha only, money formatting only, etc)

-- =============================================

CREATE Function [dbo].[strip_special](@Temp VarChar(1000))

Returns VarChar(1000)

AS

       Begin

              DECLARE @KEEP VARCHAR(50)

              SET @KEEP = '%[^a-z0-9.]%'

              While PatIndex(@KEEP, @Temp) > 0

                     Set @Temp = Stuff(@Temp, PatIndex(@KEEP, @Temp), 1, '')

              Return @TEmp

       End

GO

Adding critical logging to your procedures

Here is a little tip that will help produce detailed logging and debugging reference points:

Output the name of the stored procedure when it runs.  Simple.

Procedure names, much like anything else, can change over time.  So how do we keep up changes to those messy output strings?  You don't, Simple again.

Include this little jewel with your print output or logging output and see how easy it is to find your way back to the stored proc:
SELECT OBJECT_SCHEMA_NAME(@@PROCID) as [schema], OBJECT_NAME(@@PROCID) as [procedure]
Maybe really show off what a fancy coder you are by including it in your error handling:
BEGIN CATCH
     SELECT
     OBJECT_SCHEMA_NAME(@@PROCID) as [schema],
     OBJECT_NAME(@@PROCID) as [procedure],    
     ERROR_NUMBER() AS ErrorNumber,     
     ERROR_SEVERITY() AS ErrorSeverity,     
     ERROR_STATE() AS ErrorState,     
     ERROR_PROCEDURE() AS ErrorProcedure,     
     ERROR_LINE() AS ErrorLine,     
     ERROR_MESSAGE() AS ErrorMessage;
END CATCH;