Importing stored procedure

Hi,

I am trying to import a database from other server which is in sql2000 to server in sql 2005, i can successfully import all the tables but not able to import the stored procedure. Can any one suggest me the method to import stored procedure from other server.

Thanx

We deploy our sp's to multiple different database servers with a deploy (.sql) script.

So every stored procedure is also stored in this script.

Or you can use the following script:

USE MYDATABASE

SELECTname,text

FROM sysobjects

INNERJOIN syscomments

ON sysobjects.id= syscomments.id

WHERE xtype='p'ANDNameLIKE'%'

ORDERBYNAME

|||

What tool or method are you using to import the database? Is the stored procededure import failing? Do you get an error message?

An easy method to transfer a database from server to server is to backup the database and restore it to the new server - using SQL Server Management Studio (or the express version which is a free download). The tool will take care of upgrading the database from 2000 to 2005 for you behind the scenes.

|||

Hi divya4k2007,

You can script your database to a *.sql file and then run this query file in a new database. After that you will have a db with the same schema/structure as your orginal one ( All the tables, sp's will be copied but the data won't. So you need to Impor the data later).

Another easy way is to use backup/restore (if migrate direction is sql2000 to sql2005). You can also try database detach/attach,however, since i don't have sql2000 installed on my box,i'm not sure if detach/attach will work. You can refer to my another post which addresses the same issue: http://forums.asp.net/t/1144769.aspx

Hope my suggestion helps

Importing Excel file to SQL Server (Opinions please)

Dear All,

I am writing a procedure to importdaily the customer excel file to SQL server 2000, I managed to do that where the excel file will be imported directly to the SQL server after creating the new data table, & then I need to read the created table & import it row by row to my original data table.
The problem:

I. The original excel file has the following:
a. a protection password
b. The contents has two merged headers (which effecting the import procedure)
c. And last line is a totals line

Before importing the file I have manually to remove (a – b & c)!!

The Solution:

II. I am trying to find a way to do the above points automatically inside the project.

III. Also I thought of importing the excel file to a DataGrid first then:
a. Let the user approve the file contents &
b. Remove manually point (I.b.) above (I don't now how yet, need to try it).
c. Then import the DataGrid the the SQL server.

I think I prefer solution (III), any suggestions are highly appreciated

BR

Try this thread and read my post there is code and a link to all you will need including free Video tutorials from Microsoft. Hope this helps

http://forums.asp.net/928520/ShowPost.aspx

|||

Dear Caddre,

Thank you for your reply,
I have checked the posted links. they are talking about exporting to excel from sql server/browser.
this not what I want.

Anyhow thanks again.

|||

Hi again,
I desided to preview the excel sheet in a datagrid when the user selects the file, worked fine.
but still I am stuck if the excel file has a password, I tried to do the following :

oOLEDBConn =New OleDb.OleDbConnection
oOLEDBDA = New OleDbDataAdapter("SELECT * FROM " & sDataSheet & " ", oOLEDBConn)
oDS =New DataSet
oOLEDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & sFileName & ";Extended Properties=Excel 8.0;Password='123';"
oOLEDBConn.Open()
oOLEDBDA.Fill(oDS, "Sheet")

In this case i am getting the following error whether the file has a password or not:
(Cannot start your application. the workgroup information file is missing or opened exclusively by another user)

Any suggestions please.