SMS Tips and Tricks SQL

I don't often link to other posts - Yeah I know, a fatal bloggers mistake.  However, here is a a really nice set of tips: 

http://www.bidn.com/blogs/MMilligan/bidn-blog/3326/sql-server-management-studio-ssms-tips-and-tricks

Single Quotes in String Values

The following code includes an example of building a string value which has a single quote character in the text and also searching a field for a single quote character.

declare @test varchar(1000)

declare @i int

IF OBJECT_ID('tempdb..#testtable') IS NOT NULL  

    DROP TABLE #testtable

create table #testtable

(val varchar(200)

)

-- Intentionally misspelled

set @i = 1

set @test =  'Had this been a real emergency, you''re keester would already by fried'

 

while @i < 20

begin

  insert #testtable

  select cast(@i as varchar(1000))

  set @i = @i + 1

end

insert #testtable

select @test

-- find the one record 

select val

from #testtable

where val like '%''%'

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