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.
Questions tagged [openquery]
421 questions
2
votes
1 answer
Using OPENQUERY with an Oracle database to query a date range
We have an Oracle database that we access through OpenQuery for some stuff. You apparently can't do date comparisons directly in OpenQuery using the OleDB driver, so to get around this you have to convert the dates to Julien dates and compare those…
Pete
- 6,585
- 5
- 43
- 69
2
votes
2 answers
How to pass datetime as parameter to a sql openquery
I have an OPENQUERY statement
SELECT *
FROM OPENQUERY (NETLINE,
'SELECT * FROM XCREW.CTNAISV_HOTELCREW where RESERVATION_DATE = ''2015-05-01''')
After executing it I have error message on 2015
How to pass datetime as parameter in…
Zvonimir Tokic
- 443
- 9
- 19
2
votes
1 answer
Update Open Query for Oracle linked server
I return an open query for updating table data in oracle. Can I use condition (CARD_NUMBER=tb3.cardno) inside openquery? Below is my query:
update openquery(link,'select FORACID,CARD_TYPE,CARD_NUMBER
from sibatmbank
…
venu
- 53
- 1
- 1
- 6
2
votes
3 answers
Inserting results from a very large OPENQUERY without distributed transactions
I am trying to insert rows into a Microsoft SQL Server 2014 table from a query that hits a linked Oracle 11g server. I have read only access rights on the linked server. I have traditionally used OPENQUERY to to do something like the…
sglantz
- 2,063
- 4
- 20
- 30
2
votes
1 answer
insert with 2 linked servers
I'm connected in sql server 2012, so I connect to sql server 2005 so after that I can connect to a firebird base.
my query is like this for 2 linked server:
INSERT OPENQUERY(2005server, 'SELECT TEST_ID, AGE FROM OPENQUERY(firebirdServer, ''SELECT…
Deiwys
- 243
- 1
- 5
- 15
2
votes
1 answer
How to optimize simple linked server select query?
I have a table called Table with columns:
ID (int, primary key, clustered, unique index)
TEXT (varchar 15)
on a MSSQL linked server called LS. Linked server is on the same server computer. And:
When I call:
SELECT ID, TEXT FROM OPENQUERY(LS,…
Tom Smykowski
- 25,487
- 54
- 159
- 236
2
votes
2 answers
Openquery insert not working
I have a linked MySQL -server to MSSQL-server and I am trying to INSERT data to the table admin_user on the MySQL -server, but end up getting the error:
Cannot process the object "dbo.admin_user". The OLE DB provider
"MSDASQL" for linked server…
user1054844
- 922
- 5
- 17
- 34
2
votes
1 answer
How do you use OPENQUERY within sp_executesql?
I'm trying to verify a job number exists on a linked server and get back a variable (@JobExists) indicating whether it does or not (1 for yes, 0 for no).
To do this I'm trying to use OPENQUERY along with sp_executesql as I have to pass in a…
sr28
- 4,728
- 5
- 36
- 67
2
votes
1 answer
SQL remote function in VIEW with VARIABLE
I am trying to access a remote function from a SELECT clause (lets call it view clause).
The problem is I can't use OPENQUERY and do a JOIN because I need to send a value from the view clause to the remote function. OPENQUERY doesn't allow…
AdamG
- 55
- 5
2
votes
2 answers
Why does OPENQUERY not accept variables?
I am well aware that MS SQL Server does not allow variables to be used in the OPENQUERY statement and I'm aware of the workarounds.
What I'd like to know is, does anyone know WHY variables or concatenated strings are not allowed?
The hoops this…
Andrew
- 2,605
- 3
- 23
- 34
2
votes
2 answers
Error trying to Select x rows from DB2 (V4R5M0) via sql server linked server using OPENQUERY
I have a Linked Server from SQL Server 2008 R2, to a DB2 Database (V4R5M0) using OLE DB provider "IBMDA400"
Linked Server Detials
EXEC master.dbo.sp_addlinkedserver
@server = N'JTEST', @srvproduct=N'IBM OLE DB Provider for DB2',
…
TomEaton
- 121
- 2
- 12
2
votes
1 answer
Using OPENQUERY with a table created on the fly
I have a four-part linked server query that LEFT OUTER JOINs a table created on the fly using UNION ALL, something along these lines:
SELECT t.column1, rlt.s6, SUM(t.column3) column3
FROM Linked_server.database.schema.table t
LEFT OUTER JOIN (
…
Wade
- 418
- 4
- 9
2
votes
3 answers
Entity Framework v4 - Simple Stored Procedure Select Statement from Linked Server (Open Query) Returning -1
In SQL Server, I have created a stored procedure which fires a simple select statement (with SET NOCOUNT OFF) using Open Query to query data from a linked server. When querying this data in SQL Server, the correct results are brought back,…
user1097734
- 163
- 1
- 3
- 10
2
votes
2 answers
Stored Procedure / SQL variable inside OPENQUERY string
I'm having trouble using OPENQUERY (to fetch data from a linked server) within a stored procedure. I've been reading up on SQL variables but can't seem to get it working so maybe I'm misunderstanding variables/strings in SQL. It's throwing the error…
valoukh
- 541
- 1
- 8
- 19
1
vote
1 answer
T-SQL Indexing Service SQL openquery optimization
Scenario:
I am using a T-SQL stored proc (Sql Server Management Studio) to return search matches for text documents using the MS Indexing Service and this (simplified) query:
SELECT *
FROM openquery(
filesystem2,
'SELECT
Path, Rank,…
dda
- 309
- 1
- 3
- 15