Accessing web.config from stored procedure

I want to access a key from appSettings section of web.config.

I have the number of days allowed for a user to activate his/her account as a key in appSettings.

I have a maintenance procedure to delete all accounts that are not activated before that many days.

In this context, i have to access web.config from stored procedure. The procedure will be scheduled as a JOB in sql server.

Thanks.

Hi,

Are you having problem to access the appSettings secion in the web.config file? If so, you can use ConfigurationManager.AppSettings property to achieve that.

Here is a link for your reference.

http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.appsettings.aspx

If your stored procedure, that is running as a job, is trying to access the value, you might need to save the value to a database table or somewhere. Then your stored procedure can get that. It cannot get the web.config value directly.

|||

Thanks Kevin.

My requirement is to access appSettings from a stored procedure of SQL Server. So that whenever I change web.config, the procedure must

automatically access the new value.

Is there any way to access any XML file from a stored procedure. May be that could solve my problem. I heard about XML support in SQL Server. What does that do?

Thanks again.

Srikanth.

|||

Hi Srikanth,

Teh SQL Server 2005 support for Xml is for Xml column type and Xml manipulation. It stays in the database level, but not for reading an external file.

A traditional stored procedure does not read from a file. In this case, I think you have 2 options.

1. Make your app write that appSetting to a certain place in the database timely. The stored procedure can get that as a parameter.

2. Write a CLR stored procedure. Since SQL Server 2005 supports running .NET code, you can write a method and put it in assembly. Each time, you can have the assembly read from certain file, parse the xml and get the setting value.

There are many articles talking about how to create a CLR stored procedure. Here are some of them.

http://msdn2.microsoft.com/en-us/library/ms131094.aspx
http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx