CF5 OLEDB & Stored Proc

[solutionfinder]

I have a MS SQL stored procedure that simply creates a temp table (#temtable)
and inserts records from 3 other tables selects records from the temp table
then deletes it. Very simple. The server is running CF 5 (which I have no
control over) and the scenario works perfect using ODBC drivers.

We recently switched to OLE DB DSN to enhance performance and now this stored
proc no longer returns any results. I used to call the stored proc like this:

<cfquery name="getData" datasource="#dsn#">
{ call myStoredProc }
</cfquery>

With ODBC the above works, with OLEDB it returns an error when I try to
reference getData.<anything> because it does not exist. However the debugging
info shows that getData object has return 0 records.

Okay, next I tried this:
<cfstoredproc procedure="myStoredProc" datasource="#DSN#" returncode="yes">
<cfprocresult name="getData">
</cfstoredproc>
Now it does not error, but it still returns 0 records. When I switch to ODBC
it returns 30 something.

Here's the stored proc:
CREATE PROCEDURE . AS

CREATE TABLE #TempTable(
state CHAR(2) NOT NULL,
stateName VARCHAR(50) NOT NULL
)

INSERT #TempTable(state,stateName)
SELECT state,stateName FROM tableA WHERE country = 'United States';

INSERT #TempTable(state,stateName)
SELECT state,stateName FROM tableB WHERE country = 'United States';

INSERT #TempTable(state,stateName)
SELECT state,stateName FROM tableC WHERE country = 'United States';

SELECT DISTINCT state,stateName FROM #TempTable
ORDER BY stateName;

DROP TABLE #TempTable
GO


Any suggestions????

[philh]

The curly-bracketed CALL statement is an ODBC-specific construct. Use EXEC MyStoredProcedure or use the CFSTOREDPROC tag instead of CFQUERY (which is probably the better choice).

HTH,

[solutionfinder]

Thanks, I have tried it both of those ways with the same problem. It works fine if the stored proc is a simple select query, but it seems to choke with the temp table stuff.

Any other ideas?

[solutionfinder]

The CF server user has System Administrator permissions, and Database Access to all DBs.

I don't know if this would be the difference but CF uses Windows authentication (remember, I'm using CF5).

[paross1]

I know that this doesn't address the temp table issue between OLDB and ODBC,
but in your specific example, I was wondering why you needed to use a temp
table at all. How about replacing all of the inserts with this?

SELECT state,stateName FROM tableA WHERE country = 'United States'
UNION
SELECT state,stateName FROM tableB WHERE country = 'United States'
UNION
SELECT state,stateName FROM tableC WHERE country = 'United States'
ORDER BY stateName;

Phil

[solutionfinder]

Philh, No connection string.
Paross1, You are right that would be a better solution. However the stored
procedure in this thread was just one example of many stored procs that use
temp tables (none of which I wrote or control).

[paross1]

Philh,

UNION, as opposed to UNION ALL (at least according to my Oracle documentation)
eliminates duplicate selected rows and SQL Server BOL says basically the same
thing. Therefore, I am assuming that one only needs to use DISTINCT to
eliminate duplicates within each individual query, and using UNION by itself
should keep the duplictes from the combined queries.

Phil

[Sojovi]

Add SET NOCOUNT ON, and I think will be better to use a variable TABLE than a temp table, but I understand rigth and you can't modify the stored procedures ?

Regards

[solutionfinder]

Sojovi,
I don't have direct access to change the stored procs, but this can be done.
I just need to find the best solution before I tell the db admin to let me
change dozens of stored procs.

What do you mean by variable table vs temp table?

[Sojovi]

Temp Table :
CREATE TABLE #TempTable(state CHAR(2) NOT NULL,stateName VARCHAR(50) NOT NULL)
Variable Table :
DECLARE @TempTable TABLE (state CHAR(2) NOT NULL,stateName VARCHAR(50) NOT
NULL)

(read BOL about the advantages).

But about your problem, don0t know, I think is trial and error the way to find
the solution...

Regards