A curious error message, local temp vs. global temp tables?!?!?

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @.cmd varchar(256)
DECLARE @.LargestEventSize int
DECLARE @.Width int, @.Msg varchar(128)
SELECT @.LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @.cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @.Width = @.LargestEventSize + 1
SET @.Msg = 'Here is the junk you asked about' + CHAR(13) + '---------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@.WhoKnows.com',
@.query = @.cmd,
@.no_header= 'TRUE',
@.width = @.Width,
@.dbuse = 'MyDB',
@.subject='none of your darn business',
@.message= @.Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?I would still like to hear if anyone knows anything different, but while looking into Des' sendmail problem, I found this lil' tidbit in BOL for xp_sendmail If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail. I suspect the "separate connection to SQL Server" is the issue here?!?!?! Hmmmm...perhaps the local temp table can be seen by child processes called by the proc that creates the table EXCEPT in xp_sendmail, etc.|||You hit the problem right on the head... xp_sendmail does execute the query in a different context, meaning that it can't see local variables, settings, or temp tables. You can think of it almost as though xp_sendmail were cranking up OSQL.EXE to execute your query (that isn't what actually happens, but it is logically pretty close).

-PatP

A curious case of data corruption

Dear group,

if someone could give me an idea what is going on in one of our
databases, this would really really be helpful.

We have two tables with around 2 / 3 million rows. These tables have no
key and no ID. (This major design flaw will be overcome in some later
version of the application-software working on this DB but right now i
have to live with this).

Now for the funny bit

1) I open one window in the Query-Analyzer and write some code like
Begin transaction INSERT INTO TABLE COMMIT
2) in another window i write "SELECT COUNT(*) from TABLE"

If I perform the insert then afterwards select count(*) the row-count
is incremented by two whereas the Insert-Statement said "1 row(s)
modified.

DBCC gives no errors.
DBCC gives amount of rows 2 million rows
Select count(*) on the same table gives 3 million rows

Exporting the data, truncating the table re-importing data gives no
result, right now the DTS-status is 203 and the machine is "thinking".

Is there any possibility to check the "integrity" of the table?

This problem is on the production machine, but right now i am working
on a copy so it was propagated with backup / restore-mechanism.

Any hint would be very helpful

Thanks and Greetings

Uli(uli2003wien@.lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
> Now for the funny bit
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(uli2003wien@.lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
> Now for the funny bit
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog schrieb:
> (uli2003wien@.lycos.at) writes:
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> > Now for the funny bit
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli|||
Erland Sommarskog schrieb:
> (uli2003wien@.lycos.at) writes:
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> > Now for the funny bit
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli|||(uli2003wien@.lycos.at) writes:
> as always a great help and a hint for the right direction. Actually
> this table had already a clustered index but dropping the index and
> recreating the index did the job for me (and much faster than
> exporting, dropping and importing the table)

That's good to hear. I would keep an eye on the table, in case the
problem would reappear.

By the way, rather than dropping and recreating, DBCC DBREINDEX can
be somewhat quicker. You can also use WITH DROP_EXISTING on CREATE INDEX.

This is particularly important if you have non-clustered indexes on the
table as well, as they will have to be rebuilt if you drop the clustered
index. This is because the NC indexes use the clustered index keys as
their row locator.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp