March 19, 2010 at 5:57 pm
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
------------------------------
March 21, 2010 at 9:41 pm
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.
March 22, 2010 at 10:34 am
Happy Cat, thank you for taking the time to respond to my original post. I followed your advice, and it resolved the issue.
October 9, 2010 at 9:56 pm
Perfect. Worked like a charm. Appreciate the help, as I spent a good chunk of time searching in vain elsewhere.
November 22, 2010 at 8:08 am
perfect! thanks!
October 30, 2013 at 9:41 am
Thank you also -- even in 2013 we find ourselves maintaining 2005 instances..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply