I am current working on a task in which I am needing to call a method in a web service from a CLR stored procedure. A bit of background: Basically, I have a task that requires ALOT of crunching. If done strictly in SQL, it takes somewhere around 30-45 mins to process. If I pull the same process into code, I can get it complete in sec...
I have written a CLR stored procedure that is in an assembly. I have a build system that can auto-build and deploy .net application from our source control repository. I want the two things to work together so I can redeploy the assembly that hosts the CLR stored proc. However it looks like, unlike IIS, simply replacing the binaries ...
Are multi-threaded CLR stored procs possible? I have a data-intensive task with lots of potential for parallelization. CLR Stored Procs would be great to remove the overhead of moving the data out of process, my I fear that I'd have to give up parallel computing. What are my options? Note: We're on SQL Server 2005 with plans in the ...
We have a third party application that accept calls using an XML RPC mechanism for calling stored procs. We send a ZIP-compressed dataset containing multiple tables with a bunch of update/delete/insert using this mechanism. On the other end, a CLR sproc decompress the data and gets the dataset. Then, the following code gets executed: ...
Is there a managed interface that can be used within a CLR Stored Procedure to send mail via the configured DBMAIL profiles? I am looking to send emails with attachments that will be downloaded from a web service so I am hoping to be able to send these without having to spool them out to disk in order to use the standard DBMAIL stored p...
Hi, When building a CLR Storedprocedure Project using MSBuild on our build server (Team City) we're getting the following error: error MSB4019: The imported project "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\SqlServer.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists o...
I am building CLR Stored Procedures and UDFs as discussed in this article: http://www.codeproject.com/KB/cs/CLR_Stored_Procedure.aspx When I execute Build > Deploy, I get the following error: Error: Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. N...
In an ASP.NET application, I have a small number of fairly complex, frequently used operations to execute against a database. In these operations, one or more of several tables needs updates or inserts based a logical evaluation of both input parameters and values of certain tables. I've maintained a separation of logic and data access, ...
In short - is it a good design solution to implement most of the business logic in CLR stored procedures? I have read much about them recently but I can't figure out when they should be used, what are the best practices, are they good enough or not. For example, my business application needs to parse a large fixed-length text file, ...
Question: In SQL Server 2005, how can I list all SQL CLR-functions/procedures that use assembly xy (e.g. MyFirstUdp) ? For example a function that lists HelloWorld for query parameter MyFirstUdp CREATE PROCEDURE HelloWorld AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].HelloWorld GO after I ran CREATE ASSEMBLY MyFirstUdp FROM ...
Question: If I add IF not exists to a create procedure as external name statement, I get a syntax error... why? Both statements work fine if I run them separately... IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PriceSum') AND type in (N'P', N'PC') ) CREATE PROCEDURE PriceSum(@sum int OUTPUT) ...
is there a way to make a query which DB the Stored Procedure is located? I got this bunch of Databases doing a lot of Cross DB query from each other, now I got lost with 1 stored procedure and I just want to look from which DB it is located. I want to make a script that will return the DB name of where that stored procedure is located. ...
I'm working with some SQL 2005 CLR code written in C#. We have recently altered a few of the functions to allow NULL parameters. We did this by changing parameters from 'double' to 'SqlDecimal' types. We successfully tested the changes in development and have moved to deploy the updates to the production server. We're using a SQL script ...
I'm trying to create a CLR function in SQLServer that calls a web service. When I create the first assembly, the AssemblyVersion in AssemblyInfo.cs is 1.0.*. I'll run sgen to create the accompanying XMLSerializers assembly, and I don't see any problems there. >"C:\Program Files\Microsoft SDKs\Windows\v7.0\Bin\sgen.exe" /force /assembl...
When I'm trying to execute a clr sproc (Still in C# IDE for this) I have permission errors. The error is " Request for the permission of type 'System.Data.SqlClient.SqlClientPermission,... failed", I've looked around online and it seems to throw back to IIS settings (I'm not using IIS nor am I using a website so these solutions don't app...
Has anyone had any luck creating an assembly for iTextSharp for use with CLR Stored procedures? I've been trying all afternoon and have not been able to get any results. SQL management studio is saying I need to register system.drawing first (can't be the new version, has to be the old version no less) but it gives me issues when I try...
My initial approach to this was to use a stored procedure in database of Application A, triggered on an insert to gather additional data and call a web-service, hosted by Application B to do the necessary mapping and persistence there. Application A and Application B may not be on the same machine. The initial requirement is to support a...
I have created a SQL CLR Stored Procedure which will call a WCF Service. I have managed to get all the correct DLL's loaded into SQL Server to get this to work however I now have following error message: System.Configuration.ConfigurationErrorsException: An error occurred creating the configuration section handler for system.serviceMode...
Hi All I have written stored procedure which has 2 Insert queries and 1 Update query inside it. Of all these,either insert queries or update query are executed at a time. Now my problem is to get ROWCOUNT in each case. Say suppose if insert operations are executed,then I want stored procedure to return @@ROWCOUNT to the calling applicati...
What are the limitations, unexpected pitfalls and performance traits of moving from in process C# code to SQL CLR Functions? We currently have several data heavy processes that run very fast using in process C# Asp.net MVC project without using a db at all. Performance is very important. The application uses a static in memory cache ...