Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

Import Stored Procedures from Sql 2000 to 2005

I'm new to Sql 2005 and was wondering if someone could give me quick instructions on how to import some stored procedures from a Sql 2000 database into a Sql 2005 database. The Import Data function at the Sql 2005 database level only seems to allow for tables to be imported.

Thanks!
0
dddon123
Asked:
dddon123
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would take the following script:

F OBJECT_ID('GenerateSP') IS NOT NULL DROP PROC GenerateSP
GO

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



Create the procedure on the SQL Server 2000 database, run it and take the output to run it on the sql server 2005.
note that not all the scripts might be compatible on sql server 2005, requiring manual correction.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can generate the script for the sps and run it on the SQL server 2005
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now