ClubSite template + sql server 2005 not cooperating...

I've managed to get the clubsite template from asp.net up and running, sorda (http://www.flysniper.com), I get a system error which I believe is related to the fact that my DB isn't working correctly.

I'm trying to configure it to work with ms sql server 2005 which is running on the same machine as the website.

I've followed the instructions of Scott Gu (http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx)

while trying to run aspnet_regsql.exe I get the following error

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -S XXXXXXXXXX
-U YYYYYYYY -P ZZZZZZZZZ -A all -d flysniper

Start adding the following features:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider

.................
An error has occurred. Details of the exception:
An error has occurred while establishing a connection to the server. When conne
cting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Unable to connect to SQL Server database.

I have tried several different things (shooting in the dark) to resolve this issue, including:

verifing that port 445 is avaliable on the firewall per these directions:http://support.microsoft.com/default.aspx?scid=kb;en-us;839269

configured SQL server 2005 to allow remote connections following these directions:http://support.microsoft.com/kb/914277

with no change in results.

Anything else I should look into?

Thanks in advance,

Mike

I get that message when I forget to start sql server before trying to connect to the website.

|||

LockH - thanks for your response, I have confirmed that the SQL Service is up and running.

UPDATE: I have managed to get the aspnet_regsql.exe to run and the tables are created.

I have tried to set the connection string up correctly: <addname="ClubSiteDB"connectionString="Data Source=StormTrooper\FlySniper;Initial Catalog=FlySniperDev;Persist Security Info=True;User ID=XXXXXXXXXXXXX;Password=YYYYYYYYYYYY;"providerName="System.Data.SqlClient"/>

I think its just a switch somewhere that I'm missing.

I have set up a developer website for testing purposes, please usehttp://dev.flysniper.com/flysniperClubSite to see the actual error.

TIA,

Mike

|||

Did you create the tables in the database on the server, or did you copy the database files from your dev machine?

If you copied the database files onto that machine, did you go into database admin and attach the database files to that server?

|||

The tables were created directly on the server. It's my personal machine so I have direct access to it.

Thanks for you time,

Mike

CLR Stored Procedures & VS2005 ASP.NET designer support?

I have successfully created CLR stored procures using c# and SQL Server 2005. Now I want to use the CLR stored procedures with some ASP.NET controls. I can add the CLR stored procedure to the dataset designer and have it add a new table adapter but it does not list the columns in the designer view. This forces me to code the column references manually into the controls (tried: DropDownList, GridView).

I can deal with adding the columns manually for controls using a CLR stored procedure, but I want to make sure I was not missing something, am I?

BTW: TSQL stored procedures work as expected (show columns in the dataset designer)

thanks!

-pieter

Hi,

as metadata can be defined on the fly in a stored procedure by building your rowset one by one and then sending it through the pipe, I don′t really know if there is a way to give the designer a hint via some properties or anything else to sniff into the CLR resultset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks. Your reply does help.

I just wanted to be sure it was not something I was doing incorrectly. The CLR stored procedures do indeed have a great deal of power available to them.

Let us hope the VS team can find some way of bringing the two closer together in the future.

-pieter

CLR Question (BASIC)

I have Visual Studio .Net 2003...I am assuming that since it can't find
System.Data.SQLServer that I need Visual Studio 2005 to develop CLR apps for
SQL?Hello RSH,

> I have Visual Studio .Net 2003...I am assuming that since it can't
> find System.Data.SQLServer that I need Visual Studio 2005 to develop
> CLR apps for SQL?
Correct
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||There's no System.Data.SQLServer anymore. You use the regular connection obj
ects, reader etc. For
SQLCLR specific things (like context and pipe), you use the Microsoft.SqlSer
ver.Server namespace.
You do need Framework 2.0 (as the things you need are in the System.Data.dll
file that comes with
Framework 2.0). You can download Framework 2.0 and write your CLR objects us
ing Notepad and
command-line compile them. If you want to use VS to write SQLCLR objects you
need VS 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RSH" <way_beyond_oops@.yahoo.com> wrote in message news:ejDCfJ29FHA.160@.TK2MSFTNGP12.phx.gb
l...
>I have Visual Studio .Net 2003...I am assuming that since it can't find Sys
tem.Data.SQLServer that
>I need Visual Studio 2005 to develop CLR apps for SQL?
>|||You don't really need Visual Studio at all. You can download the .NET 2.0
SDK and use the command-line tools. But if you want the IDE experience,
yes, VS2005 is necessary.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:ejDCfJ29FHA.160@.TK2MSFTNGP12.phx.gbl...
>I have Visual Studio .Net 2003...I am assuming that since it can't find
>System.Data.SQLServer that I need Visual Studio 2005 to develop CLR apps
>for SQL?
>

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR Integration in SQL Server (Problem in "sp_configure ''clr enabled'', 1")

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

OR

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

|||

Hiiiii

I have also tried the enabling advance option using this script

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

but this script also gives me error like this

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and i dont know much about surface area config mgr but i think it is for remote database or wht

Plz help me i need this urgently and

thnxxxxx again.......

|||

Surface Area Config Mgr. is the easiest way to do it.

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

Hopefully this should set you up.

|||

Hiiiiiiiii

I have set up this sucessfully

Really this is easyest way to do this

thnxxxxxxx a lot

and how do i check tht my clr confitration is enabled or not

because when i write query like this

exec sp_configure 'show advance options'

then this again executes with the previous err....

thnx a lot

|||

select [value_in_use] from sys.configurations where name = 'clr enabled'

|||

Hi,

I don't see CLR Integration and all that you have listed above. I did an upgrade from SQL 2000 to 2005. Have I missed out something during the installation?

Thanks in advance,

APUK

|||

Hi,

I re-installed SQL 2005 with upgrade option and got those options.

Thanks,

APUK

CLR integration - Could not find Type '?' in assembly '?'.

Hi,

I'm new to Integration services and .Net programming but am trying to
create a dll that I can access from Sql server 2005.

The dll read's an xml file and carries out some processing. I've run
the code as an console app and it works fine.

I have created the assembly in sqlserver thus:

create assembly PinCodeLoader from
'C:\PinCodeLoader\PinCodeLoader\PinCodeLoader\bin\Debug\PinCodeLoader.dll'
with permission_set = external_access

But when I try to reference the assembly from a stored proc

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.PinCodeloader.Main

I get the following error:

Msg 6505, Level 16, State 1, Procedure interface_processPinCodefile,
Line 3
Could not find Type 'PinCodeloader' in assembly 'PinCodeLoader'.

I understand the context of the syntax should be
assembly_name.class_name.method_name. The first lines of the code in
the DLL are as follows

namespace PinCodeLoader
{
class PinCodeLoader
{
static void Main(string[] args)
{

Therefore assembly = PinCodeLoader, class_name = PinCodeLoader and
method_name = Main. Which should equal
EXTERNAL name PinCodeLoader.PinCodeloader.Main, I thought.

Has anybody come across this or can they offer any assistance?

Many thanks,

Paul

Hi, try to compile the .vb file with this command line:

csc.exe /t:library PinCodeLoader.bv (Put the correct name of the .vb file)

If this work look at the properties of the project and search for 'root namespace' (or something similar), and delete it, then try again compiling the entire solution from de IDE...

Good luck

Alejandro F.

|||

You need to include the namespace as well:

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.[PinCodeLoader.PinCodeloader].Main

However, you still won't be able to create your proc because SQL can't map nvarchar(1024) to the string array args.

|||

Thanks for your replies guys. I didn't actually try that method but found a way around it.

In my c# program I removed the reference to a namespace, as I was told it's not really necessary and changed the code as follows.

public class PinCodeLoad

{

public static void Main(string filename,string datasource,Int32 timeout, string companyname)

{

So instead of using the args parameter I'm implictly defining them. Plus I preceded my class and method with public

I kept the stored proc as before.

I hope that helps anybody else.

Paul

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.Krishna
Hi
"Krishna" wrote:

> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.

> how to redtify the error
> Regards
> T.A.Krishna
John
|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.KrishnaHi
"Krishna" wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.
> how to redtify the error
> Regards
> T.A.Krishna
John|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.KrishnaHi
"Krishna" wrote:

> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.

> how to redtify the error
> Regards
> T.A.Krishna
John|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR Assemblies

From SQL 2005 docs (type "Designing Assemblies" in the index lookup; bottom
of the page):
Supported .NET Framework Assemblies
Users can refer to the following .NET Framework assemblies in SQL Server
2005 without registering them by using CREATE ASSEMBLY:
custommarshallers.dll
Microsoft.visualbasic.dll
Microsoft.visualc.dll
mscorlib.dll
system.data.dll
<snip> etc ...
Anyone know how? All I can think of is, for example:
CREATE TYPE dbo.Int32
EXTERNAL NAME mscorlib.[System.Int32]
But that errors with:
Msg 6267, Level 16, State 1, Line 1
Assembly "mscorlib" does not exist, or the user does not have permission to
reference it.
I may be reading that sentence wrong, but it sounds like you should be able
to register System.Int32 as a type in SQL 2005 (assuming it conforms to the
requirements for CLR UDTs), but how to refernece the assembly when it hasn't
been registered with CREATE ASSEMBLY? I don't think I'd actually want to use
core CLR types, but ran across it in the docs so now I'm curious.
Thanks - KHAs far as I know, there are a list of assemblies that are loaded from the
GAC by SQL Server and make available for use (since there is no need to
register them into each database). The rest of all asemblies (like user
assemblies) can only be loaded through CREATE ASSEMBLY.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"KH" <KH@.discussions.microsoft.com> wrote in message
news:2E6A8137-DA4B-4367-A688-AFCDF06FB913@.microsoft.com...
> From SQL 2005 docs (type "Designing Assemblies" in the index lookup;
> bottom
> of the page):
> Supported .NET Framework Assemblies
> Users can refer to the following .NET Framework assemblies in SQL Server
> 2005 without registering them by using CREATE ASSEMBLY:
> custommarshallers.dll
> Microsoft.visualbasic.dll
> Microsoft.visualc.dll
> mscorlib.dll
> system.data.dll
> <snip> etc ...
> Anyone know how? All I can think of is, for example:
> CREATE TYPE dbo.Int32
> EXTERNAL NAME mscorlib.[System.Int32]
> But that errors with:
> Msg 6267, Level 16, State 1, Line 1
> Assembly "mscorlib" does not exist, or the user does not have permission
> to
> reference it.
> I may be reading that sentence wrong, but it sounds like you should be
> able
> to register System.Int32 as a type in SQL 2005 (assuming it conforms to
> the
> requirements for CLR UDTs), but how to refernece the assembly when it
> hasn't
> been registered with CREATE ASSEMBLY? I don't think I'd actually want to
> use
> core CLR types, but ran across it in the docs so now I'm curious.
> Thanks - KH
>|||"examnotes" <KH@.discussions.microsoft.com> wrote in
news:2E6A8137-DA4B-4367-A688-AFCDF06FB913@.microsoft.com:

> Users can refer to the following .NET Framework assemblies in SQL
> Server 2005 without registering them by using CREATE ASSEMBLY:
>
"Refer to" above, means that you can have a dependency on the assembly
without having to catalogue the assembly directly through CREATE
ASSEMBLY (as Sri says in his post). In other words the assemblies
mentioned are the only ones that are allowed to be loaded from the GAC.
All other assebmlies have to be created in the database in question.
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 and SQL 2005 Compact Edition

I believe that there is a also a new SQL database for the PDA. Am I right? Is it also possible to use (CF) .Net code inside this edition?

Yes. It's called SQL Server 2005 Mobile Edition. This works on Windows Mobile devices (PocketPC and SmartPhone). You need to install .NETCF 2.0 (compact framework) on the device for using SQL Mobile.

You can develop mobile applications using Visual Studio for Devices.

SQL Mobile is part of SQL Server 2005 or Visual Studio 2005.

You can get more information here:

The SQL Mobile page on MSDN : http://msdn.microsoft.com/mobility/sqlmobile/default.aspx

SQL Mobile page from SQL Server home: http://www.microsoft.com/sql/editions/sqlmobile/default.mspx

News groups: microsoft.public.sqlserver.ce. and

Msdn forums: You will find SQL Mobile under SQL Server: http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1

|||But can you also create UDF en UDT with .Net like the desktop editions?|||SQL Mobile does not support UDT or UDF. Also the CLR is not inside SQL Mobile database. The programming model is different.

Simply stated:
SQL Mobile components is a bunch of dlls that are linked in your device application project. So the database routines are embedded in your application code.

You declare some variables, structures
Application logic - pre data processing
query, modify, delete data from SQL Mobile
Application logic - post data processing

CLR .Net Framework 3.0

Hi

Is there any way to get the Sql 2005 to load the .Net 3.0 Framework instead of 2.0, when running a Stored Proc?

Thanks

You wouldn′t ask if you used SQLServer 2005 since beta :-) No, you can′t.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Actually the portion of the .NET Framework used by sqlclr is essentially exactly the same in .NET Framework 3.0 as .NET Framework 2.0. The BCL and CLR layers of the .NET Framework were (except for servicing fixes) unchanged and still return the same version in .NET Framework 3.0 as they did before.

See the MSDN documentation for more information on what exactly .NET Framework 3.0 entails: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnlong/html/netfx30.asp

So except for bug fixes, it doesn't matter to sqlclr whether your machine has .NET Framework 3.0 or 2.0 installed.

Steven

CLR .Net Framework 3.0

Hi

Is there any way to get the Sql 2005 to load the .Net 3.0 Framework instead of 2.0, when running a Stored Proc?

Thanks

You wouldn′t ask if you used SQLServer 2005 since beta :-) No, you can′t.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Actually the portion of the .NET Framework used by sqlclr is essentially exactly the same in .NET Framework 3.0 as .NET Framework 2.0. The BCL and CLR layers of the .NET Framework were (except for servicing fixes) unchanged and still return the same version in .NET Framework 3.0 as they did before.

See the MSDN documentation for more information on what exactly .NET Framework 3.0 entails: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnlong/html/netfx30.asp

So except for bug fixes, it doesn't matter to sqlclr whether your machine has .NET Framework 3.0 or 2.0 installed.

Steven

closing the database connection

Hi all,
sorry if this is in another post - I did a few searches and found nothing.
We have a custom asp .net ui and are rendering our reports directly to pdf.
We are running sql server stored procedures to populate our reports. What I
have noticed, however, during the course of testing the reports, is that if
you look in SQL Server EM the connections (listed as .Net SQL Client
Provider) seem to stay open for quite some time. This is a concern, and I am
trying to figure out if there is a way that we can close the connection(s) as
soon as the data has been retrieved and the report populated. I believe this
must be the connection from RS directly calling the SP to populate - has
anyone else seen this?
Thanks,Perhaps you can set some timers in the rsconfig.
I've noticed that my oracle connenctions stay open relatively long to;
although it has something to do with congestion on either de DB-server or the
RS-server. I notice that when I automticly rennder a number of reports (to
make chached-reports for all parameter values) the RS-jobs sometimes heap up
and the open connections do also (although there are never more than tree
reports rendered at the same time). The problem solves itself automaticly
thoug, everything slows down and apperantly RS gets time to clean it up.
"Myles" wrote:
> Hi all,
> sorry if this is in another post - I did a few searches and found nothing.
> We have a custom asp .net ui and are rendering our reports directly to pdf.
> We are running sql server stored procedures to populate our reports. What I
> have noticed, however, during the course of testing the reports, is that if
> you look in SQL Server EM the connections (listed as .Net SQL Client
> Provider) seem to stay open for quite some time. This is a concern, and I am
> trying to figure out if there is a way that we can close the connection(s) as
> soon as the data has been retrieved and the report populated. I believe this
> must be the connection from RS directly calling the SP to populate - has
> anyone else seen this?
> Thanks,