Thursday, January 29, 2009

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

Friday, January 23, 2009

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

Thursday, January 15, 2009

[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

Monday, January 12, 2009

[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'

Thursday, January 8, 2009

[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'

Wednesday, January 7, 2009

[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 ;