Magic Tables with Text Columns

Hi folks,

I need to execute some kind of

select a,b,c into #inserted from inserted

a = int
b = varchar(50)
c = text

Easy so far. But I what I need is to find out whether the audited table contains text(or nText / image) columns or not. If it contains any text columns the statement should automatically be changed to

select a,b into #inserted from inserted

Remember that the trigger must be usable for any table in the database without manually entering the tables structure or the column names.

Best would be fetching the structure fropm the system views (INFORMATIONSCHEMES)

Any suggestions ?

Thanks a lot!

StefanTRIGGERS are built for a table, and as such are a "property" of that table.

The only way would be to build dynamic sql with each trigger for each table.

Meaning you would still have to build a trigger per table anyway.

So why not just build them the way they need to be per table?

And what's with the temp table?

Magic Date?

Anyone know why 1899-12-30 is a special date?
If you put that date with a time in sql server EM and a vb call will return only the time portion...
More background and sample code
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30709In vb, the date is a floating-point where the integer portion is the date. 12/30/1899 is the base date. So if you enter a time only, the integer portion of the datetime will be 0 and the decimal portion will be the time.|||What's EM Built in? If you just enter a time through EM, it'll ne 1899-12-30...

And isn't 1900-01-01 the 0 date for sql server?

SELECT CONVERT(datetime,0,101)

???

And why, if you call sql server from vb through ado, does it pass back just the time component...with no conversion function?|||I wonder if there is a magic time, that just returns the date...

Why the original developer didn't use CONVERT is betond me...|||Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.|||SELECT CONVERT(datetime,-2,101)
gives '1899-12-30 00:00:00.000'|||Originally posted by rnealejr
Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.

What does that mean?

Datetime is stored as a number...4 before the decimal, 4 after...

What do you mean time only?|||So what does this prove?

SELECT DATEADD(d,1,CONVERT(datetime,0.1))

Add 1 what?

What do you mean with no date? There's always a date component?

0 is the default

(Unless of course you add through EM then it's -2)

huh?

And why, if you make a sql call from vb, and the date is 1899-12-30, it only returns the time..VERY bizzare|||Just had the developer do it from an Excel workbook too.

It puts just the time in the Cell with that 1899-12-03 date

It doesn't return the date...bizzaro

Anyone else seen this?|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27101

Hey Brett ... some more with excel problems ...

I believe the reason is that while SQL takes the default date to be 1900-01-01

and other MS applications use 1899-12-30

Here is a link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/whatisdatehowdiditgetthere.asp|||Thanks...forgot all about that thread...

Still doesn't explain you only get the decimal portion of the datetime field though (that's the time component)

Magic Blank Page

Getting the magic blank 2nd page for a report that seems to be well within 1
page (brings back Access memories). Can't seem to figure out how to diag or
what to look for.
--
William Stacey [MVP]This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C61D8A.A9892E70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm with ya pal. I have 5 charts + a header all easily fitting on a =8.5x11.0 with point 25s all the way around and print preview is actual =pages * 2.
Unfortunately, this is one of those engagements where printing is =important.
-- TIM ELLISON
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message =news:%23Bw5dDTHGHA.3532@.TK2MSFTNGP14.phx.gbl...
Getting the magic blank 2nd page for a report that seems to be well =within 1 page (brings back Access memories). Can't seem to figure out how to =diag or what to look for.
-- William Stacey [MVP]

--=_NextPart_000_0015_01C61D8A.A9892E70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'm with ya pal. I have 5 charts =+ a header all easily fitting on a 8.5x11.0 with point 25s all the way around and =print preview is actual pages * 2.
Unfortunately, this is one of those =engagements where printing is important.
-- TIM ELLISON
"William Stacey [MVP]" = wrote in message news:%23Bw5dDTHGHA.=3532@.TK2MSFTNGP14.phx.gbl...Getting the magic blank 2nd page for a report that seems to be well within 1 =page (brings back Access memories). Can't seem to figure out how to =diag or what to look for.-- William Stacey [MVP]

---

--=_NextPart_000_0015_01C61D8A.A9892E70--