IAS Logging

Hello MVP's,
I want to store my IAS logs in SQL2000 but can't remember the stored
procedure script to first set up the database, can anybody help?
--
SteveI don't think there is any "one" script but there is the
sample from the docs - is that what you are thinking of? The
script for creating IASODBC database?
You can find that at:
http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
-Sue
On Tue, 25 Apr 2006 11:57:01 -0700, Steven
<Steven@.discussions.microsoft.com> wrote:
>Hello MVP's,
>I want to store my IAS logs in SQL2000 but can't remember the stored
>procedure script to first set up the database, can anybody help?|||I'll try it, thanks.
--
Steve
"Sue Hoegemeier" wrote:
> I don't think there is any "one" script but there is the
> sample from the docs - is that what you are thinking of? The
> script for creating IASODBC database?
> You can find that at:
> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> -Sue
> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> <Steven@.discussions.microsoft.com> wrote:
> >Hello MVP's,
> >
> >I want to store my IAS logs in SQL2000 but can't remember the stored
> >procedure script to first set up the database, can anybody help?
>|||Thanks Sue, with a few minor edits it works great. However the timestamp
field is 4 hours ahead of actual time, any idea why? The system clock is
correct. Thanks again!
--
Steve
"Sue Hoegemeier" wrote:
> I don't think there is any "one" script but there is the
> sample from the docs - is that what you are thinking of? The
> script for creating IASODBC database?
> You can find that at:
> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> -Sue
> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> <Steven@.discussions.microsoft.com> wrote:
> >Hello MVP's,
> >
> >I want to store my IAS logs in SQL2000 but can't remember the stored
> >procedure script to first set up the database, can anybody help?
>|||Hey Steve,
I can't remember specifically how it works with radius but
alot of system logging is done using UCT or GMT and isn't
specific to your own time zone. I'd suspect it's related to
that.
-Sue
On Thu, 27 Apr 2006 15:08:02 -0700, Steven
<Steven@.discussions.microsoft.com> wrote:
>Thanks Sue, with a few minor edits it works great. However the timestamp
>field is 4 hours ahead of actual time, any idea why? The system clock is
>correct. Thanks again!|||Where do you live? Timestamps are usually UTC.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Steven" <Steven@.discussions.microsoft.com> wrote in message
news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> Thanks Sue, with a few minor edits it works great. However the timestamp
> field is 4 hours ahead of actual time, any idea why? The system clock is
> correct. Thanks again!
> --
> Steve
>
> "Sue Hoegemeier" wrote:
>> I don't think there is any "one" script but there is the
>> sample from the docs - is that what you are thinking of? The
>> script for creating IASODBC database?
>> You can find that at:
>> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
>> -Sue
>> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
>> <Steven@.discussions.microsoft.com> wrote:
>> >Hello MVP's,
>> >
>> >I want to store my IAS logs in SQL2000 but can't remember the stored
>> >procedure script to first set up the database, can anybody help?
>>|||Hi Roger,
Minneapolis, MN. Is there a way to format it to CDT?
--
Steve
"Roger Wolter[MSFT]" wrote:
> Where do you live? Timestamps are usually UTC.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> > Thanks Sue, with a few minor edits it works great. However the timestamp
> > field is 4 hours ahead of actual time, any idea why? The system clock is
> > correct. Thanks again!
> > --
> > Steve
> >
> >
> > "Sue Hoegemeier" wrote:
> >
> >> I don't think there is any "one" script but there is the
> >> sample from the docs - is that what you are thinking of? The
> >> script for creating IASODBC database?
> >> You can find that at:
> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> >>
> >> -Sue
> >>
> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> >> <Steven@.discussions.microsoft.com> wrote:
> >>
> >> >Hello MVP's,
> >> >
> >> >I want to store my IAS logs in SQL2000 but can't remember the stored
> >> >procedure script to first set up the database, can anybody help?
> >>
> >>
>
>|||Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
years.
Try something like this:
CREATE TABLE TSTest (TS datetime)
INSERT INTO TSTest VALUES (getutcdate())
SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
TSTest
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Steven" <Steven@.discussions.microsoft.com> wrote in message
news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
> Hi Roger,
> Minneapolis, MN. Is there a way to format it to CDT?
> --
> Steve
>
> "Roger Wolter[MSFT]" wrote:
>> Where do you live? Timestamps are usually UTC.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Steven" <Steven@.discussions.microsoft.com> wrote in message
>> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
>> > Thanks Sue, with a few minor edits it works great. However the
>> > timestamp
>> > field is 4 hours ahead of actual time, any idea why? The system clock
>> > is
>> > correct. Thanks again!
>> > --
>> > Steve
>> >
>> >
>> > "Sue Hoegemeier" wrote:
>> >
>> >> I don't think there is any "one" script but there is the
>> >> sample from the docs - is that what you are thinking of? The
>> >> script for creating IASODBC database?
>> >> You can find that at:
>> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
>> >>
>> >> -Sue
>> >>
>> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
>> >> <Steven@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello MVP's,
>> >> >
>> >> >I want to store my IAS logs in SQL2000 but can't remember the stored
>> >> >procedure script to first set up the database, can anybody help?
>> >>
>> >>
>>|||Live near Anoka, work downtown Mpls.
Wanna do me a big favor... put that in a .sql or point me to a KB Article.
hmm, maybe that will work in sql analyzer... Would rather edit existing
timestamp field in existing table, that possible?
Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
LOL ;-)
--
Steve
PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
__________________________________________________________________
"Roger Wolter[MSFT]" wrote:
> Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
> years.
> Try something like this:
> CREATE TABLE TSTest (TS datetime)
> INSERT INTO TSTest VALUES (getutcdate())
> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
> TSTest
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
> > Hi Roger,
> >
> > Minneapolis, MN. Is there a way to format it to CDT?
> > --
> > Steve
> >
> >
> > "Roger Wolter[MSFT]" wrote:
> >
> >> Where do you live? Timestamps are usually UTC.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> >> > Thanks Sue, with a few minor edits it works great. However the
> >> > timestamp
> >> > field is 4 hours ahead of actual time, any idea why? The system clock
> >> > is
> >> > correct. Thanks again!
> >> > --
> >> > Steve
> >> >
> >> >
> >> > "Sue Hoegemeier" wrote:
> >> >
> >> >> I don't think there is any "one" script but there is the
> >> >> sample from the docs - is that what you are thinking of? The
> >> >> script for creating IASODBC database?
> >> >> You can find that at:
> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> >> >>
> >> >> -Sue
> >> >>
> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> >> >> <Steven@.discussions.microsoft.com> wrote:
> >> >>
> >> >> >Hello MVP's,
> >> >> >
> >> >> >I want to store my IAS logs in SQL2000 but can't remember the stored
> >> >> >procedure script to first set up the database, can anybody help?
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Try something like:
UPDATE TSTest SET TS = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
GETDATE()), TS)
to change the times
Diamond Lake is just East of 35W north of Crosstown.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Steven" <Steven@.discussions.microsoft.com> wrote in message
news:B310FC38-DDCB-47B6-A9A8-021D2F0ED2ED@.microsoft.com...
> Live near Anoka, work downtown Mpls.
> Wanna do me a big favor... put that in a .sql or point me to a KB
> Article.
> hmm, maybe that will work in sql analyzer... Would rather edit existing
> timestamp field in existing table, that possible?
> Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
> LOL ;-)
> --
> Steve
> PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
> __________________________________________________________________
>
> "Roger Wolter[MSFT]" wrote:
>> Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
>> years.
>> Try something like this:
>> CREATE TABLE TSTest (TS datetime)
>> INSERT INTO TSTest VALUES (getutcdate())
>> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
>> TSTest
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Steven" <Steven@.discussions.microsoft.com> wrote in message
>> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
>> > Hi Roger,
>> >
>> > Minneapolis, MN. Is there a way to format it to CDT?
>> > --
>> > Steve
>> >
>> >
>> > "Roger Wolter[MSFT]" wrote:
>> >
>> >> Where do you live? Timestamps are usually UTC.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> Use of included script samples are subject to the terms specified at
>> >> http://www.microsoft.com/info/cpyright.htm
>> >>
>> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
>> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
>> >> > Thanks Sue, with a few minor edits it works great. However the
>> >> > timestamp
>> >> > field is 4 hours ahead of actual time, any idea why? The system
>> >> > clock
>> >> > is
>> >> > correct. Thanks again!
>> >> > --
>> >> > Steve
>> >> >
>> >> >
>> >> > "Sue Hoegemeier" wrote:
>> >> >
>> >> >> I don't think there is any "one" script but there is the
>> >> >> sample from the docs - is that what you are thinking of? The
>> >> >> script for creating IASODBC database?
>> >> >> You can find that at:
>> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
>> >> >>
>> >> >> -Sue
>> >> >>
>> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
>> >> >> <Steven@.discussions.microsoft.com> wrote:
>> >> >>
>> >> >> >Hello MVP's,
>> >> >> >
>> >> >> >I want to store my IAS logs in SQL2000 but can't remember the
>> >> >> >stored
>> >> >> >procedure script to first set up the database, can anybody help?
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Roger,
Well that gives me exactly what I want but I do I use it? I view the results
of the IAS database through an htm page, the timestamp field needs to look
like what you've given me. Thanks for all your help.
--
Steve
"Roger Wolter[MSFT]" wrote:
> Try something like:
> UPDATE TSTest SET TS = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
> GETDATE()), TS)
> to change the times
>
> Diamond Lake is just East of 35W north of Crosstown.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:B310FC38-DDCB-47B6-A9A8-021D2F0ED2ED@.microsoft.com...
> > Live near Anoka, work downtown Mpls.
> >
> > Wanna do me a big favor... put that in a .sql or point me to a KB
> > Article.
> > hmm, maybe that will work in sql analyzer... Would rather edit existing
> > timestamp field in existing table, that possible?
> >
> > Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
> >
> > LOL ;-)
> > --
> > Steve
> >
> > PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
> > __________________________________________________________________
> >
> >
> > "Roger Wolter[MSFT]" wrote:
> >
> >> Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
> >> years.
> >>
> >> Try something like this:
> >>
> >> CREATE TABLE TSTest (TS datetime)
> >>
> >> INSERT INTO TSTest VALUES (getutcdate())
> >>
> >> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
> >> TSTest
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
> >> > Hi Roger,
> >> >
> >> > Minneapolis, MN. Is there a way to format it to CDT?
> >> > --
> >> > Steve
> >> >
> >> >
> >> > "Roger Wolter[MSFT]" wrote:
> >> >
> >> >> Where do you live? Timestamps are usually UTC.
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >> Use of included script samples are subject to the terms specified at
> >> >> http://www.microsoft.com/info/cpyright.htm
> >> >>
> >> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> >> >> > Thanks Sue, with a few minor edits it works great. However the
> >> >> > timestamp
> >> >> > field is 4 hours ahead of actual time, any idea why? The system
> >> >> > clock
> >> >> > is
> >> >> > correct. Thanks again!
> >> >> > --
> >> >> > Steve
> >> >> >
> >> >> >
> >> >> > "Sue Hoegemeier" wrote:
> >> >> >
> >> >> >> I don't think there is any "one" script but there is the
> >> >> >> sample from the docs - is that what you are thinking of? The
> >> >> >> script for creating IASODBC database?
> >> >> >> You can find that at:
> >> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> >> >> >>
> >> >> >> -Sue
> >> >> >>
> >> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> >> >> >> <Steven@.discussions.microsoft.com> wrote:
> >> >> >>
> >> >> >> >Hello MVP's,
> >> >> >> >
> >> >> >> >I want to store my IAS logs in SQL2000 but can't remember the
> >> >> >> >stored
> >> >> >> >procedure script to first set up the database, can anybody help?
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||So I should be able to do something like this'
UPDATE IASODBC SET [timestamp] = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
GETDATE()), [timestamp])
in my origianl IAS database'
Steve
"Roger Wolter[MSFT]" wrote:
> Try something like:
> UPDATE TSTest SET TS = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
> GETDATE()), TS)
> to change the times
>
> Diamond Lake is just East of 35W north of Crosstown.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:B310FC38-DDCB-47B6-A9A8-021D2F0ED2ED@.microsoft.com...
> > Live near Anoka, work downtown Mpls.
> >
> > Wanna do me a big favor... put that in a .sql or point me to a KB
> > Article.
> > hmm, maybe that will work in sql analyzer... Would rather edit existing
> > timestamp field in existing table, that possible?
> >
> > Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
> >
> > LOL ;-)
> > --
> > Steve
> >
> > PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
> > __________________________________________________________________
> >
> >
> > "Roger Wolter[MSFT]" wrote:
> >
> >> Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
> >> years.
> >>
> >> Try something like this:
> >>
> >> CREATE TABLE TSTest (TS datetime)
> >>
> >> INSERT INTO TSTest VALUES (getutcdate())
> >>
> >> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
> >> TSTest
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
> >> > Hi Roger,
> >> >
> >> > Minneapolis, MN. Is there a way to format it to CDT?
> >> > --
> >> > Steve
> >> >
> >> >
> >> > "Roger Wolter[MSFT]" wrote:
> >> >
> >> >> Where do you live? Timestamps are usually UTC.
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >> Use of included script samples are subject to the terms specified at
> >> >> http://www.microsoft.com/info/cpyright.htm
> >> >>
> >> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> >> >> > Thanks Sue, with a few minor edits it works great. However the
> >> >> > timestamp
> >> >> > field is 4 hours ahead of actual time, any idea why? The system
> >> >> > clock
> >> >> > is
> >> >> > correct. Thanks again!
> >> >> > --
> >> >> > Steve
> >> >> >
> >> >> >
> >> >> > "Sue Hoegemeier" wrote:
> >> >> >
> >> >> >> I don't think there is any "one" script but there is the
> >> >> >> sample from the docs - is that what you are thinking of? The
> >> >> >> script for creating IASODBC database?
> >> >> >> You can find that at:
> >> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> >> >> >>
> >> >> >> -Sue
> >> >> >>
> >> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> >> >> >> <Steven@.discussions.microsoft.com> wrote:
> >> >> >>
> >> >> >> >Hello MVP's,
> >> >> >> >
> >> >> >> >I want to store my IAS logs in SQL2000 but can't remember the
> >> >> >> >stored
> >> >> >> >procedure script to first set up the database, can anybody help?
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||this did it:
UPDATE accounting_data SET [timestamp] = DATEADD (Hour, DATEDIFF(Hour,
GETUTCDATE(),
GETDATE()), [timestamp])
THANKS FOR ALL YOUR HELP, both of you!!!
PS what dows the [ ] represent?
--
Steve
"Roger Wolter[MSFT]" wrote:
> Try something like:
> UPDATE TSTest SET TS = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
> GETDATE()), TS)
> to change the times
>
> Diamond Lake is just East of 35W north of Crosstown.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:B310FC38-DDCB-47B6-A9A8-021D2F0ED2ED@.microsoft.com...
> > Live near Anoka, work downtown Mpls.
> >
> > Wanna do me a big favor... put that in a .sql or point me to a KB
> > Article.
> > hmm, maybe that will work in sql analyzer... Would rather edit existing
> > timestamp field in existing table, that possible?
> >
> > Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
> >
> > LOL ;-)
> > --
> > Steve
> >
> > PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
> > __________________________________________________________________
> >
> >
> > "Roger Wolter[MSFT]" wrote:
> >
> >> Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
> >> years.
> >>
> >> Try something like this:
> >>
> >> CREATE TABLE TSTest (TS datetime)
> >>
> >> INSERT INTO TSTest VALUES (getutcdate())
> >>
> >> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
> >> TSTest
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
> >> > Hi Roger,
> >> >
> >> > Minneapolis, MN. Is there a way to format it to CDT?
> >> > --
> >> > Steve
> >> >
> >> >
> >> > "Roger Wolter[MSFT]" wrote:
> >> >
> >> >> Where do you live? Timestamps are usually UTC.
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >> Use of included script samples are subject to the terms specified at
> >> >> http://www.microsoft.com/info/cpyright.htm
> >> >>
> >> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> >> >> > Thanks Sue, with a few minor edits it works great. However the
> >> >> > timestamp
> >> >> > field is 4 hours ahead of actual time, any idea why? The system
> >> >> > clock
> >> >> > is
> >> >> > correct. Thanks again!
> >> >> > --
> >> >> > Steve
> >> >> >
> >> >> >
> >> >> > "Sue Hoegemeier" wrote:
> >> >> >
> >> >> >> I don't think there is any "one" script but there is the
> >> >> >> sample from the docs - is that what you are thinking of? The
> >> >> >> script for creating IASODBC database?
> >> >> >> You can find that at:
> >> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> >> >> >>
> >> >> >> -Sue
> >> >> >>
> >> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> >> >> >> <Steven@.discussions.microsoft.com> wrote:
> >> >> >>
> >> >> >> >Hello MVP's,
> >> >> >> >
> >> >> >> >I want to store my IAS logs in SQL2000 but can't remember the
> >> >> >> >stored
> >> >> >> >procedure script to first set up the database, can anybody help?
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Ok, forget all that other stuff... Here's what I have for the day... signing
off for now.
When I run the update against the table and existing data it changes it
exactly the way I want to see it but:
1.) it doesn't stick, "new" data is still UTC. Was this meant to be a result
query?
2.) When I run the web wizard it displays the timestamp in military time
even when the data is seen as CDT exactly the way I want it.
3.) This may be non-related, I've run the web wizard a few times and
sometimes it errors out, but only when i select the "When the SQL Server data
changes", the error is SQL-DMO ODBC SQLState 42000 Error 170 line 1 incorrect
syntec 'C'.
--
Steve
"Roger Wolter[MSFT]" wrote:
> Try something like:
> UPDATE TSTest SET TS = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
> GETDATE()), TS)
> to change the times
>
> Diamond Lake is just East of 35W north of Crosstown.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:B310FC38-DDCB-47B6-A9A8-021D2F0ED2ED@.microsoft.com...
> > Live near Anoka, work downtown Mpls.
> >
> > Wanna do me a big favor... put that in a .sql or point me to a KB
> > Article.
> > hmm, maybe that will work in sql analyzer... Would rather edit existing
> > timestamp field in existing table, that possible?
> >
> > Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
> >
> > LOL ;-)
> > --
> > Steve
> >
> > PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
> > __________________________________________________________________
> >
> >
> > "Roger Wolter[MSFT]" wrote:
> >
> >> Cool! What part of Minneapolis? I lived near Diamond Lake for about 20
> >> years.
> >>
> >> Try something like this:
> >>
> >> CREATE TABLE TSTest (TS datetime)
> >>
> >> INSERT INTO TSTest VALUES (getutcdate())
> >>
> >> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS) FROM
> >> TSTest
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
> >> > Hi Roger,
> >> >
> >> > Minneapolis, MN. Is there a way to format it to CDT?
> >> > --
> >> > Steve
> >> >
> >> >
> >> > "Roger Wolter[MSFT]" wrote:
> >> >
> >> >> Where do you live? Timestamps are usually UTC.
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >> Use of included script samples are subject to the terms specified at
> >> >> http://www.microsoft.com/info/cpyright.htm
> >> >>
> >> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> >> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
> >> >> > Thanks Sue, with a few minor edits it works great. However the
> >> >> > timestamp
> >> >> > field is 4 hours ahead of actual time, any idea why? The system
> >> >> > clock
> >> >> > is
> >> >> > correct. Thanks again!
> >> >> > --
> >> >> > Steve
> >> >> >
> >> >> >
> >> >> > "Sue Hoegemeier" wrote:
> >> >> >
> >> >> >> I don't think there is any "one" script but there is the
> >> >> >> sample from the docs - is that what you are thinking of? The
> >> >> >> script for creating IASODBC database?
> >> >> >> You can find that at:
> >> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
> >> >> >>
> >> >> >> -Sue
> >> >> >>
> >> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
> >> >> >> <Steven@.discussions.microsoft.com> wrote:
> >> >> >>
> >> >> >> >Hello MVP's,
> >> >> >> >
> >> >> >> >I want to store my IAS logs in SQL2000 but can't remember the
> >> >> >> >stored
> >> >> >> >procedure script to first set up the database, can anybody help?
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Updating the data will only work for the data that's in the table when you
run the update. If you want to see new data with the correct timezone, I
would recommend either using the query I started with or if that's too
messy, use the query to define a view and do your reporting against the
view.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Steven" <Steven@.discussions.microsoft.com> wrote in message
news:970CA73C-CC65-4AF8-9DFE-85E5DCEA973D@.microsoft.com...
> Ok, forget all that other stuff... Here's what I have for the day...
> signing
> off for now.
> When I run the update against the table and existing data it changes it
> exactly the way I want to see it but:
> 1.) it doesn't stick, "new" data is still UTC. Was this meant to be a
> result
> query?
> 2.) When I run the web wizard it displays the timestamp in military time
> even when the data is seen as CDT exactly the way I want it.
> 3.) This may be non-related, I've run the web wizard a few times and
> sometimes it errors out, but only when i select the "When the SQL Server
> data
> changes", the error is SQL-DMO ODBC SQLState 42000 Error 170 line 1
> incorrect
> syntec 'C'.
> --
> Steve
>
> "Roger Wolter[MSFT]" wrote:
>> Try something like:
>> UPDATE TSTest SET TS = DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(),
>> GETDATE()), TS)
>> to change the times
>>
>> Diamond Lake is just East of 35W north of Crosstown.
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Steven" <Steven@.discussions.microsoft.com> wrote in message
>> news:B310FC38-DDCB-47B6-A9A8-021D2F0ED2ED@.microsoft.com...
>> > Live near Anoka, work downtown Mpls.
>> >
>> > Wanna do me a big favor... put that in a .sql or point me to a KB
>> > Article.
>> > hmm, maybe that will work in sql analyzer... Would rather edit existing
>> > timestamp field in existing table, that possible?
>> >
>> > Note: I am MCSA &MCSE so my DBA skills are a bit rusty.
>> >
>> > LOL ;-)
>> > --
>> > Steve
>> >
>> > PS Diamond Lake, why does that ring a bell? There are 2 in Hennepin.
>> > __________________________________________________________________
>> >
>> >
>> > "Roger Wolter[MSFT]" wrote:
>> >
>> >> Cool! What part of Minneapolis? I lived near Diamond Lake for about
>> >> 20
>> >> years.
>> >>
>> >> Try something like this:
>> >>
>> >> CREATE TABLE TSTest (TS datetime)
>> >>
>> >> INSERT INTO TSTest VALUES (getutcdate())
>> >>
>> >> SELECT DATEADD (Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), TS)
>> >> FROM
>> >> TSTest
>> >>
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> Use of included script samples are subject to the terms specified at
>> >> http://www.microsoft.com/info/cpyright.htm
>> >>
>> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
>> >> news:F7A4B732-C033-4F16-95AE-0C7233B047D8@.microsoft.com...
>> >> > Hi Roger,
>> >> >
>> >> > Minneapolis, MN. Is there a way to format it to CDT?
>> >> > --
>> >> > Steve
>> >> >
>> >> >
>> >> > "Roger Wolter[MSFT]" wrote:
>> >> >
>> >> >> Where do you live? Timestamps are usually UTC.
>> >> >>
>> >> >> --
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >> Use of included script samples are subject to the terms specified
>> >> >> at
>> >> >> http://www.microsoft.com/info/cpyright.htm
>> >> >>
>> >> >> "Steven" <Steven@.discussions.microsoft.com> wrote in message
>> >> >> news:E91A5522-6DCB-4CAE-B227-BE763B4CAC5A@.microsoft.com...
>> >> >> > Thanks Sue, with a few minor edits it works great. However the
>> >> >> > timestamp
>> >> >> > field is 4 hours ahead of actual time, any idea why? The system
>> >> >> > clock
>> >> >> > is
>> >> >> > correct. Thanks again!
>> >> >> > --
>> >> >> > Steve
>> >> >> >
>> >> >> >
>> >> >> > "Sue Hoegemeier" wrote:
>> >> >> >
>> >> >> >> I don't think there is any "one" script but there is the
>> >> >> >> sample from the docs - is that what you are thinking of? The
>> >> >> >> script for creating IASODBC database?
>> >> >> >> You can find that at:
>> >> >> >> http://technet2.microsoft.com/WindowsServer/en/Library/5dcae8bc-d1e0-4562-9f53-b8478e5d33081033.mspx
>> >> >> >>
>> >> >> >> -Sue
>> >> >> >>
>> >> >> >> On Tue, 25 Apr 2006 11:57:01 -0700, Steven
>> >> >> >> <Steven@.discussions.microsoft.com> wrote:
>> >> >> >>
>> >> >> >> >Hello MVP's,
>> >> >> >> >
>> >> >> >> >I want to store my IAS logs in SQL2000 but can't remember the
>> >> >> >> >stored
>> >> >> >> >procedure script to first set up the database, can anybody
>> >> >> >> >help?
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>sql