OpenQuery to a LinkedServer

I have SQL Server 2000 with Pervasive SQL 2000i attached
as a linked server. When I run the following...
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT "START_ORDER_NO"
FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0
--The START_ORDER_NO field contains a 48
Changing the SET clause to ...
SET START_ORDER_NO = 1~9
--The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10
--The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
--The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
--The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value I write...
The LinkedServer is Pervasive SQL 2000i using 'OLE DB
Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code
page issue, but I haven't had any luck yet.
Any help would be greatly appreciated.
Hi Bob,
Thanks for your post.
From your descriptions, I understood that you would like to set the number
to be one and it appears to be something else. Correct me if I was wrong.
This issue seems strange, here are some steps I think you could make a try
to see whether it will make any further progress
First of all, try to use xp_enum_loedb_providers listed in the document
below to setup your linked server.
INF: xp_enum_oledb_providers Enumerates the OLE DB Providers
http://support.microsoft.com/?id=216575
Secondly, could you use INTEGER or UINTEGER in Pervasive.SQL 2000 instead
of NUMERIC(8,0)?
Pervasive.SQL 2000 Supported Data Types
http://www.pervasive.com/library/doc...BtrDType2.html
Unfortuantely, I do not have Pervasive SQL 2000 installed and cannot
reporduce it. If I may, I would like to suggest you seeing whether
Pervasive Software had meet this before.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||You understand correctly.
I am using 'OLEDB Provider for ODBC' or MSDASQL
The field types have been defined by the original vendor, Pervasive SQL
2000 is part of a proprietary ERP system. I don't have the luxury of
changing field definitions.
Thanks for the information though.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Bob,
Thanks for your prompt updates letting me know the status of the issue.
First of all, you may use (numeric(8,0), 0) to test whether it will make
any progress.
Secondly, what odbc driver you are using? I think you may try to check this
with that Pervasive ODBC Driver vendor about issue.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

OpenQuery - Execution plan

Whats the best way to run some dynamic sql when using OpenQuery on a linked
server? Is it possible to use sp_executesql in OpenQuery?
Ben
See if this helps you
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..authors'
EXECUTE ('SELECT * FROM OPENQUERY([servername], ''SELECT * FROM ' +
@.tablename + ''') AS T')
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E6724190-2A1D-4FCB-A6E5-18A8F312D6E6@.microsoft.com...
> Whats the best way to run some dynamic sql when using OpenQuery on a
> linked
> server? Is it possible to use sp_executesql in OpenQuery?
sql

OpenQuery - Execution plan

Whats the best way to run some dynamic sql when using OpenQuery on a linked
server? Is it possible to use sp_executesql in OpenQuery?Ben
See if this helps you
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..authors'
EXECUTE ('SELECT * FROM OPENQUERY([servername], ''SELECT * FROM ' +
@.tablename + ''') AS T')
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E6724190-2A1D-4FCB-A6E5-18A8F312D6E6@.microsoft.com...
> Whats the best way to run some dynamic sql when using OpenQuery on a
> linked
> server? Is it possible to use sp_executesql in OpenQuery?

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay|||Absolutely right My mistake
I have updated my Query and if i run the below Query it Gives ame an Error as
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Update JAX_EOL_COntract
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
please advice
Thanks
"Aaron [SQL Server MVP]" wrote:
> What is the purpose of Contract_ID in the inner SELECT?
> Look at the query you're running. You're saying, basically, "... SET
> some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
> the SET statement supposed to do with it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> >
> > Update COntract
> > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > FROM LinkedServer.Closed_Contract
> > where Disp_Date is not Null)
> >
> > This Query gives me an Error as
> > Only one expression can be specified in the select list when the subquery
> is
> > not introduced with EXISTS.
> >
> > My Select Query does return more then 1 record. is their any way to
> perform
> > this Query in a better way as the select Query retrieves more then 200,000
> > records.
> >
> > LinkedServer = Linked server from one Server to another
> >
> > Thanks
> > samay
>
>|||Well if there are two dates in Closed_Contract where disp_date is not null,
let's say 20041110 and 20041108, which one do you want? You can use SELECT
MIN() or SELECT MAX() or some other condition which enforces only one row to
be returned.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
> Absolutely right My mistake
> I have updated my Query and if i run the below Query it Gives ame an Error
as
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The statement has been terminated.
> Update JAX_EOL_COntract
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> please advice
> Thanks
> "Aaron [SQL Server MVP]" wrote:
> > What is the purpose of Contract_ID in the inner SELECT?
> >
> > Look at the query you're running. You're saying, basically, "... SET
> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
is
> > the SET statement supposed to do with it?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> > >
> > > Update COntract
> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > > FROM LinkedServer.Closed_Contract
> > > where Disp_Date is not Null)
> > >
> > > This Query gives me an Error as
> > > Only one expression can be specified in the select list when the
subquery
> > is
> > > not introduced with EXISTS.
> > >
> > > My Select Query does return more then 1 record. is their any way to
> > perform
> > > this Query in a better way as the select Query retrieves more then
200,000
> > > records.
> > >
> > > LinkedServer = Linked server from one Server to another
> > >
> > > Thanks
> > > samay
> >
> >
> >|||Following up on Aaron's comment, possibly you need to add a restriction to
the subquery so it only returns the record for the current contract.
Something like
Update JAX_EOL_COntract JC
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract CC
where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Mike A.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:echAR10xEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Well if there are two dates in Closed_Contract where disp_date is not
> null,
> let's say 20041110 and 20041108, which one do you want? You can use
> SELECT
> MIN() or SELECT MAX() or some other condition which enforces only one row
> to
> be returned.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message
> news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
>> Absolutely right My mistake
>> I have updated my Query and if i run the below Query it Gives ame an
>> Error
> as
>> Subquery returned more than 1 value. This is not permitted when the
> subquery
>> follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
>> The statement has been terminated.
>> Update JAX_EOL_COntract
>> Set dDispositionDate = (SELECT Disp_Date
>> FROM LinkedServer.Closed_Contract
>> where Disp_Date is not Null)
>> please advice
>> Thanks
>> "Aaron [SQL Server MVP]" wrote:
>> > What is the purpose of Contract_ID in the inner SELECT?
>> >
>> > Look at the query you're running. You're saying, basically, "... SET
>> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
> is
>> > the SET statement supposed to do with it?
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>> >
>> >
>> > "KritiVerma@.hotmail.com"
> <KritiVermahotmailcom@.discussions.microsoft.com>
>> > wrote in message
> news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
>> > >
>> > > Update COntract
>> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
>> > > FROM LinkedServer.Closed_Contract
>> > > where Disp_Date is not Null)
>> > >
>> > > This Query gives me an Error as
>> > > Only one expression can be specified in the select list when the
> subquery
>> > is
>> > > not introduced with EXISTS.
>> > >
>> > > My Select Query does return more then 1 record. is their any way to
>> > perform
>> > > this Query in a better way as the select Query retrieves more then
> 200,000
>> > > records.
>> > >
>> > > LinkedServer = Linked server from one Server to another
>> > >
>> > > Thanks
>> > > samay
>> >
>> >
>> >
>|||> Following up on Aaron's comment, possibly you need to add a restriction to
> the subquery so it only returns the record for the current contract.
> Something like
> Update JAX_EOL_COntract JC
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract CC
> where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Yes, this will work, assuming a one-to-one relationship (which, based on
table names, seems likely).

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samay
What is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay