OPENQUERY End-of-file Error

Sep 5, 2007



I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is

SELECT * FROM OPENQUERY( server, '
SELECT

*
FROM

(
WITH a AS
(
SELECT

a,
b,
c
FROM

table1
)
SELECT

x,
y,
z
FROM

a a1
INNER JOIN

table2 t2
ON a1.a = t2.a
)
')

When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?

View 4 Replies


ADVERTISEMENT

Openquery Error

Nov 20, 2002

Hello,
if I am running this query and getting the error the below error

SELECT * FROM OPENQUERY(AS400ODBC,'
SELECT CBH_CREDIT_MEMO_NMBR,CBH_CREDIT_MEMO_DATE,CBH_CUST _TAX_CODE,
CBH_TAXABLE_TOTAL,CBH_CUST_NMBR FROM CREDITBLDH CR1 WHERE CR1.
CBH_CREDIT_MEMO_DATE = (SELECT MAX(CR2.CBH_CREDIT_MEMO_DATE) FROM CREDITBLDH CR2 WHERE CR1.CBH_CUST_NMBR = CR2.CBH_CUST_NMBR)')

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time 7211 exceeds limit 600.]


What can I do for this?.
I can able to run fine without subquery
You suggestion appreciated.
T's,
Ravi

View 1 Replies View Related

OPENQUERY ERROR WITH NEGATIVES NUMBERS

Apr 15, 2008

I am using a linked server to retrieve data from an off site source.

I am connecting via a Microsoft OLE DB Provider for ODBC Drivers connection to a local DSN.

I am executing a query like this in Management Studio.



SELECT * FROM OPENQUERY (MY_DSN_NAME,'SELECT * FROM MY_TABLE_NAME')





42949622.96--------> -50.00

42949660.46--------> -12.50

42949671.80--------> -1.16



42949431.65--------> -241.31

My negative numbers are coming out all messed up and in some instances it gives me an error of

Invalid data for type "numeric".

Any comments or ideas?

View 1 Replies View Related

Error Running Openquery(mdx) Through A Linked Server

Feb 26, 2007

I'm trying to create linked server to access DMX functions from SQL Server as per:

Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx

I create the link this query

EXEC master.dbo.sp_addlinkedserver

@server = N'KLSSQL01AnalysisServerLink',

@srvproduct=N'Analysis Services 2005',

@provider=N'MSOLAP',

@datasrc=N'kls-sql01',

@catalog=N'AnalysisServicesPredictorPrototype'

GO

SELECT * FROM OPENQUERY(KLSSQL01AnalysisServerLink, 'select node_caption, node_type from [Misuse Abuse Profile].content')

where [Misue Abuse Profile] is the Mining model

Provider options: Allow in process

I receive the follwing error:

OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "The peer prematurely closed the connection.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink".
I found this post but there was no resolution.

run openquery(mdx) through a linked server



http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924869&SiteID=1

View 2 Replies View Related

Linked Server Error... Openquery() Locking Tables

Feb 1, 2008



Hi All -

My Set up:

Server A - Oracle 10g Database
Server B - SQL2005
Client PC - Sql Express

Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).

When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:

Command attempted:
{call sp_MSreleasemakegenerationapplock}
Error messages:
The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)

I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!

View 1 Replies View Related

Intermittent Openquery Error ..The OLE DB Provider 'MSDASQL' Indicates That The Object Has No Columns..

Jul 20, 2005

Help,I am running a pass through query to oracle from SQL server 2000 asfollows;select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')I have run this query through both DTS and the query analyzer and getthe foloowing error;Server: Msg 7357, Level 16, State 2, Line 3Could not process object 'select * from FND_FLEX_VALUES'. The OLE DBprovider 'MSDASQL' indicates that the object has no columns.OLE DB error trace [Non-interface error: OLE DB provider unable toprocess object, since the object has no columnsProviderName='MSDASQL',Query=select * from FND_FLEX_VALUES'].The really strange thing is, I'll get this error the first time Iexecute the query but if I execute it immeadiatley after it will runfine.Any help would be most appreciated!Cheers

View 1 Replies View Related

OPENQUERY Throws Error 7357 When The Source SP Uses Temporary Table.

Mar 31, 2006

Hello Everybody / Anybody,
Sorry but exiting problem!

The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.

[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]

I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.
I think you got what the situation is!! Woh!

Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!
------------------------------------- The Code ----------------------------




create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx

create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')

-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine

Thanks in advance...

View 5 Replies View Related

Linked Servers OPENQUERY Error Deferred Prepare Could Not Be Completed

May 13, 2008

I am running the following query trying to return server properties across a linked server. I want to store the results in a table on the server where I an running the query.


DECLARE @BuildClrVersionx nvarchar(128)

SET @BuildClrVersionx =

(SELECT *

FROM OPENQUERY(LKMSSQLXYZ01, 'CONVERT(nvarchar(128),SERVERPROPERTY("BuildClrVersion")'))

I am getting the following errors:

OLE DB provider "SQLNCLI" for linked server "LKMSSQLADM01" returned message "Deferred prepare could not be completed.".

Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.


If you have any ideas how I can run this query across a linked server I would appreciate it.


Thanks,
Scott

View 8 Replies View Related

[File System Task] Error: An Error Occurred With The Following Error Message: Access To The Path Is Denied

Sep 7, 2007

Hi -

I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:

[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".

Where do I change the security settings?

Thanks - Grace

View 5 Replies View Related

Integration Services :: Flat File Error File Being Created In-spite Of No Errors

Jun 23, 2015

I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file.   I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the  data from Source to Destination.

View 5 Replies View Related

Help Getting Error When Using Operation Rename A File In The File System Task Editor?

Aug 18, 2006

Does anyone know how to do this using variables?  Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001. 

 I also tried it writing a script and still the same error.  If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename.  So the value of the variables will change everyday.  Here is my expression:

@[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:Documents and SettingsmroushDesktopOSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

 

View 6 Replies View Related

File System Task Error When Using SQL Server Agent (when Move File On Network Drive)

Jun 8, 2007

I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.



What I can do to solve this issue.



Bye!

Daniel

View 1 Replies View Related

Flat File Connector Stops Processing File On Empty Row And Generates Fatal Error

Dec 27, 2007

Here's a really annoying problem. Let's say you have a text file with 2 million rows.Delimiters all look good and rows are previewed well but the file has a missing row at say lin 1234567 - way deep in the file. When SSIS encounters the blank row, an error is raised and processing on the file STOPS! I verified this in by checking the SSIS log and have even developed an error routine to notify me via email when the error occurs (really cool if I do say so myself ). The main problem still remains - how to resume processing from the point of failure in the file? Any help is appreciated. Thanks.

View 13 Replies View Related

Cannot Attach Mdf: Create File Encountered Operating System Error 5 While Attempting To Open The Physical File...

Sep 5, 2006

I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.

Any way I can recover it?

Thanks.

View 4 Replies View Related

DTS Migration Wizard Failed To Save Package File -with Error 0x80070002 - Th System Cannot Find The File Specified.

Jan 31, 2007

Hi,

I use the DTS 2000 Migration Wizard to migrate one of the DTS 2000 packages to SSIS. The migration failed with the following error message:


LogID=17
#Time=6:31 PM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:Documents and SettingsfuMy DocumentsVisual Studio 2005ProjectsKORTONKORTONProcessCubesMF.dtsx" with error 0x80070002 "The system cannot find the file specified.".
---> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:Documents and SettingsfuMy DocumentsVisual Studio 2005ProjectsKORTONKORTONProcessCubesMF.dtsx" with error 0x80070002 "The system cannot find the file specified.".

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)

Looking at the call stack, it looks like COM wrapper fails on SaveToXML. Can someone tell me how I should workaround this problem?

Thanks,

Bobby Fu

View 1 Replies View Related

Operating System Error 38(Reached The End Of The File.) On File C:Datamyfile_log.LDF

Dec 21, 2006

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'myserver'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:Datamylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:Datamylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

 

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

 - put the db in emergency mode;

 - stop sql service and replace the mdf file;

 - start sql service;

 - Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

 

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

 

View 5 Replies View Related

Openquery???

Sep 14, 2004

Hi All,

Does anyone know the syntax for an insert statement using Openquery in a stored procedure? All the examples I've seen are Select statements, but I want to send data to a linked server.

Would I be better off using DTS??

Thanks,

Greg

View 1 Replies View Related

Openquery

Dec 13, 2007



How to create linked server to Dbf,

How to openquery util step by step

View 1 Replies View Related

OPENQUERY

Sep 19, 2007



HI,
Can we use OPENQUERY with a parameter? Something like this:
SELECT * FROM OPENQUERY(@SOURCE_SERVER_NAME, 'Select * from dbo.FEED')


Please let me know at the earliest. Thanks a lot,.

Mannu.

View 3 Replies View Related

Deleting A File From File System - Syntax Error

Jun 29, 2015

Ok so I have some dynamic sql to delete a file that is created via sql earlier on. It is to provision a copy of a database to an instance on link server. Everything works great and the files used to delete. Now, with no code changes it is throwing a syntax error. I do a print of what the dynamic sql is creating before executing and then I copy / paste what was generated into command prompt and guess what!! The file deletes.

Here is the result on screen:

@DeleteBackupFileStatement: DEL adas16.clients.advance.localwip$AvionteAP_Template_893.bak /Q
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.

Here is the code that creates the statement:

SET @DeleteBackupFileStatement = NULL
BEGIN
SET @DeleteBackupFileStatement = 'DEL ' + LTRIM(RTRIM(@BackupFile)) + ' /Q'
END
PRINT '@DeleteBackupFileStatement: ' + cast(@DeleteBackupFileStatement as varchar(400))
BEGIN
EXEC adasdb.master.sys.Sp_executesql
@DeleteBackupFileStatement
END
END

The value of @BackupFile is simply the path of the file with the file name, everything in the prepared statement with the exception of DEL and the switch at the end.

View 9 Replies View Related

File Does Not Exist Error While Renaming A Flat File

May 22, 2008

Hi All,

We have a SSIS package which is accessing a remote Windows file share location.
The package first moves the file from folder-1 to folder-2 and also renames the file during this process.
Then the package reads the file (using a flat file connection FF_SRC) from folder-2 and renames it again after processing it successfully.

The permissions given to the user executing the package on folder-2 are: Read+Write+Modify+List folder contents.

We are facing an error:




Code SnippetFile or directory "Z:folder-2XYZ.txt" represented by connection "FF_SRC" does not exist.





We are getting the above error when the SSIS package is trying to rename the file the second time in folder-2.

However, the file exists in folder-2.

The OS is Windows 2000 Server SP4.

Any ideas why this could be happening and how it could be resolved?

Best Regards,
Avnip

View 8 Replies View Related

Foreach File Enumerator, Skip File On Error.

Mar 11, 2008



Hi,
I'm struck with a small issue.. would be great if somebody can help me out. Here is te scenario

1. There would be more than one CSV files in INPUT folder.
2. I'm using a Foreach Loop file enumerator to loop thru the files and load the data into database.
3. If loading is successful the file need to be moved to ARCHIVE folder and next file needs to be picked up for loading
4. In case if there is an error in loading the file has to be moved into ERROR folder, Error description should be logged to error log text file and next file needs to be picked up for loading.


I don't think increasing max error count is an option as I don't know how many no. of input files are available as it depends upon the feed.

What is the best way to go.

View 16 Replies View Related

CSV File Saved As An Excel File - Error Message

Aug 2, 2007

I hope someone can help me with this - I started receiving this error message in the past month or so when I open a csv report and save it as an Excel file in a folder I use on my VPN and in My Documents. It does not show up when I save it to my Desk Top.

I have Microsoft Office Student and Teacher and Office XP Professional installed on my notebook. I tried to uninstall Office XP and it would not let me. Something about a "patch could not be opened......"

The error message is as follows:

Header: .NET-BroadcastEventWindow.2.0.0.0.33c0d.0.EXCEL.EXE-Application Error

Excel error message The instruction at 0x0beab865 referenced memory at "0x00000008"

The memory could not be "read".
Click ok to terminate the program.

I hope someone could please help me with this I received 60 - 80 csv files a week and everythime I save on I get this pop up message!

Thank you!

Leslie

View 1 Replies View Related

Openquery Problem

Apr 18, 2008

Hi everybody,
I am having a problem using a servername with '' in the openquery statement. I'd really appriciate if someone could suggest how I should be using it. Here is the query:
select * from openquery(sqldev est,'SELECT COUNT(*) FROM t_login WHERE username=''Tom''') into count
thanks in advance
devmetz

View 2 Replies View Related

Openquery() Search With NOT LIKE

May 20, 2008

Hi All,I want to use the following code to use 'NOT LIKE' clause for my File system search here is the code:SELECT Docs.FileNameFROM OPENQUERY(OPINIONSERVER, 'SELECT Filename FROM SCOPE() WHERE FREETEXT(''Any text not to search'')') AS Docs I want to use the above code for my html file system search similar to:ColumnName NOT LIKE N'%1971%'The confusing part for me is that in normal queries we use the column name to search in, but while searching in the file system using the FREETEXT() function how we exclude the words user dont want to search.I am using Dotnetnuke.

View 2 Replies View Related

Using Functions In Openquery

Jul 20, 2001

I'm trying to use the date() function in an openquery statement in query analyzer and I keep getting an illegal symbol ")" error. The statement is :

select *
from openquery([Big Blue], 'select cde_date, cde_item from acch1 where cde_date < date()')

The objective is to find records where cde_date is prior to today. The syntax works fine if I execute it on the mainframe in QMF and the ODBC connection works fine if I hard code the value. What I need is the ability to use the function so I can run the query on subsequent days without having to edit it.

Any suggestions??

View 1 Replies View Related

Openquery For Oracle

Jan 22, 2003

Hi,
I am using the below SQL query for Oracle
SELECT FP_MONTH
FROM CDWD..CDW.ACCOUNT_SALES
WHERE LAST_MODIFIED_DT > '2002-12-01 00:00:00.000'
OR SOURCE_CONTROL_DT > '2002-12-01 00:00:00.000'

My questions are
1. If I am using this date fileter it is taking long time than without filter
Why ?.
2. How I can write Open query for the above query?.
I am expecting valuable advice.
Thanks,
Ravi

View 3 Replies View Related

Openquery Erro Help

Feb 5, 2003

Hi,
I need your help to solve this error.
I am running the open query against Oracle server and this shows blow.


UPDATE Lp.dbo.CB
SET oldest_invoice_date = x.oldest_invoice_date
FROM ( SELECT MIN(INVOICE_DATE) as oldest_invoice_date,
I.ACCOUNT_NUM
FROM CDWD..CDW.INVOICE I
WHERE I.INVOICE_NUM >= 0
AND INVOICE_DATE IS NOT NULL
GROUP BY (I.ACCOUNT_NUM)
)x
WHERE account_num = x.ACCOUNT_NUM

I am getting the below error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'oldest_invoice_date'.


Anybody give solution what I am doing wrong?.
Thanks,
Ravi

View 3 Replies View Related

OpenQuery() Help Needed

Aug 25, 2004

Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success. DECLARE @sql nvarchar(4000)SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' ' select producer_id from openquery([sybtest], @sql) -------------------------------------------DECLARE @producer_id char(1)SET @producer_id = 'A' select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )

View 4 Replies View Related

OPENQUERY Parameter

Oct 11, 2005

I am trying to do the following:

SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], Expire Date
From Product Where [ExpireDate] > 2005-12-31')
GO

However the above sql statement doesn't get the dates greater than the date provided unless there are quotes around the date. How do I add a variable that will cover this date and include the identifiers to get the correct records

View 4 Replies View Related

OpenQuery Using A Variable

Jan 29, 2004

Hi,

Here's what I did:

1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @CQUERY = 'SELECT ...'

Now, when I try to execute the OpenQuery method using that variable, it fails.

Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @CQUERY)

I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@CQUERY'.

Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

Thanks,

Skip.

View 5 Replies View Related

How To Execute A SP Using OpenQuery

Jun 12, 2008

Hi,

I am trying to execute a ServerB stored Procedure which takes Int as paramter using OpenQuery in ServerA.But this doesnt seems to working.
Please Help !!!!

DECLARE @param1 INT
SET @param1 1

SELECT *
FROM OPENQUERY(ServerB,'DBNAME.dbo.SP_NAME ''@param1''')

View 3 Replies View Related

SQL Openquery && Oracle

Jul 23, 2005

In SQL Server 2000 I have set up an Oracle linked server. When I runthe following query it runs fine:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ORACLETABLENAME')However the following query does not work:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT FIELDNAME FROM ORACLETABLENAME')This error is returned:-Server: Msg 7321, Level 16, State 2, Line 1An error occurred while preparing a query for execution against OLE DBprovider 'MSDAORA'.[OLE/DB provider returned message: ORA-00904: "FIELDNAME": invalididentifier]OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Preparereturned 0x80040e14].Basically select * works ok, but if I specify the field(s) I need thenit errors. I have tried entering the field names in upper and lowercase but it makes no difference.My real problem is that some dates in the Oracle database are pre 1753which SQL server does not recognise so I need to convert (decode) them.Any help would be appreciated.ThanksChris

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved