ID for New record

Hi,
How can I insert a new record in sql table and retrun the ID of that reocrd using a stored procedure?
Thanks,From Books Online:

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

In stored proc:

create procedure sp_Test

@.outputvalue int output
as

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.outputvalue = @.@.IDENTITY|||Thanks DMWCincy.|||Watch out fot @.@.IDENTITY, better use SCOPE_IDENTITY() besuase @.@.IDENTITY will return the last increment for the table, not necessarily the one that was generated by you transaction.

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

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Do a find for Torn I/O.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>sql

I/O error (Torn page)

I run a sp in sqlserver 2000 and got this error message.
(1 row affected)
Msg 823, Level 24, State 2, Server DBINT02, Procedure
KundAvpris_Insert, Line 13
I/O error (torn page) detected during read at offset
0x0000013a29a000 in file
'E:\Program Files\Microsoft SQL
Server\MSSQL\data\MARKISDATA_Data.MDF'.
Does anybody know how i do to correct this error?I suggest you perform a log backup. Then restore the latest clean database backup and all subsequent
log backups (including this last one). This will most probably give you zero data loss.
If you don't have log backups in place, then just go for the last clean database backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Robert
Ask your system
administrator to check for disk corruption.You should make sure to run DBCC
CHECKDB or DBCC CHECKTABLE on that table.
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Torn pages does most likely occur because a partially performed write operation. CHECKDB or
CHECKTABLE does not help here, as it will only confirm what we already know: a corruption in the
database. Also, this does, unfortunately, temp some to try the repair options (which in most cases
doesn't help), and possibly hinder the ability to do the vital last log backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uhUFzm4dDHA.2340@.TK2MSFTNGP09.phx.gbl...
> Robert
> Ask your system
> administrator to check for disk corruption.You should make sure to run DBCC
> CHECKDB or DBCC CHECKTABLE on that table.
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||That might not all be necessary. If the torn page is in a non-clustered
index you can just rebuild the index and everything will be fine. If it is
in a clustered index or it is a page that is used by SQL Server internally,
Tibor's method is the safest way to go.
DBCC CHECKDB will tell you in which object the torn page is located. You can
run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
which can otherwise take a long time.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> I suggest you perform a log backup. Then restore the latest clean database
backup and all subsequent
> log backups (including this last one). This will most probably give you
zero data loss.
> If you don't have log backups in place, then just go for the last clean
database backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine.
Does above apply to torn pages as well?
I thought that torn pages are "corrupted beyond repair", even if a page can, technically, be dropped
as part of an index...
I.e., a torn page marks a "hands off - something is fishy here" to SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine. If it is
> in a clustered index or it is a page that is used by SQL Server internally,
> Tibor's method is the safest way to go.
> DBCC CHECKDB will tell you in which object the torn page is located. You can
> run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
> which can otherwise take a long time.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > I suggest you perform a log backup. Then restore the latest clean database
> backup and all subsequent
> > log backups (including this last one). This will most probably give you
> zero data loss.
> >
> > If you don't have log backups in place, then just go for the last clean
> database backups.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > I run a sp in sqlserver 2000 and got this error message.
> > >
> > > (1 row affected)
> > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > KundAvpris_Insert, Line 13
> > > I/O error (torn page) detected during read at offset
> > > 0x0000013a29a000 in file
> > > 'E:\Program Files\Microsoft SQL
> > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > >
> > > Does anybody know how i do to correct this error?
> >
> >
>|||Only thing a torn page tells you as far as I understand it, is that it was
written to disk partially but not completely, i.e. the check bits for all
the 512 byte sectors is the page are not the same, which means that some of
the sectors have changed the last time the page was written and some
haven't. It's a "logical" rather than a physical error, it doesn't tell you
anything about the current physical state of the page only about the current
logical state of the page (inconsistent) and that the last write operation
on that page didn't succeed completely. The page being torn in itself
doesn't make the harddisk space where it is located unusable. (The torn page
can ofcourse be caused by a harddisk problem which makes the disk space
unusable, but that's a separate issue.)
If the torn page has been cause by a power failure or a similar problem,
that is not a permanent hardware problem, like a bad sector on a disk, I see
no reason why you could not reuse the page?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine.
> Does above apply to torn pages as well?
> I thought that torn pages are "corrupted beyond repair", even if a page
can, technically, be dropped
> as part of an index...
> I.e., a torn page marks a "hands off - something is fishy here" to SQL
Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine. If it
is
> > in a clustered index or it is a page that is used by SQL Server
internally,
> > Tibor's method is the safest way to go.
> >
> > DBCC CHECKDB will tell you in which object the torn page is located. You
can
> > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
process,
> > which can otherwise take a long time.
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > I suggest you perform a log backup. Then restore the latest clean
database
> > backup and all subsequent
> > > log backups (including this last one). This will most probably give
you
> > zero data loss.
> > >
> > > If you don't have log backups in place, then just go for the last
clean
> > database backups.
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > I run a sp in sqlserver 2000 and got this error message.
> > > >
> > > > (1 row affected)
> > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > KundAvpris_Insert, Line 13
> > > > I/O error (torn page) detected during read at offset
> > > > 0x0000013a29a000 in file
> > > > 'E:\Program Files\Microsoft SQL
> > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > >
> > > > Does anybody know how i do to correct this error?
> > >
> > >
> >
> >
>|||I agree, Jacco. My point as only the SQL Server code (design of-). Whether SQL Server will never
re-uses/repairs a torn page or not, even though it can safely drop the page (because the HW might be
OK). I guess the answer is inside the SQL Server code, which I don't have access to... ;-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> Only thing a torn page tells you as far as I understand it, is that it was
> written to disk partially but not completely, i.e. the check bits for all
> the 512 byte sectors is the page are not the same, which means that some of
> the sectors have changed the last time the page was written and some
> haven't. It's a "logical" rather than a physical error, it doesn't tell you
> anything about the current physical state of the page only about the current
> logical state of the page (inconsistent) and that the last write operation
> on that page didn't succeed completely. The page being torn in itself
> doesn't make the harddisk space where it is located unusable. (The torn page
> can ofcourse be caused by a harddisk problem which makes the disk space
> unusable, but that's a separate issue.)
> If the torn page has been cause by a power failure or a similar problem,
> that is not a permanent hardware problem, like a bad sector on a disk, I see
> no reason why you could not reuse the page?
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine.
> >
> > Does above apply to torn pages as well?
> > I thought that torn pages are "corrupted beyond repair", even if a page
> can, technically, be dropped
> > as part of an index...
> > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> Server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine. If it
> is
> > > in a clustered index or it is a page that is used by SQL Server
> internally,
> > > Tibor's method is the safest way to go.
> > >
> > > DBCC CHECKDB will tell you in which object the torn page is located. You
> can
> > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> process,
> > > which can otherwise take a long time.
> > >
> > > --
> > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > Database Administrator
> > > Eurostop Ltd.
> > >
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > I suggest you perform a log backup. Then restore the latest clean
> database
> > > backup and all subsequent
> > > > log backups (including this last one). This will most probably give
> you
> > > zero data loss.
> > > >
> > > > If you don't have log backups in place, then just go for the last
> clean
> > > database backups.
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > >
> > > > > (1 row affected)
> > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > KundAvpris_Insert, Line 13
> > > > > I/O error (torn page) detected during read at offset
> > > > > 0x0000013a29a000 in file
> > > > > 'E:\Program Files\Microsoft SQL
> > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > >
> > > > > Does anybody know how i do to correct this error?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry Tibor, your post is not really clear to me...
I don't think that you can't drop an index because it contains a torn page,
if that is what you mean? (Too bad the problem is quite difficult to
replicate). Dropping an index only deallocates the index pages and deletes
the rows from the system tables and doesn't do anything to the actual pages,
so whether they are torn or not should not make any difference.
> I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
But you have access to people who have access (or at least have access to
people who have access) ;-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyZU7n5dDHA.1448@.TK2MSFTNGP12.phx.gbl...
> I agree, Jacco. My point as only the SQL Server code (design of-). Whether
SQL Server will never
> re-uses/repairs a torn page or not, even though it can safely drop the
page (because the HW might be
> OK). I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> > Only thing a torn page tells you as far as I understand it, is that it
was
> > written to disk partially but not completely, i.e. the check bits for
all
> > the 512 byte sectors is the page are not the same, which means that some
of
> > the sectors have changed the last time the page was written and some
> > haven't. It's a "logical" rather than a physical error, it doesn't tell
you
> > anything about the current physical state of the page only about the
current
> > logical state of the page (inconsistent) and that the last write
operation
> > on that page didn't succeed completely. The page being torn in itself
> > doesn't make the harddisk space where it is located unusable. (The torn
page
> > can ofcourse be caused by a harddisk problem which makes the disk space
> > unusable, but that's a separate issue.)
> >
> > If the torn page has been cause by a power failure or a similar problem,
> > that is not a permanent hardware problem, like a bad sector on a disk, I
see
> > no reason why you could not reuse the page?
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine.
> > >
> > > Does above apply to torn pages as well?
> > > I thought that torn pages are "corrupted beyond repair", even if a
page
> > can, technically, be dropped
> > > as part of an index...
> > > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> > Server.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine. If
it
> > is
> > > > in a clustered index or it is a page that is used by SQL Server
> > internally,
> > > > Tibor's method is the safest way to go.
> > > >
> > > > DBCC CHECKDB will tell you in which object the torn page is located.
You
> > can
> > > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> > process,
> > > > which can otherwise take a long time.
> > > >
> > > > --
> > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > > Database Administrator
> > > > Eurostop Ltd.
> > > >
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > > I suggest you perform a log backup. Then restore the latest clean
> > database
> > > > backup and all subsequent
> > > > > log backups (including this last one). This will most probably
give
> > you
> > > > zero data loss.
> > > > >
> > > > > If you don't have log backups in place, then just go for the last
> > clean
> > > > database backups.
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > > ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > > >
> > > > > > (1 row affected)
> > > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > > KundAvpris_Insert, Line 13
> > > > > > I/O error (torn page) detected during read at offset
> > > > > > 0x0000013a29a000 in file
> > > > > > 'E:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > > >
> > > > > > Does anybody know how i do to correct this error?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.
Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/...y/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/...y/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use in a stored procedures two databases

I have two database, database1 and database2.
Now I want to write a stored procedure in database1, in this Database I need to make a select statement to a table in database2. It is possible? What is the correct code for this problem?

Can someone help me?SELECT * FROM Database2.dbo.Table1

(assuming they are on the same server, that is).

I want to parse @ArrayOfDays into @d1 through @d5

In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through
@.d5.

@.ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.

Most active vars:
@.i - loop counter
@.char - current char in string
@.tempVal - contains the current element as it is being built
@.tempValExecString - contains SELECT stmt for EXEC()

I'm using EXEC() to execute a dynamically built SELECT.

The error I get when calling from vb.net is:
Must declare the variable '@.tempVal'.

Two manual traces indicate the logic is ok.

I suspect my assignment statement for @.tempValExecString.

Any help would be appreciated. - BobC

------------------
DECLARE
@.d1 varchar(3),
@.d2 varchar(3),
@.d3 varchar(3),
@.d4 varchar(3),
@.d5 varchar(3),
@.i int,
@.char char(1),
@.tempVal varchar(3),
@.tempValExecString varchar(30)

SELECT @.tempVal = ''
SELECT @.i = 1

WHILE @.i < LEN(@.ArrayOfDays)
BEGIN
SELECT @.char = SUBSTRING(@.ArrayOfDays, @.i, 1)
WHILE @.char <'.'
BEGIN
SELECT @.tempVal = @.tempVal + @.char
SELECT @.char = SUBSTRING(@.ArrayOfDays, @.i+1, 1)
IF @.char = '.'
BEGIN
/* the following should produce "SELECT @.d1 = 1" when it reads the
first period(.) */
SELECT @.tempValExecString = 'SELECT @.d' + LTRIM(RTRIM(STR(@.i))) + '
= @.tempVal'
EXEC(@.tempValExecString)
SELECT @.tempVal = ''
SELECT @.i = @.i + 1
END
SELECT @.i = @.i + 1
END
END
------------------bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through
@.d5.
>
@.ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.
>
Most active vars:
@.i - loop counter
@.char - current char in string
@.tempVal - contains the current element as it is being built
@.tempValExecString - contains SELECT stmt for EXEC()
>
I'm using EXEC() to execute a dynamically built SELECT.

Wait a minute. You are in a relational database now, not in a C++ program.

I didn't ask why you returned a delimited string in the procedure in
your first post, but if you intend on unpack the string in the
calling procedure, you are on the wrong track altogther. Pass the
data in a table, and perform your operations on the whole set.

Quote:

Originally Posted by

The error I get when calling from vb.net is:
Must declare the variable '@.tempVal'.
>
Two manual traces indicate the logic is ok.

No, it's not. A batch of dynamic SQL is a scope of its own, and you
cannot access variables in outer scope. If you want to assign
variables @.d1 to @.d5, that's five SELECT statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through @.d5 .. execute a dynamically built SELECT. <<

You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton

CREATE PROCEDURE Foobar
(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;

YOU can use COALESCE in the IN() list to handle NULLs or whatever.|||On Sep 28, 8:52 pm, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through @.d5 .. execute a dynamically built SELECT. <<

>
You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton
>
CREATE PROCEDURE Foobar
(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;
>
YOU can use COALESCE in the IN() list to handle NULLs or whatever.

Thanks, all. Both posts (yesterday and today) have been part of my
experiments to find the most efficient method of querying a ton of
data a ton of different ways to populate a "dashboard" page on
our .net intranet. You've both discovered that I am more an
applications programmer than a sql programmer, so I know you'll
forgive me. Meanwhile, your input has been very helpful. Thanks for
your time. -BobC

ps: es, the output string "array" was intended to be parsed by my
vb.net app. I just got a little curious about how arrays could be
implemented in t-sql, and possibly save some calls to the db server by
my app, or at least reduce the number of batches. I'm sure it's all
been done before, but I had to try and fail for myself. A learning
experience if nothing else. Thanks again.|||CREATE PROCEDURE Foobar

Quote:

Originally Posted by

(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;
>
YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>

Amazing, you just don't learn do you.

What is the risk and resource assessment of adding value number 6?

Resource assessment...

1) Change the stored procedure to accept an extra parameter
2) Change the query IN to accept an extra parameter
3) Change all the applications that call the stored procedure to accept
the extra parameter - that can be '1' to 'n' in a real environment where
applications share common logic (stored procedures).

Risk assessment...

1) Database changes - requires application to be taken offline while the
release to add the extra parameter is done
2) Application changes - each application binary needs to be updated to
use the new parameter; for fat clients that would be quite an involved task
for a couple of thousand clients even with SMS.
3) Testing - did you capture all applications using the procedure, each
application requires a test plan and testing.

Now, if you had used CSV instead - you'd pass a single parameter to the
stored procedure containing 1 to 'n' values then you wouldn't have any of
the above, it would just work; there would be no requirement to take the
application offline, there would be no risk that some clients didn't get
updated properly so weren't using the correct version of the executable
etc...

I really do wish you'd start listening to people who actually do this type
of thing day in day out and have done so for 20 + years, sitting writing
books for 30+ years and teaching people is no replacement for solid
industrial experience.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1191027166.216455.160600@.50g2000hsm.googlegro ups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through
>>@.d5 .. execute a dynamically built SELECT. <<

>
You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton
>
CREATE PROCEDURE Foobar
(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;
>
YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>

|||>I just got a little curious about how arrays could be implemented in T-SQL, .. <<

The concept of arrays, linked lists, etc. do not exist in SQL -- just
tables. Ever work with LISP? No arrays, and it uses recursion
instead of loops. And LISP only has lists.

SQL is very much a foreign language to the procedural programmer. For
example, in Japanese, there are no articles or plurals, the pronoun
system is totally different (no direct equivalent to first person
singular, etc), the verb tenses are totally different and sentences
have a topic, but not a subject, etc.). But millions of people still
use Japanese.

You can fake a matrix with this skeleton:

CREATE TABLE Array
(i INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
j INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
vali INTEGER NOT NULL);

For INTEGER ARRAY A[1:10, 1:10] in a procedural language, but then you
have to write your or own library functions, loop constructs, etc.
And performance will stink.|||bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

ps: es, the output string "array" was intended to be parsed by my
vb.net app.

Also in that case it would be better to return a result set and receive
that in a datatable in VB .Net. The code for composing the list is
dependent on the number of elements being fixed. Add one more value,
and you have a maintenance job to do. With result set + datatable you have
not.

Quote:

Originally Posted by

I just got a little curious about how arrays could be
implemented in t-sql, and possibly save some calls to the db server by
my app, or at least reduce the number of batches. I'm sure it's all
been done before, but I had to try and fail for myself.

You may be interested in this link for a rainy day:
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||And yes, Tony... I'm after engineering, not quick and dirty code. Not

Quote:

Originally Posted by

sure who your criticism is aimed at, but I have been referring to an

110% @. celko and his proposed solution - the guy is an idiot with little
real industrial experience based on his solutions...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

Here is an example of what I'm trying to do: Populate a grid(below)
on a .net dashboard web page with counts of various types of
activity(y axis) that would be distributed into bins(x axis) . The
datasource would be a single table or simple view(no aggregates). The
bins would represent increments in specific criteria, which would NOT
necessarily involve sequential values such as days of the month. (The
actual page will contain several grids, each having a different number
of bins. I would like to use the same code for all grids, if possible
-- sending the datasource, number of bins, and bin criteria as input
parameters.)
>...
I could call 20 different stored procedures, each having n SELECT
statements, but we all know a whole list of reasons why that's a bad
idea.

Why would you have different procedures for different activities?

I will have to admit that I don't get a very good understanding of
what you are trying to achieve. But a standard recommendation is that
you post:

1) CREATE TABLE statement for your table(s).
2) INSERT statements with sample data.
3) The desired result given the sample.

Of course, this assumes that the data model is set, and neither that is
clear to me. Then again, if you post what you have now, we may get a
better grip of where you're heading.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Quote:

Originally Posted by

I could call 20 different stored procedures, each having n SELECT
statements, but we all know a whole list of reasons why that's a bad
idea.

>
Why would you have different procedures for different activities?

Thanks for your patience, Erland. In the statment above, I was
stepping through my thought process for you -- moving from the simple
method of executing many slightly different SELECTs, to a black box
solution that can handle all bins for all activities. The black box
is what I'm aiming for.

Quote:

Originally Posted by

Of course, this assumes that the data model is set, and neither that is
clear to me. Then again, if you post what you have now, we may get a
better grip of where you're heading.

The data model is set. I will rewrite my post today, providing the
information you've asked for.

Thanks again very much.

BobC|||I hope this makes the problem more understandable. Thanks to anyone
who takes an interest.

I want to display a series of grids on a .net web page.

The grids would make up a dashboard that provides a snapshot of sales
and marketing activities, customer status information, forecasts and
objectives, etc.

Activities are grouped into grids by customer status (lead, active
customer, under contract, closed buyer) because the activities and
milestones (represented by the bins) vary for each status.

Activites(1-n) in a single grid are not all pulled from the same table
or view, and some of the criteria would differ. So I don't believe a
GROUP BY clause on a single dataset would fit the problem.

In the example below, activity1 represents sales leads that are
currently 1, 7, 20, 25 and 60 days old, respectively. These values
will come from the Prospects table.

Activity2 might represent forecasts for current sales leads at the
same intervals. These values would come from a sales objectives
table.

1 7 20 25 60
activity1 0 0 0 0 0
activity2 0 0 0 0 0
activity3 0 0 0 0 0
activity4 0 0 0 0 0
...
activityn 0 0 0 0 0

Another grid might have fewer or more bins.

My goal is to minimize:
# open connections to the database
# calls to stored procedures
length of code in stored procedures or udf's
maintenance as activities or bins are added or dropped

My "procedural language programmer" solution would be to open one
connection to the db and grab all the information at once, retrieving
all the activity rows in individual activity parameters. I could then
parse each parameter value into the appropriate bins with vb behind
the page.

Another approach, which Erland suggested, is to return a dataset.

I have listed (below) simplified versions of the Prospect table
description and the proc GetLeadsByStatusAge.

I use bin variables @.d1-@.d5 rather than literals 1, 7, 20, 25, 60
because I'm hoping there is a way to programmatically vary the number
and values of bin boundaries, through input parameters or some other
means, and make this thing elegant. In other words, I'd like to
create a black box that can process any activity I ask it to,
regardless of where the data comes from, how many bins apply, or what
the bin boundaries are.

GetLeadsByStatusAge returns an array string, but could just as easily
insert a row into a temporary table.

Thanks to Erland for suggesting the use of CASE. I've used it before
in a similar way, but sometimes forget its power.

Thanks to everyone who takes a look at it.

BobC

/ * Simplified definition of the Prospects table: */

CREATE TABLE Prospects (
CommunityCode varchar (3),
LastName1 varchar (30),
FirstName1 varchar (15),
InitialContactDate datetime,
ProspectStatus varchar (1),
StatusChangeDate datetime
)
GO

/* Simplified procedure intended to return one row of the grid on
my .net page. */

CREATE PROCEDURE GetLeadsByStatusAge
@.CommCode char(3),
@.Result varchar(255) OUTPUT

AS

SELECT @.Result =

(SELECT
ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d1 AND
@.d2-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d2 AND
@.d3-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d3 AND
@.d4-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d4 AND
@.d5-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @.d5
WHEN TRUE THEN 1 ELSE 0 END))) + '.'

FROM prospects
WHERE CommunityCode = @.CommCode
AND prospectstatus='L'
)

GO|||CORRECTION: syntax of the case statement was wrong in last post.
Shoud be...

CREATE PROCEDURE GetLeadsByStatusAge
@.CommCode char(3),
@.Result varchar(255) OUTPUT

AS

SELECT @.Result =

(SELECT
ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d1 AND
@.d2-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d2 AND
@.d3-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d3 AND
@.d4-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d4 AND
@.d5-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @.d5
THEN 1 ELSE 0 END))) + '.'

FROM prospects
WHERE CommunityCode = @.CommCode
AND prospectstatus='L'
)

GO|||Not giving up yet! I wrote a new proc that would return one row, and
I think looks very flexible. I can vary the number of bins and their
values, the data source, the WHERE clause of the SELECT it builds, and
even accomodate differences in column names from one data source to
another(a date field in this case). It could be called by a wrapper
to build a set of rows. The wrapper would execute a set of EXEC()'s
building a dataset, and then return the dataset to my vb.net page.

Open one db connection one time, get all the data back in one package,
eliminate verbose code.

I could build all the input parameters in vb and send them to the
wrapper, and vary the number of calls the wrapper makes to the
subproc. That way, I would only have to change the code in my page
when the number or value of bins change. Wouldn't have to edit the
stored procs.

Does it look like a reasonable solution to you? Is it reasonably
efficient?

Bob

------------------------------

CREATE PROCEDURE [dbo].[commdash_activity_by_age]
@.ArrayOfBins varchar(255), -- an input array string of variable
size(number of elements)
@.ArraySize int, -- number of elements
@.DataSourceName varchar(50), -- the data source name (table, view,
other stored proc)
@.DateFieldName varchar(25), -- the date field name differs in some
source tables
@.WhereClause varchar(255) -- the predetermined WHERE clause for the
SELECT

AS

DECLARE
@.WorkingArray varchar(255), -- copy of the input array string,
truncated from the left as bin elements are read
@.WorkingArrayLength int, -- actual length of the string
@.FirstDelimiter int, -- position of first bin delimiter in
WorkingArray
@.col int, -- index of current column in the SELECT clause
@.LowerBin varchar(3),
@.UpperBin varchar(3),
@.SelectClause varchar(4000), -- the select clause of the final query
to be executed
@.query varchar(4000) -- the final query to be executed

SET @.WorkingArray = @.ArrayOfBins
SET @.WorkingArrayLength = LEN(@.WorkingArray)
SET @.col = 1
SET @.LowerBin = ''
SET @.UpperBin = ''
SET @.SelectClause = 'SELECT '

-- get the first bin value from the input array string
SET @.FirstDelimiter = CHARINDEX('.', @.WorkingArray)
SET @.LowerBin = SUBSTRING(@.WorkingArray, 1, @.FirstDelimiter - 1)
-- remove the leading bin value and its trailing delimiter from the
front of @.ArrayOfBins
SET @.WorkingArray = SUBSTRING(@.WorkingArray, @.FirstDelimiter + 1,
@.WorkingArrayLength - @.FirstDelimiter)

-- loop through the appending of column statements @.ArraySize times
(number of elements)
WHILE @.col < @.ArraySize
BEGIN

-- get the upper bin value from the input array string
SET @.FirstDelimiter = CHARINDEX('.', @.WorkingArray)
SET @.UpperBin = SUBSTRING(@.WorkingArray, 1, @.FirstDelimiter - 1)
-- remove the leading bin value and its trailing delimiter from the
front of @.ArrayOfBins
SET @.WorkingArray = SUBSTRING(@.WorkingArray, @.FirstDelimiter + 1,
@.WorkingArrayLength - @.FirstDelimiter)

-- append the next column
SET @.SelectClause = @.SelectClause + 'ltrim(str(SUM(CASE WHEN
DATEDIFF(day, ' + @.DateFieldName + ', CURRENT_TIMESTAMP) BETWEEN ' +
@.LowerBin + ' AND ' + STR(CONVERT(integer, @.UpperBin)-1) +
'THEN 1 ELSE 0 END))), '

-- shift @.LowerBin up to @.UpperBin in preparation for next column
SET @.LowerBin = @.UpperBin
-- advance to the next column
SET @.col = @.col + 1

IF @.col = @.ArraySize -- append the column for the last bin, then
exit the loop.
BEGIN
SET @.SelectClause = @.SelectClause + 'ltrim(str(SUM(CASE WHEN
DATEDIFF(day, ' + @.DateFieldName + ', CURRENT_TIMESTAMP) ' +
@.LowerBin +
'THEN 1 ELSE 0 END))) '
END

END

-- append the FROM and WHERE clauses
SET @.query = @.SelectClause + 'FROM ' + @.DataSourcename + ' WHERE ' +
@.WhereClause

-- execute the query
EXEC(@.query)
GO|||bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

That has been the fundamental question all along. "How complex and/or
flexible can a single stored procedure be, and still be efficient?"
>
What I'm learning is that they can be very complex, but not as
flexible as c, vb, etc.

Depends on what you mean with flexible, but with regards to column and
tables, yes, SQL puts you into a straight-jacket. But there is stuff
which is a lot of work to do in a traditional language which is a breeze
in SQL.

Quote:

Originally Posted by

I could build all the input parameters in vb and send them to the
wrapper, and vary the number of calls the wrapper makes to the
subproc. That way, I would only have to change the code in my page
when the number or value of bins change. Wouldn't have to edit the
stored procs.
>
Does it look like a reasonable solution to you? Is it reasonably
efficient?

Except that this far you have not really gained of the strength of a
stored procedure. You could just as well have built that SQL string
in client code. And then use a real array, with need for any list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx