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
TSQL Openquery not creating temp table
I have the following code:
declare @cQuery varchar(2000);
declare @cTSQL varchar(2000);
declare @cLinkServer varchar(20);
declare @cTable varchar(20);
set @cQuery = 'SELECT iln.product_nbr FROM axs.pub.ivc_line iln';
set…
CuriousOne
- 51
- 10
0
votes
1 answer
How to ensure specific WHERE condition evaluates first on view
Consider the following view:
create view x as
select 1 as [source],* from some_table
union all
select 2,* from some_other_table
When I run
select * from x where source=1
can I be sure that the select 2.... query is not even executed?
The reason is…
George Menoutis
- 6,894
- 3
- 19
- 43
0
votes
1 answer
OpenQuery inside a stored procedure
I have an OpenQuery that works only outside the stored procedure.
This works:
SELECT *
FROM OpenQuery(LINKED_OLAP,
'SELECT [Measures].[Sales] on columns FROM [Volumes] WHERE ([Calendar].[Day].&[' + convert(nvarchar(4),@Date,120) +…
MrM1k4d0
- 55
- 1
- 10
0
votes
2 answers
sql server - Openquery vs 4part name
A view that references a remote server
4part name ([ServerName], [DatabaseName], [Owner], [Object Name]
OpenQuery
Which is better performance?
Why is performance good?
DBaaa
- 29
- 4
0
votes
1 answer
SQL Server OPENQUERY running against master database
I have the following statement
SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT * FROM Table')
Which is failing with the error
OLEDB provider "SQLNCLI10" for linked server "MyLinkedServer" returned
messages "Deffered prepare could not be…
SE1986
- 2,534
- 1
- 10
- 29
0
votes
0 answers
OPENQUERY "Deferred prepare could not be completed."
Im trying to run OPENQUERY on a linked server without much success. I have 2 queries that are using a UNION operator. They run fine seperately and together when running them as a normal query. I am getting the following message when i try and run…
Adam Briers
- 101
- 3
- 14
0
votes
2 answers
Insert into openquery and WHERE statement
I have some SQL (not written by me) and I cannot find the meaning of it.
Statement is similar to:
INSERT INTO OPENQUERY (linkedServerName, "SELECT col1, col2 FROM service WHERE ServiceId < 0")
(col1, col2)
SELECT col1, col2 FROM Service
I get…
Pawel
- 280
- 3
- 10
0
votes
1 answer
OPENQUERY into temp table
I'm trying to use OPENQUERY to get some data from MySQL, but I need to get the results into a temp table to manipulate.
I've tried a couple of examples but getting the same error:
DECLARE @lastid int = (SELECT MAX(remoteid) FROM cdr)
DECLARE @TSQL…
Ben
- 609
- 6
- 21
0
votes
3 answers
OpenQuery Date Selection
I have the below openquery which works fine but now I want to select only transactions >= '2017-07-01' using gl_trans.gl_trans_date but cannot get the correct syntax. The backend database is Informix
I have tried many suggestions via google without…
fgt
- 11
- 1
- 5
0
votes
1 answer
Error converting data type varchar to float (SQL to oracle linked Server)
Sample Data to upload is here
Same data is uploading successfully from another server. What might be the reason behind it ???
Regon
- 392
- 1
- 4
- 17
0
votes
2 answers
Running an Access ImportExport from vbs fails if vbs is executed from Excel vba - any idea?
I have an Excel file (file1) with a macro that has this line:
Shell "wscript " & SFilename, vbNormalFocus
Where "SFilename" is the name of a vbs file.
In the vbs file I have a line:
appAccess.DoCmd.RunSavedImportExport ("ImportMonthlyData")
Above…
LEBoyd
- 151
- 12
0
votes
1 answer
How to round or format openquery result? Round() works as expected under SSAS but disappears under openquery
I am trying to round the output I get from SSAS mdx query.
When I run this query on the analysis server it works as expected.
WITH MEMBER Result AS Round([Measures].[Avg Movement Length Of Stay Days],2)
SELECT NON EMPTY { Result } ON COLUMNS FROM…
eMRe
- 3,097
- 5
- 34
- 51
0
votes
1 answer
Where clause in Openquery
I am trying to get values from an Openquery. Can you please tell me how would I translate this where clause into an Openquery statement?
select *
from openquery(
XYZ,
'select
concat(RTRIM(LTRIM(Digit1)), '',''
…
1moreLearner
- 81
- 10
0
votes
1 answer
SQL Query to get data from various databases
I wrote the below query to pull the data from different databases. I have created two temp tables to pull the data from two different databases and finally a select statement from the original database to join all the tables. My query is getting…
unnikrishnan
- 141
- 2
- 3
- 15
0
votes
1 answer
SQL SP_EXECSQL @VAR to run a "dynamic" OpenQuery
I have an OpenQuery (Used in SQL2005 to run a query against a legacy database). I'm building the string to run so that I'll return the last 6 months of data.
The problem I'm having is with '||' to concat.
This works on the legacy system:
SELECT
…
WernerCD
- 2,137
- 6
- 31
- 51