x

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?

more ▼

asked Feb 16 '10 at 04:59 AM in Default

Simonn gravatar image

Simonn
33 2 2 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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".

more ▼

answered Feb 16 '10 at 11:08 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

+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?
Feb 16 '10 at 12:46 PM Håkan Winther
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).
Feb 16 '10 at 02:26 PM Tom Staab
Fantastic answer. Depending on the precise intended use you could pull it out of pure sql and execute it from Python/Powershell/C#. That would let you easily connect to the new database after it is created and if done in one of the .Net variants you could also leverage SMO.
Feb 16 '10 at 02:59 PM TimothyAWiseman
My favorite part of the longer example is the use of quadruple quotes for quotes inside quotes inside quotes. :)
Feb 16 '10 at 04:15 PM Tom Staab
Pure genius!!!! Thank you so much I worked like a charm just had to made some more modifications to the create trigger statement.
Feb 17 '10 at 06:51 AM Simonn
(comments are locked)
10|1200 characters needed characters left

Simply add a GO statement after the USE command eg:

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

answered Feb 16 '10 at 05:21 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

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'.
Feb 16 '10 at 05:26 AM Simonn

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
Feb 16 '10 at 05:35 AM Fatherjack ♦♦

Still gives me the same error

Cannot create trigger on 'OTA_RetailerName.dbo.TempVoucherUpload' as the target is not in the current database.
Feb 16 '10 at 05:54 AM Simonn
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 16 '10 at 06:19 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

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.
Feb 16 '10 at 06:27 AM Simonn
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.
Feb 16 '10 at 06:30 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
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

more ▼

answered Feb 16 '10 at 06:40 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

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.
Feb 16 '10 at 06:52 AM Simonn
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
Feb 16 '10 at 08:07 AM Håkan Winther
Need to find a way to get this working. Is there no way of letting the EXEC statement take affect on your current session?
Feb 16 '10 at 09:03 AM Simonn
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x672
x235
x107
x51

asked: Feb 16 '10 at 04:59 AM

Seen: 20987 times

Last Updated: Feb 16 '10 at 06:05 AM