Re: Displaying thousandths in SQL Server datetime fields

Or "rollback tran" if you do not want to commit it :) But do not neglect doing either one of "rollback tran" or "commit tran" otherwise you may have issues. To detect if you have any open tran, run "DBCC Opentran".

I know, subject has changed. CUT! ;)

--
Ekrem Önsoy



"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message news:%23ms30B16HHA.5012@xxxxxxxxxxxxxxxxxxxxxxx
Andrew feels is the worst way to do so. (Makes one wonder why MS makes the
worst way so easy to get to!)

Yes they made it way too easy. :)

I would still defend the Open Table view as being useful. It lets you hack
existing records and enter new ones easily. Beats typing lots of SQL updates
and inserts, at least for me. But it clearly has its limitations, too, and
that's where the query editor can save the day with more raw power.

Yes Hack is the right word. This grid is a live editing tool and can cause blocking in the system. I have seen DBA's bring the production apps to a halt because they were "hacking" and locked up the table without knowing it. I have also seen times (many in fact) where the DBA was simply scrolling thru looking at rows and they accidently hit a key and changed a row. If they didn't happen to see what they changed you can be in a world of hurt.

By the way if you right click on any table in SSMS you will get a menu choice to generate Insert, Update, Delete and Select statements. You simply fill in the values. You do not have to type a single column or table name and there are NO typos for names either. And in the Query Editor you can add a BEGIN TRAN at the beginning so if you ran the statement and the rows affected wasn't what you expected you can roll it back and try again. Just remember to actually commit it when done:).

Have fun
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Charley" <Charley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C96F8385-3EDE-4D9D-97C5-53F8612B7F6C@xxxxxxxxxxxxxxxx
Andrew--

Thanks for the info. One tidbit in what you wrote got me over the hump:
Query editor. I thought I'd document this in case someone else runs across
this in the future.

Within Microsoft SQL Server Management Studio, I had been examining my
tables by browsing down to the table in question in the Object Explorer pane,
then right-clicking and choosing Open Table. That brings up the whole table
in a sort of spread***-like format where you can edit and play--and which
Andrew feels is the worst way to do so. (Makes one wonder why MS makes the
worst way so easy to get to!) If I needed a more refined selection, I could
click the little SQL toolbar button to get a SQL editing pane above my
"spread*** view" results pane. In that view, the fractional seconds seem to
be always suppressed.

With Andrew's clue in hand, I discovered the New Query button in the
toolbar. At a quick glance this takes you to a place similar to where Open
Table goes, but it's not. The key thing for me at the moment, is that it
displays the fractional part (the thousandths) of datetime seconds. (It also
sizes the columns more intelligently.)

BTW, my earlier empirical evidence about an application misbehaving because
of not seeing subsecond differences in datetime values may be a design flaw
in that program! Now seeing the full precision, I see four separate records
that app wrote with identical datetime values, probably because this PC is so
stinkin' fast! The app was depending on them being slightly different. A bit
of irony.

I mentioned before typing a full precision value into the Open Table view
and then having the thousandths disappear. Once I saw those records in the
New Query (or query editor) view, the values I had typed were there all along.

I would still defend the Open Table view as being useful. It lets you hack
existing records and enter new ones easily. Beats typing lots of SQL updates
and inserts, at least for me. But it clearly has its limitations, too, and
that's where the query editor can save the day with more raw power.


.