Improve company productivity with a Business Account.Sign Up

x
?
Solved

primary key in stored procedure temp table question

Posted on 2008-10-02
9
Medium Priority
?
940 Views
Last Modified: 2008-10-13
Hi,

I use primary key in my temp tables since they hold many rows and I wanted to speed up the performance.  Many of these stored procedures uses open query.  These store proc are fired via scheduled DTS.  The trouble is that they sometimes fail (I don't know why, maybe timeout issues), and the temp tables gets orphaned in tempdb.  The temp tables have unique names w/in tempdb, but not the indexes.  The next time the same sp runs, the pk name is already taken and the sp will fail every scheduled run thereafter.  I could not figure out why the sp fails.  Is there a way the temp table can have unique index names so that if the temp tables gets orphaned, it would not prevent the same sp from running again?  

Unfortunately, the only way I can remove the orphaned temp tables is to restart the sql server.

Thx.
0
Comment
Question by:skip1000
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:3abqari
ID: 22630767
Are you referencing these temp tables only within that given code block in the SP?
0
 

Author Comment

by:skip1000
ID: 22631025
yes
0
 
LVL 4

Accepted Solution

by:
randy_knight earned 1600 total points
ID: 22631259
Not sure why your temp tables are not being dropped.  The temp table are created with # prefix, not ##?  Or hard tables?  By this I mean you can create a permanent table in tempdb just like any other database.  The difference being it will go away when the instance i restarted.  

In any case, to answer your index name question, one solution would be to use dynamic sql to generate a unique index name using NEWID().  Use the REPLACE() function to strip out the - and append it to your index name.

declare @Indexname varchar(50)
set @indexname = 'ix_' + replace(newid(),'-','')
print @indexname

use the @indexname in dynamic sql to create your index.


0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 1

Expert Comment

by:sqlconsumer
ID: 22632071
Hi Skip,

I was wondering why your creating such big temp tables? Could you achieve your objective by using indexed views ???

Whats going in/out of these temp tables ???

Could you supply an example of your SP ?
0
 

Author Comment

by:skip1000
ID: 22632119
bc it is openquery, it is not possible to join rows in mssql to mysql.  for me to be aware of any changes, i would have to take all row (selected fields) from mysql and bring it to mssql b4 i can make any comparison.  these tables in mysql can contain almost 2million rows, therefore, indexing would help w/ processing the info from temp tables.  if u know of a way to compare lots of data (hundreds of thousands of row) from 2 different environment (mysql and ms sql), then you would've indirectly solve my problem.:)
0
 
LVL 1

Expert Comment

by:sqlconsumer
ID: 22632159
Hows your knowledge of linked servers???

Here is a link that decribes setting up mySQL as a linked server. It might take a bit of time and patients to get it working, but I think this will create a manageable and scalable solution to your problem.

http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

The MSSQL <=> MYSQL marriage is a common industry topic so you should be able to find lots of info out there.

Hope this message has been of help.

sqlconsumer.
0
 

Author Comment

by:skip1000
ID: 22632202
i doubt there is a solution for that other than the way I'm doing it.  I posted a question here ages ago and it went unanswered for the longest time.  i think i finally had to just close it.  maybe it was deleted since i can't find any record of that question now.
0
 

Author Comment

by:skip1000
ID: 22674552
hi randy_knight,

I tried to create the temp table dynamically, except it disappears as soon as it is created.  seems like the temp table only exist w/in session of the dynamic sql, eg. exec(@sql).  I guess in the worst case, i might have to resort to using permanent tables that get reused each time.
0
 
LVL 4

Expert Comment

by:randy_knight
ID: 22675433
Nothing wrong with permanent tables that you create/drop each time.  And put them in tempdb so if they get orphaned they will get dropped on the next SQL Sevice restart.


0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question