I'm having a problem executing a SQLCLR function: this function
calls a web services that processes a query to a data base and
returns a table to be used in a stored procedure. In a low
concurrency scenario (not to many clients connected), the function
returns correctly, however when the concurrency level is increased
we have a SQLCLR command execution problem (all the SQLCLR
processes hangs), making the server unavailable to all web services
processes.
At first we thought the problem could be the SQLCLR, since the web
services is 100% available, all the time. We monitored to come to
this conclusion. Do you know of some SQLCLR bug?
Could someone help me with this? I'm in a difficult situation with
my client, considering that we defended the MS SQLServer technology
and now it's not working properly.
I am trying to find a solution to my woes creating CLR activation stored procedure that references Enterprise library data access dlls. Does anyone know best practices to deploy .NET 2.0 assemblies as SQLCLR. I am using VS2005 SqlClrProject to deploy my .NET 2.0 assemblies to Sql Server and it fails with error such as "Assembly was not found in the sql catalog". If I manually try to load a referenced assembly within SqlServer by browsing to the target location, like for example, System.Management.dll it fails with similar error, trying to resolve its dependencies. Even if I manage to walk through the dependency tree and resolve it to the final dependent dll, I am only able to load it in "UNSAFE" mode. Any recommendations, suggestions and feedback are welcome.
I'll keep trying new threads here... sooner or later, I'm sure an expert Microsoft CLR employee will gladly lend a helping hand!
The pieces:
1. SQL 2005, MS Windows Server 2003, Standard Edition, SP 1
2. .NET 2005/C#
3. Instance of SQL 2005 running locally.
Trigger on local SQL2005 DB table INSERT calls 2 CLR Functions:
1. Retrieve data from SQL2005 DB table and populate local DBF through OLEDB
2. Call external 16-bit application (written in Clipper) that iterates through local DBF records (added from step 1 above) and populates DBF on domain resource.
Step 2 detail:
External 16-bit application is called by CreateProcessAsUser after impersonating token.
THIS IS SUCCESSFUL - IF: I populate SQL2005 DB table using TSQL insert statement. The trigger executes, Step 1 and Step 2 execute perfectly!
THIS IS UNSUCCESSFUL - IF: The SQL2005 DB table is populated by synchronizing SQL Mobile Server database from a SQL Mobile Edition 2005 PDA emulation. The trigger executes. Step 1 executes perfectly. Step 2 executes without exceptions. HOWEVER, the 16-bit application does not execute! Remember, no exceptions. In fact, the result variable returns true from function below:
result = ProcessUtility.CreateProcessAsUser(
hDupedToken,
null,
@"C:MobileDBMobile.exe",
ref sa, ref sa,
false, 0, IntPtr.Zero,
@"C:MobileDB", ref si, ref pi
);
Also, if I Right-click on the Step 2 function in the Server Explorer and click "Execute", it works perfectly. Domain DBF is updated successfully.
So, in short CLR "CreateProcessAsUser" is not doing it's job when the trigger is fired after SQL 2005 DB is populated via replication/sychronization. I would appreciate a response... Thx!
My .NET SQL UDF needs do very complex computation on every call regardless on user input. I would be very happy if I could cache this computed data somewhere in SQL Server memory. And then I should not need to recompute this complex information on every UDF call. Is it possible to cache something inside SQL Server from CLR ?
I'm personally in favor of using the SQL CLR where appropriate, although I'm wondering what the negative consequences of enabling SQL CLR might be? Its disabled by default within SQL Server 2005 and most likely 2008, so what was the reason behind this ... beyond the fear of the DBA enabling something he might not himself fully understand.
By using impersonation I am able to push data to network path / old fashioned DBF's. Of course, I must use unsafe. I am unable, however to successfully run a "cmd.exe" process(), even if I populate the Username, Domain and password (with securestring). It authenticates correctly (checked by giving wrong password or bad username). But when it runs (even just a "cmd.exe"), I get an access is denied error...
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in "secret")
password.AppendChar(c);
Process p = new Process();
ProcessStartInfo si = new ProcessStartInfo("cmd.exe");
si.UserName = "MyName";
si.Password = password;
si.Domain = "MySecretDomain";
si.UseShellExecute = false;
try
{
p.StartInfo = si;
p.Start();
}
catch (Exception ex)
{
//handle the exception here (This exception handler will not handle the exception, but I get a
//Window popup (While executing my CLR!!!)
}
finally
{
sw.Close();
}
The message popup says: The application failed to initialize poperly (0xc0000142). Click on OK to terminate the application. I'm kind of at a loss...
I'm using a Windows 2003 server box with the latest SQL Server 2005, .NET 2.0/2005. Let me know if you need anything else.
Oh, just FYI - I am moving replicated data from SQL2005 server to a legacy app using DBF (FoxPro driver). I really need an external DOS app to process some data for the DOS application (Clipper).
I am searching for a way of using SQLCLR to do Bulk Insert/Copy within SQL Server 2005. I am not permit to use SQL command BULK INSERT with any of text based file csv, or xml etc. I did tried to use SqlBulkCopy within SQLCLR but failed, the context connection was not allowed, I did also use normal connection string with sa & pwd but no luck.
I understand that I can move bulk insert to a service such as windows, web or WCF but it is not on the card at present. Is there a way of doing this within CLR, size of bulk is fair about 2000+ rows.
If you have solution, sample or link would be appriciated.
I have an SQLCLR assembly which is required to connect to a remote WCF service. In order to do this in a host such as IIS you would need to store all your WCF configuration data (endpoints, types etc) in the Web.config or App.config (in a windows forms app). This begs the question: Where do you store configuration data for SQLCLR assemblies?
The System.Configuration assembly is available in the SQLCLR, but this assumes a Web or App config files exists? Does SQL have its own config file that you can keep your settings in which is called when the assembly is running from within the SQL process?
If this is not possible, should I rather be storing configuration data in the database itself?
This particular example relates to WCF configuration but is relevant for assemblies using Enterprise Library which is also config driven.
Just wondering which scenarios is suitable to use SQLCLR. Any kind of data access is not recommended I guess. Only things that cannot be easily done in TSQL should be done in SQLCLR but why? Can't those things be done in app layer itself? Scenarios recommended for SQL CLR: - External data access like filesystem, registry etc - Complex calculation - Recursion without data access (this can be implemented with CTE for data access)
If data access with SQL CLR is not recommended why should CLR should be even used and logic reside in database layer.. it makes no sense to me. Any thoughts??
I have just begun to delve into the SQLCLR objects in SQL Server 2005. I have created a simple VB function as a SQLFunction. Here's the code:
Imports System
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function FileDate(ByVal FilePath As String) As Date
Return FileDateTime(FilePath)
End Function
End Class
The function is run with this T-SQL:
declare @FileDate datetime
select @FileDate = dbo.FileDate('C:setup.bat)
select @FileDate
This works fine, but I had to set the Permission Level of the Visual Studio project to "Unsafe" rather than "External" in order to avoid the following error message:
A .NET Framework error occurred during execution of user defined routine or aggregate 'FileDate':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Is there any way to avoid having to set the permission level to "Unsafe"? If not, what does "unsafe" mean in terms of overall system security? Is it acceptable to use on an internal LAN?
I had DTS the Northwind sample database from SqlServer 2000 to 2005. It's went ok and no errors. Then, I created a SP named upGetCustomer, bascially it queries the Customers table and list some of it's fields and order by CustomerId,CustomerName, Country decrementally. SP is so simple and has no errors.
Then, I created a SqlServer project in VS2005 using C#. Add store procedure class as below, using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void Customers(string customerid) { using (SqlConnection sqlConn = new SqlConnection("context connection=true")) { sqlConn.Open(); SqlPipe sqlPipe = SqlContext.Pipe; SqlParameter param = new SqlParameter("@custId", SqlDbType.VarChar, 5); param.Value = customerid; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "upGetCustomer"; sqlCmd.Parameters.Add(param); SqlDataReader rdr = sqlCmd.ExecuteReader(); SqlContext.Pipe.Send(rdr); } }
I had taken method of created SQLCLR from source in Microsoft website, but coding is mine, and hopes it is correct. I used SqlConnection string as 'context connection=true' which I still not quite sure what does it means, hopes it mean current connection I am using on my Windows authentication.
The project did compiled & deployed OK on the VS2005 side.
The problem is that when I tried to locate the assembly on the Sql Server 2005, but can't find it anywhere on Sql Server.
I did try complied and deloyed again, it keeps saying there is an error in deployment as customers assembly is already exist on the database. I then, tried to remove this assembly on the database using SQL script, drop assembly customers in the Northwind database but got error saying it does not exist. So where is it???
I keep getting different answers from different people on regarding if you can or cannot kill the hosting sql server process with an unsafe assembly. Can you do this? If so could you please attach a sample demonstrating this?
Throughout the course of this book and even before it I have come across conflicting information regarding how SQLCLR attempts to resolve system/CLR assembly references. For example, prior to my latest read thourgh April BOL 2005, I thought SQLCLR attempted to resolve these references implicity for you via the local machine's GAC. Yet here is what I found while trying to help another person in this forum yesterday in BOL... Assembly Validation SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:
The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.
The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.
For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:
The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.
The assembly should not contain any static data members in its classes unless they are marked as read-only.
The classes in the assembly cannot contain finalizer methods.
The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.
Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:
Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.
For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.
COULD SOMEONE PLEASE GIVE ME THE DEFENITE ANSWER ON HOW THIS WORKS!
Hi, I've got a requirement for a procedure to write a text file and want to do it without using command line utilities.
I wrote a sqlclr which queries a db and writes the file that I need. After setting the external_access and database to trustworthy, It only works on my own machine, not on network shares.
Some threads I've found have been related to the "1 network hop" of Windows credentials, which most of us are probably familiar with.
It doesn't make sense to me that this is the problem, since I'm on my workstation, connecting to a local db instance and hopping one time to the shared server. Anyone know what the cause might be?
Code Block System.UnauthorizedAccessException: Access to the path '\isrc02Users3sgreene estwrite.txt' 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) at System.IO.StreamWriter.CreateFile(String path, Boolean append) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize) at System.IO.StreamWriter..ctor(String path) at StoredProcedures.Inovah_JV(String& greeting)
I am trying to generate a SQL CLR stored procedure and i have done it too. Now the next step is to convert that *.dll file in to assembly. I am getting erro that. My server Database is on the LAN, not on my PC.
Here is the code which i have tried to register an assembly...
/* CREATE ASSEMBLY MyAssembly FROM 'C:Documents and SettingsAdministrator.ORC80My DocumentsVisual Studio 2005ProjectsMyDB1MyDB1inDebugMyDB1.dll'
WITH PERMISSION_SET=SAFE
GO */
and here is the error that i am getting...
/* Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation. */
The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:
If(ClassName.privateStaticDelegate == null) ClassName.privateStaticDelegate = new MyDelegate(HiddenMethodName); CallAnonymousMethod(ClassName.privateStaticDelegate);
Is there any workaround to fix this problem.
P.S. I googled about this problem and found only one article on it: http://www.ayende.com/Blog/default,date,2005-12-26.aspx
I know the rule of thumb is to use T-SQL when manipulating data and to use SQLCLR for conditionals, looping, etc. My question is how much slower (percentage, factor of, anything!) is SQLCLR for doing SELECT, INSERT, and UPDATE commands?
Is the performance difference *that* much greater that the simplicity of SQLCLR doesn't apply?
CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.
What I want to do here is enable/create a "MODIFY" option on CLR-based objects in Object Explorer.
Option is only supported for assemblies who's source code has been previously loaded into DB via ALTER ASSEMBLY Upon electing the option, source code gets displayed in editor. Users alters managed source User clicks the "Execute" button in the editor onClick of "Execute" performs following
Alters managed source (not sure if this can be done in the system tables, may have to save .cs/.vb files out to file system first then reupload) Builds updated source/.dll is built Executes an ALTER ASSEMBLY statement to "refresh" the dll and it's associated source code file(s)
I have a sqlclr stored procedure that works well if started via execute in SSMS. (The stored procedures makes a SSRS web service call.) When I install a trigger on a database table to execute the stored procedure
create trigger NewWdsStatsEntry on dbo.WDSSTATS after insert as execute UpdateReportExecutionSnapshot N'http://localhost/ReportServer/reportservice2005.asmx', N'/Report1'; go
and add something to the table, I get the following error:
Msg 6522, Level 16, State 1, Procedure UpdateReportExecutionSnapshot, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "UpdateReportExecutionSnapshot": System.Net.WebException: The request was aborted: The operation has timed out. System.Net.WebException: at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at UserProcedures.ReportService2005.ReportingService2005.UpdateReportExecutionSnapshot(String Report) at StoredProcedures.UpdateReportExecutionSnapshot(String url, String reportPath) . The statement has been terminated.
The insert times out after 100 seconds and no row has been added to the table, but the web service call actually created a new report! Any idea why the trigger can't execute the stored procedure? Thanks!
I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.
The following code:
Process newProcess = new Process();
string path = @"C:TEST.BAT";
newProcess.StartInfo.FileName = path;
newProcess.Start();
Executes without error, but does not actually run the external.
Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.
I expected Optional ByRef nArg as Integer=123 to setup a sp arg with a default value of 123. Doesn't look like it does. Anybody know what I'm doing wrong?
Also, I can't write a function with a return using AS System.Int32. There is no RETURN sp arg.
I have some code in a SQL CLR stored procedure that calls out to a web service at UPS to obtain tracking information for a package.
The code fails on the last line, in the call to WebRequest.GetRequestStream(), with the following exception:
"System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) --- End of inner exception stack trace --- at System.Net.HttpWebRequest.GetRequestStream() at UPSTracking.TrackShipment(String TrackingUri)"
This same code works just fine when not running in the SQL CLR assembly. The assembly is marked as External, but I've also tried marking it as Unsafe, which did not work either.
Any suggestions on what the problem might be or how to troubleshoot this further would be greatly appreciated.
Msg 6532, Level 16, State 49, Procedure clrsp_RetailPriceOnDate, Line 0 .NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being aborted. System.Threading.ThreadAbortException: at Data_SQLCLR.StoredProcedures.clrsp_RetailPriceOnDate(SqlInt32 iSQLsysid_Individual, SqlInt32 iSQLStore, SqlInt32 iSQLGroup, DateTime dtPriceDate, SqlInt32 iSQLInclude) Msg 6532, Level 16, State 49, Procedure clrsp_RetailPriceOnDate, Line 0 .NET Framework execution was aborted by escalation policy because of out of memory.
Problem is that it is intermittent - runs fine several times(takes c 30 secs to run, returns 15k records), then fails, then runs ok, fails/runs/runs/fails etc... etc...
From the SQL Server documentation : "The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, except rowversion, text, ntext, image, timestamp, table, or cursor"This is a problem for me. Here's what I'm trying to do :I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR Chapter 2 Building a Procedure Chapter 3 SQLCLR Strucutre & Common Tasks Chapter 4 Creating Objects Chapter 5 Compare & Contrast Chapter 6 Replacing TSQL Objects Chapter 7 Using the Base Library Chapter 8 Using Procedures in Apps Chapter 9 Error Handling Chapter 10 Administration Chapter 11 Case Study
I have create few Mannaged SP which acces some external resources like closing and starting some windows process.. the assembly is assigend unsafe security level
all the processes are started from sqlserver.exe service and are allowed to interact with desktop
these SPs are called frequently
Problem... a very few times sql server is crashed..
does mannaged SP load app domain each time they are called?
what possible reason can be?
I am not exactly clear about the reson .. so I decided to post over here ..
I suspect this is very poor design but I've been asked to research this: can I maintain a persistent communication session inside an sqlclr proc? (tcp/binary packets)
The process has to maintain very high throughput - setting up and tearing down a connection on each transaction is cost prohibitive. For TCO and deployment reasons it is preferable not to have a separate windows service etc that accepts requests from inside the clr proc. Is there the luxury of an alternative integrated into the database?
In article http://support.microsoft.com/kb/913668 it says to generate a serialization assembly and pop this into the database along side the assembly with the serialized type. All well and good, but how doe the XmlSerializer know to look in the database for the assembly - does it use the name of the assembly + XmlSerializer?
It is certainly ambiguous in the aforementioned kb article. In one place it generates an assembly in the database called
CREATE ASSEMBLY [MyTest.XmlSerializers] from 'C:CLRTestMyTestMyTestinDebugMyTest.XmlSerializers.dll' WITH permission_set = SAFE
and in another it uses
USE dbTest GO CREATE ASSEMBLY [MyTest] from 'C:CLRTestMyTest.dll' GO CREATE ASSEMBLY [MyTest.XmlSerializers.dll] from 'C:CLRTestMyTest.XmlSerializers.dll' GO
I can't get either to work, and I've tried various combinations, this and the fact that the kb artice uses two different naming conventions suggests to me that there must be "something else" which links the assembly with the serialization assembly.
