retrieve sql database stored procedures

tried to retrieve a sql server database using detach and attach.  got the database working but the stored procedures did not come across to the new database with the same names as the source database.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if you go for detach/attach, it will SURELY bring all SPs as it is. there may be some user or login issue with you.
>>got the database working but the stored procedures did not come across to the new database with the same names as the source database.

Ouch.. using detach, attach?
That is something really weird.... what exactly do you mean.; if you check the SSMS, database, Procedures catalog, they do not appear?  Have you realigned permissions if you moved to a new server - it could be a permission issue?
Do you mean their names changed (never seen it)?
Shinesh PremrajanEngineering ManagerCommented:
Better option is to export and import the database as it retains all the constraints and the procedures in place.

Hope this helps
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Or maybe simpler, on source databse to export the sql creation statements for the sp's, then run these on the target database...
wuotAuthor Commented:
export and import not working because ssms not working on the source server.  added / changed logins and can acces the new database.  a stored procedure name in the source database for example see old sql 05 screen for stored procedures compared with the current 08 screen.  
All that looks like to me is that either
(1) you are comparing apples with oranges (not the same db); or
(2) an app has been installed against the database.  It introduces a whole lot of aspnet_* stored procedures.

Have you scrolled further down to see if your original procs are listed - they should be?
wuotAuthor Commented:
none of the old procs lised.  one more fact.  the old db had the name schldr1 as seen in the file mgr but schldr in ssms.  but when trying to attach to a newly created db named schldr got the error msg db already exists. so i set up a new db named tempdb then attached schldr1 to tempdb then imported the tempdb into the new schldr db.  i also wanted to get the db in the default location and the origingal attach left the db in the location of the soucrce file copied.  i think this cased the problem.  but how to do the attach so that the .mdf file ends up in the default sql directory and allows changing the name to schldr in ssms.
>  but when trying to attach to a newly created db named schldr got the error msg db already exists

Okay, so what you are saying is that you are comparing the "schldr" that ALREADY EXISTED against the "schldr" (file name schldr1) from the old server.  How do you expect them to be the same?

> so i set up a new db named tempdb then attached schldr1 to tempdb

You do know that "tempdb" is the system temporary workspace, so how exactly do you create a db named "tempdb" (exactly)?

Something is missing from this picture.

What you need to do is to forget what you did.  Delete/detach the current schldr db from the new server (if you really don't need it anymore), then move the files away (if detached).
Place the schldr1.mdf and .ldf files into the default sql directory.
Using SSMS, attach a db, find the schldr1.mdf file and name it as schldr (this works now that the other db is gone).
Finally, refresh the databases tree and you should see schldr with the procedures all intact.
wuotAuthor Commented:
yes i know this but as i stated previously.  the file name for schldr is schldr1 and the ssms name is schldr.  the db has two names when viewed in file mgr schldr1 when viewed in ssms schldr.  i do not know for sure how this coud be.  but we get an error msg db already exists when trying to attach, that canot be overcome directly in the ssms ui.  so somehow, we could change the name of schldr / schldr1(the source db)in order to attach as schldr or set up some kind of intermediary db then import or attach as (change name) schldr in the default sql data directory.  i don't remember exactly the temp db established previoulsy as an intrmediary but the process worked for geting the db across to an extent but not completely in that we did not get the user defined stored procedures.
wuotAuthor Commented:
1) When we tried to attach the database using “create database for attach“ we got below error :
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\schldrlog_1.ldf" may be incorrect. Msg 5123, Level 16, State 1, Line 4 CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\schldr_log.LDF'.
Msg 1813, Level 16, State 2, Line 4
Could not open new database 'schldr'. CREATE DATABASE is aborted.

2)We found that SQL Server service account didn’t had required permissions on ldf file. So we gave full permissions on folder which contained ldf file to SQL Server service account. Then we were able to attach the database.
3) We also gave access to a login to attached database. We were getting execute denied permissions on a stored Procedure which we resolved by granting execute permissions.

=================================  --CREATE DATABASE FOR ATTACH -- GRANT Object Permissions

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.