 |
|
|
|
Next: Design question: User objects and a Role object
|
| Author |
Message |
External
Since: Jan 14, 2008
Posts: 164
|
(Msg. 1) Posted: Thu Oct 09, 2008 11:11 am
Post subject: Problem in sql join with two tables in different database on same
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hi,
I have a table named temp in a database PRR. I also have (in the same server
) another database named PRS.There is a table named Plants in the PRS
database. Table plants have a field named plant_desc
PlantID in table temp is equivalent to plant field in Plants table.
Project_Num
, (Left([project_num],  ) AS project_num_trun
, Project_Desc
, Eng_ID
, Last_Name
, First_Name
, TC_Num
, TC_Loc
, PlantID
are from table temp in PRR database
Now I am trying to do a join in these two tables using a select query in QA.
However I am getting the following error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'PRS' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
I am not sure why this error message is coming. Any help is highly
appreciated. Thanks
CODE:
SELECT
Project_Num
, (Left([project_num], ) AS project_num_trun
, Project_Desc
, Eng_ID
, Last_Name
, First_Name
, TC_Num
, TC_Loc
, PlantID
, Plant_Desc
FROM PRR.dbo.temp1 LEFT JOIN
PRS.dbo.Plants.Plants ON temp1.PlantID = PRS.dbo.Plants.Plant |
|
| Back to top |
|
 | |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 2) Posted: Thu Oct 09, 2008 2:28 pm
Post subject: Re: Problem in sql join with two tables in different database on same [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Aug 20, 2008
Posts: 672
|
(Msg. 3) Posted: Thu Oct 09, 2008 2:32 pm
Post subject: Re: Problem in sql join with two tables in different database on [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Seems you just have an extra part in the reference to the Plants table.
When referencing tables on the same server you just need the 3 part
notation "Database.Schema.Table". This should work:
SELECT
Project_Num
, (Left([project_num], ) AS project_num_trun
, Project_Desc
, Eng_ID
, Last_Name
, First_Name
, TC_Num
, TC_Loc
, PlantID
, Plant_Desc
FROM PRR.dbo.temp1 AS T
LEFT JOIN PRS.dbo.Plants AS P
ON T.PlantID = P.Plant;
--
Plamen Ratchev
http://www.SQLStudio.com >> Stay informed about: Problem in sql join with two tables in different database .. |
|
| Back to top |
|
| |
External
Since: Jan 14, 2008
Posts: 164
|
(Msg. 4) Posted: Thu Oct 09, 2008 2:32 pm
Post subject: Re: Problem in sql join with two tables in different database on s [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks Aaron and Plamen for your quick help. I was checking the code again
and again overlooking this issue. Folks you saved my time. Thanks. Regards.
"Plamen Ratchev" wrote:
> Seems you just have an extra part in the reference to the Plants table.
> When referencing tables on the same server you just need the 3 part
> notation "Database.Schema.Table". This should work:
>
> SELECT
> Project_Num
> , (Left([project_num],) AS project_num_trun
> , Project_Desc
> , Eng_ID
> , Last_Name
> , First_Name
> , TC_Num
> , TC_Loc
> , PlantID
> , Plant_Desc
> FROM PRR.dbo.temp1 AS T
> LEFT JOIN PRS.dbo.Plants AS P
> ON T.PlantID = P.Plant;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> >> Stay informed about: Problem in sql join with two tables in different database .. |
|
| Back to top |
|
| |
External
Since: Feb 17, 2011
Posts: 1
|
(Msg. 5) Posted: Thu Feb 17, 2011 1:25 am
Post subject: Re: Thanks Aaron and Plamen for your quick help. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
I WAS ALSO HAVING THE SAME PROBLEM ...WAS JOINING TABLES DIFF DATABASE FROM SAME SERVER ...BUT WAS USING FULLY QUALIFIED NAME....
THANX GUYS
> On Thursday, October 09, 2008 2:11 PM Jac wrote:
> Hi,
> I have a table named temp in a database PRR. I also have (in the same server
> ) another database named PRS.There is a table named Plants in the PRS
> database. Table plants have a field named plant_desc
> PlantID in table temp is equivalent to plant field in Plants table.
>
> Project_Num
> , (Left([project_num],) AS project_num_trun
> , Project_Desc
> , Eng_ID
> , Last_Name
> , First_Name
> , TC_Num
> , TC_Loc
> , PlantID
>
> are from table temp in PRR database
>
> Now I am trying to do a join in these two tables using a select query in QA.
> However I am getting the following error:
>
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'PRS' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
>
> I am not sure why this error message is coming. Any help is highly
> appreciated. Thanks
> CODE:
>
> SELECT
> Project_Num
> , (Left([project_num],) AS project_num_trun
> , Project_Desc
> , Eng_ID
> , Last_Name
> , First_Name
> , TC_Num
> , TC_Loc
> , PlantID
> , Plant_Desc
> FROM PRR.dbo.temp1 LEFT JOIN
> PRS.dbo.Plants.Plants ON temp1.PlantID = PRS.dbo.Plants.Plant
>> On Thursday, October 09, 2008 2:28 PM Aaron Bertrand [SQL Server MVP] wrote:
>> This should be
>>
>> PRS.dbo.Plants
>>> On Thursday, October 09, 2008 2:32 PM Plamen Ratchev wrote:
>>> Seems you just have an extra part in the reference to the Plants table.
>>> When referencing tables on the same server you just need the 3 part
>>> notation "Database.Schema.Table". This should work:
>>>
>>> SELECT
>>> Project_Num
>>> , (Left([project_num],) AS project_num_trun
>>> , Project_Desc
>>> , Eng_ID
>>> , Last_Name
>>> , First_Name
>>> , TC_Num
>>> , TC_Loc
>>> , PlantID
>>> , Plant_Desc
>>> FROM PRR.dbo.temp1 AS T
>>> LEFT JOIN PRS.dbo.Plants AS P
>>> ON T.PlantID = P.Plant;
>>>
>>> --
>>> Plamen Ratchev
>>> http://www.SQLStudio.com
>>>> On Thursday, October 09, 2008 2:41 PM Jac wrote:
>>>> Thanks Aaron and Plamen for your quick help. I was checking the code again
>>>> and again overlooking this issue. Folks you saved my time. Thanks. Regards.
>>>>
>>>> "Plamen Ratchev" wrote:
>>>> Submitted via EggHeadCafe
>>>> WCF Generic DataContract object Serializer
>>>> http://www.eggheadcafe.com/tutorials/aspnet/59ae2b9e-a3be-4cd5-a0ef-93...abbdc3a >> Stay informed about: Problem in sql join with two tables in different database .. |
|
| Back to top |
|
| |
| Related Topics: | Join two tables - Hi, I want to join two different table which should give me the output in this format. TableA Session_ID Title_Name Vendor 607 AAAA uty 607 BBBB uvi 607 CCCC tyu 708 YUYU jhj..
Cannot join tables -
complicated join for view three tables. - I have three tables filelog id filename eventlog id eventdescr eventdatetime errorlog id errordescr errordatetime Id connects all three tables. I need to produce id filename type descr datetime where descr and datetime migh...
join tables, having, not exists (case end) ...? - Hy, The first table is for coding cloth(name, etc.), the second table contain what is neded to create that cloth. Since I know that some things must be present to create a piece of cloth, I want a list of cloths cods that can't be made with the actual..
left join/inner join/outer join with repeat values - hi david, thanks for the reply. basically what i need this for is: there are two tables: table1 is a master table, table2 is a detail table. table1's primary_id is present in details_id of table2 table1 has only 1 record table2 could have many.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
