Float data

Hello.

We have a third party app that's database holds allsorts of info as floats. I have to update records in this database from my source database that stores it's data as decimal(8,2).

so when I update a row with 25.00 it goes into the 3rd party app database as 25 because it's a float. Is it possible to put the data in so it reads 25.00 without changing the datatype from a float?

Thanks.

You should not worry about the format of numbers as they come from a query. Use your application to apply any formatting that is needed.|||The "float" data type does not store non-significant digits. The trailing 0 is non-significant. So the answer is no.

You would have to reformat it to decimal(8,2) when you get it back out of the float.|||

Thanks for the replies, I thought as much after reading BOL but you see my source database is an Ingres database (remember them!) and it's float data type does store the non-significant digits so I was curious to see if SQL could do the same.

Thanks again

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:
>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?
for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:

>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?
|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:

> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:

>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:

> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

First connection fails through Access

I am accessing a linked server through a VPN connection. I am updating
through an Access interface. The first time that I perform an update
on the linked server table, I get an update on linked table error. The
new transaction could not enlist in the specified transaction...
After I click OK, I have update and add records with no errors. Is
there a way that I can trap for this error or avoid it all together?Hi
This sounds like a delay introduced by the VPN means it takes longer to
connect first time through. You may want to try forcing a connection earlier
before you try to update. Also check any VPN settings such as timeouts or
keep alive to see if changing them helps.
John
"dgall" wrote:
> I am accessing a linked server through a VPN connection. I am updating
> through an Access interface. The first time that I perform an update
> on the linked server table, I get an update on linked table error. The
> new transaction could not enlist in the specified transaction...
> After I click OK, I have update and add records with no errors. Is
> there a way that I can trap for this error or avoid it all together?
>

firehouse mode

I just got the following message after trying to update
records in a sql table:
"transaction cannot start while in firehouse mode"
any ideas what the problem is? please advise and thanksWhat does your code look like? My suggestion is to not use recordset
objects in ADO to modify data. Send UPDATE statements instead...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks|||hi Aaron -
I am entering data directly into a table, no query.
>--Original Message--
>What does your code look like? My suggestion is to not
use recordset
>objects in ADO to modify data. Send UPDATE statements
instead...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940101c478a1$06a5e660$a601280a@.phx.gbl...
>> I just got the following message after trying to update
>> records in a sql table:
>> "transaction cannot start while in firehouse mode"
>> any ideas what the problem is? please advise and thanks
>
>.
>|||Use an UPDATE statement in Query Analyzer, do not "enter data directly into
a table"...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:973d01c478a7$ed278c00$a301280a@.phx.gbl...
> hi Aaron -
> I am entering data directly into a table, no query.
> >--Original Message--
> >What does your code look like? My suggestion is to not
> use recordset
> >objects in ADO to modify data. Send UPDATE statements
> instead...
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> >> I just got the following message after trying to update
> >> records in a sql table:
> >> "transaction cannot start while in firehouse mode"
> >> any ideas what the problem is? please advise and thanks
> >
> >
> >.
> >|||SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>|||thanks Jim, I'll try it - Aaron's suggestion did not work.
regards!
>--Original Message--
>SYMPTOMS
>If you attempt to make changes to a row in a table
>displayed in SQL Server Enterprise Manager (SEM), unless
>you scroll down to the end of the table (the last row of
>the table), Enterprise Manager returns the following
>error:
>Cannot start transaction while in firehose mode.
>CAUSE
>When using SEM to display the rows from a table, all rows
>are returned by a "firehose cursor"; however, only the
>rows that are displayed have been processed. A "firehose
>cursor" refers to how the server sends rows to the client
>as fast as the client can process them. Rows that are not
>displayed in the Enterprise Manager are not processed
and,
>therefore, they remain in the network buffer.
>The "Cannot start transaction while in firehose mode"
>error occurs when an OLE-DB provider attempts to perform
a
>join transaction with results pending and while not in an
>updateable cursor mode.
>WORKAROUND
>Scroll all the way down to the last row of the table.
This
>forces all the rows to be processed. You can then edit
the
>row needed and execute the update.
>
>>--Original Message--
>>I just got the following message after trying to update
>>records in a sql table:
>>"transaction cannot start while in firehouse mode"
>>any ideas what the problem is? please advise and thanks
>>.
>.
>|||> Aaron's suggestion did not work.
What does "did not work" mean?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||If you are in SEM, the Jim's response is the most likely cure... Until all
of the rows which you have chosen are displayed, the cursor for displaying
those rows is still open , and the connection can not do anything else...
So scroll to the bottom, then go back up and make your changes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks

Firehose mode

Hi
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"

Can anyone explain what this is and what it means ?

Thanks

David GreenbergDavid Greenberg (davidgr@.iba.org.il) writes:

Quote:

Originally Posted by

I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Can anyone explain what this is and what it means ?

It means that you should open a Query Analyzer window and write an UPDATE
statement.

My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "David Greenberg" <davidgr@.iba.org.ilnapisal w wiadomosci
news:469C9971.7050205@.iba.org.il...

Quote:

Originally Posted by

Hi
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Can anyone explain what this is and what it means ?

I don't know exactly what it means. But I usualay solve this problem opening
not whole table but only e.q. TOP 10

br
Bober|||Erland,

Doesn't FIREHOSE mode mean that EM has read the records in a "forward only
cursor" and has no means of updating? Hence the name "firehouse" which is a
rapid stream of data only going 1 way.

Oscar

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99707AD4B7BCCYazorman@.127.0.0.1...

Quote:

Originally Posted by

David Greenberg (davidgr@.iba.org.il) writes:

Quote:

Originally Posted by

>I'm in Enterprise Manager and have made a change to a row.
>When I try to update I get a message that says:
>Transaction cannot start, Firehose mode"
>>
>Can anyone explain what this is and what it means ?

>
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||http://support.microsoft.com/kb/286199
On Jul 23, 8:55 pm, "Oscar Santiesteban"
<o_santieste...@.bellsouth.netwrote:

Quote:

Originally Posted by

Erland,
>
Doesn't FIREHOSE mode mean that EM has read the records in a "forward only
cursor" and has no means of updating? Hence the name "firehouse" which is a
rapid stream of data only going 1 way.
>
Oscar
>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns99707AD4B7BCCYazorman@.127.0.0.1...
>
>
>

Quote:

Originally Posted by

David Greenberg (davi...@.iba.org.il) writes:

Quote:

Originally Posted by

I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Can anyone explain what this is and what it means ?

>

Quote:

Originally Posted by

It means that you should open a Query Analyzer window and write an UPDATE
statement.

>

Quote:

Originally Posted by

My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -

>
- Show quoted text -

|||Jason Lepack (jlepack@.gmail.com) writes:

Quote:

Originally Posted by

http://support.microsoft.com/kb/286199

Ah, that was a very special situation. Thanks for the link, Jason.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx