dll & Web Service in VB.Net 2005 Stored Procedure

[James Wong]

Hi,

I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProjec t Template.
Now, I have two questions for this stored procedure.

1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?

-------------------------------------------------------------------
Partial Public Class StoredProcedure s1
<Microsoft.SqlS erver.Server.Sq lProcedure()> _
Public Shared Sub GetTable()

Dim strSQL As String = "SELECT * FROM Table1"

Using conn As New SqlConnection(" context connection=true ")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Tex t
conn.Open()
SqlContext.Pipe .ExecuteAndSend (cmd)
.Connection.Clo se()
End With
End Using
End Using
End Sub
End Class
-------------------------------------------------------------------

Thanks!

[Cor Ligthert [MVP]]

James,

As you use a newsreader as Outlook Express, will you than be so kind not to
multipost to these dotNet newsgroup. We appriciate crossposting (sending one
message to more newsgroups at once)

I have given an answer in another newsgroup where you have placed the
question.

Thanks in advance

Cor

"James Wong" <cp*******@nosp am.nospam> schreef in bericht
news:%2******** **********@TK2M SFTNGP02.phx.gb l...

Hi,

I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProjec t Template.
Now, I have two questions for this stored procedure.

1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?

-------------------------------------------------------------------
Partial Public Class StoredProcedure s1
<Microsoft.SqlS erver.Server.Sq lProcedure()> _
Public Shared Sub GetTable()

Dim strSQL As String = "SELECT * FROM Table1"

Using conn As New SqlConnection(" context connection=true ")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Tex t
conn.Open()
SqlContext.Pipe .ExecuteAndSend (cmd)
.Connection.Clo se()
End With
End Using
End Using
End Sub
End Class
-------------------------------------------------------------------

Thanks!

[James Wong]

Thanks!

"Cor Ligthert [MVP]" <no************ @planet.nl> ¼¶¼g©ó¶l¥ó·s»D: %2************* ***@TK2MSFTNGP0 5.phx.gbl...

James,

As you use a newsreader as Outlook Express, will you than be so kind not
to multipost to these dotNet newsgroup. We appriciate crossposting
(sending one message to more newsgroups at once)

I have given an answer in another newsgroup where you have placed the
question.

Thanks in advance

Cor

"James Wong" <cp*******@nosp am.nospam> schreef in bericht
news:%2******** **********@TK2M SFTNGP02.phx.gb l...

Hi,

I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProjec t Template.
Now, I have two questions for this stored procedure.

1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?

-------------------------------------------------------------------
Partial Public Class StoredProcedure s1
<Microsoft.SqlS erver.Server.Sq lProcedure()> _
Public Shared Sub GetTable()

Dim strSQL As String = "SELECT * FROM Table1"

Using conn As New SqlConnection(" context connection=true ")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Tex t
conn.Open()
SqlContext.Pipe .ExecuteAndSend (cmd)
.Connection.Clo se()
End With
End Using
End Using
End Sub
End Class
-------------------------------------------------------------------

Thanks!

[Steven Cheng[MSFT]]

Hello James,

Is the stoerd procedure you mentioned here means the new CLR stored
procedure in SQL Server 2005? If so, you can use managed code in CLR stored
procedure (just a .net class method). And the general steps to create and
install such a CLR SP is :
1. compile our CLR stored procedure as assembly

2. install the assembly into SQL 2005 database (grant suffient access level
and also some other security related setting)

3. create the stored procedure refer to the assembly and class

Here is a good web article demostrate a complete example. The author
created a CLR stored procedure which will access a remote webservice and
use the webservice result in his SP.

#CLR Stored Procedure Calling External Web Service
http://davidhayden.com/blog/dave/arc...4/25/2924.aspx

Also, for general information on SQL 2005 CLR stored procedure, you can
refer to the following BOL document:

#CLR Stored Procedures
http://msdn2.microsoft.com/en-us/library/ms131094.aspx

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Community Support
=============== =============== =============== =====

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Cor Ligthert [MVP]]

Steven,

Thanks, so you see you learn every day in these newsgroups.

:-)

Cor

"Steven Cheng[MSFT]" <st*****@online .microsoft.com> schreef in bericht
news:4V******** ******@TK2MSFTN GXA01.phx.gbl.. .

Hello James,

Is the stoerd procedure you mentioned here means the new CLR stored
procedure in SQL Server 2005? If so, you can use managed code in CLR
stored
procedure (just a .net class method). And the general steps to create and
install such a CLR SP is :
1. compile our CLR stored procedure as assembly

2. install the assembly into SQL 2005 database (grant suffient access
level
and also some other security related setting)

3. create the stored procedure refer to the assembly and class

Here is a good web article demostrate a complete example. The author
created a CLR stored procedure which will access a remote webservice and
use the webservice result in his SP.

#CLR Stored Procedure Calling External Web Service
http://davidhayden.com/blog/dave/arc...4/25/2924.aspx

Also, for general information on SQL 2005 CLR stored procedure, you can
refer to the following BOL document:

#CLR Stored Procedures
http://msdn2.microsoft.com/en-us/library/ms131094.aspx

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Community Support
=============== =============== =============== =====

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no
rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Hello James,

Any further progress on this? If there is still anything we can help,
please feel free to post here.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
=============== =============== =============== =====

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)