Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stumbling
Posting Yak Master
104 Posts |
Posted - 2007-08-28 : 06:45:16
|
| Hi ALLI have a server with 50 databases all the same with different data but the same structure, is there a way to update a table in multiple databases using the sysdatabases to get the relevant database names from? I imagine it would have to be a cursor to do this? I checked thru the Search feature but could not find any info on this topic is it possible?CheersPhil |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-08-28 : 06:57:32
|
| you could use something like the system procedure "sp_msforeachdb"...search here for samples of it in use....and it's restrictions (unsupported i think) |
 |
|
|
stumbling
Posting Yak Master
104 Posts |
Posted - 2007-08-28 : 07:45:21
|
quote: Originally posted by AndrewMurphy you could use something like the system procedure "sp_msforeachdb"...search here for samples of it in use....and it's restrictions (unsupported i think)
Thanks for that you learn something everyday!!I must be missing something but as the following will not run, any ideas?CheersPhildeclare @sql nvarchar(4000)set @sql = ''select @sql = @sql + 'exec ' + name + '..sp_msforeachtable @command1= ''Update arm set code = ''cash1'' where code = ''cash''''' + char(13)from master..sysdatabaseswhere name <> 'master'and name <>'model' and name <>'pubs' and name <> 'msdb'and name <> 'tempdb'and name <>'northwind'print @sqlexec (@sql) |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-08-28 : 09:58:44
|
| what do you get for the "print @SQL" line?You can't say EXEC EXEC etc...."...which in effect is what the exec (@SQL) line states.... |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-08-28 : 10:00:06
|
| Also....investigate using BEGIN TRANSACTION, COMMIT TRANSACTION statements....otherwise this could end up with you in a large pile of "dodo" |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 20:34:30
|
| Did you declare @command1? |
 |
|
|
|
|
|