More questions about porting from MySQL to MS SQL

1) In several tables, in my MySQL version, I created columns using
something like the following:

`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,

This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)

2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.

3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.

Thanks

Ted

.

Relevant Pages

  • Re: DATE formatting and TIME zones - how to deal with this hell?
    ... I must support MySQL -AND- SQLite. ... Is it needed to convert date to timestamp? ... Should I generate date in SQL or in PHP? ...
    (comp.lang.php)
  • Re: LINQ Where 1=0
    ... The LinqDataSource control stores values for all primary keys ... Before LINQ to SQL updates or deletes data, it checks the values in view ... If the underlying data source contains a timestamp field that is ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: More questions about porting from MySQL to MS SQL
    ... `ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update ... I learned the hard way that MS SQL does not like ... FUNCTION and CREATE VIEW need to be the first statement in a script. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Cannot edit data in a Query
    ... I would check the SQL Database table and make sure it has a primary key AND that it has a timestamp field. ... TimeStamp is a data type that exposes automatically generated, unique binary numbers within a database. ...
    (microsoft.public.access.queries)
  • Re: Form error????
    ... I stated below that a TimeStamp was a GUID. ... Kind of confusing now that (in SQL 2008) there is a new data type/alias ... of a linked SQL Server table ...
    (microsoft.public.access.forms)