SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSMS Error: could not find stored procedure 'dbo.sp_dts_listfolders'.


SSMS Error: could not find stored procedure 'dbo.sp_dts_listfolders'.

Author
Message
Tim Weintz
Tim Weintz
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 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
------------------------------
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4783 Visits: 3226
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.



Tim Weintz
Tim Weintz
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 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.
swellguy
swellguy
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 260
Perfect. Worked like a charm. Appreciate the help, as I spent a good chunk of time searching in vain elsewhere.
LuchoF
LuchoF
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 94
perfect! thanks!
petebronken
petebronken
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
Thank you also -- even in 2013 we find ourselves maintaining 2005 instances..



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search