Cannot Expanding MSDB in SSIS

Hi,
I am trying to expanding MSDB under SSIS and getting following error:
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
ADDITIONAL INFORMATION:
The SQL server specified in SSIS service configuration is not present or is
not available. This might occur when there is no default instance of SQL
Server on the computer. For more information, see the topic "Configuring the
Integration Services Service" in Server 2005 Books Online.
Login timeout expired
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
TCP Provider: No connection could be made because the target machine
actively refused it. (MsDtsSrvr)
My sql server is clustered and SSIS is clustered also.
Thanks in advance!
Yuhong
Is SSIS in the same resource group as the SQL Server? Is the SQL Server the
default instance or a named instance?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Yuhong" wrote:

> Hi,
> I am trying to expanding MSDB under SSIS and getting following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
> --
> ADDITIONAL INFORMATION:
> The SQL server specified in SSIS service configuration is not present or is
> not available. This might occur when there is no default instance of SQL
> Server on the computer. For more information, see the topic "Configuring the
> Integration Services Service" in Server 2005 Books Online.
> Login timeout expired
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> TCP Provider: No connection could be made because the target machine
> actively refused it. (MsDtsSrvr)
> --
> My sql server is clustered and SSIS is clustered also.
> Thanks in advance!
> --
> Yuhong
|||SSIS is in the same resource group as the SQL Server. I think it is a named
instance in the cluster. I don't think I have a default instance configured
in the cluster. Is there a work around so I can expand the msdb with out
reconfigure the servers?
Thanks for helping!
Yuhong
"mrdenny" wrote:
[vbcol=seagreen]
> Is SSIS in the same resource group as the SQL Server? Is the SQL Server the
> default instance or a named instance?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
>
> "Yuhong" wrote:
|||This is a fairly easy fix, I actually just posted a blog entry walking
through the fix. The problem is that SSIS is not clusterable, and the
config files on the active node are pointing MSDB to the node itself.
The config files need to point to the cluster name.
http://www.sqlstop.com/index.php/2007/07/16/clusters-last-stand/
That details exactly how to fix the problem.
Thanks,
Shawn
*** Sent via Developersdex http://www.codecomments.com ***
|||Shawn,
Thanks so much for the information. I got it working. I almost gave up and
decided to store everything in File System.
It is surprising that there are not much informaton about this online, at
least I did not find any. Thanks again!!!!
Yuhong
"Shawn m" wrote:

> This is a fairly easy fix, I actually just posted a blog entry walking
> through the fix. The problem is that SSIS is not clusterable, and the
> config files on the active node are pointing MSDB to the node itself.
> The config files need to point to the cluster name.
> http://www.sqlstop.com/index.php/2007/07/16/clusters-last-stand/
> That details exactly how to fix the problem.
> Thanks,
> Shawn
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||*** Sent via Developersdex http://www.codecomments.com ***
sql

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error message:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
Limmer
Try:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>
|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:

> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
>
>

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error messag
e:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
LimmerTry:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:

> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
>
>

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error message:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
LimmerTry:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:
> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> > When I try to enable the Service Broker I receive the following error
> > message:
> >
> > Cannot enable the Service Broker in database "msdb" because the Service
> > Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> > not
> > match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> > ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> >
> > So as far as I can understand in the MSDB database the Service Broker GUID
> > is different from the one in the table sys.databases.
> > Is there a possibility to correct this so I will be able to enable the
> > Service Broker?
> > I want to enable it so I can configure the email part on this SQL server.
> >
> > If the GUID itself can not be changed is there a way to rebuild the MSDB
> > database?
> >
> > Thanks for the info,
> > Limmer
> >
>
>sql

Cannot delete or edit transfered SQL Server Jobs

Hello,

I have a problem with EM and the msdb. I get an error message 14274
when I try to edit or delete a job. The data in the msdb for the jobs
was generated by a restored backup that came from another machine, and
a different instance of SQL Server. The old default server instance
was called 'SQL_Server' and the new default instance is called
'SDO-SQL'.
The error message states:

Cannot add, update, or delete a job (or its steps or schedules) that
originated from an MSX server.

I don't have a master scheduling server. I get the error
message when attempting to change a job. eg. enable/disable a job,
change the time, delete a job, add a job.

Server is running w2k Server SP4

I tired the following:

USE MSDB

UPDATE sysjobs

SET originating_server = 'SD0-SQL'

From the properties of any job, I can see that the 'Source' is now
correctly identified as 'SDO-SQL', but the problem still remains.

What can I do with these jobs so that I can edit and delete them on
the new server?

Thank you for your help!

CSDunnHi

It looks like you may have already seen

http://support.microsoft.com/defaul...kb;en-us;281642

It suggests that you rename the server back to the old server name rather
than mess with the tables, but if you are trying to update sysjob I would
expect originating_server to be in the form 'server\instance'.

John

"CSDunn" <cdunn@.valverde.edu> wrote in message
news:807dbff7.0405151632.2f68ffe8@.posting.google.c om...
> Hello,
> I have a problem with EM and the msdb. I get an error message 14274
> when I try to edit or delete a job. The data in the msdb for the jobs
> was generated by a restored backup that came from another machine, and
> a different instance of SQL Server. The old default server instance
> was called 'SQL_Server' and the new default instance is called
> 'SDO-SQL'.
> The error message states:
> Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server.
> I don't have a master scheduling server. I get the error
> message when attempting to change a job. eg. enable/disable a job,
> change the time, delete a job, add a job.
> Server is running w2k Server SP4
> I tired the following:
> USE MSDB
> UPDATE sysjobs
> SET originating_server = 'SD0-SQL'
> From the properties of any job, I can see that the 'Source' is now
> correctly identified as 'SDO-SQL', but the problem still remains.
> What can I do with these jobs so that I can edit and delete them on
> the new server?
> Thank you for your help!
> CSDunn|||John,
Thank you for your help!

CSDunn

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<0lEpc.948$XY4.12507228@.news-text.cableinet.net>...
> Hi
> It looks like you may have already seen
> http://support.microsoft.com/defaul...kb;en-us;281642
> It suggests that you rename the server back to the old server name rather
> than mess with the tables, but if you are trying to update sysjob I would
> expect originating_server to be in the form 'server\instance'.
> John
> "CSDunn" <cdunn@.valverde.edu> wrote in message
> news:807dbff7.0405151632.2f68ffe8@.posting.google.c om...
> > Hello,
> > I have a problem with EM and the msdb. I get an error message 14274
> > when I try to edit or delete a job. The data in the msdb for the jobs
> > was generated by a restored backup that came from another machine, and
> > a different instance of SQL Server. The old default server instance
> > was called 'SQL_Server' and the new default instance is called
> > 'SDO-SQL'.
> > The error message states:
> > Cannot add, update, or delete a job (or its steps or schedules) that
> > originated from an MSX server.
> > I don't have a master scheduling server. I get the error
> > message when attempting to change a job. eg. enable/disable a job,
> > change the time, delete a job, add a job.
> > Server is running w2k Server SP4
> > I tired the following:
> > USE MSDB
> > UPDATE sysjobs
> > SET originating_server = 'SD0-SQL'
> > From the properties of any job, I can see that the 'Source' is now
> > correctly identified as 'SDO-SQL', but the problem still remains.
> > What can I do with these jobs so that I can edit and delete them on
> > the new server?
> > Thank you for your help!
> > CSDunn|||I found another workaround (OS W2K3 Server, SQL Server 2K with SP4):

1. Script out all of the jobs.
2. Update the job source by:
USE MSDB
UPDATE sysjobs
SET originating_server = 'New_Server_Name'
3. Rename the jobs to be deleted.
4. Delete the jobs.
5. Add back the jobs by running the script generated from step 1.

Hope this will work better!

Ray

Cannot Connect to a Remote SSIS Server

I originally posted this issue on the SSIS forum and it was suggested I cross post here. I'm trying to connect to the MSDB database on a remote SSIS server using the integration services connection listed in the drop down box on SQL Server 2005 Management Studio. Every connection attempt ends with an 'Access Is Denied' error message. I have full db_dtsadmin rights on the remote server. We followed the MSDN whitepaper on connecting to a remote SSIS server (the section on eliminating the Access Is Denied error - configuring rights for remote users). Nothing works, I always get the generic access denied error message. Are there other permissions that need to be assigned in conjunction with db_dtsadmin to get this to work? I wonder if there is something simple we're overlooking.

Thanks.

Can you include a link to your original post?

SQL Server does not respond to connection attempts with "Access Denied" messages, so you would need to do more investigation into who is returning that error message. To see if the connection request reaches the server, you can use Profiler. You could also look at the server error log (the ERRORLOG files) and search for connection errors - these would most likely appear as "Login failed for user ...". If the connection request does not reach the server, then the problem is elsewhere.

Thanks
Laurentiu

|||To connect to a remote SSIS server when you aren't in the Administrators group of the remote server you need to have Activation permissions (remote) for the MsDtsServer Comm Object (In the Component Services console) and also be a member of the Comm Plus Users local group (it's called something other than that, I can't remember the exact name).|||Also check this http://msdn2.microsoft.com/en-us/library/aa337083.aspx blog.