please help: I'm new to linked servers & in MS SQL I have successfully added one (from an MS Access file) as shown:
I'm having trouble querying this database here. What is the syntax? Thanks.
Asked
Active
Viewed 161 times
0
-
I dont think you need to specify the default – Mihai May 15 '15 at 08:16
-
What are your column types? This naming method wont work if you have XML column types – Matt May 15 '15 at 08:18
-
Isn't [default] the database name? Linked server require four part name - [servername].[dbname].[schemaname].[objectname] – Greg May 15 '15 at 08:20
-
The query as it, its really OK. I tested just now on my server. the problem is in the configuration of linked server. – Tirma May 15 '15 at 08:23
2 Answers
0
Okay, by simply using Management Studio in MS SQL I ran a SELECT query on the table through "Script Table as" (right-click) and with query results I was sure that this then is the format:
SELECT * FROM [RXPIPEDB]...[product]
Furthermore, I can run updates using this syntax:
UPDATE [RXPIPEDB]...[Product] SET X = ..something..
Of-course for the UPDATE query, you must have set your LINKED SERVER properties > Server Options "RPC" & "RPC out" to TRUE.
Thank you all.
Chagbert
- 722
- 7
- 16
-1
The query its OK. U have a problem in the configuration of linked server. Check this: Run a Query from Linked Server (Oracle) in SQL Server2008 R2
-
Thank you for your answer @Tirma, I read through the blog and I note that it is only for linked server to another SQL. My linked server is to MS Access. In both Providers (SQLCNLI and Microsoft.Jet.OLEDB.4.0) these settings are already unchecked (zero level only). From the image, it seems the database name is 'default'. how can that be corrected? – Chagbert May 15 '15 at 08:35
-
i found something similar here: http://stackoverflow.com/questions/6370601/run-a-query-from-linked-server-oracle-in-sql-server2008-r2 – Tirma May 15 '15 at 08:43
-
No the query is not OK if the source is access. Access doesn't know about `dbo`. – Nick.Mc Jun 30 '16 at 01:41