When the partners have hurt their brains for the upgrade of the database. It is often not the upgrade of the program (the version control is good, this may not be a problem, but for a large part of the Chinese company is unavoidable). Some N long ago should use a new program, the database upgrade is simply unsained. So the criticality of the database upgrade is gradually conveyed. It is not difficult for the upgrade of the table and fields. The relatively easy to find the sysobjects and SysColumns can be found, and then add no objects. And the updates of non-objective objects such as view and stored procedures are somewhat embarrassing (of course, if the view and store procedure are manually used by manual It is very simple). One is how to generate an object script, and the other is how to do. Everyone knows that the SysComments table has a script with such objects in the SYSComments table, people will definitely consider this treasure gallery. When standing in characters variables When storing 8,000 characters, this is simply an insurmountable obstacle. People want to have a natural defect from the script removed from the field and discover the script removed from the field: PROPUS.
Now, this problem can also be solved). Wanner has helpless people concentrate on SQLDMO, and her body exudes the omnipotent ray.
When the wonderful figure of the Transfer object is present in front of people, everyone is convinced to her beauty - this is not what we are looking for? Her moving is that you can save a database's object script in memory. Then connect to another database. Great! Now let's take a look at her outline:
Important properties:
CopyAllDefaults Boolean all default values
CopyAllObjects Boolean all objects
CopyAllRules Boolean all rules
CopyAllStoredProceducedProcedProcedurated Procedure
CopyAllTables Boolean all tables
CopyAllTriggers Boolean all triggers
CopyAllUserDefinedDataTypes Boolean All User Custom 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 deletes target objects
INCLUDEDEPENDENCIES BOOLEAN does not contain reliable objects
ScriptType Boolean script type Important method:
AddObject increases the object
AddObjectByname increases objects by object name
Ok, everyone should have some understanding of this object. People familiar with SQLDMO may get inspiration from it, and beginners are afraid that they are still a mist. But don't worry, specific usage we will slowly :
CREATE Procedure P_UDB - Upgrade the target database as the source database
(@Source_db sysname - Original Database
, @ Des_db sysname - Target Database
, @ Username sysname - Username
, @ psw sysname - password
)
AS
Set nocount on
- / * Local variable declaration
Declare @objname sysname
, @ Srvid Int - Server ID
, @ Dbsid int - Database Set ID
, @ tSferid Int - Transport ID
, @ SDBID INT - Source Database ID
, @ DDBID INT - Target Database ID
, @ Sviewlistid int - Source Database View List
, @ DVIEWLISTID INT - List of Target Database View
@ Str nvarhar (4000)
@ Name sysname
, @ HR INT - Execute Statement Return Value
, @ Error Int - Error return value (999: stored procedure or trigger error; 9999: View error)
- * /
- / * Before you create a SQLDMO object, we have already said that SQLDMO is a COM. Using OLE in SQL Server, the automation object needs to use a series of stored procedures such as sp_oacreate. If you don't understand it, you can check it. data
Exec @ hr = sp_oacreate 'sqldmo.sqlser', @ srvid Output
IF @hr <> 0
Begin
Set @ error = 1
Goto Pend
end
- * /
--/*connect to the server
Exec @ hr = sp_oamethod @ srvid, 'connect', null, @@ servername, @ username, @ PSW
IF @hr <> 0
Begin
Set @ error = 2
Goto Pend
end
- * /
- / * Take the database set
EXEC @ hr = sp_oagetproperty @ srvid, 'Databases', @ dbsid output - * /
- / * Select the source database
Exec @ hr = sp_oamethod @ dbsid, 'item', @ SDBID OUTPUT, @ Source_DB
IF @hr <> 0
Begin
Set @ error = 3
Goto Pend
end
- * /
- / * Select the 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 (generated three major objects)
EXEC @ hr = sp_oacreate 'sqldmo.transfer', @ Transferid Output
Exec @ hr = sp_oasetproperty @ transfeerid, 'destserver', @@ servername
Exec @ hr = sp_oasetproperty @ transfeerid, 'destLogin', @ username
Exec @ hr = sp_oasetproperty @ transfeerid, 'destpassword', @ PSW
Exec @ hr = sp_oasetproperty @ transferid, 'destDatabase', @ des_db
Exec @ hr = sp_oasetproperty @ transfeerid, 'DropDestObjectsfirst', 1
Exec @ hr = sp_oasetproperty @ transfeerid, 'CopyallStoredProcedures', 1
Exec @ hr = sp_oasetproperty @ transfeerid, '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 stored procedure on the upper side is as directed by the implementation of the introduction, and the specific functions such as the comparison of the tables and fields are omitted here.
