SQL CLR stored procedure - SQL Server Forums

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

.NET Inside SQL Server (2005)

SQL CLR stored procedure

Author

Topic

clerus
Starting Member

8 Posts

Posted - 2009-02-11 : 05:58:52

Hello there guys

Have anyone of you encountered this type of error when using a user stored procedure using SQL CLR.

I've tried creating a stored procedure using vb.net. Here's the code.

======================================================================
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub MirumotoSampleStoredProcedure1(ByVal lastname As String)

Dim sqlConnection As SqlConnection = New SqlConnection("context connection = true")
Try
sqlConnection.Open()

Dim sqlCommand As New SqlCommand("Select * from Employees where strLastname = '" + lastname + "' ")
Dim sqlReader As SqlDataReader = sqlCommand.ExecuteReader()
SqlContext.Pipe.Send(sqlReader)
Catch ex As Exception
Throw ex
Finally
If Not sqlConnection Is Nothing Then
sqlConnection.Close()
End If
End Try

End Sub
End Class

======================================================================

This is succesfully deployed on our sql server.
The stored procedure created "MirumotoSampleStoredProcedure1" appeared on the Stored Procedures folder and the name of the project of course also appeared on the Assemblies folder.

Then I ran the stored procedure using the following script
exec MirumotoSampleStoredProcedure1 'Cruz'

An error message prompt...

Msg 6522, Level 16, State 1, Procedure MirumotoSampleStoredProcedure1, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'MirumotoSampleStoredProcedure1':
System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.
System.InvalidOperationException:
at MirumotoPayrollVB.StoredProcedures.MirumotoSampleStoredProcedure1(String lastname)

Is there additional other scripts or sql command needed to be run in order execute the created stored procedure?

Thanks for any information you can give guys!! Hoping to get a feedback. Thanks again!

clerus
Starting Member

8 Posts

Posted - 2009-02-11 : 21:22:46

oops guys, I've figured out what went wrong with the code above. I should have been put an object connection to my object reader. Here's hows it will look like. (The added one is in color red)
======================================================================
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub MirumotoSampleStoredProcedure1(ByVal lastname As String)

Dim sqlConnection As SqlConnection = New SqlConnection("context connection = true")
Try
sqlConnection.Open()

Dim sqlCommand As New SqlCommand("Select * from Employees where strLastname = '" + lastname + "' " ,SqlConnection )
Dim sqlReader As SqlDataReader = sqlCommand.ExecuteReader()
SqlContext.Pipe.Send(sqlReader)
Catch ex As Exception
Throw ex
Finally
If Not sqlConnection Is Nothing Then
sqlConnection.Close()
End If
End Try

End Sub
End Class

======================================================================

After running the above vb codes to deploy in sql server assemblies folder, the created store procedure just work fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources