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
Openquery statement in SQL Server
I am fairly new to SQL, and I am hoping someone can help me with a problem I'm having. I haven't been able to find any answers helping me figure out this exact problem.
I have two tables in two SQL Server databases on two different servers that I…
lunderkl041
- 3
- 1
- 3
0
votes
1 answer
Error while Executing OPENQUERY with Oracle 10g in SQL Server 2008 R2
Linked Server Name:CBSLINKED //Oracle 10g
Name of Database if oracle : CBSIVRDB
Name of Stored Procedure : FIU_INSERT
having parameter as (FromDate,ToDate)
My DB: SQL server R2
execute ('call CBSIVRDB.FIU_INSERT (''
…
Dgan
- 10,077
- 1
- 29
- 51
0
votes
1 answer
Linked server dynamic catalog for executing MDX through OpenQuery
I have multiple OLAP databases in my project, so is it possible to dynamically decide the catalog for executing this MDX query?
SELECT * FROM OpenQuery(OLAP_SERVER, 'WITH MEMBER measures.X AS dimensions.count SELECT Measures.X ON 0 FROM MyCube') as…
Manish Surolia
- 85
- 1
- 12
0
votes
1 answer
Stored Procedure to Insert the result of a parameterized OPENQEURY into a local table
So this is "fun", and by "fun" I mean mindbending. I have managed to:
Feed parameters to OPENQUERY and get results like this:
Declare @MyString varchar(max)
, @TheDateAfter datetime
set @TheDateAfter = DATEADD(d, 1, @TheDate)
set …
n8.
- 1,732
- 3
- 16
- 38
0
votes
1 answer
What happens with OPENQUERY when the linked server is down/unavailable?
In a stored procedure on an SQL Server 2008 database, I've added an OPENQUERY in the FROM clause of a SELECT statement, and that in turn contains an OPENQUERY, something like this:
SELECT MyField
FROM OPENQUERY (LinkedServer,
'select…
MarioDS
- 12,895
- 15
- 65
- 121
0
votes
1 answer
Openquery INSERT syntax
I am trying to learn openquery. I have MSSQL server that has a linked server MySQL_testing. Now I have been fighting with the openquery syntax all morning and figured here are probably much smarter people who know how it works.
So what is the…
user1054844
- 922
- 5
- 17
- 34
0
votes
2 answers
Error converting data type nvarchar to datetime with stored procedure
[Using SQL Server 2008 R2 Enterprise x64 SP1]
I am trying to use some form of GETDATE() to pass today's date to a stored procedure inside OPENQUERY(), but I keep getting the error
Msg 8114, Level 16, State 1, Procedure spCalcProjection, Line 0
…
Conrad
- 2,197
- 28
- 53
0
votes
1 answer
UPDATE local MSSQL table from Identical MySQL Linked Server Table Where Clause
I have two tables both named employer. One table resides on a MSSQL the other on a MySQL. The MySQL DB has been Linked.
I'm trying to update all rows in the MSSQL DB table with the data from the MySQL DB where the data is different.
Ideally,…
user3598460
- 3
- 2
0
votes
0 answers
Update Local MS SQL Table from Linked Server (MySQL) Table
I'm trying to update a local MSSQL Table from a Linked Server Table (MySQL).
The tables on both the servers have identical columns.The only difference between these tables is the name of MSSQL Table is employer and the name of MySQL Table is…
user3598460
- 3
- 2
0
votes
1 answer
Openquery with XML data: Incorrect syntax near '<'
I have this code:
DECLARE @xml_var XML
SET @xml_var =
(
SELECT ID_CODE AS '@ProductionId',
CODE AS '@ItemId',
CODE_LEVEL AS '@ItemPackagingLevelId',
COMMISIONING_FLAG AS '@ItemFlag',
TIMESPAN AS '@TimeStamp',
…
VansFannel
- 45,055
- 107
- 359
- 626
0
votes
1 answer
SQL Server: Query not linked server providing credencials. Is possible?
I tried to query another specific server many times, but i failed... I searched and i found out that the server must be linked with the other server in order to achieve what i want. Unfortunately is not in my hands to change that so my question is,…
CodeArtist
- 5,534
- 8
- 40
- 65
0
votes
1 answer
Passing Variable list from one OpenQuery to another OpenQuery
New to asking questions, but have been using Stackoverflow for years.
I have two individually linked servers that do not speak to one another. I need to get a list of variables from one server into SQL and then pass that list of variables into the…
Dacius
- 1
- 1
0
votes
0 answers
SQL server UPDATE OPENQUERY
I am trying to link two servers (the host is SQL Server 2008 and the destination is ORACLE) so that a table on the oracle server is populated by data held in multiple tables on the SQL server.
I have set up an INSERT OPENQUERY, which works perfectly…
ChrisG
- 11
- 5
0
votes
3 answers
linked server with cross join
I have two servers. One is mine and the other is of the other company. In the second server I can´t create any database or add any functions or store procedures, but I need to return information to do cross join with my database.
for example,
select…
0
votes
2 answers
Special statements with Openquery
I would like to transfer data from a MS SQL Server database to a MySQL database. So, I added a linked server to MS SQL so that I can use Openquery to insert the data in the MySQL database. I want to optimize the performance of the data transfer and…
manash
- 6,985
- 12
- 65
- 125