I got this script from the query analyzer templates as a database basic template:
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'<database_name, sysname, test_db>')
DROP DATABASE <database_name, sysname, test_db>
GO
CREATE DATABASE <database_name, sysname, test_db>
GO
I am not sure what it all means, what I need to do it name the database a certain name each time. and then create one table with 6 column names.
can someone give me guidance please...the script is just trying to make sure there is no other db with that name and drop the db one if there is one..
hth|||Not really, heres what I got...
Declare @.name Char(10)
Set @.name = 'test'
CREATE DATABASE [@.name] ON (NAME = N'' & @.name & '_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\' & @.name & '_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'' & @.name & '_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\' & @.name & '_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'@.name', N'autoclose', N'false'
GOexec sp_dboption N'@.name', N'bulkcopy', N'false'
GO
and so on...
Getting the variable '@.name' to go where I want it is where my problem occurs|||its more like
CREATE DATABASE+ @.name+ ON (NAME = N'' ......
hth|||exec sp_dboption N'hol', N'read only', N'false'
GO
What I need to do is pass, from the web page, a variable called @.name
So where it shows N'hol', the variable passed from the web form textbox whould go where 'hol' is.
N'' + @.name + '', does not work. what is the proper sytax for this?
E|||I suspect you don't understand what the 'N' bit means? (sorry if I'm wrong) You could split the batch up and use proper params. Although I'm a tad concerned that a web users is allowed to issue such commands, but hey I'm sure you've taken care of the security aspect.|||absolutely, what I am trying to do is automate as much as possible. this will be a hidden command in which the user can maintain an image database just by naming it where the form pulls the name from a textbox. Administration only should have access to this sort of command, so its all good with security.
SO tell me more about this spliting the batch up and the use of proper param names you speak of.. No, I am not sure of what the 'N' bit means either. Can you help?|||Params are good because you can literally use
exec spbla @.myParam1, @.myParam2
and you won't have to worry about all the horrible string concat problems.
Lookup SQLParameter.
Plus if your command type is stored procedure you can call spblaa directly!
I think you'll have to split up the batch cause normally client exec'd code doesn't like the "GO"s, although I've not actually tried it with the ADO.net.
I'll whisper the following, you don't really need the 'N' bit. Its just saying that the text is going to be unicode. You rarely have to worry about that, certainly not for params (i.e you don't prefix a param with 'N'). I didn't say that ok ;)
