executing procedure via ODBC Connection

[apoorva_raje]

trying to execute this stored procedure using a prg that connects to the server using an ODBC DSN. it doesnot retrieve the result set. CREATE PROCEDURE PROC_1 AS CREATE TABLE #abc ( ssn char(30) ) insert into #abc(ssn) select phone from tbl_users select * from #abc GO when executed directly, ie. say, Query Analyser or Excel, there r no probs encountered. also, when i remove the create n insert stmts, n change the #abc to a table abc, am getting the results. Thanks..

[Colin Angus Mackay]

A slightly off topic question - but why are you creating a temprorary table, populating it with a fairly simple query then retrieving the result set. Would it not be quicker just to retrieve the result set from the SELECT used on the INSERT? The temp table will just disappear at the end of the stored procedure call, so what use is it? Or is there more code you are not telling us about which may help us answer your query. In other words why are you not just doing:

SELECT phone from tbl_users

---

Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website

[apoorva_raje]

yeah.. there r more operations that r to be performed on this temp table.. modifications, etc.. but i got to get this running b4 i put in the other stmts... SOS!!

[ash04]

use this insert into #abc(ssn) select isnull(phone,'') from tbl_users

DAsh04

[andyharman]

Try adding

set nocount on

as the first line of your procedure. This stops SQL-Server from sending-back a running commentary of what its up to (which is probably being counted as the resultset for you current procedure).

[apoorva_raje]

thx a lot!! kudos..