Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is meant to be in the MSDB database.

But it doesn't seem to be there at all, did we not install something or is this no longer a valid stored procedure in 2008 R2? What replaced it?

share|improve this question

migrated from stackoverflow.com Feb 1 '12 at 14:08

This question came from our site for professional and enthusiast programmers.

    
No it's a brand new install, I was under the impression this was a standard MSDB stored procedure? –  Gavin Mannion Feb 1 '12 at 7:44
1  
Do you have any maintenance plans? –  Arion Feb 1 '12 at 7:50
    
@Arion not that I am aware off, that's taken care off by the SQL DBA's I think. This stored proc is being called by our code, I'm trying to track down the actual line that does it though.. it's hiding ;) –  Gavin Mannion Feb 1 '12 at 7:57
add comment

1 Answer

up vote 1 down vote accepted

Why I think it can be something about the maintenance plans is because on msdn it states:

If the account that the SQL Server Agent runs under on the target server (TSX) is not a member of the sysadmin fixed server role on the master server (MSX), to import a distributed maintenance plan, the account must be granted the following permission on the master server.

.......

If this permission is not granted, the following error will appear in the SQL Server Agent log on the target server when the multiserver SQL Server Agent Job that runs the maintenance plan is refreshed by the target server:

"Failed to import Maintenance Plan from < NameOfMasterServer>: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_getfolder', database 'msdb', schema 'dbo'.)"

After these permissions are changed, each existing distributed maintenance plan must be reloaded on the target server. Make a small change to each plan and save. This will force the target server to reload the maintenance plan from the master server. By default, the target server will update jobs from the master server every 60 seconds. There will be a minimum delay of 120 seconds after you save the maintenance plan before it appears and can be run on the target server.

Here is the link to read about it

You should also looked at this kb. This is a fix from Microsoft regarding this problem.

After some investigation I found that going from sql 2005 to sql 2008 there are some changes in the system store procedures

Now the twist in the story is, since SSIS 2005 has grown up from DTS, the system tables and system stored procedures use a naming convention like "dts" in its name as you can see in first column of table below. With SSIS 2008, the SSIS team has standardize the naming convention and uses "ssis" in its name as you can see in the second column of the table below. So if you are using these system tables or system stored procedure in your code and upgrading to SSIS 2008, your code will break unless you change your code to accommodate this new naming convention.

So the [msdb].[dbo].[sp_dts_getfolder] is actually changed to [msdb].[dbo].[sp_ssis_getfolder].

You can find all of the other procedure changed and some more information in the article Best Practice #17 - Names of system tables and procedures have changed between SSIS 2005 and SSIS 2008

share|improve this answer
    
I'm not sure that would be my solution, but I'll ask the DBA's to add a maintenance plan and maybe that will create the SP's.. –  Gavin Mannion Feb 1 '12 at 8:18
    
Okay there is already a maintenance plan on that server.. still no stored procedures though? –  Gavin Mannion Feb 1 '12 at 8:29
    
Updated the answer see the link in the answer –  Arion Feb 1 '12 at 8:58
    
unfortunately that doesn't give me any insight into why the stored procedures don't exist in the 2008 database at all. –  Gavin Mannion Feb 1 '12 at 11:17
    
Updated the answer. Hope it helps –  Arion Feb 1 '12 at 12:20
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.