CLR TVF -> Using Datareader...

Hi
I'm trying to create a CLR-TVF which should do some stuff (in my sample it's
just getting the syscolumns name column for the database _ODS).
I’ve got this error:
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.
Question: Is it really not possible to make a sql-query within the C#-Part
of the CLR? I know, I could easily do it using a StoredProc…but I need a T
VF.
Thanks for your help…
Here is the code:
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)" )]
public static IEnumerable LoadSysColumns(string str2)
{
return str2;
}
public static void FillRow2(object row,
out string str2)
{
// creating a connection using the current sqlserver-context
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from _ODS.dbo.SYSCOLUMNS";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
str2 = sqlDataReader.GetValue(0).ToString();
}
}"Dominic" <Dominic@.discussions.microsoft.com> wrote in message
news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Hi
> I'm trying to create a CLR-TVF which should do some stuff (in my sample
> it's
> just getting the syscolumns name column for the database _ODS).
> I've got this error:
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Data access is not allowed in this
> context. Either the context is a function or method not marked with
> DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
> data from FillRow method of a Table Valued Function, or is a UDT
> validation
> method.
> Question: Is it really not possible to make a sql-query within the C#-Part
> of the CLR? I know, I could easily do it using a StoredProc.but I need a
> TVF.
> Thanks for your help.
> Here is the code:
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)" )]
> public static IEnumerable LoadSysColumns(string str2)
> {
> return str2;
> }
> public static void FillRow2(object row,
> out string str2)
> {
> // creating a connection using the current sqlserver-context
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from
> _ODS.dbo.SYSCOLUMNS";
> SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
> sqlDataReader.Read();
> str2 = sqlDataReader.GetValue(0).ToString();
> }
> }
>
Any data access should be in the LoadSysColumns method, not in the
FillRowMethod. In LoadSysColumns just fill a List<string> and return that.
SQL will enumerate it and pass each member to your FillRowMethod, EG:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)")]
public static IEnumerable LoadSysColumns()
{
List<string> names = new List<string>();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
while (sqlDataReader.Read())
{
names.Add(sqlDataReader.GetValue(0).ToString());
}
}
return names;
}
public static void FillRow2(object row, out string str2)
{
str2 = (string)row;
}
};
David|||Thanks David...works fine!
"David Browne" wrote:

> "Dominic" <Dominic@.discussions.microsoft.com> wrote in message
> news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Any data access should be in the LoadSysColumns method, not in the
> FillRowMethod. In LoadSysColumns just fill a List<string> and return that
.
> SQL will enumerate it and pass each member to your FillRowMethod, EG:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections;
> using System.Collections.Generic;
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.None,
> SystemDataAccess = SystemDataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)")]
> public static IEnumerable LoadSysColumns()
> {
> List<string> names = new List<string>();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
> using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
> while (sqlDataReader.Read())
> {
> names.Add(sqlDataReader.GetValue(0).ToString());
> }
> }
> return names;
> }
> public static void FillRow2(object row, out string str2)
> {
> str2 = (string)row;
> }
> };
>
> David
>
>

Closing a conversation later on and Microsoft Service Broker Interface (sample)

In a messaging application, a client sends a message to the server. The server uses Service Broker Interface, and is CLR based. On the server, the message has a life cycle - going throughout multiple transitions until is final (Pending, In Transit, Delivered, TimedOut, Undeliverable). As the server receives the message, it sends back to the client a reply status message of In Transit. It does so using the same message’s conversation. It also saves – along with the message – its conversation handle. The dialog is not closed. After a while, the server may decide that the message has gotten to a final state (Delivered or TimedOut). I would like at that time to send a reply status message (Final State) back to the client and also close the conversation. The conversation handle has been saved by both, client and server. Having the conversation handle available - how do I get back to the client later on?

Thanks,

I don't understand what you mean by 'get back to the client later on'. Do you want to send a message using the conversation handle which was saved earlier? That's simple, you only have to execute the SEND statement with the saved conversation handle (or call Conversation.Send(...) in the ServiceBrokerInterface sample). Note that you cannot send after the conversation has been closed at your endpoint.|||

Yes, indeed, I would like to send a message using the conversation handle thant was saved earlier. This will be done by a different thread. All I have saved is the conversation hanlde and I am thinking to create a Conversation object based on the handle only. Can it be done?

Thanks,

|||I'll have to look it up if the Conversation class has a public constructor. If not, the ServiceBrokerInterface library is free for you to modify :-)|||That's right :). I am thinking about this way: (new Service(server service parameters)).GetConversation (mySavedConversationHandle). Thanks for following up.

Client-side programming patterns/idioms for SQL Mobile 2005?

Hello,

Where can I get sample code, or a pointer to sample code demonstrating common programming pattern(s) for a client of SQL Mobile 2005 (where the client is also running on the device)?

For eg, with ADO.NET, I'd do something like:

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = conn.CreateCommand();
conn.CommandText = “--...my query…”;
SqlDataReader reader = conn.ExecuteReader();
// loop and process the data

What are the corresponding APIs and calls that I’d use for a SQL Mobile 2005 application using the latest CF.Net 2.0 ADO.Net APIs (or whatever they’re called)? I could only find a couple of articles on MSDN, both applying to SQLCE 2.0 and not SQL Mobile:

[1]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road05222002.asp
[2]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/ppcdatabase.asp

If this is documented in Books Online, how can I get to it? I couldn’t find it.

Thanks in advance!

-Ravi

The samples are in books on line of SQL Mobile and .NET CF 2
http://www.microsoft.com/sql/editions/sqlmobile/overview.mspx
http://msdn.microsoft.com/mobility/sqlmobile/default.aspx|||Navigating from the above links to get to the information I need is extremely cumbersome. For now, a search on MSDN2 led me to the following page, on which the link [Using Programming Interfaces] is exactly what I was looking for:

http://msdn2.microsoft.com/en-us/library/ms172961

Thanks,
ravi|||

For the benefit of others who may have the same question, I thought I would post some sample code that demonstrates a client connection programming pattern for SQL Mobile clients:

string dbFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "/data.sdf";
string connectionString = string.Format("Data Source = {0};", dbFilePath);
using (SqlCeConnection conn = new SqlCeConnection(connectionString)) {
try {
/* open the connection, get a command */
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = @."CREATE TABLE myTable(i int primary key)";
cmd.ExecuteNonQuery();
}
catch (Exception e) {
string logMessage = e.Message;
/* logging code goes here */
}
finally {
conn.Close();
}
} // end using

Note that this code is intended for a client application running on a device that is attempting to connect to a SQL Mobile database on the same device.

|||

Ravi Subramanian wrote:

Note that this code is intended for a client application running on a device that is attempting to connect to a SQL Mobile database on the same device.

Can you actually connect to a SQL Mobile db on another device? I thought it always had to be on the same device as your app.
Can anyone confirm this?|||SQL Mobile Client app and the DB should be on the same device except in the following scenarios:
1) SQL Server 2005 Management Studio (running on Desktop) can be used to connect to a SQL Mobile DB on the device
2) VS 2005 Management Studio (running on Desktop) can be used to connect to a SQL Mobile DB on the device

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Click through Report Parameter

Hi there.

I am having a problem where I have a Summary Report for a Region that lists out data for each community in that region (sample below):

West Region Count Community 1 N/A Community 2 14 Community 3 41 Community 4 25 Community 5 38 Community 6 67 Community 7 40 Community 8 52

I have navigation setup such that when I click on a Community (like Community_1 above), the detailed community report is called. I am passing CommunityID as a parameter to the community report. The community report has a CommunityID (literally) parameter setup. This seems fine, but when I click on a community from the Region report (for example, Community_1 above), the community report does not automatically get rendered, it makes me select a community before it renders. I must have something setup incorrectly, but can't figure it out. Any ideas would be appreciated.

I want the community report to be rendered based on the selected community without having to select it again.

Thanks, Mike

Sounds like the detailed community report has a parameter that does not have a default value set. That would be why it is asking for an input when it starts up. There is a parameter button on the navagation tab of the textbox properties window to map the default parameter from the original report to the report you are jumping. If there is only one "CommunityID" parameter in the jump too report that should be it. Might also check for a duplicate parameter that could have been accidently added without a default.

|||

Thanks for the response.

I do not have a default parameter set on the community detail report, but how do I do that when the report is dual purpose - 1. A click through where you pass a community id AND 2. a standalone community report where you pick your community from a list. Is this possible? It seems like it should be and I have something setup wrong still.

I do not see a place to setup a default parameter on the navigation for the textbox. I assume this is what you mean:

I click on parameters button on the navigation tab (where I have the community report selected). I pick CommunityID from the dropdown and set it = to =Fields!CommunityID.Value.

I think that should do it.

Also, I checked and I do not have a duplicate parameter. Other ideas?

Thanks, Mike

|||

To set the default parameter of the "Jump to report" from the original report:

In the original report:

Right click on the textbox with the jump to feature.

Select properties from the drop down.

Click navagation tab.

In the hyperlink action section beside the jump to report field is a parameters button.

Click it to open a parameter mapping window.

On this window you select a value from the original report to default into the parameter of the "jump to" report.

When filled in there should be two fields with values; one the parameter name from the "jump to" report and the other a parameter value which is a field in the original report.

If the jump to report is receiving the value correctly it will not ask for the value to be input when it is called up. If you are using the report for dual purpose it does not need to have a built in default value. The default can be blank and its value populated as described above.

|||

Thanks again for the response.

I have the default parameter setup correctly as you described, so I think it is not being passed correctly. The value I am getting from the database os a char(5), so it might not be handling the padding correctly or something. I am going to try it with another field that is an int and make sure that works... then I will know if it is that stupid char(5) value. Grrrrrrr.

Thanks, Mike

|||This issue is killing me. I can get clickthrough reports to work fine with other parameters (ints instead of char(5)). I have tried trimming the value before sending it etc. I have tried passing a specific community code (with and without padding) and that does not work. Anyone have ideas?|||Is there a way that I can see the actual value being passed?|||

Put the value in question into a textbox in the original report.

The data type does need to be the same for the field in both reports; might be able to workaround using some kind of type conversion in the parameter or sql stmt.

|||

OK, some more info...

I am passing the community code and it does contain spaces for padding up to the 5 chars, but I do not think that is the issue. I just tried setting up the report parameter on the community report and forced the default to be one of my community codes (non-queried) and the report still does not automatically populate. I still have to select a community code.

What would cause the report not to generate?

|||More info. I can get it to work fine when I do not have it setup to get available values from a query. I should be able to do that shouldn't I?|||

I figured it out...

In my dataset on the Community detail report which gets the list of available values, I had to cast the community id to be a varchar as below:

CAST(CommunityID AS varchar(5))

Then it worked fine. What a pain.

Mike

Click through Report Parameter

Hi there.

I am having a problem where I have a Summary Report for a Region that lists out data for each community in that region (sample below):

West Region Count Community 1 N/A Community 2 14 Community 3 41 Community 4 25 Community 5 38 Community 6 67 Community 7 40 Community 8 52

I have navigation setup such that when I click on a Community (like Community_1 above), the detailed community report is called. I am passing CommunityID as a parameter to the community report. The community report has a CommunityID (literally) parameter setup. This seems fine, but when I click on a community from the Region report (for example, Community_1 above), the community report does not automatically get rendered, it makes me select a community before it renders. I must have something setup incorrectly, but can't figure it out. Any ideas would be appreciated.

I want the community report to be rendered based on the selected community without having to select it again.

Thanks, Mike

Sounds like the detailed community report has a parameter that does not have a default value set. That would be why it is asking for an input when it starts up. There is a parameter button on the navagation tab of the textbox properties window to map the default parameter from the original report to the report you are jumping. If there is only one "CommunityID" parameter in the jump too report that should be it. Might also check for a duplicate parameter that could have been accidently added without a default.

|||

Thanks for the response.

I do not have a default parameter set on the community detail report, but how do I do that when the report is dual purpose - 1. A click through where you pass a community id AND 2. a standalone community report where you pick your community from a list. Is this possible? It seems like it should be and I have something setup wrong still.

I do not see a place to setup a default parameter on the navigation for the textbox. I assume this is what you mean:

I click on parameters button on the navigation tab (where I have the community report selected). I pick CommunityID from the dropdown and set it = to =Fields!CommunityID.Value.

I think that should do it.

Also, I checked and I do not have a duplicate parameter. Other ideas?

Thanks, Mike

|||

To set the default parameter of the "Jump to report" from the original report:

In the original report:

Right click on the textbox with the jump to feature.

Select properties from the drop down.

Click navagation tab.

In the hyperlink action section beside the jump to report field is a parameters button.

Click it to open a parameter mapping window.

On this window you select a value from the original report to default into the parameter of the "jump to" report.

When filled in there should be two fields with values; one the parameter name from the "jump to" report and the other a parameter value which is a field in the original report.

If the jump to report is receiving the value correctly it will not ask for the value to be input when it is called up. If you are using the report for dual purpose it does not need to have a built in default value. The default can be blank and its value populated as described above.

|||

Thanks again for the response.

I have the default parameter setup correctly as you described, so I think it is not being passed correctly. The value I am getting from the database os a char(5), so it might not be handling the padding correctly or something. I am going to try it with another field that is an int and make sure that works... then I will know if it is that stupid char(5) value. Grrrrrrr.

Thanks, Mike

|||This issue is killing me. I can get clickthrough reports to work fine with other parameters (ints instead of char(5)). I have tried trimming the value before sending it etc. I have tried passing a specific community code (with and without padding) and that does not work. Anyone have ideas?|||Is there a way that I can see the actual value being passed?|||

Put the value in question into a textbox in the original report.

The data type does need to be the same for the field in both reports; might be able to workaround using some kind of type conversion in the parameter or sql stmt.

|||

OK, some more info...

I am passing the community code and it does contain spaces for padding up to the 5 chars, but I do not think that is the issue. I just tried setting up the report parameter on the community report and forced the default to be one of my community codes (non-queried) and the report still does not automatically populate. I still have to select a community code.

What would cause the report not to generate?

|||More info. I can get it to work fine when I do not have it setup to get available values from a query. I should be able to do that shouldn't I?|||

I figured it out...

In my dataset on the Community detail report which gets the list of available values, I had to cast the community id to be a varchar as below:

CAST(CommunityID AS varchar(5))

Then it worked fine. What a pain.

Mike

Clearing cache

Is it possible to clear cache without restarting the SSAS service ?.

In AS2000 It was done by using the flushcache vbscript sample, but I didnt find a method similar to suspend/resume one, in AMO or xmla syntaxis.

Thanks

Leandro

I've just found a command in xmla for clearing cache:

<Command>
<ClearCache>
<Object>...</Object>
</ClearCache>
</Command>

Thanks Leandro

|||

You might find the 'ascmd' utility useful. It includes a sample for clearing the cache. The utility is part of the April 2006 updated Samples under Analysis Services \ Administration

Download here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

_-_-_ Dave Wickert