how to make a connection to a SQL Compact server 4

[44nato44]

Hi

I have installed a SQL compact server 4 and cannot get a connection string to work with it.

I looked here

http://www.connectionstrings.com/

but could not get any to work (I probably am doing something wrong)

The code below gives this error in the iis log : |5|80004005|- 80

I hope somebody can help ?

<%
Set Cnxn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=E:\Migrate to Task Manager\TriActis\TaskManager.sdf;ssce:database password=Secret"
Cnxn.Open strCnxn
cmd.ActiveConnection = Cnxn
cmd.CommandText = "SELECT * FROM tblCompany"
While Not pRS.EOF
WScript.Echo pRS(0)
pRS.MoveNext
wend
%>

 

[xwb]

Try it without the provider part: just the data source.

 

[44nato44]

Then I get this error : 6|80004005|[Microsoft][ODBC_Driver_Manager]_Data_source_name_not_found_and_no_default_driver_specified 80

With this line : strCnxn = "Data Source=c:\TaskManager.sdf;ssce:database password=Entity48*

 

[xwb]

Looks like it needs a driver. Open a cmd prompt, type odbccp32.cpl. Wait for the ODBC Data Source Administrator dialog to pop up. This will be in the User, System or File DSN tab. Look for the line that says (*.sdf). The part in the driver column is the driver string. Add

Driver=<driver string>;

to your connection string.

 

[44nato44]

I am not able to find odbccp32.cpl .. I am on Windows 7

 

[xwb]

Not sure what it is called on Windows 7 but there is another way. Click on Start/Help and Support

In the dialog box, type odbc and click on the magnifying glass

Expand How do I open the ODBC Data Source Administrator
Click on Click to open ODBC Data Source Administrator

 

[xwb]

Just found out: it is called

odbcad32.exe

 

[44nato44]

Was not able to find anything with sdf and I tried to set up another datasource with my database but it would not let me. It was not able to open the database it said.

It will give me two options to connect to the DB windows or SQL.. but the compact databases only have a password.

Not sure if I am running the wrong way or what I might be doing wrong ?

 

[xwb]

I've just installed it. Try

Driver=SQL Server

 

[44nato44]

Maybe I am doing something wrong :

I go to File DSN
Click Add
Choose SQL Server
Select My File
And then click finish on the following screen and then I get the error on the attachment.

Hope it makes sense to you

 

[xwb]

Sorry - just reread your title. I was using 2005. I'll try installing 4.0 and see what happens.

 

[xwb]

Sorry for misleading you. I thought there was an ODBC driver for SSCE but it appears there is only one for SS but not for SSCE. Try this. Put the following in a file called sqtest.vbs

Set Cnxn = CreateObject("ADODB.Connection") 
strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=E:\Migrate to Task Manager\TriActis\TaskManager.sdf;ssce:database password=Secret"
WScript.echo strCnxn
Cnxn.Open strCnxn 
WScript.echo "connection successful"
cmd.ActiveConnection = Cnxn 
set pRS = Cnxn.Execute("SELECT * FROM tblCompany")
While Not pRS.EOF 
   WScript.Echo pRS(0) 
   pRS.MoveNext 
wend

Run it using

cscript sqtest.vbs

It will probably fail after the connection string with an error. This time it will tell you what the error is in words instead of numbers.

 

[44nato44]

Thanks so far.. now I get

Micosoft SQL Server Compace OLE DB Provider: Unspecified error


Does not say much....at least not to me

 

[xwb]

At a guess it is something to do with the password.

1) Can you create a DB without a password. Then try connecting without a password. That should work and prove that your installation is OK.

2) Then try your original DB without a password. Do you get something like

Microsoft SQL Server Compact OLE DB Provider: Authentication failed. [,,,,,]

 

[xwb]

Are you trying to open a database created with 3.5 with 4.0? That will normally give an unspecified error.