dynamic tsql in sql 2005

Question

Hi i am using the following dynamic sql:

declare @cmd nvarchar(4000)

set @cmd= 

'select ''exec msdb.dbo.sp_update_job @job_id=''''''
+ convert(nvarchar(255), job_id)+ '''''',

@owner_login_name=''''sa'''';''
from msdb..sysjobs'


exec sp_executesql @cmd

but all it is doing is printing as exec .......

I want to execute the results not just print it.

What should i do?

Regards

Manjot

Answer 1

Remove the 'Select' ! (and also remove some escaped single quotes) In other words...

set @cmd=
   'exec msdb.dbo.sp_update_job @job_id=''' 
   + convert(nvarchar(255), job_id)  
   + ''', @owner_login_name=''sa'';'

Note: not quite sure where this last thing fits (probably at the end of the sp_update_job statement.

   -- from msdb..sysjobs'

Essentially what was going on, with the SELECT statement was that the dynamic SQL being executed was a select statement which happen to select a string dynamically crafted. SQL would then print that string, just as if the SELECT statement had been, say:

select 'Once upon a time, a very poor lumberjack..."

(Whatever is in the string after select doesn't affect SQL in any way)

Now, as is more obvious with Remus' nice formatting of the question, the dyanamic SQL statement itself seems to be wrong, but at least SQL will try to execute it, now...

Answer 2

How about:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = COALESCE(@sql, '') + '
    EXEC msdb.dbo.sp_update_job @job_id = ''' 
    + CONVERT(NVARCHAR(255), job_id) 
    + ''', @owner_login_name = ''sa'';'
FROM msdb.dbo.sysjobs;

EXEC sp_sqlexec @sql;

NOTE that sp_sqlexec is undocumented and unsupported. To do this in a supported way, why not just create a cursor, it is much easier to follow and debug (no sea of red and no "invisible" SQL):

DECLARE @j UNIQUEIDENTIFIER;

DECLARE c CURSOR FOR
    SELECT job_id FROM msdb.dbo.sysjobs;

OPEN c;

FETCH NEXT FROM c INTO @j;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_job
     @job_id = @j,
     @owner_login_name = 'sa';

    FETCH NEXT FROM c INTO @j;
END

DEALLOCATE c;

Or ever easier, since msdb.dbo.sysjobs is not technically a system table, and you can edit it directly:

UPDATE msdb.dbo.sysjobs 
    SET owner_sid = SUSER_SID('sa');