Data import / export with SQL Server Express using DTS Wizard

There are IMHO two major annoyances with SQL Server Express :
 - No Server Agent
 - No Import / Export function (called Data Transformation Services – DTS in SQL Server 2000)
 
Missing the Server Agent means you have to make your own backup (or other) scheduling, but luckily DTS is possible – it is just not installed as default with SQL Server Express (SSE). It’s very strange to me why Microsoft did not just include in the standard install so that it was available from SQL Server Studio, but I’ll settle for just being happy that it exists at all!

Check if you already have it by looking for this file: c:\%programfiles%\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe. If you don’t have a ‘DTS’ directory (or even a ‘90′ directory) it has not been installed.

Here is how you get it:
 - It is included in the SQL Server Express Edition Toolkit – and only that. It you have installed another version of SSE, it works fine to install this package afterwards without uninstalling the others. Get it here: http://go.microsoft.com/fwlink/?LinkId=65111
- The DTS Wizard is included in the option “Business Intelligence Development Studio” so be sure to select that for install 
- If you have installed another version of SSE, the installer might report that there is nothing to install. Override this by checking the checkbox that displays the version number (in the installer wizard)

After install has finished, the DTS Wizard is available at c:\<programfiles>\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe
you might want to make a shortcut, or even include it on the tools menu of SQL Studio.

ActiveSync and Comodo Firewall

I recently installed the Comodo Firewall since my old one Sygate FW has become obsolete, and although it seems quite nice and powerful there are some issues with it. One problem is that it often give false warnings about ‘parents’ (a program that launches another program), e.g. claiming that notepad tries to start Internet Explorer, when I have done it myself (this was a problem with Sygate too, but it actually seems worse here).

The other problem is that it seems to block quite a few things silently – for instance ActiveSync. To get it to work I had to turn off the whole thing, which might be a bad idea if you are not behind a hardware firewall. Seems like it blocks the mobile device from getting an automatic IP or somesuch. You have to make a custom rule to get it to work. I suggest you change this rule a little to allow a IP range instead of just the one IP just in case.  I’ll repeat the walkthrough by Egemen here in case the URL goes missing:

( in Comodo: )
1- Go to “Security->Nework Montitor”,
2- Right click on the first rule(Rule Id = 0)
3- Select Add Rule->Add Before
4-  Action “Allow”, Protocol “TCP”, Direction “In”
5- Source IP : “Single IP” = “169.254.2.1″,
6- Remote IP : “Any”
6- Source Port : “Any”
8- Remote Port : “Any”
9- Click Ok button.

My alternative is to change step 5 so that you get 101 available IPs:
5-Source IP : “IP Range” = “169.254.2.0″  to “169.254.2.100″

Thanks to this I at least learned that there are more reserved IP ranges than the 192.168.x.x range, and that the 192.254.0.0 to 192.255.255.255 is reserved for “Automatic Private IP Addressing” – making this rule quite safe to install.

(To be fair there are some things that I really like with Comodo, for instance that you can disallow certain URL or IPs for each program, so that you can allow a program to access the Internet, but you can disallow it from calling home by banning the home IP only.)

Reinstalling Compact Framework 2.0

I recently did a hard reset on my device, but had problems installing CF2 because the installer complained that I already had installed it. (This seems like a bug to me – what if I had two Windows Mobile devices?)

Anyway, I didn’t want to uninstall then reinstall CF2 as suggested by the installer because I was afraid that it would mess up my settings and paths in Visual Studio.

To manually install CF2 just follow the instructions as for setting up the emulator - i.e. install the file NETCFv2.wm.armv4i.cab.

Standalone Device Emulator 1.0

http://www.microsoft.com/downloads/details.aspx?FamilyId=C62D54A5-183A-4A1E-A7E2-CC500ED1F19A&displaylang=en

Didn’t catch this when it happened (2006-06-14) but MS have released a stand alone version of the emulator. Should be a nice tool for demos and presentations!

You might need the Virtual Machine Network driver too.

SQL Server Express

Recently I’ve been playing around with SQL Server Express, the free database (and replacement for MSDE) from Microsoft. It’s really quite amazing the features you get for nothing, and in my book its a real competitor to MySQL. A nice thing about the different versions of SQL Server are that they are upwards-compatible, ie. if you go from Express to the real version, you are not required to change any code (other than the connection string perhaps). So if you application outgrows the Express, it relatively painless to upgrade (except for the price of SQL Server that is

The stated limitations on SQL Express, is 1 processor, 1 gig of ram and 4 gigs of data in the database. It’s important to note that it will work just fine on a machine with more juice than this, only it will limit its use of the resources to those numbers.

In fact the SQL Express guys recommends installing it on a machine with better specs – if you have 1 gig or less of RAM the database must share it with the OS and the other processes! So 1,5 gigs and 2 procs are (unofficially) recommended I think.

However there are 2 other real disabling limitations (IMHO) that might not be apparent at first glance:

1. No Server Agent, which means that scheduled backup (and other tasks) is not possible within the database
2. No DTS services, which means no easy data import/export functionality*

So if you want to run backup or import/export data from MS Access or another source, you have to create your own routine and use the Windows Scheduler or similar for scheduling.

Also note that SQL Server Express cannot be a publisher in a replication, but it can be a subscriber.

* Luckily DTS is still possible and available, but for some unfathomable reason you either have to be lucky, or know the ‘trick’ to get it to work. I’ll tell you how in the next post (not really trying to create suspense here, I just don’t want the walktrough to be hidden down at the end of a boring text about SQL Express Server in general :-)
(sorry about not strictly beeing about mobile development)

Update

Boy, that was a long summer vacation!

Sorry about the lack of update, but (unfortunately) I have been busy with a lot of stuff not directly related to programming mobile devices.
Phase 1 of the project I was working on finished, and phase 2 seems to be delayed, so I didn’t really have a lot of stuff to publish.

Recently I’ve picked it up again, so hopefully I’ll be able to post some more or less interesting tidbits, although probably a bit more sporadic.

Please feel free to email me or leave a comment about any suggestions you may have – either for topics or general improvement of this blog!

Cheers!

Installing CF2.0 on the emulator

It’s real weird, but the localized images for the Emulator doesn’t seem to have CF2.0 installed on them. I tried to install it by using the msi installer, but it told me I already had it installed and just stopped. What about people that have two Windows Mobile devices ? Shouldn’t you be able to install the device run-time anyway?

I suspect there must be an easier way to do this, but here is what I did:

• Start the Emulator (from Device Emulator Manager), and connect it to ActiveSync (se earlier posts if you don’t know how)
• Copy the file NETCFv2.wm.armv4i.cab to the emulator (mine was located at %program files%\Visual Studio 2005\SmartDevices\SDK\CompactFramework\2.0\v2.0\WindowsCE\
  wce500\armv4i (phew))
• In the emulator, use File Explorer and click on the cab file you copied – it should then install
• This is real important: When the installation is finished, you’ll be asked if you want to reset the device. DO NOT CLICK ON OK!
• Click Cancel. Then in the emulator select File->Reset->Soft
• When the emulator has booted again, it may be a good time to save the state

When I clicked on OK after the installation, the emulator apparently did a hard reset. Yes, this means that all installed programs and settings was deleted, including the Compact Framework 2.0.

Emulator 101

This is perhaps a newbie topic, but I have seen people use the emulator wrong (IMO), so today I’ll write about the basics regarding the emulator in Visual Studio 2005.

Unfortunately support for Windows Mobile 5.0 devices wasn’t ready when Visual Studio 2005 was released. There was a quite stable beta (Magneto) available, but several things was changed for the final release.

So lets assume you have a clean install of Visual Studio.
Download and install the Windows Mobile 5.0 SDK. Also download and install ActiveSync. If all goes well you should have a couple of new project types in Visual Studio, e.g. Visual C#->Smartdevice->Windows Mobile 5.0 Pocket PC. If you start a new project like this, you have access to the emulator for testing.

I think it’s important how you think about the emulator. I think it is common to simply think of it as a test run-time, i.e. you start it each time you test your application, then close it when you’re done. A better way is to think of it as a real physical device that just happens to be located on your machine. That means you start it and then keep it running in the background. If you press the stop button in Visual Studio (when debugging) the application will stop, but the emulator will keep on running. In this way at least you save some time not having to start the emulator each time.

How to use it:
You should start the emulator through the Device Emulator Manager. Go to Tools->Device Emulator Manager…
This manager is a powerful, but not very user-friendly tool (to start, you have to scroll to get to the Windows Mobile emulators). Start the emulator by right-clicking on Windows Mobile 5.0 Pocket PC Emulator and select Connect. If the emulator has a black screen, it means that is booting – just wait it out. If this is the first time starting the emulator, it may be a good idea to set up any changes (e.g. network connections see below) now, and the just close it again – answering Yes to save the emulator state.

If you do this and then start the emulator again (you might have to click the Refresh button on the DE Manager), you’ll see that it starts much quicker (with any changes you made). This is of course because of the saved state is just loaded in again exactly as it where.

One real neat thing about the emulator is that it is an exact copy of the real thing. So if you don’t have a real device you can play around with it to see how it’s like. If you mess it up, just don’t save the state when you close the emulator, and everything is back to normal (File->Clear Saved State (in emulator) if you accidentally save a messed up state).

If you start your program in Visual Studio (and select the Emulator in the dialog) it starts inside the emulator. An extremely cool thing is that you can set breakpoints and do remote debug against it (as you can do with a real device).

How to connect it to the Internet:
You have to use ActiveSync to do this. Make sure it is installed, then when the emulator is running – right click it in the Device Emulator Manager, and select Cradle. After a few seconds ActiveSync will pop-up after having noticed the new device. Just click cancel if ActiveSync wants to set up a profile for the device, and click Stop if it tries do synchronize. As you can see ActiveSync doesn’t know that it is an emulator, to it this is just another device connected to the PC via a cable.

You now have access to the emulators file-system either by clicking Explore in ActiveSync or selecting “Mobile Device” in Windows Explorer (under My Computer). Select My Windows Mobile-Based Device to go to the root of the device. The emulator should now be able to connect to the Internet through ActiveSync (make sure ActiveSync is not blocked by a firewall). Verify this by starting Internet Explorer in the emulator and click on the “Windows Mobile” logo (picture).

Tip: If you get the cannot connect error, go to Settings->Connections->Advanced. Then click Select network and select My work network in the topmost dropdown (so that both have this selected). Then click OK in top right corner twice. Back in IE try clicking on the logo now – usually that does the trick

Symbian.Net

Some cool news: .Net might be coming to the Symbian OS (Nokia, SonyEricsson etc.) !

http://www.redfivelabs.com/Solutions/tabid/53/Default.aspx

Unfortunately it’s only the CF 1.0 (for Smartphones), however CF2.0 is sure to follow if it is a success!

(from Nino.Mobile)

Testing with localized versions of Windows Mobile

For testing purposes it is very useful to be able to run your program on different language versions of Windows Mobile. Luckily you don’t have to buy different physical devices, just change the OS image in the emulator.

To do this start by downloading the images (note: there are different images for smartphones). After downloading the MSI files simply install them as normal, and the new OS will appear in the Device List with the 3 letter abbreviation as a prefix.

Unfortunately it installs 8 different devices (items in the list) for each language, and if you plan to install a lot of different languages this can be annoying. Another way to change the OS is to simply use the image binary itself. The binary is located at <path where you installed the image>/Deviceemulation/0414/ the one you probably want is called PPC_xxx.bin (xxx = language code) (to get it – either extract it from the MSI or install, get the bin then uninstall the image).

Go to the options for the emulator and select the binary in the OS Image Name textbox. After restart (and clearing state!) you are set to go!