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
Truncating IBM i table via OpenQuery in SQL
I'm currently trying to delete data held in a table on IBM i, via an openquery using a linked server, with the following command in SSMS;
DELETE FROM OpenQuery(LnkSrv,'SELECT * FROM LibA.Table1')
When running the above, I get an error…
nowYouSeeMe
- 935
- 6
- 11
- 21
2
votes
1 answer
The Basics between deciding to use SSIS vs openquery?
I work with an OLAP sql server environment that relies on TSQL's openquery function for routine data integration from Oracle Data Warehouse.
It was a surprise to me that no other ETL tool was used, but the process works and has been in place for…
Lee Y.
- 119
- 1
- 1
- 5
2
votes
1 answer
Equivalent option like openquery of SQL server in MySQL
Is there similar kind of option like "OpenQuery" used in SQL server in MySQL.
Actually I need to operate the database residing in another server using MYSQL Command.
If there is any option, please let me know.
Thanks in Advance
Phanindra
- 221
- 1
- 3
- 10
2
votes
1 answer
Linked server(s) SELECT UNION results if one or more linked server(s) in SELECT go offline
I'm in the process of creating a view but before doing so I'm testing and working with multiple linked servers returning column values in a select union statement, I use the following simple code:
SELECT * FROM OPENQUERY ([linkedserver1],'SELECT…
Brakkie101
- 149
- 1
- 2
- 12
2
votes
2 answers
DELETE FROM Openquery
I'm doing a project at work regarding linked servers. As we need to pass the name of the linked server I came up with this approach:
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000);
DECLARE @name nvarchar(10) =…
Hype
- 31
- 1
- 1
- 5
2
votes
1 answer
Unable to return a value from a linked database within stored procedure
I'm trying to write a store procedure (or function if easier) to query a linked database and return a value but massively struggling. Any help greatly appreciated...
What I have tried:
CREATE Proc dbo.Sample (@FolderID as VARCHAR(50),
@FolderName…
Tim Rose
- 45
- 5
2
votes
1 answer
SELECT * INTO [newdatabase].[table] FROM [otherdatabase].[table]
I'm trying to figure out how to create a copy of a table from one linked server to another inside Management Studio. I have both linked servers created, and I can query them. However, one is a SQL Server instance and the other is a ODBC connection…
Phil
- 4,029
- 9
- 62
- 107
2
votes
1 answer
Possible to use "INSERT INTO @VARIABLE EXEC() AT LINKED_SERVER" syntax? (SQL Server 2005)
I am trying to execute a query on a linked server, but I need the results locally.
DECLARE @test TABLE
(
greeting CHAR(5)
)
INSERT INTO @test
EXEC('select ''hello'' as greeting')
SELECT * FROM @test
Uses an EXEC() and INSERT INTO but, obviously…
nosirrahcd
- 1,467
- 3
- 18
- 36
2
votes
2 answers
Adding parameters to OPENQUERY SQL through an Excel cell
I have a SQL query which works in Excel but it uses the following code.
SET QUOTED_IDENTIFIER OFF
SELECT *
FROM OPENQUERY (
INSQL,
"SELECT DateTime = convert(nvarchar, DateTime, 21), [TagName]
FROM WideHistory
WHERE…
Bud.HA
- 31
- 1
- 2
2
votes
1 answer
Linked Servers and local tables join
I'm having a problem joining local tables and linked server tables. I can do it just by using inner joins, but it's taking me too long to execute the query.
I know there's a way to do it with OPENQUERY, but I can't get it.
Here's what I was doing in…
João Amaro
- 454
- 2
- 10
- 23
2
votes
2 answers
How to overcome "Failure getting record lock on a record from table"?
I am running a query using OpenQuery and getting a peculiar error.
This is my query:
select * from OpenQuery("CAPITAOC",'SELECT per.*
FROM pub."re-tenancy" AS t
INNER JOIN pub."re-tncy-person" AS per
ON t."tncy-sys-ref" =…
Steve Staple
- 2,983
- 9
- 38
- 73
2
votes
1 answer
Getting syntax error when trying to use OPENQUERY
I am trying to do a query via ODBC to our ERP database. The documentation guide suggests that we use OPENQUERY to send the query.
Here is my example query
SELECT
Q.Part_No,
Q.[Description],
Q.Part_Type
FROM OPENQUERY
(
LINKEDSERVER,
'
SELECT
…
NA Slacker
- 843
- 6
- 12
- 24
2
votes
1 answer
OpenQuery SQL returns different results
I have setup a view in an Oracle database that runs the code:
select *
from Availabilities
This returns the results
Roster | StartDate | EndDate
Avail | 18/5/16 | 18/5/16
Train | 19/5/16 | 19/5/16
Avail | 20/5/16 | 30/5/16
these results…
JamesW
- 21
- 1
2
votes
0 answers
Correct query syntax for SQL DELETE via OPENQUERY to linked server
I have a Linked Server I am attempting to access through vbscript, to perform a DELETE command. The current syntax for my query string is:
DBQUERY = "SELECT * FROM OPENQUERY(" & strLinkedServer & ",
'DELETE FROM pacts.acct_posting where…
CJDownUnder
- 71
- 7
2
votes
1 answer
SQL Server datetime2 in OPENQUERY
We're migrating form SQL Server 2005 to 2014 for a pretty large environment. And we've noticed that OPENQUERY behaves differently when interacting with MySQL database when it comes to datetime. Previously, it would translate just fine to DATETIME…
Sergey
- 1,181
- 7
- 18