Stack Overflow
Stack Overflow

Questions tagged [openquery]

OPENQUERY is a T-SQL function that allows for executing a pass-through query on a linked server. The linked server should be an OLE DB data source. OPENQUERY is used in SELECT, INSERT, DELETE and UPDATE statements as if it were a table name.

421 questions
106
votes
15 answers

including parameters in OPENQUERY

How can I use a parameter inside sql openquery, such as: SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME where field1=@someParameter') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK
gaponte69
  • 1,337
  • 3
  • 14
  • 21
23
votes
7 answers

SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery

I am trying to query a MySQL linked server using SQL Server. The below query runs just fine. SELECT * FROM OPENQUERY([Linked_Server], 'SELECT * FROM Table_Name') Is it possible to run the same query without using the OpenQuery call?
Kevin
  • 491
  • 1
  • 4
  • 6
21
votes
2 answers

How to ignore duplicate keys when extracting data using OPENQUERY while joining two tables?

I am trying to insert records into MySQL database from a MS SQL Server using the "OPENQUERY" but what I am trying to do is ignore the duplicate keys messages. so when the query run into a duplicate then ignore it and keep going. What ideas can I do…
Jaylen
  • 39,043
  • 40
  • 128
  • 221
15
votes
2 answers

Workaround for calling table-valued function remotely in SQL Server has even more issues

I had a query with a set of parameters that needed to be run multiple times with different parameters, so I wrapped it in a table-valued function. That table valued function needed called from a remote server. Unfortunately, the call fails on the…
Triynko
  • 18,766
  • 21
  • 107
  • 173
13
votes
6 answers

Remote Query is slow when using variables vs literal

I've searched everywhere for this situation and can't find a solution except for Dynamic SQL, which I don't want to use. Here is the table I want to UPDATE on server 2: (Stuff Id UNIQUEIDENTIFIER , stuffname NVARCHAR(64)) I need to update it from…
Greg Viers
  • 3,473
  • 3
  • 18
  • 36
8
votes
1 answer

MySQL Linked Server can't update longtext

I have a Linked Server from SQL Server to my mySQL database (Windows Server using MySQL ODBC Driver 5.3). I have a unique situation where I can only get my longtext columns to return if I convert them in an OPEN QUERY: SELECT * FROM OPENQUERY…
Paul D
  • 676
  • 6
  • 8
8
votes
4 answers

Slow query when connecting to linked server

I've got this query UPDATE linkeddb...table SET field1 = 'Y' WHERE column1 = '1234' This takes 23 seconds to select and update one row But if I use openquery (which I don't want to) then it only takes half a second. The reason I don't want to use…
Jamie Taylor
  • 3,500
  • 21
  • 65
  • 99
8
votes
3 answers

Escape single quote in openquery using dynamic query

I need to retrieve data from a linked server using a parameter, @PickedDate for example. The query works fine if I skip @A and @B, but it always returns an error due to a missing single quote. Please advise, thanks. The query: Declare @OPENQUERY…
Weihui Guo
  • 3,669
  • 5
  • 34
  • 56
8
votes
4 answers

Using OPENQUERY (exec stored procedure) to create new temporary table fails with error 11526

I have SQL Server 2012 full version installed on my development PC. I am trying to follow the examples here, which show how to create a new temporary table using a stored procedure as the source of the data. I am trying to combine the results of…
Tim
  • 8,669
  • 31
  • 105
  • 183
7
votes
7 answers

Execute stored proc with OPENQUERY

I have SQL Server 2008 with a linked Sybase server and I am trying to execute a stored procedure on the Sybase server using OPENQUERY. If I have a stored proc that doesn't take parameters it succeeds fine. If I have a stored proc with parameters…
ctrlalt313373
  • 3,935
  • 7
  • 36
  • 40
7
votes
1 answer

OpenRowSet, OpenQuery, OpenDataSource - which is better in terms of performance

This can be a debatable answer, but I'm looking for the case where a local Excel file needs to be exported to a local SQL Server 2008' table. Has anyone ever had the chance to check execution time to compare OpenRowSet/OpenQuery/OpenDataSource for a…
Loser Coder
  • 2,338
  • 8
  • 42
  • 66
7
votes
1 answer

SQL Server 2012 - Insert into linked server table using openquery

I have a linked server Remoteserver containing a table that holds file and folder names from a dir When I am on the remote server I can run a built in procedure (xp_dirtree) and populate the 'files' table but what i need to do is to run a query from…
Kiran Reddy
  • 2,836
  • 2
  • 16
  • 20
6
votes
4 answers

How can I pass parameters to a pass-through query in c#?

I have a C# program that uses OPENQUERY() to select from a linked server. This pass-through query takes in a parameter. To guard against SQL injection, I would like to pass this parameter in a manner similar to SqlCommand.Parameters.AddWithValue,…
user3750325
  • 1,502
  • 1
  • 18
  • 37
6
votes
3 answers

Why is using OPENQUERY on a local server bad?

I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and…
Dlongnecker
  • 3,008
  • 3
  • 25
  • 40
6
votes
2 answers

Update table using Openquery linked server

I've tried this code and still got the following error, perhaps anyone could help? UPDATE a SET a.MMDWNO = '21' FROM OPENQUERY(NMIIFLIB, 'select * from MVXCDTANSN.MITMAS WHERE MMITTY = ''25''') a Error : OLE DB provider "MSDASQL"…
user1088172
1
2 3
…
27 28