Remote Server Syntax
scott
queries from within mang. studio to update the remote server's db with data
from my local sql server.
What would the SELECT syntax look like to access the remote server?
My remote server has a hostname address called "db1.mydomain.com" and an ip
address of 87.111.100.2.
Would a select statement look like the below? The below syntax gives an
error saying"
The object name 'dbo.db1.mydomain.com.mydb.mytable contains more than the
maximum number of prefixes. The maximum is 3."
CODE:
==================
SELECT * from dbo.db1.mydomain.com.mydb.mytable
Jeffrey Williams
1) Use a linked server and access the table directly using 4-part naming
2) Use SSIS and build a package
3) Export to a file and import from the file
My preference would be to use SSIS and build a package that connects
directly to each instance. To use a linked server, you first have to create
the linked server - and then you can use a query like:
SELECT {columns}
FROM linkedservername.database.schema.table
WHERE {criteria};
Jeff
"scott" <sba...@mileslumber.com> wrote in message
news:#ya8FghT...@TK2MSFTNGP04.phx.gbl...
scott
CODE:
Select * FROM Northwind..Categories
"Jeffrey Williams" <jeff.wil...@verizon.net> wrote in message
news:D2313A51-962F-4E6B...@microsoft.com...
Erland Sommarskog
> What does the below "double period" syntax refer to?
>
> CODE:
>
> Select * FROM Northwind..Categories
are separated by periods.
The leftmost component is the object. (For instance a table) This component
is mandatory.
The next component is the schema. A schema holds a group of objects, and
it's kind of a namespace. If you leave out the schema, the user's default
schema applies. It many cases this is the dbo schema.
The third component from the left is the database. If left out the
current database is implied.
The fourth component from the left is the server. Default is the local
server.
In the example above, database and table have been specified, but the
schema is left out. In practice this is the same as
Northwind..Catogories
But if your default schema in Northwind is Scott, and there is a
Northwind.Scott.Categories too, Northwind..Categories would resolve
to that table.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>scott (sba...@mileslumber.com) writes:
>> What does the below "double period" syntax refer to?
>>
>> CODE:
>>
>> Select * FROM Northwind..Categories
>
>An object reference in SQL Server can have to up for components that
>are separated by periods.
>
>The leftmost component is the object. (For instance a table) This component
^^^^
>is mandatory.
>The next component is the schema. A schema holds a group of objects, and
>it's kind of a namespace. If you leave out the schema, the user's default
>schema applies. It many cases this is the dbo schema.
>
>The third component from the left is the database. If left out the
^^^^
>current database is implied.
>
>The fourth component from the left is the server. Default is the local
^^^^
>server.
I hope you meant "right" in each of the above cases.
>In the example above, database and table have been specified, but the
>schema is left out. In practice this is the same as
>
> Northwind..Catogories
>
>But if your default schema in Northwind is Scott, and there is a
>Northwind.Scott.Categories too, Northwind..Categories would resolve
>to that table.
Sincerely,
Gene Wirchenko
Erland Sommarskog
Thank for correct what I *left* out!
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx