Copying Spatial Data between SQL Server Databases¶

date:: 2010-01-04 15:01
author:: admin
category:: geodatabases, sql server 2008
slug:: copying-spatial-data-between-sql-server-databases
status:: published

Aim - to copy a table containing geometry fields between two database servers. The task was made slightly trickier as the two servers are on two different domains.

I wanted to use SQL rather than FME, or a SQL Server wizard, as it would be easier to script the process for the several tables I had to copy.

The first task was to set up linked servers. I used the following SQL (when logged in to my destination server, and using SQL Server Management Studio) to add the source server:

EXEC sp_addLinkedServer 'MY_SOURCE_SERVER_NAME'

I then needed to allow log-in access to the source server. The SQL below assumes there is a sa user with a password of sa on the source server:

sp_addlinkedsrvlogin @rmtsrvname = 'MY_SOURCE_SERVER_NAME'
 , @useself = 'FALSE'
 , @locallogin = null
 , @rmtuser = 'sa'
 , @rmtpassword = 'sa'

I thought I was then ready to run a “SELECT INTO” query to copy records from the source table into a new table in the destination database. However I was met by the following error:

Msg 7325, Level 16, State 1, Line 1
Objects exposing columns with CLR types are not allowed in distributed queries.
Please use a pass-through query to access remote object '"MY_SOURCE_SERVER_NAME"."schema_name"."TableName"'.

It appeared that the geometry columns containing the SQL Server spatial types were causing the problem. After reading up on pass-through queries OPENQUERY seemed to be the solution. I tried the following SQL:

SELECT * INTO newschema.MyNewTableName
FROM OPENQUERY([MY_SOURCE_SERVER_NAME],
'SELECT * from [source_database_name].[schema_name].[TableName]');

:-)

orphan:

Comments¶

2. Phillip **

Hi there,

While I’ll be posting some details on the PyISAPIe list later regarding changes in the new version to make this process easier, I just thought I’d mention a few things that could help you in the future-

PYI_PY_VERSION can be defined in the project settings, under C++/Preprocessor. This is the easiest way to compile for different versions because no source changes are required.
You can add additional include and library directories at the project settings level rather than the Visual Studio settings level, which can help in some cases.
If SubWCRev wasn’t working from Visual Studio but was from the command line, all I can suggest is to log out and back into your Windows account to be sure that your PATH has been updated everywhere (it works in Windows 7 right after installing TortoiseSVN).

Thank you for this guide, I’m positive that it will be a great help to those compiling the project themselves.

3. geographika **

@Phillip

Thanks for the tips. I’ve updated the post to include these, and went
through the steps with the latest sourcecode.
I should have realised the PATH had not been updated having wrote
about it last week…!
All is working well, and the new project set up makes it easy to
compile for 32 bit or 64 bit, with many different Python versions.

4. Jonathan Mayer **

Fantastic work ( you’re on my favourites list!!)

You don’t happen to have a copy of the dll do you,

Cheers Jonathan ;-)

5. geographika **

Thanks Jonathan.

You can get a copy of the 64 bit DLL for Python 2.5 at

http://geographika.co.uk/downloads/PyISAPIe64_Python25.zip.

6. chris **

Great work and an excellent tutorial!

I’m struggling, getting mod_python/mod_wsgi to work under Apache

2.2 compiled for 64-bit Windows for a while… After reading this, I definitely will give IIS7+PyISAPIe a try. | Thanks!

7. geographika **

It has also been announced recently that development on mod_python is to be abandoned http://blog.dscpl.com.au/2010/06/modpython-project-is-now-officially.html so bail out now!

8. James Crowley **

This is an awesome article, thank you!!

I’ve successfully compiled this project to 64bit against Python 2.7. However, when I set up the ISAPI filter in IIS I keep getting a

The specified module could not be found. (0x8007007e)

error. As far as I can tell, IIS has full access to that file (I’ve gone as far as giving the Users group and IUSR and Network Service full read/write access). Is there anything else likely to be going on here? Not sure if there might be issues loading the python bindings or something? Any ideas would be much appreciated!

9. geographika **

Hi James,

Take a look at http://geographika.co.uk/setting-up-python-on-iis7

which goes through the set up steps in IIS7. Previous versions should be similar. | If you just installed Python on the server then try a reboot to make sure the Python environment variables have been set.

10. James Crowley **

Seth, I’ve followed all those steps as far as I can see (I’m using IIS 7), and the server has been rebooted. Is there any way I can check that the PyISAPIe library works without hooking in IIS? Thanks so much for this :)

11. geographika **

As far as I know there is no wat to check outside of IIS. Does the hello.py script produce the “module” error/

What I have found useful when getting errors is to use Process Monitor to see what DLLs Windows is looking for, and any errors if it can’t find them or if it does not have the correct security settings.

12. James Crowley **

Thanks! Yeah, its the hello.py script producing the module error. Will try process monitor and see if that reveals anything.

BTW, as a sense check, downloaded your x64 bit version and the 2.5.4 amd build you linked - this “works”, although complains about a ctypes module not being present in python. I checked and it seems to be there in 2.7 builds but not the 2.5.4 for some reason?

have joined the pyisapie email list now so will try and stop bugging you now! :)

13. MapFish Print Module for IIS at geographika **

[…] Install and configure PyISAPIe. You can follow the instructions described on this post or in the README file included with PyISAPIe . If you want to run a 64 bit version, have a look at this post. […]

14. Quaspam **

Hi thanks for the great article, i have a very stupid question if i

may say, i followed about every step you mentioned and i am stuck when the build gets to the linking stage, it gave the following error: | Error LNK1104: cannot open file ‘python27.lib’ | here is my build command line:

``/OUT:”x64ReleasePyISAPIe.dll” /INCREMENTAL:NO /NOLOGO /LIBPATH:”C:pyisapiePythonx64Python27libs” /LIBPATH:”C:pyisapiePythonx64Python26libs” /LIBPATH:”C:pyisapiePythonx64Python25libs” /DLL “kernel32.lib” “user32.lib” “gdi32.lib” “winspool.lib” “comdlg32.lib” “advapi32.lib” “shell32.lib” “ole32.lib” “oleaut32.lib” “uuid.lib” “odbc32.lib” “odbccp32.lib” /NODEFAULTLIB:”libc libcmt msvcrt” /DEF:”PyISAPIe.def” /MANIFEST /ManifestFile:”x64ReleasePyISAPIe.dll.intermediate.manifest” /ALLOWISOLATION /MANIFESTUAC:”level=’asInvoker’ uiAccess=’false’” /PDB:”C:pyisapiex64ReleasePyISAPIe.pdb” /SUBSYSTEM:WINDOWS /OPT:REF /OPT:ICF /PGD:”C:pyisapiex64ReleasePyISAPIe.pgd” /LTCG /TLBID:1 /DYNAMICBASE:NO /IMPLIB:”x64ReleasePyISAPIe.lib” /MACHINE:X64 /ERRORREPORT:QUEUE ``

I am using VS2010 and i checked if python27.lib is accessible and everything seems fine. Can you please help me

15. geographika **

Hi - it has been a while since I did this. I’ve not tried with 2.7, but you probably have to add:

``#elif PYI_PY_VERSION >= PY_27 #define PYI_PY_VERSION_MAJOR PY_27 #include #include #include ``

My build outout for Python 2.6 (in VS2010) is:

``/OUT:”x64ReleasePyISAPIe.dll” /INCREMENTAL:NO /NOLOGO /LIBPATH:”D:ProjectsPyISAPIePyISAPIePythonx64Python26libs” /LIBPATH:”D:ProjectsPyISAPIePyISAPIePythonx64Python25libs” /DLL “kernel32.lib” “user32.lib” “gdi32.lib” “winspool.lib” “comdlg32.lib” “advapi32.lib” “shell32.lib” “ole32.lib” “oleaut32.lib” “uuid.lib” “odbc32.lib” “odbccp32.lib” /NODEFAULTLIB:”libc libcmt msvcrt” /DEF:”PyISAPIe.def” /MANIFEST /ManifestFile:”x64ReleasePyISAPIe.dll.intermediate.manifest” /ALLOWISOLATION /MANIFESTUAC:”level=’asInvoker’ uiAccess=’false’” /PDB:”D:ProjectsPyISAPIePyISAPIex64ReleasePyISAPIe.pdb” /SUBSYSTEM:WINDOWS /OPT:REF /OPT:ICF /PGD:”D:ProjectsPyISAPIePyISAPIex64ReleasePyISAPIe.pgd” /LTCG /TLBID:1 /DYNAMICBASE:NO /IMPLIB:”x64ReleasePyISAPIe.lib” /MACHINE:X64 /ERRORREPORT:QUEUE ``

16. wolf++ » Running Django on Windows: Python 2.7.2 x64, PyISAPIe and IIS 7.5 **

[…] not a compiled version of this available for Python 2.7 (x64 in my case). geographika has a great post on doing this. The following instructions are based on his guide, updated with newer versions. […]

Add Comment