dll & Web Service in VB.Net 2005 Stored Procedure

9 Jun 2006 6:36 AM

James Wong

Hi,

I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProject 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 StoredProcedures1
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    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.Text
                    conn.Open()
                    SqlContext.Pipe.ExecuteAndSend(cmd)
                    .Connection.Close()
                End With
            End Using
        End Using
    End Sub
End Class
-------------------------------------------------------------------

Thanks!

9 Jun 2006 6:56 AM

David Browne

"James Wong" <cphk_msdn@nospam.nospam> wrote in message
news:u155F84iGHA.1260@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I am writing a vb.net2005 program that needs to create a stored procedure
> with SqlServerProject Template.
> Now, I have two questions for this stored procedure.
>
> 1) How can I import and execute the .dll in this Stored Procedures?

You need to deploy the assembly into your database (CREATE ASSEMBLY) and
then add a stored procedure referencing the method in the assembly (CREATE
PROCEDURE). Visual Studio will do this for you if you right-click on the
project and select Deploy.

Here's how you would do it by hand:

CREATE ASSEMBLY [SqlServerProject2]
   FROM 'c:\SqlServerProject2.dll'
   WITH PERMISSION_SET = SAFE

GO

CREATE PROCEDURE [GetTable]
AS
   EXTERNAL NAME
[SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]

> 2) How can I connect the Web Service and get the result in this Stored
> Procedures?
>

From the web service just connect to the database and execute it with a
SqlCommand.

David

9 Jun 2006 7:20 AM

James Wong

Hi David,

sorry, i think that u were misunderstood my problem.
My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
..dll & web service to get some value.

Thanks

Show quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:O3xF9H5iGHA.4***@TK2MSFTNGP05.phx.gbl...
>
> "James Wong" <cphk_msdn@nospam.nospam> wrote in message
> news:u155F84iGHA.1260@TK2MSFTNGP05.phx.gbl...
>> Hi,
>>
>> I am writing a vb.net2005 program that needs to create a stored procedure
>> with SqlServerProject Template.
>> Now, I have two questions for this stored procedure.
>>
>> 1) How can I import and execute the .dll in this Stored Procedures?
>
> You need to deploy the assembly into your database (CREATE ASSEMBLY) and
> then add a stored procedure referencing the method in the assembly (CREATE
> PROCEDURE). Visual Studio will do this for you if you right-click on the
> project and select Deploy.
>
> Here's how you would do it by hand:
>
> CREATE ASSEMBLY [SqlServerProject2]
>   FROM 'c:\SqlServerProject2.dll'
>   WITH PERMISSION_SET = SAFE
>
> GO
>
> CREATE PROCEDURE [GetTable]
> AS
>   EXTERNAL NAME
> [SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]
>
>
>> 2) How can I connect the Web Service and get the result in this Stored
>> Procedures?
>>
>
> From the web service just connect to the database and execute it with a
> SqlCommand.
>
> David
>

9 Jun 2006 7:41 AM

David Browne

"James Wong" <cphk_msdn@nospam.nospam> wrote in message
news:eWXrxU5iGHA.412@TK2MSFTNGP05.phx.gbl...
> Hi David,
>
> sorry, i think that u were misunderstood my problem.
> My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
> .dll & web service to get some value.
>

To use another .dll add a reference to your database project. To use a web
service add a web reference.

David

9 Jun 2006 8:01 AM

James Wong

Hi David,

1) For DLL, VS.Net 2005 is not allow me to import a new References in
SqlServerProject Template.

2) For Web Service, it will occur error when running.

-------------------------------------------------------------------------------------------------------------
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SPWithWebService()

        Dim WebService As New SqlServerProject1.localhost.Service
        Dim msg As String = CStr(WebService.HelloWorld())

        SqlContext.Pipe.Send(msg)
    End Sub
End Class
-------------------------------------------------------------------------------------------------------------

Thanks

Show quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:OqYxDh5iGHA.3***@TK2MSFTNGP04.phx.gbl...
>
> "James Wong" <cphk_msdn@nospam.nospam> wrote in message
> news:eWXrxU5iGHA.412@TK2MSFTNGP05.phx.gbl...
>> Hi David,
>>
>> sorry, i think that u were misunderstood my problem.
>> My Stored Procedure "StoredProcedures1.GetTable()" is need to call
>> another .dll & web service to get some value.
>>
>
> To use another .dll add a reference to your database project. To use a
> web service add a web reference.
>
> David
>

9 Jun 2006 9:31 AM

privatenews

Hello James,

When you are using the VS SQL Server Project you are restricted to what
assemblies you can reference. This behavior is by design. However, you
could create a normal class library project, add references as per usual
and then manually create your assembly through CREATE ASSEMBLY. Make sure
your referenced
assemblies are in the same directory as your user assembly.

If you want to use the VS SQL Server Project you can create the assembly
you want to reference in the database before you try to reference it. You
need do a manual CREATE ASSEMBLY against the DLL you want.

As for Web service , you will also need to use the CLR SDK tool SGEN.EXE to
create and register a helper assembly for the assembly generated by WSDL,
as described in:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.