SQL Mobile Remote Data Access - Creating Upload-Only Tables

Hi Jim,

you´ll need to have the network connection open before trying to pull the data.
If there is currently no connection open and we would need the VPN-Connection to our company network to continue,
how can this VPN-Connection be established programmatically?
I can´t find any useful answers to this question over the web.
Thanks in advance.

Greate solution for a common problem out in the field. If you're placing new orders on a device out at a customer site, your peers who are doing the same thing with other customers don't necessarily want to download your new order to their device during their next RDA sync.

Thanks Rob - I'm a big fan of RDA because it is just so lightweiht - I know Merge Rep is necessary sometimes but RDA often fits so nicely and with so much less cost. :-)

I don't have the luqury of setting up IIS on my host machine due to security concerns.

Trust me, I can't get around this politically.

But I really need a way to communicate from the Handheld device to the computer.

Can you recommend how I may go about executing SQL queries against the SQL Server instance on the host computer without having to install IIS on the host machine? Thanks.

Dotnetfellow

dotnetfellow@yahoo.com

DotnetFellow: There are a couple of options.

One solution is to reach the SQL Server instance through an IIS instance on another machine. If you have a machine on the network that is running IIS and can see the SQL Server instance you can route through the IIS server by setting the InternetUrl property to the machine running IIS and then specifying the appropriate database server in the server connection string's Data Source.

Another alternative is to access the SQL Server instance using the classes in System.Data.SqlClient. These are the same classes one normally uses on a desktop or web app to access SQL Server. These classes connect directly to the SQL Server instance as a standard client using the TDS format. If you do go this route be very careful. Unlike HTTP, TDS is very connection-oriented and doesn't do well on spotty network connections. In general you want to stay connected to the SQL Server instance as little as possible. If you have large blocks of SQL to execute, I encourage you to put those in stored procedures to minimize how much SQL you have to send across the wire.

If the IIS solution I mentioned above is available, I encourage you to go that route. As a general rule, don't use the SqlClient library from a mobile device unless there's no other choice.

Hope this helps,
Jim

Hi Jim,

Thank you very much for your response!

Regarding your second alternative in your posting above, you state:

"Another alternative is to access the SQL Server instance using the classes in System.Data.SqlClient."

This requires a network connected device, correct?

IOW, am I correct in my understanding, that I cannot use this option if my device only has a USB connection to a PC?

Thank you again for your response.



Sincerely, -Dotnetfellow

dotnetfellow@yahoo.com

DotnetFellow;

Yes you do need a network connection but that's ok - as long as the device you have connected via USB has an Active/Sync connection between the desktop and the device. In this case, your device piggy-backs on the network connection of the desktop. This is called a "Desktop Pass-through" network connection.

To verify that the pass-through network connection is in place, connect the device to the USB port, wait for ActiveSync to connect and then use Pocket IE to browse to a Web Page within your network (browsing to a public web page can sometimes be a little tricker but internally you should have no trouble). Although the page may look garbled when viewed in Pocket IE due to the small screen size, you should be able to reach the page. This proves you're connected to the network so the SqlClient classes should work fine.

One other quick thing - when I wrote my last response, I was under the impression that you're using a WiFi connection. Because you have a desktop pass-through connection which is very stable as compared to WiFi, using the SqlClient libraries is actually a pretty safe solution. You're not much more likely to loose the devices pass-through network connection then the desktop computer is to loose it's hard-wired connection as long as the device maintains the ActiveSync connection.

Hi Jim,

Wow, I have been searching for someone with this experience who could guide me through this. I really appreciate
your help.

I have tried using the SqlClient class to connect to the database, but I haven't had much success. Here is my
setup:

Desktop/Workstation has Windows XP, ActiveSync, .NET Framework 2.0, and SQL Server 2005. The production Workstation
will have 2000; so I do want to ensure SQL Server 2000 compatibility with my solution.

Handheld device is an Intermec 751g with docking station, Windows CE 4.2, .NET Compact Framework 2.0 with SP1 and
latest patch.

Development tools consist of Visual Studio 2005 and C# .NET language.

Note that when I attempt to ping my desktop from the handheld command prompt I get "transmit failed, error code
11010".

Also, I am able to access the Internet from my handheld device.

My error is encountered when I execute this fill code:

int returnValue = this.Adapter.Fill(dataTable);

Please note that all of my code is generated by the VS 2005 IDE at this juncture, as I am simply trying to get the
plumbing to work.

Here is the error that I encounter:

System.Data.SqlClient.SqlException was unhandled
Message="SqlException"
Class=20
LineNumber=0
Number=6
Procedure="ConnectionOpen (Connect())."
Server="09Q-GHAZEL2\\SQL2K5"
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError()
at System.Data.SqlClient.SqlInternalConnection.OnError()
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Connect()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.AdventureWorksDataSetTableAdapters.ContactTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Program.Main()





Thanks again for your insight. Sincerely, -Dotnetfellow dotnetfellow@yahoo.com

Dotnetfellow - I'm flying down to NYC for the day tomorrow. I'll check out your latest reply on Thursday.

Jim

HI Jim,

i'm havin kinda the same problem as DotNetFellow, hope you could help me coz when i try to test my virtual directory which i created using SQL CE Connectivity Management,
all i could see in my Pocket PC IE is Cannot Connect.

how will i set up such connection to my virtual directory in my desktop? thanks in advance! hope you could help me!

Got it working.

First I had to figure out how to connect to the server specifically. I found this article here helped tremendously.


How to: Configure Express to accept remote connections

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx


Then I had to get the connection string right. Here is the connection string that worked:

Data Source=197.162.5.100,1433;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=ausername;Password=apassword;


Some things that I found a little confusing.

I can do a port scan using vxUtil from the handheld, against the target port, but I can not do a ping using vxUtil from the handheld, against the target ip address.


Also, when doing a port scan with vxUtil, a successful return status will look like this:

Port: 1433 is open
Port Scan Complete


If the port scan was unsuccessful then it will merely return:

Port Scan Complete


In the end, I am able to get the SqlClient namespace in .NET 2.0 CF to connect me to SQL Server 2005 on the host computer over a USB ActiveSync connection.



Finally, if you want to connect and debug from VS over a Ethernet drop, without the use of USB ActiveSync, you will find this article step you through that process.


Debugging on CE5.0 device without Activesync

http://blogs.msdn.com/vsdteam/archive/2005/04/28/413304.aspx

Thanks Jim.

I use RDA to upload new and updated records to the server. But ive got one problem. Users with the pocket pc, delete some old data often, therefore, when i push the data, the records that have been deleted on the ppc will be deleted from the main server too . Is there a way to stop deleting records from the main server?

Microsoft sucks

i am going to try this immediately. my question is, how can you determine the IP of the host machine?

Hi Jim,

Im trying to figure out how to make RDA work via a Mobile VPN tunnel connection, such as in Windows Mobile 6.

the .InternetProxyServer parameter does not seem to be enough.

Any ideas?


Thanks.