July 29, 2008 at 3:12 am
Hello,
I hope one of you SQL wizards will be able to help me out a bit. We would like to test an application and database on another server, I have taken a copy of the database and attached it to the new server and I have also pointed the front end application to it, no problems so far.
However, on testing the application, is it saying it cannot find certain stored procedures so I need to know if there is a way of finding out what views and SP's are used by the database and how I can move them all across to the new server and successfully start testing the application.
I am ok at SQL server, but I am still at an early stage of learning so I would really appreciate any help at all.
Thank you in advance,
Regards,
D.
July 29, 2008 at 3:21 am
The best way to move the stored procedures across, is to right click on the database and click on tasks--> generate scripts
Use this to create the scripts for your stored procedures. Check the code that it contains what you need then run the script against your new database.
You may have to check the application code to find out exactly what stored procedures are called by it.
July 29, 2008 at 3:26 am
How did you take the copy of the database?
If you've done a backup/restore or copied the MDF/LDF files, then all the stored procedures will be in the copied database already.
Is your application calling stored procedures in a different database, or are any of the stored procedures calling other stored procedures from another database?
July 29, 2008 at 3:40 am
Hello,
Firstly, thanks for getting back, I appreciate your time is valuable. I got the database from a backup from the night before, I take I would be better of copying the database then?
I am just looking at the generate script wizard right now, and I am just making some choices, presumably I only need to select Stored Procedures, UDF's and Views and leave Users and Tables unchecked, if I should continue to go ahead with the wizard.
Regards,
D.
July 29, 2008 at 3:41 am
have u added procedures after the night backup.
"Keep Trying"
July 29, 2008 at 3:48 am
If you have last nights backup , then a restore of this would be easiest and you will know that everything has been copied over .
July 29, 2008 at 3:50 am
As you got the copy from a backup, you won't need to do anything else... the tables, data, stored procedures, UDFs etc etc all get restored.
Are you sure the stored procedure that the application can't find is not in a different database?
July 29, 2008 at 7:17 am
Hello,
I am not sure if the app is trying to retrieve from a different database, it was written in house before my time and was originally for an Access backup (it hasnt run with an access back end for quite a while, also before my time). So I am just trying to work things out as I go along.
The error that appears is 'Cannot find SP(stored proc name)', but if look I can see that the SP is right there under Programmability. Is there something extra I need to do to?
I should point out that the database was copied from a SQL 2000 SP4 machine and attached to a SQL 2005 machine (fully SP'd), I should have pointed that out before but it's only just occurred to me that this may make a difference.
Regards,
D.
July 29, 2008 at 7:39 am
What kind of app are you running? It's possible that your app is hard coded for a different instance (bad programming? that's another thread...). If the app is an exe, you'll have to go talk to a developer about the exe's contents. If php or other variation, then you should be able to get at the source code itself to research how the sp is invoked. It's quite possible someone performed a 'no-no' in coding the application, and you've only now discovered this 'undocumented feature'. 😉
July 30, 2008 at 5:17 am
sounds like you'll need to check the application to find what procs are being called and from where
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 30, 2008 at 5:21 am
Check the permissions of the stored proc that the application cannot find, it may have the View Definition permission denied.
July 30, 2008 at 5:56 am
Is it SQL 2005? Is the proc in a schema that the profile that the app is using to access the DB can't see?
In order to eliminate security "issues" temporarily escalate your app user to "sa" status on the server (enter them into the "system Administrator" server role). I will say this again though - this must only be done on your test/dev server, and only temporarily - its really bad practice 😀
If you do the above and the proc still can't be found, then yes, you have to go back to your develoipers and find exactly what it is calling.
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
July 30, 2008 at 9:20 am
Hello,
Thank you al for your replys, I believe it is a permissions problem, however due to further complications its been decided that it should be left for now, basically the developer is giving me details that simply do not work.
But thank you all for reading and answering my thread.
Kind Regards,
D.
July 30, 2008 at 10:16 am
I've seen some applications where developers keep data in one database and procedures in a different one... don't know why, nobody has been able to give me an understandable answer for such a strategy.
Who knows, may be this is your case too.
If you backup/restore the right stuff all data and code will move along, no need to script anything.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 31, 2008 at 11:10 am
In SQL 2000 BOL
Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.
Do the SPs which can not be found have the sp_ prefix? And if so did the developers create them in the 2000 Server Master Database.
From SQL 2005
System stored procedures begin with the characters sp_. They are physically stored in the Resource database, but logically appear in the sys schema of every system- and user-defined database in the instance of Microsoft SQL Server.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply