SQL-DMO problems

From: Zoury (yanick_lefebvre_at_hotmail.com)
Date: 05/05/04 

Date: Wed, 5 May 2004 11:08:46 -0400

Hi all! :O)

I'm trying to generate the sql script for a given database. For some reason
the script file isn't created on the disk.. I can see the beginning of the
script on the first row of the [Grids] tab though (QA)... any idea? 

---
declare @DatabaseCollection int
declare @Server int
declare @Transfer int
declare @Database int
declare @hr int
declare @src varchar(255)
declare @desc varchar(255)
-- intializes the server object
exec @hr = sp_OACreate 'SQLDMO.SQLServer', @Server OUT
exec @hr = sp_OASetProperty @Server, 'LoginSecure', 'TRUE'
-- connects to the server
exec @hr = sp_OAMethod @Server, 'Connect', null, null, null ,null
if @hr <> 0
begin
 exec sp_OAGetErrorInfo @Server, @src OUT, @desc OUT
 select convert(varbinary(4), @hr) as hr, @src as Source, @desc as
[Description]
 return
end
-- get the database collection
exec @hr = sp_OAGetProperty @Server, 'Databases', @DatabaseCollection OUT
if @hr <> 0
begin
 exec sp_OAGetErrorInfo @Server, @src OUT, @desc OUT
 select convert(varbinary(4), @hr) as hr, @src as Source, @desc as
[Description]
 return
end
-- get database we want from the collection
exec @hr = sp_OAMethod @DatabaseCollection, 'Item', @Database OUT, 'te'
if @hr <> 0
begin
 exec sp_OAGetErrorInfo @Server, @src OUT, @desc OUT
 select convert(varbinary(4), @hr) as hr, @src as Source, @desc as
[Description]
 return
end
-- intializes the transfer object
exec @hr = sp_OACreate 'SQLDMO.Transfer', @Transfer OUT
exec @hr = sp_OASetProperty @Transfer, 'CopySchema', 1
exec @hr = sp_OASetProperty @Transfer, 'CopyAllObjects', 1
exec @hr = sp_OASetProperty @Transfer, 'CopyData', 0
-- launch the script generation
exec @hr = sp_OAMethod @Database, 'ScriptTransfer', null, @Transfer, 2,
'c:\myscript.sql'
if @hr <> 0
begin
 exec sp_OAGetErrorInfo @Server, @src OUT, @desc OUT
 select convert(varbinary(4), @hr) as hr, @src as Source, @desc as
[Description]
 return
end
-- disconnect from the server
exec @hr = sp_OAMethod @Server, 'DisConnect'
-- clean up
exec sp_OADestroy @Database
exec sp_OADestroy @DatabaseCollection
exec sp_OADestroy @Transfer
exec sp_OADestroy @Server
---
-- 
Best Regards
Yanick Lefebvre

Relevant Pages

  • Re: Share Point Services
    ... Change the database ownership and permissions for the configuration database ... Microsoft SQL Server, and then click Query Analyzer. ... EXEC sp_changedbowner @AdminVSAccount; ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Update databases
    ... Compares if all tables in one database have analog in second ... declare @sqlStr varchar ... exec ('declare @Name sysname select @Name=name from ... -- ##CompareStr - will be used to pass comparing strings into dynamic script ...
    (microsoft.public.sqlserver.programming)
  • Re: Script to delete backup files which are 7 days old
    ... you might want to know that the sp_smtp_sendmail and xp_smtp_sendmail have been used for this purpose by many many people on SQL Server 2000. ... DECLARE @DeleteFiles NVARCHAR ... SELECT 'exec xp_cmdshell ''DEL "' ...
    (microsoft.public.sqlserver.tools)
  • Re: Script to delete backup files which are 7 days old
    ... -- This is to configure a remote SMTP server. ... DECLARE @DeleteFiles NVARCHAR ... SELECT 'exec xp_cmdshell ''DEL "' ...
    (microsoft.public.sqlserver.tools)
  • Re: Replicating database schema changes in "daisy chain" situation
    ... create database central ... exec sp_replicationdboption 'central','merge publish','true' ... SQL Server B> is installed in another country and replicates key master data from server A.> MSDE Instance C replicates with Server B to receive this master data as well> as merge data that is shared only between B and C. ...
    (microsoft.public.sqlserver.replication)