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 |
|
kalyan.cse05
Yak Posting Veteran
74 Posts |
 Posted - 2011-04-20 : 07:09:42
|
| Hi,I have stored procedure which use try to access a table which resides in a different database. The sp runs perfectly in sql server 2005 but gives error in SQL server 2008. It's now not able to access the table which reside in the different databaseExample:SELECT ABC.aID as ABCNum, Test.Test_Code as Test, UOM.UOM_Code as ABC_UOM_ID, UOM.Description as ABC_UOM, ABC.aQtyProblemTestHeld as Qty_Held, ABC.aQtyProblemTestConsumed as Qty_Consumed, Test.Base_UOM as Test_UOM_ID, UOM2.Description as Test_UOM, '' as UOMSelect, ''as IsDirty, '' as Msg, '' as Display, Test.Old_Test_Code as TestOld FROM dbo.ABC INNER JOIN Second_database.dbo.Test Test on ABC.aTestID= Test.Test_Code AND Test.Language_Code = 'EN' LEFT OUTER JOIN Second_database.dbo.UOM UOM on ABC.aBase_UOM= UOM.UOM_Code AND UOM.Language_Code = 'xx' Left OUTER JOIN Second_database.dbo.UOM UOM2 on Test.Base_UOM = UOM2.UOM_Code AND UOM2.Language_Code = 'xx'Here the sp trying to access the table Test which resides on the different database and SQL server 2008 unable to ge the table Test as weel as the Second_database.Please helpkalyan Ashis Dey |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-20 : 07:17:02
|
| Whats the actual error ???--------------------------http://connectsql.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-20 : 08:08:46
|
| Does the login have access to the other database (and table).Do they have the same collation?I take it the databases are on the same server?Should be obvious from the error message.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
How to access a table from a different database