Cluster and User Defined Functions

Has anyone had difficulty with UDF's in a clustered environment? We have
some DTS code that references a user defined function. When we run the DTS
package on non-clustered servers, the DTS package executes in several
minutes. However, when we run the same code against the database in a
clustered environment, the package never finishes. Watching the proc
monitor shows fairly decent server activity, but just never returns.
Anyone?
Thanks
Rob Heyman
I'm not sure what the UDF could be doing that will only cause problems in a
cluster so maybe that is not the real issue. Have you tried tracing the
execution of the package under Profiler to see where the package execution
gets "stuck"? You should also check for blocking when the package is
running. Please see the following article for more info:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

CLR Table valued functions error

Hi,

I'm trying to create a CLR functions

this is the Sql Function attribute and the FillRowMethod signature

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read,

FillRowMethodName = "FillRows",IsPrecise=true,

TableDefinition = "SOCIETA nvarchar(55),CLIENTE nvarchar(150),NUMEROCONTRATTO nvarchar(255),FIRMA datetime,CHIUSURA datetime,AUTORIZZATO float"

)]

publicstaticIEnumerable dbf_Create_RiepilogoAccordi(SqlInt32 commessa, SqlInt32 tipo_commessa, SqlInt32 progetto, SqlInt32 DAC, SqlInt32 figura, SqlDateTime dataFatturazioneDa, SqlDateTime dataFatturazioneA)

publicstaticvoid FillRows(Object obj, outSqlString SOCIETA, outSqlString CLIENTE, outSqlString NUMEROCONTRATTO, outSqlDateTime FIRMA, outSqlDateTime CHIUSURA, SqlDouble AUTORIZZATO)

Whe I try to deploy my function, I get the following error:

Error 1 Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function 'dbf_Create_RiepilogoAccordi' due to column 6. CM.Reports.SIA.RiepilogoAccordi

I get this error whichever combination of name/value I use for column 6

Can someone help me?

Thanks

Marco

I forgot to mark parameter AUTORIZZATO as an out parameter. the signature should be

publicstaticvoid FillRows(Object obj, outSqlString SOCIETA, outSqlString CLIENTE, outSqlString NUMEROCONTRATTO, outSqlDateTime FIRMA, outSqlDateTime CHIUSURA, out SqlDouble AUTORIZZATO)

CLR programming: best place to define utilities functions

Hallo,

I find the CLR integration very handy and I would like to use it in the proper way but I need some helps because is not easy to find "best practice" programming info.

This is what I have to do: from a "period" coming from the DB I have to compute start/End date and save them in the DB.

The computation involves regular expression and several functions that makes several calculations on date type variable.

I have created my UserDefinedFunction class that contains the TV function to return a row with 2 fields (startdate,enddate).
Now my question is: what is the base place to define my utilities functions that perform the computation?
In the same class or it is better to add a new class?

For example I have created a module that contains all the Regular expressions patterns.

Any help is appreciated!

Thankx

Marina B.

I think that this is no matter of a SSQL Server decision rather than a cidong decision. It is always good to have a namespace schema and separated classes which allow you to reuse your your. I would split the regular expressions in you case out of the datetime classes if you want to use them in other projects as well. If this is an internal regular expression calculation you should use them in the same class (and make them evtl. internal if you don′t want to call them directly from SQL Server).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

clr integration....

sir here are the functions on my test application...

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function WeekInYear(ByVal dt As DateTime) As Integer

Return DatePart("ww", dt, FirstDayOfWeek.Monday, _

FirstWeekOfYear.FirstFourDays)

End Function

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function HttpPost(ByVal uri As String, ByVal parameters As String) As String

Try

Dim urlRegEx As New System.Text.RegularExpressions.Regex("http://192.168.1.23:81/.*")

Dim p As New System.Net.WebPermission(System.Net.NetworkAccess.Connect, urlRegEx)

p.Assert()

Dim req As System.Net.WebRequest = System.Net.WebRequest.Create(uri)

req.ContentType = "application/x-www-form-urlencoded;charset=utf-8"

req.Method = "POST"

Try

Dim bytes() As Byte = System.Text.Encoding.UTF8.GetBytes(parameters)

req.ContentLength = bytes.Length

Dim os As System.IO.Stream = req.GetRequestStream

os.Write(bytes, 0, bytes.Length)

'Push it out there

os.Close()

Catch ex As Exception

Throw New Exception(ex.Message)

End Try

Dim resp As System.Net.WebResponse = req.GetResponse

If resp Is Nothing Then

Return Nothing

End If

Return "pass completed"

Catch ex As Exception

Return ex.Message

Finally

End Try

End Function

the first function works fine, the second function doesn't work, it seems that if i ever use another namespace it requires a security permission.

can you specify why do i recieved this error at runtime?

Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

You have to give the assembly the appropiate permissions in SQL Server. By default only a few assemblies / namespaces are *trusted*. Try to give more access to the assembly by registering the assenblies as UNSAFE or external_Access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

As Jens says, the most likely issue is that you haven't created the assembly with a permission set that allows you to do web "stuff". For that to work you need to create the assembly with a permission set of EXTERNAL_ACCESS.

Niels

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