Stack Overflow
Stack Overflow

Questions tagged [openquery]

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.

421 questions
6
votes
2 answers

How to join linked server table and sql server table while using openquery

I use openquery syntax to read the data from a linked server. SELECT * FROM OPENQUERY(LinkServer, 'SELECT * FROM Product') I want to join this link server table with an Sql server table to get my final results. For now I do it by, having a temp…
Muthukumar
  • 8,679
  • 17
  • 61
  • 86
6
votes
3 answers

Querying an Oracle database from SQL Server

I have an Oracle 11g XE database that I would like to transfer into SQL Server Express 2005. At first I thought I'd just generate the tables in Oracle as SQL, manipulate the data formats, and run the query in SQL Server. This worked for small…
mikimr
  • 311
  • 3
  • 8
  • 18
5
votes
3 answers

Insert into table on the Linked Server with data from local table

I'm working with SQL Server Express, I created a linked server to an Oracle database. As the title indicates, I want to insert data selected from a local table into a table at the Linked Server. I tried many queries but no one of them worked as I…
Meher Jebali
  • 199
  • 2
  • 3
  • 18
5
votes
1 answer

What is the reasoning for using OPENQUERY within a tsql stored procedure?

I am currently reviewing some jobs that run stored procedures on a database. All of these stored procedures are connecting to a linked server(s). I am not too familiar with this functionality. I am at the moment attempting to determine why these…
Bill Blankenship
  • 3,316
  • 6
  • 43
  • 73
5
votes
3 answers

SQL Server OpenQuery() behaving differently then a direct query from TOAD

The following query works efficiently when run directly against Oracle 11 using TOAD (with native Oracle drivers) select ... from ... where ... and srvg_ocd in ( select ocd from rptofc where eff_endt = to_date('12/31/9999','mm/dd/yyyy') …
dbenham
  • 127,446
  • 28
  • 251
  • 390
5
votes
2 answers

How to test linkedserver's connectivity in TSQL

I need to write a procedure to collect data from several remote servers, I use linkedservers and OPENQUERY to gather data from servers, but sometimes I lose connection to some servers or I simply can't connect them (e.g. remote server is offline)-…
dogant
  • 1,376
  • 1
  • 10
  • 23
4
votes
2 answers

Update MySql from MS SQL server

I need help in writing queries which will update MySql tables from SQL server. I have created linked server and select queries work fine but I'm getting errors while doing update. I'm really new to writing such type of queries so please help me…
pramodtech
  • 6,300
  • 18
  • 72
  • 111
4
votes
5 answers

How to make WHERE clause case insensitive: From SQL Server querying Oracle linked server

We have a MS SQL Server 2005 installation that connects to an Oracle database through a linked server connection. Lots of SELECT statements are being performed through a series of OPENQUERY() commands. The WHERE clause in the majority of these…
Peter Bridger
  • 9,123
  • 14
  • 57
  • 89
4
votes
1 answer

SQL Server: OPENQUERY in easy words (with example)

I am pretty new to SQL Server, and I have stumbled upon this right here: BEGIN TRANSACTION loadTTAllocations INSERT INTO @ttaAllocs SELECT company_code AS CompanyId, job_number AS JobNo, ... …
MrMee
  • 163
  • 1
  • 1
  • 7
4
votes
1 answer

How can I use a table variable as a parameter for OPENQUERY

How can I use a table variable as a parameter for OPENQUERY Something Like: DECLARE @TSQL VARCHAR(8000) DECLARE @VAR TABLE (VAR1 VARCHAR (2)) INSERT INTO @VAR values ('CA'),('OR') ,('WA') SELECT @TSQL = 'SELECT * FROM…
Francisco Jaramillo
  • 41
  • 1
  • 3
4
votes
1 answer

Issue with Bracketed Identifier inside SQL Server OPENQUERY

Not sure, if this should go on Stack Overflow, or the DBA section. Having an issue with a procedure I'm writing. I have a variable, lets say: SET @name sysname --also tried to make as varchar This is used inside a cursor, and will basically contain…
Arun
  • 941
  • 6
  • 13
4
votes
1 answer

How to Set a variable using OPENQUERY in SQL Server

I am trying to read data from a table. This table have a list of table name. for each row of the data set I want to run a couple of queries to pull data and insert it into a temporary table. Here is What I have done DECLARE @campName…
Jaylen
  • 39,043
  • 40
  • 128
  • 221
4
votes
1 answer

SQL Server OpenQuery - Passing parameter result from main SELECT query to sub query

I am creating a report in SSRS that returns a bunch of data, one of the columns I need to return is a value from Active Directory. I have a query which gets a value that I want to pass into a subquery which looks a value up in Active Directory.…
Livewire
  • 77
  • 8
4
votes
1 answer

Variables in OPENQUERY "Deferred prepare could not be completed" error

I understand that you cannot include variables in OPENQUERY so the work around is dynamic SQL and I did the following: DECLARE @etd AS DATETIME = '2014-06-28' DECLARE @source AS VARCHAR(46) DECLARE @dbName AS VARCHAR(30) DECLARE @query AS…
arvstracthoughts
  • 730
  • 1
  • 7
  • 24
4
votes
3 answers

Convert text blob to nvarchar from Firebird to SQL Server

I am using SQL Server and have got a Firebird database as a linked server in my SQL Server. I am trying to do an OPENQUERY to import data from the Firebird db; SELECT * FROM OPENQUERY(Workbench, 'SELECT * FROM table_name') and am getting the…
mithilatw
  • 908
  • 1
  • 10
  • 29
1
2
3
…
27 28