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
No comments:
Post a Comment