Solved

sp_executesql in another db

Posted on 2014-04-10
8
266 Views
Last Modified: 2014-04-15
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
0
Comment
Question by:jvoconnell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39992742
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

0
 
LVL 1

Author Comment

by:jvoconnell
ID: 39992758
SELECT EDWIDSqlTXT FROM PHS.ClientAdmin.SecurityBaseView WHERE DatabaseNM = 'Payer' AND SchemaNM = 'Reference' AND viewNM = 'Actuarial' AND HasIDFLG =  'Y'


...above is the output
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39992848
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?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 1

Author Comment

by:jvoconnell
ID: 39992865
the answer is  yes to both questions
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39992885
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

0
 
LVL 1

Author Comment

by:jvoconnell
ID: 39993073
I received the following message:

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

Accepted Solution

by:
jvoconnell earned 0 total points
ID: 39993075
This worked. I tried using an Output parameter. I don't think this was the most efficient means of doing this by any means, but it does give the desired output.
                  Thank you very much for the assistance. It is very much appreciated.

                  declare @sql nvarchar(max)
                  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) = 'SELECT @sql=EDWIDSqlTXT ' +  
                        'FROM EDWAdmin.ClientAdmin.SecurityBaseView '  +
                        'WHERE DatabaseNM = ' + QUOTENAME(@DB, '''') + ' AND SchemaNM = ' + QUOTENAME(@Schema, '''') + ' AND viewNM = ' + QUOTENAME(@VW, '''') + ' AND HasEDWIDFLG =  ''Y'''
           

         DECLARE @ParamDefinition NVARCHAR(max)
       SET @ParamDefinition = N'@sql nvarchar(max) OUTPUT' --parameter definition (list input and output parameters and the order)
       EXEC sp_executesql @AlterViewSQL, @ParamDefinition, @sql OUTPUT
                 
             EXEC sp_executesql @AlterViewSQL, @ParamDefinition, @sql OUTPUT
           
                     eXEC @ExecTargetDB @sql
0
 
LVL 1

Author Closing Comment

by:jvoconnell
ID: 40001151
An Output Parameter gave the desired output
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Creating and Managing Databases with phpMyAdmin in cPanel.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question