November 16, 2011 at 8:25 pm
Using MS SQL Express 2008.
I first created a Linked Server using the wizard in MS SQL Management Studio. I connected successfully to our in-house server.
My problem began tring to run this query, the \ is the not accepted.
Select * From SVR1\SQLExpress.Database1.Table1
So I tried this and other bracket ideas:
Select * From [SVR1\SQLExpress].Database1.Table1
November 16, 2011 at 10:32 pm
After a linked server is defined, to reference data objects in that linked server, you can use a four-part name in the form linked_server_name.catalog.schema.object_name in Transact-SQL statements.
The parts are defined as:
linked_server_name
Linked server that references the OLE DB data source.
catalog
Catalog in the OLE DB data source that contains the object.
schema
Schema in the catalog that contains the object.
object_name
Data object in the schema.
November 17, 2011 at 2:40 am
It should definitely work with square brackets.
If you created the linked server successfully, navigate your object explorer to one of the remote tables, right click and select "Script Table as SELECT to..." --> "New Query Editor Window". You should see the exact syntax for the statement. You probably have spaces or some other kind of weird char in the linked server definition.
Hope this helps
Gianluca
-- Gianluca Sartori
November 17, 2011 at 8:13 am
Probably the best advice I've received: "Script Table as SELECT to..."
The script shows the proper syntax:
[SVR1\SQLEXPRESS].[aspnetdb].[dbo].[aspnet_Applications]
Also discovered new error trying to run my 'Select Into' statement (create table):
Select * Into [SVR1\SQLEXPRESS].[aspnetdb].[dbo].[new_table]
From aspnetdb.dbo.new_table
Error: "The object name 'SVR1\SQLEXPRESS.aspnetdb.dbo.new_table contains more than
the maximum number of prefixes. The maximum is 2."
So it seems creating a table on a remote SLQ server is not possible, at least the way I'm doing it...
November 17, 2011 at 8:48 am
All queries that have the standard form of SELECT select_list FROM clause WHERE clause are allowed. The INTO new_table_name clause of SELECT is not allowed when the new_table_name refers to a remote table (linked server).
Try INSERT INTO instead.
INSERT INTO MyLinkServer.AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
November 17, 2011 at 9:07 am
That's exactly what I did after creating the tables on the remote.
Thanks for all your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply