Stored Procedure w/ Temp table problem in DW painter

Okay, I've not seen this problem before and we use temp tables pretty
frequently in StoredProcedures.  We are in the early stages of constructing
an MSSQL 7.0 based system and we are actually building our first stored
procedure that does any kind of work.  The stored procedure has a temp table
in it and works perfectly.  But when I go to PB 7.0.3 build 10009, create a
new tabular dw, w/ the sp as the source, I don't get any columns for my
result set.  If I try a manual result set and do it myself, PB blows up when
I try to retrieve...  But if I eliminate the temp table as the result set,
it works perfectly.

Has anyone seen this or have any ideas on what the problem is??  I'm
wondering (hoping) if the latest version of PB has fixed this issue?

--
Travis Hettenbach
hettenbt@kochind.com


0
Travis
3/2/2001 4:43:13 PM
sybase.powerbuilder.general 62418 articles. 17 followers. Follow

2 Replies
724 Views

Similar Articles

[PageSpeed] 3

Found a workaround in previous msgs.
SET NOCOUNT ON

I added this to the beginning of the SP and it took care of all of our
problems.

I forgot to mention I was connecting w/ OLEDB as this seemed to contribute
to the problem.
Also, I'm not sure if the temp table was really the problem or if it was
anything w/ more than one statement!

"Travis Hettenbach" <hettenbt@kochind.com> wrote in message
news:GHQWcpzoAHA.161@forums.sybase.com...
> Okay, I've not seen this problem before and we use temp tables pretty
> frequently in StoredProcedures.  We are in the early stages of
constructing
> an MSSQL 7.0 based system and we are actually building our first stored
> procedure that does any kind of work.  The stored procedure has a temp
table
> in it and works perfectly.  But when I go to PB 7.0.3 build 10009, create
a
> new tabular dw, w/ the sp as the source, I don't get any columns for my
> result set.  If I try a manual result set and do it myself, PB blows up
when
> I try to retrieve...  But if I eliminate the temp table as the result set,
> it works perfectly.
>
> Has anyone seen this or have any ideas on what the problem is??  I'm
> wondering (hoping) if the latest version of PB has fixed this issue?
>
> --
> Travis Hettenbach
> hettenbt@kochind.com
>
>


0
Travis
3/2/2001 11:47:26 PM
try usinig 
sqlca.autocommit = true
before issuing the retieve command

I think this will work.

Trilok rawat
0
Trilok_Rawat
3/10/2001 10:08:51 AM
Reply:

Similar Artilces:

Temp table problem by stored procedures
I create two stored procedures by SA. Create proc stored1 As begin create table tempdb..table end Another stored procedure create proc stored2 AS begin select * from tempdb..table end.. It is ok when the SA access stored1 and stored2 sequentially. However, when a normal user like me, access two stored procedures. It is ok, it can create a temp table owned by me. However, when i execute the stored2, i get the table which is generated by SA, not by me? I dont know whether it is a bug or not? Thx try creating the procs also in your login insted of creating in sa log...

Stored Procedure problem w/reading proxy table
Breck, this is the continuation of the earlier thread... Now I get the following error when the SP tries to update the Id table for the proxy tables: ASA Error -192: Update operation attempted on non-updatable query. One of the statements that is causing the problem is: Update Id Set NextId = (Select IFNULL(MAX(SysId),1,MAX(SysId)+1) FROM users) Where TableName = 'Users'; I'm trying to update the Id table, not the proxy table. Can't I do this? Is there a better or another way of doing this? The Id table contains a record for every table that ...

Dw, containing an ASE Stored Procedure within a temp table creation
Hi! I have a datawindow with PB9 and this is wrapping a Stored Procedure (for example SP_TABLES). Those DWs, containing procedures that have "temp table creation", fail at run-time although they work fine at design-time. At runt-time im getting "Select error: The "CREATE TABLE" command is not allowed within a multi-statement transaction in th e"tempdb" database" error. Please help me how can i cover this situtation. DDL can't be executed within a transaction, but it sounds like your DW retrieve or update is occurring within a transa...

Unable to get DW .NET w/Stored Procedure w/One Argument to work
Has anyone ran into this...in DataWindow Designer 2.0 I have a DW that uses a stored procedure that takes one argument and it previews just fine. When I put it into VS 2005 and do a: WebDataWindowControl1.Retreive("sample data"); ...a run-time error ... "Retrieve Failed due to a Database error" Any ideas? Is the retrieval argument a date field? Then it is probably the date format is wrong. For example the underlying database may expect a date format of YYYY-MM-DD whereas the supplied argument may be DD-MM-YYYY or vice-versa. Regards, Kola Ogunlana Pe...

Stored Procedure
I have a DW based on a SP which requests 4 parameters (2 are optional) but when executing a retrieve (para1, para2, para3, para4) I don't obtain any result The SP is executed OK in mode interactive What's the problem? Thanks Alejandro Check in detail the value of each one of your parameters. If one of them is NULL, is probably thay your SP doesn't work. Good Luck, Javier Barbieri "Alejandro G�mez" <agomez@ucv.cl> wrote in message news:emM3DATwAHA.157@forums.sybase.com... > I have a DW based on a SP which requests 4 pa...

Stored procedure and Temp tables.
Hi, We are having 2 problems with stored procedures and temp tables This is on Solaris / Syb 11.9.2 1. When a procedure is creating a temp table, sometime the internal ID generated for the table (to have it unique in tempdb) is the same. Then we get the error "Table already exists". This shouldn't happen right ? Is there a case or bug number or patches available ? 2. In the same idea, if by mistake we do not have a "drop table #sample" at the end of the procedure, sometime not always the table is not being dropped after the procedure finishes the executio...

Stored Procedure and Temp Tables
This SP will fail to insert data into the temporary table "InvoiceSuppRequest" when the table does not exist when the server is rebooted unless the SP is created with the recompile option. The error indicates a problem with the IDENTITY COLUMN. My initial thought was that since it is precompiled and optimized that it was referencing an invalid object Id that no longer exists but how is it different than creating a #temp table, then compiling the SP, droping the #temp table and then creating a second SP that creates the #temp table then calls the referencing SP? The #temp ta...

Stored Procedure
I have a DW based on a SP which requests 4 parameters (2 are optional) but when executing a retrieve (para1, para2, para3, para4) I don't obtain any result The SP is executed OK in mode interactive What's the problem? Thanks Alejandro Check in detail the value of each one of your parameters. If one of them is NULL, is probably thay your SP doesn't work. Good Luck, Javier Barbieri "Alejandro G�mez" <agomez@ucv.cl> wrote in message news:emM3DATwAHA.157@forums.sybase.com... > I have a DW based on a SP which requests 4 pa...

problem with dw and a stored procedure
Hi all! I'm using a dw with an SP as data source. In design the dw retrieves data perfectly, but at run time I get this error: 5 Select error: ct_bind(): user api layer: external error: An illegal value of 15 given for parameter item. I'm using the same parameters in design and run time. Any idea about this? thanks in advance. PB 8.0.4 ASE 11.92 Win XP In design mode you are using a different database profile then at run time. You can try a few things: change the DBParm to set staticbind=0. It is probably set in the database profile during design (it ...

DW and stored procedure problem
Hi! We have problem with sp as datawindow source. Server: Sybase 11.9.2 Linux, PowerBuilder 7.0.2 When I create dw I get message from PowerBuilder "Cannot create DataWindow SQLSTATE = 37000 [INTERSOLV][ODBC SQL Server driver][SQL Server] Incorrect syntax near '='. 1 execute dbo.test1;0 RETURN_VALUE =:RETURN_VALUE" SP: CREATE PROCEDURE dbo.test1 AS BEGIN select message from load_log END If anybody knows what it means and how can I fix it, please respond Thanx. ...

Stored procedure DW problem
Hi all, PB 12.1 Net (6639), SQL Server 2005, SNC. Stored procedure DW hangs PB (IDE not respond) when I try retrieve. When I run app and retrieve on this dw hangs app too. I try SQLNCLI10 and SQLNCLI. The same DW on PB 12.1 classic works OK. In trace log I see PB not set parameters for procedure. On retrieve dialog I set sBarcodeId to 24059589 but in trace log I see sBarcodeId = 0. My trace log : (15e5fce8): DIALOG CONNECT TO TRACE SNC SQL NATIVE CLIENT(OLE DB): (15e5fce8): LOGID=... (15e5fce8): SERVER=... (15e5fce8): DBPARM=Database='...',Provider='SQLNCLI...

Temp tables in stored procedure
Can someone please tell me why I get an error message when I try to create this stored procedure? And how I can avoid it? The error message is: ------------------------------------------------------------ Server Message: Number 2714, Severity 16 Procedure 'sp_mystoredproc', Line 15: There is already an object named '#foobar' in the database. ------------------------------------------------------------ The create statement is: ------------------------------------------------------------ create proc sp_mystoredproc (@start integer = 0, @rowcount integer = 0,...

Stored procedures and temp tables
This is the problem: create table #temp1 ( col1 smallint) go create procedure sp_test1 ( @prm1 smallint ) as begin if @prm1 = 1 select count(*) from sysobjects else update #temp1 set col1 = 20 end go drop table #temp1 go create procedure sp_test2( @parm smallint) as begin create table #temp1 ( col1 smallint) insert into #temp1 values (10) insert into #temp1 values (30) insert into #temp1 values (40) exec sp_test1 @parm select * from #temp1 end Both procedures are created OK. If I try to execute sp_test1 immediatelly after creating the two procedures ...

DW-Stored Procedure Problem
Retrieving a PB7 DataWindow from a MS SQL Server 7.0 Stored Procedure. I am running PowerBuilder 7.0 and using Microsoft SQL Server 7.0 as my Back-end. I have written a Stored procedure in SQL Server which accepts two arguments and returns a set of rows after a set of processing. But my Problem is when I create the DataWindow Object in PB7 it shows a "Data Conversion error" after the final step of the DW object creation wizard. This error does not come up if I check the Manual Result set option in the Wizard. But when I do so I don't get the output and also I am una...

Web resources about - Stored Procedure w/ Temp table problem in DW painter - sybase.powerbuilder.general

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Robust Growth In Procedures, Beneficial Product Mix Drive Intuitive Surgical's Strong Q3 Performance
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 12/15/2015 5:44:51 PM