ads wizard: Failure to open SQL Server with given connect string

Hi, sorry but I am just getting my feet wet with the ADS wizard. I have managed to get the PPC emulator running the ADS wizard and have the emulator ActiveSync connected with desktop. Port 1024 opens from the emulator to the desktop. On the ADS wiz I can connect to the device .sdf. WHen I try to connect to the desktop I am getting a connection failure in the ADS wiz log and after stepping through the code VS watch window gives the following message for the SQL exception:

Message "Failure to open SQL Server with given connect string. [ connect string =
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.accdb; Jet OLEDBystem
Database=C:\\Documents and Settings\\vhaporellinr.POR-EE778886\\Application Data\\Microsoft\\Access\\system.mdw ]" string

I do not know enough to know where to go next to move past this no-doubt simple problem. Can you direct me to the appropriate docs or refs to start digging into looking for solutions?

Is there a particular Northwind.mdb file that is supposed to be used?

Are we talking some kind of permissions issues on the database?

I was assuming I did not have to install SQL Server 2005 and the SQL Server management console(?) to do development with the SSCE. Is that correct?

THank you for any helping pointers you can provide.

Try this connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\NorthWind.accdb;Persist Security Info=False

|||Hi Erik. Thanks for the string. Still having problems though. I am going to step back and run through the early tutorials which work with the Northwind.mdb and test.sdf files. This Northwind I am using might be the problem. TIme to pay some dues I guess. Thanks again for the help.|||

Ran into snags on Tutorial 1. Posted problem in this forum. Tutorial 2 worked pretty good. Dropped back to fussing around with access synchcronizer app. Messed with mdw file and other configuration settings. Changed to Access 2003 mdb file. Now application will not run on emulator at all. Same IIS problem when running on device.

I am going to temporarily abandon syncronizing to access database for now. One of these days I will try it on another computer, on a private network.

|||

I tried the ADS wizard installation on another computer on a private network. Tried Access 2003. At first received similar problems. Went back to readme.doc and examined it more precisely. Checked the registry, things looked ok. A Desktop IE would not open to the ads desktop server port. Went back to the XP Firewall settings. Added a 1024 port in the exceptions tab. Ran the ADS wizard exe on the PPC emulator ( had not tried that beforealways had used VS2005 debugger). The ADS wizard connected up to both device and desktop databases and pulled a 6MB database down to the device!

Things are looking pretty good right now on this system. Will check out the other machine/network Firewall settings in a few days. So far, it looks like it probably was operator error on my part causing all my problems.

Thank you Microsoft for investing in this SQL Server Compact Edition and providing this ADS Wizard application and source. The technology looks like the perfect fit for two of my current projects.

|||

Today I went back to previous problem system. I got the ADS Wizard to run on the PocketPC emulator after tweaking the firewall network card port settings and downloading and installing the .CAB file that ships with the syncapp package.

The ADS Wizard.exe that the .CAB file installs works great now on the emulator and will suffice until I need a custom app.

Ado Xml

Im getting an XML String from the server which I load into an ADO Recordset for display.
Im use the XMLDomDocumentPtr
and then use ADO Recordset.Open method for the XMLDocument.

All this works fine. The only problem i have is it is very slow. It takes 2seconds for just 1000 records which is not good.

Any help is appreciated...My experience with large recordsets mirrors yours; it does get slow (though I generally began experiencing issues at around 10,000 records). The data is being loaded into an object resident in memory. I usually stick to this technique when recordsets are smaller than 1,000 rows.

Regards,

hmscott|||My first observation would have to be that if you are returning rows for the user to peruse, what are the chances that they'll actually look at more than ten? While recordsets can be huge, my users rarely care about sets with more than twelve rows, and never care about more than 25, so a limit of a thousand seems silly to me!

Next order of business is data size... XML tends to produce largish rows, often in the 2Kb to 10 Kb range. If you figure out how much data that amounts to for a thousand rows, your application is having to slog through quite a bit of data, and that data is often coming down a relatively slow line. If this is your case, just moving and reformatting the data takes a while.

While you might be an exception to the rule and have a perfectly valid reason to do what you are doing, nearly every case I've seen where someone complains about performance for this kind of thing has hinged on a design flaw. Please examine what you are doing, and the performance you are complaining about, and if the problem isn't apparent please give more details and I'm sure that someone here can offer you suggestions.

-PatP

ADO doesnt work without ODBC. Why?

In my application VB, I am effecting the connection with the SQL Server it saw connection string, without ODBC, thus does not function.

I need to create any ODBC connection to complete the connection. Why?

My Connection string:
"provider=SQLOLEDB;UID=Etiquetas;PWD=xxxxxxxx;DATAB ASE=Etiquetas;Server=BROMO;"
or
"driver={SQL SERVER};UID=Etiquetas;PWD=xxxxxxxx;DATABASE=Etique tas;Server=BROMO;"ADO is high level or top layer for OLE DB, it can handle ODBC too cauze Microsoft OLE DB Provider for ODBC drivers

see http://www.able-consulting.com/tech.htm for tons of connection samples...

OLE DB is generally faster than ODBC...

jiri

ADO Connection String not working when SQL on local machine

Here is the connect tring from table properties:
ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates

Here is the connect string from the ADO .Open connect string:
"ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"

Here is the ADO .Open code.
Set CNN = New ADODB.Connection
Dim strDEFConn As String
strDEFConn = FixConnStr(DEFCONN)
CNN.Open strDEFConn

The last line fails with the CNN.Open strDEFConn with this message:
Run-time error '-2147467259 (80004005)';
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

This code works when the SQL Server is on it's own server, but for testing at my own office, I have SQL Server on the same machine as the Access application. I'm getting the above error where SQL Server and the Access app are on the same machine. I can open a linked SQL table from the user interface, and VBA code that deals with the tables as Access tables works. It is the ADO .Open statement where the error happens.

Thanks for any help you can give me on getting this to work.Here is the connect tring from table properties:
ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates

Here is the connect string from the ADO .Open connect string:
"ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"

The error message is basically telling you that it can't find the DSN entry.

Okay, so you are using ODBC (which you have to because it's Access), how do you have the DSN configured? Is the DSN a user DSN or a user DSN. I'm not entirely clear on what it is that you are doing, but if the script is executed as a batch file using a system account, I think it won't find the DSN unless the DSN is a "system" DSN.

By the way, those connection string look a little odd to me. Did you check out www.connectionstrings.com?

Regards,

hmscott|||I think these are DSNless connections.

Ordinarily I have a switch in my apps that change the server to (Local) when developing and that pull up the server name when run by a normal user. However I am not sure how this will work with a named instance. Is there more than one instance on VICRAUCH?|||Yes, I have more than one instance on VICRAUCH. What I have to switch between different servers and databases is a local table in Access which I name "tblSetup" This is the table that holds all my "macro" variables. I use the term "macro" as in large scope. Those that relate to the app as a whole. This Access db seems to have had 3 or 4 different developers that have put it together and each one has sort of started over with their own methodology. One of my goals is to try to standardize the app without doing a complete re-write. But at the same time, be very responsive to this client when they want something done yesterday. This is continuing to be quite the challenge.
Because I am so new to SQL Server could you give me some insight as how you do the "switch" thing with the server from production to local?
Thanks,|||The "switch" is trivial - I meant (programmatically) swapping:
ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidateswith
ODBC;DRIVER=SQL Server;SERVER=(Local);APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates
However - you are connecting to a named local instance. I'm not sure how that would work. I don't have a named (local or otherwise) instance here at work but I do at home. I will try tonight if no one else sorts it out for you by then.|||What I did to get the named instance to work was "VICRAUCH\SRVRVGSMISC" as the Server= name rather than just SRVRVGSMISC. What you posted here is what needs to be done, and actually what I'm doing by changing the values in my tblSetup. I have in tblSetup the proforma connection string, Driver={SQL Native Client};Server=srvrName;Database=SQLdbName;Uid=use rName;Pwd=;and then before setting that connection string, I replace the placeholders in the proforma string with what is in the coresponding field of tblSetup.

Thank you very much for your help.

ADO Connection String Error for Mirrored Database

I am trying to connect to a failover partner using ADO (non .Net) and Visual C++. ADO is throwing the following exception:

Caught: Unspecified error - Invalid connection string attribute(Microsoft OLE DB Provider for SQL Server)

Connection String:
Provider=SQLOLEDB.1;Data Source=Edwardsville\SQLSERVER;Failover Partner=Mirror2\SQLSERVER;Initial Catalog=SomeCatalog;User Id=SomeId;Password=SomePassword

Any ideas?

As far as I know this is only supported in ADO.NET 2.0.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi, Im trying to do the same thing.

Could you get any answer to this? Its possible with ADODB ?

Thanks

ADO Connection String Error for Mirrored Database

I am trying to connect to a failover partner using ADO (non .Net) and Visual C++. ADO is throwing the following exception:

Caught: Unspecified error - Invalid connection string attribute(Microsoft OLE DB Provider for SQL Server)

Connection String:
Provider=SQLOLEDB.1;Data Source=Edwardsville\SQLSERVER;Failover Partner=Mirror2\SQLSERVER;Initial Catalog=SomeCatalog;User Id=SomeId;Password=SomePassword

Any ideas?

As far as I know this is only supported in ADO.NET 2.0.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi, Im trying to do the same thing.

Could you get any answer to this? Its possible with ADODB ?

Thanks

ADO Connection port with clustered servers

I am using ADO to connect to clustered SQL Servers, how do I know which port to use?
My current connection string format is:
Provider=sqloledb;Network Library=DBMSSOCN;Data Source=<server ip>,<server port>;Initial Catalog=<my catalog>;/<user>/<pass>
But with clustered servers this will not work as ports are assigned dynamically.
Thanks
If the client machine is using MDAC 2.6 or greater it will figure out the
port number.
MDAC sends a UDP packet to the server to gather this information before
sending the login packet.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||So should I just not include the port number in the connection string?
"Kevin McDonnell [MSFT]" wrote:

> If the client machine is using MDAC 2.6 or greater it will figure out the
> port number.
> MDAC sends a UDP packet to the server to gather this information before
> sending the login packet.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

ADO Connection port with clustered servers

I am using ADO to connect to clustered SQL Servers, how do I know which port
to use?
My current connection string format is:
Provider=sqloledb;Network Library=DBMSSOCN;Data Source=<server ip>,<server p
ort>;Initial Catalog=<my catalog>;/<user>/<pass>
But with clustered servers this will not work as ports are assigned dynamica
lly.
ThanksIf the client machine is using MDAC 2.6 or greater it will figure out the
port number.
MDAC sends a UDP packet to the server to gather this information before
sending the login packet.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||So should I just not include the port number in the connection string?
"Kevin McDonnell [MSFT]" wrote:

> If the client machine is using MDAC 2.6 or greater it will figure out the
> port number.
> MDAC sends a UDP packet to the server to gather this information before
> sending the login packet.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>