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
1
vote
1 answer
Converted data with NUMBER in Oracle to NUMERIC in SQL Server with openquery
I have a linked server to Oracle database in SQL server and retrieve data to local SQL server database every day by scheduling, the problem is: one of the Oracle database column has holding number with 18 fixed digits which type is NUMBER(18) and…
Hassan Hosseini
- 410
- 1
- 4
- 20
1
vote
1 answer
Dynamic OPENQUERY with DATETIME criteria
Can someone please explain to me what is wrong with the below expression. I believe that's I'm converting my date correctly using CONVERT 126 and that my quotes are escaped correctly using char 39 but I could be wrong.
T-SQL:
DECLARE @end2 DATETIME2…
Guitoux
- 23
- 4
1
vote
0 answers
Parametrized OPENQUERY on Firebird database token unknown error message
I've been at this for a little longer then I'd like to admit.
From a SQL Server database, I'm trying to run a dynamic OPENQUERY on a Firebird database, but I'm getting in all kinds of trouble with the datetime condition. Now, I'm trying to use a…
Guitoux
- 23
- 4
1
vote
1 answer
Open query in hibernate
I am using struts and hibernate in my problem. I tried the following query
String hql ="insert into "+
"OPENQUERY(OracleLinkedServer, \'SELECT * FROM report_access_log\') "+
"(CALLINGHOST, ACCESSTIMESTAMP, HTTPREQUESTMETHOD,…
rozar
- 1,058
- 3
- 15
- 28
1
vote
0 answers
SQL OPENQUERY does not work for Provider = INSQL
INSQL is linked server that uses INSQL provider (from Wonderware Historian)
Query SELECT * FROM OPENQUERY(INSQL,' SELECT 1') gives error as below:
Msg 7399, Level 16, State 1, Line 27
The OLE DB provider "INSQL" for linked server "INSQL" reported an…
Michał Krasuski
- 11
- 3
1
vote
1 answer
Insert data into a table from Open Query with variable
I am trying to using OPENQUERY to pull some data into a table. Here's what my code looks like:
DECLARE @TSQL VARCHAR(MAX)
DECLARE @CD VARCHAR(10) = 'XX'
DECLARE @OracleData TABLE (Cd VARCHAR(20), ApptDATE Datetime )
INSERT INTO…
Shrimp2022
- 51
- 8
1
vote
1 answer
Return variable using OPENQUERY
I am trying to make a simple function that reads a table from an ORACLE database and returns a sequence number. I would either like to return it directly or store the value inside of @cwpSeq and return that to the calling program.
Right now I am…
esjones
- 13
- 3
1
vote
1 answer
Unable to update linked MySQL table from SQL Server with MySQL Connector/ODBC 8.0
I'm using a linked server on a SQL 2016 server to read and write data in different MySQL tables.
With MySQL Connector/ODBC 5.3 everything works fine, after updating the MySQL Connector/ODBC to latest version 8.0.26 due to security reasons updating…
ehoefler
- 21
- 3
1
vote
1 answer
Calling parameterized stored procedure in OpenQuery - SQL Server 2008
I am calling a stored procedure in OpenQuery and passing parameters to the stored procedure.
declare @Src nvarchar(max),@Tgt nvarchar(max)
declare @sql_str nvarchar (4000)
set @Src = '$\VMT\Versions\0900\AMS\'
set @Tgt =…
Anirudh
- 21
- 1
- 3
1
vote
1 answer
How to Use OpenQuery to do Create Alias (IBM DB2) in SQL Server
I use linked server to connect AS400 DB2.
For example: select query can work
select *
from openquery([DB2], 'select t1.* from lib.table01 t1
fetch first 1 rows only')
But I want to use query
Create Alias Library.T_temp For…
White Gallime
- 11
- 2
1
vote
1 answer
Passing variables into Openquery and SQL Injection
I have two databases (A and B), both SQL Server, on different servers. These databases are connected with a linked server.
I have to be able to insert rows with distinct values into a table in database B using a stored procedure on database A. This…
Iridium
- 31
- 1
- 8
1
vote
0 answers
Using ENUM datatype with MySQL ODBC Connector in SSMS with Linked Server
I've been using the connector for a few years now to push and pull data between a MS SQL Server database and a couple different MySQL databases. I set up a Linked Server, then using OPENQUERY I create the views I need and I write my selects,…
jbz
- 163
- 7
1
vote
0 answers
Not able to enable xp_cmdshell via Openquery() in MSSQL
I have DB Links access from Instance 1 -> Instance 2.
I can enable/disable xp_cmdshell in Instance 1 by directly executing the SQL query. But when I try with Openquery() I cannot enable it. I have sysadmin privilege in both Instances.
I got no…
melvinjose
- 385
- 1
- 3
- 9
1
vote
1 answer
Dynamically Executing Stored Proc with output variables
I have a script that calls a stored procedure on a different SQL Server,using a Linked Server reference and an OPENQUERY() statement. For demonstration, I have made the following procedure, on the remote server:
-- ON SQL_SVR Remote Server
CREATE…
High Plains Grifter
- 1,357
- 1
- 12
- 36
1
vote
2 answers
Table-Based Function using variables and OpenQuery
I am attempting to query data from our Oracle server via our SQL server. To perform this in a thin-client manner, I am using OpenQuery. I would like to build a single table-based function that can be used to query various linked tables as needed. I…
Riser Of Evil
- 19
- 2