Help on writing CLR code that connects to a variable server and runs a procedure

Question

Post reply

Help on writing CLR code that connects to a variable server and runs a procedure

Victor Shahar

Mr or Mrs. 500

Points: 533
More actions
July 9, 2009 at 11:47 am

#208088

Hi guys,
I triyed to manage my self about this one but I'm sorry, i didn't succeed...
I only need an 'working' code written in VB .net or c# .net (..not important for me) as follows:
1. this clr code will be imported as a sp in my sql 2005 db.
2. the procedure will get parameters : ServerName and Cmd , i want the procedure to connect with the server name from the variable and perform the procedure from the Cmd parameter.
that's all, i'll be very happy if one of you will be nice and will write this for me, thank a lot for this issue.
Best regards,
Victor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Just out curiosity, why don't you use a linked server or xp_cmdshell to call sqlcmd?

Here a little sample in C#

using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static SqlInt32 StoredProcedure1

(

[SqlFacet(IsNullable=false)]

SqlString serverName,

[SqlFacet(IsNullable = false)]

SqlString procName

)

{

string cnStr = string.Format("Server={0};Integrated Security=SSPI;", serverName.Value);

using (SqlConnection cn = new SqlConnection(cnStr))

{

cn.Open();

using (SqlCommand cmd = new SqlCommand(procName.Value, cn))

{

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

}

return new SqlInt32(0);

}

};

Victor Shahar Mr or Mrs. 500 Points: 533 More actions · Answer 2

Thank you very much !!!

I so searched for something like this.

Btw, don't i need a 'close' (..nothing) for the cob=nnection ?

Again, thank you very much.

Victor

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Glad I could help 🙂

No, you don't have the "using"-block affects an implicit Dispose() for your objects. SqlConnection.Dispose() automatically calls Close().

Paul White SSC Guru Points: 150467 More actions · Answer 4

Florian Reischl (7/9/2009)
Just out curiosity, why don't you use a linked server or xp_cmdshell to call sqlcmd?

Yes, absolutely. With a linked server, the EXECUTE AT {linked server} syntax is very useful. CLR adds nothing but overhead here.

Florian Reischl (7/9/2009)
Here a little sample in C#

I would just add that one should map a parameter to the return value of the ExecuteNonQuery call and return that value instead of the "new SqlInt32(0)".

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Paul White (7/10/2009)
Florian Reischl (7/9/2009)
Here a little sample in C#
I would just add that one should map a parameter to the return value of the ExecuteNonQuery call and return that value instead of the "new SqlInt32(0)".

Good point and better solution.