f



Stored Procedure Issue - Problem with temporary tables

I would like to create a stored procedure which creates a temp table to
store some XML.  The # of fields of XML is dependent upon the contents
of another table in the application, so the first part of my procedure
identifies the # of fields necessary.  That is working correctly.  The
second part of the procedure actually attempts to create the table.
This is where my issue is.

If I attempt to create the table as a non-temporary table, the
procedure executes correctly.  As soon as I add the hash marks in front
of the table name to indicate that it is a temporary table, it is
failing.  Is this a known bug?  Or is my code just uncharacteristically
bad? ;)

I'm getting an error that says "Invalid object name '#temp'."

The section of code that has an issue is (the value of @max is 25 in my
test):

SET @xq = 'CREATE TABLE #temp ( respid int, '
SET @i = 0
WHILE( @i <= @max ) BEGIN
          SET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + '
xml'
          IF ( @i < @max ) BEGIN
                    SET @xq = @xq + ', '
          END
          SET @i = @i + 1
END
SET @xq = @xq + ' )'
SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) )
EXECUTE sp_executesql @nxq

......

DROP TABLE #temp

0
6/13/2006 11:49:54 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

3 Replies
336 Views

Similar Articles

[PageSpeed] 30

Hi Pinney

> EXECUTE sp_executesql @nxq

What this does is creates a table in the scope of the EXECUTE and then table 
then goes away as soon as the EXECUTE is finished,

But Execute does know about #temp tables created by the calling stored 
procedure.

What you migjht try is something like this.

alter proc spTemp
as

Create Table #tempx(
 row_id int not null identity(1,1) Primary Key)

declare @sql nvarchar(2000)
set @sql = N'alter table #tempx add data1 varchar(32)'
EXECUTE sp_executesql @sql
select * from #tempx
-- Will show row_id and data1 columns

-- 
-Dick Christoph

<pinney.colton@gmail.com> wrote in message 
news:1150242594.196816.277930@c74g2000cwc.googlegroups.com...
>I would like to create a stored procedure which creates a temp table to
> store some XML.  The # of fields of XML is dependent upon the contents
> of another table in the application, so the first part of my procedure
> identifies the # of fields necessary.  That is working correctly.  The
> second part of the procedure actually attempts to create the table.
> This is where my issue is.
>
> If I attempt to create the table as a non-temporary table, the
> procedure executes correctly.  As soon as I add the hash marks in front
> of the table name to indicate that it is a temporary table, it is
> failing.  Is this a known bug?  Or is my code just uncharacteristically
> bad? ;)
>
> I'm getting an error that says "Invalid object name '#temp'."
>
> The section of code that has an issue is (the value of @max is 25 in my
> test):
>
> SET @xq = 'CREATE TABLE #temp ( respid int, '
> SET @i = 0
> WHILE( @i <= @max ) BEGIN
>          SET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + '
> xml'
>          IF ( @i < @max ) BEGIN
>                    SET @xq = @xq + ', '
>          END
>          SET @i = @i + 1
> END
> SET @xq = @xq + ' )'
> SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) )
> EXECUTE sp_executesql @nxq
>
> .....
>
> DROP TABLE #temp
> 


0
dchristo99 (44)
6/14/2006 12:37:50 AM
DickChristoph wrote:
> > EXECUTE sp_executesql @nxq
>
> What this does is creates a table in the scope of the EXECUTE and then table
> then goes away as soon as the EXECUTE is finished,

Wow.  I was about to reply with "I don't believe you" b/c I have a
similar section of code just above it that, at first glance, does the
same thing.  But in that case, the temporary table is being created
immediately before sp_executesql (b/c the # of columns is not unknown
to me).

Subtle difference - but I see it now.  Thanks for opening my eyes.

0
6/14/2006 1:21:01 AM
pinney.colton@gmail.com (pinney.colton@gmail.com) writes:
> I would like to create a stored procedure which creates a temp table to
> store some XML.  The # of fields of XML is dependent upon the contents
> of another table in the application, so the first part of my procedure
> identifies the # of fields necessary.  That is working correctly.  The
> second part of the procedure actually attempts to create the table.
> This is where my issue is.
> 
> If I attempt to create the table as a non-temporary table, the
> procedure executes correctly.  As soon as I add the hash marks in front
> of the table name to indicate that it is a temporary table, it is
> failing.  Is this a known bug?  Or is my code just uncharacteristically
> bad? ;)

The latter.
 
I don't know enough to suggest an alternative solution, but normally
creating tables with a schema that is not known until run-time indicates
that there is a problem in the underlying design. Since all access to that
temp table will have to be through dynamic SQL, the procedure will be
very complex and difficult to maintain.

Maybe you should simply have one procedure per XML schema you need to
handle.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
esquel (7068)
6/14/2006 10:42:02 AM
Reply:

Similar Artilces:

Using temporary tables within the stored procedure
Hi, If one uses a temporary table/ table variable within a stored procedure, will it use the compiled plan each time the stored procedure is executed or will it recompile for each execution? Thanks in advance, Thyagu It will use compiled plan. Thyagu wrote: > Hi, > > If one uses a temporary table/ table variable within a stored > procedure, will it use the compiled plan each time the stored procedure > is executed or will it recompile for each execution? > > Thanks in advance, > Thyagu ...

Using a temporary tables within the stored procedure
Hi, If one uses a temporary table/ table variable within a stored procedure, will it use the compiled plan each time the stored procedure is executed or will it recompile for each execution? Thanks in advance, Thyagu ...

Stored procedure to create a new database with tables
I want my application to create a new database/tables when run for the first time. I have created a stored procedure to create the new database named "budget". When I run the stored procedure, it creates the budget database but the tables are created in the "master" database. Please help. Before the CREATE TABLE statements you should put this: USE Budget go "Shiller" <shillerc@gmail.com> ha scritto nel messaggio news:1172675843.097303.293060@8g2000cwh.googlegroups.com... > I want my application to create a new database/tables when run for the > first time. I have created a stored procedure to create the new > database named "budget". When I run the stored procedure, it creates > the budget database but the tables are created in the "master" > database. Please help. > On Feb 28, 10:44 am, "Massimo-Mastino" <mast...@hotmail.it> wrote: > Before the CREATE TABLE statements you should put this: > > USE Budget > go > > "Shiller" <shill...@gmail.com> ha scritto nel messaggionews:1172675843.097303.293060@8g2000cwh.googlegroups.com... > > > > > I want my application to create a new database/tables when run for the > > first time. I have created a stored procedure to create the new > > database named "budget". When I run the stored procedure, it creates > > the budget database but the tables are created i...

Declared Global Temporary Table in multiple Stored Procedures
Hi, As I understand it, Declared Global Temporary Tables (DGTTs) have a scope that is session/connection based. Using the same connection, I have discovered that if I declare a DGTT in one stored procedure, then I can't create a second stored procedure that uses the DGTT, as the DGTT is not "visible". The only way around this is to use dynamic SQL in the second stored procedure. In this way (using dynamic sql) i can create and use the DGTT across multiple stored procedures. Is there perhaps a way to make DGTT visible across multiple stored procedures using static sql, or is this an inherint limitation of DGTTs. Cheers Otto Otto Carl Marte wrote: > Hi, > > As I understand it, Declared Global Temporary Tables (DGTTs) have a > scope that is session/connection based. > Using the same connection, I have discovered that if I declare a DGTT > in one stored procedure, then I can't create a second stored procedure > that uses the DGTT, as the DGTT is not "visible". The only way around > this is to use dynamic SQL in the second stored procedure. In this way > (using dynamic sql) i can create and use the DGTT across multiple > stored procedures. > > Is there perhaps a way to make DGTT visible across multiple stored > procedures using static sql, or is this an inherint limitation of > DGTTs. The limitation is this: To create a procedure that uses a DGTT the table must be declare...

How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database ?
My MS-SQL 2000 Database have 50 more Stored Procedure . How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database? (mickyang@gmail.com) writes: > My MS-SQL 2000 Database have 50 more Stored Procedure . > > How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL > Database? I assume that you have the source code in files. Use a text editor that supports Find/Replace with regular expressions. Open all files in the editor. The change " AS *$" to " WITH RECOMPILE AS" in all files. Good text editor to use is TextPad, http://www.textpad.com. I would also recommend that you keep a backup directory, and use a compare merge/tool that permits you easily undo false positives. An excellent tool for this is Beyond Compare, http://www.scootersoftware.com. If you want something more robust, you would need to write a program that parses the file. Hardly worth it, if you only have 50+ procedures. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ...

Stored procedures using declared temporary tables: please help!
Hi, we are currently implementing an application running on DB2 V7 on Z/OS using largely COBOL stored procedures, managed using WLM. Some of these stored procedures declared global temporary tables, which are declared with ON COMMIT DELETE ROWS to perform work on and then return these temporary tables to the client (which is a message driven EJB connecting via DB2 connect). The client reads the data in the result sets, creates some XML to be sent back to the front end and then commits the transaction. At this point, I would expect the temporary tables to be destroyed and the t...

How to find query plan for a stored procedure using temporary tables
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these procs Repro --*********************************** use pubs go CREATE PROCEDURE Test @percentage int AS SET Nocount on --Create and load a temporary table select * into #Temp1 from titleauthor --Create second temporary table create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord int, rolaylityper int) --load the second temporary table from the first one insert into #Temp2 select * from #Temp1 go set showplan_Text ON go EXEC Test @percentage = 100 GO set showplan_Text OFF go ************************************** I get the following error Server: Msg 208, Level 16, State 1, Procedure Test, Line 10 Invalid object name '#Temp2'. Server: Msg 208, Level 16, State 1, Procedure Test, Line 10 Invalid object name '#Temp1'. I do understand what the error message means. I just want to know a better way of finding the query plan when using temp objects. My real production procs are hundreds of lines with many temp tables used in join with other temp tables and/or real tables. Regards On 25 Oct 2006 15:41:52 -0700, comp_databases_ms-sqlserver wrote: >This post is related to SQL server 2000 and SQL Server 2005 all >editions. >Many of my stored procedures create temporary tables in the code. I >want to find a way to find the query plan for the...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" statement of the SP I'm trying to create >within the outer SP. Can this be done? > >Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). Write it out to flatfile and execute the create from command line using dbaccess??? John Carlson wrote: > On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) > wrote: > > >>Is it possible to create a stored procedure from a stored procedure? >>When I attempt this inanity, it doesn't blow up until syntax error at >>the first "end procedure" statement of the SP I'm trying to create >>within the outer SP. Can this be done? >> >>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). > > > Write it out to flatfile and execute the create from command line > using dbaccess??? As John said, somewhat obliquely, there is...

Search for a table/string within job steps, dts, database, stored procedures etc
Hi All : A couple of tables have been identified to be deleted. My job is to find if it is at all used. On searching the web, i found a proc to search for a string within all databases in a server. using system sproc : sp_msforeachdb it searches for a string in views, sprocs, functions, check constraints, defaults, foreign key, scalar function, inlined tablefunction, primary key, 'Replication filter stored procedure, System table, Table function, Trigger, 'User table, 'UNIQUE constraint''Extended stored procedure' So it is pretty extensive. But i dont think it is covering the code within execsqltasks in DTS, and tsql code within JOB STEPS. Those are the two more places where code exists in my server. If any of you have done so in the past, do let me know if there is a system stored proc or code that you have written, to do the same thanks RS Link to the above procedure http://www.sql-server-performance.com/ak_find_sql_server_database_objects.asp (rshivaraman@gmail.com) writes: > A couple of tables have been identified to be deleted. My job is to > find if it is at all used. > On searching the web, i found a proc to search for a string within all > databases in a server. > using system sproc : sp_msforeachdb > > it searches for a string in > views, sprocs, functions, check constraints, defaults, foreign key, > scalar function, inlined tablefunction, primary key, 'Replication > filter stored procedure, System ...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > &...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? Well, I'm sure SQLServer has connectivity to Oracle? If you want to go this route, use that and do this from SQLServer. -- Galen Boyer On 10 Aug 2004 07:15:16 -0700, jewelk@free.fr (Cyril) wrote: >Hello, > >I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > >Is it possible to use the command "COPY FROM ... TO ..." ? >So, what is the correct syntax ? > > >Thanks for your help > >Cyril Read up on using the Heterogeneous Gateway to Sqlserver. -- Sybrand Bakker, Senior Oracle DBA "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100615.6371b40e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "...

How to stored a stored procedure's results into a table
Hi, How can I store a stored procedure's results(returning dataset) into a table? Bob *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! "Bob James" <BJ@system.com> wrote in message news:41ed4eb1$1_2@127.0.0.1... > > Hi, How can I store a stored procedure's results(returning dataset) into > a table? > > Bob > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! insert into dbo.MyTable exec dbo.My...

calling a stored procedure from a stored procedure
Hi Folks, I'm trying to define a (I thought) trivial stored procedure (SP) as a front-end to the standard SP "dbms_system.set_ev", which I want to use to generate a 10046 tracing event. I'm trying to execute the following PL/SQL: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; and getting the error: PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared If I provide the schema in which "set_ev" is defined, in this case "SYS", as in: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin sys.dbms_system.set_ev(sid, serial, 10046, 4, ''); end; I get: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared Any suggestions? thanks, RU On May 10, 1:25 pm, RU <r...@vakuum.de> wrote: > Hi Folks, > > I'm trying to define a (I thought) trivial stored procedure (SP) as > a front-end to the standard SP "dbms_system.set_ev", > which I want to use to generate a 10046 tracing event. I'm trying > to execute the following PL/SQL: > > create or replace procedure rob_enable_tracing ( > sid in integer, > serial in integer > ) > is > begin > dbms_system.set_ev(sid, serial, 10046,...

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never reach the Catch and the insert I try to do there to get the error message. On both Databases the user, that is taken to execute the trigger is existent and DB-Owner of both Databases. If I go and execute the Stored Procedures manually after an insert or update to Database B everything works fine. I also already tried to check on Database B if there is an insert or update from Database A and if, to execute the Stored Procedures, with the same result, nothing and all happens anymore, neither update on Database A and also not on Database B. And also I cant catch the error as the Try/Catch is not working. Hope I could explain it understandable and maybe someone remembers already having the same problem. Thanks & Best regards Pascal (pascal.baetscher@gmail.com) writes: > I face currently a problem where I could need some input for searching > the source of the Problem > >...

Storing all stored procedures in one database to minimize different connection strings
In order to minimize the number of connection strings I have to use to access different databases on the same Sql Server, I was considering storing all stored procedures in just one database. I want to do this because connection pooling in my application - ASP.NET is based on this connection string. So if I need to access 6 different databases on one sql server & set 6 different connection strings, I end up creating 6 different connection pools. Other than it might create more management work for the DBA, are there any performance implications with implementing this scheme? Do stored p...

MS Access VBA making multiple tables in database from a main table.
I have a database table called "tbl_personal" and struggling to VBA program a bit of code to create multiple tables in a database from the main "tbl_personal" table. In this case the 'color' column is the field I would like to look at. 1. Identify the number of unique records in column 'color' 2. For each color record that is label as red in the "tbl_personal" table, create a table called 'red' and populate with all red records in from the "tbl_personal" table. 3.Then move onto the next unique color and repeat the whole process again. 4. When all unique color tables are created in MS Access database end with a msg "batch process done" name surname age color country aa aaa 1 red UK bb bbb 10 green USA cc ccc 20 blue China dd ddd 30 red Germany ee eee 40 green France ff fff 1 blue Italy gg ggg 20 red Spain hh hhh 40 yellow Canada ii iii 34 black Italy =85 Is this doable automatically in MS Access? Many thanks. brenda Are you sure you want to do this? I would expect 3 tables vis: Table of Personel with PersonelID, Suname, FirstName, ColourID, CountryID & DateOfBirth (Age will be out of date next birthday and should be calculated), Table of Colours ColourID & Colour Table of Countries CountryID & Country. You can then use simple queries to return all the red people or people who live in UK. Am I missing something? Phil <bbcdancer@hotmail.com> wrote in message news:495c4ca6...

stored procedure issue
Hi, The following does not work, it doesn't seem to like a stored procedure combined with a sql request. Why and how can I overcome this ? SELECT table_name from INFORMATION_SCHEMA.tables where table_name IN ( exec pr_Admin_TablesFromRelations 1 ) Thx http://www.sommarskog.se/share_data.html Simon Put the contents of "pr_Admin_TablesFromRelations" into the subquery is probably the simplest and most efficient solution. -- David Portas SQL Server MVP -- ok, i was about to move the content of it into a function, is that ok ? Thx for your replies. This works fine : ...

Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get data for me while in another stored procedure. Its like a view, but a view you can't pass parameters to. In essence I would like a sproc that would be like this Create Procedure NewSproc AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... Or Delcare Table @TEMP @Temp = MAIN_SPROC 'a','b',..... Any ideas how I could return rows of data from a sproc into another sproc and then run a WHERE clause on that data? Thanks Chris Auer Instead of using the complicated stored proced...

Tables and Stored Procedures
SQL Server 2000: Question 1 If you drop / rename a table and then recreate the table with the SAME NAME, what impact does it have on stored procedures that use these tables? From a system perspective, do you have to rebuild / recompile ALL the stored procedures that use this table? I had a discussion with someone that said that this is a good idea, since the IDs of the tables change in sysobjects and from a SQL SERVER query plan perspective, this needs to be done... Question 2 If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if an error occurs? Will it Rollba...

Issue with a stored procedure
[Running 8.2.0 on Suse 8.1] I'm building a stored procedure that simulates cars arriving in a car park, and staying for different amounts of time, based on historical data. So, first I define a cursor that's populated by querying last year's bookings for the period that I'm interested in. This is about 6,500 bookings. The sp then looks at the first booking and compares it to how many cars are already in the car park. If adding that booking would violate the maximum space in the car park, it excludes the booking. Otherwise, it increments the number of cars in ...

To retrive a table from one database to and another database(same table)
hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,, Hi, use the three part notation: SELECT * FROM database.owner.Objectname (replace owner by schema for SQL2k5) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Hi surya While the first database is "Current" as would result from: Use FirstDatabaseName select * from OtherDatabaseName..Emp -- -Dick Christoph "surya" <suryaitha@gmail.com> wrote in message news:1142588469.061525.268810@u72g2000cwu.googlegr...

Stored Procedure Permissions Issue
Hi everyone, In order to provide security for a database I have been using stored procedures to obtain data from a MS SQL server. I establish an ADO connection using the following connection string "Provider=SQLOLEDB;Data Source=servername; Trusted_Connection=No;Initial Catalog=databasename;Uid=username;Pwd=password;" This username has no access to any of the tables but it does have "Execute" permissions on the stored procedures. The stored procedures have the same owner as all of the tables. All works very well until I try to write a stored procedure that updates records in a table. When I try to execute that procedure an error is returned saying that select permission is denied on one of the tables. Does anyone have any ideas where I've gone wrong? Daniel.Peaper@gmail.com (Daniel.Peaper@gmail.com) writes: > In order to provide security for a database I have been using stored > procedures to obtain data from a MS SQL server. > > I establish an ADO connection using the following connection string > > "Provider=SQLOLEDB;Data Source=servername; > Trusted_Connection=No;Initial > Catalog=databasename;Uid=username;Pwd=password;" > > This username has no access to any of the tables but it does have > "Execute" permissions on the stored procedures. The stored procedures > have the same owner as all of the tables. All works very well until I > try to write a stored procedure that updates recor...

DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main stored proc that may be calling multiple smaller stored procs? Just found out about this need late this afternoon and wondering what the best approach is to doing such a task. Any and all ideas most welcome. thanks, fv <PJackson@txfb-ins.com> wrote in message news:1175743257.812351.234300@y80g2000hsf.googlegroups.com... >I have been given the task of taking a 3,200 line COBOL stored > procedure and duplicating the same functionality in UDB 7.2 on the > Windows platform with a procedural SQL stored procedure. I have > fiddled with procedural SQL stored procs on UDB, but mostly > short,trivial ones. Looking for good advice, links, etc. on the best > approach to this. There doesn't seem to be the concept of subroutines > within a stored proc, so I am guessing one main stored proc that may > be calling multiple smaller stored procs? Just found out about this > need late this afternoon and wondering what the best approach is to > doing such a task. > > Any...

Temp Table in a Stored Procedure
I need to select and mark 150 records at a time in a large table. What I'm trying to do is... Select top 150 xxxx into #temp from largeTable update largeTable set marked = 1 where xxxx in #temp This is very simplified, the real procedure is quite large. The Error I am getting is Invalid object name '#temp'. I ended up creating the table in it's own statement and it is working now, I guess that insert into doesn;t create the table, but it doesn't throw an error either. (rbonin@gmail.com) writes: > I need to select and mark 150 records at a time in a large table...

Assessment of Database Stored Procedures
I am attempting to compile a list of questions that will enable me assess 'at risk' stored procedures that need to be remediated in order to minimize unplanned downtime and enhance database performance. Is there a subset of criteria (parameters) from the MS SQL Server Best Practices Analyzer that can be used for this purpose? Any pointers to the appropriate documentation is most welcome. Thanks in advance. It depends what you class as acceptable criteria. It depends on the nature of the stored procedures. For example, if it's a simple SELECT statement , returning it in 6 seconds may be to slow for the end user., and could point to something else being the problem. Check http://www.sql-server-performance.com/articles_audit.asp for a systematic approach -- Jack Vamvas ___________________________________ Need an IT job? <a href="http://www.itjobfeed.com">uk it jobs</a> "Umar Reyi" <iyerra@indiatimes.com> wrote in message news:1177366044.733092.100680@y80g2000hsf.googlegroups.com... >I am attempting to compile a list of questions that will enable me > assess 'at risk' stored procedures that need to be remediated in order > to minimize unplanned downtime and enhance database performance. Is > there a subset of criteria (parameters) from the MS SQL Server Best > Practices Analyzer that can be used for this purpose? Any pointers to > the appropriate documentation is most welcome. > > T...

Web resources about - Stored Procedure Issue - Problem with temporary tables - comp.databases.ms-sqlserver

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 ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

New Video Series Shows The Reality Of Abortion Procedures
New Video Series Shows The Reality Of Abortion Procedures

Yay going in for kidney stone procedure soon!
There is a G-d i have been in the hospital with stone pain and bowel pain the past week. the bowel issue resolved by itself without surgery ...

Resources last updated: 3/22/2016 3:55:46 AM