Using SQLDMO in SQL Server

Server


Once upon a time, the partners had a headache for the upgrade of the database. Often the upgrade of the program does not catch up with the upgrade of the database (version control is good, this may not be a problem, but for a large number of Chinese companies this is unavoidable). and some n-long ago databases to use new programs, Database upgrades are almost impossible. So the critical nature of the database upgrade is gradually protruding. for table and field upgrades are not difficult, it is easy to find differences through sysobjects and syscolumns, Then add the objects that you don't have. Updates to non-table objects, such as views and stored procedures, are somewhat awkward (and, of course, the view and stored procedures are simple by hand). One lies in how object scripts are generated, The other is how to do it. Everyone knows that the syscomments table contains scripts for such objects, and people will certainly give priority to exploiting this treasure trove. When the maximum number of characters in a character variable can store only 8,000 characters, This is simply an insurmountable obstacle. When people want to start by exporting a text script and executing this idea, they also find that the script taken from the field has a natural flaw: the line-wrapping problem. (Last issued

Now, this problem can also be solved. Helpless the eyes of the people focused on the SQLDMO, her body always exudes the light of omnipotence.

When the beautiful stature of the transfer object unfolds in the people's eyes, everyone is impressed with her beauty--isn't that what we're looking for? Her beauty lies in the fact that a database object script can be stored in memory and then connected to another database for execution. That's great! Now let's take a look at her outline:

Important attributes:

CopyAllDefaults Boolean All default values

CopyAllObjects Boolean All objects

CopyAllRules Boolean All Rules

CopyAllStoredProcedures Boolean All stored procedures

CopyAllTables Boolean All Tables

CopyAllTriggers Boolean all triggers

CopyAllUserDefinedDatatypes Boolean All user-defined types

CopyAllViews Boolean All views

CopyData Boolean All data

DestDatabase String target Object Database

DestLogin String Target Database login user name

DestPassword String Target Database login password

DestServer String Target Server

DestUseTrustedConnection Boolean User Trust connection

DropDestObjectsFirst Boolean Delete target object first

Whether the IncludeDependencies Boolean contains dependent objects

ScriptType Boolean Script Type

Important Method:

AddObject Add Objects

AddObjectByName add objects by object name

Well, we should have a little more understanding of this object. People familiar with SQLDMO may get inspiration from it at a draught, and beginners here are probably confused. But don't worry, the specific usage we will slowly:

CREATE PROCEDURE P_udb--Upgrading the target database with the source database as a template

(@Source_DB sysname--Original database

, @Des_DB sysname--Target database

, @UserName sysname--User name

, @psw sysname--Password

)

As

SET NOCOUNT ON

--/* local variable declaration

declare @ObjName sysname

, @SrvID int--Server ID

, @DBsId INT--Database set ID

, @transferID INT--Transport ID

, @SDBId INT--source database ID

, @DDBID INT--Target database ID

, @SViewListID INT--List of source database views

, @DViewListID INT--List of target database views

, @str Nvarhar (4000)

, @name sysname

, @hr INT--Execution statement return value

, @Error INT--Error return value (999: Stored procedure or trigger error; 9999: view error)

--*/

--/* Create SQLDMO Object before we've said SQLDMO is a COM, use OLE in SQL Server--automation objects need to use a series of stored procedures such as sp_OACreate, if readers have not understand can be from-have access to relevant information

EXEC @hr =sp_oacreate ' sqldmo.sqlserver ', @SrvID output

If @hr <>0

Begin

Set @Error =1

Goto Pend

End

--*/

--/* Connection Server

EXEC @hr =sp_oamethod @SrvID, ' Connect ', null,@ @ServerName, @UserName, @psw

If @hr <>0

Begin

Set @Error =2

Goto Pend

End

--*/

--/* Database Set

EXEC @hr =sp_oagetproperty @SrvID, ' databases ', @DBsId output

--*/

--/* Select Source Database

EXEC @hr =sp_oamethod @DBsId, ' item ', @SDBId output, @Source_DB

If @hr <>0

Begin

Set @Error =3

Goto Pend

End

--*/

--/* Select Target Database

EXEC @hr =sp_oamethod @DBsId, ' item ', @DDBId output, @Des_DB

If @hr <>0

Begin

Set @Error =4

Goto Pend

End

--*/

/*tansfer property settings (generate three objects)

EXEC @hr =sp_oacreate ' SQLDMO. Transfer ', @transferID output

EXEC @hr =sp_oasetproperty @transferID, ' DestServer ', @ @ServerName

EXEC @hr =sp_oasetproperty @transferID, ' DestLogin ', @UserName

EXEC @hr =sp_oasetproperty @transferID, ' DestPassword ', @psw

EXEC @hr =sp_oasetproperty @transferID, ' destdatabase ', @des_DB

EXEC @hr =sp_oasetproperty @transferID, ' DropDestObjectsFirst ', 1

EXEC @hr =sp_oasetproperty @transferID, ' CopyAllStoredProcedures ', 1

EXEC @hr =sp_oasetproperty @transferID, ' copyalltriggers ', 1

EXEC @hr =sp_oasetproperty @transferID, ' copyallviews ', 1

--exec @hr =sp_oasetproperty @transferID, ' ScriptType ', 1 Here you can try this property

EXEC @hr =sp_oamethod @DDBId, ' Transfer ', NULL, @transferID

exec sp_OADestroy @TransferID

If @hr <>0

Begin

Set @Error =10

Goto Pend

End

*/

Pend:

EXEC @hr = sp_OAMethod @SrvID, ' DisConnect '

exec sp_OADestroy @SrvID

Print (@Error)

Return (@Error)

Go

The top of the stored procedures as long as the introduction of the implementation of methods, and specific functions such as table and field comparison generated here is omitted.

In fact, there is a problem that everyone can not solve is the problem of object dependence. Many objects, such as nested views in a view, will need to be sequential in their generation and execution. Although there are algorithms but there are flaws. I hope the reader can provide a good algorithm.