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
2 answers
SQL OpenQuery variable tablename
I need to transfer data from a linked server to our main SQL server. The issue is, that the table name changes everyday.
I have looked around this site to find out if it is even possible to have a variable database name, which it is, but also to see…
Mikkel Paulsen
- 75
- 8
0
votes
4 answers
Using the results of a query in OPENQUERY
I have a SQL Server 2005 database that is linked to an Oracle database. What I want to do is run a query to pull some ID numbers out of it, then find out which ones are in Oracle.
So I want to take the results of this query:
SELECT pidm
FROM…
Jason Baker
- 192,085
- 135
- 376
- 510
0
votes
2 answers
OpenQuery SQL - What's wrong with my single quotes?
When I exec this
SET @Qry =
'SELECT [LMITNO] INTO ##TABLETEMP' + LTRIM(RTRIM(STR(@@SPID))) + '
FROM OPENQUERY(MAA,''SELECT DISTINCT [LMITNO] FROM MAA.ESMEL3.MVXDDTPRD.MILOMA
WHERE [LMITNO] LIKE ' + '''' + @ChemicalPreFix + ''''…
Jack Reilly
- 158
- 2
- 14
0
votes
1 answer
LDAP OpenQuery returns only one column ADsPath
I have added my Domain Controller as a linked server to SQL Server in order to pull some data from active directory and populate my tables with it. After that, I have executed an OpenQuery to get the users list in my domain
SELECT * FROM…
Saleh Omar
- 739
- 2
- 8
- 29
0
votes
1 answer
MSSQL linked server to mysql instance collation characters issue
I am using an Mssql instance and trying to make an INSERT to a LINKED SERVER with openquery but having troubles with chinese characters.
My linked server connects to a MySQL instance utf8_general_ci and to build it I use a MySQL odbc 5.3 unicode …
DBAA
- 1
- 1
0
votes
0 answers
Open query - incorrect syntax next to passed in variable
I'm running this query
SET @Qry = ' SELECT [MLSTQT] INTO ##DISPENSED_LOCATION'
+' FROM OPENQUERY(MA,''SELECT MLSTQT FROM ES3.MD.MOC WHERE MLWHSL='''+ @ProcessOrder+''' '')'
EXEC (@Qry)
And I'm getting an error…
Jack Reilly
- 158
- 2
- 14
0
votes
1 answer
OpenQuery in Power BI giving me back an error
I am new to Stored procedures & OpenQuery in Power BI.
I am trying to connect to my SQL Server database by passing an SQL statement:
SELECT * FROM OPENQUERY ([DVT-FUSION],
'EXEC CRM_DVT.dbo.SP_RPT_Billing_Summary ''R1'', ''R2'', ''0'', ''0'' …
Rush
- 83
- 3
- 3
- 10
0
votes
2 answers
OpenQuery Update against MariaDB returning 'Table doesn't exist'
I have SQL Server 2008 SP4 instance accessing a linked server that is running a MediaWiki database on MariaDB (v 5.5.44). I am able to select from the table no problem:
SELECT *
FROM OPENQUERY(MEDIAWIKI,
'SELECT * FROM wiki.page WHERE page_title =…
chazbot7
- 598
- 3
- 12
- 34
0
votes
1 answer
Trim results from DB2 query
I have a stored procedure that queries a linked DB2 database table.
The query is one big string, though, like so:
SET @sql = 'SELECT * FROM openquery(TRAP_DB2, ''SELECT...'')'
exec sp_executesql @sql
The problem is that the results return a bunch…
blacksaibot
- 265
- 2
- 12
0
votes
1 answer
What is the syntax for date functions in openquery - OleDB through SQL Server
I'm trying to dynamically pass a date function in an openquery on SQL Server. My SQL Server is connected to and OleDB Oracle database.
When I run my query using an Oracle based date function, my query runs, but returns no data:
SELECT *
FROM…
Bobby Jobsite
- 1
- 1
- 2
0
votes
1 answer
Execute stored proedure in openquery with parameters
I have a database in SQL Server 2014 and I have a linked server.
i want to run a sp which runs inside her a stored procedure
which belongs to the linked server.
For example I've got the stored proedure :
PROCEDURE test (@myint int)
AS
…
Dimitris
- 133
- 2
- 13
0
votes
0 answers
Issue with select where in openquery
I have an openquery that I using to hit an Oracle DB with from SQL server. The query runs without error but does not return any results. If I take out the sub select and change it to where pid = and input any of the ids on the list I get results.…
spacerobot
- 265
- 1
- 5
- 23
0
votes
1 answer
How to insert value into DB2 from SQL Server using openquery which are not already inserted?
Is there any option to use not exists in openquery from SQL Server and prevent insert of values which already exist in DB2, from table.
I want to insert data from a table located in SQL Server into a table located in an IBM DB2 server, but I want to…
Puskarkc007
- 174
- 2
- 14
0
votes
2 answers
How can I use a variable inside OPENQUERY
I am getting an error running the code below. I believe it as to do with the number of single quotes used.
How can I set a variable inside OPENQUERY command?
@declare @myStr varchar(6)
set @myStr = 'Test1'
SELECT *
FROM…
Elio Fernandes
- 1,335
- 2
- 14
- 29
0
votes
2 answers
SQL Job error with OPENQUERY
I am trying to run a SQL job with this TSQL script, and the following error is raised. The code runs well in a query window!
What is the problem with this code in the sql job?
SELECT *
FROM OPENQUERY("192.168.1.1",'SET FMTONLY OFF; EXEC…
Elio Fernandes
- 1,335
- 2
- 14
- 29