CLR with SQL Server 2005 - Compatablity = SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CLR with SQL Server 2005 - Compatablity = SQL 2000

Author

Topic

Aaron
Starting Member

20 Posts

Posted - 2007-08-28 : 15:56:38

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-28 : 17:02:37

Compatibility level must be 90 or higher to use .NET code inside the database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Aaron
Starting Member

20 Posts

Posted - 2007-08-28 : 17:07:48

I'm going to need to find out. It's another development companies database design and applications that I'm having to do some work in. They have compatibility set to 2000. I'll have to find out what can be done to change this.
I'm not sure if anybody here can answer this, but this is what I'm trying to do:

Call a webservice that expects some xml. I want to do this from SQL Server. I'm not at all experienced with Visual Studio( I had developed this process in Powerbuilder, but all our applications are being replaced by this other development companies stuff ) so I'm not even sure what kind of process to create in VS to get the right kind of dll for CLR.

Is what I want to do possible?
Can someone point me in the right direction?

Thank you!

Aaron

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 17:29:51

to have anykind of .net code in your database it's compatiblity level must be set to 9.0.
no workarounds

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Aaron
Starting Member

20 Posts

Posted - 2007-08-28 : 17:35:30

And there is no other way to call a webservice from SQL Server?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 17:38:59

I have seen a way to do this with "oamCreate" method, but it was not reliable.

E 12°55'05.25"
N 56°04'39.16"

Aaron
Starting Member

20 Posts

Posted - 2007-08-28 : 17:42:07

Are you sure it's 'oamcreate'? I can't find any help on this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 17:46:12

http://www.microsoft.com/downloads/details.aspx?familyid=ca1cc72b-6390-4260-b208-2058c0bfd7de&displaylang=en

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 18:01:35

SQL Server 2000 Web Services Toolkit offers the same possibility as http endpoints in SS2k5
meaning that you expose your data as a webservice.
It doesn't let you call outside web services from sql server.

seriously Aaron don't use OA_Create because it's really unstable.
If you have to do it in SQL Server 2000 the it's way better to have an outside app act as a service doing ths job.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Aaron
Starting Member

20 Posts

Posted - 2007-08-28 : 18:49:13

Ok, Spirit, sounds good. What kind of an app would I use? Could I use Visual Studios?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 18:58:27

sure. you can create a service that polls the db at some interval and sends the mails.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Aaron
Starting Member

20 Posts

Posted - 2007-08-29 : 08:58:59

Ok, I have found that by setting the databases compatibility level to 2005, creating my stored procedure, including the code:
External Name ....
the stored procedure is created just fine.

Then I set the compatibility back to 2000 and the stored procedure runs just fine. Now this example I have working is on a pretty simple little test vb code. Is this workaround I found a bad idea?

Aaron

Aaron
Starting Member

20 Posts

Posted - 2007-08-29 : 17:35:08

Anybody?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-29 : 17:41:44

you set the compatibility back to 2000 and you can call your .net stored procedure??

that's a pretty serious bug if you ask me.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Aaron
Starting Member

20 Posts

Posted - 2007-08-29 : 19:35:17

Well, I'm only testing with a short vb script right now. With comp. set to 2000 I could not create the stored procedure that referenced the assembly that was created from my vb dll. I set it to 2005, created the stored procedure, set comp. back to 2000 and the stored procedure still runs fine.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-30 : 04:42:03

you're right this is possible, but this is something i never thought it would be possible.
you can execute already created CLR objects but you can't create new ones.

but there's one thing i don't understand.
if you're using sql 2005 why do you need 2000 compatibility level?
you can't restore a 2005 database to 2000 anyway.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 04:57:32

"if you're using sql 2005 why do you need 2000 compatibility level?"

Only reasons I can think of are:

a) No accidentally creation of SQL2005-only object/code
b) No budget for the Regression Test

Kristen

Aaron
Starting Member

20 Posts

Posted - 2007-08-30 : 08:25:36

I'm not sure why they ( other developers/other company ) need it set to 2000. All I know is that the workaround works!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-30 : 08:30:26

yes it does... i've tried it myself
http://weblogs.sqlteam.com/mladenp/archive/2007/08/30/60313.aspx

but in my opinion this shouldn't work.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 09:11:23

I read somewhere that running a SQL 2005 database as Compatibility=80 (i.e. SQL 2000 mode) imposes a performance penalty, compared to Compatibility=90 (SQL 2005 mode). If that's the case then this isn't a good idea, notwithstanding my two points above.

Kristen

Aaron
Starting Member

20 Posts

Posted - 2007-08-30 : 11:33:47

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
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Service1
Inherits System.Web.Services.WebService
<WebMethod()> _
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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources