Solved

sp_executesql in another db

Posted on 2014-04-10
8
263 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:jvoconnell
ID: 39992865
the answer is  yes to both questions
0
 
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

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.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.

830 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