Can't Call Stored Procedure in SqlFunction

There is a SqlFunction in my SQL Server Project:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@.ID"].Value = ID;*/

using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}

When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."

What does the error message mean? How could I correct it? Thanks for answering.

The problem is resolved. Just remove the default "SET NOCOUNT ON;" added by SQL 2005 while creating stored procedure, then the SqlFunction will work.|||

Does anyone know of another way to fix this? We have alot of stored procedures with "SET NOCOUNT ON" and I would really rather not have to make that change unless we have to.

Thanks,
JD

|||Hi JD,

Bleh... I can't see good way. :) You could make a normal connection instead of using the context connection, but you'll have to deploy your assembly as external-access.

Cheers,
-Isaac

Can't Call Stored Procedure in SqlFunction

There is a SqlFunction in my SQL Server Project:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@.ID"].Value = ID;*/

using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}

When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."

What does the error message mean? How could I correct it? Thanks for answering.

The problem is resolved. Just remove the default "SET NOCOUNT ON;" added by SQL 2005 while creating stored procedure, then the SqlFunction will work.|||

Does anyone know of another way to fix this? We have alot of stored procedures with "SET NOCOUNT ON" and I would really rather not have to make that change unless we have to.

Thanks,
JD

|||Hi JD,

Bleh... I can't see good way. :) You could make a normal connection instead of using the context connection, but you'll have to deploy your assembly as external-access.

Cheers,
-Isaacsql

can't call method CreateReport under asp.net

CreateReport throws an error saying that user IUSR_XXXXX doesn't have
permisssions
to run this method.
So,
1) I went to ReportServices roles and anded a new group for IUSR_XXXXX user
2) Assigned ReportserverSysadmin role for the new group.
3) Also set db_owner permissions for the IUSR_XXXXX user in ReportService
related databases on
my local instance of MS SQL.
No results. Still can't call method CreateReport().
Any ideas ?
Thanks
Paul
PS. BTW this method works fine from *.rss script that is running under
local Admin account.You need to give this user permission in the specific folder that they are
trying to create the report in. The SysAdmin role is just for system level
operations, not item specific operations.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"pTsy" <ptsygura@.bna.com> wrote in message
news:e79HvvadEHA.3944@.tk2msftngp13.phx.gbl...
> CreateReport throws an error saying that user IUSR_XXXXX doesn't have
> permisssions
> to run this method.
> So,
> 1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
user
> 2) Assigned ReportserverSysadmin role for the new group.
> 3) Also set db_owner permissions for the IUSR_XXXXX user in
ReportService
> related databases on
> my local instance of MS SQL.
> No results. Still can't call method CreateReport().
> Any ideas ?
> Thanks
> Paul
> PS. BTW this method works fine from *.rss script that is running under
> local Admin account.
>
>|||Also, try enabling impersonation for your ASP.NET app. See:
http://blogs.msdn.com/bryanke/archive/2004/03/03/83345.aspx
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"pTsy" <ptsygura@.bna.com> wrote in message
news:e79HvvadEHA.3944@.tk2msftngp13.phx.gbl...
> CreateReport throws an error saying that user IUSR_XXXXX doesn't have
> permisssions
> to run this method.
> So,
> 1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
user
> 2) Assigned ReportserverSysadmin role for the new group.
> 3) Also set db_owner permissions for the IUSR_XXXXX user in
ReportService
> related databases on
> my local instance of MS SQL.
> No results. Still can't call method CreateReport().
> Any ideas ?
> Thanks
> Paul
> PS. BTW this method works fine from *.rss script that is running under
> local Admin account.
>
>|||Hi Daniel
Thanks for answering
But how to do it through report server UI ?
or should I use some API function call ?
Thanks
Paul
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:ugZW9KcdEHA.3792@.TK2MSFTNGP09.phx.gbl...
> You need to give this user permission in the specific folder that they are
> trying to create the report in. The SysAdmin role is just for system
level
> operations, not item specific operations.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "pTsy" <ptsygura@.bna.com> wrote in message
> news:e79HvvadEHA.3944@.tk2msftngp13.phx.gbl...
> > CreateReport throws an error saying that user IUSR_XXXXX doesn't have
> > permisssions
> > to run this method.
> >
> > So,
> > 1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
> user
> > 2) Assigned ReportserverSysadmin role for the new group.
> > 3) Also set db_owner permissions for the IUSR_XXXXX user in
> ReportService
> > related databases on
> > my local instance of MS SQL.
> >
> > No results. Still can't call method CreateReport().
> > Any ideas ?
> > Thanks
> > Paul
> >
> > PS. BTW this method works fine from *.rss script that is running under
> > local Admin account.
> >
> >
> >
> >
>|||Go to report manager as a local admin, click on the property tab, click on
the security sub-tab and there you can set permissions. You can do this
for any item in the catalog.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"pTsy" <ptsygura@.bna.com> wrote in message
news:uW47l1veEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Daniel
> Thanks for answering
> But how to do it through report server UI ?
> or should I use some API function call ?
> Thanks
> Paul
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:ugZW9KcdEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > You need to give this user permission in the specific folder that they
are
> > trying to create the report in. The SysAdmin role is just for system
> level
> > operations, not item specific operations.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "pTsy" <ptsygura@.bna.com> wrote in message
> > news:e79HvvadEHA.3944@.tk2msftngp13.phx.gbl...
> > > CreateReport throws an error saying that user IUSR_XXXXX doesn't have
> > > permisssions
> > > to run this method.
> > >
> > > So,
> > > 1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
> > user
> > > 2) Assigned ReportserverSysadmin role for the new group.
> > > 3) Also set db_owner permissions for the IUSR_XXXXX user in
> > ReportService
> > > related databases on
> > > my local instance of MS SQL.
> > >
> > > No results. Still can't call method CreateReport().
> > > Any ideas ?
> > > Thanks
> > > Paul
> > >
> > > PS. BTW this method works fine from *.rss script that is running
under
> > > local Admin account.
> > >
> > >
> > >
> > >
> >
> >
>

cant add sql endpoint to my visual studio project. trying "add as web reference" does

i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.

create endpoint testEndpoint authorization [FINTAN-PC\Fintan]
state=STARTED
as http
(
path ='/sql',
authentication=(INTEGRATED),
ports = (CLEAR),
site='SERVER'
)
For SOAP
(
webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE='adventureworks',
NAMESPACE='http://thenamespace.org'
);
GO

the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?

I'm a little unclear on your mentioning of using Visual Studio to look for this SOAP endpoint. Are you referring to using UDDI?

Regarding "add as web reference" in Visual Studio, the method that has worked best for me is to type in the HTTP URL that points to the WSDL document.

In this scenario it will be: http://SERVER/sql?wsdl

This URL comes from:

site='SERVER'

path='/sql'

So, if the endpoint was configured with:

site='foobar'

path='/myOwnPath/subPath'

then the WSDL document URL will be http://foobar/myOwnPath/subPath?wsdl

One easy way to ensure the user account running Visual Studio has access to retrieve the WSDL document is to use a Web Browser (such as IE) and point to the WSDL document URL.

HTH,

Jimmy|||ok, tiny bit of progress made. when i use the http://sitename/path?wsdl , rather than getting a 404 error, i get a page cannot be displayed. this is in IE7. any ideas?

cant add sql endpoint to my visual studio project. trying "add as web reference" does

i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.

create endpoint testEndpoint authorization [FINTAN-PC\Fintan]
state=STARTED
as http
(
path ='/sql',
authentication=(INTEGRATED),
ports = (CLEAR),
site='SERVER'
)
For SOAP
(
webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE='adventureworks',
NAMESPACE='http://thenamespace.org'
);
GO

the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?

I'm a little unclear on your mentioning of using Visual Studio to look for this SOAP endpoint. Are you referring to using UDDI?

Regarding "add as web reference" in Visual Studio, the method that has worked best for me is to type in the HTTP URL that points to the WSDL document.

In this scenario it will be: http://SERVER/sql?wsdl

This URL comes from:

site='SERVER'

path='/sql'

So, if the endpoint was configured with:

site='foobar'

path='/myOwnPath/subPath'

then the WSDL document URL will be http://foobar/myOwnPath/subPath?wsdl

One easy way to ensure the user account running Visual Studio has access to retrieve the WSDL document is to use a Web Browser (such as IE) and point to the WSDL document URL.

HTH,

Jimmy|||ok, tiny bit of progress made. when i use the http://sitename/path?wsdl , rather than getting a 404 error, i get a page cannot be displayed. this is in IE7. any ideas?

cannot write to column in updatable RecordSet

Hi. I receive the SQLException:

[Microsoft][SQLServer 2000 Driver for JDBC]Can not update, the specified column is not writable.

when I attempt to call updateString() on a RecordSet column. I have set the Statement object on which I execute the query to obtain the RecordSet to be scrollable and updatable.

My goal is to replace the ID value contained in a particular column of each row with a longer text string that includes the ID value. If I can't alter the column values via the RecordSet, can I write SQL as part of the query to generate the text string?

Thank you.

RajCan You write a little example: what You have and what You want...
And your table's structure... if it is possible. and sqlsrv version.|||Hi. We run SQL Server 2000. I call the code that creates the Statement, executes the ResultSet, and outputs the results in separate methods, so here I include a summary of the code.

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_I NSENSITIVE, ResultSet.CONCUR_UPDATABLE);

...

rs = statement.executeQuery(queryString);

...

WriteEditSCRLinks(rs);

Here's the definition of WriteEditSCRLinks().

//Replaces each SCR ID with a hyperlink that opens the edit SCR page on the ID.
//Method needs to input an updatable, scrollable RecordSet.
void WriteEditSCRLinks(ResultSet rs) throws SQLException
{
String FieldName = "SCRID";

while (rs.next())
{
String IDValue = rs.getString(FieldName);

String UpdateString = "<A HREF=\"" + SiteConfigHelper.getEditSoftwareChangeRequestURL() + "?" + FieldName + "=" + IDValue + "\">" + IDValue + "</A>";

rs.updateString(FieldName, UpdateString);
}

//Need to move the cursor to the position before the first row
//so as not to break GraphingHelper methods.
rs.beforeFirst();
}

Since someone told me that the JDBC object model does not intend for me to alter the ResultSet if I shall not alter the database, I have to solve this problem in a different way anyway. However, I would appreciate knowing whether the concept of updating the ResultSet differs from the concept of writing to a column.

Thank you.

Raj

Cannot use anonymous methods inside SQLCLR...

Hi all,

The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:

If(ClassName.privateStaticDelegate == null)
ClassName.privateStaticDelegate = new MyDelegate(HiddenMethodName);
CallAnonymousMethod(ClassName.privateStaticDelegate);

Is there any workaround to fix this problem.

P.S. I googled about this problem and found only one article on it:
http://www.ayende.com/Blog/default,date,2005-12-26.aspx

Hi Almaz,

I'm not quite sure I understand the question, but we're pretty picky about statics in SAFE and EXTERNAL ACCESS assemblies. They should be okay if they're readonly or const, but otherwise not.

Cheers,

-Isaac

|||

Hi Isaac,

Here is the repro: Create a new SQL Server Project, add a following stored procedure and try to deploy the assembly:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == 5;
});
}
}

Following variant works:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int valueToFind = 5;
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == valueToFind;
});
}
}

Thanks in advance,
Sergei Almazov

|||

Right, the problem is that we don't allow store into a static field. In this case, the store was an optimization by the C# compiler. We have a requirement that whenever compiler does this, it should mark the static field with [System.Runtime.CompilerServices.CompilerGenerated] attribute. Apparently, this didn't happen here.

There are two possible workarounds here:

1) mark the class with [System.Runtime.CompilerServices.CompilerGenerated] attribute

2) register the assembly as UNSAFE assembly.

We will follow up with C# compiler and fix the issue, so you can remove your workaround in the future.

-Xiaowei

Cannot use anonymous methods inside SQLCLR...

Hi all,

The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:

If(ClassName.privateStaticDelegate ==null)
ClassName.privateStaticDelegate =new MyDelegate(HiddenMethodName);
CallAnonymousMethod(ClassName.privateStaticDelegate);

Is there any workaround to fix this problem.

P.S. I googled about this problem and found only one article on it:
http://www.ayende.com/Blog/default,date,2005-12-26.aspx

Hi Almaz,

I'm not quite sure I understand the question, but we're pretty picky about statics in SAFE and EXTERNAL ACCESS assemblies. They should be okay if they're readonly or const, but otherwise not.

Cheers,

-Isaac

|||

Hi Isaac,

Here is the repro: Create a new SQL Server Project, add a following stored procedure and try to deploy the assembly:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == 5;
});
}
}

Following variant works:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int valueToFind = 5;
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == valueToFind;
});
}
}

Thanks in advance,
Sergei Almazov

|||

Right, the problem is that we don't allow store into a static field. In this case, the store was an optimization by the C# compiler. We have a requirement that whenever compiler does this, it should mark the static field with [System.Runtime.CompilerServices.CompilerGenerated] attribute. Apparently, this didn't happen here.

There are two possible workarounds here:

1) mark the class with [System.Runtime.CompilerServices.CompilerGenerated] attribute

2) register the assembly as UNSAFE assembly.

We will follow up with C# compiler and fix the issue, so you can remove your workaround in the future.

-Xiaowei