SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CLR with SQL Server 2005 - Compatablity = SQL 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aaron
Starting Member

20 Posts

Posted - 08/28/2007 :  15:56:38  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 08/28/2007 :  17:02:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/28/2007 :  17:07:48  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/28/2007 :  17:29:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/28/2007 :  17:35:30  Show Profile  Reply with Quote
And there is no other way to call a webservice from SQL Server?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/28/2007 :  17:38:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/28/2007 :  17:42:07  Show Profile  Reply with Quote
Are you sure it's 'oamcreate'? I can't find any help on this.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/28/2007 :  17:46:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/28/2007 :  18:01:35  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/28/2007 :  18:49:13  Show Profile  Reply with Quote
Ok, Spirit, sounds good. What kind of an app would I use? Could I use Visual Studios?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/28/2007 :  18:58:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/29/2007 :  08:58:59  Show Profile  Reply with Quote
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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/29/2007 :  17:35:08  Show Profile  Reply with Quote
Anybody?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/29/2007 :  17:41:44  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/29/2007 :  19:35:17  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/30/2007 :  04:42:03  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/30/2007 :  04:57:32  Show Profile  Reply with Quote
"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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/30/2007 :  08:25:36  Show Profile  Reply with Quote
I'm not sure why they ( other developers/other company ) need it set to 2000. All I know is that the workaround works!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/30/2007 :  08:30:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 08/30/2007 08:30:54
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/30/2007 :  09:11:23  Show Profile  Reply with Quote
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
Go to Top of Page

Aaron
Starting Member

20 Posts

Posted - 08/30/2007 :  11:33:47  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000