SQLOLE newbie question

[ChevalierBayard]

Hi !

As I have to transfer a full DB (including users, roles, stored procs and so on) from a server to another, I thought I had found a great tool with SQLOLE to create server and transfert objects.

But I get stuck on my first execution line with an unexpected value returned for @hr

DECLARE @hr int
DECLARE @OServer int ---- SQLServer Object
exec @hr = sp_OACreate 'SQLOLE.SQLServer', @oServer OUT
select @hr

I have -2147221005 returned for @hr while I should have a 0...

I've found a SQLDMO.sql and thought maybe I had to execute it but it hasn't changed anything ...

Forgot to say I am on SQL Server 2000 SP4.

Any hint ?

Chevvie

[Rawhide]

Try 'SQLDMO.SQLServer' instead of 'SQLOLE.SQLServer'.

[ChevalierBayard]

Thanks it works indeed

I've modified the script for the proc sp_transfertDBD an Oracle DBA, Phil de Fer, had found for my problem.

Now the object transfer is created but I still must have something wrong in the script as if the tables and stored procedures are well transfered, the roles and users aren't.

I'm looking too on my side but you might certainly see the problem first

[ChevalierBayard]

LOL ... seems it was only questions of 1 instead of 0

Lazy me

Now maybe the script might interrest some people around

[Rawhide]

If your script is complete, and you feel like sharing it, you should submit it to the scripts section of the website. We have very few scripts that involve interaction between MS SQL and Oracle.

[ChevalierBayard]

Actually it is between 2 SQL Server but ... Phil is an Oracle DBA