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 line. Show all posts
Showing posts with label line. 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:
>
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'
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
Cannot Create local temp table using sp_executesql
Does anyone know why this doesn't work (it doesn't give an error if you comment out the last line, but it doesn't seem to create the table either):
DECLARE @.SQL nvarchar(128)
SET @.SQL = N'CREATE TABLE #MyTempTable (ID INT)'
EXEC sp_executesql @.SQL
SELECT * FROM #MyTempTable
Using the same syntax, this works:
CREATE TABLE #MyTempTable (ID INT)
SELECT * FROM #MyTempTable
As does this, removing the pound signs and creating the table in the context of the current db:
DECLARE @.SQL nvarchar(128)
SET @.SQL = N'CREATE TABLE MyTempTable (ID INT)'
EXEC sp_executesql @.SQL
SELECT * FROM MyTempTable
Interestingly, it works with a global temp table:
DECLARE @.SQL nvarchar(128)
SET @.SQL = N'CREATE TABLE ##MyTempTable (ID INT)'
EXEC sp_executesql @.SQL
SELECT * FROM ##MyTempTable
Is this because sp_executesql is operating under a different connection and so my connection does not recognize the local temp table created by sp_executesql?
Thanks,
-Dave
If it works with ## global temp table and not # local temp table it is scope related, Reporting services most times reject code with local temp table but accept global temp tables. SQL Server estimates the time needed for the task and decideds if local or temp table is needed because a stored proc will run with local temp table load the stored proc into a Reporting services dataset and it rejects it.|||Dynamic SQL is always executed in a separate scope than the calling batch. So any temporary objects that you create or SET options that you modify or variables you declare etc are available only within the dynamic SQL batch. Global temporary tables are connection specific so they will persist across the dynamic SQL batch. What are you trying to do with temporary tables?|||If you create your #Temp table BEFORE executing the dynamic SQL, the #Temp table will be available to the Dynamic SQL. You can then populate it with the dynamic SQL, and use that data after the dynamic SQL finishes. (IF the #Temp table exists BEFORE the dynamic SQL, it doesn't fall out of scope when the dynamic SQL concludes.)
For Example:
Code Snippet
USE Northwind
GO
CREATE TABLE #MyTemp
( RowID int IDENTITY,
LastName varchar(20)
)
DECLARE @.SQL nvarchar(250)
SET @.SQL = 'INSERT INTO #MyTemp SELECT LastName FROM Employees;'
EXECUTE sp_executesql @.SQL
SELECT *
FROM #MyTemp
RowID LastName
-- --
1 Buchanan
2 Callahan
3 Davolio
4 Dodsworth
5 Fuller
6 King
7 Leverling
8 Peacock
9 Smith
10 Suyama
Subscribe to:
Posts (Atom)