"Decimals" Default as Integers?

I just came across an issue, and I can't believe that this is the first
time I am finding this... if it is not a problem.
When I add a Decimal column to a table, it sets it as (18,0), which has
a scale of 0.
So if you add a decimal and try to add the value 1.1 to the field, it
rounds it to 1.
Is this right? Why would the default settings for a "Decimal" have no
decimals?Zero is the smallest permitted value for scale so it seems like a
reasonable choice for a default. Remember that DECIMAL is synonymous
with NUMERIC.
Why do you care what the default is? If you want to store decimals then
set the scale to something other than zero.
David Portas
SQL Server MVP
--|||I guess it makes more sense if you consider it as a NUMERIC and not a
DECIMAL.
But it still seems odd that a default Decimal can not be a decminal.
As long as I know this, I make sure to set the scale each time I use it.|||> Why would the default settings for a "Decimal" have no decimals?
Because the ANSI SQL standard says so:
"If a <scale> is omitted, then a <scale> of 0 is implicit."
Razvan

"Decimals" Default as Integers?

I just came across an issue, and I can't believe that this is the first
time I am finding this... if it is not a problem.
When I add a Decimal column to a table, it sets it as (18,0), which has
a scale of 0.
So if you add a decimal and try to add the value 1.1 to the field, it
rounds it to 1.
Is this right? Why would the default settings for a "Decimal" have no
decimals?Zero is the smallest permitted value for scale so it seems like a
reasonable choice for a default. Remember that DECIMAL is synonymous
with NUMERIC.
Why do you care what the default is? If you want to store decimals then
set the scale to something other than zero.
--
David Portas
SQL Server MVP
--|||I guess it makes more sense if you consider it as a NUMERIC and not a
DECIMAL.
But it still seems odd that a default Decimal can not be a decminal.
As long as I know this, I make sure to set the scale each time I use it.|||> Why would the default settings for a "Decimal" have no decimals?
Because the ANSI SQL standard says so:
"If a <scale> is omitted, then a <scale> of 0 is implicit."
Razvan

"Decimals" Default as Integers?

I just came across an issue, and I can't believe that this is the first
time I am finding this... if it is not a problem.
When I add a Decimal column to a table, it sets it as (18,0), which has
a scale of 0.
So if you add a decimal and try to add the value 1.1 to the field, it
rounds it to 1.
Is this right? Why would the default settings for a "Decimal" have no
decimals?
Zero is the smallest permitted value for scale so it seems like a
reasonable choice for a default. Remember that DECIMAL is synonymous
with NUMERIC.
Why do you care what the default is? If you want to store decimals then
set the scale to something other than zero.
David Portas
SQL Server MVP
|||I guess it makes more sense if you consider it as a NUMERIC and not a
DECIMAL.
But it still seems odd that a default Decimal can not be a decminal.
As long as I know this, I make sure to set the scale each time I use it.
|||> Why would the default settings for a "Decimal" have no decimals?
Because the ANSI SQL standard says so:
"If a <scale> is omitted, then a <scale> of 0 is implicit."
Razvan

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.
4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim