God Only Knows Why???

CREATE TABLE [__1] (
[n] [int] IDENTITY (1, 1) NOT NULL ,
[a] [decimal](16, 5) NULL
) ON [PRIMARY]
GO

INSERT INTO __1
(a)
VALUES (1.22115)

select * from __1

------------
RESULTS FROM Query Analyzer
------------

(1 row(s) affected)

n a
---- ------
1.00 1.22

(1 row(s) affected)

When I go to the Enterprise Manager and see the data, it appears as

n a
---- ------
1.00 1.22115

I am soooo confused!! Highly appreciating your comments...try inserting 1.22000|||Originally posted by RaedT
CREATE TABLE [__1] (
[n] [int] IDENTITY (1, 1) NOT NULL ,
[a] [decimal](16, 5) NULL
) ON [PRIMARY]
GO

INSERT INTO __1
(a)
VALUES (1.22115)

select * from __1

------------
RESULTS FROM Query Analyzer
------------

(1 row(s) affected)

n a
---- ------
1.00 1.22

(1 row(s) affected)

When I go to the Enterprise Manager and see the data, it appears as

n a
---- ------
1.00 1.22115

I am soooo confused!! Highly appreciating your comments...
it gives right results for me.|||Karolyn:
After iserting 1.22000, nothing changed!
the output is still rounded to 1.22

harshal_in:
Notice the output from the Query Analyzer. The inserted value 1.22115 was rounded to 1.22, I need the Query Analyzer to give me what really exists in the table 1.22115, not 1.22!!

Originally posted by harshal_in
it gives right results for me.|||sorry I've mis-read your pb

it's the same pb as last week !

you must have a property or option in your QA that
rounds up your data

or maybe you don't see it totally... (?)

try

Select '-' + convert(varchar(50),a) + '-' From Table|||CREATE TABLE [__1] (
[n] [int] IDENTITY (1, 1) NOT NULL ,
[a] [decimal](16, 5) NULL
) ON [PRIMARY]
GO

INSERT INTO __1
(a)
VALUES (1.22115)

select 'Value = '+convert(varchar,a) from __1

What does this return ?|||Output:
------------
Value = 1.22115

? What does this mean?

Originally posted by Enigma

CREATE TABLE [__1] (
[n] [int] IDENTITY (1, 1) NOT NULL ,
[a] [decimal](16, 5) NULL
) ON [PRIMARY]
GO

INSERT INTO __1
(a)
VALUES (1.22115)

select 'Value = '+convert(varchar,a) from __1

What does this return ?|||that you don't see the all the numbers of your result

the column in the QA is too narrow
maybe there's an option to adapt the witdht of a column|||Just curious .. whats the version of sql query analyzer are you using ...
I found no option in QA that does rounding off :confused:|||Go see TOOLS-OPTIONS in the QA

in the Result tab there's a number of caracters

(maybe someone that doen't like you set it to 4)|||QA Version: 8.00.760
Max. Characters per column: 256

Note, even the int type value (1) appears as 1.00

Originally posted by Karolyn
Go see TOOLS-OPTIONS in the QA

in the Result tab there's a number of caracters

(maybe someone that doen't like you set it to 4)|||At least you've got the good result in the table...
(Being optimistic)

Check all the options in your databases and in QA|||how are you starting up your query analyzer ?

By clicking on an icon ? The only thing i can think of is that you might be starting up your qa with some configuration options

Check your shortcut properties ... the error might lie there|||I am starting my QA from the EM.

TOOLS=> SQL QA

Originally posted by Enigma
how are you starting up your query analyzer ?

By clicking on an icon ? The only thing i can think of is that you might be starting up your qa with some configuration options

Check your shortcut properties ... the error might lie there|||did you find the round-up-property ??|||I can go ahead of my work, I converted the decimal numbers to varchar, so I can get the results with 5 decimal places, but I am so frustrated and need to know why? I am so curious to know what the problem is.

Originally posted by Karolyn
did you find the round-up-property ??|||If you see BlindMan or Breitt Kaiser online
Ask them !!!|||I get

n a
---- ------
1 1.22115

(1 row(s) affected)

No way an int is decimal(5,2)

I checked the options for the result set and don't see anything...

What collation are you using...(doubt that that's it either)

I don't buy that a column defined like you posted will ever display an identity that way...|||Grasping at straws here, but Karolyn obligates me to respond...

What is your "Use regional settings when outputting currency, number, dates, and times" setting in QA options? (It's in the connections tab.)

If it is on, try setting it to off.

From Books Online:

"Use regional settings when outputting currency, number, dates, and times.
Turning this setting to ON causes the ODBC driver to respect the local client setting when converting numeric, date, time, and currency values to character strings. The conversion is from SQL Server native data types to character strings only. When the setting is OFF, the driver does not convert numeric, date, time, and currency data to character string data using the client locale setting. The conversion setting is only applicable to output conversion and is only visible when currency, numeric, date, or time values are converted to character strings (which is always the case with Query Analyzer). The default for this setting is OFF. "|||It's in

Tools>Options

Menu

Never even thought to look their...

if that's the case...

Why do people mess with ANY settings...I leave'em alone...

God, If I had to remeber what I set...I'd go crazy....

Certainly ups the Mriacle ante.....

as in "God only knows why"|||Blindman .. i believe you have hit the nail on the head ...|||If I did, it's only because it was the last thing that could be suggested!|||I never would have found that in QA. Good job, Blindman.|||Blindman, you deserve millions kisses. You are a superman.
Brett Kaiser: I did not get all what you posted, but I can smell the aggressive way in your reply. Sorry for INCONVENIENCE and thanks anyway.
Thank you all for help, now I can sleep well.|||Originally posted by RaedT
Blindman, you deserve millions kisses. You are a superman.
Brett Kaiser: I did not get all what you posted, but I can smell the aggressive way in your reply. Sorry for INCONVENIENCE and thanks anyway.
Thank you all for help, now I can sleep well.

Whatever...It was a stretch for us to figure out what happend...know why?

Because we never touch that stuff...

But it seems like someone did...why?

My point was exactly that...don't mess with the settings...|||still that agressive tone in your writing...|||I can't believe I got that one. Like I said, you guys had already eliminated everything else.|||Brett Kaiser: Man take a chill pill,
That option is set to ON by default after installation.

Originally posted by Brett Kaiser
Whatever...It was a stretch for us to figure out what happend...know why?

Because we never touch that stuff...

But it seems like someone did...why?

My point was exactly that...don't mess with the settings...|||"That option is set to ON by default after installation."

Really? I don't recall seeing that set to ON before. And I don't recall every changing it.|||RaedT
try and see if there's someone in your entourage
that doesn't like you
and put that option to ON|||That's because it's not....|||If setting that value to ON is somebody's idea of being malicious, they aren't very imaginative. It was probably set by accident, or there was a "reason for it at the time".

global temp table issue with SQL Server 2000

In a SP I create a global temp table as follows:
if not object_id('tempdb..##t') is null drop table ##t
create table ##t (a int, b int)
Then, only on occasion, I get an "invalid column" error when referring to
column b. I think this is due to the fact that I create a table with the sam
e
name -- one that doesn't have column b -- prior to running this SP. However,
this doesn't make any sense since I drop it and create it again...
What I am missing here?Why are you using a global temp table?
Have you ever printed the result of object_id('tempdb..##tt')?
A
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:22351B35-23E4-4C75-954B-4502791E5E4C@.microsoft.com...
> In a SP I create a global temp table as follows:
> if not object_id('tempdb..##t') is null drop table ##t
> create table ##t (a int, b int)
> Then, only on occasion, I get an "invalid column" error when referring to
> column b. I think this is due to the fact that I create a table with the
> same
> name -- one that doesn't have column b -- prior to running this SP.
> However,
> this doesn't make any sense since I drop it and create it again...
> What I am missing here?
>|||Thank you for your reply.
The global temp table is used to interface with another SP. It's global
because this other SP is called in different contexts, including the SP at
hand and a manual query analyser sessions.
I have never printed the return value of the existence test. But here's my
thinking: if the code fails to drop the table for any reason, why doesn't th
e
'create table' statement generate an error?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why are you using a global temp table?
> Have you ever printed the result of object_id('tempdb..##tt')?
> A
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:22351B35-23E4-4C75-954B-4502791E5E4C@.microsoft.com...
>
>|||> The global temp table is used to interface with another SP.
http://www.sommarskog.se/share_data.html|||These are very useful tips in general, but they do not explain my problem in
particular. I looked at the comments regarding the use of temp tables for
parameter passing and the related maintenance drawbacks, but mine is a
different situation. I did not modify any parameters. This article (please
correct if I'm wrong) says that errors will occur when the column definition
changes in either the caller or the callee. And that is perfectly normal
behavior. However, my error does not occur in the callee. This is what I do
in the caller:
drop table ##t
create table ##t (a int, b int)
-- fill ##t with input parameters
exec p -- SP 'p' fills column a in ##t, but it does not use column b
select b from ##t -- this is where the error occurs => 'b is an invalid
column'
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.sommarskog.se/share_data.html
>
>|||Without exact code where we can reproduce the problem, it is tough to
speculate what happens "only on occasion." Is it even remotely possible
that you are reusing the name #tt in more than one place, for tables with
different structures? You may consider using a more useful, legible and
unique name. I would have absolutely no concept of what would reside in a
table called #tt and would find myself doing a grep for that name in other
people's code, if it were even close to an attractive name to use in my
own...
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:6E47FE3B-5525-445D-BB12-05301672B231@.microsoft.com...
> These are very useful tips in general, but they do not explain my problem
> in
> particular. I looked at the comments regarding the use of temp tables for
> parameter passing and the related maintenance drawbacks, but mine is a
> different situation. I did not modify any parameters. This article (please
> correct if I'm wrong) says that errors will occur when the column
> definition
> changes in either the caller or the callee. And that is perfectly normal
> behavior. However, my error does not occur in the callee. This is what I
> do
> in the caller:
> drop table ##t
> create table ##t (a int, b int)
> -- fill ##t with input parameters
> exec p -- SP 'p' fills column a in ##t, but it does not use column b
> select b from ##t -- this is where the error occurs => 'b is an invalid
> column'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||You are absolutely right about naming and '##t' is not the real name of this
table. I just used it to explain my point here.
The callee SP p expects temp table ##t with column 'a'. This SP is called by
caller 1 and caller 2.
Caller 1 does:
drop table ##t
create table ##t (a int, b int)
-- populate ##t
exec p
Caller 2 does:
drop table ##t
create table ##t (a int, c char)
-- populate ##t
exec p
select c from ##t -- this is where the error occurs (column c not recognized
)
I never use caller 1 and caller 2 concurrently.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Without exact code where we can reproduce the problem, it is tough to
> speculate what happens "only on occasion." Is it even remotely possible
> that you are reusing the name #tt in more than one place, for tables with
> different structures? You may consider using a more useful, legible and
> unique name. I would have absolutely no concept of what would reside in a
> table called #tt and would find myself doing a grep for that name in other
> people's code, if it were even close to an attractive name to use in my
> own...
>
>
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:6E47FE3B-5525-445D-BB12-05301672B231@.microsoft.com...
>
>|||Here is the code that will allow reproduction of this error. I take back the
"only on occasion", since you can actually reproduce this behavior all the
time. Create the three SPs below, and then call them one after the other.
-- callee
create procedure [dbo].[callee] as
insert ##t (a)
select 1
-- caller 1
create procedure [dbo].[caller1] as
if not object_id('tempdb..##t') is null drop table ##t
create table ##t (a int, b int)
exec dbo.callee
update ##t set b = 1
-- caller 2
create procedure [dbo].[caller2] as
if not object_id('tempdb..##t') is null drop table ##t
create table ##t (a int, c char)
exec dbo.callee
update ##t set c = 'x'
-- test
exec dbo.caller1
exec dbo.caller2 -- blows up here
/* The code in caller2 that drops the table and creates it again never
executes. In fact, the error happens before any code in caller 2 runs at all
.
It seems as caller 2 does some checking prior to executing, which includes
matching column names to the columns in the tables, if those tables exist. I
f
a table doesn't exist (i.e. you drop ##t before running caller 2), it checks
without errors. */
"Aaron Bertrand [SQL Server MVP]" wrote:

> Without exact code where we can reproduce the problem, it is tough to
> speculate what happens "only on occasion." Is it even remotely possible
> that you are reusing the name #tt in more than one place, for tables with
> different structures? You may consider using a more useful, legible and
> unique name. I would have absolutely no concept of what would reside in a
> table called #tt and would find myself doing a grep for that name in other
> people's code, if it were even close to an attractive name to use in my
> own...
>
>
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:6E47FE3B-5525-445D-BB12-05301672B231@.microsoft.com...
>
>|||And this is exactly what I was suggesting. You have two different stored
procedures, creating a global temp table with the same name, with different
structure.
STOP DOING THAT. Problem solved.
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:E5DBA890-F8EB-4011-9E7F-7C09015AC4B7@.microsoft.com...
> Here is the code that will allow reproduction of this error. I take back
> the
> "only on occasion", since you can actually reproduce this behavior all the
> time. Create the three SPs below, and then call them one after the other.
> -- callee
> create procedure [dbo].[callee] as
> insert ##t (a)
> select 1
> -- caller 1
> create procedure [dbo].[caller1] as
> if not object_id('tempdb..##t') is null drop table ##t
> create table ##t (a int, b int)
> exec dbo.callee
> update ##t set b = 1
> -- caller 2
> create procedure [dbo].[caller2] as
> if not object_id('tempdb..##t') is null drop table ##t
> create table ##t (a int, c char)
> exec dbo.callee
> update ##t set c = 'x'
> -- test
> exec dbo.caller1
> exec dbo.caller2 -- blows up here
>
> /* The code in caller2 that drops the table and creates it again never
> executes. In fact, the error happens before any code in caller 2 runs at
> all.
> It seems as caller 2 does some checking prior to executing, which includes
> matching column names to the columns in the tables, if those tables exist.
> If
> a table doesn't exist (i.e. you drop ##t before running caller 2), it
> checks
> without errors. */|||On Wed, 21 Dec 2005 08:28:02 -0800, Ken Abe wrote:

>Here is the code that will allow reproduction of this error. I take back th
e
>"only on occasion", since you can actually reproduce this behavior all the
>time. Create the three SPs below, and then call them one after the other.
(snip)
Hi Ken,
This has to do with how the compilation process works. The complete
batch is compiled at once, and SQL Server does not attempt to parse
CREATE TABLE, ALTER TABLE and DROP TABLE statements. If a table does not
exist at compile-time, checking the columns is delayed until execution
time (or rather: until the re-compilation that will occur after creating
the table). If the table does exist, the current definition is used to
check the code and create a plan.
The solution is to drop the table when you're done with it, not retain
it and only drop it right before you want to create a new version. This
way, the table won't exist when the stored procedure is called and there
won't be any compilation time errors.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)