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....
blossomparkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blossomparkAuthor Commented:
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'.
Den_HBRCommented:
Where did you ran it?

If I run it in 'Microsoft SQL Server Management Studio Express', it runs fine.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

blossomparkAuthor Commented:
ran it in management studio
 msgrab
Den_HBRCommented:
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
blossomparkAuthor Commented:
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
Den_HBRCommented:
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

blossomparkAuthor Commented:
this works
use master
select name,cmptlevel
from sysdatabases

and all databases are 80
Den_HBRCommented:
COMPATIBILITY_LEVEL { 80 | 90 | 100 }
Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

80 = SQL Server 2000 

90 = SQL Server 2005 

100 = SQL Server 2008

Open in new window


That is why CROSS APPLY doesn't work. It needs compatibility level 90

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blossomparkAuthor Commented:
Hi Den HBR, thanks for your assistance on this one...much appreciated :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.