1 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: transferring data from R2002 to R6 using SQL Express

Hi

On hitting problems with transferring data from Recorder 2002 to Recorder 6 with SQL Express and the sa login I reported this to Dorset Software to be fixed. However, Dorset Software have come back to me to say that the transfer does not succeed because of SQL Express' security comfiguration and that a workaround is required, rather than a change to Recorder 6. The workaround and explanation they have provided is as follows:

In SQL Express the sa login does not have access to the local hard disk, but if you use a trusted connection then it assumes your level of priviledges and can therefore access the C drive.

The solution is:

1. Go to the Recorder 2002\Database folder right click and select the Security tab.

2. Click Add Locations and select the local machine name, then click Ok.

3. In the 'Enter Object Names' box type "Net" and press return, then select the network service. Click Ok and then grant Full Control to the network service account and click Ok.

I'm afraid I haven't been able to try this solution out yet but thought I should at least pass this information on at this stage.

If you do want to try this out and have any questions please post them here and John from Dorset Software might be able to help.

Best wishes,

Sarah Shaw
Biodiversity Information Assistant
JNCC

2 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: transferring data from R2002 to R6 using SQL Express

I want to do a Recorder 2002 to Recorder 6 data transfer on my laptop running Windows XP Professional with Recorder 6 installed with SQL Server 2005 Express so I tried to use the information above. When I right clicked on D:\Recorder 2002\Database I was offered ‘Sharing and Security’ not just a Security tab and there was no option to Add Locations, etc.

I thought I would try the transfer anyway but, as expected, it failed. I used a trusted connection and it ran for 10 minutes or more but then it reported “ODBC—connection to ‘SQL ServerSALLY-44306DEEB\RECORDER6’ failed” while the wizard said

Recorder 6 Workstation Setup Wizard:
Transfering Data and Files
Migrating data from Access database…
Records Transferred: 0
Finalising data migration
Errors encountered:1

Checking Recorder 6 revealed that no data had been transferred.

Could someone please provide more information about how to get the transfer to work with this setup.

Sally Rankin

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

3 Reply by Luck

  • Luck
  • Veteran
  • Offline
  • From: Cheshire
  • Registered: 14-08-2007
  • Posts: 100

Re: transferring data from R2002 to R6 using SQL Express

hi Sally,

have you try turn of the firewall on your laptop?

Luck

IT Officer
rECOrd (A Biodiversity Information System for Cheshire, Halton, Warrington and Wirral)
www.record-lrc.co.uk

Luck's Website

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: transferring data from R2002 to R6 using SQL Express

hi Sally

The problem for an SQL Server Express  installation is more complex than this. The default configuration for SQL Server Express is to have one or two features needed by the transfer turned off. In order to use  SQL Server Express to do the tansfer you need to change the surface area configuration features.  ‘Add Hoc Distributed Queries’  and  ‘xp-cmdshell’ are probably not enabled and need to be. Depending on what version of SQL Server Express you installed the facility to change this may be there. Look in  Explorer  under Microsoft SQL Server 2005/Configuration Tools/Surface Area configuration. If this is there then you can run this to change the surface area configuration for features.  Set the Ad Hoc Remote queries on by ticking the box if it isn't already on and do the same for xp_cmdshell. The transfer from R2002 to R6 should now work if run as a Trusted connection.  If you need to run it as 'sa' then right click on the Recorder 2002/Database folder and choose properties.  Tick to share and to allow updates.

The version of SQL Server Express which definitely contains the configuration tools is at http://msdn2.microsoft.com/en-gb/express/bb410792.aspx . You are looking for    SQL Server 2005 Express Edition  SP2 (Filename SQLEXPR32.EXE). I am not sure if this can be run just as an upgrade or if you need to remove other other versions of SQL Server Express first.


Mike Weideli

Mike Weideli

MikeWeideli's Website

5 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: transferring data from R2002 to R6 using SQL Express

I have now made more attempts to transfer data from Recorder 2002 to Recorder 6 with a SQL Server 2005 Express installation of Recorder 6. Start – All Programs – Microsoft SQL Server 2005 – Configuration Tools – SQL Server Surface Area Configuration – select Surface Area Configuration for Features revealed that Ad Hoc Remote Queries and xp_cmdshell were both enabled and checking my notes confirmed that I had installed SQL Server 2005 Express Edition SP2 on my laptop but the transfer failed with the message “ODBC--connection to ‘SQL ServerSALLY-44306DEEB\RECORDER6’ failed”.

On my laptop I have MSDE on the D: drive and SQL Server 2005 Express Edition SP2 on the C: but when I checked SQL Server Service Manager I discovered that it was the MSDE instance that was running so I swapped over to running the SQL Server Express instance. Another attempt at transferring data failed with the same ODBC message as above.

I then checked the registry and found that trusted security was set to 0 so I made the following changes:
HKEY_LOCAL_MACHINE\SOFTWARE\Dorset Software\Recorder 6 Server: set Trusted Security to 1
HKEY_LOCAL_MACHINE\SOFTWARE\Dorset Software\Recorder 6: set Trusted Security to 1
Another attempt at transferring data failed with the same ODBC message as above.

I then enabled CLR Integration (Common language runtime) and OLE Automation in Surface Area Configuration for Features. Another attempt at transferring data failed with the same ODBC message as above. In each of the attempts so far the transfer ran for about 10 minutes before producing the message but no data was transferred.

I then selected D:\Recorder 2002 – right clicked and selected Sharing and Security – ticked Share this folder on the network and Allow network users to change my files (under Network sharing and security). This time the transfer failed with the same ODBC message as above but this time it ran for about an hour before producing the message and on checking the statistics on the Welcome screen and the number of rows in 18 tables in Recorder 6, the data appears to have transferred with the exception of the items in UpgradeErrors.mdb. Also, the items in this file were the same as for a transfer done on the same data with a SQL Server 2000 installation of Recorder 6. In this case the transfer concluded satisfactorily with a “Data Migration Complete” window – there was no ODBC connection failure message.

Can anyone shed any light on this message and its implications? SALLY-44306DEEB\RECORDER6 is the name of the instance on my desktop whereas the instance names on my laptop are SONYVGN-S5XP\RECORDER (MSDE) and SONYVGN-S5XP\SQLEXPRESS, yet all these tests have been done on my laptop, many miles from my desktop. I also got this message on another transfer I did soon after the v6.10.4 installation CDs came out but in this case the instance name was not one that had been on any of my computers.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

6 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: transferring data from R2002 to R6 using SQL Express

Sally

If the databae is not in its original location, you get an ODBC error when the process finishes. It is not confined to SQL Server Express  editions, but also happens with MSDE.  As far as I can tell all the data is transferred. I suspect it may be something to do with the update of the tables for mapping, but not sure.

Mike

Mike Weideli

MikeWeideli's Website