ADO Connection to MSSQL Server 2005... Need help

Hi,

at first a few things about my system:
I use Visual Studio 2005 Pro on WinXP SP2 and I have recently installed MSSQL Server 2005 Express on my system.

I want to establish a connection to the MSSQL Server from C++ (not .NET) using ADO.

For this I use the following connection String:

Open(L"Provider=SQLNCLI;Server=FREECASTLE;Integrated

Security=SSPI;DataTypeCompatibility=80;MARS

Connection=True;",L"",L"",ADODB::adConnectUnspecified);

This works. But this is not exactly that what I wanted, because here

just my Windows Authentication is used. I want to be able to pass

username and password for users that are allowed to access the SQL

Server. Here my first confusion begins: I have the option to pass the username and password into the connection string (User Id and Password) or I guess that I can pass them also as parameter 2 and parameter 3. But with trying both of these options I get always a connection error that my specified Username is not be trusted ( I don't know how the message looks in the English version, because I have the german version), but I suppose you know what I mean.

Of course I have to create such accounts in the SQL Server before trying to login with them. I guess that I made something wrong when doing this. I started the Server Manager Studio and there I navigated into the Object Explorer. In the folder Security I created a new account for testing (using SQL Server Authentication). But I cannot use that account for logging in.

I have to admit that I am very new to the SQL Server thing, so I hope someone can help me or can give me hints on what to do better. I am very thankful for every reply...That specific error message means, that you only enabled your server for WIndows authentication, if you want to use also SQL Server authentication you will have to enabled it first (using mixed Authentication). See the Screencast on my site for more information about Changing the authentication mode in SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hi Jens,
with your Screencast I solved my problem. Thank you very much for your help.

ADO

Hi:

Is it true that a .net project of WinCE 5.0 pro can't access Win2000 SQL database thru ADO?

Any solution?

Chang

ADO.Net CE actually has a different forum. I'll move the thread over there.|||

no, not true. A .NET Compact Framework 1.0 or 2.0 project running on WinCE5 can indeed access a SQL Server 2000 database directly using the version of the SqlClient included with the COmpact Framework.

Darren

|||

Thank you Darren:

Can you give me the address to download the version of the SqlClient included with the COmpact Framework?

My purpose is to use a WinCE mobil computer to run a .net program and real time access my SQL2000. Someone suggest me to use sqlCE to sychronize with SQL2000? or use webservice but neither of these way were workable, I don't know what's wrong.

Jeanet

|||

If you are developing a Smart Device project in Visual Studio 2005, all you need to do to work with SQL Server 2000 using the SqlClient is add a reference to your project to System.Data.SqlClient version 3.0.3600.0. The SqlClient comes as part of the .NET Compact Framework, which is included with versions of Visual Studio that are capable of doing Smart Device projects (VSTS Developer, VS2005 Professional, etc).

In general, you should only be using SqlClient if you are reliably connected (WinCe device to server) during database operations. As this is often impossible to guarantee, most people use a local data cache on device (SQL CE or SQL Mobile are the common choices) and periodically synchronize data between the server and local data cache when a connection is available. A newsgroup is not the place to teach you how to implement this, but any of the books on the .NET Compact Framework cover this topic and the new Mobile Client Software Factory provides a reference implementation. http://msdn.microsoft.com/mobility/default.aspx?pull=/library/en-us/dnpag2/html/mcsflp.asp

Darren

|||

Thank you Darren:

I'm new to work with WinCE. My purpose is to use a handheld (Symbol 3090 running WinCE 5.0) to run a simple application (a .net 2005 program) over RF connection to real time access my SQL server.

I need clear instructions to setup the environment.

Your answer will be very appreciaated.

Jeanet