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.
-
I dont think you need to specify the defaultMihai– Mihai2015-05-15 08:16:05 +00:00Commented May 15, 2015 at 8:16
-
What are your column types? This naming method wont work if you have XML column typesMatt– Matt2015-05-15 08:18:16 +00:00Commented May 15, 2015 at 8:18
-
Isn't [default] the database name? Linked server require four part name - [servername].[dbname].[schemaname].[objectname]Greg– Greg2015-05-15 08:20:57 +00:00Commented May 15, 2015 at 8: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– Tirma2015-05-15 08:23:10 +00:00Commented May 15, 2015 at 8:23
Add a comment
|
2 Answers
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.
Comments
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
3 Comments
Chagbert
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?
Tirma
i found something similar here: stackoverflow.com/questions/6370601/…
Nick.Mc
No the query is not OK if the source is access. Access doesn't know about
dbo.