SQL Newie needs help

Question

SQL Newie needs help

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Again, hi Jigman,

quote:
While working in Enterprise Manager I try to update some fields, and then when i go to close the table i get the message:
'Transaction cannot start while in firehose mode'
What the????

no answer to your question.

Maybe one hint for you.

I guess you can attract more members on your issues when you start a new thread instead of replying to your own rather old one. I guess most think it's still about the same issues as in the first post.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

quote:
I was inserting data and was stopping every now and then to c if it worked. My last check was at ID 51. After ID 60 I stopped to check again. After I reopend my database I was alarmed because I thought the data wasn't there anymore. What had happened though was it chucked IDs 52-60 up between IDs 14 and 15. E.g 13,14,52,53,...60,15. I have NO IDEA why it did this and at the time I didnt know it had done this. I couldnt see the fields so I assumed they werent stored for some reason. So I added record 52 again, closed the table, opened it again, and couldnt c it, so i thought again it didnt work but once again it moved it higher where I couldnt c it. I repeated again as all newbies do 🙂 Once I figured out what had happened I have 3 records set as 52. Now ID is meant to b the primary key but I dont have it set as it as such. So when I try to delete the 2 of ID 52 fields it tells me 'Key column information is insignificant of incorrect. Too many rows were affected by update.'

as I have written before.

Unless otherwise forced to, table data is not stored in a sorted manner. It's kind of nondeterministic. You can't tell for sure you get the same ordering when issuing several SELECT statements.

quote:
- Unable to create index 'PK_KAVPRICEBOOK2'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '52'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Making a column PrimaryKey implies setting a NOT NULL and a UNIQUE constrain on this field. In your case this UNIQUE constrain is violated like the error message says

To find out the duplicate values try something like this

SELECT id

FROM your_table

GROUP BY id

HAVING COUNT(*) > 1

quote:
Please tell me I don't have to start this table all over again.

I don't think so!

Good luck!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ritch Ten Centuries Points: 1044 More actions · Answer 3

Hi Jigman,

quote:
'Transaction cannot start while in firehose mode'

One of our developers had this when updating a table in EM

The only thing I could find in BOL relating to this was...

quote:
The client cannot send any other Transact-SQL statements on that connection until the application has either processed all the rows returned by SQL Server or sent SQL Server a request to cancel the rest of the results. No updates can be done on the connection until all the results have been processed; the result sets are read-only.
Note Firehose cursor is an obsolete term for default result sets.

Laters

*I didn't do anything it just got complicated*

"I didn't do anything it just got complicated" - M Edwards

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 4

Hi Frank!

Now, this I really, really liked 'cause it's so darn true:

quote:
Why C++ is like teenage sex. It is on everyone's mind all the time.
Everyone talks about it all the time.
Everyone thinks everyone else is doing it.
Almost no one is really doing it.
The few who are doing it are:
Doing it poorly.
Sure it will be better next time.
Not practicing it safely.

All the best!

Dale

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Dale,

quote:
Now, this I really, really liked 'cause it's so darn true:

good to hear ?!? you again. I was beginning to worry

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 6

No problems, Frank. I'm back from a vacation in Boston. I'll be posting again this week. Thanks for missing me.

This looked like a good thread. I'm with you on the Books Online thing. That's exactly how I learned SQL and felt it was more thorough than taking a training class 'cause I had to learn myself. Fortunately, my boss let me print out all the materials and take some TIME to look at it and study.

All the best,

Dale

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

JigMan SSC-Addicted Points: 451 More actions · Answer 7

Once again, I would like to thank everyone for there help and patience. I was able to avoid the problems by using the SQL functions within enterprise manager to insert and delete the data. If anyone else has the problem, i'm sorry but I still don't know why I was getting that firehose error.

sqlsoulman SSC Enthusiast Points: 146 More actions · Answer 8

Hey, I'm with Frank on this one again. You should post that in another thread. I remember having that error before and I believe it had to do with record locking or concurrent sessions. Anyway, I'm at home at 9pm and too tired to think. If you post the thread tomorrow, I will do some research on it for you from my own experience.