| Author |
Topic |
|
Jusvistin
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-09 : 05:34:20
|
| Hello again,How can I change the sort order of the result set returned by the sp_columns stored procedure ?While you're at it,what is the easiest way to get the results from any similar stored procedure into a table or a table variable ?Thanks,Kevin(sleep is seriously overrated) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-09 : 06:22:50
|
| You can't put it into a table variable but to a temp tableinsert #aexec sp_columnsDepends on what is returned from the SP as to whether this will work.It is usually better to write your own sp,==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 06:50:40
|
| Uh-oh,That means I have to know the result set structure first....So I can't do a select into with stored procedure result sets ?Let's say I'm gonna recreate the functionality of sp_columns by querying the system tables directly, is this one of those "you really shouldn't count on your code working correctly in future releases" situations ?Thanks,Kevin |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-09 : 06:54:03
|
| Well you can using openquery or openresultsetselect *into #afrom openquery(self, 'exec sp_columns')Where self is a linked server to the local server.This will again depend on the SP as to whether or not it will work.Better to write your own using the information schema views.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 07:08:37
|
| I think I've using tried openquery before, but I seem to remember that the result set was missing a couple of rows compared to just executing the SP directly in Query Analyzer. I don't remember the details clearly, I think I was just playing around.Information Schema Views - that's a new term to me.... I guess it's back to BOL again.Thanks for your time,Kevin |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-09 : 07:17:52
|
| information_schema views are part of the ansi standard and will probably be consistent between versions. You can do the same thing using the system tables but the views are preferred (but sometimes you do need to go back to the system tables).I tend to use system tables because it means less typing :).Get a copy of "inside sql server" by Kalen Delaney.There will be a link on this site somewhere to it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 07:26:08
|
| I was using the system tables to re-create the sp_columns functionality, but was pretty sure it was not the best practice.I've got a copy of Kalen's book on order from BookPool, along with Rob Viera's book.Looks like I'll need them to satisfy my curiosity.Thanks,Kevin |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 07:52:20
|
| Nigel,Thanks for tip about Information Schema Views - very interesting indeed.Kevin |
|
|
|
|
SP_Columns - how to change result sort order ?