Solved

sp_executesql in another db

Posted on 2014-04-10
8
261 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
  • 5
  • 3
8 Comments
 
LVL 40

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 40

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
 
LVL 1

Author Comment

by:jvoconnell
ID: 39992865
the answer is  yes to both questions
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 40

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now