Sp_sproc_columns does not seem to work on Managed Stored Procedures
-
Hi,
I have written a stored procedure using C#.NET. I deployed it to SQL Server, and everything works fine.
However, an Third-Party app needs to see what parameters it can use for this SP by executing sp_sproc_columns <SP name>
sp_sproc_columns
Seems to work on normal SP's written in T-SQL, but not on Managed SP's. On my SP, sp_sproc_columns does not seem to work. It does not return the parameters. On normal SP's, it does.
Anyone have an idea why this is?
-
Because Microsoft didn't think about that when they added CLR integration and they forgot to add that specific object type to the underlying views or sql statements. That's just a guess but probably a pretty accurate one. I'd build up a view or procedure to get the information your looking for. You should just need to deal with sys.all_objects, sys.parameters, and sys.types to get the info you want.
-
<rant>
And you think I can just shoot plain SQL at my server???
You are wrong...
The only way to execute an Stored Procedure is to use a method called "ExecuteProcedure", which has to know which parameters can be passed by getting the info from sp_sproc_columns. Maybe try "ExecuteQuery" then? And just write "EXEC procedure".
No, that won't budge. ExecuteQuery can only get data from tables. And, every query then is wrapped into cursors.
The real fun starts when you switch server, then you have to change 20 connectionstrings. One for each table. After you have done that, it probably doesn't work either.
Fortunately we can do method calls from Classic ASP. You put the method name into a string like: "get_getVehicle". Whereas the string is passed as a parameter to the object which has the method.
And for this we pay like $4000 on top of the Microsoft stack. For the great web server (that relies on IIS), and for the great development tools. And you ofcourse have to pay for the SQL connectivity kit (http://www.dataaccess.com/products.asp?pageid=792). Which works great as you can see above!
Hey they copied the icons and shit from VS2005! How cool is that! It almost looks alike!!!!!
Can someone please post www.visualdataflex.com on the frontpage so that aspiring developers are wary. Maybe then I never again have to deal with this shit. I rather rot away making a career of building mailforms in PHP. Hell, at least PHP can send mails without instantiating a memory leaking COM object.
Greetings,
a disgruntled Intern
</rant>
-
I think what lpope187 was trying to say is, you can write a replacement for sp_sproc_columns using the data dictionary. Is the 3rd-party app connecting directly to your database? Do you have any control over how it gets its information? It is highly likely that a fairly simple WTF can be built to solve your problem, but I don't have enough details to go on at this point.
-
No its too high level for that. And I don't have any control over it whatsoever.
I'm no so sure I would want to dive into the SQL driver architecture of VDF.
Only 1 week to go, so no time for that.
-
Wait, you have a procedure and it works, right? Why can't you create a T-SQL procedure that is just a wrapper that takes the same arguments and passes them to the managed stored procedure? Then tell the 3-rd party app to use the wrapper.
-
hehe, that is a solution. Didn't think of that!
Nice one!
-
It's not really elegant or change-friendly, but it works.
A few years back I had to write a bunch of code generators (and re-generate code several times a week) to do some similar hackery. For one of them, there was this 3rd-party tool couldn't invoke stored procedures, but I could hook it to a DLL... so I had to have functions in the DLL with parameters that matched the stored procedures, passing that along to the database and returning the results.
It was ugly, but at least I was the one coding it... if one of the other programmers did it, it would have been hand-written and required manual updating for every change. As much as I hate code generators, I saved that company weeks upon weeks of developer time with them.
