Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
Michael
Because Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
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
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegr oups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>
|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> 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
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegr oups.com...
|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
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
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegro ups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>
Showing posts with label level. Show all posts
Showing posts with label level. Show all posts
Tuesday, March 27, 2012
Cannot enable SQL Broker
Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
MichaelBecause Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
--
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
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> 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
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> > Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> > Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> >
> > USE [master];
> > GO
> >
> > EXEC sp_configure 'clr enabled', 1;
> > RECONFIGURE WITH OVERRIDE;
> > GO
> >
> > ALTER DATABASE [master] SET ENABLE_BROKER
> >
> >
> > I am running SQL 2005 Developer Edition (SP1) with everything
> > installed.
> >
> > Thank you,
> > Michael
> >|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
--
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
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegroups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>> Because Service Broker cannot run in master. What use case causes you to
>> want to run Service Broker in the master DB? In general master should
>> contain only fairly static data and not highly dynamics data such as
>> messages on a queue.
>> --
>> 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
>> <mpaine@.htxml.com> wrote in message
>> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
>> > Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
>> > Option 'ENABLE_BROKER' cannot be set in database 'master'.?
>> >
>> > USE [master];
>> > GO
>> >
>> > EXEC sp_configure 'clr enabled', 1;
>> > RECONFIGURE WITH OVERRIDE;
>> > GO
>> >
>> > ALTER DATABASE [master] SET ENABLE_BROKER
>> >
>> >
>> > I am running SQL 2005 Developer Edition (SP1) with everything
>> > installed.
>> >
>> > Thank you,
>> > Michael
>> >
>
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
MichaelBecause Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
--
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
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> 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
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> > Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> > Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> >
> > USE [master];
> > GO
> >
> > EXEC sp_configure 'clr enabled', 1;
> > RECONFIGURE WITH OVERRIDE;
> > GO
> >
> > ALTER DATABASE [master] SET ENABLE_BROKER
> >
> >
> > I am running SQL 2005 Developer Edition (SP1) with everything
> > installed.
> >
> > Thank you,
> > Michael
> >|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
--
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
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegroups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>> Because Service Broker cannot run in master. What use case causes you to
>> want to run Service Broker in the master DB? In general master should
>> contain only fairly static data and not highly dynamics data such as
>> messages on a queue.
>> --
>> 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
>> <mpaine@.htxml.com> wrote in message
>> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
>> > Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
>> > Option 'ENABLE_BROKER' cannot be set in database 'master'.?
>> >
>> > USE [master];
>> > GO
>> >
>> > EXEC sp_configure 'clr enabled', 1;
>> > RECONFIGURE WITH OVERRIDE;
>> > GO
>> >
>> > ALTER DATABASE [master] SET ENABLE_BROKER
>> >
>> >
>> > I am running SQL 2005 Developer Edition (SP1) with everything
>> > installed.
>> >
>> > Thank you,
>> > Michael
>> >
>
Cannot enable SQL Broker
Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
MichaelBecause Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
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
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> 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
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
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
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegroups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
MichaelBecause Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
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
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> 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
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
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
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegroups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>
Sunday, March 25, 2012
Cannot Drop Server
I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?
It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>
|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>
|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?
It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>
|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>
|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
Cannot Drop Server
I am receiving the following error when attempting to drop a server to renam
e
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>
e
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>
Cannot Drop Server
I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
> >I am receiving the following error when attempting to drop a server to
> >rename
> > it
> >
> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> > Line
> > 39
> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> > replication.
> >
> > Replication is not occuring on this server.
> > When I execute sp_get_distributor the results show that replication is not
> > occuring on this server.
> >
> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> > My question is should I delete the record that is in this table to allow
> > renaming of the server?
> >
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>> It is possible this server has a remote distributor. Issue a call to
>> sp_helpdistributor to get the server which is acting as the distributor's
>> name.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in
>> message
>> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>> >I am receiving the following error when attempting to drop a server to
>> >rename
>> > it
>> >
>> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
>> > Line
>> > 39
>> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
>> > replication.
>> >
>> > Replication is not occuring on this server.
>> > When I execute sp_get_distributor the results show that replication is
>> > not
>> > occuring on this server.
>> >
>> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
>> > My question is should I delete the record that is in this table to
>> > allow
>> > renaming of the server?
>> >
>>sql
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
> >I am receiving the following error when attempting to drop a server to
> >rename
> > it
> >
> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> > Line
> > 39
> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> > replication.
> >
> > Replication is not occuring on this server.
> > When I execute sp_get_distributor the results show that replication is not
> > occuring on this server.
> >
> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> > My question is should I delete the record that is in this table to allow
> > renaming of the server?
> >
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>> It is possible this server has a remote distributor. Issue a call to
>> sp_helpdistributor to get the server which is acting as the distributor's
>> name.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in
>> message
>> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>> >I am receiving the following error when attempting to drop a server to
>> >rename
>> > it
>> >
>> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
>> > Line
>> > 39
>> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
>> > replication.
>> >
>> > Replication is not occuring on this server.
>> > When I execute sp_get_distributor the results show that replication is
>> > not
>> > occuring on this server.
>> >
>> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
>> > My question is should I delete the record that is in this table to
>> > allow
>> > renaming of the server?
>> >
>>sql
cannot drop index....it is being used for Foreign Key Enforcement ??
I try to drop a Non Clustered index on a column and get the following
Message
Server: Msg 3723, Level 16, State 6
An Explicit DROP INDEX is not allowed on index
'dbo.Jobs.nci_Jobs_StartTime'. It is being used for FOREIGN KEY constraint
Enforcement.
Ummm...No It's not...!
I scripted the entire DB and searched for any foreign key referencing this
column and it simply does not exist in the script.
I just wanna drop the index named nci_Jobs_StartTime (is that so wrong?)
Here is the Jobs Table Script:
CREATE TABLE [dbo].[Jobs] (
[JobUID] [uniqueidentifier] NOT NULL ,
[ParentJobUID] [uniqueidentifier] NULL ,
[JobTypeID] [tinyint] NOT NULL ,
[JobStatusID] [tinyint] NOT NULL ,
[CNCSystemID] [tinyint] NULL ,
[JobStep] [tinyint] NULL ,
[HasSubJobs] [bit] NOT NULL ,
[CurrentSubJobStep] [tinyint] NULL ,
[JobStartTime] [datetime] NULL ,
[JobStopTime] [datetime] NULL ,
[JobPickedUpAtTime] [datetime] NULL ,
[JobPickedUpByMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1
_CI_AS
NULL ,
[JobData] [varbinary] (4096) NULL ,
[JobRawData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Retry] [tinyint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
(
[JobUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [DF__Jobs__Retry__4AB81AF0] DEFAULT (0) FOR [Retry]
GO
CREATE INDEX [nci_Jobs_StartTime] ON [dbo].[Jobs]([JobStart
Time]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] ADD
CONSTRAINT [FK_Jobs_CNCSystem] FOREIGN KEY
(
[CNCSystemID]
) REFERENCES [dbo].[CNCSystem] (
[CNCSystemID]
),
CONSTRAINT [FK_Jobs_Jobs] FOREIGN KEY
(
[ParentJobUID]
) REFERENCES [dbo].[Jobs] (
[JobUID]
),
CONSTRAINT [FK_Jobs_JobStatus] FOREIGN KEY
(
[JobStatusID]
) REFERENCES [dbo].[JobStatus] (
[JobStatusID]
),
CONSTRAINT [FK_Jobs_JobType] FOREIGN KEY
(
[JobTypeID]
) REFERENCES [dbo].[JobType] (
[JobTypeID]
)
GOYou have to drop foreign key, below is the syntax:
ALTER TABLE tablename
DROP CONSTRAINT foreign key
Hope this will help you|||there is no foreign key.
that is the problem.
GAJ
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
> You have to drop foreign key, below is the syntax:
> ALTER TABLE tablename
> DROP CONSTRAINT foreign key
> Hope this will help you|||Strange... What does sp_helpconstraint say?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:O1BKuojBEHA.3748@.tk2msftngp13.phx.gbl...
> there is no foreign key.
> that is the problem.
>
> GAJ
>
> "Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
> news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
>|||does not list any FKEY on the JobStartTime Column.
I look in SQL
I look in Toad
I look everywhere
no sign of any FKEY on jobs.JobStartTime
The weird thing is, I can "Change" the index definition. I modified the
index to use a different column, etc. No issues.
I just cant drop the stupid thing.
GAJ|||Strange... Can you produce a repro? If not, I'd suspect some type of
corruption somewhere. Try DBCC CHECKDB and DBCC CHECKCATALOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uJ$OTasBEHA.3524@.TK2MSFTNGP10.phx.gbl...
> does not list any FKEY on the JobStartTime Column.
> I look in SQL
> I look in Toad
> I look everywhere
> no sign of any FKEY on jobs.JobStartTime
> The weird thing is, I can "Change" the index definition. I modified the
> index to use a different column, etc. No issues.
> I just cant drop the stupid thing.
>
> GAJ
>|||what's a "Repro"
I ran dbcc checkdb and all seems well.
this is very very weird
GAJ|||"Repro" = Code with which we can reproduce the error.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uCPqr9sBEHA.3064@.tk2msftngp13.phx.gbl...
> what's a "Repro"
> I ran dbcc checkdb and all seems well.
> this is very very weird
>
> GAJ
>|||the ddl is in my first post.
I wish there was something else I could give you, I'd do it in a heartbeat.
Thanks for the feedback up to now anyway
GAJ|||I'm sorry, but that is not a complete repro. There are tables that you
reference in your FK constraint which are lacking. The point of a repro is
for use to reproduce the same error message. In this case, I didn't find
anything obvious, but sometime you see the problem if you have it in front
of you. Hence the request for a repro. If you can't produce such, it might
be time for opening a case with MS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uSIEoBvBEHA.3344@.tk2msftngp13.phx.gbl...
> the ddl is in my first post.
> I wish there was something else I could give you, I'd do it in a
heartbeat.
> Thanks for the feedback up to now anyway
>
> GAJ
>sql
Message
Server: Msg 3723, Level 16, State 6
An Explicit DROP INDEX is not allowed on index
'dbo.Jobs.nci_Jobs_StartTime'. It is being used for FOREIGN KEY constraint
Enforcement.
Ummm...No It's not...!
I scripted the entire DB and searched for any foreign key referencing this
column and it simply does not exist in the script.
I just wanna drop the index named nci_Jobs_StartTime (is that so wrong?)
Here is the Jobs Table Script:
CREATE TABLE [dbo].[Jobs] (
[JobUID] [uniqueidentifier] NOT NULL ,
[ParentJobUID] [uniqueidentifier] NULL ,
[JobTypeID] [tinyint] NOT NULL ,
[JobStatusID] [tinyint] NOT NULL ,
[CNCSystemID] [tinyint] NULL ,
[JobStep] [tinyint] NULL ,
[HasSubJobs] [bit] NOT NULL ,
[CurrentSubJobStep] [tinyint] NULL ,
[JobStartTime] [datetime] NULL ,
[JobStopTime] [datetime] NULL ,
[JobPickedUpAtTime] [datetime] NULL ,
[JobPickedUpByMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1
_CI_AS
NULL ,
[JobData] [varbinary] (4096) NULL ,
[JobRawData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Retry] [tinyint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
(
[JobUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [DF__Jobs__Retry__4AB81AF0] DEFAULT (0) FOR [Retry]
GO
CREATE INDEX [nci_Jobs_StartTime] ON [dbo].[Jobs]([JobStart
Time]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] ADD
CONSTRAINT [FK_Jobs_CNCSystem] FOREIGN KEY
(
[CNCSystemID]
) REFERENCES [dbo].[CNCSystem] (
[CNCSystemID]
),
CONSTRAINT [FK_Jobs_Jobs] FOREIGN KEY
(
[ParentJobUID]
) REFERENCES [dbo].[Jobs] (
[JobUID]
),
CONSTRAINT [FK_Jobs_JobStatus] FOREIGN KEY
(
[JobStatusID]
) REFERENCES [dbo].[JobStatus] (
[JobStatusID]
),
CONSTRAINT [FK_Jobs_JobType] FOREIGN KEY
(
[JobTypeID]
) REFERENCES [dbo].[JobType] (
[JobTypeID]
)
GOYou have to drop foreign key, below is the syntax:
ALTER TABLE tablename
DROP CONSTRAINT foreign key
Hope this will help you|||there is no foreign key.
that is the problem.
GAJ
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
> You have to drop foreign key, below is the syntax:
> ALTER TABLE tablename
> DROP CONSTRAINT foreign key
> Hope this will help you|||Strange... What does sp_helpconstraint say?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:O1BKuojBEHA.3748@.tk2msftngp13.phx.gbl...
> there is no foreign key.
> that is the problem.
>
> GAJ
>
> "Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
> news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
>|||does not list any FKEY on the JobStartTime Column.
I look in SQL
I look in Toad
I look everywhere
no sign of any FKEY on jobs.JobStartTime
The weird thing is, I can "Change" the index definition. I modified the
index to use a different column, etc. No issues.
I just cant drop the stupid thing.
GAJ|||Strange... Can you produce a repro? If not, I'd suspect some type of
corruption somewhere. Try DBCC CHECKDB and DBCC CHECKCATALOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uJ$OTasBEHA.3524@.TK2MSFTNGP10.phx.gbl...
> does not list any FKEY on the JobStartTime Column.
> I look in SQL
> I look in Toad
> I look everywhere
> no sign of any FKEY on jobs.JobStartTime
> The weird thing is, I can "Change" the index definition. I modified the
> index to use a different column, etc. No issues.
> I just cant drop the stupid thing.
>
> GAJ
>|||what's a "Repro"
I ran dbcc checkdb and all seems well.
this is very very weird
GAJ|||"Repro" = Code with which we can reproduce the error.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uCPqr9sBEHA.3064@.tk2msftngp13.phx.gbl...
> what's a "Repro"
> I ran dbcc checkdb and all seems well.
> this is very very weird
>
> GAJ
>|||the ddl is in my first post.
I wish there was something else I could give you, I'd do it in a heartbeat.
Thanks for the feedback up to now anyway
GAJ|||I'm sorry, but that is not a complete repro. There are tables that you
reference in your FK constraint which are lacking. The point of a repro is
for use to reproduce the same error message. In this case, I didn't find
anything obvious, but sometime you see the problem if you have it in front
of you. Hence the request for a repro. If you can't produce such, it might
be time for opening a case with MS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uSIEoBvBEHA.3344@.tk2msftngp13.phx.gbl...
> the ddl is in my first post.
> I wish there was something else I could give you, I'd do it in a
heartbeat.
> Thanks for the feedback up to now anyway
>
> GAJ
>sql
Cannot Drop Index
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda
ALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda
|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda
|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use and
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
> not work.
> Gracias. LaEsmeralda
>
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda
ALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda
|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda
|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use and
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
> not work.
> Gracias. LaEsmeralda
>
Cannot Drop Index
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeraldaALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use a
nd
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it di
d
> not work.
> Gracias. LaEsmeralda
>
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeraldaALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use a
nd
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it di
d
> not work.
> Gracias. LaEsmeralda
>
Thursday, March 22, 2012
Cannot deploy assembly from VS.NET into SQL server 2005
Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?
Thanx, Fipil
Can you:
1. Post or describe your code?
2. See if the same error happens when you run CREATE ASSEMBLY, CREATE FUNCTION etc. in Management Studio, rather than deploying through Visual Studio?
3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?
Thanks|||
1. Here is my code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}
private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;
byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;
}
};
2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:
CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;
3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.
Can you create and deploy other assemblies without seeing this error?
|||When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.
On SQL server logs I found this message:
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is SQL DUMP:
2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *
Can you email me the full log and the mini-dump (mdmp) file?
stevehem at microsoft dot com
Thanks
|||Thanks for reporting this and for sending me the dump.
This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.
The workaround is to run:
exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'
|||Greetings...I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?
Best regards...
|||
If using SQL authentication (you enter a username and password to connect to your server)
Code Snippet
EXEC sp_changedbown 'sa'should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:
Code Snippet
EXEC sp_changedbown 'BUILTIN\Administrators'
sqlCannot deploy assembly from VS.NET into SQL server 2005
Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?
Thanx, Fipil
Can you:
1. Post or describe your code?
2. See if the same error happens when you run CREATE ASSEMBLY, CREATE
FUNCTION etc. in Management Studio, rather than deploying through
Visual Studio?
3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?
Thanks|||
1. Here is my code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}
private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;
byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;
}
};
2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:
CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;
3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.
Can you create and deploy other assemblies without seeing this error?
|||When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.
On SQL server logs I found this message:
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is SQL DUMP:
2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *
Can you email me the full log and the mini-dump (mdmp) file?
stevehem at microsoft dot com
Thanks
|||Thanks for reporting this and for sending me the dump.
This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.
The workaround is to run:
exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'
|||Greetings...I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?
Best regards...|||
If using SQL authentication (you enter a username and password to connect to your server)
Code Snippet
EXEC sp_changedbown 'sa'should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:
Code Snippet
EXEC sp_changedbown 'BUILTIN\Administrators'
Cannot deploy assembly from VS.NET into SQL server 2005
Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?
Thanx, Fipil
Can you:
1. Post or describe your code?
2. See if the same error happens when you run CREATE ASSEMBLY, CREATE FUNCTION etc. in Management Studio, rather than deploying through Visual Studio?
3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?
Thanks|||
1. Here is my code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}
private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;
byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;
}
};
2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:
CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;
3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.
Can you create and deploy other assemblies without seeing this error?
|||When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.
On SQL server logs I found this message:
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is SQL DUMP:
2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *
Can you email me the full log and the mini-dump (mdmp) file?
stevehem at microsoft dot com
Thanks
|||Thanks for reporting this and for sending me the dump.
This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.
The workaround is to run:
exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'
|||Greetings...I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?
Best regards...
|||
If using SQL authentication (you enter a username and password to connect to your server)
Code Snippet
EXEC sp_changedbown 'sa'should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:
Code Snippet
EXEC sp_changedbown 'BUILTIN\Administrators'
Cannot deploy assembly from VS.NET into SQL server 2005
Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?
Thanx, Fipil
Can you:
1. Post or describe your code?
2. See if the same error happens when you run CREATE ASSEMBLY, CREATE
FUNCTION etc. in Management Studio, rather than deploying through
Visual Studio?
3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?
Thanks|||
1. Here is my code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}
private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;
byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;
}
};
2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:
CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;
3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.
Can you create and deploy other assemblies without seeing this error?
|||When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.
On SQL server logs I found this message:
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is SQL DUMP:
2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *
Can you email me the full log and the mini-dump (mdmp) file?
stevehem at microsoft dot com
Thanks
|||Thanks for reporting this and for sending me the dump.
This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.
The workaround is to run:
exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'
|||Greetings...I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?
Best regards...|||
If using SQL authentication (you enter a username and password to connect to your server)
Code Snippet
EXEC sp_changedbown 'sa'should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:
Code Snippet
EXEC sp_changedbown 'BUILTIN\Administrators'
Tuesday, March 20, 2012
Cannot create view WITH VIEW_METADATA
I'm trying to create a view and make it updatable using an INSTEAD OF UPDATE trigger. This works OK on SQL Server 2005 (even with compatibility level 80), but on SQL Server 2000 I receive an error message:
"Could not perform CREATE VIEW because WITH VIEW_METADATA was specified and the view is not updatable."
The following sql reproduces the problem.
CREATE TABLE test (a int, b int)
go
INSERT INTO test VALUES(1,0)
INSERT INTO test VALUES(2,0)
INSERT INTO test VALUES(3,1)
INSERT INTO test VALUES(3,1)
go
CREATE VIEW testview WITH VIEW_METADATA AS
SELECT a,SUM(b) b FROM test GROUP BY a
go
CREATE TRIGGER TR_testview_U ON testview INSTEAD OF UPDATE AS
BEGIN
UPDATE test SET b=inserted.b
FROM test INNER JOIN inserted ON test.a = inserted.a
END
go
SELECT * FROM test
SELECT * FROM testview
UPDATE testview SET b=2 WHERE a=3
SELECT * FROM test
SELECT * FROM testview
go
DROP VIEW testview
DROP TABLE test
I want my view to be updatable so I can use it in my VB6/ADO application, and the trigger won't fire unless I specify VIEW_METADATA.
When you create any view with following operation (operator/clause) , that view will be become as NOT UPDATEABLE VIWE, when it is not UPDATABLE you can’t specify the WITH VIEW_METADATA.
TOP,
GROUP BY,
UNION
DISTINCT
|||Thanks for the reply. As I said in SQL Server 2005 this works, just wondering why not in SQL Server 2000 - WITH VIEW_METADATA is precisely for making non UPDATABLE views updatable, according to BOL"When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers."
which seems to imply that the columns wouldn't have been updatable without VIEW_METADATA.
Monday, March 19, 2012
Cannot create new database
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
SeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx
|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?
|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx
|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
SeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx
|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?
|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx
|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Cannot create new database
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try t
o
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try t
o
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Cannot create new database
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Subscribe to:
Posts (Atom)