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
2 answers
PROGRESS DB extract string between semi colons - INSTR Function error
I'm querying a Progress DB via an OpenQuery in MS SQL Server.
I have a field (addr) that contains a string value in the below format:
text123; text 456; text 789; text 1011
I need to extract each value before the semi colon ; for separate…
Michael
- 2,507
- 8
- 35
- 71
1
vote
0 answers
Send parameter in SQL OpenQuery with Recursive CTE
I have the following Recursive CTE which is used in an OpenQuery function in SQL
I use OpenQuery to query a SQL Linked Server.
The request does not work because the values from rows Line not transfer .
How to transfer values from column Line in…
Joh Travell
- 71
- 3
1
vote
1 answer
Conversion failure using OPENQUERY from OLAP
I'm trying to retrieve some simple data from an SSAS cube using OPENQUERY in with an MDX string in SQL.
Cannot get the data of the row from the OLE DB provider "MSOLAP" for
linked server "OLAP_LIVE". Conversion failed because the data value
…
Kai
- 2,050
- 8
- 28
- 46
1
vote
0 answers
Partial data returned by OpenQuery in SQL
I have a Link server exposed on SQL server of a Oracle Database and I am using the following query to retrieve data. MsgCol is of type LONG in oracle. The query is always returning 100 Character but the original data is way bigger than that. …
Tapas
- 21
- 3
1
vote
0 answers
run an open query to an API using SQL Server or SSIS
I am trying to run an OPENQUERY to get data from a table connected with in a API. I am trying to do this via SQL server and having no joy.
I have looked at SSIS to query the data, but i don't know how to connect to an API XML server as my knowledge…
Joeysonic
- 255
- 3
- 11
1
vote
1 answer
How can I update a 4D record from SQL Server, over ODBC?
I've got a 4D database running in 4D Server, with 4D Sql Server running. On another Windows machine, I've got an an ODBC data source set up for it. On that 2nd machine, in SQL Server Management Studio, I've created a Linked Server using that ODBC…
David Chase
- 31
- 4
1
vote
0 answers
SQL Server : issue to concat parameter in a query with double openquery
I edit a query which contains a double openquery. But in this query, I need to add a parameter. Because I never worked on SQL Server, I don't know how add this parameter to my query.
The query look like :
SELECT *
FROM openquery([1STCONNECTOR],'
…
Saul
- 11
- 4
1
vote
1 answer
SQL OPENQUERY, Incorrect syntax near '+'
I have this sql query:
DECLARE @VAR varchar(8)
SET @VAR = '20140202'
SELECT * FROM OPENQUERY([NMD],'SELECT * FROM NmdItemGroups01.dbo.InvTranDtl WHERE TranDate = ''' + @VAR + '''')
But when I run it, it gives this error:
Incorrect syntax near…
amin mohammadi
- 901
- 2
- 15
- 33
1
vote
1 answer
Openquery convert yyyymmdd to MM/DD/YYYY
I have tried
FROM OPENQUERY(TESTsvr,
Select to_date(CREATE_DT, "MM/DD/YYYY") as Testing
From Test.ViewTest
and
FROM OPENQUERY(TESTsvr,
to_char(CREATE_DT, "mm/dd/yyyy hh24:mi:ss") as Testing
From Test.ViewTest
but when I execute it says invalid…
jrussin
- 79
- 1
- 7
1
vote
1 answer
ssms to oracle openquery DATEADD
New to using Openquery and trying to pull data from current month and last month.
typically I would just use DATEADD for getting specific dates but OpenQuery doesn't seem to like it. Any tips on how to pull from current month and previous…
jrussin
- 79
- 1
- 7
1
vote
2 answers
How to update a SQL Server table with updated Oracle table Data
I am using SQL Server (and Oracle as a linked Server).
The task involves updating the SQL Server table with any new changes to the column NAME in the Oracle TABLE.
EMP is PK and NAME is the column that can change.
ORACLE TABLE = TABLE1:
EMP …
pthfndr2007
- 93
- 1
- 10
1
vote
1 answer
Conversion failed when converting date and/or time from character string - dynamic SQL and openquery
Code below is our stored procedure that is trying to populate log data into our data warehouse. For the life of me I can't figure out why I'm getting this conversion error. All timestamp columns are datetime. I've tried a myriad of different…
Jeff Lohr
- 83
- 9
1
vote
1 answer
sql server linked oracle how to execute query with dataset
In sql server I have a table with id. I pass id through openquery in a loop.
while @coun < 1000
begin
select @id=id from #temp where num = @coun
@sql = 'select * from tableoracle where id=' + @id
execute('insert into #temp2 select * from…
Gennady Ktn
- 11
- 7
1
vote
2 answers
MS SQL/OPENQUERY call to Informix -- too many single quotes? too few?
I am trying to communicate with an external Informix datasource from an MS SQL 2016 database instance to issue a date bound query. To do this, I am doing the following:
--enter code here
declare @date_string varchar(10)
set @date_string =…
plditallo
- 701
- 2
- 13
- 31
1
vote
1 answer
Date Parameters Open Query SQL - ERROR: Unclosed quotation mark after the character string
Could someone look at my code below and let me know where I am going wrong. Trying to pass date parameters through Open Query & getting error - Unclosed quotation mark after the character string
--@PID varchar(11),
@START datetime,
@END…
Claire
- 51
- 6