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
0
votes
1 answer
openquery update with temp table
I am trying to update a table using openquery and a temp table. Whenever I execute the query I get the error The multi-part identifier "#tempTable.PERMITEE" could not be bound.
The multi-part identifier "#tempTable.BILLTOPERSON" could not be…
kevorski
- 816
- 1
- 11
- 29
0
votes
1 answer
SSMS: Oracle Error Message: missing expression
I'm getting the following message and I can't find what's wrong.
I'm needing help reviewing my code.
ERROR MSG:
OLE DB provider "OraOLEDB.Oracle" for linked server "S" returned message "ORA-00936: missing expression".
Msg 7321, Level 16, State 2,…
cmpmd2
- 165
- 1
- 2
- 16
0
votes
1 answer
How to create tables in a linked table using OPENQUERY?
I have several tables that I would like to create in a linked server using OPENQUERY.
Basically, I have a query which fills a table (TableA) that is run on the linked server (ServerB). I am working on a way to run this query from a different server…
Mike
- 1,853
- 3
- 45
- 75
0
votes
2 answers
How to make your Server as Linked Server for selected views?
We have a SQL Server 2012 instance which is the database for our security system. Now we have a scenario where we want to allow another team our SQL Server to select certain records, ie we created four views for them for their requirements.
But, we…
aryan
- 49
- 6
0
votes
2 answers
Select from IBM DB2 using Openquery() and member
I want to fetch some data from an IBM DB2 server using SQL Server 2008.
For this I have tried to use:
SELECT * From Openquery(LINKED_SRV,'Select * from dta.Filename')
which gives me data, albeit rather old data.
I have since gathered that the…
Cenderze
- 1,202
- 5
- 33
- 56
0
votes
2 answers
Use a variable in SQL Where clause that contains commas
I have a table that is in SQL server 2008. The table has a field named CC_TERMID and it is a numeric field. I have data sitting on an oracle server in which I would like to find the current TermID's for the date we are in and I would like to use…
rockman20
- 3
- 2
0
votes
2 answers
Passing a parameter into a an Openquery select statement
I've a query:
DECLARE @sql2 nvarchar(max), @counts int, @maxdate date;
SELECT @maxdate=isnull(Max(btestDateResultBack),'01/01/1900')
FROM BloodTests
PRINT @maxdate
SET @sql2 = 'SELECT CONVERT(GROUP_CONCAT(btestDonor) USING utf8) AS DonorIDs, '
…
joeldub
- 57
- 1
- 10
0
votes
1 answer
Find all tables of a linked server using server provider IBMDASQL from SQL Server
I have some linked servers which, when I make the statement,
exec sp_linkedservers
in SQL server shows up as:
SRV_NAME SRV_PROVIDERNAME SRV_CAT
SERVER1 IBMDASQL INICAT1
SERVER2 IBMDASQL …
Cenderze
- 1,202
- 5
- 33
- 56
0
votes
0 answers
OLE DB provider "IBMDASQL" for linked server returned message "SQL0104: Token . was not valid
simple open query insert fails when running from stored procedure but same thing works if i run it manually;
INSERT INTO OPENQUERY([ServerName],
'select LocationCode, Name, Zip from Location.TKT')
VALUES(…
Venki
- 11
- 3
0
votes
1 answer
exec sp_tables_ex returns nothing on a server I can find using exec sp_linkedservers
I'm trying to find data in a database which hasn't been used that much.
I've seen that it is a linked server using:
exec sp_linkedservers
which returns the servername, TheSRV along with for instance its catalog name S100.
I then try to find…
Cenderze
- 1,202
- 5
- 33
- 56
0
votes
1 answer
Delete records in db2 from SQL Server via odbc
I have an ODBC connection to a db2 system from SQL Server. I can use OPENQUERY to select, insert, and delete records from my intended table.
But, I need to routinely delete all records from a table and when trying to delete the entire contents I'm…
Tod Meinke
- 116
- 1
- 1
- 7
0
votes
1 answer
Issues deleting a row using OPENQUERY() with RPC enabled, obeying case sensitivity
I'm using T-SQL and I want to delete a row from a linked server using MYSQL.
For this I've tried:
delete from openquery(MYSQLServer,
'select * from dTable where date = cast(cast(date_add(now(),interval -6 day)as date) as int)
and ProductKey =…
Cenderze
- 1,202
- 5
- 33
- 56
0
votes
0 answers
Using Set inside openquery
Is there any way to declare a variable inside an OPENQUERY statement (querying MySQL db). I only want to declare a variable inside the actual statement. So I don't need to pass a variable into the statement (I know this isn't possible).
Is it…
redstubble
- 121
- 2
- 12
0
votes
0 answers
Error saving decimal value from SQL SERVER to RDB
i use openquery to save values from sql server to RDB through ORACLE RDB DRIVER ,ODBC
insert into OPENQUERY([RDB_ODBC_ACCOUNTING],
'select * from test')
(f1) values( 0.001)
the value saved in rdb= 100
any suggest?
hussein
- 5
- 4
0
votes
1 answer
openquery passing date parameter conversion failed
Created the following stored procedure to create a dataset within SSRS. But when I run the query in SSRS I get the conversion failed error when converting date from character string. I'm sure it's a syntax or variable issue. Any ideas?
ALTER…
Wendi
- 25
- 1
- 8