Distributed Query

• Azzam Alkadi

  SSChasing Mays

  Points: 640

  More actions

  November 9, 2005 at 5:49 am

  #102915

  Hi,

  Please help, I am trying to run the following script in T-SQL or SP, and getting an error see below

  SET QUOTED_IDENTIFIER ON

  DECLARE @srvname nvarchar(50),

    @query nvarchar(200)

  SET @srvname='Test123'

  SET @query=N'' + @srvname

  SET @query=@query + '.master..sp_msforeachdb '

  SET @query=@query + '''USE [?] EXEC sp_changedbowner '

  SET @query=@query + '''sa'''

  SELECT @query

  EXEC (@query)

  Error:

  Test123.master..sp_msforeachdb 'USE [?] EXEC sp_changedbowner 'sa'

  (1 row(s) affected)

  Server: Msg 170, Level 15, State 1, Line 1

  Line 1: Incorrect syntax near 'sa'.

  Server: Msg 105, Level 15, State 1, Line 1

  Unclosed quotation mark before the character string ''.

  I get the same error even when I am using sp_executesql.

  But when I execute this code it works fine.

  EXEC Test123.master..sp_msforeachdb 'USE [?] exec sp_changedbowner ''sa'''

  What is it I am doing wrong?

• Azzam Alkadi

  SSChasing Mays

  Points: 640

  More actions

  November 9, 2005 at 6:01 am

  #602992

  Thanks Guys, I've managed to crack it. It just needed more single qoutes, and here is the code

  SET QUOTED_IDENTIFIER ON

  DECLARE @srvname nvarchar(50),

    @query nvarchar(200)

  SELECT @srvname='TEST123'

  SELECT @query=N'' + @srvname

  SELECT @query=@query + '.master..sp_msforeachdb '

  SELECT @query=@query + '''USE [?] EXEC sp_changedbowner '

  SELECT @query=@query + '''''sa'''''''

  --SELECT @query

  EXEC (@query)