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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-10 : 07:53:55
|
| Mark writes "How can I change the database from with a stored proc.I have used the EXEC ('use ' + @new_db), however, it does not seem to work. It also does not give an error.Thanks for any help you can provide." |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-10 : 07:57:58
|
| You should use all the queries inside that EXECExec('Use '+@new_db+'Select columns from Table')Better to avoid passing DBName as parameterMadhivananFailing to plan is Planning to fail |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-11 : 16:37:44
|
| EXEC ('use ' + @new_db)does work - it just has the scope of the batch and so reverts to your database when the statement completes.you could also useexec (@new_db + 'dbo.sp_executesql N''select columns from table''')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-12 : 00:58:57
|
| Well Nigel. Which method is more efficient?MadhivananFailing to plan is Planning to fail |
|
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-21 : 15:31:27
|
| I am not sure in which situation we will be using option 1 as the scope is limited to the statement.In terms of performance it should be the same. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-21 : 20:08:36
|
| If you want to just process some data in the other database just prefix the table name with the database name:SELECT * FROM OtherDatabase.dbo.MyTableKristen |
|
|
|
|
|
|
Stored Proc