Cannot Pull table with RDA

Question

Hi,
I'm using the following code to extablish a rda between my laptop and windows mobile classic 6 emulator to pull down a table named customers from a db named ristobar, having configure an account to access to the database which has db_owner permissions (user id=MobileUser, password=pippo)
Anyway I always get the following exception:

Source : Microsoft SQL server 2005 Everywhere Edition
Native Error: 28627
Message: Failure to open SQL Server with given connect string
HResult: 80004005

Below follows IIS 7.0 logfile.
The Ip address of my laptop is 192.168.0.199 and its name is SKYWALKER

What am I doing wrong?
Thank you
BEholder



Public Class Rdademo

    Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=\SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"
    Dim rdaUrl As String = "http://192.168.0.199/ristobardb/sqlcesa30.dll"  ' The ip is correct
    Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf"

    Private Sub RdaPull()
        Try
            If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My_ Documents\Rdadb.sdf")
            Dim engine As New SqlCeEngine
            engine.LocalConnectionString = localConnection
            engine.CreateDatabase()
            engine.Dispose()
         
            Dim rda As SqlCeRemoteDataAccess = Nothing

            rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
            rda.InternetUrl = rdaUrl
            rda.LocalConnectionString = localConnection

            rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOnWithIndexes, "Error Table")
            rda.Dispose()

            MessageBox.Show("Done!")

        Catch ex As System.Data.SqlServerCe.SqlCeException
            MessageBox.Show(ex.Source)
            MessageBox.Show(ex.NativeError)
            MessageBox.Show(ex.Message)
            MessageBox.Show(Hex(ex.HResult))
        End Try
    End Sub



IIS 7.0 Log:

Software: Microsoft Internet Information Services 7.0
#Version: 1.0
#Date: 2007-08-29 14:28:54
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
2007-08-29 15:21:33 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:35 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:38 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:40 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:43 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:47 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:49 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0

Answer 1

Dears,
I'm trying to set up a rda connection through the pocket pc emulator (standalone version 1.0) and my laptop which runs Windows Vista Home Premium. To start up the rda I first need to connect from my emulated device to my laptop which runs IIS 7.0 (complete installation with compatibility toward IIE 6.0). Connection to http://localhost from my laptop is working fine.

I start the emulator (Pocket Pc with Windows Professional) and cradle the emulated device.
Connection starts and I'm able to browse from my pc the content of the emulated device or to synchronize it.
Then I run Internet Explorer on the emulator and try to connect to http://mycomputername (correct) but I receive a message which says that it cannot connect to the page I was looking for because connection was lost.
Any suggestion on how to solve this issue?
Kind regards and many thanks in advance
Cristian


It seems that now I can connect to http://mycomputername and I'm able to browse the net with my pocket pc emulator.
Anyway when I'm attempting to connect to htpp://mycomputername/subfolder/sqlcesa30.dll (the path is correct, I receive the following error message: "The page cannot be displayed or downloaded because the connection was lost. Check the connection and try later"..which is the same message I've been receiving before)

What shall I do?

Answer 2

Hi,
it seems that I can connect to http://mycomputernae and http://mycomputername/virtualfolder/sqlcesa30.dll only if my laptop is connected to the local network or to the internet.
Does anybody know why?
Thankyou

Answer 3

Change your OLEDB connectionstring:

 

Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"

 

or

 

Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=192.168.0.199;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"

Answer 4

Thanks for your reply Erik,

unfortunately it didn't work.

I tried both using Data Source=SKYWALKER and using Data Source=192.168.0.199 but I am receiving the same error.

Shall I use a particular port to connect to the SQL server?

By the way....reading other posts I noticed a couple of things: 1) shall I indicate the full path of the SQL server ? That is, if SQL Server Express is running on my machine (default name SQLEXPRESS): 192.168.0.199\SQLEXPRESS,1433 ? Port 1433 should be opened, is that right?

2) Shall I specify TCP/IP connection inside the connection string even if I am using a pocket pc emulator configured to use DMA access or it is not necessary?

Thank you for your help

Cristian

Answer 5

You did not mentions SQL Server Express... Yes, a connection string with IP address and \SQLEXPRESS,1433 is required and port 1433 must be open for connections.

It is not required to specify TCP/IP connection in the connectionstrin when cradled via DMA.

 

Answer 6

OK, it seems that somehow things are beginning to work, infact I am getting a new error message.
First of all I must say that now I am able to connect to SQL Server Express through SQL Server Management Studio Express using MobileUser as user id and pippo as password.

I run the same code that i posted before, changed the connection string to
"Provider=sqloledb;Data Source=localhost\SQLEXPRESS;User ID=MobileUser;Password=pippo;Initial Catalog=ristobar;"

and I get the following error:
Number: 29022
Description:The version of the Microsoft OLE DB Provider for SQL Server is not correct. Install MDAC 2.8 or later.
HResult: 0x80004005

What do I have to install now?
Thanks for your kind help
Cristian

Answer 7

OK, now we are getting closer

 

You need to install the latest version of the SQL Compact Server Tools, build 5300. See this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1368182&SiteID=1 for installtion tips

(install the msi from an elevated command prompt!)

 

The latest version of the tools can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=4e45f676-e69a-4f7f-a016-c1585acf4310&displaylang=en

 

After installation, the sqlcesa30.dll file should be build version 5300.

 

Also see this KB article, under the heading: Known issues for SQL Server Compact Edition 3.1 on Windows Vista

http://support.microsoft.com/?kbid=920700

Answer 8

 

Thanks again for your help.

I will try to install SQL Compact Server Tools at the link you specified from the command prompt (and will look to known issues on Vista)

Anyway I was pretty sure to have already installed that build...perhaps not in the right way.

 

If I am getting the same error after the installation sould I install MRDAC 2.8 or MRDAC 2.8 SP1 to solve this issue?

Cristian

Answer 9

MDAC is not supported on Vista, so no.

 

Answer 10

So we must succeed

Answer 11

...done!
Now rda.pull is working properly: the table is downloaded correctly into the file ristobar.sdf.
As I can see on Query Analyzer all records are there.

Anyway I still got an issue regarding rda.dispose() : it crashes the emulator.

The pull command is executed as below:

rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOff)

MessageBox.Show ("Done!")

breakpoint      rda.dispose ()

If I remove the breakpoint or the corresponding line of program the emulator won't crash, otherwise It will got stuck and will have to be shut down.
What should it depend on? A misconfigured time-out on SQL Server Express or on IIS ?
Thank you!
Cristian

Answer 12

Try to catch any exceptions as per the pattern in this: http://msdn2.microsoft.com/en-us/library/swwy8y74.aspx

To get all the error information look at this sample: http://msdn2.microsoft.com/en-us/library/ms174079.aspx

Answer 13

Well, having read your suggestion I changed my code a little.
I'm executing the following subroutine to manage the rda.pull and to catch possible exceptions. Unfortunately the emulator still get stuck where highlighted.
Thanks
Cristian

 Private Sub RdaPull()
        Dim rdaOleDbConnectionString As String = "Provider=sqloledb;Data Source=localhost\SQLEXPRESS;User ID=MobileUser;Password=pippo;Initial Catalog=ristobar;Persist Security Info=True;"
        Dim rdaUrl As String = "http://192.168.0.199/ristobardb2/sqlcesa30.dll"
        Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf"
        Dim rda As SqlCeRemoteDataAccess = Nothing

        Try
            If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My Documents\Rdadb.sdf")

            Dim engine As New SqlCeEngine
            engine.LocalConnectionString = localConnection
            engine.CreateDatabase()
            engine.Dispose()
          
            rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
            rda.InternetUrl = rdaUrl
            rda.LocalConnectionString = localConnection
           
            rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOff)

            MessageBox.Show("Done!")

        Catch ex As System.Data.SqlServerCe.SqlCeException
            MessageBox.Show(ex.Source)
            MessageBox.Show(ex.NativeError)
            MessageBox.Show(ex.Message)
            MessageBox.Show(Hex(ex.HResult))
        Finally
            rda.Dispose()
        End Try

Answer 14

Is the sub that I posted well formed?

Answer 15

OK, now we are getting closer

 

You need to install the latest version of the SQL Compact Server Tools, build 5300. See this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1368182&SiteID=1 for installtion tips

(install the msi from an elevated command prompt!)

 

The latest version of the tools can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=4e45f676-e69a-4f7f-a016-c1585acf4310&displaylang=en

 

After installation, the sqlcesa30.dll file should be build version 5300.

 

Also see this KB article, under the heading: Known issues for SQL Server Compact Edition 3.1 on Windows Vista

http://support.microsoft.com/?kbid=920700

Yeeeaaahhh!!! Esa versión del sqlcesa30.dll me funcionó sobre Windows 7, gracias MVP!!!