Getting a 404/401 on a long running report

Greetings.
I have a report that I am running through ASP.Net with the rs.render method.
When I have a longer date range, the execution time of the query gets longer
and I start to get funky errors. Namely, at around 3 minutes, I get
prompted for network credentials by the standard IE prompt. It doesn't
accept what they are, and after a few times, it takes me to a 404 saying that
the page can't be found. If I try to cancel the prompt, I get a 401 that I
don't have access. I'm not sure what else to look at here. This report has
worked fine in the past, and there are no problems if I just scale back the
date range.
I have the render make a pdf file in a virtual directory on my web server
and then my postback page has a client script inserted to open a window
showing that pdf file.
Anyone have any ideas?
Dale.I fixed it.
It turns out that the HTTP connection timeout settings needed to be
increased a little bit to allow the report to complete. The default of 120
seconds had worked fine in IIS 5 on Windows 2000 Server, but the same value
in IIS 6 on Windows 2003 Server didn't fare too well.
Also, I wound up re-writing the stored proc for the report. The original
author of it had cursors and correlated subqueries doing things they were
never intended to do. Execution time is now 33s instead of 10m15s.
:-)
"Dale M." wrote:
> Greetings.
> I have a report that I am running through ASP.Net with the rs.render method.
> When I have a longer date range, the execution time of the query gets longer
> and I start to get funky errors. Namely, at around 3 minutes, I get
> prompted for network credentials by the standard IE prompt. It doesn't
> accept what they are, and after a few times, it takes me to a 404 saying that
> the page can't be found. If I try to cancel the prompt, I get a 401 that I
> don't have access. I'm not sure what else to look at here. This report has
> worked fine in the past, and there are no problems if I just scale back the
> date range.
> I have the render make a pdf file in a virtual directory on my web server
> and then my postback page has a client script inserted to open a window
> showing that pdf file.
> Anyone have any ideas?
> Dale.

GetSchema

Should the compact edition connection support the GetSchema method? When I call it gives me a not supported exception. I am after the table names in my database. I could do a query on Information_schema.tables, but I thought using GetSchema("Tables") would be a more generic way to get the information.

Thanks,

Juan Foegen

It is a bug, see http://support.microsoft.com/kb/912435. Use INFORMATION_SCHEMA.TABLES instead (it is a standard way of getting schema information)

GetReportParameters method not updating ReportParameters array.

I have developed a report that uses two 'datatime' parameters ('start date'
and 'end date'). I then introduced a third parameter ('Range') which
provides a difinitive list of integer values to select from.
On selecting one of the integer values from the 'Range' parameter, the
'start date' and 'end date' are updated accordingly (by applying
"=Code.GetStartDate(Parameters!Range.Value)" as the non-queried Default
Values of the 'start date' parameter properties, and similar for the 'end
date'). This works perfectly when run from the Report Manager (i.e. If I
select the third option (last month) from the 'Range' parameter, the code
will update the 'start date' value to the beginning of last month and the
'end date' value to the last day of last month).
I have also developed a C# user control that acts as a client side utility
for viewing reports on a specific server (using the Reporting Services
WebService provided). This applicatiuon works perfectly for all reports
with the exception of that described above. The problem lies in obtaining
the updated dates when selecting a 'Range' using the 'GetReportParameters'
method. I have no problem returning updated parameter values from Query
Dependant parameters, which causes me to believe that I am calling the
Method correctly, however the 'start date' and 'end date' default values do
not seem to be updated by the webservice despite passing the updated value
of the 'Range' parameter to the method.
Any information as to why the parameter values are not being updated
correctly would be helpful.
Below is a code snippet:
Thanks in advance.
----
ReportServer.ReportParameter[] parameters;
try
{
parameters = rs.GetReportParameters("/" +
reportPath,null,true,currentParamValues,null);
}
catch(System.Web.Services.Protocols.SoapException sex)
{
SoapException(sex);
return null;
}
----I am seeing something similar, only with a cascading parameter. I've
stripped it down to the simplest example I can think of... the report has 2
parameters, A and B. B has a valid values list based on a dataset that
requires A.
I call GetReportParameters( ReportName, false, null, null, null ) and both
parameters come back with parameter 2 dependent upon parameter 1. I then
provide a ParameterValue array with 1 entry for parameter A. I call
GetReportParameters( ReportName, true, null, parameterValues, null ) and I
get both parameters back but B has no valid values and the parameter's state
is HasOutstandingDependencies.
Have you determined what your parameter's state is after the
GetReportParameters call?
"Paul B" wrote:
> I have developed a report that uses two 'datatime' parameters ('start date'
> and 'end date'). I then introduced a third parameter ('Range') which
> provides a difinitive list of integer values to select from.
> On selecting one of the integer values from the 'Range' parameter, the
> 'start date' and 'end date' are updated accordingly (by applying
> "=Code.GetStartDate(Parameters!Range.Value)" as the non-queried Default
> Values of the 'start date' parameter properties, and similar for the 'end
> date'). This works perfectly when run from the Report Manager (i.e. If I
> select the third option (last month) from the 'Range' parameter, the code
> will update the 'start date' value to the beginning of last month and the
> 'end date' value to the last day of last month).
> I have also developed a C# user control that acts as a client side utility
> for viewing reports on a specific server (using the Reporting Services
> WebService provided). This applicatiuon works perfectly for all reports
> with the exception of that described above. The problem lies in obtaining
> the updated dates when selecting a 'Range' using the 'GetReportParameters'
> method. I have no problem returning updated parameter values from Query
> Dependant parameters, which causes me to believe that I am calling the
> Method correctly, however the 'start date' and 'end date' default values do
> not seem to be updated by the webservice despite passing the updated value
> of the 'Range' parameter to the method.
> Any information as to why the parameter values are not being updated
> correctly would be helpful.
> Below is a code snippet:
> Thanks in advance.
> ----
> ReportServer.ReportParameter[] parameters;
> try
> {
> parameters = rs.GetReportParameters("/" +
> reportPath,null,true,currentParamValues,null);
> }
> catch(System.Web.Services.Protocols.SoapException sex)
> {
> SoapException(sex);
> return null;
> }
> ----
>
>sql

GetReportParameters - How can I get the default value of the parameter?

I'm using the GetReportParameters method to get the parameters of my report.
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>

GetReportParameters

I am trying to use GetReportParameters method of the reportserver webservice. Here is the code.
ReportingService.ReportingService rService = new ReportingService.ReportingService();
rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
string historyID = null;
bool forRendering = true;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters;
parameters = rService.GetReportParameters
(
"/Demo/TransactionReport",
historyID,
forRendering,
values,
credentials
);
foreach(ValidValue v in parameters[0].ValidValues)
{
Response.Write(v.Label + ": " + v.Value);
Response.Write("<br>");
}
But when i executed the above code, i am getting the following error..
System.Web.Services.Protocols.SoapException: The permissions granted to user 'DPA32W126\ASPNET' are insufficient for performing this operation. -->
I understand there is no problem with the code, but security settings needs to be changed for it to work.
Can anyone guide me on how to make the above code working.
Thankyou,
Regards,
Rajesh Jagadeesan.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If your app is an ASP.NET app, you need to add identity impersonate=true in
our web.config. Otherwise, your app will run as the ASPNET account, not as
your client's account.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:#fTKv5NaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> I am trying to use GetReportParameters method of the reportserver
webservice. Here is the code.
> ReportingService.ReportingService rService = new
ReportingService.ReportingService();
> rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
> string historyID = null;
> bool forRendering = true;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters;
> parameters = rService.GetReportParameters
> (
> "/Demo/TransactionReport",
> historyID,
> forRendering,
> values,
> credentials
> );
> foreach(ValidValue v in parameters[0].ValidValues)
> {
> Response.Write(v.Label + ": " + v.Value);
> Response.Write("<br>");
> }
> But when i executed the above code, i am getting the following error..
> System.Web.Services.Protocols.SoapException: The permissions granted to
user 'DPA32W126\ASPNET' are insufficient for performing this operation. -->
> I understand there is no problem with the code, but security settings
needs to be changed for it to work.
> Can anyone guide me on how to make the above code working.
> Thankyou,
> Regards,
> Rajesh Jagadeesan.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Hi Jenny:
You can use the NetworkCredential class from the System.Net namespace.
Also, my article might be of some help:
http://odetocode.com/Articles/216.aspx
--
Scott
http://www.OdeToCode.com
On Mon, 13 Sep 2004 13:13:08 -0700, "Jenny"
<Jenny@.discussions.microsoft.com> wrote:
>Hi! I am having a similar problem. I am creating a custom interface for
>users to gather parameters and then select reports to view. I want to only
>pass in the parameters that a given report needs rather then all the
>parameters I am gathering. I am getting this error when the
>GetReportParameters runs:
>The request failed with HTTP status 401: Unauthorized.
>I tried adding in the identity impersonate = true but it didn't do anything.
> Can I change the credentials that are passed in? What else can I check? Is
>there another way to check what parameters are needed for a given report?
>Thank you for the help!
>Jenny
>

getMoreResults(Statement.KEEP_CURRENT_RESULT) problem

Hi,
I am using Microsoft JDBC driver (SP2) in order to communicate with my
application.
When calling the Statement's getMoreResults() method with a
Statement.KEEP_CURRENT_RESULT parameter, the driver throws the following
exception:
java.lang.AbstractMethodError:
com.microsoft.jdbc.base.BaseCallableStatement.getM oreResults(I)Z
However, when I dropped the KEEP_CURRENT_RESULT everything went smoothly.
In both ways I am using a TYPE_SCROLL_INSENSITIVE and a CONCUR_READ_ONLY
when executing a query.
Since I really need to keep the previous results for display (for example
when calling for EXEC sp_monitor), I can't "live" without the
KEEP_CURRENT_RESULT argument.
Is there any known way around it?
Thanks!
Shaji,
The KEEP_CURRENT_RESULT parameter is a JDBC 3.0 spec function. The
Microsoft JDBC driver (SP2) does not support JDBC 3.0 spec functions.
There is no work around with this driver.
DataDirect recently released a version of our driver which does
support this parameter. It is supported in our 3.4 release. If you
would like to download an evaluation copy of the driver, go to
http://www.datadirect.com/downloads/...jdbc/index.ssp
Sue Purkis
DataDirect Technologies
"Shaji" <shji1@.yahoo.com> wrote in message news:<O#p6#YhTEHA.1368@.TK2MSFTNGP11.phx.gbl>...
> Hi,
> I am using Microsoft JDBC driver (SP2) in order to communicate with my
> application.
> When calling the Statement's getMoreResults() method with a
> Statement.KEEP_CURRENT_RESULT parameter, the driver throws the following
> exception:
> java.lang.AbstractMethodError:
> com.microsoft.jdbc.base.BaseCallableStatement.getM oreResults(I)Z
> However, when I dropped the KEEP_CURRENT_RESULT everything went smoothly.
> In both ways I am using a TYPE_SCROLL_INSENSITIVE and a CONCUR_READ_ONLY
> when executing a query.
> Since I really need to keep the previous results for display (for example
> when calling for EXEC sp_monitor), I can't "live" without the
> KEEP_CURRENT_RESULT argument.
> Is there any known way around it?
> Thanks!

GetDataDrivenSubscriptionProperties vs GetSubscriptionProperties

Is there a similar method for Data Driven Subscriptions like there is for plain subscriptions ListSubscriptions. I would like to get SubscriptionID for use with the GetDataDrivenSubscriptionProperties just as I do for GetSubscriptionProperties. When I execute GetSubscriptionProperties for Data Driven Subscriptions there is no array of Subscriptions.

Here is an examole of the code which I execute using rs.exe:

Sub Main()
'Dim rs As New ReportingService()
'rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim DRPlan As DataRetrievalPlan
Dim extSettings As ExtensionSettings
Dim desc As String
Dim active As ActiveState
Dim status As String
Dim eventType As String
Dim matchData As String
Dim values As ParameterValue() = Nothing
Dim subscriptions As Subscription() = Nothing
Dim extensionParams As ParameterValueOrFieldReference() = Nothing

Try
subscriptions = rs.ListSubscriptions("/SecLend Monthly Statements/CLS0001-Client Statement Package V1", "MWDOMAIN\asingh")
' subscriptions = rs.ListSubscriptions("/Risk Management/ALD1701-AldStatistics", Nothing)

If Not (subscriptions Is Nothing) Then
Console.WriteLine("not subscriptions Is Nothing")

' Retrieve properties for the first subscription in the list.

Dim subx As Subscription

For Each subx In subscriptions

if subx.IsDataDriven = false then
Console.WriteLine("not IsDataDriven")
rs.GetSubscriptionProperties(subx.SubscriptionID, extSettings, desc, active, status, eventType, matchData, values)
else
Console.WriteLine("IsDataDriven")
rs.GetDataDrivenSubscriptionProperties(subx.SubscriptionID, extSettings, DRPlan, desc, active, status, eventType, matchData, values)
end if

Console.WriteLine("Description: {0}", desc)
Console.WriteLine("Status: {0}", status)
Console.WriteLine("EventType: {0}", eventType)
Console.WriteLine("matchData: {0}", matchData)
Console.WriteLine("Extension: {0}", extSettings.Extension)

extensionParams = extSettings.ParameterValues

If Not (extensionParams Is Nothing) Then
Dim extensionParam As ParameterValueOrFieldReference
For Each extensionParam In extensionParams
Console.WriteLine((CType(extensionParam, ParameterValue).Name + ": " + CType(extensionParam, ParameterValue).Value))
Next extensionParam
End If

If Not (values Is Nothing) Then
Dim pv As ParameterValue
For Each pv In values
Console.WriteLine("Name: {0}", pv.Name)
Console.WriteLine("Value: {0}", pv.Value)
Next pv
End If
Next subx
End If

Catch e As SoapException
Console.WriteLine(e.Detail.OuterXml)
End Try
End Sub 'Main

Please let me know if anybody has any ideas on how I can get GetDataDrivenSubscriptionProperties if I don't have the SubscriptionID.

Elias

ListSubscriptions will list all subscriptions of a report including data driven subscriptions. Check out the link below. Also, I'm not sure if I'm missing something here because you already have theSubscriptionId that you can use with GetDataDrivenSubscriptionProperties. Please let me know what I'm missing here.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.listsubscriptions.aspx

Thanks,
Sharmila

|||

Sharmila,

My experiance is when I do a rs.ListSubscriptions on a report that has Data driven Subscriptions then the statement "For Each subx In subscriptions" does not find any subscriptions and never falls into the for each loop. On a plain Subscriptions it does loop around for each subscription.

It seems that rs.ListSubscriptions does not work with Data driven Subscriptions.

Thanks

Elias

|||

It could be that the current user does not have sufficient permissions. From the link I sent earlier:

If only the Report parameter is submitted, the method returns all subscriptions for all users of the specified report that the current user has permission to view.

Can you check if the user that makes the ListSubscriptions API call has "View Reports" and "Manage All Subscriptions" permissions?

Thanks,
Sharmila

|||

I changed:

subscriptions = rs.ListSubscriptions("/SecLend Monthly Statements/CLS0001-Client Statement Package V1", "MWDOMAIN\asingh")

to

subscriptions = rs.ListSubscriptions("/SecLend Monthly Statements/CLS0001-Client Statement Package V1", Nothing)

Now I am getting subscriptions. But when it reaches a data driven subscription and executes the following line:

rs.GetDataDrivenSubscriptionProperties(subx.SubscriptionID, extSettings, DRPlan, desc, active, status, eventType, matchData, values)

it gets an error message of:

Unhandled exception:
Specified cast is not valid.

Any Ideas?

Thanks

Elias

|||

You need to pass in "extensionParams " instead of "values" for GetDataDrivenSubscriptionProperties. See documentation below.

http://msdn2.microsoft.com/de-de/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getdatadrivensubscriptionproperties.aspx

Hope this helps,

Sharmila

|||

Sharmila,

Sorry I didn't get back till now. but I was out of town.

I am still having a problem. My problem is with data driven Subscriptions.

I am getting the following error:

Unhandled exception:
Specified cast is not valid.

This error is only for Data Driven Subscriptions.

I get this error on either line of code

Console.WriteLine((CType(extensionParam, ParameterValue).Name + ": " + CType(extensionParam, ParameterValue).Value))

or

Console.WriteLine((CType(pv2, ParameterValue).Name + ": " + CType(pv2, ParameterValue).Value))

Here is the code:

Sub Main()
'Dim rs As New ReportingService()
'rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim DRPlan As DataRetrievalPlan
Dim extSettings As ExtensionSettings
Dim desc As String
Dim active As ActiveState
Dim status As String
Dim eventType As String
Dim matchData As String
Dim values As ParameterValue() = Nothing
Dim subscriptions As Subscription() = Nothing
Dim extensionParams As ParameterValueOrFieldReference() = Nothing
Dim values2 As ParameterValueOrFieldReference() = Nothing
Dim values2Params As ParameterValueOrFieldReference() = Nothing

Try
subscriptions = rs.ListSubscriptions("/SecLend Monthly Statements/CLS0001-Client Statement Package V1", Nothing)
' subscriptions = rs.ListSubscriptions("/Risk Management/ALD1701-AldStatistics", Nothing)

If Not (subscriptions Is Nothing) Then
Console.WriteLine("not subscriptions Is Nothing")

Dim subx As Subscription

For Each subx In subscriptions

if subx.IsDataDriven = false then
Console.WriteLine("not IsDataDriven")
rs.GetSubscriptionProperties(subx.SubscriptionID, extSettings, desc, active, status, eventType, matchData, values)
else
Console.WriteLine("IsDataDriven")
rs.GetDataDrivenSubscriptionProperties(subx.SubscriptionID, extSettings, DRPlan, desc, active, status, eventType, matchData, values2)
end if

Console.WriteLine("Description: {0}", desc)
Console.WriteLine("Status: {0}", status)
Console.WriteLine("EventType: {0}", eventType)
Console.WriteLine("matchData: {0}", matchData)
Console.WriteLine("Extension: {0}", extSettings.Extension)

extensionParams = extSettings.ParameterValues

If Not (extensionParams Is Nothing) Then
Dim extensionParam As ParameterValueOrFieldReference
For Each extensionParam In extensionParams
Console.WriteLine((CType(extensionParam, ParameterValue).Name + ": " + CType(extensionParam, ParameterValue).Value))
Next extensionParam
End If

if subx.IsDataDriven = false then
If Not (values Is Nothing) Then
Dim pv As ParameterValue
For Each pv In values
Console.WriteLine(pv.Name + ": " + pv.Value)
Next pv
End If
Else
If Not (values2 Is Nothing) Then
Dim pv2 As ParameterValueOrFieldReference
'Dim pv As ParameterValue
For Each pv2 In values2
Console.WriteLine((CType(pv2, ParameterValue).Name + ": " + CType(pv2, ParameterValue).Value))
Next pv2
End If
End If

Next subx
End If

Catch e As SoapException
Console.WriteLine(e.Detail.OuterXml)
End Try
End Sub 'Main

Elias

|||

Hi Elias,

Try the following for data driven subscriptions. I'm sure you can translate this to VB.

foreach (ParameterValueOrFieldReference extensionParam in extensionParams)

{

if (extensionParam is ParameterFieldReference)

{

Console.WriteLine(((ParameterFieldReference)extensionParam).ParameterName + ": " + ((ParameterFieldReference)extensionParam).FieldAlias);

}

else

{

Console.WriteLine(((ParameterValue)extensionParam).Name + ": " + ((ParameterValue)extensionParam).Value);

}

}

Hope this helps,

Sharmila

|||

Hi Sharmila,

I am having a problem figuring out how to test extensionParam if is ParameterFieldReference or ParameterValue in VB.

Please help.

Elias

|||

Hi Elias,

I think you need to use TypeOf: See http://msdn2.microsoft.com/en-us/library/0ec5kw18(VS.80).aspx for more info.

If TypeOf extensionParam Is ParameterFieldReference Then ....

Thanks,

Sharmila

|||

Sharmila,

Thanks, I finally got it to work. Your help was very much appreciated.

Elias

GetBlobData method fails

Hi,

I have s Script Component, that retrieves data from NTEXT column using this code:

Dim b As Byte()

If (Row.OutputXML.Length > 0) And (Not (Row.OutputXML_IsNull)) Then

b = Row.OutputXML.GetBlobData(0, CInt(Row.OutputXML.Length))

End If

I′m getting this error:

[Script Component 1 [838]] Error: System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

OutputXML column is filled by Ole Db Command from an output parameter (nvarchar(max)) of a stored procedure. In management studio sp works fine, even Execute SQL Task returns correct (but truncated) data.

Please, help!

what is the SSIS data type (not the original SQL data type) assigned in OleDB Command to OutputXML column?|||data type is DT_NTEXT

GetAttribute Method of the Urn Class

There is probably a simple way to find this out but I cannot find it:

How would I be able to find out what the possible attributeName values are which you can retrieve by using the GetAttribute method. For example, one of them is "Name" which returns the name of the object. I need to find out the type of the object (e.g. table, view, UDF...)

You can obtain the type of the object as a string using the Urn.Type property.
eg. for Urn u = new Urn("Server/Database/Table");
u.Type would return you "Table".

Thanks,
Kuntal

Get the old SQL servername

I recall a method to get the old servername of a SQL server but old age has gotten to me.
I suspect a particular server has been renamed at one point. Obviously it wasn't me and the person before me is not around for me to ask.
So, can someone help an old guy out?

The results of print @.@.servername is NULL.

ThanksWhat OS? Which version of SQL?

If @.@.SERVERNAME is null it usually means that the sysservers entry is missing.

I'm not positive where to look for the old name, but a good place to start would be the registry. Look in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances.

Regards,

hmscott|||SELECT datasource
FROM master.dbo.sysservers
WHERE 0 = srvid-PatP

Get servers current date format?

sql server 2k...
I found the Set DateFormat method but I can't seem to find a get method
where it will return to me what sqlserver is set as...(mdy,dmy etc..) does
such a method/property exist?
thanks
Doug
Doug Swanson
Senior Applications Developer
Synchrono, Inc
651.228.1772
dswanson@.synchrono.comYou can use DBCC USEROPTIONS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Doug Swanson" <dcswanson@._remove_this_surfbest.net> wrote in message
news:ujHMya0FEHA.1272@.TK2MSFTNGP12.phx.gbl...
> sql server 2k...
> I found the Set DateFormat method but I can't seem to find a get method
> where it will return to me what sqlserver is set as...(mdy,dmy etc..) doe
s
> such a method/property exist?
> thanks
> Doug
> --
> Doug Swanson
> Senior Applications Developer
> Synchrono, Inc
> 651.228.1772
> dswanson@.synchrono.com
>

Get path of SYSTEM32 directory

Hello all, i have a little problem...

...Is there any method for getting the path of the SYSTEM32 directory from a stored procedure?

I need it for register an assembly that is in this directory or the server.

Thanks.

Alejandro F.

In SQL Server 2005, I would use the CLR to do this. You would have to mark it as unsafe allowing external access, but then you could just use .NET's native functionality to get the value.

|||

Thanks, but I need the path of system32 directory for registering my DLL with my CLR functions.

It’s any way to register a DLL without specify the entire path of the DLL?

Sorry about my English…

|||

No, but what I was suggesting was to build a CLR function (that you will be building and deploying manually) and use a windows API type call to get the path of system32.

Since this directory doesn't change, why not just have a configuration table that you put the path into? That would be easiest.

|||It's not a bad idea, thanks for all.|||

Consider doing these type of operations outside of the database? What is the purpose of this task? Is it an administrative routine? How often does it run? Do you really need to enable CLR or xp_cmdshell for such simple operations? Who runs these operations? For example, if you are creating assemblies using a script then it is trivial to obtain the path using various client-side techniques. It takes more programming and manageability to do even simple operations that require access to OS resources. And not to mention the security implications.

So if you have a choice keep TSQL for operations it can do best. I usually find it strange when people try to use TSQL to solve every problem. For example, in a CMD script you need to just reference %windir%\system32 - this assumes you run the script on the server. Now, if you want to do the same using TSQL then you need to enable xp_cmdshell, create temporary table, insert results into temporary table, read from temporary table, check for errors etc. The CMD script approach is more robust easy to execute on server, isolates higher-privilege operations, easy to modify (what if you later want to deploy assemblies by synching from your source code control system on different server) and so on.