February 10, 2005 at 9:39 am
HI, I have a problem with a Stored procedure, this SP is for generate a temporary table or cursor used by a Crystal report. when I run the SP with certain parameters everything goes well, but certain parameters I have an error message of Data could be truncated.
I really think that is the structure of two diferent ##tables that should be the same structure but since one of them is created with CREATE TABLE command , the other one is created with a group of SELECT statements.
I guess that is where the problem begins cause the data those sql statements retrive and the structure becomes diferent of the other one.
I need to compare the structures, and the data retrived too, but when i try to perform a select against the ##table, it seems to be blocked and after the SP ends , the table ain't there.
i even tried to insert the data to a permanet table but the table disappears after the SP ends
any idea
February 10, 2005 at 11:05 am
We have had similar problems with SELECT INTO statements on text fields, (i.e., char, varchar, etc). If these select statements are hardcoded, you can try specifying the same datatypes as the original table. For instance, TableA.Column1 is a varchar(30).
SELECT SUBSTRING( TableA.Column1 + SPACE(30), 1, 30) AS Column1
(When we've had these problems, it is due to the first string, (i.e., 'String One' + SPACE(7) versus 'String Number Two' selected into a table being shorter than the following string, hence the need to use + SPACE(x) )
I wasn't born stupid - I had to study.
February 11, 2005 at 3:41 am
Just been reading an article about using temporary tables and it recommends that you use DDL (i.e. build it yourself) rather than use select into. That way you can be sure that your temporary table has the same structure as your other one.
February 11, 2005 at 6:32 am
I feel your pain. I tried to do that too at one point. I had an SP create a temp table, then try to populate it in a different SP. It would create the temp table, but the second SP would fail to recognize the newly created temp table.
I finally gave up on deciding that maybe it just isn't possible. Maybe MS thought it might be a bad idea. Anyway I finally found two work arounds, which may be faster anyway.
Method 1: Create a permanent table, and reuse it, just truncate it when you are done (thereby eliminating the logging overhead of deleting all records).
Method 2: Build a virtual table using table valued functions and views. If you can get the indexed views to work, that can significantly speed matter up.
These are my thoughts. I hope that helps.
February 11, 2005 at 6:36 am
Just create both temporary tables with create statements and insert the data in!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply