Dynamic creat database
-
hey guys i'm trying to use the following script to create a database but getting error
(Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '
ON (NAME =''.
)
.
declare @name nvarchar(200)
declare @month nvarchar(100)
declare @year nvarchar(100)
declare @filename nvarchar(900)
set @filename = N'D:\Data\'+@name+ '_Data.MDF'
set @month = month(convert(nvarchar(16),getdate(),112))
set @year = year(convert(nvarchar(16),getdate()))
set @name = N'Weblog'+@year+@month
--print @name
print @filename
-- This step will take at least 10 minutes.
declare @cmd varchar(4000)
set @cmd = 'CREATE DATABASE '+@name'
ON (NAME ='''+@name+'_Data'''',
FILENAME = ''D:\Data\'+@name +'_Data.MDF'',
SIZE = 10000,
FILEGROWTH = 10%)
LOG ON (NAME = '@name+'_Log','
FILENAME = ''D:\Data\'+@name +'_Log.LDF'' ,''
SIZE = 10,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
'
exec (@cmd)
appreciate all the help thanks
-
I believe the problem is caused by @name being inside the double quotes so SQL server is seeing @name instead of the value you intended to pass.
Try closing the double quotes before the @name and reopening them after.
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
Several syntacs mistakes:
declare @name nvarchar(200)
declare @month nvarchar(100)
declare @year nvarchar(100)
declare @filename nvarchar(900)
set @filename = N'D:\Data\'+@name+ '_Data.MDF'
set @month = month(convert(nvarchar(16),getdate(),112))
set @year = year(convert(nvarchar(16),getdate()))
set @name = N'Weblog'+@year+@month
--print @name
print @filename
-- This step will take at least 10 minutes.
declare @cmd varchar(4000)
set @cmd = 'CREATE DATABASE '+ @name + '
ON (NAME ='''+@name+'_Data'',
FILENAME = ''D:\Data\'+@name +'_Data.MDF'',
SIZE = 10000,
FILEGROWTH = 10%)
LOG ON (NAME = '+ @name+'_Log'',
FILENAME = ''D:\Data\'+@name +'_Log.LDF'' ,''
SIZE = 10,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
'
exec (@cmd)
-
ok, did that but still getting the same error
-
You had some issues with your single quotes ('). try the following code:
declare @name nvarchar(200)declare @month nvarchar(100)declare @year nvarchar(100)declare @filename nvarchar(900)set @filename = N'D:\Data\'+@name+ '_Data.MDF'set @month = month(convert(nvarchar(16),getdate(),112))set @year = year(convert(nvarchar(16),getdate()))set @name = N'Weblog'+@year+@month--print @nameprint @filename-- This step will take at least 10 minutes.declare @cmd varchar(4000)set @cmd = 'CREATE DATABASE '+@name + 'ON (NAME ='''+@name+'_Data'''',FILENAME = ''D:\Data\'+@name +'_Data.MDF'',SIZE = 10000,FILEGROWTH = 10%)LOG ON (NAME = ' + @name + '_Log,FILENAME = ''D:\Data\'+ @name + '_Log.LDF'' ,''SIZE = 10,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--exec (@cmd)print @cmd;
-
There's at least to more places where @name is used and you should make the changes there too.
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
I found one more error when I rechecked my code. Try this:
declare @name nvarchar(200)declare @month nvarchar(100)declare @year nvarchar(100)declare @filename nvarchar(900)set @filename = N'D:\Data\'+@name+ '_Data.MDF'set @month = month(convert(nvarchar(16),getdate(),112))set @year = year(convert(nvarchar(16),getdate()))set @name = N'Weblog'+@year+@month--print @nameprint @filename-- This step will take at least 10 minutes.declare @cmd varchar(4000)set @cmd = 'CREATE DATABASE '+@name + 'ON (NAME ='''+@name+'_Data'''',FILENAME = ''D:\Data\'+@name +'_Data.MDF'',SIZE = 10000,FILEGROWTH = 10%)LOG ON (NAME = ' + @name + '_Log,FILENAME = ''D:\Data\'+ @name + '_Log.LDF'' ,SIZE = 10,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--exec (@cmd)print @cmd; -
i got the following:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'D:'.
Msg 132, Level 15, State 1, Line 7
The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark after the character string ' ,
SIZE = 10,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
'.
-
Ok i got it down to the following error:
Msg 153, Level 15, State 1, Line 6
Invalid usage of the option Weblog20089_Log in the CREATE/ALTER DATABASE statement.
any idea wut this could be?
-
Okay, one last try. I found another place that required a change. I also copied the generated sql to a seperate window in SSMS and checked the syntax, and this looks good:
declare @filename nvarchar(900)set @filename = N'D:\Data\'+@name+ '_Data.MDF'set @month = month(convert(nvarchar(16),getdate(),112))set @year = year(convert(nvarchar(16),getdate()))set @name = N'Weblog'+@year+@month--print @nameprint @filename-- This step will take at least 10 minutes.declare @cmd varchar(4000)set @cmd = 'CREATE DATABASE '+@name + 'ON (NAME ='''+@name+'_Data'',FILENAME = ''D:\Data\'+@name +'_Data.MDF'',SIZE = 10000,FILEGROWTH = 10%)LOG ON (NAME = ' + @name + '_Log,FILENAME = ''D:\Data\'+ @name + '_Log.LDF'' ,SIZE = 10,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--exec (@cmd)print @cmd; -
Sorry. Here is the final version:
You have to make sure that the folder d:\Data exists.
declare @name nvarchar(200)
declare @month nvarchar(100)
declare @year nvarchar(100)
declare @filename nvarchar(900)
set @filename = N'D:\Data\'+@name+ '_Data.MDF'
set @month = month(convert(nvarchar(16),getdate(),112))
set @year = year(convert(nvarchar(16),getdate()))
set @name = N'Weblog'+@year+@month
--print @name
print @filename
-- This step will take at least 10 minutes.
declare @cmd varchar(4000)
set @cmd = 'CREATE DATABASE '+ @name + '
ON (NAME ='+@name+'_Data' + ',
FILENAME = ''D:\Data\'+@name +'_Data.MDF'',
SIZE = 10000,
FILEGROWTH = 10%)
LOG ON (NAME = '+ @name+'_Log' + ',
FILENAME = ''D:\Data\'+@name +'_Log.LDF'' ,
SIZE = 10,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
'
exec (@cmd)
-
ok thts it. thanks
-
The first problem is on this line
set @cmd = 'CREATE DATABASE '+@name'
You have a single quote after the identifier but you need an operator so change it to
set @cmd = 'CREATE DATABASE ' +@name + '
Then you will receive the same type of error message for the line
LOG ON (NAME = '@name+'_Log','
where you have the same type of problem.
I recommend that you change you add a print (@cmd) and just walk through the dynamic string generation a line at a time. You will need a combination of one, two and sometimes three single quotes to get the syntax of the generated string correct.
You always have to have a concatenation operator to join substrings.
You always have to have two single ticks in the string to get one in the output string (I sometimes thnink of them meta-ticks).
It sometimes help to think of each line of the clause as a separate substring.
So
declare @CrLfchar(2)
set @CrLf = Char(13) + Char(10)
Set @Cmd = 'Create Database ' + @Name + @CrLf
+ ' On (Name = '' ' + @Name + '_Dat ' + @CrLf
+ ', FileName = D:\Data\' + @Name + '_Data.mdf' + @CrLf
+ ', Size = 10000' + @CrLf
+ ', FileGrowth = 10%) ' + @CrLf
+ ''''
Print @cmd
The @CrLf just makes the string more readable while you build it up.
The last line is 4 single quotes that closes off the command string for you.
I have always found that I have to build these up one clause at a time and in a very rigoursly formatted manner. Otherwise it takes me a long time to debug them.
Also this format makes it easy to comment out single parameters when you actually start debugging the generated script
The joy of Dynamic SQL
Enjoy
-
Thanks Ray,
the above definitely helps
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply