Configuration in SQLCLR Assembly

Hi,

I have an SQLCLR assembly which is required to connect to a remote WCF service. In order to do this in a host such as IIS you would need to store all your WCF configuration data (endpoints, types etc) in the Web.config or App.config (in a windows forms app). This begs the question: Where do you store configuration data for SQLCLR assemblies?

The System.Configuration assembly is available in the SQLCLR, but this assumes a Web or App config files exists? Does SQL have its own config file that you can keep your settings in which is called when the assembly is running from within the SQL process?

If this is not possible, should I rather be storing configuration data in the database itself?

This particular example relates to WCF configuration but is relevant for assemblies using Enterprise Library which is also config driven.

Any help would be appreciated.

Chris

You can use a config file with SQLCLR. Create a config file sqlservr.exe.config and place it in the same directory as the SQL Server executable (sqlservr.exe). Whatever is in that file will be parsed in when an appdomain is loaded.

However, config files are not supported as such, and there is no guarantee it will work in the future. In addition, there is no way to differentiate between config ettings for different databases.

So, use the best storage facility there is - the database.

Niels
|||

Hi Niels,

Thank you for your response. I will test out the sqlservr.exe.config today. I agree that as the application will be running in SQL that the best storage spce is the DB, this however does not solve the problem where you are using tools like EntLib or Technologies like WCF where they rely specifically on configuration files. I am not aware of anyway to make WCF or EntLib collect configuration data from a sql database rather than the Application configuration file.

Chris

Configuration Error...name wanted

Hi, does somebody recognize the problem with my code? -Thanks!

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:This is an unexpected token. The expected token is 'NAME'. Line 58, position 52.

Source Error:

<

sessionStatemode="SQLServer"stateConnectionString="Jensen"sqlConnectionString=

"datasource=Database;userid=username;password=pass"cookieless="false"timeout="60"/>

problem solved

<

sessionStatemode="SQLServer"stateConnectionString="tcpip=BRAHMA"sqlConnectionString="data source=epscor;user id=EpscorUser;password=epsc0r"cookieless="false"timeout="60"/>

Conditionally required field

How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Conditional Report Parameters

I have a report with required parameters of company and project. I then have 3 parameters that I want to be conditional - start date, end date and cycle.

By conditional I mean if cycle is chosen then start & end date are not required. If start and end date are chosen then cycle is not required.

I have made all 3 parameters 'Allow Null Values" however cycle's available values are set from a query once a project is chosen. When the field refreshes I lose my null checkbox for cycle, so it ends up always being required.

Am I going about this the wrong way? I'm using 2005.

I'm not sure that conditional report parameters exist, but could you accomplish this using optional parameters?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=621340&SiteID=1

|||

Hmm, not really. I don't want all 3 to be optional, either cycle or start/end date need to be required.

I'll keep banging my head against the wall... Thanks though.