Aug 9, 2005 #1 MrGandalf Programmer Jul 19, 2005 35 NL Hi, Can anyone tell me when to use database name in SQL? For example: Select * from zodata.client OR Select * from client What is the difference? I get the error : Invalid object name client
Hi, Can anyone tell me when to use database name in SQL? For example: Select * from zodata.client OR Select * from client What is the difference? I get the error : Invalid object name client
Aug 9, 2005 1 #2 SajidAttar Programmer May 23, 2005 132 IN if you want to use Table/View from different database then you have to use database name [Example] Use ProductionDB -- Change database to production select * from CustomerDB..Customer -- Will select all rows from Customer table of CustomerDB Database /* you can also create a view to point the table from different databse */ create view dbo.CustomerViewInProductionDB as select * from CustomerDB..Customer [/Example] for more detail look BOL (Books Online) Regards, "There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell Upvote 0 Downvote
if you want to use Table/View from different database then you have to use database name [Example] Use ProductionDB -- Change database to production select * from CustomerDB..Customer -- Will select all rows from Customer table of CustomerDB Database /* you can also create a view to point the table from different databse */ create view dbo.CustomerViewInProductionDB as select * from CustomerDB..Customer [/Example] for more detail look BOL (Books Online) Regards, "There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell
Aug 9, 2005 Thread starter #3 MrGandalf Programmer Jul 19, 2005 35 NL Tx. What if I'm in a stored procedure? Must I then use the database name as a prefix? And must I use it also in the joins and where statements? Upvote 0 Downvote
Tx. What if I'm in a stored procedure? Must I then use the database name as a prefix? And must I use it also in the joins and where statements?
Aug 9, 2005 #4 SajidAttar Programmer May 23, 2005 132 IN If your tables/views are in the same Database, where your SP residing then you dont have to use database name before Tables. and IF YOUR TABLES/VIEWS ARE IN DIFFERENT DATABASE THEN YOU HAVE TO PREFIX THE DATABASE NAME. Regards, "There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell Upvote 0 Downvote
If your tables/views are in the same Database, where your SP residing then you dont have to use database name before Tables. and IF YOUR TABLES/VIEWS ARE IN DIFFERENT DATABASE THEN YOU HAVE TO PREFIX THE DATABASE NAME. Regards, "There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell
Aug 9, 2005 #5 SQLBill MIS May 29, 2001 7,777 US By the way, the reason you got the error is that the proper syntax is: Database.owner.table If you own the table you can leave out the name, but the dots must still be there: Database..table -SQLBill Posting advice: FAQ481-4875 Upvote 0 Downvote
By the way, the reason you got the error is that the proper syntax is: Database.owner.table If you own the table you can leave out the name, but the dots must still be there: Database..table -SQLBill Posting advice: FAQ481-4875
Aug 9, 2005 Thread starter #6 MrGandalf Programmer Jul 19, 2005 35 NL Ah, that explains. I am using temporary tables. That means it's using temdb and my own database. It's clear. Tx, Sajid. Upvote 0 Downvote
Ah, that explains. I am using temporary tables. That means it's using temdb and my own database. It's clear. Tx, Sajid.