Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSMS Error: could not find stored procedure 'dbo.sp_dts_listfolders'. Expand / Collapse
Author
Message
Posted Friday, March 19, 2010 5:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 2, 2012 3:25 PM
Points: 158, Visits: 147
When I attempt to view the list of packages deployed to MSDB via Management studio (Sqlwb) by expanding the tree below "MSDB" I get an error which states that dbo.sp_dts_listfolders cannot be found.

The procedure is there, it runs from a query window, and all permissions seem to be in order. I launched profiler and it captured absolutely nothing, as if Sqlwb did not even connect to that instance at all. So far, I have not found anyone who has encountered this and I am looking for some help.

Some things to note:

OS WinDOZE Server 2008 R2 64 bit ed
SQL 2005 SP3 64 bit enterprise edition, named instance
also installed is a SQL 2008 SP1 standard, 64 bit, default instance


Exact syntax of the error included below.

Thanks in advance,

Tim

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Could not find stored procedure 'dbo.sp_dts_listfolders'. (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------
Post #886798
Posted Sunday, March 21, 2010 9:41 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:16 PM
Points: 2,922, Visits: 2,521
The error is happening because
1. the name of various SSIS related sprocs changed in SQL 2008 ("DTS" finally becomes "SSIS")
2. by default, SSIS 2005 looks at the default SQL instance on the server, which for you is a SQL 2008 instance.

Hence, even though can find the sprocs, you are looking in the named instance of SQL 2005.

To fix this , you will need to edit "MsDtsSrvr.ini.xml" which is in a path similar to "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\" (depending on your particular installation). In this file, is the name of the sql server instance that SSIS uses for it storage (i.e. the location of the MSDB database for it to use). By default, this is "." which is the default instance on the server. Change this to the name of your instance (e.g. "myServerName\InstanceName") and restart the SSIS service.



Post #887147
Posted Monday, March 22, 2010 10:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 2, 2012 3:25 PM
Points: 158, Visits: 147
Happy Cat, thank you for taking the time to respond to my original post. I followed your advice, and it resolved the issue.
Post #887493
Posted Saturday, October 9, 2010 9:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 1:29 PM
Points: 16, Visits: 258
Perfect. Worked like a charm. Appreciate the help, as I spent a good chunk of time searching in vain elsewhere.
Post #1001775
Posted Monday, November 22, 2010 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 5:22 AM
Points: 2, Visits: 94
perfect! thanks!
Post #1024368
Posted Wednesday, October 30, 2013 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:40 AM
Points: 1, Visits: 3
Thank you also -- even in 2013 we find ourselves maintaining 2005 instances..


Post #1509846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse