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
Determine the columns of a dynamic stored procedure. SQL Server
I have the problem that I need to know the columns of any stored procedure that passes as a parameter inside another. But in any case I do not get any results.
ALTER PROCEDURE getColumnsTable
(@tableColumnsNames nvarchar(45))
AS
BEGIN
…
Jorge Abraham
- 21
- 4
1
vote
1 answer
linked server OLE DB provider "SQLNCLI11" for linked server
Hello I have 2 different SQL Server databases.
They are connected to each other via a linked server. The linked server is working fine.
When i try:
SELECT
*
FROM
OPENQUERY
([192.168.1.170],
'SELECT…
user7139872
1
vote
1 answer
How to join linked server tables with openquery
I want to join tables from two different linked servers. I am not sure how to do that.
First query:
SELECT * FROM OPENQUERY(PANTS,'
SELECT
LONG
,SHORT
FROM JEANS
Second query:
SELECT * FROM OPENQUERY(SHIRTS,'
SELECT
WHITE
,BLACK
FROM…
Linda
- 31
- 1
- 4
1
vote
0 answers
MS SQL - How to have dynamic query of OPENQUERY in sql view
I have to create a view querying AD group using OpenQuery. I have gone through this question but this doesn't work in case of an SQL view since we can't have local variables in a view.
Please see my below view query:
SELECT
…
NayeemKhan
- 1,210
- 7
- 19
- 38
1
vote
1 answer
NativeQuery with OpenQuery and Parameters
I've been triyng to create a query using a dblink with some parameters.
I'm using Hibernate 4 and the database is a MSSQL.
But I either get:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
if my query is…
Batousay
- 21
- 4
1
vote
1 answer
Execute a literal string as a SQL statement - placement of single quotes
I have the following SQL string pulls data from a ODBC Progress Database via a linked server. This query works fine.
SELECT *
INTO [TABLE]
FROM
OPENQUERY
(
"ODBC DRIVER",
'SELECT CAST(SCHEMA."AB_ACCESSIBILITY"."ACCESS_CODE" AS VARCHAR(20)) AS…
user27768
- 13
- 5
1
vote
2 answers
SQL JOIN with OPENQUERY
How do I correctly join a query with a open query?
Here is a how my query is layed out right now. The query that is part of the OPENQUERY works by itself.
Select d.* from db.dbo.table d
left join (select * from OPENQUERY(otherSource,'
--working…
ejj
- 172
- 2
- 3
- 18
1
vote
0 answers
How to use SUBSTRING in OpenQuery - SQL Server
I have written an "open query" to fetch data from Proactive/Oracle DB
SELECT * FROM OPENQUERY (METER,
'SELECT
ME.Col1
,ME."ext" AS ext
,ME."t-date" AS Date
,ME."per-id" …
AskMe
- 2,495
- 8
- 49
- 102
1
vote
1 answer
TSQL openquery with different result sets
all.
I am using openquery to call remote stored procedure at the linked server.
If the procedure's work was successed - the result set contains a,b,c columns.
If there was inner error, it returns only error column in result set.
As I know, to select…
Yuriy D
- 95
- 1
- 9
1
vote
1 answer
Why do we need to use OPENQUERY to pull data from Sybase to SQL Server?
I have data on a Sybase server and want to get the data to my SQL Server instance. I have a linked server defined to the Sybase query and when I try to use the 4 part naming convention to access the data, I get the following error:
Msg 7356, Level…
RobertoGuzman
- 75
- 8
1
vote
1 answer
OPENQUERY(SERVERNAME, STOREDPROCEDURE) Syntax error
This is my code
DECLARE @stringvariable nvarchar(200) = 'Hello';
DECLARE @sql nvarchar(2000) = SELECT * INTO ##global FROM OPENQUERY(DB1, ''EXEC GETCASE ''' + @stringvariable + ''''')'
Printing @sql returns a correctly formatted query, however SQL…
Govind Rai
- 14,406
- 9
- 72
- 83
1
vote
2 answers
OPENQUERY update on linked server
I want to execute the following statement through from a linked server (openquery):
UPDATE SAP_PLANT
SET (OWNER, OWNER_COUNTRY) = (SELECT import.AFNAME, import.COUNTRY
FROM SAP_IMPORT_CUSTOMERS import, SAP_PLANT…
Werner
- 35
- 3
- 9
1
vote
1 answer
SQL SERVER ERROR: "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value." (informix to sql server)
I have a sql server linked server to access an informix database, and I'm using openquery to run the queries, and then insert the results in a sql server table.
The problem is that when I run a code like:
INSERT INTO table_name
( date, comments,…
Smaily Carrilho
- 131
- 2
- 12
1
vote
0 answers
Open query - Oracle with clause
It is possible to use SQL Server's Open Query, with Oracle With AS clause, in query?
SELECT * FROM OPENQUERY(OMSP,
'
WITH OrderReserve AS
(SELECT cvwarehouseid,
lproductid,
SUM(lqty) lqty
FROM iOrdPrdQtyDate
GROUP BY cvwarehouseid,
…
Kulis
- 988
- 3
- 11
- 25
1
vote
0 answers
OPENQUERY inside function
I need to create a function that executes a query to a linked server through openquery, returning a scalar value.
My problem is that I cannot use a static query because one of the function parameters identifies the field that must be returned from…
gipinani
- 14,038
- 12
- 56
- 85