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

MDB to SDF

Hello, everyone.

I am a new developer of .NET CF. Currently, I have a project that needs transferring tables from a existing Access database to SQL Server 2005 Mobile database. Does anybody know how to do it?

Thanks in advance.

Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Thanks for your help.

It looks like that I have to develop a tool myself.

|||

We are really sorry that we could not help you much in this regard. But we would surely take this as feedback and work on this seriously.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Raise an issue (as a suggestion) in the public Feedback Database if you'd like such a tool to be included in a future SQL Server release:

http://lab.msdn.microsoft.com/productfeedback/

|||

Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.

Is there any walkthru that I can perview on exactly how to do that?

MDB to SDF

Hello, everyone.

I am a new developer of .NET CF. Currently, I have a project that needs transferring tables from a existing Access database to SQL Server 2005 Mobile database. Does anybody know how to do it?

Thanks in advance.

Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Thanks for your help.

It looks like that I have to develop a tool myself.

|||

We are really sorry that we could not help you much in this regard. But we would surely take this as feedback and work on this seriously.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Raise an issue (as a suggestion) in the public Feedback Database if you'd like such a tool to be included in a future SQL Server release:

http://lab.msdn.microsoft.com/productfeedback/

|||

Are

you talking about MS Access Database or MS Pocket Access Database. In

any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs

and transfer the data one row by row. I know its a slow operation but

it is all design time.

Is there any walkthru that I can perview on exactly how to do that?

MDB to SDF

My company has purchased the SQL 2005 Server Enterprise and I have imported the Access database to the SQL Server.

I have built a SQL 2005 mobile database (a sdf file) within VS.net 2005. Can any one tell me how to load it to the SQL server management studio and transfer tables from SQL Server Enterprise database to the SQL 2005 Server Mobile database?

By the way, I could not fine the “SQLMobile.sql” file mentioned in the article named “Step by step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005”, which I downloaded from Microsoft website. Anyone knows?

Thanks in advance.

There is a MSI in that white paper which you need to download to get SQLMobile.sql.

You can use SQL Server Integration Services to transfer the data between different data bases.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Laxmi NRO MSFT ,

Thank you very much for your help.

I downloaded the MSI and finished the "Step by step" exercise. It is very helpful and I have the confidence to finish my project now.

|||

Thanks alot and pleasee feel free to post your comments on the paper so that we can keep updating it for more and more usefulness.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hi,

I want to import tables and data in them from an access database(.mdb) to .sdf file (i.e SQL 2005 compact Edition).

Could you please guide me the steps to do this.

Thanks and regards

G Sreenaiah <gsreenaiah@.msn.com>

MDB to SDF

My company has purchased the SQL 2005 Server Enterprise and I have imported the Access database to the SQL Server.

I have built a SQL 2005 mobile database (a sdf file) within VS.net 2005. Can any one tell me how to load it to the SQL server management studio and transfer tables from SQL Server Enterprise database to the SQL 2005 Server Mobile database?

By the way, I could not fine the “SQLMobile.sql” file mentioned in the article named “Step by step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005”, which I downloaded from Microsoft website. Anyone knows?

Thanks in advance.

There is a MSI in that white paper which you need to download to get SQLMobile.sql.

You can use SQL Server Integration Services to transfer the data between different data bases.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Laxmi NRO MSFT ,

Thank you very much for your help.

I downloaded the MSI and finished the "Step by step" exercise. It is very helpful and I have the confidence to finish my project now.

|||

Thanks alot and pleasee feel free to post your comments on the paper so that we can keep updating it for more and more usefulness.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hi,

I want to import tables and data in them from an access database(.mdb) to .sdf file (i.e SQL 2005 compact Edition).

Could you please guide me the steps to do this.

Thanks and regards

G Sreenaiah <gsreenaiah@.msn.com>

mdb query to adp function conversion failed

Good evening everybody! This is my first post here, necessary because I have recently upgraded from Access *.MDB to Access *.ADP :)

I have a problem with a query that does not appear to have converted correctly. Below is the SQL of the *.mdb and the *.adp. I would be very grateful if somebody could indicate the correct format for the ADP code. I cant find an example in the help file :(

Mdb Query SQL ==========================
SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=[Forms]![frmMainQuote]![QuoteLoadSize]));

Adp Function SQL =========================
ALTER FUNCTION qryGetTonne1 (@.Forms___frmMainQuote___CboLorr varchar (255)
)
RETURNS TABLE
AS RETURN (SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=@.Forms___frmMainQuote_ __CboLorr)))

Any comment gratefully receivedUp-sizing problems

I still havent found the correct syntax for a query in an adp file. However I did find two more upsizing problems for which I have found a cure, these can be seen in the attached Access 2000 *.mdb file. Run it in mdb then run the upsize wizard and note the problems.

Change True to 1 and False to 0
The first upsizing problem I had was that in an *.mdb file you can use True or False in an SQL statement. However when you upsize you need to change the True to 1, and the False to 0. It might pay, when you are designing your database to think about using 1 and 0 to indicate true or false situations.

Using forms collection can cause a problem
The other problem related nicely to the query problem mentioned above. I found that some code in my form frmSeeTown did not work because the SQL contained a reference to a combo box via the forms collection in the form of:--

[Forms]![frmSeeTown]![cboSelectCounty]

This relates to my query problem because the query with the problem also contained a reference to a combo box through the forms collection. Therefore I am reasonably sure that you cannot reference a combo box this way in an SQL string in an Access Project file. However Im right at the bottom of the learning curve so please educate me.

Now down to the problem!
If you look at the attached *.mdb file you will see that there are three functions behind the form frmSeeTown they are called:-

fBuildStr1() Assigns data from the combo box with me.cboSelectCounty
fBuildStr2() Assigns data using the forms collection (Dont work in *.asp)
fBuildStr3() Assigns data from a variable.

To view the effect of the different methods change the called function name from fBuildStr3 to fBuildStr2

The following NOTES have been extracted from the form frmSeeTown module
'Both of these functions "fBuildStr1" and "fBuildStr2" work OK in an mdb, but "fBuildStr2" won't work in an adp.
'So there are 4 possible options ------

'1) It maybe I'm using the wrong syntax? -- can't find anything in the help? So I think it's something else.
'2) Reference the combo box control with the Me. function. -- Doing it this way could be a problem with sub-forms
'3) Assign the combo box value to variable and use that in the SQL -- this has got to be the way to do it :)
'4) Something else, embarrassingly simple that I don't know about yet :) Hell that's why I'm posting...

'Since writing the above, I have now added a further function fBuildStr3 which assigns the combo box value to a variable and then uses that variable in the SQL code. This appears to work OK.. :||||Found this just now!!!

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=AxxessXP&Number=137340&Forum=AxxessXP&Words=access%20project&Match=Entire%20Phrase&Searchpage=0&Limit=25&Old=allposts&Main=137340&Search=true#Post137340

So I'm outa the woods at last (Famous last words) :) :)

Whos a happy bunny then!!!|||After several days of head scratching, Ive discovered that my original suspicion that there was a problem with the syntax was correct. If you look at the two lines of code below taken from an SQL statement both line 1) and line 2) work in Access mdb format, but only line 2) works in Access adb format.

1) "WHERE (((tlkpUkTowns.TownOnlyCounty) = [Forms]![frmSeeTown]![cboSelectCounty] )) ORDER BY tlkpTowns.TownTown"

2) "WHERE (((tlkpUkTowns.TownOnlyCounty) = " & Forms![frmSeeTown].cboSelectCounty & ")) ORDER BY tlkpTowns.TownTown"

As you can see the SQL in line one is incorrectly formatted. It appears that the SQL engine for an mdb file is more forgiving, and corrects minor mistakes in the SQL string automatically. So I am inferring from this that an adp file uses a different engine to handle the SQL statements. This would make sense, as there are differences in the SQL between the two access formats. However I say again I am very low down on the learning curve and would welcome any input.sql

mdb file support UTF8

i create mdb file in VB.NET project in vs2005 . i want that it support UTF8 for arabic language.

thanks .

There are two ways to do that you can create typed dataset with UTF8 or use some configuration provided by Microsoft, so there is no standard way to do it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/aa662944(office.11).aspx

http://office.microsoft.com/en-us/ork2003/HA011402281033.aspx

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