Solved

retrieve sql database stored procedures

Posted on 2010-08-18
10
596 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:wuot
10 Comments
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
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.
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
>>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)?
0
 
LVL 17

Expert Comment

by:shinuq
Comment Utility
Better option is to export and import the database as it retains all the constraints and the procedures in place.

Hope this helps
0
 
LVL 11

Expert Comment

by:slemmesmi
Comment Utility
Or maybe simpler, on source databse to export the sql creation statements for the sp's, then run these on the target database...
0
 

Author Comment

by:wuot
Comment Utility
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.  
stored-procedures-schldr-08.JPG
stored-procedures-schldr-05.JPG
0
Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
All that looks like to me is that either
(1) you are comparing apples with oranges (not the same db); or
(2) an asp.net 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?
0
 

Author Comment

by:wuot
Comment Utility
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.
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
>  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.
0
 

Author Comment

by:wuot
Comment Utility
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.
0
 

Accepted Solution

by:
wuot earned 0 total points
Comment Utility
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.


RELATED KNOWLEDGE BASE ARTICLES
=================================
http://technet.microsoft.com/en-us/library/ms176061.aspx  --CREATE DATABASE FOR ATTACH

http://msdn.microsoft.com/en-us/library/ms188371.aspx -- GRANT Object Permissions
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
530 User cannot login, home directory inaccessible 18 37
Roaming Profiles 8 57
SQL 2012 and SQL 2014 always on 9 23
Log Backup 2 11
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now