October 30, 2010 at 1:49 pm
Pff, can't get it to work.
I have a SP in master, and it runs some checks on a db but it should also create a new table in the db it is running the checks on.
From my SP code i have the database name in @otherdb.
From my SP code i tried following but none did work:
CREATE TABLE [@otherdb].[dbo].[my_new_table]
=> does not work
USE [@otherdb]
CREATE TABLE [dbo].[my_new_table]
=> 'use' is not allowed in sp
SET @CMD = "USE [@otherdb] CREATE TABLE [dbo].[my_new_table]"
=> does not work
I'm getting :crazy:
Anyone could help me out? SP needs to stay in master.
October 30, 2010 at 3:20 pm
Build the command in a string and use EXECSql to run it
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'CREATE TABLE ' + '[' + @otherdb' + '].[dbo].[my_new_table]'
Exec ( @SQL)
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 31, 2010 at 9:15 am
Andrew thanks for pointing that out to me. Was using wrong syntax.
Thanks for the help man!
Brgds
T
October 31, 2010 at 9:43 am
And just to be perfectly safe, use QUOTENAME
SET @SQL = 'CREATE TABLE ' + QUOTENAME(@otherdb) + '.[dbo].[my_new_table]'
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply