- in the name of the Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

- in the name of the Server

Author

Topic

mab
Starting Member

3 Posts

Posted - 2014-10-06 : 19:56:58

We need to run a query from 2 data sets on 2 servers. We are trying to name one server in the query: Using XYZ-SQL1.abc.inventory_qty.qty_on_shelf produces several errors. Does the - in the server name pose a problem?

Or how would I name the ABC database in this formula with a server name of XYZ-SQL1? Can we use the IP address?

Select inventor.item_id, inventor.available, inventor.qty_pickable, inventor.qty_on_hand, sku, abc.dbo.inventory_qty.qty_on_shelf
From INVENTOR
inner join abc.dbo.product_sku on sku = item_id
inner join abc.dbo.inventory_qty on abc.dbo.inventory_qty.product_id = abc.dbo.product_sku.product_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 20:00:57

[XYZ-SQL1].abc.dbo.inventory_qty

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mab
Starting Member

3 Posts

Posted - 2014-10-06 : 21:41:30

I added the brackets and it resulted in the error code below.
Select inventor.item_id, inventor.available, inventor.qty_pickable, inventor.qty_on_hand, sku, [XYZ-SQL1].abc.inventory_qty.qty_on_shelf
From INVENTOR
inner join [XYZ-SQL1].abc.product_sku on sku = item_id
inner join [XYZ-SQL1].abc.inventory_qty on [XYZ-SQL1].abc.inventory_qty.product_id = [XYZ-SQL1].abc.product_sku.product_id

Msg 208, Level 16, State 1, Line 3
Invalid object name 'XYZ-SQL1.abc.product_sku'.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'XYZ-SQL1.abc.inventory_qty'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-07 : 11:59:24

You can't use the 4-part name in the select. Use an alias instead.

Select inventor.item_id, inventor.available, inventor.qty_pickable, inventor.qty_on_hand, sku, iq.qty_on_shelf
From INVENTOR
inner join [XYZ-SQL1].abc.product_sku on sku = item_id
inner join [XYZ-SQL1].abc.inventory_qty iq on iq.product_id = [XYZ-SQL1].abc.product_sku.product_id

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-07 : 12:29:08

You can use a 4-part name in the SELECT FROM, but you can't leave out any parts

. However, you definitely want to start using aliases, because it will make modifying your queries soooo much easier.

FROM INVENTOR i
inner join [XYZ-SQL1].abc.dbo.product_sku ps on ps.sku = i.item_id
inner join [XYZ-SQL1].abc.dbo.inventory_qty iq on iq.product_id = ps.product_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources