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
SQL Server OpenQuery to Oracle schema with $
I am trying to connect from SQL Server 2008 R2 into an Oracle Linked Server, but the Oracle Schema has a $ symbol in it. Unfortunately, I can't change the schema name, but it appears to be causing me an issue when trying to extract information…
G-Man
- 184
- 2
- 14
0
votes
1 answer
Access 2010 form with command buttons - OpenQuery and PrintPreviewQuery
I have created an access 2010 form where I have a listbox and two command buttons. Listbox includes all of the query names and command button one is for "query print preview" and the command button 2 is for "opening the query" which should be same…
makalele
- 79
- 1
- 2
- 10
0
votes
1 answer
Tricky issue joining sql table with active directory user on guid
Ok so we have some tables in SQL that identifies users by their Guid from active directory. Originally the dev team wanted to just mirror the user base in sql, but the manager insists that we keep it in active directory. So at any rate, one of the…
Sinaesthetic
- 11,426
- 28
- 107
- 176
0
votes
3 answers
SQL server openquery - "Key column information is insufficient or incorrect. Too many rows were affected by update."
I'm trying to run a delete query from Micrsoft SQL server to clear out a table in a remote mySQL table.
Query is:
delete from OPENQUERY(WEB_DB,'select id,university_name,college_name from table_name');
This is returning the error:
OLE DB provider…
Jona
- 305
- 1
- 4
- 15
0
votes
2 answers
How MySQL Select OpenQuery Not In Statement
Below statement will return values from OpenQuery(MYSQL).
Select * From OpenQuery(MYSQL, 'Select * From ResearchRpt') a
Where Not Exists (Select * From dbo.ResearchRpt where Id_report = a.Id_report)
I want to reversed it where the result values…
SiChiPan
- 73
- 1
- 10
0
votes
2 answers
Insert error using Linked Server MySQL
I have created a linked server using ODBC driver with following provider string
DRIVER={MySQL ODBC 5.1 Driver};SERVER=HOST;USER=uid;PASSWORD=pw;OPTION=3
My linked server is named MYSQL. If i do Select and UPDATE in that way it works right
SELECT *…
Luigi Saggese
- 5,299
- 3
- 43
- 94
-1
votes
1 answer
EXECUTE DYNAMIC OPENQUERY INCLUDE VARIABLE IN FUNCTION INPUT
I want to execute a function through a linked server in SQL Server.
When I execute OPENQUERY, it runs successfully, but when I use execute @SQL, it throws an error.
This code runs successfully:
SELECT A
FROM OPENQUERY([SERVER20], 'SELECT COUNT(9)…
fahime abouhamze
- 328
- 3
- 16
-1
votes
1 answer
Linked server SQL Server to Oracle not returning data
i have a stored procedure in SQL Server like this:
DECLARE @EFEKTIF_DATE VARCHAR(15);
SET @EFEKTIF_DATE = (SELECT REPLACE(CONVERT(NVARCHAR,CAST(GETDATE() AS DATE),106),' ','-') EFEKTIF_DATE)
DECLARE @SQL_P1 VARCHAR(MAX) =
'SELECT …
Didit Praditya
- 1
- 1
- 4
-1
votes
1 answer
SQL Server stored procedure dynamic query (openquery) for a linked SQLite database returns errors
I have SQL Server 2019 and have linked an SQLite database called [PSMDB_WIN_SVR] to it.
My objective is to retrieve data from the linked database for all records greater than or equal to a double variable parameter representing Julian Date/Time. The…
rgarnett
- 37
- 4
-1
votes
1 answer
Unable to convert field data-type
I am reading the contents of a field from a linked server with an openquery statement without any problem. The field's data type is char(1). However if I try to convert to a different data-type, the openquery fails.
Neither of the statements…
Leon
- 1
- 2
-1
votes
1 answer
Including list of params in OPENQUERY
I'm trying to get the values from an external Database server executing the following code:
DECLARE @TSQL VARCHAR(8000), @VAR CHAR(2)
DECLARE @Prefixos VARCHAR(MAX);
WITH cte AS
(
SELECT DISTINCT prefixo
FROM ARH.arh.UorPos
)
SELECT…
jMarcel
- 958
- 5
- 24
- 54
-1
votes
1 answer
T-SQL openquery doesn't update on NULL value
I have a Microsoft SQL Server trigger that updates a remote database with new values when the local database is updated. Everything works fine, and I tested the script and it updates fine, unless there is a null value.
The code is below:
DECLARE…
Iqbal Khan
- 363
- 2
- 6
- 22
-1
votes
2 answers
how to pass date parameter in openquery statement
I have one procedure
Create procedure [dbo].[daily_stats]
@date datetime, @grouping varchar(150)
as
select…
user3456974
- 1
- 1
-2
votes
2 answers
Min Function select 3 columns
I have an sql statement where I want to select the min of a date:
SELECT EEEV_EMPL_ID, MIN(EEEV_DT), prev
But i dont want to include the prev column in the min, but I want that column in the result. So say I have
1 3/5/2018 UB3
1 5/28/2018…
dk96m
- 301
- 3
- 18
-2
votes
1 answer
OpenQuery for SQL Query
Kindly help me writing below query in openquery.Thanks in advance
INSERT INTO Tablename
SELECT * FROM tablename1 WHERE insertionorderid IN (
SELECT orderid FROM temp_table2)
venu
- 53
- 1
- 1
- 6