Re: Convert help needed desperately

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/14/04 

Date: Fri, 14 May 2004 15:08:15 -0400

Be careful. If you are altering tables, then you could be voiding your
warranty. It's better to make your code work properly than to be fiddling
with table structures. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CL" <cl@mail> wrote in message
news:ejaPrXeOEHA.3712@TK2MSFTNGP10.phx.gbl...
I actually figured it all out -
All I had to do was check the allow nulls - my script worked and then
re-check it. Thanks for the Insert syntax - however I knew that - it was
just trying to get the script to work and it was in the way the table was
set up - all along.
I appreciate all the help.  I wished I would have thought to check the allow
nulls sooner.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OQB$eUeOEHA.2876@TK2MSFTNGP09.phx.gbl...
> You really should pick up a book on T-SQL programming.  Also, it would
have
> helped to know that this a vendor app that you cannot alter.
>
> You can insert a row like this:
>
> insert MyTable values (123, 'blah')
>
> For any columns that have defaults, default values will be entered.  For
any
> nullable columns without defaults, NULL will be entered.  For any
> non-nullable columns that you failed to specify - and for which there is
no
> default - the statement will fail.  Also, you may not specify a value for
> the timestamp column.  Consequently, if your INSERT looked like this:
>
> insert MyTable (MyIntCol, MyStringCol, MyTimestampCol)
> values (123, 'blah', 123456)
>
> ... it will fail.  Make sure to specify column names as I have shown in
the
> second INSERT statement BUT omit columns that are timestamp or identity.
>
> HTH
>
> --
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
>
> "CL" <cl@mail> wrote in message
> news:OsaN8UcOEHA.1276@TK2MSFTNGP11.phx.gbl...
> Okay - I don't think I can simply dump the column - the database structure
> was set by our software vendor - I'm just inserting records so we don't
have
> to hand enter 9000 records.  I got the errors figured out but now I get
this
> one:
>
> Server: Msg 273, Level 16, State 1, Procedure sp_CustomerDump, Line 14
> Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column.
>
> The timestamp does not allow nulls so I'm not sure what this error is
> telling me. I'm so frustrated.
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:upNRFtUOEHA.2996@TK2MSFTNGP12.phx.gbl...
> > No.  The timestamp column is useless to you.  Drop the column.  You can
> add
> > a new column of datatype datetime and populate it with getdate().
> >
> > --
> >    Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON   Canada
> > www.pinnaclepublishing.com/sql
> > .
> > "CL" <cl@mail> wrote in message
> > news:usBP7kUOEHA.3420@TK2MSFTNGP11.phx.gbl...
> > I tried this already as well - this is the Error I get
> >
> > 'Customer' table
> > - Unable to modify table.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> > overflow error converting expression to data type datetime.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >
> >
> > Is my sql server hosed????
> > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > news:#q8WegUOEHA.892@TK2MSFTNGP09.phx.gbl...
> > > Don't use timestamp.  Change the column datatype to datetime and
simply
> > set
> > > the value = getdate().  Timestamp - now known as rowversion - is not
> used
> > > for anything to do with time.
> > >
> > > --
> > >    Tom
> > >
> > > ----------------------------------------------------
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON   Canada
> > > www.pinnaclepublishing.com/sql
> > > .
> > > "CL" <cl@mail> wrote in message
> > > news:uT8y0cUOEHA.3044@TK2MSFTNGP10.phx.gbl...
> > > It is just getting the current system date and placing it a field that
> is
> > of
> > > the timestamp data type.  I'm trying to do an update query and this is
a
> > > required field - and does not accept null
> > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > > news:Own$yBUOEHA.3044@TK2MSFTNGP10.phx.gbl...
> > > > Unlike its name, timestamp has absolutely nothing to do with time.
> What
> > > is
> > > > your actual business requirement?
> > > >
> > > > --
> > > >    Tom
> > > >
> > > > ----------------------------------------------------
> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > > SQL Server MVP
> > > > Columnist, SQL Server Professional
> > > > Toronto, ON   Canada
> > > > www.pinnaclepublishing.com/sql
> > > > .
> > > > "CL" <cl@mail> wrote in message
> > > news:eor1A$TOEHA.556@tk2msftngp13.phx.gbl...
> > > > I have a stored procedure that is throwing an error 291 when I'm
> trying
> > to
> > > > convert datetime to a timestamp.
> > > >
> > > > The table I am inserting into needs to have a timestamp My code in
> > > question
> > > > looks like this:
> > > >
> > > > ,   Convert(timestamp(8), getdate()) as upsize_ts
> > > >
> > > > Can any one help to see what I am doing wrong????
> > > >
> > > > CL
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages
Loading