Thursday, September 27, 2012

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


Wednesday, September 19, 2012

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