An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

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.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

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.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

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.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An attempt to attach an auto-named database for file C:\... failed

Hi,

I recently installed VS Web Developer Express Ediition and SQL Server Express and I'm doing the How To tutorials and I can't finish the basic data access walkthrough:

http://msdn2.microsoft.com/en-US/library/tw738475.aspx

Because I get an error like this (link for complete error below):

An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

http://pastecode.com/6196

When I am setting up the data source for the gridview tool, everything works fine and the test query works fine, but then when I try to actually test the output in the browser with Cntrl+F5 I get this error.

I have been trying for 48 hours to find a solution on my own. I have googled, I have searched everything and I just am at a point where I think I need to ask someone for help.

Please help.

Hi,

I guess you want to attach a user instance to SQL Server. But the path specified for the user instance is a server attached instance, which makes you able to view the data through the server explorer but won′t make you able to use the run the code, because at startup VS will try to create a user instance at the location specified (which is already occupied by the server instance database) and which will fail. The solution is to specify another folder to create the suer attached instance during the build of the code, thats all. That should make you able to run the build process as well as the code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Try this in the config file ...

Data Source=.\SQLEXPRESS;AttachDBFilename=C:\...\db.mdf;User Instance=true

An attempt to attach an auto-named database for file C:\... failed

Hi,

I recently installed VS Web Developer Express Ediition and SQL Server Express and I'm doing the How To tutorials and I can't finish the basic data access walkthrough:

http://msdn2.microsoft.com/en-US/library/tw738475.aspx

Because I get an error like this (link for complete error below):

An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

http://pastecode.com/6196

When I am setting up the data source for the gridview tool, everything works fine and the test query works fine, but then when I try to actually test the output in the browser with Cntrl+F5 I get this error.

I have been trying for 48 hours to find a solution on my own. I have googled, I have searched everything and I just am at a point where I think I need to ask someone for help.

Please help.

Hi,

I guess you want to attach a user instance to SQL Server. But the path specified for the user instance is a server attached instance, which makes you able to view the data through the server explorer but won′t make you able to use the run the code, because at startup VS will try to create a user instance at the location specified (which is already occupied by the server instance database) and which will fail. The solution is to specify another folder to create the suer attached instance during the build of the code, thats all. That should make you able to run the build process as well as the code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Try this in the config file ...

Data Source=.\SQLEXPRESS;AttachDBFilename=C:\...\db.mdf;User Instance=true

Am I right in using BI Studio to run Reporting Services ?

Hi,

This is a basic question but I'd be grateful for any suggestions because I just can't get started.

I installed Reporting Services from a CD-ROM.

When I go to all Programs, I find an entry for BI Development Studio but when I run it, it describes itself as Visual Studio on the top left.

I can create basic Reports OK, but when I try to create Charts, following Brian Larson's book, I find myself looking for things that sound basic, but I can't find them. An example is Report Designer, whereas I have a Report Wizard.

Then I can find a 'Chart' under Report Items, but I can't find out what to do with it - I tried dragging-and-dropping it, double-clicking it and so on, but nothing worked.

I am sure I am doing something basic wrong but I can't figure out what it is.

I'd appreciate any advice !

Barry

.

The BI Studio has full support of all RS features. You may need to re-read the steps |||

Thanks for your advice and for responding so quickly.

I was impressed by your web site so I'll take your advice seriously !!! ;)

I was trying to skip the basics using my previous knowledge (which turned out not to be so portable after all) so I guess I'll start again at page 1.

Barry

am i in the right place?

I would like to find a newsgroup where I could get some basic querying help.
Is this the right place, or can you reccomend another group?
Thanks,
Dana WannerThis is a multi-part message in MIME format.
--=_NextPart_000_0183_01C35B57.3F9E30C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Actually, microsoft.public.sqlserver.programming is the place for query =help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dana Wanner" <dwanner@.westportofficefurniture.com> wrote in message =news:eOFXsg3WDHA.652@.TK2MSFTNGP10.phx.gbl...
I would like to find a newsgroup where I could get some basic querying =help.
Is this the right place, or can you reccomend another group?
Thanks,
Dana Wanner
--=_NextPart_000_0183_01C35B57.3F9E30C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Actually, =microsoft.public.sqlserver.programming is the place for query help.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Dana Wanner" wrote in message news:eOFXsg3WDHA.652@.T=K2MSFTNGP10.phx.gbl...I would like to find a newsgroup where I could get some basic querying =help.Is this the right place, or can you reccomend another group?Thanks,Dana Wanner

---

--=_NextPart_000_0183_01C35B57.3F9E30C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0027_01C35B4E.5C99DCF0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23vkaej3WDHA.2312@.TK2MSFTNGP10.phx.gbl...
Actually, microsoft.public.sqlserver.programming is the place for =query help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dana Wanner" <dwanner@.westportofficefurniture.com> wrote in message =news:eOFXsg3WDHA.652@.TK2MSFTNGP10.phx.gbl...
I would like to find a newsgroup where I could get some basic querying =help.
Is this the right place, or can you reccomend another group?
Thanks,
Dana Wanner
--=_NextPart_000_0027_01C35B4E.5C99DCF0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Tom!
"Tom Moreau" = wrote in message news:%23vkaej3WDHA.=2312@.TK2MSFTNGP10.phx.gbl...
Actually, =microsoft.public.sqlserver.programming is the place for query help.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Dana Wanner" wrote in message news:eOFXsg3WDHA.652@.T=K2MSFTNGP10.phx.gbl...I would like to find a newsgroup where I could get some basic querying help.Is this the right place, or can you reccomend another group?Thanks,Dana =Wanner

---

--=_NextPart_000_0027_01C35B4E.5C99DCF0--

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.