Cross Database Issue

Hi

I have a view which is like following

Like

DB1.View1

Select * from Accounting,Employee

where Accounting.Emp_Id = Accounting.Emp_Id

and i am using this view in DB2.storedProcedure1, but this gives me an error when I execute this Stored procedure from VBA code. the same SP works from query analyser with same user_id and pwd

Error - Could not find database id 9. Database may not be activated yet or may be in transition.

If I change the view to following the SP works

DB1.View1

Select * from DB1.Accounting,DB1.Employee

where DB1.Accounting.Emp_Id = DB1.Accounting.Emp_Id

But I dont want to change the view as i dont own it.

Please suggest the solution for this.

Thanks in Advance.

Ashutosh

Moving your question to a different thread, where they can offer more help.

Thanks

Richard Cook

VS Debugger Team

|||

Is there a synonym defined on the objects mentioned, forcing SQL Server to go to a non-existing database ? Did you trace the command which is fired against the database using SQL Profiler ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You did not mentioned the version and the service pack of sql server. IF its 2000 check this, it seems to be u have not applied the patches

http://support.microsoft.com/kb/834688

Madhu

|||

Thanks Jens,

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000

|||

Thanks Madhu.

I did saw that post on support.microsoft.com but here there are 2 things

1. It was working till yesterday and stoped working today..so can it be an issue with user privilages?

2. Its working with the solution that i have implmeneted, but that is too specific and i need generic solution.

Its SQL server 2000.

please see this

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000