CLR user defined aggregate function (SS2005)

Hello,
In a user defined aggregate in Visual Studio 2005 with SQL Server 2005, I
declared a private variable in the struct and initialized in the Init "event
"
of the aggregate.
In the Terminate "event" it turned out that the variable was out of scope
(null).
My first thought was that this happened as a result of reads and writes of
intermediate results of the aggregate function, where (in my case) the
private variable was not explicitly saved.
But this idea is not consistent with the Merge "event" that also uses the
private variable and there it never is out of scope.
Who can explain?
Thx.examnotes <Axford@.discussions.microsoft.com> wrote in
news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:

> In a user defined aggregate in Visual Studio 2005 with SQL Server
> 2005, I declared a private variable in the struct and initialized in
> the Init "event" of the aggregate.
> In the Terminate "event" it turned out that the variable was out of
> scope (null).
> My first thought was that this happened as a result of reads and
> writes of intermediate results of the aggregate function, where (in my
> case) the private variable was not explicitly saved.
> But this idea is not consistent with the Merge "event" that also uses
> the private variable and there it never is out of scope.
How do you establish that the variable is not null in the Merge method?
In other words, are you certain that the Merge is being called. SQL will
not call Merge unless it creates a new instance of your aggregate and
runs that instance in parallell over a group in your accumulation.
Anyway, can you please post the code for your aggregate and we'll have a
look.
Also, it'd probably be better is you posted this in the sqlserver.clr
group which deals explicitly with SQLCLR issues. I will x-post this
reply to there.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Sorry it was the Accumulate event where the private variable was in scope. S
o
probably the Merge event will set it to null also. Can I enforce thecall to
Merge in some way?
I cannot find a group sqlserver.clr.
Thanks for the reply.
"Niels Berglund" wrote:

> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:
>
>
> How do you establish that the variable is not null in the Merge method?
> In other words, are you certain that the Merge is being called. SQL will
> not call Merge unless it creates a new instance of your aggregate and
> runs that instance in parallell over a group in your accumulation.
> Anyway, can you please post the code for your aggregate and we'll have a
> look.
> Also, it'd probably be better is you posted this in the sqlserver.clr
> group which deals explicitly with SQLCLR issues. I will x-post this
> reply to there.
>
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||examnotes <Axford@.discussions.microsoft.com> wrote in
news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:

> Sorry it was the Accumulate event where the private variable was in
> scope. So probably the Merge event will set it to null also. Can I
> enforce thecall to Merge in some way?
OK, so in Accumulate the variable is OK, but in Terminate it is null -
is that correct? If so, please post the code for your UDA. AFAIK, you
can not force a call for Merge.

> I cannot find a group sqlserver.clr.
Do a refresh of your newsgroup list - that should make it available to
you (microsoft.public.sqlserver.clr).
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Here you go with the code (it is from Microsoft samples):
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,MaxByteSize=8000, IsInvariantToNulls=true,
IsInvariantToDuplicates = false, IsInvariantToOrder=true,
IsNullIfEmpty=true)]
public struct Concatenate : IBinarySerialize
{
private string delimeter;
private StringBuilder intermediateResult;
public void Init()
{
delimeter = "-";
intermediateResult = new StringBuilder();
}
public void Accumulate(SqlString value)
{
if (value.IsNull)
return;
intermediateResult.Append(value.Value).Append(delimeter);
}
public void Merge(Concatenate other)
{
intermediateResult.Append(other.intermediateResult).Append(delimeter);
}
public SqlString Terminate()
{
string output = string.Empty;
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0,
this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
#region IBinarySerialize Members
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
intermediateResult = new StringBuilder(r.ToString());
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(intermediateResult.ToString());
}
#endregion
}
"Niels Berglund" wrote:

> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:
>
> OK, so in Accumulate the variable is OK, but in Terminate it is null -
> is that correct? If so, please post the code for your UDA. AFAIK, you
> can not force a call for Merge.
>
> Do a refresh of your newsgroup list - that should make it available to
> you (microsoft.public.sqlserver.clr).
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

CLR Question (BASIC)

I have Visual Studio .Net 2003...I am assuming that since it can't find
System.Data.SQLServer that I need Visual Studio 2005 to develop CLR apps for
SQL?Hello RSH,

> I have Visual Studio .Net 2003...I am assuming that since it can't
> find System.Data.SQLServer that I need Visual Studio 2005 to develop
> CLR apps for SQL?
Correct
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||There's no System.Data.SQLServer anymore. You use the regular connection obj
ects, reader etc. For
SQLCLR specific things (like context and pipe), you use the Microsoft.SqlSer
ver.Server namespace.
You do need Framework 2.0 (as the things you need are in the System.Data.dll
file that comes with
Framework 2.0). You can download Framework 2.0 and write your CLR objects us
ing Notepad and
command-line compile them. If you want to use VS to write SQLCLR objects you
need VS 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RSH" <way_beyond_oops@.yahoo.com> wrote in message news:ejDCfJ29FHA.160@.TK2MSFTNGP12.phx.gb
l...
>I have Visual Studio .Net 2003...I am assuming that since it can't find Sys
tem.Data.SQLServer that
>I need Visual Studio 2005 to develop CLR apps for SQL?
>|||You don't really need Visual Studio at all. You can download the .NET 2.0
SDK and use the command-line tools. But if you want the IDE experience,
yes, VS2005 is necessary.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:ejDCfJ29FHA.160@.TK2MSFTNGP12.phx.gbl...
>I have Visual Studio .Net 2003...I am assuming that since it can't find
>System.Data.SQLServer that I need Visual Studio 2005 to develop CLR apps
>for SQL?
>

CLR Deployment

I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.

I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...

I keep receiving the following error when attempting to deploy to SQL Server:

Error 1 Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

After some experimentation I have determined that its because the DLL is too
large: 1.8 mb. (I also have a larger one).

I have looked through various documentation sources but have found none
concerning this problem other than specifing something aboug a 100ms
execution timeout (not sure if this is the same).

Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)

Is there anyway to deploy large DLLs to SQL Server without having to break
them up? If so, where is the setting to allow a longer timeout period for
deployment?

You could create script manualy. Just use CREATE ASSEMBLY/CREATE PROCEDURE and other statements.|||

Thanks for the reply.

Yes I know I could do that, but it is so much easier to use VS.

Also the major time consuming problem is the number of tables and procedures.

Heres the scenario.

We have 2 databases, 1 flat, 1 relational. (Same problem is occuring on both).

The flat database has been imported from FoxPro 2.6 DBF's for conversion into the relational to supply historic data to an application upgrade. Needless to say there are a large number of tables. I have utilized ApexSQL to generate CLR Stored Procedues for each table. (i.e., INSERT,UPDATE,DELETE,SELECT, and so on...)

VS Compiles the source with no errors, it is just during deployment to SQL Server. As a work around for now I have split the .CS files into somewhat logical groups and created a seperate VS solution for each, all of these deploy with no problems.

For documentation and maintenance I would rather keep everything together in one VS Solution for each Database.

Again thanks for your reply.

Glenn

CLR Deployment

I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.

I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...

I keep receiving the following error when attempting to deploy to SQL Server:

Error 1 Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

After some experimentation I have determined that its because the DLL is too
large: 1.8 mb. (I also have a larger one).

I have looked through various documentation sources but have found none
concerning this problem other than specifing something aboug a 100ms
execution timeout (not sure if this is the same).

Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)

Is there anyway to deploy large DLLs to SQL Server without having to break
them up? If so, where is the setting to allow a longer timeout period for
deployment?

You could create script manualy. Just use CREATE ASSEMBLY/CREATE PROCEDURE and other statements.|||

Thanks for the reply.

Yes I know I could do that, but it is so much easier to use VS.

Also the major time consuming problem is the number of tables and procedures.

Heres the scenario.

We have 2 databases, 1 flat, 1 relational. (Same problem is occuring on both).

The flat database has been imported from FoxPro 2.6 DBF's for conversion into the relational to supply historic data to an application upgrade. Needless to say there are a large number of tables. I have utilized ApexSQL to generate CLR Stored Procedues for each table. (i.e., INSERT,UPDATE,DELETE,SELECT, and so on...)

VS Compiles the source with no errors, it is just during deployment to SQL Server. As a work around for now I have split the .CS files into somewhat logical groups and created a seperate VS solution for each, all of these deploy with no problems.

For documentation and maintenance I would rather keep everything together in one VS Solution for each Database.

Again thanks for your reply.

Glenn

CLR Assembly redeploy problem.

I have created a C# library containing CLR stored procedures and user-
define functions using Visual Studio 2005, and I have used Visual
Studio to deploy the assembly to a SQL Server 2005 instance
successfully, of course these sps and udfs are used by other T-SQL sps
and udfs. Now the assembly has a new version, and I want to use it to
replace the original one, but when I use Visual Studio 2005 to deploy
the new assembly, the following error occurs:

Error1Cannot drop the function 'TranslationStringLike', because it
does not exist or you do not have permission.
Cannot drop the function 'TranslateEngString', because it does not
exist or you do not have permission.
Cannot drop the function 'TranslateEngStringReverse', because it does
not exist or you do not have permission.
DROP ASSEMBLY failed because 'FirmBankCLR' is referenced by object
'TranslationStringLike'.FirmBankCLR

That is the assembly is dependent by other objects, it can't be
dropped until it is not dependent by other objects. How can I resolve
this problem using Visual Studio 2005 or something else?Amber (guxiaobo1982@.gmail.com) writes:

Quote:

Originally Posted by

I have created a C# library containing CLR stored procedures and user-
define functions using Visual Studio 2005, and I have used Visual
Studio to deploy the assembly to a SQL Server 2005 instance
successfully, of course these sps and udfs are used by other T-SQL sps
and udfs. Now the assembly has a new version, and I want to use it to
replace the original one, but when I use Visual Studio 2005 to deploy
the new assembly, the following error occurs:
>
Error 1 Cannot drop the function 'TranslationStringLike', because
it does not exist or you do not have permission. Cannot drop the
function 'TranslateEngString', because it does not exist or you do not
have permission. Cannot drop the function 'TranslateEngStringReverse',
because it does not exist or you do not have permission. DROP ASSEMBLY
failed because 'FirmBankCLR' is referenced by object
'TranslationStringLike'. FirmBankCLR
>
That is the assembly is dependent by other objects, it can't be
dropped until it is not dependent by other objects. How can I resolve
this problem using Visual Studio 2005 or something else?

As long as the interface of the assembly does not change, you can just
do ALTER ASSEMBLY. Either specify directly, which is simple if the DLL
is accessible from SQL Server. Or specify the DLL as a hexstring directly
in the ALTER ASSEMBLY statement.

If the interface has changed, you will need to drop all functions
created from it, as well as dependent assemblies.

I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
of Visual Studio. I don't use Visual Studio to work with assemblies
but stick to the command line. And all impression I get is that
using Visual Studio just makes things harder.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
of Visual Studio. I don't use Visual Studio to work with assemblies
but stick to the command line. And all impression I get is that
using Visual Studio just makes things harder.

It seems Microsoft hasn't done the job well, but if we have a lot of
objects in the assembly, the manual process of dropping and creating
them is horrible.|||Amber (guxiaobo1982@.gmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
>of Visual Studio. I don't use Visual Studio to work with assemblies
>but stick to the command line. And all impression I get is that
>using Visual Studio just makes things harder.

>
It seems Microsoft hasn't done the job well, but if we have a lot of
objects in the assembly, the manual process of dropping and creating
them is horrible.

Yes, it's horrible if you have to do it each time you change the
implementation of some single method, and it's amazing that VS cannot
handle this situation.

If you change the interface, it's still a lot of work of course of dropping
and recreating objects. But it is or more less inevitable.

I don't know what's in your assemblies, but I woudl suggest that if you
have a suite of functions and procedures that are independent of each other,
that it's best to have a separate assembly for each function/procedure. Of
course, if they use a lot of common code, you still need to put that common
code in a single assembly in which case you are back to the same situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Close existing connection before deleting/restore database

I want to be able to force a restore or a delete on a database even if there still have some active connections. In SQL Server management studio there is a checkbox when you're trying to delete a database to "Close existing connection" i want to do the same thing but using SMO. How can i do this ?

ThankHi,

see my blog entry here:

http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

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.

Client tool Installation

How can I install client tool on my PC for SQL Server Mgmt Studio 2005?

Client Tools are called "Workstation Components" and are the last of the options, after the servers, on the setup feature selection.|||

Is "Workstation components" load on a Windows server or xp machine? It looks xp machine does not satisfy the requirement for setup program.

Client Agent Error: integrity violation

Hello:

I tried to do the merge replication between SQL 2000 database and the SQL mobile server on PDA with SQL server management studio from SQL 2005 and I have already successfully synchronized my PDA with one small SQL server database file. However when I tried to synchronized my PDA with another larger SQL server database file, I got the error on PDA as following: “The row operation cannot be reapplied due to an integrity violation. Check the publication filter. [Table = AuditCriterion, operation = Insert, RowGuid = {1ee9321d-f00d-410c-8d5b-08d4220d2627}]”. I have keep checking the size of sdf file during synchronization, I found after the size of the sdf file stop increasing for about 20 mintues, then I got the error above. Morever, AuditCriterion table have a foreign key with another table AuditElement and I have not used publication filter at this stage.

Please help thanks.

Eddie

If AuditCriterion has as a foreign key with AuditElement have you included both tables in the publication?

If the table is missing the insert will fail because SQL Server 2000 cannot find the required primary key.

Thanks

Nabila Lacey

ClickOnce for SQL Express Database?

I'm working on an app in Visual Studio 2005 that needs to have installed locally SQL Express to handle its own data.

This app is in a strong upgrading process. I've been using clickonce to publish the app and let my users upgrade automatically (by the way this works amazing ...).

Yet many times changes to the DB are made. Is there a simple way to upgrade their local SQL Express DB like click once does with the app?

Thanks.

hi,

I'm not sure about click once capability to perform the requested task, but even if possible, are you sure you like to do it? that means you would upgrade users database loosing their data modifications... I do think you've better just "upgrade" the database schema and not replace their database...

if this is the case, you can perhaps have a look at syncornization tools like http://www.red-gate.com/products/SQL_Compare/index.htm..

regards

|||

Thanks, the products looks like a very good solution. I've already downloaded it and I'm testing.

Have you used it yourself? Do you know other products that do the same?

|||

hi,

yes, I do use it myself since versions 2...

I know other similar tools are available out there, like http://www.innovartis.co.uk/database_dbghost_home.aspx, but I've always been satisfied by red-gate tools.. they even provides http://www.red-gate.com/products/SQL_Packager/index.htm, base on SQL Compare tool, to just distribute an exe packaged file that, at run time, will perform the metadata updates for you..

but here we are in advertising mode

BTW, I'm not involved in any way with the reported products/company, I'm just a user as you could be..

for further info, a free comparison tool is available at http://www.absistemi.it/sqlCompare.aspx by another italian SQL Server MVP fellow, but I'm not sure it has been updated for SQL Server 2005...

regards

|||Grazie tante ...|||

prego...

(you're welcome)

Clearing the Error List window

This is probably a 'silly' question, but I am rather new to Visual Studio... But is there a way to clear the Error List window?

Thanks,
Hans Geurtsen.Well... get rid of the errors in the code and rebuild the project