CLR Trigger in Problem in Sql Server 2005 april ctp

Hi all,
I've got a problem using various CLR Trigger samples: Most samples include
expressions like the snippet below, using the System.Data.SqlServer namespac
e
to access the SqlContext:
...
using System.Data.SqlServer;
...
SqlTriggerContext stc = SqlContext.GetTriggerContext;
SqlCommand scomm = SqlContext.GetCommand();
scomm.CommandText = "SELECT * from " + "INSERTED";
SqlDataRecord sdr = scomm.ExecuteRow();
String result1 = (string)sdr[0];
...
My problem: I don't have this namespace! Can't find it in my assemblies,
it's not even included in System.Data. Is there a new way for accessing the
exact updated/inserted/deleted data which caused the trigger to execute? I'm
using the april releases of Visual Studio 2005 Beta 2 and SQL Server 2005 CT
P.
thanks in advanceThat should go for the beta newsgroup.
If you make up a new projects within Visual Studio and use the Project SQL
Server Procedure, youll find those namespace to import. if you are running
on the actual Server with Visual Studio they are installed. If you are
running on another machine you probably have to install the native client to
get this namepsaces as well as the extensions to Visual Studio 2005 to use
it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"al" <al@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2AC8B8A5-4275-4D77-801E-5D6080002287@.microsoft.com...
> Hi all,
> I've got a problem using various CLR Trigger samples: Most samples include
> expressions like the snippet below, using the System.Data.SqlServer
> namespace
> to access the SqlContext:
> ...
> using System.Data.SqlServer;
> ...
> SqlTriggerContext stc = SqlContext.GetTriggerContext;
> SqlCommand scomm = SqlContext.GetCommand();
> scomm.CommandText = "SELECT * from " + "INSERTED";
> SqlDataRecord sdr = scomm.ExecuteRow();
> String result1 = (string)sdr[0];
> ...
> My problem: I don't have this namespace! Can't find it in my assemblies,
> it's not even included in System.Data. Is there a new way for accessing
> the
> exact updated/inserted/deleted data which caused the trigger to execute?
> I'm
> using the april releases of Visual Studio 2005 Beta 2 and SQL Server 2005
> CTP.
> thanks in advance|||hi jens,
that helped to solve the problem. looking at the references i found that
this namespace is held in the sqlaccess.dll, located in the sql server
folders.
a minor problem now is a compiler warning, stating that this namespace will
soon be obsolete and the classes be relocated to the system.data.sqlclient
namespace =) currently the move seems incomplete here, so my app will be as
beta as the server ;)
greetz
al
"Jens Sü?meyer" wrote:

> That should go for the beta newsgroup.
> If you make up a new projects within Visual Studio and use the Project SQL
> Server Procedure, you′ll find those namespace to import. if you are runni
ng
> on the actual Server with Visual Studio they are installed. If you are
> running on another machine you probably have to install the native client
to
> get this namepsaces as well as the extensions to Visual Studio 2005 to use
> it.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Nice to hear that it helped.

>so my app will be as
> beta as the server
We share your experience wiht that;-)
But i think thats quite nice from the sql server team because you can plan
it to move this over. In previous version there hasnt been such a compiler
warning. :-)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"al" <al@.discussions.microsoft.com> schrieb im Newsbeitrag
news:42A09372-8CE6-44FA-991D-4896A79C680A@.microsoft.com...
> hi jens,
> that helped to solve the problem. looking at the references i found that
> this namespace is held in the sqlaccess.dll, located in the sql server
> folders.
> a minor problem now is a compiler warning, stating that this namespace
> will
> soon be obsolete and the classes be relocated to the system.data.sqlclient
> namespace =) currently the move seems incomplete here, so my app will be
> as
> beta as the server ;)
> greetz
> al
> "Jens Smeyer" wrote:
>

CLR Performance

We had a CLR function that was running in seconds under the April CTP - now it takes over a minute in the new Sep CTP. We've determined that the bottleneck is in the following line:

Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")

Is there a new recommended way to open a connection?

That looks right to me, and I'd be very surprised if it was simply the SqlConnection constructor that was causing this (otherwise everyone would be hitting it).

Can you provide any more information?

Thanks,
-Isaac|||

My fault - it's not the constructor that's the overhead, it's the open command. My test function looks like the following:

<SqlFunction(dataaccess:=DataAccessKind.Read)> _
Public Shared Function PerfTest() As Integer
Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")
SqlConn.Open()
SqlConn.Close()
Return 0
End Function

If I call this function 15,000 times using Apr CTP, it runs in about 5 seconds. Using the latest, Sept CTP, it takes a minute or longer.

|||There was a bug introduced during this time (and fixed after the Sept. CTP) that might have some bearing. Check the memory allocations occuring in the CLR (SELECT single_pages_kb FROM sys.memory_clerks WHERE type = 'MEMORYCLERK_SQLCLR'). We had a problem where Open + Close of the context connection leaked memory, and required additional work to set up again. The equivalent to the connection pool not re-using connections, but with a bit less overhead to re-establish the connection.

One other question: are you seeing this perf difference for functions specificially, or does it also show up for procedures?

~Alazel

Developer
Microsoft Sql Server

This posting is provided "AS IS" with no warranties, and confers no rights.|||- Is there anyway I can obtain the fix to try out?
- I do not see the view "sys.memory_clerks" that you mention in your mail.
- The problem is apparent in the function since I call it over and over in a SELECT statement. I'm not sure about procedures.
|||- Sept CTP is the last SQL Server 2005 CTP. We would RTM soon and the fix would be available in it.
- Its actually sys.dm_os_memory_clerks
- Could you try creating a Stored Procedure with the same code and execute it same number of times as suggested by Alazel?

Thanks,
-Vineet.|||- The single_pages_kb increased from 1088 to 1096 when running the function.
- I created a stored procedure as you requested and I see the same performance problem - it runs in about the same time as the function.

I'm just curious if you have tried to run the PerfTest function (listed earlier in the posting) with the new SQL Server version to make sure the fix you mentioned has truly fixed the problem.
|||I tested running your function 15,000 times on a post-CTP16 build and it executed quickly (2 seconds, actually) as both a UD Function and a UD Stored Procedure.
Here is how I executed it to make sure we're doing an equivalent test:
declare @.i int, @.null int
set @.i = 1
while @.i < 15000
begin
select @.null = dbo.PerfTest()
set @.i = @.i + 1
end|||Yes, you're sample is pretty much what I did to test the UD stored procedure. I just think it would be in Microsoft's interest to send me a patch to test the fix in my environment. Let me know.

Client tools for 2005 Sept CTP

I haven't found a way to get the client tools for 2005 installed. After a full install of SQL Server 2005 Sept CTP, I don't have the Studio Management tool or whatever it is called. Can anyone help me here?

C-monkey,

We're experiencing the same issue here on a clean, new Win2K3 server with all the pre-req's. Getting the following message in the install log:

"Machine : xxxx
Product : Workstation Components, Books Online and Development Tools
Error : There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor.
--
Machine : xxxx
Product : Microsoft SQL Server 2005 Tools CTP
Product Version : 9.00.1314.06
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_xxxx_Tools.log
Error Number : 1603
-- "
The message in the tools log reads:

"Property(S): SOURCEDIR = E:\SQL Install\Tools\Setup\
Property(S): SourcedirProduct = {1DD463C0-A50A-4394-B7E4-5895C02F9E0D}
MSI (s) (94:58) [17:30:12:946]: Note: 1: 1729
MSI (s) (94:58) [17:30:12:946]: Product: Microsoft SQL Server 2005 Tools CTP -- Configuration failed."

Questions:
What is error number 1603 and Note: 1: 1729 ?
Could the SourcedirProduct = {1DD463C0-A50A-4394-B7E4-5895C02F9E0D} be duplicated somewhere on the server?

Any info appreciated!

|||Yeah, I did a clean install of Win2k3 as well. It really sucks but this problem, in addition to my other programmers having problems getting team system features running on their client, will cause me to format this server *again* and go back to Beta 2 bits. Wish I could wait a month and get the final releases.

Anyone know when those will show up on MSDN?
|||C-M,

No clue as to when the 'real' version will be available. Our MS rep says Nov. 7 and no earlier.

I tried to open a support call today and was told beta products don't have a live support team available until after the official roll-out. Was told I could send an email to support, but I can't seem to find anyplace that looks appropriate to send one.

Will post again if I get anywhere with this......

LMS|||Looks like Oct 14th will be an awesome day...

http://channel9.msdn.com/ShowPost.aspx?PostID=122324
|||I had a similar problem with the iso download. When I downloaded the "self-extracting" install package, *this* problem went away.