Solved

sp_executesql in another db

Posted on 2014-04-10
8
260 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

12 Experts available now in Live!

Get 1:1 Help Now