CLR with SQL Server 2005 - Compatablity = SQL 2000
-
I have a Sql Server 2005 Database that has it's compatibility set to 2000. I'm trying to create a stored procedure that references a vb.net assembly. I've gotten to the point where I'm creating my stored procedure and trying to have it include the code:
External Name ....
where I reference the assembly ( which was created successfully )
I'm getting the message:
'Incorrect syntax near 'external'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.'
If I can't change the compatibility level to 2005 am I out of luck?
Any help would be appreciated,
Aaron
-
To use CLR stored procedures, your database needs to be in Compatability Mode 9.0 (SQL Server 2005).
-
I've successfully created my assembly from my vb.net project. However when I try to create my stored procedure to call the function with the statement External Name DWFWebService ( where DWFWebService is the name of my assembly ) I'm not sure how to reference my functing that I created in my vb project ( I have very little experience in vb ).
Here's a rundown of whats in my project:
Solution Explorer
DWF_vb_WebService
My Project
- Web References
ServiceBroker
ClassDiagram1.cd
DWFWebService.snk
Service1.asmx
Settings.vb
Web.config
Then I have, what I guess, is a function
Service1.asmx.vb
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
_
_
_
Public Class Service1
Inherits System.Web.Services.WebService
_
Public Function CALL_WebService(ByVal strXML As String) As String
Try
Dim objService As ServiceBroker.dwng_broker = New ServiceBroker.dwng_broker()
Return objService.Talk(strXML, "3")
Catch ex As Exception
Return ex.Message
End Try
End Function
End Class
Any help would be appreciated.
Aaron
-
We solved this problem by creating a 'Tools' database which is in SQL2005 mode, and hosting CLR objects and other 2005 stuff there. You can then call them from the 2000 compatible database.
I have used this to handle CTE based recursive functions and code needing features like CROSS APPLY as well as CLR functions. Seems to work OK!
Tony -
That's exactly what I ended up doing. Everything's going live this weekend, so fingers are crossed!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply