External Activation and Notification Services

I have been going over the samples from PDC 2005 and trying to run the External Activator program in section 2 'CLR Services'. After sending a message to the Inventory Queue, I see that 'dm_broker_queue_monitors' table has a row in the table, with the state column shown as 'NOTIFIED', but the ExternalActivator program never gets notified of the event and therefore never launches the 'InventoryServer' program.

Querying the [ExternalActivatorQueue] shows no rows.

Querying the [Inventory Queue] shows messages are waiting to be received.

Executing the 'activator' command in the ExternalActviator program shows

Notification service 'ExternalActivator' on SQL Server 'DBSERVER\' and Database 'In
ventory' is connected to the database and working.

Any Ideas?

Thanks,

-KuoAfter some browsing through code in the samples, I figured out the problem. The queue thats suppose to contain all the notification messages for the ExternalActivator program was checking the correct queue for messages, however no messages were being created in the [ExternalActivatorQueue] whenever a new message was sent to the [Inventory Queue].
So I, dDropped the event, 'en', and reran the create event notifcation:

drop event notification en on queue [Inventory Queue]

create event notification en
on queue [Inventory Queue]
for queue_activation
to service 'ExternalActivator', 'current database';

sent a few more messages and the ExternalActivatorQueue started getting messages in the queue whenever a new message was sent to the [Inventory Queue].

This is really strange. I know I must of ran the create event notification command a few times. Oh well, it works now.

-Mr.Kuo

external access denied to update a config file

Hello,

I'm having an issue with a CLR Stored procedure. Everything works great in a 32 bit environment, I have a CLR SP that updates an xml file stored on a local drive. When I execute the Stored proc it does go and update what I want it to in a 32 bit system. When I run the CLR SP on a 64 bit cluster, I seem to have give the "everyone" group write permissions to my G: drive (which is where the file is located that I'm updating). What security context is this SP running under? I thought it would be either under the SQL Service account (which is a domain user in the local administrators group) or what I'm logged in as when I run it from Management Studio (which is a domain admin, also in the local administrators group). If I have given the local administrators group "Full Control" access to the G: drive, why isn't this enough? Why do I have to give the Everyone group write access?

The security context seems odd to me, it seems like it's not running as either one of those 2 users I mentioned, because if it was, then it should be able to update the xml file.

Any help appreciated. Here's the error I'm getting:

Msg 6522, Level 16, State 1, Procedure usp_XMLWriter, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_XMLWriter':

System.UnauthorizedAccessException: Access to the path 'g:\ssisPackages\BuildCalendar\andy.dtsConfig' is denied.

System.UnauthorizedAccessException:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)

at System.Xml.XmlTextWriter..ctor(String filename, Encoding encoding)

at System.Xml.XmlDocument.Save(String filename)

at XMLWriter.StoredProcedures.usp_XMLWriter(String xmlDocPath, String xmlNodePath, String xmlInnerText)

.

I found out that I'm able to get this to work if I put the SQL Service User account directly into the security of the G: Drive. For some reason, even though the user is in the Local Administrators group, it doesn't seem to pick up the fact that my user in is in the group, and therefore won't let him update a file in the folder.

Does anyone know of any issues in a 64 bit cluster where a CLR Stored proc isn't able to decipher the users in a local Group?

|||This turned out to be a cluster issue. We weren't doing permissions through the cluster, we were doing permissions to the file shares through each node, which is a mistake in a cluster.

Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a we
ek.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
.................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know wha
t
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using
a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need a
t
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain,
a
> C# program is probabaly better than an extended stored procedure. Extend
ed
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL.
So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation o
n
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>

Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a week.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?
"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David
|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
..................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know what
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>
|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David
|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need at
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain, a
> C# program is probabaly better than an extended stored procedure. Extended
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation on
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>

Extended Stored Procedure <-> CLR

Hi
I should write an Extended Stored Procedure for a SQL Server 2000. This
Extended Stored Procedure includes differnet function which I like to call
from applications (C++, Java) via ODBC or JDBC. I have
done that already, I mean I know how to do that.
But now I read something
about CLR and that several functions from the Extended Stored Procedure will
be not supported in coming SQL server versions.
- Is CLR available in SQL server 2000?
- Is there some example code available?
- How can I call a function written with CLR from a C++ (or Java) app via
ODBC (or JDBC)?
Thanks for the help.
Regards
HansHello Hans,

> - Is CLR available in SQL server 2000?
No, at least not SQLCLR as you talked about in the first part of the post.

> - Is there some example code available?
Plenty. Just Google on SQLCLR.

> - How can I call a function written with CLR from a C++ (or Java) app
> via ODBC (or JDBC)?
Essentially the same you call an XP. You need to catalog the assembly with
SQL Server, the catalog T-SQL objects (functions, sprocs, types, triggers,
aggregators) to the cataloged assembly. You can then call the mapped T-SQL
objects in a select or action statement, or with an exec depending on the
type of object cataloged.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Extended properties created by Visual Studio on CLR UDF''s

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

Thanks a lot.

Aneela_B wrote:

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

AFAIK, the above properties do not have any use at the moment. They have no impact on debugging.

If you change the .NET code you have to re-deploy your assembly (either CREATE or ALTER).

Niels

Extended properties created by Visual Studio on CLR UDF''s

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

Thanks a lot.

Aneela_B wrote:

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

AFAIK, the above properties do not have any use at the moment. They have no impact on debugging.

If you change the .NET code you have to re-deploy your assembly (either CREATE or ALTER).

Niels