Link to home
Start Free TrialLog in
Avatar of blossompark
blossomparkFlag for Ireland

asked on

displaying query string sent to linked server in sql server 2005

Hi,
     I have a stored procedure (SP_IncidentResolution ) which is assembled into a connection string and then sent to a linked server
SP_IncidentResolution
@start_date_range='2010-11-01',
@end_date_range='2010-11-30'

My question is, how can I see the actual query string that is sent to the linked server?
Is there some way of displaying this in sql server 2005?

i have access to the syntax of the stored procedure in the programibility folder  but i want to see what exactly is packaged in the connection string


thanks....
Avatar of Den_HBR
Den_HBR

Avatar of blossompark

ASKER

Hi Den HBR, thanks for your response,
 i ran the query at  the url
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but got
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'APPLY'.
Where did you ran it?

If I run it in 'Microsoft SQL Server Management Studio Express', it runs fine.
ran it in management studio
 User generated image
Strange, the APPLY turn gray in my management studio.

Please run  
select name,compatibility_level 
from sys.databases

Open in new window

and post the level for the database you are using
Hi Den HBR...ran the above and got
Invalid object name 'sys.databases'.


i have solved the problem by
Modifying  the sp to PRINT the variable the contains the query
Eg PRINT(@TSQL)
This  prints out the actual sp sent to the linked server on the Messages Tab
Glad it is solved.
But the rest is making me wonder...

Could you try
USE master
select name,compatibility_level 
from sys.databases

Open in new window

this works
use master
select name,cmptlevel
from sysdatabases

and all databases are 80
ASKER CERTIFIED SOLUTION
Avatar of Den_HBR
Den_HBR

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Den HBR, thanks for your assistance on this one...much appreciated :-)