Database USE issues with dynamic SQL

Question

question

Simonn asked Feb 16, '10

Database USE issues with dynamic SQL

Hi All

I want to create a stored procedure that creates a new database with tables, triggers and its own set of stored procedures each time the stored procedure is executed. Creating the tables is easy but now trying to create a trigger I get a error saying that I'm not using the correct database and if I try to include the USE statement in the Create trigger it returns a error saying the CREATE TRIGGER statement needs to be first in the Query. Please help me as this is critical. Please see code below. Thank you in advance

DECLARE @Retailer VARCHAR(30)

SET @Retailer = 'RetailerName'
SET @Retailer = '[OTA_' + @Retailer + ']'

DECLARE @CreateVoucherMoveTrigger NVARCHAR(4000)

SET @CreateVoucherMoveTrigger = 
'USE ' + @Retailer + '
GO

CREATE TRIGGER [dbo].[UpdateVoucherStock]
   ON '+ @Retailer +'.[dbo].[TempVoucherUpload]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
INSERT INTO ' + @Retailer + '.[dbo].[VoucherStock]
           ([Network]
           ,[RatePerProvider]
           ,[SumOfVoucher]
           ,[Status]
           ,[Chain]
           ,[DateTimeStamp])
SELECT (LEFT(TVP.[ProductCode],(CHARINDEX(''_'',TVP.[ProductCode])-1))) AS [Network]
      ,RIGHT(TVP.[ProductCode],(LEN(TVP.[ProductCode])-CHARINDEX(''_'',TVP.[ProductCode]))) AS [RatePerProvider]
      ,TVP.[Vouchers]
      ,TVP.[Status]
      ,TVP.[Chain]
      ,TVP.[ReportDate]
  FROM ' + @Retailer + '.[dbo].[TempVoucherUpload] AS TVP
    LEFT JOIN ' + @Retailer + '.[dbo].[VoucherStock] AS VS
    	ON (LEFT(TVP.[ProductCode],(CHARINDEX(''_'',TVP.[ProductCode])-1))) = VS.[Network]
    	AND RIGHT(TVP.[ProductCode],(LEN(TVP.[ProductCode])-CHARINDEX(''_'',TVP.[ProductCode]))) = VS.[RatePerProvider]
    	AND TVP.[Chain] = VS.[Chain]
    	AND TVP.[ReportDate] = VS.[DateTimeStamp]
WHERE VS.[Network] IS NULL

DELETE FROM ' + @Retailer + '.[dbo].[TempVoucherUpload]

END
GO'
PRINT @CreateVoucherMoveTrigger
execute sp_executesql @CreateVoucherMoveTrigger

Weird thing is the Message returned when placed in a new query window it works perfectly?

sql database database-design dynamic

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-16T11:08:31Z

Tom Staab answered Feb 16, '10

You cannot use "GO" inside your dynamic SQL (because, as Håkan said earlier, sp_executesql doesn't support multiple batches). However, if you change the database before running the stored procedure, it will execute with that as the current database. The problem in your case is that even the database name is dynamic. So, I came up with a solution that uses nested dynamic SQL.

DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

SET @dbName = 'master';
SET @sql = 'SELECT DB_NAME()';

SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@BigSQL)

In your case, your CREATE TRIGGER code would go in the @sql variable, and obviously the database name would go in @dbName.

Good luck, and thanks for the challenge. :)

In response to Håkan's comment, here's a longer example that creates a stored procedure, executes it, drops it and then attempts to execute it again (which intentionally fails).

DECLARE @EndLine nchar(2); SET @EndLine = NCHAR(13) + NCHAR(10);
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

SET @dbName = 'master';
--SET @sql = 'SELECT DB_NAME()';

SET @sql =
    N'IF OBJECT_ID(''''dbo.GetDatabaseName'''') is not null' + @EndLine +
    N'	DROP PROCEDURE dbo.GetDatabaseName'
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
PRINT @BigSQL
EXEC (@BigSQL)

SET @sql =
    N'CREATE PROCEDURE dbo.GetDatabaseName' + @EndLine +
    N'AS' + @EndLine +
    N'BEGIN' + @EndLine +
    N'	SELECT DB_NAME();' + @EndLine +
    N'END'
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
PRINT @BigSQL
EXEC (@BigSQL)

SET @BigSQL = 'USE ' + @dbName + '; EXEC dbo.GetDatabaseName';
PRINT @BigSQL
EXEC (@BigSQL)

SET @sql =
    N'IF OBJECT_ID(''''dbo.GetDatabaseName'''') is not null' + @EndLine +
    N'	DROP PROCEDURE dbo.GetDatabaseName'
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
PRINT @BigSQL
EXEC (@BigSQL)

SET @BigSQL = 'USE ' + @dbName + '; EXEC dbo.GetDatabaseName';
PRINT @BigSQL
EXEC (@BigSQL)

Here's the "Messages" output:

USE master; EXEC sp_executesql N'IF OBJECT_ID(''dbo.GetDatabaseName'') is not null
    DROP PROCEDURE dbo.GetDatabaseName'
USE master; EXEC sp_executesql N'CREATE PROCEDURE dbo.GetDatabaseName
AS
BEGIN
    SELECT DB_NAME();
END'
USE master; EXEC dbo.GetDatabaseName

(1 row(s) affected)
USE master; EXEC sp_executesql N'IF OBJECT_ID(''dbo.GetDatabaseName'') is not null
    DROP PROCEDURE dbo.GetDatabaseName'
USE master; EXEC dbo.GetDatabaseName
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.GetDatabaseName'.

The "Results" output is just "master".

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther commented · Feb 16, 2010 at 12:46 PM

+1 Excellent solution, I was wondering if nesting the statements could be a solution, but I didn't dare to suggest it. :) Did it work with CREATE TRIGGER or CREATE PROCEDURE?

2 ·

Tom Staab ♦ commented · Feb 16, 2010 at 02:26 PM

In response to Håkan's question, I updated my answer to show a longer example that creates a stored procedure. To directly answer your question, yes it worked. :) I gave your comment a +1 both because I thought it was a good comment and because that will let you know that I responded (if you check your activity).

2 ·

Answer 2 · 2010-02-16T05:21:50Z

Fatherjack answered Feb 16, '10

Simply add a GO statement after the USE command eg:

Use Adventureworks
GO
Create trigger <yourtriggername> on <tablename>

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Simonn commented · Feb 16, 2010 at 05:26 AM

Returns the following.... (Might be because I'm using dynamic code?) Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'GO'. Msg 111, Level 15, State 1, Line 4 'CREATE TRIGGER' must be the first statement in a query batch. Msg 102, Level 15, State 1, Line 38 Incorrect syntax near 'GO'.

0 ·

Fatherjack ♦♦ commented · Feb 16, 2010 at 05:35 AM

Ah, just noticed your syntax, you'll need to try using sp_executesql as: DECLARE @sql NVARCHAR(20) DECLARE @db NVARCHAR(10) SET @db = 'Adventureworks' SET @sql = 'Use ' + @db EXEC sp_executesql @sql

0 ·

Simonn commented · Feb 16, 2010 at 05:54 AM

Still gives me the same error Cannot create trigger on 'OTA_RetailerName.dbo.TempVoucherUpload' as the target is not in the current database.

0 ·

Answer 3 · 2010-02-16T06:19:08Z

Håkan Winther answered Feb 16, '10

I have tried implementing "USE" in dynamic SQL with sp_executeSQL but didn't find any solution for it. The only solution was to use "fully qualified" name, that is, databasename.schemaname.objectname. In your case, try to use :

'CREATE TRIGGER ' + @retailer + '[dbo].[UpdateVoucherStock]   ON ' 
+ @Retailer +'.[dbo].[TempVoucherUpload]

I am not sure it will work for triggers, but it works for other objects. Personally I would avoid triggers unless I have to, because of the performance impact, but I know there are some benefits with triggers if you can't limit direct access to the tables.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Simonn commented · Feb 16, 2010 at 06:27 AM

Seems like I will have to specify the USE database statement as I still get and error with the code supplied above. Msg 166, Level 15, State 1, Line 1 'CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.

0 ·

Håkan Winther commented · Feb 16, 2010 at 06:30 AM

I was afraid of that! Triggers are a little special becuase it is not a "standalone" object, but it was worth a try I guess.

0 ·

Answer 4 · 2010-02-16T06:40:13Z

Håkan Winther answered Feb 16, '10

DECLARE @sql AS NVARCHAR(4000)
SET @SQL ='USE SQLTools;
select * from sys.procedures;
create table dbo.x (id int NOT NULL);'
execute sp_executesql @sql

This works for me, but it doesn't include a trigger, and if I add a trigger it doesn't work.

You can read more about rules for batches at this page:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1161826_mem1,00.html

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Simonn commented · Feb 16, 2010 at 06:52 AM

Keeps complaining that the CREATE TRIGGER statement should be the first in the batch Msg 111, Level 15, State 1, Line 3 'CREATE TRIGGER' must be the first statement in a query batch.

0 ·

Håkan Winther commented · Feb 16, 2010 at 08:07 AM

Yes it does, because according to the rules I posted it is not possible to specify any statement before create trigger, and you can't have multiple batches in sp_executeSQL

0 ·

Simonn commented · Feb 16, 2010 at 09:03 AM

Need to find a way to get this working. Is there no way of letting the EXEC statement take affect on your current session?

0 ·

question