/* This transfers the table MyTableName */

declare	
	@SourceSVR	varchar(128) ,
	@SourceDB	varchar(128) ,
	@SourceUID	varchar(128) ,
	@SourcePWD	varchar(128) ,
	@DestSVR	varchar(128) ,
	@DestDB		varchar(128) ,
	@DestUID	varchar(128) ,
	@DestPWD	varchar(128) ,
	@WorkDir	varchar(500) ,
	@rc 		int, 
	@context	varchar(255)
	
select	@SourceSVR	= 'SourceServer' ,
	@SourceDB	= 'SourceDatabase' ,
	@SourceUID	= 'sa' ,
	@SourcePWD	= 'sapwdSource' ,
	@DestSVR	= 'DestServer' ,
	@DestDB		= 'DestDatabase' ,
	@DestUID	= 'sa' ,
	@DestPWD	= 'saPwdDest' ,
	@WorkDir	= 'C:\MSSQL7\TRANSFER'
	
declare	@objServer		int ,
	@objTransfer		int ,
	@strResult		varchar(255) ,
	@strCommand		varchar(255)

	exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD
	if @rc <> 0 or @@error <> 0 goto ErrorHnd
	
	-- Create transfer object
	exec sp_OACreate 'SQLDMO.Transfer', @objTransfer OUT			-- Create Object
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllDefaults' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllObjects' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllRules' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllTables' , 0	
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllTriggers' , 0	
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllUserDefinedDatatypes' , 0	
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllStoredProcedures' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyAllViews' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'IncludeGroups' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'IncludeUsers' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopyData' , 0
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'CopySchema' , 1
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	-- Set Destination Properties
	exec @rc = sp_OASetProperty @objTransfer , 'DestDatabase' , @DestDB
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'DestServer' , @DestSVR
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'DropDestObjectsFirst' , 1
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'DestLogin' , @DestUID
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OASetProperty @objTransfer , 'DestPassword' , @DestPWD
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OAMethod @objTransfer , 'AddObjectByName' , null , 'MyTableName' , 16
	if @rc <> 0 or @@error <> 0 goto ErrorHnd


	set  @strCommand =   ' Databases("' + @SourceDB + '").ScriptTransfer'
	exec @rc = sp_OAMethod @objServer, @strCommand, @strResult OUT,  @objTransfer, 1, @WorkDir
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	set  @strCommand =  ' Databases("' + @SourceDB + '").Transfer'
	exec @rc = sp_OAMethod @objServer, @strCommand, NULL, @objTransfer 
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OADestroy @objTransfer
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OAMethod @objServer, 'Disconnect'
	if @rc <> 0 or @@error <> 0 goto ErrorHnd

	exec @rc = sp_OADestroy @objServer
	if @rc <> 0 or @@error <> 0 goto ErrorHnd


return
ErrorHnd:
select 'fail', @context