July 11, 2008 at 12:30 am
Hi all,
i m using single stored proc which create 5 database and 7 tables for each database.......but before creating tables when i use database by syntax:
USE dabase_name
it shows ERROR:
a USE database statement is not allowed in a procedure, function or trigger.
can anyone give me solution, how to do this.....
July 11, 2008 at 12:49 am
Hi,
Why are you creating stored procedure for this?
Instead you can create script like:
IF DB_ID('Database1') IS NOT NULL
DROP DATABASE Database1
GO
CREATE DATABASE Database1
GO
CREATE TABLE Database1.dbo.Table1(id int)
CREATE TABLE Database1.dbo.Table2(id int)
GO
IF DB_ID('Database2') IS NOT NULL
DROP DATABASE Database2
GO
CREATE DATABASE Database2
GO
CREATE TABLE Database2.dbo.Table1(id int)
CREATE TABLE Database2.dbo.Table2(id int)
July 11, 2008 at 1:11 am
Thanx Hari,
i can do this but what i want to do is to create such database on multiple machine(local for each)....So i m thinking to create stored procedure which is then called from Vb application....if i write scipt directly then how i can create executable from it or how can i run that script from Vb application which will then give me exe...ur suggession will be lot of help for me....
July 11, 2008 at 1:29 am
Hi,
As far as I know, the Use can't be given directly inside the procedure as a statement. But, you can try this.
DECLARE @alert_str varchar(25)
SET @alert_str = 'use ' + '['+@dbname+']'
EXEC (@alert_str)
Renuka__
[font="Verdana"]Renuka__[/font]
July 11, 2008 at 8:47 am
A possible way to do this a bit more easily than from a VB app, is do it in Management Studio. You can even run the script on one server, then use the reconnect button (on the toolbar) to connect to a different server, then run the script on that server; repeate for each server.
Otherwise, if you really need to do this from VB, you could save the script as a file, and then pull that file into VB and run it from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 9:09 am
Thanx Gsquared
you got what i exactly want to say....but i dont want to go for 1st way you suggested as i dont want my client to use management studio....
Thus i want to run script from VB itself...
can u expain it with sample piece of code??
July 14, 2008 at 7:26 am
I really can't give you a meaningful VB example, since it's going to be specific to your application. In VBA, you would create a filesystem object, define a textstream connection to read from your script file, feed that into an SQLCmd object, open a connection to the database, and execute the SQLCmd object.
Of course, if you're setting up end-users to do that, you might as well just remove all security on your SQL server right now and put up a sign that says, "Please hack me!", since all anyone has to do is edit the script to do pretty much whatever they want to the database.
I'd really hate to see what happens if this ends up on a laptop that someone loses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 9:34 pm
Create the tables using three part names, then you don't need any USE statements in your procedure.
CREATE TABLE DatabaseName.dbo.TableName ...
July 16, 2008 at 4:21 pm
If you're creating five copies of the same database containing the same seven tables, how about creating them in the Model database then just create the databases? You might need to drop the tables from Model after you're done, unless you want all future copies to have those additional seven tables.
I can't find a specific reference, but there are some commands that you can't do in a stored procedure, and I would bet that USE (dbname) is one of them. Since the stored procedure is saved in the current database, you're sort of breaking your link to the code that you're running if you change the database.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 16, 2008 at 6:04 pm
I am assuming that each local machine has SQL server installed.
Using either VB6, VB dot net or VB2005. Use ADO. Create an ADO command object. Set the command object type to "Text", then set the command text to your script
IF DB_ID('Database1') IS NOT NULL
DROP DATABASE Database1
GO
CREATE DATABASE Database1
GO
CREATE TABLE Database1.dbo.Table1(id int)
CREATE TABLE Database1.dbo.Table2(id int)
GO
Then open a connection to the master database, assigned this connection to the command objects active connection. Then execute the command. For example.
Dim Ado_Cmd as ADODB.Command
Dim Cnn As ADODB.Connection
Set Cnn = New ADODB.Connection
SET Ado_cmd = New adodb.command
'here you add the code required to open the connection'
Ado_cmd.activeconnection = cnn
Ado_cmd.comandtype = adCmdText
Ado_cmd.commandText = "your script"
Ado_cmd.execute
I will let you fill in the assigning the connection string to the connection object and opening the connection
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply