Why does this query only run in master?

Question

Why does this query only run in master?

Susan-322874

SSCrazy

Points: 2300
More actions
March 15, 2011 at 4:58 pm

#246921

Am I doing something wrong. It runs fine in master, but if I try and run it out of our dbadmin database it errors out.
Does it have to run in master? Am I missing something -
Here's the query-
insert into DBAdmin.dbo.sysprocesses_blocking_log
(blocked_spid,
blocked_kpid,
blocked_blocked,
blocked_waittype,
blocked_waittime,
blocked_lastwaittype,
blocked_waitresource,
blocked_dbid,
blocked_uid,
blocked_cpu,
blocked_physical_io,
blocked_memusage,
blocked_login_time,
blocked_last_batch,
blocked_ecid,
blocked_open_tran,
blocked_status,
blocked_sid,
blocked_hostname,
blocked_program_name,
blocked_hostprocess,
blocked_cmd,
blocked_nt_domain,
blocked_nt_username,
blocked_net_address,
blocked_net_library,
blocked_loginame,
blocked_context_info,
blocked_sql_handle,
blocked_stmt_start,
blocked_stmt_end,
blocking_spid,
blocking_kpid,
blocking_blocked,
blocking_waittype,
blocking_waittime,
blocking_lastwaittype,
blocking_waitresource,
blocking_dbid,
blocking_uid,
blocking_cpu,
blocking_physical_io,
blocking_memusage,
blocking_login_time,
blocking_last_batch,
blocking_ecid,
blocking_open_tran,
blocking_status,
blocking_sid,
blocking_hostname,
blocking_program_name,
blocking_hostprocess,
blocking_cmd,
blocking_nt_domain,
blocking_nt_username,
blocking_net_address,
blocking_net_library,
blocking_loginame,
blocking_context_info,
blocking_sql_handle,
blocking_stmt_start,
blocking_stmt_end,
blocked_query,
blocking_query,
add_date)
select
sp1.spid,
sp1.kpid,
sp1.blocked,
sp1.waittype,
sp1.waittime,
sp1.lastwaittype,
sp1.waitresource,
sp1.dbid,
sp1.uid ,
sp1.cpu ,
sp1.physical_io,
sp1.memusage ,
sp1.login_time ,
sp1.last_batch ,
sp1.ecid ,
sp1.open_tran ,
sp1.status ,
sp1.sid ,
sp1.hostname ,
sp1.program_name,
sp1.hostprocess ,
sp1.cmd ,
sp1.nt_domain ,
sp1.nt_username ,
sp1.net_address ,
sp1.net_library ,
sp1.loginame ,
sp1.context_info,
sp1.sql_handle ,
sp1.stmt_start ,
sp1.stmt_end ,
sp2.spid ,
sp2.kpid ,
sp2.blocked ,
sp2.waittype ,
sp2.waittime ,
sp2.lastwaittype,
sp2.waitresource,
sp2.dbid ,
sp2.uid ,
sp2.cpu ,
sp2.physical_io ,
sp2.memusage ,
sp2.login_time ,
sp2.last_batch ,
sp2.ecid ,
sp2.open_tran ,
sp2.status,
sp2.sid ,
sp2.hostname ,
sp2.program_name ,
sp2.hostprocess ,
sp2.cmd ,
sp2.nt_domain ,
sp2.nt_username ,
sp2.net_address ,
sp2.net_library ,
sp2.loginame ,
sp2.context_info ,
sp2.sql_handle ,
sp2.stmt_start ,
sp2.stmt_end,
blocked.text blockedsql,
blocking.text blockingsql
, getdate()
from master.dbo.sysprocesses sp1
inner join master.dbo.sysprocesses sp2 on sp1.blocked = sp2.spid
cross apply master.sys.dm_exec_sql_text(sp1.sql_handle) as blocked
cross apply master.sys.dm_exec_sql_text(sp2.sql_handle) as blocking
where sp1.blocked <> 0
and sp1.spid <> sp2.spid
Here's the error -
Msg 102, Level 15, 1, Line 69
Incorrect syntax near '.'.
Any help would be greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 1

I've run the SELECT part of the statement in Master and a user database and it works, so the problem is no there. Without knowing the details of DBAdmin.dbo.sysprocesses_blocking_log, I can't debug any further. However, it is in the insert where the problem lies. Are all necessary permissions set?

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

The select works for me, inside and outside of master.

Not sure what's wrong for you.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

What's your database's compatibility level? If it's set to 80, you'll receive this error.

Lowell SSC Guru Points: 323482 More actions · Answer 4

i just tried to run the code in a database that is set at compatibility level 80 and it fails with that syntax check; in 90 or 100 it goes with no problem.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Steve Jones - SSC Editor (3/15/2011)
The select works for me, inside and outside of master.
Not sure what's wrong for you.

of course it works, the dbname is hardcoded in the query!

GSquared SSC Guru Points: 260824 More actions · Answer 6

Cross Apply isn't compatible with SQL 2000 (which is what Compat 80 means).

It's giving a syntax error because it thinks the Cross Apply target is a table, and the parameter is a table hint.

Try this in a Compat 80 database, and see what error you get:

SELECT *

from master.dbo.sysprocesses sp1

cross apply master.sys.dm_exec_sql_text(sql_handle) as blocked

It'll tell you:

Msg 321, Level 15, State 1, Line 3
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

You'll either need to have the query run from a Compat 90 or higher database, or use a cursor-based method of building a temp table for each input you want in the Cross Apply, and then joining to the temp table instead of using Cross Apply.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Susan-322874 SSCrazy Points: 2300 More actions · Answer 7

Very nice. It was the compatibility level.

Thanks all for your help!

Susan