MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!
Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>

MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>

MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online.
Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you a
re using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you n
eed to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we
have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WA
RNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>sql

MDAC Roadmap and .mdb files

I know this isn't SQL server related but this seemed to be the only appropriate forum.

I would like to work with Access databases (.mdb files) via (non-managed) C++. At the moment I'm using the ODBC provider for OleDB. However I see from the MDAC "Data Access Technologies Road Map" that this provider is deprecated. The OleDB/ADO alternatives seem to be focussing on SQL server only; I just want a 'local', non-networked desktop system. What technology should I use that isn't deprecated?

Yours,

Peter

If you are using C++ and either OLE DB or ODBC then you might want to consider SQL Server 2005 Express Edition (http://msdn.microsoft.com/sql/express/).

I'd also recommend looking at C++/CLI (there's an Express edition for Visual Studio 2005 also - http://msdn.microsoft.com/vstudio/express/visualc/default.aspx). It's now very straightforward to mix native and .Net code so you can get the best of both worlds - reuse your code and add new features/UI with .Net at your own pace http://msdn.microsoft.com/msdnmag/issues/04/05/VisualC2005/ and http://msdn.microsoft.com/msdnmag/issues/05/01/COptimizations/

|||

Thanks for your answer. That has given me confidence that I can stick with my current system for the time being, but shift to SQL Server in the future, as required. My database needs are pretty simple and I think I could do it without actually changing any code apart from the connection string.

Pete

MDAC error

I have upgraded MDAC to vers 2.7 SP1(winXP).

On checking via the MDAC Cmponent Checker that one file has an error or maybe currupt.

I have tried to reinstall with no joy. The error description is as follows:

Item Name,Item Type,ErrorString,Status
oledb32.dll,File,Field 'FileVersion' mismatch: Expect '2.71.9030.0 built by: Lab06_N(dagbuild)' Got '2.71.9031.4 built by: Lab06_N(dagbuild)',Error

Could you shed any light on what might be the problem is?

Cheers PetermJust uninstall everything related to MDAC and remove registry keys, reboot the box.

For information on MDAC issues refer to this Article (http://www.8wire.com/articles/index.asp?AID=2318) and this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;324520).

Make sure to install latest MDAC and SP from MS.

MDAC 2.6 and SQL Server 2000

I have been working on a VB6 app that talks to a SQL Server 2000 (SP2) engine via OLEDB. All works well with the exception of one transaction. The particular a transaction updates two tables via stored procs and then is either committed or rolled back.

I have an weird error where by when the transaction calls the second stored proc the data from the first stored proc is gone. I have put a break point in VB before the second stored proc is executed and the data exists but after execution of the second stored proc the data is gone.

The issue gets more weird becuase the app runs fine on a Win 2k machine with MDAC 2.5 but not with MDAC 2.6. I have other transactions that update via two stored procs but they are fine. I tried the app under MDAC 2.7 and that doesn't work either.

I have no rollbacks or commits in the stored procs. Also worth noting that if I am only upadting the second table by adding 5 rows for example, only the last row will be written.

Any suggestions would be most welcome.RE: Q1 Any suggestions would be most welcome.

A1 Are you quite certain you have tested your applications and results using the same MDAC levels accross the server, and the clients being tested (each time)? (There is an executable utility available to check and verify MDAC version levels.) Some symptoms sound like they may result from isolation level issues, while others sound quite peculiar given the data as presented.|||What is the "weird error" you are receiving ? The data that exists and then is gone - is it stored in a table and then "rolled back" out of the table ? I have attached ms component checker if you are concerned about mdac. You can either extract it or rename is to *.exe and it will extract automatically ( I have to a zip extension on it because exe's are not uploadable).|||A possible simple explanation for the "weird error" as described, is that it might be nothing more than an ordinary dirty read (made before a successful commit). The "when upadting the second table by adding 5 rows for example, only the last row will be written" issue is more peculiar. I've sometimes seen such kinds of things in procedures that use cursors (when "unexpected" conditions exist) and / or errors are not handled well.|||Is each stored procedure doing a commit or rollback (within the stored procedure) ? Can you post the stored procedures ( or a simpler version of what you are doing ) and the vb code ?|||The more I thought about your problem, I remembered a similar situation a couple of weeks ago - take a look at the following post and see if it fits:

post (http://dbforums.com/showthread.php?s=&threadid=554748)|||Hi.
Firstly, thankyou for your help. For reference, I am developing on a MDAC 2.6 machine running windows 2k, sp2, but I have referenced mdac 2.5 in the VB project references. The SQL server is running MDAC 2.6.
I can confirm that the stored procedures do not have any rollbacks, this is all handled by the VB code. The stored proc is called once for each row that is to be updated, as the VB code searches through a grid row by row. If any changes are detected on a row then the stored proc is called to perform the update.Once all rows have been searched and updated (if necessary) then the commit is called. In the event of an error then a rollback is called.
Thanks for your help!|||I would run sql profiler for both the successful and unsuccessful transactions - then look at the two and see what is different. If you don't see an answer within these results, please post the profiler output.|||RE: "I can confirm that the stored procedures do not have any rollbacks, this is all handled by the VB code. The stored proc is called once for each row that is to be updated, as the VB code searches through a grid row by row. If any changes are detected on a row then the stored proc is called to perform the update.Once all rows have been searched and updated (if necessary) then the commit is called. In the event of an error then a rollback is called."

Q1 I'm guessing "this is all handled by the VB code" means the ADO, ODBC, etc., API is making the actual transaction syntax on the Sql Server. That could account for the apparent MDAC related differences you are seeing. The rnealejr suggestion to run sql profiler traces for both the successful and unsuccessful transactions and compare them in minute detail is a very good one.

Q2 Just to clarify, this sounds like a description of a series of transactions (one for each updated row), collectively nested in a single transaction for the entire grid. Is that correct?|||Hi.
I have finally managed to make some progress on this matter. I ran the profiler, with a trace on two different situations, i.e. one which works OK and one which doesn't. I kept things simple and simply traced 1 - a deletion of 1 row, on its own, and 2 - a deletion of 2 rows, which rolls back the first deletion so that only the last worked on is actually committed (deleted). I am not sure how to post the trace files on here properly (forgive me, I am new to the profiler) but the text output is:-

Firstly for the deletion of one row which works..
---
set implicit_transactions on (Visual Basic)
---
declare @.P1 varchar(255) (Visual Basic)
set @.P1='OK'
exec UOM_CHANGE @.P1 output, 1, '108', '108', 'tracetest', 'TRC', '12/31/3000'
select @.P1
---
IF @.@.TRANCOUNT > 0 COMMIT TRAN (Visual Basic)
---

Now secondly the deletion of 2 rows which doesnt work so well....
---
set implicit_transactions on (Visual Basic)
---
declare @.P1 varchar(255) (Visual Basic)
set @.P1='OK'
exec UOM_CHANGE @.P1 output, 1, '108', '108', 'trace test 1', 'TRC1', '12/31/3000'
select @.P1
---
SET NO_BROWSETABLE ON (Visual Basic) *
---
SET FMTONLY ON EXEC UOM_CHANGE ' ',0,' ',' ',' ',' ',' ' SET FMTONLY OFF (Visual Basic) *
---
set fmtonly off (Visual Basic) *
---
SET NO_BROWSETABLE OFF (Visual Basic) *
---
declare @.P1 varchar(255) (Visual Basic)
set @.P1='OK'
exec UOM_CHANGE @.P1 output, 1, '109', '109', 'trace test 2', 'TRC2', '12/31/3000'
select @.P1
---
IF @.@.TRANCOUNT > 0 COMMIT TRAN (Visual Basic)
---

Now then, the only difference between the two (apart from of course that the stored proc is called twice in the second trace) are the four operations that I have highlighted above with an asterisk. From reading MSDN it would seem that there is a known bug that a patch is available for, so I will need to look into this further.
Please see this article, ref Q281633

http://support.microsoft.com/default.aspx?scid=KB;en-us;q281633

Our customers build is MDAC 2.5 so we must code to their requirements, but possible the MDAC 2.6 service pack may work. Watch this space!
Thanks for your help, its proven very useful.|||Bingo ! Are you running the original version of mdac 2.6 (no service packs) ? If so, then you hit the nail on the head. Actually, I ran into this problem a few months ago - once I saw your profiler with the set fmtonly - it reminded me.sql