[Profiler] Found: "Error: 208, Severity: 16, State: 0"

So, while running some code, trying to figure out why it's failing, I see this:

Error: 208, Severity: 16, State: 0

What is it, you ask? Some sort of weird bug, maybe, involving temp tables.

Run this:

exec ('exec master..xp_cmdshell ''dir c:\''')

No problems.

Run this:

create table #Listing (results varchar (255))
insert #Listing (results) 
exec ('exec master..xp_cmdshell ''dir c:\''')

Bingo.

Why is this the case? I wish I knew.

[Tuning] Getting rid of Dynamic SQL

We've all done it. There's times when you just need to write something quick and dirty that deals with a simple problem. And there doesn't seem to be an easy way to make it proper SQL, so you write some dynamic SQL instead.

CREATE PROCEDURE cases_select_dynamic
@sortBy varchar(100),
  @status Int
AS
BEGIN
IF len(@sortBy)>0
BEGIN
 DECLARE @sql varchar(max)
 SET @sql='SELECT * FROM cases WHERE 
    status = ''' + CONVERT(VARCHAR(100),@status) + '''
 order by ' + @sortBy
 EXEC (@sql)
END
ELSE
BEGIN
 SELECT * FROM cases WHERE 
    status = @status
END
END

Fortunately, there are people out there who don't particularly like dynamic SQL. Fortunately for us, because they've found ways around it. Instead of the above, do something like

CREATE PROCEDURE cases_select_nondynamic
  @sortBy varchar(100),
  @status Int
as 
BEGIN
 SELECT * 
 FROM cases
 WHERE  status = @status
 order by CASE @sortBy WHEN 'finished' THEN finished ELSE NULL END, 
   CASE @sortBy WHEN 'name' THEN name ELSE NULL END, 
   CASE @sortBy WHEN 'lead' THEN lead ELSE NULL END
END

Which does the same thing. A little more work, but you gain several benefits.

• Dynamic SQL runs under the users context, so they need perms to the tables. Nondynamic means you just grant normal SP rights.
  
• Cached plans. 2000/2005 are good about reusing the cached plan - if it's IDENTICAL. Unless people are always running the exact same query, parameters and all, it won't cache. Nondynamic gets cached once and used for all parameters.
  
• Faster. In my case, running the exact same parameters for both versions resulted in the dynamic version having a 89% Query cost (relative to the batch), while the nondynamic version was a mere 11%.
  

So go out there and convert 1 SP today to be nondynamic. Read Erland's page, he's got a lot of examples and a lot of thought in there.

http://www.sommarskog.se/dynamic_sql.html . Erland is a SQL god.

[SQL] A very easy way to convert tinyint to hex

SELECT CONVERT(VARBINARY(1), 254)
0xFE

If you need more leading zeroes, raise the size of the Varbinary.

SELECT CONVERT(VARBINARY(8), 254)
0x000000FE

[SQL] Running code on most databases on a server

Everybody should already be using sp_MSforeachDB to do things in all databases.

This is a really cheap way of only doing it to some databases; just add the list in two single-quotes (since you're running it within sp_MSforeachDB)

sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'')
begin 
 use ?
 exec sp_updatestats 
end' 

[Notification] and a version to watch a SPID

WHILE (SELECT    COUNT(*)
FROM     sysprocesses WHERE spid = 230 AND cmd IN ('ALTER table','CREATE index')
)>=1
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA',
    @recipients = 'dba@thebakingdba.com',
    @subject = 'index creation done'

[Jobs] Code to email when job finishes

If you have to run a job outside its normal time, and didn't remember to change the notification before you ran it, fret not. By hitting the system tables, you can have it notify you when the job finishes.

WHILE (SELECT    COUNT(*) 
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= '20090107' and --today's date
         j.NAME = 'your_job_name' AND 
--max step for the job, so you don't get emailed when step 1 of 5 finishes
         h.step_id = 3 
         )=0
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAs',
    @recipients = 'thebakingdba@yourcompanyname.com',
    @query = 'SELECT    j.[name],
         s.step_name,
         h.step_id,
         h.step_name,
         h.run_date,
         h.run_time,
         h.sql_severity,
         h.SERVER,
         h.run_status
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= ''20090107'' and 
         j.NAME = ''your_job_name'' AND 
         h.step_id = 3
' ,
    @subject = 'Your Job Finished',
    @query_result_separator =  ' ',
    @query_result_width = 750,
    @attach_query_result_as_file = 0 ;