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
0 answers
Call SQL linked sever function or stored procedure from select statement
I have a stored procedure which calls a lookup function as part of the SELECT statement:
-- PX type
CASE WHEN coi.SKU_ID like 'PX%' THEN
[GG].dbo.PXStockInventoryLookup(coi.SKU_ID)
ELSE
0
END
In the production environment, the GG database…
markpsmith
- 4,860
- 2
- 33
- 62
0
votes
1 answer
Adding a parameter to a pass through query
I am trying to use parameters within a pass through query the issue is when I set the statement up I get
Incorrect syntax near '+'. Expecting ')'
I've done this before so I am not sure what I am doing incorrectly now. I've shortened the query…
MrNeedsaHand
- 45
- 7
0
votes
1 answer
Making update to AS400 from SQL 2000
I'm trying to update something in AS400 from sql server 2000 through openquery like following.
UPDATE
OPENQUERY(odbcname, 'SELECT * FROM "libname"."filename" WITH NC')
SET NBFLAG01=1
WHERE NBFLAG01 <> 1
Here's the error I'm getting.
"OLE DB…
chulhee
0
votes
1 answer
TSQL Openquery with Text Field hangs up MS SqlServer
This happens when I query a text field using linked server. For example: select * from openquery(LS,'select text_field from table')
then my server blows up! HELP?
0
votes
1 answer
Multipart identifier could not be bound with cross apply to linked server function with OPENQUERY
I am trying to make a remote call to a function over a linked server which is not allowed directly ([linkedserver].[database].[dbo].fnGet()] so I'm having to use OPENQUERY to perform that function call. This works great when I hard code in a value…
Blair Holmes
- 1,521
- 2
- 22
- 35
0
votes
1 answer
SQL OpenQuery - Escaping Quotes
Been trying for some hours to convert this to a query I can use with OPENQUERY in SQL Server 2014 (to use with Progress OpenEdge 10.2B via ODBC). Can't seem to get the escaping of the quote right. Can anyone offer some assistance? Is there a tool to…
mark1234
- 1,110
- 2
- 24
- 41
0
votes
0 answers
join the local DB inside a pass-through query(openquery) on a linked server
I am querying a linked Oracle server from a local SQL Server instance using openquery. The issue is that I want to limit the results returned based on a table in the local DB (about 10k rows) while executing in the linked instance. Normally I would…
JWheeler
- 320
- 4
- 14
0
votes
1 answer
Limit Return of SQL Query against Active Direct groups to specific Organisation Unit
I have a query to get all the groups that an AD person is in; but I have not been able to limit the results to only chosen Organisation Units, which I would like to do. Here is the query I have:
-- Test Variable --
DECLARE @Username NVARCHAR(254) =…
High Plains Grifter
- 1,357
- 1
- 12
- 36
0
votes
1 answer
Assign dynamic Openquery to a variable in SQL Server
I have a stored procedure with the following dynamic query
ALTER PROCEDURE [dbo].[Del_AE_Update]
@uid nvarchar(MAX)
AS
BEGIN
Declare @AEname varchar(MAX);
Declare @sqlquery varchar(MAX);
Set @sqlquery = ('Select name FROM…
Urwish Patel
- 11
- 8
0
votes
0 answers
Filtering Date Range by business hours
I have a date range:
SET @declared_start_datetime = '11/02/2015 07:00:00'
SET @declared_end_datetime = '11/09/2015 18:00:00'
This was working until I realized it was pulling all records from 7:00 am on 2nd of November until 6:00 pm on November…
James Wilson
- 5,074
- 16
- 63
- 122
0
votes
0 answers
Intermittent OLE DB error from MSDASQL for OPENQUERY call to MySql
I have a SQL statement running in SQL Server 2012 that sometimes works fine (returns a little over 4,000 rows in about 25 seconds) and sometimes fails after about 9 1/2 minutes with the error:
The OLE DB provider "MSDASQL" for linked server…
Mark Freeman
- 1,155
- 3
- 24
- 42
0
votes
1 answer
OpenQuery Not Working in C#
I can successfully connect to a remote server using an odbc connection in C#.
public void checkGradedSerials()
{
List gradedHides = new List();
string queryString = "SELECT COUNT(DISTINCT Serial_No)…
Danrex
- 1,657
- 4
- 31
- 44
0
votes
2 answers
Linked Server Query Runs But Doesn't Finish?
June 29, 2010 - I had an un-committed action from a previous delete statement. I committed the action and I got another error about conflicting primary id's. I can fix that. So morale of the story, commit your actions.
Original Question -
I'm trying…
Clint Davis
- 451
- 1
- 12
- 29
0
votes
1 answer
OpenQuery from SQL Server 2008R2 to Oracle 11g Slow but same query run on the Oracle server is fast
Code:
WITH GTransNums
AS (
SELECT /*+ INDEX (GTRANS_DEFS_24) */ gtrans_num
FROM pro.gtrans_defs
INNER JOIN pro.loc_defs ON (
loc_defs.loc_num = gtrans_defs.gdest_num
AND loc_defs.loc_type = '' JLP ''
)
WHERE gdest_num !=…
Danny McGreevy
- 121
- 7
0
votes
0 answers
Time outs using ODBC but not Openquery to a linked server
I am connecting to an external database via a linked server. I am using a multithreaded console program in C# to break up this SELECT statement.
SELECT *
FROM (
SELECT
C.Part_Key,
C.Part_Operation_Key,
C.Net_Weight,
…
Danrex
- 1,657
- 4
- 31
- 44