Cluster Server Planning.

I would like some advise on my plan to install an active/active sql cluster.
The plan is to build 2 boxes with W2k3 server and SQL2003. I'll have 13
drives on a shared disk storage (two sets of mirrored drives) (two sets of
Raid 5 [4 drives each]) (1 global hot spare). I'll create a 1 Gig logical
drive (Q & P)on each of the mirrored sets (P will be a right-off but will
keep everything looking the same). The Q drive is for the Quorum. So I'll
have C: and D: on each machine, (Q: Emirrored drives F: Raid5 (P: G
mirrored drives H: Raid5. SQL1 will own Q: E: and F:, SQL2 will own P: G:
and H:. E: and G: will be for transaction logs while F: and H: are for the
databases. I'll install instances of sql running on each server and spilt
the databases between the two (we have around fifty). Is this a sound plan,
or have I just wasted my time?
That sounds pretty good. I would seriously look at RAID 10 rather than RAID
5. The difference in write performance can be huge.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:9AD5CF79-5664-466C-AB15-F48C4F99972C@.microsoft.com...
> I would like some advise on my plan to install an active/active sql
cluster.
> The plan is to build 2 boxes with W2k3 server and SQL2003. I'll have 13
> drives on a shared disk storage (two sets of mirrored drives) (two sets of
> Raid 5 [4 drives each]) (1 global hot spare). I'll create a 1 Gig logical
> drive (Q & P)on each of the mirrored sets (P will be a right-off but will
> keep everything looking the same). The Q drive is for the Quorum. So I'll
> have C: and D: on each machine, (Q: Emirrored drives F: Raid5 (P: G
> mirrored drives H: Raid5. SQL1 will own Q: E: and F:, SQL2 will own P:
G:
> and H:. E: and G: will be for transaction logs while F: and H: are for
the
> databases. I'll install instances of sql running on each server and spilt
> the databases between the two (we have around fifty). Is this a sound
plan,
> or have I just wasted my time?
|||I do not understand the Q and P drive assignment.
I have a Q (Quorum) in a seperate cluster resource group (with seperate
ip-address an networkname)
I have a X (MSDTC) in a seperate cluster resource group (with seperate
ip-address an networkname)
The Q and X drive do not come back in the SQL cluster resource group's
You do not mention a seperate cluster resource group for MSDTC. You should
do that.
Gr. G
(more info on MSDTC: http://sswug.org/blogging/gbrander/)
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:9AD5CF79-5664-466C-AB15-F48C4F99972C@.microsoft.com...
>I would like some advise on my plan to install an active/active sql
>cluster.
> The plan is to build 2 boxes with W2k3 server and SQL2003. I'll have 13
> drives on a shared disk storage (two sets of mirrored drives) (two sets of
> Raid 5 [4 drives each]) (1 global hot spare). I'll create a 1 Gig logical
> drive (Q & P)on each of the mirrored sets (P will be a right-off but will
> keep everything looking the same). The Q drive is for the Quorum. So I'll
> have C: and D: on each machine, (Q: Emirrored drives F: Raid5 (P: G
> mirrored drives H: Raid5. SQL1 will own Q: E: and F:, SQL2 will own P:
> G:
> and H:. E: and G: will be for transaction logs while F: and H: are for
> the
> databases. I'll install instances of sql running on each server and spilt
> the databases between the two (we have around fifty). Is this a sound
> plan,
> or have I just wasted my time?
|||I am not planning going to use and entire physical disk for the Quorum. I'm
going to make a partition on a mirrored set (that will be a physical disk)
that will be the Q: drive. The P: drive is just my way of keeping
everything looking the same. My plan is to have only 2 cluster groups.
Group 1 be will the Cluster IP, Cluster Name, the Physical Disk (E: Q, the
Physical Disk (F, the MSDTC, and the first instance of SQL. Group 1 will
be owned by server1. Group 2 be will the Physical Disk (G: P, the
Physical Disk (H, and the second instance of SQL. Group 2 will be owned by
server2. Will this work ?
"Gé Brander" wrote:

> I do not understand the Q and P drive assignment.
> I have a Q (Quorum) in a seperate cluster resource group (with seperate
> ip-address an networkname)
> I have a X (MSDTC) in a seperate cluster resource group (with seperate
> ip-address an networkname)
> The Q and X drive do not come back in the SQL cluster resource group's
> You do not mention a seperate cluster resource group for MSDTC. You should
> do that.
> Gr. Gé
> (more info on MSDTC: http://sswug.org/blogging/gbrander/)
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:9AD5CF79-5664-466C-AB15-F48C4F99972C@.microsoft.com...
>
>
|||I too and lost by your wording I think, or maybe cause its Monday here. Are
you saying you will split a LUN into 2 or more partitions and then try to
use different partitions with different nodes? Clustering does not deal with
partitions, only drives. So a node or instance will not be able to share a
drive (and one or more partitions) with another node/instance.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:3AA6C951-0E13-4619-906F-BB0B0DB0C463@.microsoft.com...[vbcol=seagreen]
>I am not planning going to use and entire physical disk for the Quorum.
>I'm
> going to make a partition on a mirrored set (that will be a physical disk)
> that will be the Q: drive. The P: drive is just my way of keeping
> everything looking the same. My plan is to have only 2 cluster groups.
> Group 1 be will the Cluster IP, Cluster Name, the Physical Disk (E: Q,
> the
> Physical Disk (F, the MSDTC, and the first instance of SQL. Group 1
> will
> be owned by server1. Group 2 be will the Physical Disk (G: P, the
> Physical Disk (H, and the second instance of SQL. Group 2 will be owned
> by
> server2. Will this work ?
> "G Brander" wrote:
|||Good catch Rodney.
Clustering looks at physical disks (LUNs in SAN-speak). If you partition
the disk, clustering still sees the underlying physical disk.
Also, you don't want your Quorum disk to be part of your SQL Resource group.
That is a Low-Availability approach. Putting MSDCT in with a SQL instance
is an even worse approach. You need one disk for the Quorum, preferably one
disk for MSDTC (although it can be the same as the Quorum disk), and at
least one (preferably two or more) disks per SQL instance. These must be
physical disks or separate LUNs from a SAN device. Anything else will
compromise availability to the point that a cluster won't buy you any higher
availability.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:3AA6C951-0E13-4619-906F-BB0B0DB0C463@.microsoft.com...
> I am not planning going to use and entire physical disk for the Quorum.
I'm
> going to make a partition on a mirrored set (that will be a physical disk)
> that will be the Q: drive. The P: drive is just my way of keeping
> everything looking the same. My plan is to have only 2 cluster groups.
> Group 1 be will the Cluster IP, Cluster Name, the Physical Disk (E: Q,
the
> Physical Disk (F, the MSDTC, and the first instance of SQL. Group 1
will
> be owned by server1. Group 2 be will the Physical Disk (G: P, the
> Physical Disk (H, and the second instance of SQL. Group 2 will be owned
by[vbcol=seagreen]
> server2. Will this work ?
> "G Brander" wrote:
should[vbcol=seagreen]
13[vbcol=seagreen]
sets of[vbcol=seagreen]
logical[vbcol=seagreen]
will[vbcol=seagreen]
I'll[vbcol=seagreen]
G[vbcol=seagreen]
P:[vbcol=seagreen]
for[vbcol=seagreen]
spilt[vbcol=seagreen]
|||Rodney and Geoff, I admit my terminology was bad. I 'AM' going to have 2
physical disks (LUNs in SAN-speak) per instance of SQL. One for databases
and one for transaction logs. I was trying to see if I could cheat and 'NOT'
use an entire disk for the quorum, but looks like that will not work (or not
work very well) in a multiple instance cluster. So it looks like I'll need
14, probably 15 disks on my shared storage to make this work.
So how about plan B:
Setup one physical disk (probably mirrored) for the quorum and the MSDTC.
Setup one physical disk (mirrored) for the transaction logs for each instance
of SQL. Setup one physical disk (Raid 5 or maybe 10) for the databases for
each instance of SQL. And if I can afford it a global hot spare. Better to
get it right in the planning stage than looking like an idiot trying to get a
bad design to work.
Thanks
"Geoff N. Hiten" wrote:

> Good catch Rodney.
> Clustering looks at physical disks (LUNs in SAN-speak). If you partition
> the disk, clustering still sees the underlying physical disk.
> Also, you don't want your Quorum disk to be part of your SQL Resource group.
> That is a Low-Availability approach. Putting MSDCT in with a SQL instance
> is an even worse approach. You need one disk for the Quorum, preferably one
> disk for MSDTC (although it can be the same as the Quorum disk), and at
> least one (preferably two or more) disks per SQL instance. These must be
> physical disks or separate LUNs from a SAN device. Anything else will
> compromise availability to the point that a cluster won't buy you any higher
> availability.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:3AA6C951-0E13-4619-906F-BB0B0DB0C463@.microsoft.com...
> I'm
> the
> will
> by
> should
> 13
> sets of
> logical
> will
> I'll
> G
> P:
> for
> spilt
>
>
|||I like plan B, and not just cause I fully understand it. Question, are your
SQL applications going to use MSDTC? If so, for performance reasons you may
want to have a mirror just for the log and extend the default log size.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:4B6F25F5-FB78-4023-AE65-6F4335DA4940@.microsoft.com...[vbcol=seagreen]
> Rodney and Geoff, I admit my terminology was bad. I 'AM' going to have 2
> physical disks (LUNs in SAN-speak) per instance of SQL. One for databases
> and one for transaction logs. I was trying to see if I could cheat and
> 'NOT'
> use an entire disk for the quorum, but looks like that will not work (or
> not
> work very well) in a multiple instance cluster. So it looks like I'll need
> 14, probably 15 disks on my shared storage to make this work.
> So how about plan B:
> Setup one physical disk (probably mirrored) for the quorum and the MSDTC.
> Setup one physical disk (mirrored) for the transaction logs for each
> instance
> of SQL. Setup one physical disk (Raid 5 or maybe 10) for the databases for
> each instance of SQL. And if I can afford it a global hot spare. Better
> to
> get it right in the planning stage than looking like an idiot trying to
> get a
> bad design to work.
> Thanks
> "Geoff N. Hiten" wrote:
|||Rodney, yes I plan on the SQL applications using the MSDTC. Where can I find
info on how to extend the default log size.
Thanks for the heads up.
"Rodney R. Fournier [MVP]" wrote:

> I like plan B, and not just cause I fully understand it. Question, are your
> SQL applications going to use MSDTC? If so, for performance reasons you may
> want to have a mirror just for the log and extend the default log size.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:4B6F25F5-FB78-4023-AE65-6F4335DA4940@.microsoft.com...
>
>
|||First follow http://support.microsoft.com/kb/817064 on each machine BEFORE
you install the cluster service.
Then on each node - open component services - Computers - My Computer -
Properties - MSDTC tab - Capacity = 12 or 16 or anything larger then 4 MB,
close it out. Make both machines the same size log.
Install Microsoft clustering.
Finally follow
http://support.microsoft.com/default...b;en-us;301600
Then Install SQL in the Cluster.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:AD40F458-65B9-45DC-8CA0-5031975CD3DD@.microsoft.com...[vbcol=seagreen]
> Rodney, yes I plan on the SQL applications using the MSDTC. Where can I
> find
> info on how to extend the default log size.
> Thanks for the heads up.
> "Rodney R. Fournier [MVP]" wrote:

CLR procedure calling webservice

Hi, I want to create following procedure to call a webservice. build ok
execution not ok.
When i do it in a seperated program it works. in the clr procedure not.
It always end with 'System.InvalidOperationException' occurred in
System.Xml.dll
Can some one help me.
Ludo
SQL code:
exec dbo.SendStatusToWebservice 'SQL2K5','TEST Ludo','GREEN'
.Net code
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
public partial class CLR_Procedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
}
public static void SendStatusToWebservice(SqlString MyAppl, SqlString
MyMessage, SqlString MyStatus)
{
string log;
// Connect to webservice and add logging to it
SQL_UDP.bgc.wss.Library wlib = new SQL_UDP.bgc.wss.Library();
wlib.Credentials = System.Net.CredentialCache.DefaultCredentials;
log = wlib.WSScreateLog(MyAppl.Value);
wlib.WSSwriteLog(log, MyMessage.Value);// + " at @. " +
DateTime.Now.ToString);
wlib.SetBatchStatus(MyStatus.Value, log);
}
};
Debug result:
Auto-attach to process '[3068] [SQL] bgc-mikmxeue486' on machine
'bgc-mikmxeue486' succeeded.
Debugging script from project script file.
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
Auto-attach to process '[3068] sqlservr.exe' on machine 'bgc-mikmxeue486'
succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is
optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.
dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\Syst
em.Transactions.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System
.Security.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.
dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'SQL_UDP', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\Sy
stem.Web.Services.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\S
ystem.Configuration.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'WebserviceCLR', Symbols loaded.
A .NET Framework error occurred during execution of user defined routine or
aggregate 'CallWebservice':
System.InvalidOperationException: Cannot load dynamically generated
serialization assembly. In some hosting environments assembly load
functionality is restricted, consider using pre-generated serializer. Please
see inner exception for more information. --> System.IO.FileLoadException:
LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by
the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean
fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters
options, String[] fileNames)
at
Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters
options, String[] sources)
at
Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.C
ompileAssemblyFromSourceBatch(CompilerPa
rameters options, String[] sources)
at
System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerPa
rameter
..
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns,
CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[]
xmlMappings, Type[] types, String defaultNamespace, Evidence evidence,
CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings,
Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[]
mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at WebserviceCLR.wsslib.Library...
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
A first chance exception of type 'System.InvalidOperationException' occurred
in System.Xml.dll
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The program '[3068] [SQL] bgc-mikmxeue486: bgc-mikmxeue486' has exited with
code 0 (0x0).
The program '[3068] sqlservr.exe: Managed' has exited with code 259 (0x103)."examnotes" <Ludo@.discussions.microsoft.com> wrote in
news:825F081A-11D6-4D36-8C9A-F8B4AEA96CE5@.microsoft.com:

> Hi, I want to create following procedure to call a webservice. build
> ok execution not ok.
> When i do it in a seperated program it works. in the clr procedure
> not. It always end with 'System.InvalidOperationException' occurred
> in System.Xml.dll
> Can some one help me.
>
[snip]

> In some hosting
> environments assembly load functionality is restricted, consider using
> pre-generated serializer. Please see inner exception for more
> information. --> System.IO.FileLoadException: LoadFrom(), LoadFile(),
> Load(byte[]) and LoadModule() have been disabled by the host.
> System.IO.FileLoadException:
As the error says, SQLCLR doesn't allow you to load a dynamically
generated assembly (which happens when you do web-services). You need to
sgen the proxy code into a dll and catalogue that assembly in SQL
Server.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

CLR Enabled

Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

I changed the 'clr enabled' property to 1 using sp_configure but I still get this error.

Thanks
Mike

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go|||Thank you again Gorm!|||

Gorm Braarvig wrote:

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

Hi!

I'm having the same problem. I'm just wondrin what that 'something' is. Is that a placeholder of some sort? What can be the values for that?I really need a working command using that 'something'. Thanks.|||That 'something' is the value you want to change through sp_configure. In the case of enabling CLR, it is 'clr enabled'. The full code for this is:

sp_configure 'clr enabled', 1
go
reconfigure
go

The reconfigure is important, and it has to be done in a separate batch, therefore the go between the sp_configure and reconfigure.

Also, you can change this as well (together with other stuff) through the SQL Server Surface Area Consiguration tool (SAC). Go to Start | SQL Server | Configuration Tools and you'll find it. (The names are from memory and not correct - but you'll know what I mean).

Niels
|||

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

'something' is a placeholder, yes. To see all possible configurable options you might try

-- sp_configure test

sp_configure 'show advanced options', 1

go

RECONFIGURE

go

sp_configure

go

-- EO sp_configure test

This should list everything you can replace 'something' with.

I guess that most of what you are supposed to turn on or off is available in GUIs too. I usually guess wrong, though.

Hope this helps.

|||I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?|||

vanni wrote:

I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?

Hmm, are you saying that you can execute the SQLCLR proc from sqlcmd but not from somewhere else? If that's the case; where can you not execute the proc from, and what is the error. If the error is that the CLR is not enabled I'd say that you are not executing against the same server instance as you do when executing using sqlcmd.

Niels
|||I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.|||

Are you perhaps running a user instance as well? If so, you'll need to enable CLR integration in the user instance separately from the main instance.

|||

Hi Nicole! This is something new. How should I know that I'm using a User's intance? Btw, the db i'm using is local, found within the project's folder. Does this have any bearing on my problem?

|||If your connection string contains "User Instance = true", then you're using a user instance.|||

I'm indeed using user instance. I only enabled CLR integration on the main instance. My problem is how to enable it in the user instance. Is there a way to do so on the fly in my C# app?

|||

Just issue the same series of T-SQL statements (already covered earlier in this thread) that you would use to enable CLR use in a standard instance. Using ADO.NET, this can be accomplished by setting the CommandText property of a SqlCommand instance to the desired T-SQL statement, then calling the ExecuteNonQuery method of the SqlCommand.

|||

This works very well!

But if I change the .Net application, adds new methods etc. how do I tell the MS SQL Server about the new features, without dropping and adding the assembly?

CLR Enabled

Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

I changed the 'clr enabled' property to 1 using sp_configure but I still get this error.

Thanks
Mike

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go|||Thank you again Gorm!|||

Gorm Braarvig wrote:

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

Hi!

I'm having the same problem. I'm just wondrin what that 'something' is. Is that a placeholder of some sort? What can be the values for that?I really need a working command using that 'something'. Thanks.|||That 'something' is the value you want to change through sp_configure. In the case of enabling CLR, it is 'clr enabled'. The full code for this is:

sp_configure 'clr enabled', 1
go
reconfigure
go

The reconfigure is important, and it has to be done in a separate batch, therefore the go between the sp_configure and reconfigure.

Also, you can change this as well (together with other stuff) through the SQL Server Surface Area Consiguration tool (SAC). Go to Start | SQL Server | Configuration Tools and you'll find it. (The names are from memory and not correct - but you'll know what I mean).

Niels|||

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

'something' is a placeholder, yes. To see all possible configurable options you might try

-- sp_configure test

sp_configure 'show advanced options', 1

go

RECONFIGURE

go

sp_configure

go

-- EO sp_configure test

This should list everything you can replace 'something' with.

I guess that most of what you are supposed to turn on or off is available in GUIs too. I usually guess wrong, though.

Hope this helps.

|||I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?|||

vanni wrote:

I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?

Hmm, are you saying that you can execute the SQLCLR proc from sqlcmd but not from somewhere else? If that's the case; where can you not execute the proc from, and what is the error. If the error is that the CLR is not enabled I'd say that you are not executing against the same server instance as you do when executing using sqlcmd.

Niels|||I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.|||

Are you perhaps running a user instance as well? If so, you'll need to enable CLR integration in the user instance separately from the main instance.

|||

Hi Nicole! This is something new. How should I know that I'm using a User's intance? Btw, the db i'm using is local, found within the project's folder. Does this have any bearing on my problem?

|||If your connection string contains "User Instance = true", then you're using a user instance.|||

I'm indeed using user instance. I only enabled CLR integration on the main instance. My problem is how to enable it in the user instance. Is there a way to do so on the fly in my C# app?

|||

Just issue the same series of T-SQL statements (already covered earlier in this thread) that you would use to enable CLR use in a standard instance. Using ADO.NET, this can be accomplished by setting the CommandText property of a SqlCommand instance to the desired T-SQL statement, then calling the ExecuteNonQuery method of the SqlCommand.

|||

This works very well!

But if I change the .Net application, adds new methods etc. how do I tell the MS SQL Server about the new features, without dropping and adding the assembly?

CLR Enabled

Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

I changed the 'clr enabled' property to 1 using sp_configure but I still get this error.

Thanks
Mike

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go|||Thank you again Gorm!|||

Gorm Braarvig wrote:

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

Hi!

I'm having the same problem. I'm just wondrin what that 'something' is. Is that a placeholder of some sort? What can be the values for that?I really need a working command using that 'something'. Thanks.|||That 'something' is the value you want to change through sp_configure. In the case of enabling CLR, it is 'clr enabled'. The full code for this is:

sp_configure 'clr enabled', 1
go
reconfigure
go

The reconfigure is important, and it has to be done in a separate batch, therefore the go between the sp_configure and reconfigure.

Also, you can change this as well (together with other stuff) through the SQL Server Surface Area Consiguration tool (SAC). Go to Start | SQL Server | Configuration Tools and you'll find it. (The names are from memory and not correct - but you'll know what I mean).

Niels
|||

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

'something' is a placeholder, yes. To see all possible configurable options you might try

-- sp_configure test

sp_configure 'show advanced options', 1

go

RECONFIGURE

go

sp_configure

go

-- EO sp_configure test

This should list everything you can replace 'something' with.

I guess that most of what you are supposed to turn on or off is available in GUIs too. I usually guess wrong, though.

Hope this helps.

|||I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?|||

vanni wrote:

I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?

Hmm, are you saying that you can execute the SQLCLR proc from sqlcmd but not from somewhere else? If that's the case; where can you not execute the proc from, and what is the error. If the error is that the CLR is not enabled I'd say that you are not executing against the same server instance as you do when executing using sqlcmd.

Niels
|||I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.|||

Are you perhaps running a user instance as well? If so, you'll need to enable CLR integration in the user instance separately from the main instance.

|||

Hi Nicole! This is something new. How should I know that I'm using a User's intance? Btw, the db i'm using is local, found within the project's folder. Does this have any bearing on my problem?

|||If your connection string contains "User Instance = true", then you're using a user instance.|||

I'm indeed using user instance. I only enabled CLR integration on the main instance. My problem is how to enable it in the user instance. Is there a way to do so on the fly in my C# app?

|||

Just issue the same series of T-SQL statements (already covered earlier in this thread) that you would use to enable CLR use in a standard instance. Using ADO.NET, this can be accomplished by setting the CommandText property of a SqlCommand instance to the desired T-SQL statement, then calling the ExecuteNonQuery method of the SqlCommand.

|||

This works very well!

But if I change the .Net application, adds new methods etc. how do I tell the MS SQL Server about the new features, without dropping and adding the assembly?

CLR Enabled

Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

I changed the 'clr enabled' property to 1 using sp_configure but I still get this error.

Thanks
Mike

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go|||Thank you again Gorm!|||

Gorm Braarvig wrote:

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

Hi!

I'm having the same problem. I'm just wondrin what that 'something' is. Is that a placeholder of some sort? What can be the values for that?I really need a working command using that 'something'. Thanks.|||That 'something' is the value you want to change through sp_configure. In the case of enabling CLR, it is 'clr enabled'. The full code for this is:

sp_configure 'clr enabled', 1
go
reconfigure
go

The reconfigure is important, and it has to be done in a separate batch, therefore the go between the sp_configure and reconfigure.

Also, you can change this as well (together with other stuff) through the SQL Server Surface Area Consiguration tool (SAC). Go to Start | SQL Server | Configuration Tools and you'll find it. (The names are from memory and not correct - but you'll know what I mean).

Niels
|||

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

'something' is a placeholder, yes. To see all possible configurable options you might try

-- sp_configure test

sp_configure 'show advanced options', 1

go

RECONFIGURE

go

sp_configure

go

-- EO sp_configure test

This should list everything you can replace 'something' with.

I guess that most of what you are supposed to turn on or off is available in GUIs too. I usually guess wrong, though.

Hope this helps.

|||I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?|||

vanni wrote:

I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?

Hmm, are you saying that you can execute the SQLCLR proc from sqlcmd but not from somewhere else? If that's the case; where can you not execute the proc from, and what is the error. If the error is that the CLR is not enabled I'd say that you are not executing against the same server instance as you do when executing using sqlcmd.

Niels
|||I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.|||

Are you perhaps running a user instance as well? If so, you'll need to enable CLR integration in the user instance separately from the main instance.

|||

Hi Nicole! This is something new. How should I know that I'm using a User's intance? Btw, the db i'm using is local, found within the project's folder. Does this have any bearing on my problem?

|||If your connection string contains "User Instance = true", then you're using a user instance.|||

I'm indeed using user instance. I only enabled CLR integration on the main instance. My problem is how to enable it in the user instance. Is there a way to do so on the fly in my C# app?

|||

Just issue the same series of T-SQL statements (already covered earlier in this thread) that you would use to enable CLR use in a standard instance. Using ADO.NET, this can be accomplished by setting the CommandText property of a SqlCommand instance to the desired T-SQL statement, then calling the ExecuteNonQuery method of the SqlCommand.

|||

This works very well!

But if I change the .Net application, adds new methods etc. how do I tell the MS SQL Server about the new features, without dropping and adding the assembly?

CLR Enabled

Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

I changed the 'clr enabled' property to 1 using sp_configure but I still get this error.

Thanks
Mike

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go|||Thank you again Gorm!|||

Gorm Braarvig wrote:

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

Hi!

I'm having the same problem. I'm just wondrin what that 'something' is. Is that a placeholder of some sort? What can be the values for that?I really need a working command using that 'something'. Thanks.|||That 'something' is the value you want to change through sp_configure. In the case of enabling CLR, it is 'clr enabled'. The full code for this is:

sp_configure 'clr enabled', 1
go
reconfigure
go

The reconfigure is important, and it has to be done in a separate batch, therefore the go between the sp_configure and reconfigure.

Also, you can change this as well (together with other stuff) through the SQL Server Surface Area Consiguration tool (SAC). Go to Start | SQL Server | Configuration Tools and you'll find it. (The names are from memory and not correct - but you'll know what I mean).

Niels|||

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go

'something' is a placeholder, yes. To see all possible configurable options you might try

-- sp_configure test

sp_configure 'show advanced options', 1

go

RECONFIGURE

go

sp_configure

go

-- EO sp_configure test

This should list everything you can replace 'something' with.

I guess that most of what you are supposed to turn on or off is available in GUIs too. I usually guess wrong, though.

Hope this helps.

|||I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?|||

vanni wrote:

I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

The sproc works using sqlcmd. Is this a normal behavior?

Hmm, are you saying that you can execute the SQLCLR proc from sqlcmd but not from somewhere else? If that's the case; where can you not execute the proc from, and what is the error. If the error is that the CLR is not enabled I'd say that you are not executing against the same server instance as you do when executing using sqlcmd.

Niels|||I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.|||

Are you perhaps running a user instance as well? If so, you'll need to enable CLR integration in the user instance separately from the main instance.

|||

Hi Nicole! This is something new. How should I know that I'm using a User's intance? Btw, the db i'm using is local, found within the project's folder. Does this have any bearing on my problem?

|||If your connection string contains "User Instance = true", then you're using a user instance.|||

I'm indeed using user instance. I only enabled CLR integration on the main instance. My problem is how to enable it in the user instance. Is there a way to do so on the fly in my C# app?

|||

Just issue the same series of T-SQL statements (already covered earlier in this thread) that you would use to enable CLR use in a standard instance. Using ADO.NET, this can be accomplished by setting the CommandText property of a SqlCommand instance to the desired T-SQL statement, then calling the ExecuteNonQuery method of the SqlCommand.

|||

This works very well!

But if I change the .Net application, adds new methods etc. how do I tell the MS SQL Server about the new features, without dropping and adding the assembly?

Click to Sort Column Headings

Is there a table property that allows for this? or Do I have to build that
into my query and requery everytime I want to sort? Can someone provide an
example?Rich,
Thanks for the response. I don't see the attached project. Can you post
the link to where you found the project or provide a detailed description on
how to do it (please be as descript as possible...I am noob)?
Thanks!
"Rich Millman" wrote:
> No, there is no table property for this.
> It is not difficult.
> First thing to understand is that you don't sort in your query, you let RS
> do the sorting.
> Then in your column headings you provide actions to open your report,
> passing in parameters that control the sorting.
> Attached is a project that you can use as a sample. I didn't write it, but
> it helped me.
>
> "Neo" <Neo@.discussions.microsoft.com> wrote in message
> news:3FC1A47E-4D46-4604-981D-01DE508D134B@.microsoft.com...
> > Is there a table property that allows for this? or Do I have to build
> > that
> > into my query and requery everytime I want to sort? Can someone provide
> > an
> > example?
>
>|||If you are using Outlook express a paper clip shows the attachment. The
posting did have an attachment with it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Neo" <Neo@.discussions.microsoft.com> wrote in message
news:B964369A-A97D-4C66-AE47-F6F670051B74@.microsoft.com...
> Rich,
> Thanks for the response. I don't see the attached project. Can you post
> the link to where you found the project or provide a detailed description
on
> how to do it (please be as descript as possible...I am noob)?
> Thanks!
> "Rich Millman" wrote:
> > No, there is no table property for this.
> > It is not difficult.
> > First thing to understand is that you don't sort in your query, you let
RS
> > do the sorting.
> > Then in your column headings you provide actions to open your report,
> > passing in parameters that control the sorting.
> >
> > Attached is a project that you can use as a sample. I didn't write it,
but
> > it helped me.
> >
> >
> > "Neo" <Neo@.discussions.microsoft.com> wrote in message
> > news:3FC1A47E-4D46-4604-981D-01DE508D134B@.microsoft.com...
> > > Is there a table property that allows for this? or Do I have to build
> > > that
> > > into my query and requery everytime I want to sort? Can someone
provide
> > > an
> > > example?
> >
> >
> >|||Sorry, I was using the Webbased MS Technet. I opened up the newsgroup with
newsgroup viewer and now have the file.
Thanks!
"Bruce L-C [MVP]" wrote:
> If you are using Outlook express a paper clip shows the attachment. The
> posting did have an attachment with it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Neo" <Neo@.discussions.microsoft.com> wrote in message
> news:B964369A-A97D-4C66-AE47-F6F670051B74@.microsoft.com...
> > Rich,
> >
> > Thanks for the response. I don't see the attached project. Can you post
> > the link to where you found the project or provide a detailed description
> on
> > how to do it (please be as descript as possible...I am noob)?
> >
> > Thanks!
> >
> > "Rich Millman" wrote:
> >
> > > No, there is no table property for this.
> > > It is not difficult.
> > > First thing to understand is that you don't sort in your query, you let
> RS
> > > do the sorting.
> > > Then in your column headings you provide actions to open your report,
> > > passing in parameters that control the sorting.
> > >
> > > Attached is a project that you can use as a sample. I didn't write it,
> but
> > > it helped me.
> > >
> > >
> > > "Neo" <Neo@.discussions.microsoft.com> wrote in message
> > > news:3FC1A47E-4D46-4604-981D-01DE508D134B@.microsoft.com...
> > > > Is there a table property that allows for this? or Do I have to build
> > > > that
> > > > into my query and requery everytime I want to sort? Can someone
> provide
> > > > an
> > > > example?
> > >
> > >
> > >
>
>