SQL Server 2008 alternative for SQL-DMO

Question

I previously was using SQL-DMO to automatically generate scripts from the database. Now I upgraded to SQL Server 2008 and I don’t want to use this feature anymore since Microsoft will be dropping this feature off.

Is there any other alternative I can use to connect to a server and generate scripts automatically from a database?

Any answer is welcome. Thanks in advance.

This is the procedure i was previously using:

CREATE PROC GenerateSP (
 @server varchar(30) = null,
 @uname varchar(30) = null,
 @pwd varchar(30) = null,
 @dbname varchar(30) = null, 
 @filename varchar(200) = 'c:\script.sql'
)
AS

DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(200)
DECLARE @exec_str varchar(2000)
DECLARE @spname sysname

SET NOCOUNT ON

-- Sets the server to the local server
IF @server is NULL
 SELECT @server = @@servername

-- Sets the database to the current database
IF @dbname is NULL
 SELECT @dbname = db_name()

-- Sets the username to the current user name
IF @uname is NULL
 SELECT @uname = SYSTEM_USER

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
 PRINT 'error create SQLOLE.SQLServer'
 RETURN
END

-- Connect to the SQL Server
IF @pwd is NULL
 BEGIN
  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server, @uname
  IF @hr <> 0
   BEGIN
    PRINT 'error Connect'
    RETURN
   END
 END
ELSE
 BEGIN
  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server, @uname, @pwd
  IF @hr <> 0
   BEGIN
    PRINT 'error Connect'
    RETURN
   END
 END

--Verify the connection
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0
BEGIN
 PRINT 'error VerifyConnection'
 RETURN
END

SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM ' + @dbname + '..sysobjects WHERE type = ''P'' ORDER BY Name'
EXEC (@exec_str)

OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @spname
WHILE (@@fetch_status <> -1)
BEGIN
 SET @exec_str = 'Databases("'+ @dbname +'").StoredProcedures("'+RTRIM(UPPER(@spname))+'").Script(74077,"'+ @filename +'")'
 EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
 IF @hr <> 0
  BEGIN
   PRINT 'error Script'
   RETURN   
  END
 FETCH NEXT FROM script_cursor INTO @spname
END
CLOSE script_cursor
DEALLOCATE script_cursor

-- Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
 PRINT 'error destroy object'
 RETURN
END
GO

What exactly are you trying to accomplish with the stored proc? Script out all stored procs? Does this need to be a stored proc, or can you use an interactive functionality in SQL Server Mgmt Studio (Tasks > Generate Scripts for the database) — marc_s, May 20 '10 at 16:53
@marc_s Don't confuse the question with the example. The question is what is the replacement for DMO. i am using DMO COM objects from VB Script. The question is the same; but a different example. — Ian Boyd, Nov 02 '12 at 18:43

score 2 · Answer 1 · answered Aug 10 '12 at 12:19

2

Download the SQLServer2005_BC.msi from

http://www.microsoft.com/en-us/download/details.aspx?id=24793

then install DMO, also works with 2008/R2!

answered Aug 10 '12 at 12:19

zdba

31
2

for 64 bit Windows you need to download SQLServer2005_BC_x64.msi – Shevek Aug 31 '17 at 10:10

score 1 · Answer 2 · answered May 20 '10 at 15:22

1

Are you aware that DMO has been replaced with SMO? Microsoft has no plans to discontinue this, that I'm aware of.

answered May 20 '10 at 15:22

Randy Minder

47,200
49
204
358

score 1 · Answer 3 · answered May 20 '10 at 15:58

1

I would suggest you try SQL CRL Stored Procedure. See http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

answered May 20 '10 at 15:58

unclepaul84

1,404
8
15

SQL Server 2008 alternative for SQL-DMO

3 Answers3

Linked