ActiveX and SQL Server question?

Apologies for posting this in the wrong section, now pointed to here.

I have the following

AciveX script set up as a DTS package which I would like to alter so

that the output is sent to another server. :-

Function Main()
dim oCmd, sSql, oDom
set oDom = CreateObject("Msxml2.DOMDocument")
set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=MyServer; Initial Catalog=MyDB; Integrated Security=sspi"

sSql = "<ROOT

xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT

* FROM VW_Projs1 FOR XML AUTO</sql:query></ROOT>"
oCmd.CommandText = sSql
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute, , 1024
oDom.Save "C:\Inetpub\wwwroot\test.xml"
Main = DTSTaskExecResult_Success
End Function

I want to schedule the package so it runs overnight, so
1.

Do I have to change the 'Integrated Security=sspi' part of the script

to reflect a user who has suitable rights to create/write a file on the

other server? If so How?
2. Would I redirect to the another

server/directory just by changing the path in oDom.Save to

"ServerName\DirectoryName".? or would there be a different format?

Thanks

I don't have much exp. in ActiveScript but I can say that if you are creating a flat file on a different server apart from the local server then you need to define the path correctly \\servername\path in order to create the file. Also ensure to check the SQLAgent permissions on the specified path as you're going to schedule this as a job.

Also you might try creating the file locally on the server and then copy the same using copy command in SQL scheduled job.

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