• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

error on sql dynamic with uniqueidentifier

Hello,

declare @sql5 varchar(max)

set @sql5 = 'DECLARE @job_id uniqueidentifier

SELECT
            @job_id = job_id

FROM
            '+ @abonne + '.msdb.dbo.sysjobs_view
WHERE
            name like N''' + @publication + '''
print @job_id
IF @job_id IS NOT NULL BEGIN
      EXEC ('+ @abonne +'.msdb.dbo.sp_delete_job @job_id = @job_id)
END

'
print @sql5
execute @sql5



The name 'DECLARE @job_id uniqueidentifier

SELECT
            @job_id = job_id

FROM
            [serverdest].msdb.dbo.sysjobs_view
WHERE
            name like N'%jobname%'
print @job_id
IF @job_id IS NOT NULL BEGIN
      EXEC ([serverdest].msdb.dbo.sp_delete_job @job_id = @job_id)
END

' is not a valid identifier.

How can I resolve this problem?

Thanks

Bibi
0
bibi92
Asked:
bibi92
  • 3
  • 3
  • 2
1 Solution
 
wdosanjosCommented:
Please try the following:

declare @sql5 varchar(max)

set @sql5 = 'DECLARE @job_id uniqueidentifier

SELECT
            @job_id = job_id

FROM
            '+ @abonne + '.msdb.dbo.sysjobs_view
WHERE
            name like N''' + @publication + '''
print @job_id
IF @job_id IS NOT NULL BEGIN
      EXEC ('''+ @abonne +'.msdb.dbo.sp_delete_job @job_id = '' + @job_id )
END

'
print @sql5
execute (@sql5)

Open in new window

0
 
BurniePCommented:
Hi,

Change execute @sql5

for

execute (@sql5)
0
 
BurniePCommented:
Ah, too slow :)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
bibi92Author Commented:
Thanks but I have this error :
Msg 168, Level 15, State 1, Line 1
The floating point value '2886E768' is out of the range of computer representation (8 bytes).
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Regards
bibi
0
 
wdosanjosCommented:
Please try the following:

set @sql5 = 'DECLARE @job_id uniqueidentifier

SELECT
            @job_id = job_id

FROM
            '+ @abonne + '.msdb.dbo.sysjobs_view
WHERE
            name like N''' + @publication + '''
print @job_id
IF @job_id IS NOT NULL BEGIN
      EXEC ('''+ @abonne +'.msdb.dbo.sp_delete_job @job_id = '''''' + @job_id + '''''''' )
END

'
print @sql5
execute (@sql5)

Open in new window

0
 
bibi92Author Commented:
Doesn't work, the print @sql5 :
print @job_id
IF @job_id IS NOT NULL BEGIN
      EXEC ('server].msdb.dbo.sp_delete_job @job_id = ''' + @job_id + '''' )
END
Thanks

Regards
0
 
wdosanjosCommented:
I think the solution was reached based on information from the posts. Specially the "execute (@sql5)" tip. So, I believe points should be given.
0
 
bibi92Author Commented:
Sorry Thanks bibi
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now