Cannot Launch DTA - Failed to open new connection

Installed SQL 2005 STD and installed SP1 for SQL on a Windows 2003 STD server.

I launch DTA and I am logged in with the same windows account which the SQL services use and I get the following message:

===================================

Failed to open a new connection.

===================================

Could not find stored procedure 'msdb..sp_DTA_help_session'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

Server Name: FHCMS,1433
Error Number: 2812
Severity: 16
State: 62
Line Number: 1

Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.QuerySessions()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

Any help would be appreciated.

Thanks,
Pete

are you using RTM or sp1 version? a MSFT guy told me that there are problems he has heard of with DTA in the RTM build...I would tell you that while I love the product, DTA has not been the most stable of components overall from my expereinces w/it.|||

I am using the SP1 version for SQL 2005, I didn't even check to see if DTA worked before that since it is a brand new server.

I saw a post that stated the user does not have sysadmin rights for SQL and that is what the error message means but I created a Domain Admin account for SQL and that is what I am logged on to the server with.

Thanks,
Pete

|||

have you checked all the usual log locations...

sql exception log

event log

unfortunetly i cannot be of any more help at this point.

|||

I have the same trouble.

Cannot Launch DTA - Failed to open new connection

Installed SQL 2005 STD and installed SP1 for SQL on a Windows 2003 STD server.

I launch DTA and I am logged in with the same windows account which the SQL services use and I get the following message:

===================================

Failed to open a new connection.

===================================

Could not find stored procedure 'msdb..sp_DTA_help_session'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

Server Name: FHCMS,1433
Error Number: 2812
Severity: 16
State: 62
Line Number: 1

Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.QuerySessions()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

Any help would be appreciated.

Thanks,
Pete

are you using RTM or sp1 version? a MSFT guy told me that there are problems he has heard of with DTA in the RTM build...I would tell you that while I love the product, DTA has not been the most stable of components overall from my expereinces w/it.|||

I am using the SP1 version for SQL 2005, I didn't even check to see if DTA worked before that since it is a brand new server.

I saw a post that stated the user does not have sysadmin rights for SQL and that is what the error message means but I created a Domain Admin account for SQL and that is what I am logged on to the server with.

Thanks,
Pete

|||

have you checked all the usual log locations...

sql exception log

event log

unfortunetly i cannot be of any more help at this point.

|||

I have the same trouble.

Cannot install SSIS or Notification services

I'm trying to reinstall SQL Server 2005 STD but I cannot get Setup to install Integration Services or Notification Services. I looked in the log and this is what I found. Needless to say, it makes no sense to me. It seems that Setup is trying to find a missing source list for a certain product. I have no clue what that product is. I've spent endless hours trying to figure it out and I'm still drawing a blank. This is a printout of the log.

Integration services -
-
MSI (s) (F0:B0) [02:33:01:437]: Machine policy value 'DisableUserInstalls' is 0
MSI (s) (F0:B0) [02:33:02:188]: End dialog not enabled
MSI (s) (F0:B0) [02:33:02:188]: Original package ==> G:\Servers\Setup\SqlRun_DTS.msi
MSI (s) (F0:B0) [02:33:02:188]: Package we're running from ==> C:\WINDOWS\Installer\188966.msi
MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: looking for appcompat database entry with ProductCode '{EE8CFFD9-6E29-4DC3-A967-7348D5F41F44}'.
MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: no matching ProductCode found in database.
MSI (s) (F0:B0) [02:33:02:339]: MSCOREE already loaded, using loaded copy
MSI (s) (F0:B0) [02:33:02:379]: Couldn't find local patch ''. Looking for it at its source.
MSI (s) (F0:B0) [02:33:02:379]: Resolving Patch source.
MSI (s) (F0:B0) [02:33:02:379]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (F0:B0) [02:33:02:379]: User policy value 'DisableMedia' is 0
MSI (s) (F0:B0) [02:33:02:379]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Media enabled only if package is safe.
MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Looking for sourcelist for product {8B7E2749-624E-4C61-92FC-2BC18307FF24}
MSI (s) (F0:B0) [02:33:02:379]: Note: 1: 1706 2: {8B7E2749-624E-4C61-92FC-2BC18307FF24} 3:
MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Failed to resolve source
MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 1708
MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 2729
MSI (s) (F0:B0) [02:33:02:409]: Note: 1: 2729
MSI (s) (F0:B0) [02:33:02:409]: Product: Microsoft SQL Server 2005 Integration Services -- Installation failed.

MSI (s) (F0:B0) [02:33:02:439]: MainEngineThread is returning 1612
The installation source for this product is not available. Verify that the source exists and that you can access it.
G:\Servers\Setup\SqlRun_DTS.msi
MSI (c) (B8:88) [02:33:02:439]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (B8:88) [02:33:02:439]: MainEngineThread is returning 1612
=== Verbose logging stopped: 5/19/2006 2:33:02 ===

--
Notification services -
--
MSI (s) (F0:30) [02:33:07:566]: Machine policy value 'DisableUserInstalls' is 0
MSI (s) (F0:30) [02:33:08:087]: End dialog not enabled
MSI (s) (F0:30) [02:33:08:087]: Original package ==> G:\Servers\Setup\SqlRun_NS.msi
MSI (s) (F0:30) [02:33:08:087]: Package we're running from ==> C:\WINDOWS\Installer\18898f.msi
MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: looking for appcompat database entry with ProductCode '{37E9AD9F-3217-4229-B5A5-7A0C82364C6C}'.
MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: no matching ProductCode found in database.
MSI (s) (F0:30) [02:33:08:167]: MSCOREE already loaded, using loaded copy
MSI (s) (F0:30) [02:33:08:187]: Couldn't find local patch ''. Looking for it at its source.
MSI (s) (F0:30) [02:33:08:187]: Resolving Patch source.
MSI (s) (F0:30) [02:33:08:187]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (F0:30) [02:33:08:187]: User policy value 'DisableMedia' is 0
MSI (s) (F0:30) [02:33:08:187]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Media enabled only if package is safe.
MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Looking for sourcelist for product {65A498A6-83B9-4B91-99EA-1BA88D73F0AA}
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1706 2: {65A498A6-83B9-4B91-99EA-1BA88D73F0AA} 3:
MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Failed to resolve source
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1708
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729
MSI (s) (F0:30) [02:33:08:187]: Product: Microsoft SQL Server 2005 Notification Services -- Installation failed.

MSI (s) (F0:30) [02:33:08:197]: MainEngineThread is returning 1612
The installation source for this product is not available. Verify that the source exists and that you can access it.
G:\Servers\Setup\SqlRun_NS.msi
MSI (c) (B8:DC) [02:33:08:207]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (B8:DC) [02:33:08:207]: MainEngineThread is returning 1612

During your first install, the MSIs were cached to the specified location (G:\servers\setup\...). Apparently those files were subsequently deleted or perhaps the drive is no longer accessible. Are you reinstalling from the original media or are you using add/remove programs?|||

Paul, I noticed these lines in the error log.

Integration services -
-
MSI (s) (F0:B0) [02:33:02:188]: Original package ==> G:\Servers\Setup\SqlRun_DTS.msi
MSI (s) (F0:B0) [02:33:02:188]: Package we're running from ==> C:\WINDOWS\Installer\188966.msi
--
Notification services -
--
MSI (s) (F0:30) [02:33:08:087]: Original package ==> G:\Servers\Setup\SqlRun_NS.msi
MSI (s) (F0:30) [02:33:08:087]: Package we're running from ==> C:\WINDOWS\Installer\18898f.msi

However, I can't find the C:\WINDOWS\Installer folder on my computer. Is this significant?

|||

The c:\windows\installer folder is ordinarily hidden. To see it, go to Windows Explorer tools\folder options and under the view tab turn on both "Display the contents of system folders" and "Show hidden files and folders". Once you've done that, the folder and its contents should be visible.

Paul

Cannot generate SSPI Context

I have SQL Server 2000 SP3 server running on W2003 SP1 Std that is setup to
run under a domain account with local admin privileges. The server and user
account are in the same domain.
When I then try to register the SQL Server instance via SQLEM, running with
my privileges and I also have local admin to the SQL Server server and have
the same setup for other servers already in place, I get an error saying
"Cannot generate SSPI context"
Does anyone have any ideas on how I can correct this?
Cheers,
JohnTake a look into the article.
http://support.microsoft.com/defaul...kb;en-us;811889
Thanks
Hari
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:BAB33841-7C8F-4873-85A4-AA4D727C8DE9@.microsoft.com...
>I have SQL Server 2000 SP3 server running on W2003 SP1 Std that is setup to
> run under a domain account with local admin privileges. The server and
> user
> account are in the same domain.
> When I then try to register the SQL Server instance via SQLEM, running
> with
> my privileges and I also have local admin to the SQL Server server and
> have
> the same setup for other servers already in place, I get an error saying
> "Cannot generate SSPI context"
> Does anyone have any ideas on how I can correct this?
> Cheers,
> John|||I went through this document and the only new thing in it for me was the SPN
creation. However, I have never needed to do this in the past so I am not
convinced this is the problem. I am also reluctant to start making that sor
t
of change when it has not been necessary in the past and start deviating fro
m
our standard install process.
Do you have any other suggestions?
"Hari Prasad" wrote:

> Take a look into the article.
> http://support.microsoft.com/defaul...kb;en-us;811889
> Thanks
> Hari
> SQL Server MVP
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BAB33841-7C8F-4873-85A4-AA4D727C8DE9@.microsoft.com...
>
>|||Can you please try to register using a SQL Server Login.If it fails try to
change the SQL Server service account to
Local system and then stop and start the service and try registering.
Thanks
Hari
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:C5C0A894-3142-434D-A1A8-51E2A6064C57@.microsoft.com...[vbcol=seagreen]
>I went through this document and the only new thing in it for me was the
>SPN
> creation. However, I have never needed to do this in the past so I am not
> convinced this is the problem. I am also reluctant to start making that
> sort
> of change when it has not been necessary in the past and start deviating
> from
> our standard install process.
> Do you have any other suggestions?
> "Hari Prasad" wrote:
>

Cannot find folder "\Maintenance Plans".

I've been looking for treads with a possible similar problem but thus far nothing.

I recently upgraded SQL 2000 (STD ed.) instance to SQL 2005 (STD ed.).

This includes the db's. All their compatibility levels were changed to level 90 (SQL 2005).

Everything seems to be working fine but when trying to migrate a legacy maintenance plan I keep getting the message, "Cannot find folder "\Maintenance Plans"." even though the folder is clearly listed.

Not sure where to start. I have created two virtual images and both have the same problem.

Any help would be greatly appreciated.

RoviWil

I still can't find anyone who had this problem and yet I got it in multiple tests.

I did however manage to use a workaround by means of system sp's.

I used SQL Profiler.

Verify that the folder is missing:
exec sp_executesql N'exec [msdb].[dbo].[sp_dts_getfolder] @.P1, @.P2',N'@.P1 nvarchar(17),@.P2 uniqueidentifier',N'Maintenance Plans','00000000-0000-0000-0000-000000000000'

Add the folder if missing:
exec sp_dts_addfolder '00000000-0000-0000-0000-000000000000','Maintenance Plans','08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

|||You for got to mention that you must do these commands from the using the msdb database. Took me 45 minutes to figure it out. I thought thier was a problem with the upgrade of my database so I compared all of the objects in a known good 2005 database to the converted one.

But thanks for the info anyway I would never ever have figured it out without it.|||

Hi,

I'm having the same problem, and I've found your post on several sites, that all lead back to your post as the only fix right now. I saw on connections were it's accepted as a bug, but no resolution yet.

What I need help with, and I know this sound really stupid, but I don't understand how to use Profiler to run a SP. I know how to create one, and how to execute it either from Studio or a query or VB, but I don't understand how to execute one from Profier and apparently you have to do it from there because I've tried creating that as a stored procedure from the MSDB Database, and it throws an error and I still have the same problem.

I can get the lookup to run, and it comes back blank, but the create throws another expection error, now it comes back that I have a duplicate key.

Msg 2627, Level 14, State 1, Procedure sp_dts_addfolder, Line 14

Violation of UNIQUE KEY constraint 'U_sysdtspackagefolders90uniquepath'. Cannot insert duplicate key in object 'dbo.sysdtspackagefolders90'.

The statement has been terminated.

(1 row(s) affected)

Any help would be appreciated.

|||

Hi tropics43!

I just had exactly the same problem as you describe.... obviously the folder with the id '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8' was already in the according msdb table.... check it out:

SELECT * FROM msdb.dbo.sysdtspackagefolders90

you will see that the folderid exists but has (for whatever reason...) the wrong name, not 'Maintenance Plans'. You can repair this by just using this statement:

UPDATE msdb.dbo.sysdtspackagefolders90
SET foldername = 'Maintenance Plans'
WHERE folderid='08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

It worked for me perfectly fine... after that maintenance plans can be created again....

Best regards,
Wolfgang

Cannot find folder "\Maintenance Plans".

I've been looking for treads with a possible similar problem but thus far nothing.

I recently upgraded SQL 2000 (STD ed.) instance to SQL 2005 (STD ed.).

This includes the db's. All their compatibility levels were changed to level 90 (SQL 2005).

Everything seems to be working fine but when trying to migrate a legacy maintenance plan I keep getting the message, "Cannot find folder "\Maintenance Plans"." even though the folder is clearly listed.

Not sure where to start. I have created two virtual images and both have the same problem.

Any help would be greatly appreciated.

RoviWil

I still can't find anyone who had this problem and yet I got it in multiple tests.

I did however manage to use a workaround by means of system sp's.

I used SQL Profiler.

Verify that the folder is missing:
exec sp_executesql N'exec [msdb].[dbo].[sp_dts_getfolder] @.P1, @.P2',N'@.P1 nvarchar(17),@.P2 uniqueidentifier',N'Maintenance Plans','00000000-0000-0000-0000-000000000000'

Add the folder if missing:
exec sp_dts_addfolder '00000000-0000-0000-0000-000000000000','Maintenance Plans','08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

|||You for got to mention that you must do these commands from the using the msdb database. Took me 45 minutes to figure it out. I thought thier was a problem with the upgrade of my database so I compared all of the objects in a known good 2005 database to the converted one.

But thanks for the info anyway I would never ever have figured it out without it.|||

Hi,

I'm having the same problem, and I've found your post on several sites, that all lead back to your post as the only fix right now. I saw on connections were it's accepted as a bug, but no resolution yet.

What I need help with, and I know this sound really stupid, but I don't understand how to use Profiler to run a SP. I know how to create one, and how to execute it either from Studio or a query or VB, but I don't understand how to execute one from Profier and apparently you have to do it from there because I've tried creating that as a stored procedure from the MSDB Database, and it throws an error and I still have the same problem.

I can get the lookup to run, and it comes back blank, but the create throws another expection error, now it comes back that I have a duplicate key.

Msg 2627, Level 14, State 1, Procedure sp_dts_addfolder, Line 14

Violation of UNIQUE KEY constraint 'U_sysdtspackagefolders90uniquepath'. Cannot insert duplicate key in object 'dbo.sysdtspackagefolders90'.

The statement has been terminated.

(1 row(s) affected)

Any help would be appreciated.

|||

Hi tropics43!

I just had exactly the same problem as you describe.... obviously the folder with the id '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8' was already in the according msdb table.... check it out:

SELECT * FROM msdb.dbo.sysdtspackagefolders90

you will see that the folderid exists but has (for whatever reason...) the wrong name, not 'Maintenance Plans'. You can repair this by just using this statement:

UPDATE msdb.dbo.sysdtspackagefolders90
SET foldername = 'Maintenance Plans'
WHERE folderid='08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

It worked for me perfectly fine... after that maintenance plans can be created again....

Best regards,
Wolfgang

Cannot find folder "\Maintenance Plans".

I've been looking for treads with a possible similar problem but thus far nothing.

I recently upgraded SQL 2000 (STD ed.) instance to SQL 2005 (STD ed.).

This includes the db's. All their compatibility levels were changed to level 90 (SQL 2005).

Everything seems to be working fine but when trying to migrate a legacy maintenance plan I keep getting the message, "Cannot find folder "\Maintenance Plans"." even though the folder is clearly listed.

Not sure where to start. I have created two virtual images and both have the same problem.

Any help would be greatly appreciated.

RoviWil

I still can't find anyone who had this problem and yet I got it in multiple tests.

I did however manage to use a workaround by means of system sp's.

I used SQL Profiler.

Verify that the folder is missing:
exec sp_executesql N'exec [msdb].[dbo].[sp_dts_getfolder] @.P1, @.P2',N'@.P1 nvarchar(17),@.P2 uniqueidentifier',N'Maintenance Plans','00000000-0000-0000-0000-000000000000'

Add the folder if missing:
exec sp_dts_addfolder '00000000-0000-0000-0000-000000000000','Maintenance Plans','08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

|||You for got to mention that you must do these commands from the using the msdb database. Took me 45 minutes to figure it out. I thought thier was a problem with the upgrade of my database so I compared all of the objects in a known good 2005 database to the converted one.

But thanks for the info anyway I would never ever have figured it out without it.|||

Hi,

I'm having the same problem, and I've found your post on several sites, that all lead back to your post as the only fix right now. I saw on connections were it's accepted as a bug, but no resolution yet.

What I need help with, and I know this sound really stupid, but I don't understand how to use Profiler to run a SP. I know how to create one, and how to execute it either from Studio or a query or VB, but I don't understand how to execute one from Profier and apparently you have to do it from there because I've tried creating that as a stored procedure from the MSDB Database, and it throws an error and I still have the same problem.

I can get the lookup to run, and it comes back blank, but the create throws another expection error, now it comes back that I have a duplicate key.

Msg 2627, Level 14, State 1, Procedure sp_dts_addfolder, Line 14

Violation of UNIQUE KEY constraint 'U_sysdtspackagefolders90uniquepath'. Cannot insert duplicate key in object 'dbo.sysdtspackagefolders90'.

The statement has been terminated.

(1 row(s) affected)

Any help would be appreciated.

|||

Hi tropics43!

I just had exactly the same problem as you describe.... obviously the folder with the id '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8' was already in the according msdb table.... check it out:

SELECT * FROM msdb.dbo.sysdtspackagefolders90

you will see that the folderid exists but has (for whatever reason...) the wrong name, not 'Maintenance Plans'. You can repair this by just using this statement:

UPDATE msdb.dbo.sysdtspackagefolders90
SET foldername = 'Maintenance Plans'
WHERE folderid='08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

It worked for me perfectly fine... after that maintenance plans can be created again....

Best regards,
Wolfgang

Cannot find folder "\Maintenance Plans".

I've been looking for treads with a possible similar problem but thus far nothing.

I recently upgraded SQL 2000 (STD ed.) instance to SQL 2005 (STD ed.).

This includes the db's. All their compatibility levels were changed to level 90 (SQL 2005).

Everything seems to be working fine but when trying to migrate a legacy maintenance plan I keep getting the message, "Cannot find folder "\Maintenance Plans"." even though the folder is clearly listed.

Not sure where to start. I have created two virtual images and both have the same problem.

Any help would be greatly appreciated.

RoviWil

I still can't find anyone who had this problem and yet I got it in multiple tests.

I did however manage to use a workaround by means of system sp's.

I used SQL Profiler.

Verify that the folder is missing:
exec sp_executesql N'exec [msdb].[dbo].[sp_dts_getfolder] @.P1, @.P2',N'@.P1 nvarchar(17),@.P2 uniqueidentifier',N'Maintenance Plans','00000000-0000-0000-0000-000000000000'

Add the folder if missing:
exec sp_dts_addfolder '00000000-0000-0000-0000-000000000000','Maintenance Plans','08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

|||You for got to mention that you must do these commands from the using the msdb database. Took me 45 minutes to figure it out. I thought thier was a problem with the upgrade of my database so I compared all of the objects in a known good 2005 database to the converted one.

But thanks for the info anyway I would never ever have figured it out without it.

|||

Hi,

I'm having the same problem, and I've found your post on several sites, that all lead back to your post as the only fix right now. I saw on connections were it's accepted as a bug, but no resolution yet.

What I need help with, and I know this sound really stupid, but I don't understand how to use Profiler to run a SP. I know how to create one, and how to execute it either from Studio or a query or VB, but I don't understand how to execute one from Profier and apparently you have to do it from there because I've tried creating that as a stored procedure from the MSDB Database, and it throws an error and I still have the same problem.

I can get the lookup to run, and it comes back blank, but the create throws another expection error, now it comes back that I have a duplicate key.

Msg 2627, Level 14, State 1, Procedure sp_dts_addfolder, Line 14

Violation of UNIQUE KEY constraint 'U_sysdtspackagefolders90uniquepath'. Cannot insert duplicate key in object 'dbo.sysdtspackagefolders90'.

The statement has been terminated.

(1 row(s) affected)

Any help would be appreciated.

|||

Hi tropics43!

I just had exactly the same problem as you describe.... obviously the folder with the id '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8' was already in the according msdb table.... check it out:

SELECT * FROM msdb.dbo.sysdtspackagefolders90

you will see that the folderid exists but has (for whatever reason...) the wrong name, not 'Maintenance Plans'. You can repair this by just using this statement:

UPDATE msdb.dbo.sysdtspackagefolders90
SET foldername = 'Maintenance Plans'
WHERE folderid='08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'

It worked for me perfectly fine... after that maintenance plans can be created again....

Best regards,
Wolfgang