[Resolved] - Remote Data Access - Error 80004005

[karahim]

Hi

I am trying to use RDA for a SmartDeviceApplication, while trying to PULL, it generates error:
Error Code: 80004005
Message : An internal eror occurred..
Minor Err.:28574
Source :Microsoft SQL Server 2000 Windows CE Edition

Environment Development/Database Server machine:

Windowns 2000 (SP4)
VS 2003, VB.NET, CF 1.0 SP3
SQL Server 2000 SP3a
SQL Server CE 2.0
IIS - created a virtual directory named SQLCE, and for SQL CE Server Agent specified HTTP content folder C:\Program Files\Microsoft SQL Server CE 2.0\Server\
ActiveSync 3.7
I am attaching the HTTP Authentication screenshot from IIS configuration, I doubt I am doing something wrong with "Account used for anonymous access"

Environment Pocket PC:
HX2750 HP iPAQ
Windows CE 4.21
ActiveSync 3.7 (cradle/USB)


Imports System.Data.SqlServerCe
Imports System.Text
Imports System.IO



Public Class Form1
Inherits System.Windows.Forms.Form
Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
Dim conn As New SqlCeConnection("Data Source=\My Documents\ssce.sdf")
Dim sqlCreateTable As SqlCeCommand = conn.CreateCommand()

' Connection string to SQL Server 2000
Dim rdaOleDbConnectString As String = _
"Provider=sqloledb; Data Source=home-pc;Initial" & _
" Catalog=Attendance;User Id=sa;Password="

' Initialize the RDA object.
Dim rda As SqlCeRemoteDataAccess = Nothing


#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
MyBase.Dispose(disposing)
End Sub

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnPush As System.Windows.Forms.Button
Friend WithEvents btnPull As System.Windows.Forms.Button
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents txtStatus As System.Windows.Forms.TextBox
Private Sub InitializeComponent()
Me.MainMenu1 = New System.Windows.Forms.MainMenu
Me.btnPush = New System.Windows.Forms.Button
Me.btnPull = New System.Windows.Forms.Button
Me.txtStatus = New System.Windows.Forms.TextBox
Me.Label1 = New System.Windows.Forms.Label
'
'btnPush
'
Me.btnPush.Location = New System.Drawing.Point(16, 80)
Me.btnPush.Text = "Push"
'
'btnPull
'
Me.btnPull.Location = New System.Drawing.Point(16, 56)
Me.btnPull.Text = "Pull"
'
'txtStatus
'
Me.txtStatus.Location = New System.Drawing.Point(56, 136)
Me.txtStatus.Size = New System.Drawing.Size(168, 20)
Me.txtStatus.Text = ""
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(8, 136)
Me.Label1.Size = New System.Drawing.Size(40, 16)
Me.Label1.Text = "Status"
'
'Form1
'
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.txtStatus)
Me.Controls.Add(Me.btnPull)
Me.Controls.Add(Me.btnPush)
Me.Menu = Me.MainMenu1
Me.Text = "RDA Example"

End Sub

#End Region

Public Sub createDB()
' if database exists, delete it and create a new one
If File.Exists( _
"\My Documents\ssce.sdf") Then
File.Delete("\My Documents\ssce.sdf")
End If
' Create a new database
Dim sqlEngine As New SqlCeEngine( _
"Data Source=" & _
"\My Documents\ssce.sdf")
sqlEngine.CreateDatabase()
txtStatus.Text = "DB Created successfully"
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
createDB()


End Sub

Private Function PullRetrieveRecordsFromSQLServer2000()
Try
rda = New SqlCeRemoteDataAccess
' http://localhost/virtual_directory_alias_name/sscesa20.dll
rda.InternetUrl = "http://home-pc/SQLCE/sscesa20.dll"
rda.LocalConnectionString = _
"Provider=Microsoft.SQLSERVER." & _
"OLEDB.CE.2.0;Data Source=\My Documents\ssce.sdf"
rda.Pull("Module", "Select * from Module", _
rdaOleDbConnectString, _
RdaTrackOption.TrackingOnWithIndexes, _
"ErrorTable")
MsgBox("Pull operation completed", _
MsgBoxStyle.Information, _
"Pull")
txtStatus.Text = "Pull Data to SQLServerCE successfully"
Catch err As SqlCeException
ShowErrors(err)
Finally
rda.Dispose()
End Try

End Function
Public Shared Sub ShowErrors(ByVal e As SqlCeException)
Dim errorCollection As SqlCeErrorCollection = e.Errors

Dim bld As New StringBuilder
Dim inner As Exception = e.InnerException

If Not inner Is Nothing Then
MessageBox.Show(("Inner Exception: " & inner.ToString()))
End If
Dim err As SqlCeError
' Enumerate each error to a message box.
For Each err In errorCollection
bld.Append((ControlChars.Cr & _
" Error Code: " & _
err.HResult.ToString("X")))
bld.Append((ControlChars.Cr & _
" Message : " & _
err.Message))
bld.Append((ControlChars.Cr & _
" Minor Err.: " & _
err.NativeError))
bld.Append((ControlChars.Cr & _
" Source : " & _
err.Source))
' Retrieve the error parameter
' numbers for each error.
Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If 0 <> numPar Then
bld.Append((ControlChars.Cr & _
" Num. Par. : " & _
numPar))
End If
Next numPar
' Retrieve the error parameters for each error.
Dim errPar As String
For Each errPar In err.ErrorParameters
If [String].Empty <> errPar Then
bld.Append((ControlChars.Cr & _
" Err. Par. : " & _
errPar))
End If
Next errPar
MessageBox.Show(bld.ToString())
bld.Remove(0, bld.Length)
Next err
End Sub
Private Function PushSQLServerCETableDataToSQLServer2000database()

' Initialize the RDA object.
Dim rda As SqlCeRemoteDataAccess = Nothing

Try
rda = New SqlCeRemoteDataAccess
' http://localhost/virtual_directory_alias_name/sscesa20.dll
rda.InternetUrl = "http://home-pc/SQLCE/sscesa20.dll"
rda.LocalConnectionString = _
"Provider=Microsoft.SQLSERVER." & _
"OLEDB.CE.2.0;Data Source=\My Documents\ssce.sdf"
rda.Push("Module", rdaOleDbConnectString, _
RdaBatchOption.BatchingOn)
MsgBox("Push operation completed", _
MsgBoxStyle.Information, "Push")
txtStatus.Text = "Push Data to SQLServer2000 successfully"
Catch err As SqlCeException
ShowErrors(err)
Finally
rda.Dispose()
End Try

End Function



Private Sub btnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPull.Click
PullRetrieveRecordsFromSQLServer2000()

End Sub

Private Sub btnPush_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPush.Click
PushSQLServerCETableDataToSQLServer2000database()

End Sub


End Class


Please advise.

[Strider]

you should use the IP Address of the machine for everything.
Do not use the hostname(pcname), localhost or 127.0.0.1

[karahim]

Thanks Strider for your reply.

On using IP address getting:
Error Code: 80070057
Message : An error has occured on computer running IIS.
Minor Err.:28022
Source :Microsoft SQL Server 2000 Windows CE Edition

[Strider]

read the end of this article http://www.codeproject.com/netcf/PocketPCwithSQLCE.asp

are you trying it on the emulator or on the PDA

[karahim]

no, i am deploying it on device using debug option.

[karahim]

please note that I have updated my last reply.

[Strider]

open your internet browser on the device and and go to http://<your ip address>//SQLCE/sscesa20.dll

does it show the web page with "SQL Server CE Server Agent"?
is it requesting a username and password?

[karahim]

It is showing:
Server Configuration Error
The server has encountered a configuration error attempting to process your request. The configuratin parameter MD_ANONYMOUS_USER_NAME (6020) has an invalid value. Please contact the server administrator for assistance.

[Strider]

Sounds like permission on the folder or problems with the IIS IUSER account

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q201851

[karahim]

yes, there was a problem with IIS IUSER account, it is now settled and I may access SQL Server CE Server Agent from device. When I deploy the application and try to RDA PULL, it generates error:
Error Code: 80004005
Message : An internal eror occurred..
Minor Err.:28574
Source :Microsoft SQL Server 2000 Windows CE Edition

[Strider]

can you post the code of your pull function

[karahim]

Try
rda = New SqlCeRemoteDataAccess
' http://localhost/virtual_directory_alias_name/sscesa20.dll
' rda.InternetUrl = "http://127.0.0.1/SQLCE/sscesa20.dll"
rda.InternetUrl = "http://203.221.83.180/SQLCE/sscesa20.dll"
rda.LocalConnectionString = _
"Provider=Microsoft.SQLSERVER." & _
"OLEDB.CE.2.0;Data Source=\My Documents\ssce.sdf"
rda.Pull("Module", "Select * from Module", _
rdaOleDbConnectString, _
RdaTrackOption.TrackingOnWithIndexes, _
"ErrorTable")
MsgBox("Pull operation completed", _
MsgBoxStyle.Information, _
"Pull")
txtStatus.Text = "Pull Data to SQLServerCE successfully"
Catch err As SqlCeException
ShowErrors(err)
Finally
rda.Dispose()
End Try

[Strider]

Here is my code for RDA. Maybe substitute your values in for mine. If that doesnt work it is probably a problem with permissions on SQL Server


Private m_cn As SqlCeConnection
Private m_rda As SqlCeRemoteDataAccess

Private m_localDB As String = "\My Documents\memodb.sdf"
Private m_localConn As String = "Data Source=" & m_localDB & ";" & _
"Password=pwd;" & _
"Encrypt Database=False"

Private m_remoteConn As String = "Provider=SQLOLEDB;" & _
"Data Source=xxx.xxx.xxx.xxx;" & _
"Initial Catalog=RDAdata;" & _
"User ID=sa;" & _
"Password="

Private Sub btPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btPull.Click
Try
btPull.Enabled = False

If Not File.Exists(m_localDB) Then
Dim l_sqlEngine As New SqlCeEngine
l_sqlEngine.LocalConnectionString = m_localConn

l_sqlEngine.CreateDatabase()
l_sqlEngine.Dispose()
Else
If m_cn Is Nothing Then
m_cn = New SqlCeConnection(m_localConn)
m_cn.Open()
End If

Dim l_cmd As SqlCeCommand = m_cn.CreateCommand

l_cmd.CommandText = "DROP TABLE FieldMemos"
l_cmd.ExecuteNonQuery()

If m_cn.State = ConnectionState.Open Then
m_cn.Close()
End If
End If

m_rda = New SqlCeRemoteDataAccess

m_rda.InternetLogin = "RDAuser"
m_rda.InternetPassword = "rda"
m_rda.InternetUrl = "http://xxx.xxx.xxx.xxx/MyRDA/sscesa20.dll"
m_rda.LocalConnectionString = m_localConn

m_rda.Pull("FieldMemos", "SELECT * FROM FieldMemos", m_remoteConn, RdaTrackOption.TrackingOnWithIndexes, "FieldMemosErrorTable")

PopulateDataGrid()
Catch ex As SqlCeException
Dim l_sqlerr As SqlCeError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
Catch ex As Exception
MsgBox(ex.Message)
Finally
m_rda.Dispose()
btPull.Enabled = True
End Try
End Sub

[karahim]

Thanks Strider for your help, I will try your code and respond.

There is one question, the required table should exist on SQL CE or not necessary? currently I do not have that table created on SQL CE.

[Strider]

You need to create the database file (the .SDF).
During the RDA Pull it will create the table on the device with any indexes required

[karahim]

Thanks,

This is what I am already doing, I just wanted to make sure about it.

[karahim]

Hi Strider, using your and mine code I am now getting:
Error Code: 80004005
Message : SQL Server CE encountered problems in opening the SQL Server CE database. [,,,Database name,,]
Minor Err.:28559
Source :Microsoft SQL Server 2000 Windows CE Edition

My CE database file ssce.sdf exist on pocket pc.

Please advise.

[Strider]

on what line of code is the exception thrown?




are you specifying the IP Address of the SQL Server?

Dim rdaOleDbConnectString As String = _
"Provider=sqloledb; Data Source=<IP ADDRESS>;Initial" & _
" Catalog=Attendance;User Id=sa;Password="

[karahim]

The exceptions are thrown on
rda.Pull("Module", "Select * from Module", _
rdaOleDbConnectString, _
RdaTrackOption.TrackingOnWithIndexes, _
"ErrorTable")

While executing the pull function, the Norton Security was showing alert:
SQL Server – Mixed Mode
Windows SubSystem is attempting to access the internet..

Then I disabled the Internet Security and run the application, now it is showing two errors:
First Error Code: 80004005
Message : An internal eror occurred..
Minor Err.:28574
Source :Microsoft SQL Server 2000 Windows CE Edition

Second Error Code: 80004005
Message : [DBNETLIB]
[ConnectionOpen (connect()),]
SQL Server does not exist or access denied
Minor Err.:17
Source :Microsoft OLE DB Provider for SQL Server 2000

[Strider]

Is the firewall allowing your IP address to access sql server? i presume you can access it throught the enterprise manager

what version of sqlce20sql2k did you install cos there are diff rules for them

There are three different installation files for SQL Server CE Server components.

Here's the rules

If the machine running IIS is not running SQL Server use the following installation file:-

sqlce20sql2ksp1.exe.

If the machine running IIS is running SQL Server then use the following installation file:-

1) For SQL Server with no service pack or service pack 1 use: - sqlce20sql2ksp1.exe
2) For SQL Server with service pack 2 use: - sqlce20sql2ksp2.exe
3) For SQL Server with service pack 3 use: - sqlce20sql2ksp3.exe

[karahim]

Thanks a lot Strider for your support.
After so many hit and tries that include un-installations and re-installations of SQL Server and its service pack, I succeeded in pulling table from SQL Server. Note: It is only executing successfully when I am disabling the Norton Internet Security. Personal Firewall is ON and having default setting.

FYI, I am using Service Pack 3a, which is shipped with my SQL Server 2000.

[karahim]

Strider, When I am trying to rate your post, the system gives me message “You must spread some reputation before giving it to Strider again”. What is the procedure to spread reputation?

[Strider]

Good to hear it works finally.
I know it can be very difficult mainly due to all the security configuration you need to do