Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sp_executesql in another db

Posted on 2014-04-10
8
Medium Priority
?
280 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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