I am not an expert on this..., but please try this code - again be warned that it seems to only be good for data since the last engine startup.

It...

You might want to check whether you have the "Perform volume maintenance tasks" user right in Windows, found in the "Local Security Policy" MMC snap-in, Local Policies, User Rights Assignment.

I think the only time I used xp_cmdshell was to break into a colleagues laptop, add myself to the local administrators group in windows, and from there set up a...

CREATE PROCEDURE GetSchemasMM

@UseDb [sysname],

@Output [varchar](MAX) OUTPUT

AS

SET NOCOUNT ON;

DECLARE @sql [nvarchar](MAX);

SELECT @sql = N'SELECT @result=STUFF((SELECT '',''+[SCHEMA_NAME] FROM ['+@UseDb+N'].[INFORMATION_SCHEMA].[SCHEMATA] WHERE [SCHEMA_OWNER] = ''dbo'' FOR xml PATH('''')),1,1,'''')'

EXEC sp_executesql @sql,N'@result [varchar](MAX) OUTPUT',@result...

Slightly off-topic, but related to your reason for the original post....

You might like to try this : http://www.atlantis-interactive.co.uk/products/sqleverywhere/default.aspx

I am not affiliated - it is just a free tool for writing...

also out of mild interest, can you query ok from the command line on the 2008 server to the 2005 server using that user?

It's not specifically related to SQLCMD. Perhaps you were using a different login?

The user doesn't have the PROCESSADMIN role.

Have you noticed/tested to see if tempdb is growing?

What is your tempdb configuration? #files , initial size, storage etc...

Have you noticed/tested to see if parallelism affects performance - i.e....

Just out of interest, what happens if you try to expand the Linked Server Catalogs in SSMS Object Explorer?

well that looks ok, can you do the same for the database permissions?

It might be worth checking permissions by running this in the Test2005Database:

EXECUTE AS user = 'TEST_USER';

SELECT * FROM fn_my_permissions('dbo.Test2005Table', 'OBJECT')

REVERT;

Just to see exactly what the user can do.

I take it that the code that calls the code you posted has the try ... catch block around the call?

I don't have an answer to why try...catch fails...previous answers...

No, you are right Jeff, I was leaving it to the OP to deal with that....

I would absolutely do it the way you so kindly offered.

BEGIN TRY

IF EXISTS(

select 1 FROM msdb.dbo.sysjobs_view sjv

join msdb.dbo.sysjobactivity sja

on sja.job_id = sjv.job_id

JOIN msdb.dbo.sysjobhistory sjh ON sja.job_history_id = sjh.instance_id

where name='test')

...