Very Computer

> I have a stored procedure that is accessing an external database using the
> OpenRowset feature provided in SQL Server.

> When I execute the SP from Query Analyzer I get results, but I see that an
> OLEDB Error occured when tracing through SQL Profiler.

> adjustnum, bankingref, receiptref, `date`, pay, erni, pay + erni AS cost,
> netcharge, netcharge - (pay + erni) AS profit, vat, total, flag, 0 As
> InvoiceNet, 0 As InvoiceDiscount FROM t_mtran WHERE (clientref > '')
> AND((clientref = 'AV2001') OR (clientref = 'AV2002'))

> EXEC ('INSERT INTO #temp (clientref, type, internalno, invoicenum,
> creditnum, adjustnum, bankingref, receiptref, date, pay, erni, cost,
> netcharge, profit, vat, total, flag, InvoiceNet, InvoiceDiscount)
> SELECT * FROM OPENROWSET(''MSDASQL'', ''DRIVER={Microsoft FoxPro VFP
Driver

> Returns results and the following error is trapped through Profiler.

> Non-interface error:  OLE DB provider MSDASQL returned an incorrect value
> for DBPROP_CONCATNULLBEHAVIOR which should be either DBPROPVAL_CB_NULL or
> DBPROPVAL_CB_NON_NULL

> Although QA is okay with this, (shows me some records), in my ASP page I e
nd
> up with a closed recordset.  I am not sure why ADO cannot see the records,
> but my main concern is the error and how to prevent it.

> I have done some research and it is alleged, (see end of post), that the
> underlying ODBC driver used does not support the property
> DBPROP_CONCATNULLBEHAVIOR and one should contact the vendor of the driver.
> Well as you can see from the above code, I am using the MICROSOFT FoxPro
> driver...!?!?!

> Is there anyway I can set this property when calling the OPENROWSET
> function?  I don't know if I could get round this by using a linked
server,
> but in my experience so far I have found OPENROWSET considerably faster
than
> using Linked Servers.

> MTIA

> David M

> --------------------------------------------------------------------------
--
> ----

> Subject: Re: MYSQL Linked Server
> Newsgroups: microsoft.public.sqlserver.server
> Date: 2002-08-19 20:00:16 PST

> Hello Andy,

> Based  on the Error,

> "OLE DB error trace [Non-interface error:  OLE DB provider MSDASQL
returned
> an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
> DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
> OLE DB error trace [Non-interface error]. "

> its a problem of incompatibility of SQL Server with the ODBC Drivers
> provided for the MySQL.
> I would recommed to contact the Provider for the MySQL ODBC Driver  for
> further information.

> Thanks,

> Vikrant Dalwale

> Microsoft SQL Server Support Professional

> --------------------------------------------------------------------------
--
> ----

> SELECT * FROM OPENROWSET(''MSDASQL'',
> ''DRIVER={Microsoft FoxPro VFP Driver(*.dbf)};
> UID=;Deleted=No;Null=No;Collate=Machine;BackgroundFetch=Yes;
> Exclusive=No;SourceType=DBF;SourceDB=d:\TEMPAID4'',
> "<result of print statement>")

> Will the error occur? I'm going to try and reproduce this with Access.

> Bob Barrows

> > Hello

> > I have a stored procedure that is accessing an external database using
the
> > OpenRowset feature provided in SQL Server.

> > When I execute the SP from Query Analyzer I get results, but I see that
an
> > OLEDB Error occured when tracing through SQL Profiler.

> > adjustnum, bankingref, receiptref, `date`, pay, erni, pay + erni AS
cost,
> > netcharge, netcharge - (pay + erni) AS profit, vat, total, flag, 0 As
> > InvoiceNet, 0 As InvoiceDiscount FROM t_mtran WHERE (clientref > '')
> > AND((clientref = 'AV2001') OR (clientref = 'AV2002'))

> > EXEC ('INSERT INTO #temp (clientref, type, internalno, invoicenum,
> > creditnum, adjustnum, bankingref, receiptref, date, pay, erni, cost,
> > netcharge, profit, vat, total, flag, InvoiceNet, InvoiceDiscount)
> > SELECT * FROM OPENROWSET(''MSDASQL'', ''DRIVER={Microsoft FoxPro VFP
> Driver

> > Returns results and the following error is trapped through Profiler.

> > Non-interface error:  OLE DB provider MSDASQL returned an incorrect
value
> > for DBPROP_CONCATNULLBEHAVIOR which should be either DBPROPVAL_CB_NULL
or
> > DBPROPVAL_CB_NON_NULL

> > Although QA is okay with this, (shows me some records), in my ASP page I
e
> nd
> > up with a closed recordset.  I am not sure why ADO cannot see the
records,
> > but my main concern is the error and how to prevent it.

> > I have done some research and it is alleged, (see end of post), that the
> > underlying ODBC driver used does not support the property
> > DBPROP_CONCATNULLBEHAVIOR and one should contact the vendor of the
driver.
> > Well as you can see from the above code, I am using the MICROSOFT FoxPro
> > driver...!?!?!

> > Is there anyway I can set this property when calling the OPENROWSET
> > function?  I don't know if I could get round this by using a linked
> server,
> > but in my experience so far I have found OPENROWSET considerably faster
> than
> > using Linked Servers.

> > MTIA

> > David M

> --------------------------------------------------------------------------
> --
> > ----

> > Subject: Re: MYSQL Linked Server
> > Newsgroups: microsoft.public.sqlserver.server
> > Date: 2002-08-19 20:00:16 PST

> > Hello Andy,

> > Based  on the Error,

> > "OLE DB error trace [Non-interface error:  OLE DB provider MSDASQL
> returned
> > an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
> > DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
> > OLE DB error trace [Non-interface error]. "

> > its a problem of incompatibility of SQL Server with the ODBC Drivers
> > provided for the MySQL.
> > I would recommed to contact the Provider for the MySQL ODBC Driver  for
> > further information.

> > Thanks,

> > Vikrant Dalwale

> > Microsoft SQL Server Support Professional

> --------------------------------------------------------------------------
> --
> > ----

> Have you considered using the Foxpro equivalent of COALESCE or ISNULL to
> avoid the Null issue?

> Bob

> > Hi Bob

> > Yes, I ran this in Query Analyzer:

> > I had thought it might be to do with the Insert too, but I think it is
> > encountering a null value somewhere, maybe in the (pay + erni) column
and
> it
> > doesn't know how to treat it.  Seems a bit wiered.  I had foolishly
> assumed

> > Hi Bob

> > So, you are left with the Visual FoxPro driver which I presume you are
> > quoting those functions from.

> No, no, no - I said "Foxpro equivalent of COALESCE or ISNULL "

> COALESCE and ISNULL (similar to Access' NZ) are SQL Server T-SQL
functions.
> I don't know if Foxpro has an equivalent function (I was assuming you
would
> know <grin>).

> Bob

> Does that help? I don't know if this function can be used in Foxpro's
brand
> of SQL, but it can't hurt to try...

> Bob

> > > Hi Bob

> > > So, you are left with the Visual FoxPro driver which I presume you are
> > > quoting those functions from.

> > No, no, no - I said "Foxpro equivalent of COALESCE or ISNULL "

> > COALESCE and ISNULL (similar to Access' NZ) are SQL Server T-SQL
> functions.
> > I don't know if Foxpro has an equivalent function (I was assuming you
> would
> > know <grin>).

> > Bob

> You will not believe this if I tell you, but my client is using a MS-DOS
> based, Clipper over FoxPro 2.x DBF files application to run their Payroll
> and Invoicing.

> I am all for the "if it aint broke" approach, but it has been causing me
no
> end of nightmares as Microsoft long since stopped supporting their FoxPro
> 2.x driver and MDAC 2.6+ prevents you from using it!

> So, you are left with the Visual FoxPro driver which I presume you are
> quoting those functions from.  As far as I am aware in FoxPro 2.x there is
> no concept of Nulls, just fields padded with blanks.  (You will notice
from
> my earlier post things like WHERE clientref > '').  This is why, I
believe,
> the Visual FoxPro driver allows you to set the option of Nulls=No.  I read
> somewhere that this is the thing to do when using VFP driver with 2.x
> databases.

> The EXEC is being used so that I can pass a parameterised query, (well
> dynamic SQL), to the OPENROWSET function which, by the way, I cannot
believe

> I will try those functions, (will need to read up on COALESCE, havn't seen
> that since SQL CE to SQL Server replication!)

> Thanks again Bob.

> David M

> > Actually, I was suspecting that the EXEC() was the culprit. I'm still
> going
> > to attempt to reproduce this with Access. Unfortunately, I think my boss
> > would like me to work on his projects now <grin> so I will need to get
> back
> > to this later.

> > Have you considered using the Foxpro equivalent of COALESCE or ISNULL to
> > avoid the Null issue?

> > Bob

> > > Hi Bob

> > > Yes, I ran this in Query Analyzer:

> > > I had thought it might be to do with the Insert too, but I think it is
> > > encountering a null value somewhere, maybe in the (pay + erni) column
> and
> > it
> > > doesn't know how to treat it.  Seems a bit wiered.  I had foolishly
> > assumed