Rahul
3/16/2007 10:35:00 AM
On Mar 16, 1:51 pm, "M A Srinivas" <masri...@gmail.com> wrote:
> On Mar 16, 12:20 pm, "Agnes" <a...@dynamictech.com.hk> wrote:
>
> > My sql server ont over 50 Database, and they got one standard and samme
> > Store Procedure
> > Now, I need to update this SP.
> > for my old method. I place the SP content into SQL analyzer and choose each
> > Database and then press "run"
> > For 50 databases , I need to choose 50 times and press 50 times.
>
> > Any simple method ?
> > such as I write a program and pass the database name to that program.
> > program A -> call program B
> > program A got selected database list --> program B "contains updated SP"
> > Thanks
>
> I assume you are using SQL 2000 . In that case write a script an run
> through osql
Exec sp_ExecDLLFromAllDatabases '\\test\C:\Test.txt', 'Adb, Bdb, Cdb'
Create Procedure sp_ExecDLLFromAllDatabases
(
@pFilePath VarChar(500),
@pDatabases VarChar(8000)
)
AS
Begin
Declare @lDatabase VarChar(100),
@lpos Int,
@Cmd VarChar(8000)
Set @lDatabase = ''
Set @pDatabases = @pDatabases + ','
Set @lpos = PatIndex('%,%', @pDatabases)
While (@lpos > 0)
Begin
Set @lDatabase = lTrim(rTrim(SubString(@pDatabases, 1, @lpos - 1)))
Set @pDatabases = SubString(@pDatabases, @lpos + 1,
Len(@pDatabases))
Set @lpos = PatIndex('%,%', @pDatabases)
Set @Cmd = 'Use ' + @lDatabase + '; '
Set @Cmd = @Cmd + 'master..xp_cmdshell type ' + @pFilePath
End
End
--Rahul