stored procedure

[mohan]

hi

is there is any way i can create a stored proceduere in
all databases in my server(sql 7.0) at a time. i am having about 20 databases.

thanks in advance
mohan

[Ray Miao]

No, have to create it in each db one by one.


------------
mohan at 1/19/01 9:43:30 AM


hi

is there is any way i can create a stored proceduere in
all databases in my server(sql 7.0) at a time. i am having about 20 databases.

thanks in advance
mohan

[Sue]

If this is something you have to do very often there is a third party admin tool from Embarcadero called DBArtisan that does this sort of thing very well.

Or,
You can run a batch using osql.
Save your create statement to a text file and then create a .bat file.

Example of the bat file:

osql -E -SServer1 -dDataBaseA -ic:mssql7jobscreatesp.sql >c:mssql7jobscreatesp.out
osql -E -SServer2 -dDataBaseB -ic:mssql7jobscreatesp.sql >>c:mssql7jobscreatesp.out
osql -E -SServer3 -dDataBaseC -ic:mssql7jobscreatesp.sql >>c:mssql7jobscreatesp.out

The example above is very basic. You may have to experiment to get it perfect. osql is documented in books online.

Of course you need connectivity and the correct permissions on each of these servers.

Warning: If you double click a .bat file it will execute. To edit a .bat file right click and choose 'edit'. Hint: I create .bat files with the .bat1 extention until I'm ready to run it.


------------
mohan at 1/19/01 9:43:30 AM


hi

is there is any way i can create a stored proceduere in
all databases in my server(sql 7.0) at a time. i am having about 20 databases.

thanks in advance
mohan

[Jim W]

You can also just create the one stored procedure in a common database and reference it everywhere else by using "exec commondb..mystoredproc"

Of course, it depends on what it updates. If you are referencing the tables specific to the database, it wouldn't be advisable. But if you are creating it to perform a system function, such as kicking off an email, it works great.

Also, I believe you can put the stored proc in the master db for use by every database, if it is something like KickOffEmail.


------------
mohan at 1/19/01 9:43:30 AM


hi

is there is any way i can create a stored proceduere in
all databases in my server(sql 7.0) at a time. i am having about 20 databases.

thanks in advance
mohan

[Greg G.]

This can work with just about any size stored procedure, however, it can become more difficult the larger the procedure. The process below is a good way to build dynamic scripts.

This script should be ran where ever your sysdatabases table resides, probably 'Master' database.

Drop Procedure RunAgainstAllDatabases
Go
Create Procedure RunAgainstAllDatabases (@procedurename varchar(50)) as

Create Table #temp (id identity(1,1), databasename varchar(50))

Insert Into #temp (databasename)
Select *
From sysdatabases
Where name = 'userdatabases' --You need to specify the databases here

Declare @counter int,
@counttemptable int,
@databasename varchar(50)
@string1 nvarchar(8000)
@string2 nvarchar(8000) --Create as many string variables as necessary.

Select @counttemptable = count(*)
From #temp

While @counter <= @counttemptable
Begin

Select @databasename = databasename
From #temp
Where id = @counter


Select @string1 = &#39;Use &#39; + @databasename + &#39; GO Create Procedure &#39; + @procedurename + &#39; As &#39; +
&#39;Create your stored procedure within a string variable. If you max your
String variable, use your other String variables which you created above. Then
concat your strings together&#39;

Exec @String1 -- + @string2 etc...

End

Go

I hope this helps.

Greg

------------
mohan at 1/19/01 9:43:30 AM


hi

is there is any way i can create a stored proceduere in
all databases in my server(sql 7.0) at a time. i am having about 20 databases.

thanks in advance
mohan

[Greg G.]

This can work with just about any size stored procedure, however, it can become more difficult the larger the procedure. The process below is a good way to build dynamic scripts.

This script should be ran where ever your sysdatabases table resides, probably &#39;Master&#39; database.

Drop Procedure RunAgainstAllDatabases
Go
Create Procedure RunAgainstAllDatabases (@procedurename varchar(50)) as

Create Table #temp (id identity(1,1), databasename varchar(50))

Insert Into #temp (databasename)
Select *
From sysdatabases
Where name = &#39;userdatabases&#39; --You need to specify the databases here

Declare @counter int,
@counttemptable int,
@databasename varchar(50)
@string1 nvarchar(8000)
@string2 nvarchar(8000) --Create as many string variables as necessary.

Select @counttemptable = count(*)
From #temp

While @counter <= @counttemptable
Begin

Select @databasename = databasename
From #temp
Where id = @counter


Select @string1 = &#39;Use &#39; + @databasename + &#39; GO Create Procedure &#39; + @procedurename + &#39; As &#39; +
&#39;Create your stored procedure within a string variable. If you max your
String variable, use your other String variables which you created above. Then
concat your strings together&#39;

Exec @String1 -- + @string2 etc...

Select @counter = @counter + 1


End

Go

I hope this helps.

Greg

------------
mohan at 1/19/01 9:43:30 AM


hi

is there is any way i can create a stored proceduere in
all databases in my server(sql 7.0) at a time. i am having about 20 databases.

thanks in advance
mohan