asked on

sp_executesql in another db

experts,
I think I'm over thinking this.

I've altered this a bit for simplicity. But the point is that we will be getting an alter view statement from a metadata table to execute in another db. It doesn't execute, it just prints the alter statement (which is correct)

What am I doing wrong?
Any help is greatly appreciated.

DECLARE @DB nvarchar(max) = 'Payer'
      DECLARE @Schema nvarchar(max) = 'Reference'
       DECLARE @VW nvarchar(max) = 'Actuarial'
      DECLARE @ExecTargetDB nvarchar(max) = @DB + '.sys.sp_executesql '
      DECLARE @AlterViewSQL nvarchar(max)

            SET @AlterViewSQL = 'SELECT EDWIDSqlTXT ' +
                        'FROM PHS.ClientAdmin.SecurityBaseView ' +
                        'WHERE DatabaseNM = ' + QUOTENAME(@DB, '''') + ' AND SchemaNM = ' + QUOTENAME(@Schema, '''') + ' AND viewNM = ' + QUOTENAME(@VW, '''') + ' AND HasIDFLG = ''Y'''

            EXEC @ExecTargetDB @AlterViewSQL

Thanks in advance

Sharath S

Can you run this and provide what is there in @AlterViewSQL?

DECLARE @DB nvarchar(max) = 'Payer'
      DECLARE @Schema nvarchar(max) =  'Reference'
       DECLARE @VW nvarchar(max) = 'Actuarial'
      DECLARE @ExecTargetDB nvarchar(max)  =  @DB + '.sys.sp_executesql '  
      DECLARE @AlterViewSQL nvarchar(max)
                  
            SET @AlterViewSQL = 'SELECT EDWIDSqlTXT ' +   
                        'FROM PHS.ClientAdmin.SecurityBaseView '  + 
                        'WHERE DatabaseNM = ' + QUOTENAME(@DB, '''') + ' AND SchemaNM = ' + QUOTENAME(@Schema, '''') + ' AND viewNM = ' + QUOTENAME(@VW, '''') + ' AND HasIDFLG =  ''Y'''
            
            PRINT @AlterViewSQL

Open in new window

jvoconnell

ASKER

SELECT EDWIDSqlTXT FROM PHS.ClientAdmin.SecurityBaseView WHERE DatabaseNM = 'Payer' AND SchemaNM = 'Reference' AND viewNM = 'Actuarial' AND HasIDFLG = 'Y'

...above is the output

Sharath S

Do you have ALTER VIEW statement in EDWIDSqlTXT column and want to execute that in another db?
Do you have the same view defined already in another db?

jvoconnell

ASKER

the answer is yes to both questions

Sharath S

check this.

DECLARE @DB nvarchar(max) = 'Payer'
      DECLARE @Schema nvarchar(max) =  'Reference'
       DECLARE @VW nvarchar(max) = 'Actuarial'
      DECLARE @ExecTargetDB nvarchar(max)  =  'TargetDB' -- replace this with actual target db name 
      DECLARE @AlterViewSQL nvarchar(max)
                  
            SELECT @AlterViewSQL = EDWIDSqlTXT    
            FROM PHS.ClientAdmin.SecurityBaseView 
            WHERE DatabaseNM = QUOTENAME(@DB, '') AND SchemaNM = QUOTENAME(@Schema, '') AND viewNM = QUOTENAME(@VW, '') 
			AND HasIDFLG =  'Y'
            
			SELECT @AlterViewSQL = 'USE ' + @ExecTargetDB + '; ' + @AlterViewSQL
			EXEC @AlterViewSQL

Open in new window

jvoconnell

ASKER

I received the following message:

Msg 2812, Level 16, State 62, Line 13
Could not find stored procedure ''.

ASKER CERTIFIED SOLUTION

jvoconnell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jvoconnell

ASKER

An Output Parameter gave the desired output