cross database

[dbforum]

" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.

what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.

i have created a view in db1 with the same name as

'create view tableOne as select * from db2..tableOne'

now i can access.
Is there some othere way with out creating view?

[skhanal]

There is no Synonym in SQL Server 2000 so view is your only option.

[rmiao]

User should be able to query db2 directly once connect to sql server since the user has permission on db2 as you said.

[skhanal]

But the application still looks for it in db1.

[Rawhide]

Well, you could modify your application code to have it look at db2 either by using the full name or by issuing a USE db2 statement first.

[dbforum]

Thank u friends for all your efforts

So there are only two options to achieve
1) View
2) Change the select query in the Application
My client online is using my Application.
Which one is the best?
Give your suggestions

[rmiao]

Use view if you don't want to modify app code.

[Rawhide]

Both are viable options. You have to figure out which one works best for you.