help emergency: stored

Showing posts with label stored. Show all posts

Friday, March 30, 2012

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

Wednesday, March 28, 2012

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/pr...ntsolutions.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.
>
>

Monday, March 26, 2012

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.
>
>

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

Friday, March 23, 2012

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).

Wednesday, March 21, 2012

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. <<

|||>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.

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

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

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

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

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.

I want to do paging in a stored procedure, but have some problem with it

my procedure is like this
////////////////////////////////////
CREATE PROCEDURE dbo.test2
(
@.Page_No int,
@.PageSize int,
@.RowCount int output
)
AS

declare @.intBeginID int
declare @.intEndID int
declare @.Counter int

set @.Counter = 1
select @.RowCount = count(*) from project

declare cro_fastread cursor scroll
for select * from project order by sector
open cro_fastread

select @.intBeginID=(@.Page_No-1)*@.PageSize+1
select @.intEndID = @.intBeginID+@.PageSize-1

fetch absolute @.intBeginID from cro_fastread

WHILE @.@.FETCH_STATUS = 0 and @.Counter < @.PageSize
BEGIN
set @.Counter = @.Counter + 1
FETCH NEXT FROM cro_fastread
END

close cro_fastread
deallocate cro_fastread
/////////////////////////////////////////
my problem is the "FETCH" method can only get a row at one time, so there would be return many recordsets, how can I merge all the return recordset into one.
thanks in advance!Why not use the built-in paging features of the DataGrid?|||that's too slow if the results is large|||Hi,

This article might help:
Creating a Stored Procedure for Custom Paging with the ASP.NET DataGrid Control

Regards,|||The article on SQL Junkies uses a temp table and 2 queries. Here's my sexy solution without temp tables and only one query:

Let's say your page size is 10 and you want page 3. Basically what you do is you get the TOP 40 records, and from that set get the bottom 10 records by using TOP and ordering it DESCENDING.

I don't know if it will be faster than the SqlJunkies.com solution, but it will at the very least be in the same ballpark. You'll need to use dynamic SQL, since TOP cannot take variables.

Here it is: (you can run it on Northwind)


declare @.PageNo int,
 @.PageSize int,
 @.Query varchar(1000)set @.PageNo = 3
set @.PageSize = 10
set @.Query = ' select O.*
 from (select top ' + cast(@.PageSize as varchar) + '
 OrderID
 from (select top ' + cast(@.PageSize * (@.PageNo + 1) as varchar) + '
 OrderID
 from Orders
 order by OrderID) as T
 order by T.OrderID desc) as B
 join Orders O
 on B.OrderID = O.OrderID
 order by O.OrderID'
exec(@.Query)

|||After thinking about it, there is an even better way to do it: Get the top 40 records and use a left join to exclude the top 30 records. My solution above will not work properly (as it is) for the last page in the set, since it always gets 10 records. Here's my second try:


declare @.PageNo int,
 @.PageSize int,
 @.Query varchar(1000)set @.PageNo = 3
set @.PageSize = 10
set @.Query = ' select O.*
 from (select top ' + cast(@.PageSize * (@.PageNo + 1) as varchar) + '
 OrderID
 from Orders
 order by OrderID) as T
 left join (select top ' + cast(@.PageSize * (@.PageNo) as varchar) + '
 OrderID
 from Orders
 order by OrderID) as B
 on T.OrderID = B.OrderID
 join Orders O
 on T.OrderID = O.OrderID
 where B.OrderID is null
 order by O.OrderID'
exec(@.Query)

Again, you can run this query on Northwind.|||This is one nice :), but (as there is always a but :P) it has a 2 drawbacks:

Due to the TOP clauses the higher the page number is, the slower the query will run, while a #temp table solution has the same performance for all pages. Also increasing the page size will drop the performance exponentially (again due to using TOP).

So doubt anyone (dare :P) will come up with something better/faster than a #temp table solution :).|||Yea it probably will be slightly slower - although we'd have to test it to be sure. With very large tables you'll probably not be able to use the Temp table solution anyway, since the WHOLE TABLE is written to the temp table for EVERY query. In that case you'll have to use a helper column that's updated by a trigger.

That will be faster than the temp table and my single-select query!

So I prefer my single-select just because it's simple and sexy, or for tables too big for it (more than 1 million rows) I'd do the trigger helper column.

But think about it: Will you really want to page through 10,000 pages of 100 rows each?

NO.|||:: Yea it probably will be slightly slower - although we'd have to test it to be sure.

Ofc i did test it BEFORE i did post my reply - using the Northwind database. Here are the results:

>> times taken from SQL-QueryAnalyzer Execution Plan
>> no index or other optimizations
>> hardware IDE-HDD, 80GB, 2 * AMD 1800+

Orders table, 830 records
===================
Pageing, using TOP:
First Page >> 0.0253
Last Page >> 0.224

Pageing, using a temp table:
First Page >> 0.088
Last Page >> 0.088

Payments table, 100.000 records
========================
Pageing, using TOP:
First Page >> 9,75
Last Page >> 12.8

Pageing, using a temp table:
First Page >> 5.967
Last Page >> 5.967

The main difference is that the solution using TOP is very CPU expensive due to the subselects needed to be ordered - check the execution plan an see what i mean - while a temp table solution is less CPU expensive (only one select/order) and the i/o cost of the #temp table is marginal (0.35 sec of the 5.967 seconds is i/o time).
This is why the larger the table gets, the slower the top version will be.

:: With very large tables you'll probably not be able to use the Temp table solution
:: anyway, since the WHOLE TABLE is written to the temp table for EVERY query.

Not the whole tables, only the ID column needs to be written to the temp table.

:: In that case you'll have to use a helper column that's updated by a trigger.

Not sure what you mean here ?

:: That will be faster than the temp table and my single-select query!

See result above.|||Yea, so the temp table is 50% faster. Not exponentially slower as claimed.

But still, you don't think there's a faster solution? My trigger-updated helper column idea WILL be faster. By orders of magnitude. If you're interested I'll post the full solution.|||Sure i am.|||:: Yea, so the temp table is 50% faster. Not exponentially slower as claimed

I didn'T claim that, i was not comparing the two solutions when i said: "Also increasing the page size will drop the performance exponentially (again due to using TOP)."

I was saying that a TOP solution will run exponentially slower the larger the pagenumber is (see result 0.02 for first page and 0.2 for last page), while for a temp table the page size does not affect the performance of the query (same performance for all pages).|||I am still interested to see it and waiting ... :)|||The triggers are trivial, so I won't write them for you, I'll instead explain the idea.

What you do is you add an int column to the table, call it OrderingID. This column is kept up to date with insert, update and delete triggers on the table. The triggers will ensure that the values in that column are unique, incremental (no gaps between numbers) and in the same sort order as the paging.

So it will have the same values as the temp table's auto-increment column, and therefore will negate the need of a massive temp table altogether.

Getting page 3 is now as simple as

select *
from TableName
where OrderingID >= (3*10)
and OrderingID < ((3+1)*10)
order by OrderingID

And as a bonus you can now add an index to that column for lightning selects.

The triggers will ensure that all the requirements for the above select are met (numbers from 0 to N-1, uniqueness, no gaps in sequence) at all times. These triggers are very simple and anyone should be able to do them.|||Oh you meant that ... well but it is only managable if you have 1-2 sort criterias. As soon as you need to order by any column (damn i always have to do that :S) you can't use this method effectively.
Another way would be creating an indexed view for each order criteria - but haven't looked into them yet.

i want to convert sql stored procedures to dll

can anyone help me and excuse my terrible english. i want to convert sql stored procedures to dll.

If i got you correctly then I don't think you can do so (convert stored procedure to dll) - the stored procedures reside in your database as its objects. Perhaps a little detail with what you want to achieve actually would have been of more help.

sql

Monday, March 19, 2012

I want to avoid using a cursor, please!

Application is a Work Tracking/Timesheet database.

The increments of work are stored in the TimesheetItem table. This
contains, inter alia, the Work Code, the Start and the Duration that
the employee spent that day on a particular project.

Some employees in the Network Support Department don't complete a
standard 7.5 hour day for various reasons, so for every Network Support
person I need to update these particular days with an amount to bring
the total day's hours to 7.5.

This SQL will get me a list of all TimesheetItem records for the
personnel concerned.

SELECT
TimesheetItem.TypeID,
[Work].WorkCode,
TimesheetItem.Start AS Start,
SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS Hours
FROM
TimesheetItem LEFT OUTER JOIN
[Work] ON TimesheetItem.WorkID = [Work].WorkID
WHERE
(TimesheetItem.EmployeeID IN
(SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))
GROUP BY
TimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCode
HAVING
(TimesheetItem.Start >= @.FromDate) AND (TimesheetItem.Start <= @.ToDate)
ORDER BY
TimesheetItem.Start

What I need is to group these records by EmployeeID where the
accumulated hours per day are < 7.5, so that I can then insert an
increment to make up the difference.

I'm writing this from home and I don't have access to the tables to
provide a script, but there's nothing untoward there.

Hope someone can help!

Thanks

Edward(teddysnips@.hotmail.com) writes:
> This SQL will get me a list of all TimesheetItem records for the
> personnel concerned.
> SELECT
> TimesheetItem.TypeID,
> [Work].WorkCode,
> TimesheetItem.Start AS Start,
> SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS Hours
> FROM
> TimesheetItem LEFT OUTER JOIN
> [Work] ON TimesheetItem.WorkID = [Work].WorkID
> WHERE
> (TimesheetItem.EmployeeID IN
> (SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))
> GROUP BY
> TimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCode
> HAVING
> (TimesheetItem.Start >= @.FromDate) AND (TimesheetItem.Start <= @.ToDate)
> ORDER BY
> TimesheetItem.Start
> What I need is to group these records by EmployeeID where the
> accumulated hours per day are < 7.5, so that I can then insert an
> increment to make up the difference.

I have problems to get a grip on this, because in the query you are
not grouping results per employee, so I can't see how you could be
able to dig out the employess that worked less than 7.5 hours.

Here is a modified version of the query that gets the employees that
worked less than 7.5 hours:

SELECT EmployeeID, convert(char(8), Start, 112), SUM(Hours)
FROM (SELECT TI.EmployeeID, TI.TypeID, W.WorkCode,
TI.Start AS Start,
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
LEFT JOIN [Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID
AND E:DepartmentID = 2)
AND TI.Start >= @.FromDate
AND TI.Start <= @.ToDate
GROUP BY TI.EmployeeID, TI.TypeID, TI.Start, W.WorkCode) AS x
GROUP BY EmployeeID, convert(char(8), Start, 112)
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)

But this can be simplified to:

SELECT TI.EmployeeID, convert(char(8), TI.Start, 112),
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID
AND E:DepartmentID = 2)
AND TI.Start >= @.FromDate
AND TI.Start <= @.ToDate
GROUP BY TI.EmployeeID, EmployeeID, convert(char(8), Start, 112)
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)

Then I really don't grasp what you are to do with that information.

Maybe you should come back on Monday when you have access to the
tables, so you also can post sample data (as INSERT statements)
and the desired result given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You usually model a timesheet with start and end time for each event.
Otherwise look for a lifetime of horrible left outer self-join thaqt
run like glue and are bitch to maintain

A data element can be a type or an identifer, but there no way it can
be both.

Also, avoid FLOAT in favor of DECIMAL unless you like rounding errors.
And if you do use FLOAT, do not divide by integers.|||--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.

Apologies for going off half-cocked. Please find at the bottom scripts
for relevant tables.

Erland's first SQL statement as amended below (his second, simplified
version, gives an error on line 7 - "Invalid column name 'Hours') does
what I want, more or less, in that it gives a list of dates and
employees who worked less than 7.5 hours on a particular day.

SELECT
EmployeeID,
convert(char(8),
Start, 112) As TimesheetDate,
SUM(Hours)
FROM
(SELECT TI.EmployeeID, TI.TypeID, W.WorkCode, TI.Start AS Start,
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
LEFT JOIN [Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID
AND E.DepartmentID = 2)
GROUP BY TI.EmployeeID, TI.TypeID, TI.Start, W.WorkCode) AS x
GROUP BY EmployeeID, convert(char(8), Start, 112)
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)

This gives a table something like this:

453200110105.0
453200111267.0
453200201047.0
453200201247.25
453200202276.75
453200203087.0
453200203286.5
453200205077.0
3,900 rows removed!

What I need to do is, for each of those employees on the corresponding
day, is to add an extra record that will bring up the number of hours
worked that day to 7.5. I've thought long and hard and can't see any
way to go without a cursor.

Thanks so far

Edward

Tabledefs below:

CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [PK_TimesheetItem] PRIMARY KEY CLUSTERED
(
[ItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED
(
[WorkID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [DF_TimesheetItem_TypeID] DEFAULT (0) FOR [TypeID],
CONSTRAINT [DF_TimesheetItem_DurationMins] DEFAULT (0) FOR
[DurationMins],
CONSTRAINT [DF_TimesheetItem_OffSite] DEFAULT (0) FOR [OffSite],
CONSTRAINT [DF_TimesheetItem_TravelToMins] DEFAULT (0) FOR
[TravelToMins],
CONSTRAINT [DF_TimesheetItem_TravelFromMins] DEFAULT (0) FOR
[TravelFromMins],
CONSTRAINT [DF_TimesheetItem_NonChargeableMins] DEFAULT (0) FOR
[NonChargeableMins],
CONSTRAINT [DF_TimesheetItem_Abroad] DEFAULT (0) FOR [Abroad],
CONSTRAINT [DF_TimesheetItem_Reviewed] DEFAULT (0) FOR [ApprovalID],
CONSTRAINT [CK_TimesheetItem] CHECK ([TypeID] like '[0-3]' and
[OffSite] like '[0-1]')
GO

ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [DF_Work_Chargeable] DEFAULT (0) FOR [Chargeable],
CONSTRAINT [DF_WorkItem_ReviewRequired] DEFAULT (0) FOR
[ApprovalRequired],
CONSTRAINT [IX_WorkCode] UNIQUE NONCLUSTERED
(
[WorkCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Work] CHECK ([ApprovalRequired] like '[0-1]')
GO

ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [FK_Employee_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
),
CONSTRAINT [FK_Employee_UserGroup] FOREIGN KEY
(
[UserGroupID]
) REFERENCES [dbo].[UserGroup] (
[UserGroupID]
)
GO

ALTER TABLE [dbo].[TimesheetItem] ADD
CONSTRAINT [FK_TimesheetItem_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_Employee1] FOREIGN KEY
(
[OTAuthorisedID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_WorkCode] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
)
GO

ALTER TABLE [dbo].[Work] ADD
CONSTRAINT [FK_Work_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
)
GO|||(teddysnips@.hotmail.com) writes:
> Erland's first SQL statement as amended below (his second, simplified
> version, gives an error on line 7 - "Invalid column name 'Hours')

I'm sorry for that, but that what you get when you don't post CREATE
TABLE and INSERT. When you do that you get a tested query. When you
don't, you get something which I've just typed, and it may contain
trivial syntax errors. The assumption is that you are able to fix those
yourself.

> What I need to do is, for each of those employees on the corresponding
> day, is to add an extra record that will bring up the number of hours
> worked that day to 7.5. I've thought long and hard and can't see any
> way to go without a cursor.

Add where? To the timesheet table? With what data?

I'm sorry, but since I don't know exactly what you want I deciline for
now.

I suggest that you trim down the table defintions to the columns relevant
for the problem, add some INSERT statements with sample data, and the
clearly specify the end result you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> (teddysnips@.hotmail.com) writes:
> > Erland's first SQL statement as amended below (his second, simplified
> > version, gives an error on line 7 - "Invalid column name 'Hours')
> I'm sorry for that, but that what you get when you don't post CREATE
> TABLE and INSERT. When you do that you get a tested query. When you
> don't, you get something which I've just typed, and it may contain
> trivial syntax errors. The assumption is that you are able to fix those
> yourself.

Erland, in no way whatsoever was I criticising you! The service you
provide here is incredibly valuable, and you don't even get paid! So I
apologise if I appeared to be critical.

Here goes from the beginning.

Basic CREATE TABLE STUFF:

CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

INSERT Section:

INSERT INTO Employee (UserName) VALUES ('Smith')

INSERT INTO Work (WorkCode) VALUES ('AB01')

INSERT INTO Work (WorkCode) VALUES ('2002')

INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(1,
'17 July 2005 08:30:00',
450,
1)

INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(1,
'18 July 2005 08:30:00',
180,
1)

This will result in a single Employee 'Smith', with two WorkCodes
'AB01' and '2002'. On 17 July Smith worked for 7.5 hours on AB01, but
on 18 July only for three hours. The SQL:

SELECT
EmployeeID,
convert(char(8), Start, 112) As TimesheetDate,
SUM(Hours) as NumHours
FROM
(SELECT TI.EmployeeID, W.WorkCode,
TI.Start AS Start,
SUM(CAST(TI.DurationMins AS float) / 60) AS Hours
FROM TimesheetItem TI
LEFT JOIN [Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID )
GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS x
GROUP BY EmployeeID, convert(char(8), Start, 112),
HAVING 7.5 - SUM(Hours) > 1E-6
ORDER BY EmployeeID, convert(char(8), Start, 112)

returns a single row:

1200507183.0

What I need to do is, for all such rows returned from the live data, to
add a record to the TimesheetItem table that would make up that day's
work to 7.5 hours for Workcode '2002'. The Start column would contain
the date and the correct offset from 08:30 - in this case 11:30 (that
is, three hours).

So, for example, the insert to make the Smith record compliant for 18
July would be

INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(1,
'18 July 2005 11:30:00',
270,
2)

Can I do this without using a cursor? I apologise if this is badly
expressed - I'm doing my best!

Edward|||Will (billrigby@.hotmail.com) writes:
> So, for example, the insert to make the Smith record compliant for 18
> July would be
> INSERT INTO TimesheetItem
> (EmployeeID,
> Start,
> DurationMins,
> WorkID)
> VALUES
> (1,
> '18 July 2005 11:30:00',
> 270,
> 2)
> Can I do this without using a cursor? I apologise if this is badly
> expressed - I'm doing my best!

Sure, no need for a cursor:

INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)

SELECT EmployeeID, dateadd(MINUTE, DurationMins, LastStart),
7.5 * 60 - DurationMins, 2
FROM (SELECT TI.EmployeeID,
convert(char(8), TI.Start, 112) As TimesheetDate,
MAX(TI.Start) AS LastStart,
SUM(TI.DurationMins) AS DurationMins
FROM TimesheetItem TI
/* WHERE EXISTS (SELECT *
FROM Employee E
WHERE TI.EmployeeID = E.EmployeeID ) */
GROUP BY TI.EmployeeID, convert(char(8), TI.Start, 112)
HAVING SUM(TI.DurationMins) < 7.5*60) AS x

However, there are still a number of issues that are not entirely
clear to me:

1) The Work table does not seem to be relevant to the query at all.
We do need a work code to insert, but you have not mentioed how
we determine this. I've assumed that the 2 you used is a hard-coded
value.

2) The table TimesheetItem is problematic. It appears logical that
(Employee, Start) is unique, but there is no constraint saying
this as I could see. Your primary key is an IDENTITY column, but
if my assumption is correct, you would need that column.

3) In your original post, you restricted the query to employees of a
certain department. Here you seem to have relaxed that requirement.
For this reason, I've commented the part of the query that accesses
Employee.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I designed a databse for a company that makes time keeping equipment
(http://www.exaktime.com/). They sell mostly to the construction
trades because their hardware is mil spec quality and can be used by
illiterate workers if they are not color blind.

Based on actually doing such a data base, I think your design is
fundamentally flawed and you need to start over. You have no real
keys, the tables are not normalized, there are too many NULL-able
columns, repeated groups to destroy even the hope of 1NF, BIT flags,
absurd data element names like "<something>TypeID" and columns that are
far too large (ever see a CHAR(50) password?, CHAR(100) email
address?). Your temporal model is wrong (look up Rick Snodgrass and
temporal SQL for an entire book on why).

All you will get here is newgroup is some kludges to work around this
design. But that is not the right solution. Get some help and start
over; this design is useless and so flawed it will never have any data
integrity.|||
Erland Sommarskog wrote:
> Will (billrigby@.hotmail.com) writes:
> > So, for example, the insert to make the Smith record compliant for 18
> > July would be
> > INSERT INTO TimesheetItem
> > (EmployeeID,
> > Start,
> > DurationMins,
> > WorkID)
> > VALUES
> > (1,
> > '18 July 2005 11:30:00',
> > 270,
> > 2)
> > Can I do this without using a cursor? I apologise if this is badly
> > expressed - I'm doing my best!
> Sure, no need for a cursor:
> INSERT INTO TimesheetItem
> (EmployeeID,
> Start,
> DurationMins,
> WorkID)
> SELECT EmployeeID, dateadd(MINUTE, DurationMins, LastStart),
> 7.5 * 60 - DurationMins, 2
> FROM (SELECT TI.EmployeeID,
> convert(char(8), TI.Start, 112) As TimesheetDate,
> MAX(TI.Start) AS LastStart,
> SUM(TI.DurationMins) AS DurationMins
> FROM TimesheetItem TI
> /* WHERE EXISTS (SELECT *
> FROM Employee E
> WHERE TI.EmployeeID = E.EmployeeID ) */
> GROUP BY TI.EmployeeID, convert(char(8), TI.Start, 112)
> HAVING SUM(TI.DurationMins) < 7.5*60) AS x

Many thanks for this - works a treat!
> However, there are still a number of issues that are not entirely
> clear to me:
> 1) The Work table does not seem to be relevant to the query at all.
> We do need a work code to insert, but you have not mentioed how
> we determine this. I've assumed that the 2 you used is a hard-coded
> value.

See my response to CELKO below.
> 2) The table TimesheetItem is problematic. It appears logical that
> (Employee, Start) is unique, but there is no constraint saying
> this as I could see. Your primary key is an IDENTITY column, but
> if my assumption is correct, you would need that column.
> 3) In your original post, you restricted the query to employees of a
> certain department. Here you seem to have relaxed that requirement.
> For this reason, I've commented the part of the query that accesses
> Employee.

I did this for simplicity with regard to the barebones CREATE TABLE and
INSERT stuff I sent. I felt that the Department was a "red herring".

Many thanks for your help.

Edward|||
--CELKO-- wrote:
> I designed a databse for a company that makes time keeping equipment
> (http://www.exaktime.com/). They sell mostly to the construction
> trades because their hardware is mil spec quality and can be used by
> illiterate workers if they are not color blind.
> Based on actually doing such a data base, I think your design is
> fundamentally flawed and you need to start over. You have no real
> keys, the tables are not normalized, there are too many NULL-able
> columns, repeated groups to destroy even the hope of 1NF, BIT flags,
> absurd data element names like "<something>TypeID" and columns that are
> far too large (ever see a CHAR(50) password?, CHAR(100) email
> address?). Your temporal model is wrong (look up Rick Snodgrass and
> temporal SQL for an entire book on why).

I'm relieved to be able to say that I didn't design the database! The
DDL that I posted upthread was simplified to illustrate the problem.
If you have time, I should be extremely interested in your comments on
the REAL data design, which I post below.

In any case, many thanks for time taken.

Edward

CREATE TABLE [dbo].[BankHolidays] (
[BankHoliday] [smalldatetime] NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department] (
[DepartmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HolidayAllocation] (
[EmployeeID] [int] NOT NULL ,
[HolidayYear] [int] NOT NULL ,
[DaysAllocated] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkOrderID] [int] NULL ,
[InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvoiceValue] [money] NULL ,
[InvoiceDate] [datetime] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RecurringWorkItem] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkComponentID] [int] NULL ,
[RefID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssignedEmployeeID] [int] NULL ,
[RecurStart] [smalldatetime] NULL ,
[RecurEnd] [smalldatetime] NULL ,
[RecurWeekly] [bit] NULL ,
[RecurMonthly] [bit] NULL ,
[RecurMonthlyDayNo] [int] NULL ,
[RecurMonthlyWeekDayOrder] [int] NULL ,
[RecurMonday] [bit] NULL ,
[RecurTuesday] [bit] NULL ,
[RecurWednesday] [bit] NULL ,
[RecurThursday] [bit] NULL ,
[RecurFriday] [bit] NULL ,
[RecurSaturday] [bit] NULL ,
[RecurSunday] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserGroup] (
[UserGroupID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccessTimesheetEntry] [int] NULL ,
[AccessTimesheetAdmin] [int] NULL ,
[AccessTimesheetReview] [int] NULL ,
[AccessWorkAdmin] [int] NULL ,
[AccessEmployeeAdmin] [int] NULL ,
[AccessContacts] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WeekProfile] (
[WeekProfileID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[PeriodStart] [smalldatetime] NOT NULL ,
[PeriodEnd] [smalldatetime] NULL ,
[Profile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DailyHours] [decimal](18, 2) NOT NULL ,
[DailyValidation] [bit] NOT NULL ,
[StartTime] [datetime] NULL ,
[LunchStartTime] [datetime] NULL ,
[LunchDurationMins] [int] NULL ,
[WeeklyHours] [decimal](18, 2) NOT NULL ,
[WeeklyValidation] [bit] NOT NULL ,
[MondayHours] [decimal](18, 2) NOT NULL ,
[TuesdayHours] [decimal](10, 2) NOT NULL ,
[WednesdayHours] [decimal](10, 2) NOT NULL ,
[ThursdayHours] [decimal](10, 2) NOT NULL ,
[FridayHours] [decimal](10, 2) NOT NULL ,
[SaturdayHours] [decimal](10, 2) NOT NULL ,
[SundayHours] [decimal](18, 2) NOT NULL ,
[WeekDayValidation] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WeekProfileTemplate] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[Profile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DailyHours] [decimal](10, 2) NOT NULL ,
[DailyValidation] [bit] NOT NULL ,
[StartTime] [datetime] NULL ,
[LunchStartTime] [datetime] NULL ,
[LunchDurationMins] [int] NULL ,
[WeeklyHours] [decimal](10, 2) NOT NULL ,
[WeeklyValidation] [bit] NOT NULL ,
[MondayHours] [decimal](18, 2) NOT NULL ,
[TuesdayHours] [decimal](18, 2) NOT NULL ,
[WednesdayHours] [decimal](18, 2) NOT NULL ,
[ThursdayHours] [decimal](18, 2) NOT NULL ,
[FridayHours] [decimal](18, 2) NOT NULL ,
[SaturdayHours] [decimal](18, 2) NOT NULL ,
[SundayHours] [decimal](18, 2) NOT NULL ,
[WeekDayValidation] [bit] NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkComponent] (
[WorkComponentID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkComponentDefaults] (
[WorkComponentDefaultID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NOT NULL ,
[Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkEmployeeRate] (
[WorkEmployeeRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NULL ,
[EmployeeID] [int] NULL ,
[RateMultipiler] [float] NULL ,
[RatePerHour] [smallmoney] NULL ,
[OffSiteOnly] [tinyint] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItem] (
[WorkItemID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkComponentID] [int] NULL ,
[RefID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Scheduled] [smalldatetime] NULL ,
[PriorityID] [int] NULL ,
[StatusID] [int] NULL ,
[ResolutionID] [int] NULL ,
[PhaseID] [int] NULL ,
[AreaID] [int] NULL ,
[VersionID] [int] NULL ,
[VersionReleasedID] [int] NULL ,
[WorkOrderID] [int] NULL ,
[AllocatedHours] [real] NULL ,
[PercentComplete] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemAreaLUT] (
[WorkItemAreaID] [int] IDENTITY (1, 1) NOT NULL ,
[Area] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemAssignment] (
[WorkItemID] [int] NOT NULL ,
[EmployeeID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemPhaseLUT] (
[WorkItemPhaseID] [int] IDENTITY (1, 1) NOT NULL ,
[Phase] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemPriorityLUT] (
[PriorityID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemResolutionLUT] (
[WorkItemResolutionID] [int] IDENTITY (1, 1) NOT NULL ,
[Resolution] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[IsFinal] [bit] NOT NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemStatusLUT] (
[WorkItemStatusID] [int] IDENTITY (1, 1) NOT NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IsFinal] [bit] NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkItemTypeLUT] (
[WorkItemTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [smalldatetime] NOT NULL ,
[TimeTo] [smalldatetime] NOT NULL ,
[RateMultipiler] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultipiler] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkType] (
[WorkTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DepartmentID] [int] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkItemPriority] [bit] NOT NULL ,
[WorkItemStatus] [bit] NOT NULL ,
[WorkItemResolution] [bit] NOT NULL ,
[WorkItemPhase] [bit] NOT NULL ,
[WorkItemArea] [bit] NOT NULL ,
[WorkItemVersion] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkVersion] (
[WorkVersionID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NULL ,
[Version] [float] NULL ,
[ReleaseDate] [smalldatetime] NULL ,
[Notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [PK_TimesheetItem] PRIMARY KEY CLUSTERED
(
[ItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[BankHolidays] WITH NOCHECK ADD
CONSTRAINT [PK_BankHolidays] PRIMARY KEY CLUSTERED
(
[BankHoliday]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[HolidayAllocation] WITH NOCHECK ADD
CONSTRAINT [PK_HolidayAllocation] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[HolidayYear]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice] WITH NOCHECK ADD
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[InvoiceID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[RecurringWorkItem] WITH NOCHECK ADD
CONSTRAINT [PK_RecurringWorkItem] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserGroup] WITH NOCHECK ADD
CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED
(
[UserGroupID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeekProfile] WITH NOCHECK ADD
CONSTRAINT [PK_HoursProfile] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[PeriodStart]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeekProfileTemplate] WITH NOCHECK ADD
CONSTRAINT [PK_HoursProfileTemplate] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED
(
[WorkID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkComponent] WITH NOCHECK ADD
CONSTRAINT [PK_WorkComponent] PRIMARY KEY CLUSTERED
(
[WorkComponentID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkComponentDefaults] WITH NOCHECK ADD
CONSTRAINT [PK_WorkTypeComponentDefaults] PRIMARY KEY CLUSTERED
(
[WorkComponentDefaultID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkEmployeeRate] WITH NOCHECK ADD
CONSTRAINT [PK_WorkEmployeeRate] PRIMARY KEY CLUSTERED
(
[WorkEmployeeRateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItem] WITH NOCHECK ADD
CONSTRAINT [PK_WorkActivity] PRIMARY KEY CLUSTERED
(
[WorkItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemAreaLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemAreaLUT] PRIMARY KEY CLUSTERED
(
[WorkItemAreaID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemAssignment] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemAssignment] PRIMARY KEY CLUSTERED
(
[WorkItemID],
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemPhaseLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemPhaseLUT] PRIMARY KEY CLUSTERED
(
[WorkItemPhaseID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemPriorityLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemPriorityLUT] PRIMARY KEY CLUSTERED
(
[PriorityID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemResolutionLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemResolutionLUT] PRIMARY KEY CLUSTERED
(
[WorkItemResolutionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemStatusLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemStatusLUT] PRIMARY KEY CLUSTERED
(
[WorkItemStatusID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemTypeLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkActivityType] PRIMARY KEY CLUSTERED
(
[WorkItemTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkOTRate] WITH NOCHECK ADD
CONSTRAINT [PK_WorkOTRate] PRIMARY KEY CLUSTERED
(
[WorkOTRateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkOTRateDefaults] WITH NOCHECK ADD
CONSTRAINT [PK_WorkOTRateDefaults] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemOrder] PRIMARY KEY CLUSTERED
(
[WorkOrderID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkType] WITH NOCHECK ADD
CONSTRAINT [PK_WorkType] PRIMARY KEY CLUSTERED
(
[WorkTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkVersion] WITH NOCHECK ADD
CONSTRAINT [PK_WorkVersion] PRIMARY KEY CLUSTERED
(
[WorkVersionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [IX_DepartmentName] UNIQUE NONCLUSTERED
(
[Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[RecurringWorkItem] WITH NOCHECK ADD
CONSTRAINT [DF_RecurringWorkItem_RecurWeekly] DEFAULT (0) FOR
[RecurWeekly],
CONSTRAINT [DF_RecurringWorkItem_RecurMonthly] DEFAULT (0) FOR
[RecurMonthly],
CONSTRAINT [DF_RecurringWorkItem_RecurMonday] DEFAULT (0) FOR
[RecurMonday],
CONSTRAINT [DF_RecurringWorkItem_RecurTuesday] DEFAULT (0) FOR
[RecurTuesday],
CONSTRAINT [DF_RecurringWorkItem_RecurWednesday] DEFAULT (0) FOR
[RecurWednesday],
CONSTRAINT [DF_RecurringWorkItem_RecurThursday] DEFAULT (0) FOR
[RecurThursday],
CONSTRAINT [DF_RecurringWorkItem_RecurFriday] DEFAULT (0) FOR
[RecurFriday],
CONSTRAINT [DF_RecurringWorkItem_RecurFriday1] DEFAULT (0) FOR
[RecurSaturday],
CONSTRAINT [DF_RecurringWorkItem_RecurSaturday1] DEFAULT (0) FOR
[RecurSunday]
GO

ALTER TABLE [dbo].[UserGroup] WITH NOCHECK ADD
CONSTRAINT [DF_UserGroup_AccessTimesheetEntry] DEFAULT (0) FOR
[AccessTimesheetEntry],
CONSTRAINT [DF_UserGroup_AccessTimesheetAdmin] DEFAULT (0) FOR
[AccessTimesheetAdmin],
CONSTRAINT [DF_UserGroup_AccessTimesheetReview] DEFAULT (0) FOR
[AccessTimesheetReview],
CONSTRAINT [DF_UserGroup_AccessWorkCodeAdmin] DEFAULT (0) FOR
[AccessWorkAdmin],
CONSTRAINT [DF_UserGroup_AccessEmployeeAdmin] DEFAULT (0) FOR
[AccessEmployeeAdmin],
CONSTRAINT [DF_UserGroup_AccessContacts] DEFAULT (0) FOR
[AccessContacts],
CONSTRAINT [IX_UserGroupName] UNIQUE NONCLUSTERED
(
[Name]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_UserGroup] CHECK ([AccessTimesheetEntry] like '[0-3]'
and [AccessTimesheetAdmin] like '[0-3]' and [AccessWorkAdmin] like
'[0-3]' and [AccessEmployeeAdmin] like '[0-3]' and
[AccessTimesheetReview] like '[0-3]' and [AccessContacts] like '[0-3]')
GO

ALTER TABLE [dbo].[WeekProfile] WITH NOCHECK ADD
CONSTRAINT [DF_WeekProfile_DailyHours] DEFAULT (0) FOR [DailyHours],
CONSTRAINT [DF_WeekProfile_DailyHoursValidation] DEFAULT (0) FOR
[DailyValidation],
CONSTRAINT [DF_WeekProfile_WeekHours] DEFAULT (0) FOR [WeeklyHours],
CONSTRAINT [DF_WeekProfile_WeeklyHoursValidation] DEFAULT (0) FOR
[WeeklyValidation],
CONSTRAINT [DF_EmploymentProfile_MondayHours] DEFAULT (0) FOR
[MondayHours],
CONSTRAINT [DF_EmploymentProfile_TuesdayHours] DEFAULT (0) FOR
[TuesdayHours],
CONSTRAINT [DF_EmploymentProfile_WednesdayHours] DEFAULT (0) FOR
[WednesdayHours],
CONSTRAINT [DF_EmploymentProfile_ThursdayHours] DEFAULT (0) FOR
[ThursdayHours],
CONSTRAINT [DF_EmploymentProfile_FridayHours] DEFAULT (0) FOR
[FridayHours],
CONSTRAINT [DF_EmploymentProfile_SaturdayHours] DEFAULT (0) FOR
[SaturdayHours],
CONSTRAINT [DF_WeekProfile_SundayHours] DEFAULT (0) FOR [SundayHours],
CONSTRAINT [DF_WeekProfile_WeekDayValidation] DEFAULT (0) FOR
[WeekDayValidation]
GO

ALTER TABLE [dbo].[WeekProfileTemplate] WITH NOCHECK ADD
CONSTRAINT [DF_WeekProfileTemplate_DailyHours] DEFAULT (0) FOR
[DailyHours],
CONSTRAINT [DF_WeekProfileTemplate_DailyValidation] DEFAULT (0) FOR
[DailyValidation],
CONSTRAINT [DF_WeekProfileTemplate_WeekHours] DEFAULT (0) FOR
[WeeklyHours],
CONSTRAINT [DF_WeekProfileTemplate_WeeklyValidation] DEFAULT (0) FOR
[WeeklyValidation],
CONSTRAINT [DF_HoursProfileTemplates_MondayHours] DEFAULT (0) FOR
[MondayHours],
CONSTRAINT [DF_HoursProfileTemplates_TuesdayHours] DEFAULT (0) FOR
[TuesdayHours],
CONSTRAINT [DF_HoursProfileTemplates_WednesdayHours] DEFAULT (0) FOR
[WednesdayHours],
CONSTRAINT [DF_HoursProfileTemplates_ThursdayHours] DEFAULT (0) FOR
[ThursdayHours],
CONSTRAINT [DF_HoursProfileTemplates_FridayHours] DEFAULT (0) FOR
[FridayHours],
CONSTRAINT [DF_HoursProfileTemplates_SaturdayHours] DEFAULT (0) FOR
[SaturdayHours],
CONSTRAINT [DF_WeekProfileTemplate_SundayHours] DEFAULT (0) FOR
[SundayHours],
CONSTRAINT [DF_WeekProfileTemplate_StrictValidation] DEFAULT (0) FOR
[WeekDayValidation],
CONSTRAINT [IX_HoursProfileTemplate] UNIQUE NONCLUSTERED
(
[Profile]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkComponent] WITH NOCHECK ADD
CONSTRAINT [IX_WorkComponent] UNIQUE NONCLUSTERED
(
[WorkID],
[Component]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkComponentDefaults] WITH NOCHECK ADD
CONSTRAINT [IX_WorkTypeComponentDefaults] UNIQUE NONCLUSTERED
(
[WorkTypeID],
[Component]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkEmployeeRate] WITH NOCHECK ADD
CONSTRAINT [DF_WorkEmployeeRate_OffSiteOnly] DEFAULT (0) FOR
[OffSiteOnly],
CONSTRAINT [IX_WorkEmployeeRate] UNIQUE NONCLUSTERED
(
[WorkID],
[EmployeeID],
[OffSiteOnly]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_WorkEmployeeRate] CHECK ([OffSiteOnly] like '[0-1]')
GO

ALTER TABLE [dbo].[WorkItem] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemCode] UNIQUE NONCLUSTERED
(
[WorkItemID],
[RefID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_WorkItem] CHECK ([PercentComplete] like '[0-100]')
GO

ALTER TABLE [dbo].[WorkItemAreaLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemAreaLUT] UNIQUE NONCLUSTERED
(
[Area]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemPhaseLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemPhaseLUT] UNIQUE NONCLUSTERED
(
[Phase]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemPriorityLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemPriorityLUT] UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemResolutionLUT] WITH NOCHECK ADD
CONSTRAINT [DF_WorkItemResolutionLUT_IsFinal] DEFAULT (0) FOR
[IsFinal]
GO

ALTER TABLE [dbo].[WorkItemStatusLUT] WITH NOCHECK ADD
CONSTRAINT [DF_WorkItemStatusLUT_IsFinal] DEFAULT (0) FOR [IsFinal],
CONSTRAINT [IX_WorkItemStatusLUT] UNIQUE NONCLUSTERED
(
[Status]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkItemTypeLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkActivityType] UNIQUE NONCLUSTERED
(
[WorkTypeID],
[Type]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkOTRate] WITH NOCHECK ADD
CONSTRAINT [DF_WorkDayRate_WorkDay] DEFAULT (0) FOR [WorkDay],
CONSTRAINT [CK_WorkDayRate] CHECK ([WorkDay] like '[0-3]')
GO

ALTER TABLE [dbo].[WorkOTRateDefaults] WITH NOCHECK ADD
CONSTRAINT [DF_WorkDayRateDefaults_WorkDay] DEFAULT (0) FOR [WorkDay],
CONSTRAINT [CK_WorkDayRateDefaults] CHECK ([WorkDay] like '[0-3]')
GO

ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [DF_WorkOrder_FixedPrice] DEFAULT (0) FOR [FixedPrice],
CONSTRAINT [DF_WorkOrder_Prepaid] DEFAULT (0) FOR [Prepaid],
CONSTRAINT [IX_WorkItemOrder] UNIQUE NONCLUSTERED
(
[WorkID],
[OrderNo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkType] WITH NOCHECK ADD
CONSTRAINT [DF_WorkType_WorkItemPriority] DEFAULT (0) FOR
[WorkItemPriority],
CONSTRAINT [DF_WorkType_WorkItemStatus] DEFAULT (0) FOR
[WorkItemStatus],
CONSTRAINT [DF_WorkType_WorkItemResolution] DEFAULT (0) FOR
[WorkItemResolution],
CONSTRAINT [DF_WorkType_WorkItemPhase] DEFAULT (0) FOR
[WorkItemPhase],
CONSTRAINT [DF_WorkType_WorkItemArea] DEFAULT (0) FOR [WorkItemArea],
CONSTRAINT [DF_WorkType_WorkItemVersion] DEFAULT (0) FOR
[WorkItemVersion],
CONSTRAINT [IX_WorkType] UNIQUE NONCLUSTERED
(
[Type]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkVersion] WITH NOCHECK ADD
CONSTRAINT [IX_WorkVersion] UNIQUE NONCLUSTERED
(
[Version]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[HolidayAllocation] ADD
CONSTRAINT [FK_HolidayAllocation_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Invoice] ADD
CONSTRAINT [FK_Invoice_WorkOrder] FOREIGN KEY
(
[WorkOrderID]
) REFERENCES [dbo].[WorkOrder] (
[WorkOrderID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RecurringWorkItem] ADD
CONSTRAINT [FK_RecurringWorkItem_Employee] FOREIGN KEY
(
[AssignedEmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
)
GO

ALTER TABLE [dbo].[WeekProfile] ADD
CONSTRAINT [FK_HoursProfile_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Work] ADD
CONSTRAINT [FK_Work_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
)
GO

ALTER TABLE [dbo].[WorkComponent] ADD
CONSTRAINT [FK_WorkComponent_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkComponentDefaults] ADD
CONSTRAINT [FK_WorkTypeComponentDefaults_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkEmployeeRate] ADD
CONSTRAINT [FK_WorkEmployeeRate_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_WorkEmployeeRate_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkItem] ADD
CONSTRAINT [FK_WorkItem_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_WorkItem_WorkComponent] FOREIGN KEY
(
[WorkComponentID]
) REFERENCES [dbo].[WorkComponent] (
[WorkComponentID]
),
CONSTRAINT [FK_WorkItem_WorkItemAreaLUT] FOREIGN KEY
(
[AreaID]
) REFERENCES [dbo].[WorkItemAreaLUT] (
[WorkItemAreaID]
),
CONSTRAINT [FK_WorkItem_WorkItemPhaseLUT] FOREIGN KEY
(
[PhaseID]
) REFERENCES [dbo].[WorkItemPhaseLUT] (
[WorkItemPhaseID]
),
CONSTRAINT [FK_WorkItem_WorkItemPriorityLUT] FOREIGN KEY
(
[PriorityID]
) REFERENCES [dbo].[WorkItemPriorityLUT] (
[PriorityID]
),
CONSTRAINT [FK_WorkItem_WorkItemResolutionLUT] FOREIGN KEY
(
[ResolutionID]
) REFERENCES [dbo].[WorkItemResolutionLUT] (
[WorkItemResolutionID]
),
CONSTRAINT [FK_WorkItem_WorkItemStatusLUT] FOREIGN KEY
(
[StatusID]
) REFERENCES [dbo].[WorkItemStatusLUT] (
[WorkItemStatusID]
),
CONSTRAINT [FK_WorkItem_WorkItemType] FOREIGN KEY
(
[TypeID]
) REFERENCES [dbo].[WorkItemTypeLUT] (
[WorkItemTypeID]
),
CONSTRAINT [FK_WorkItem_WorkOrder] FOREIGN KEY
(
[WorkOrderID]
) REFERENCES [dbo].[WorkOrder] (
[WorkOrderID]
),
CONSTRAINT [FK_WorkItem_WorkVersion] FOREIGN KEY
(
[VersionID]
) REFERENCES [dbo].[WorkVersion] (
[WorkVersionID]
),
CONSTRAINT [FK_WorkItem_WorkVersion1] FOREIGN KEY
(
[VersionReleasedID]
) REFERENCES [dbo].[WorkVersion] (
[WorkVersionID]
)
GO

ALTER TABLE [dbo].[WorkItemAssignment] ADD
CONSTRAINT [FK_WorkItemAssignment_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_WorkItemAssignment_WorkItem] FOREIGN KEY
(
[WorkItemID]
) REFERENCES [dbo].[WorkItem] (
[WorkItemID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkItemTypeLUT] ADD
CONSTRAINT [FK_WorkItemTypeLUT_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkOTRate] ADD
CONSTRAINT [FK_WorkOTRate_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkOrder] ADD
CONSTRAINT [FK_Work_WorkOrder] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WorkType] ADD
CONSTRAINT [FK_WorkType_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
)
GO

ALTER TABLE [dbo].[WorkVersion] ADD
CONSTRAINT [FK_WorkVersion_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO|||It only gets worse -- repeated groups for the days of the week, numeric
codes stored as strings, etc.!

I hope you fired the moron who did this to you. But at this point,the
best thing is to design a relational schema, back the data out of this
mess as best you can (the lack of data integrity is going to make that
a bit harder than you think) and start over.

Friday, March 30, 2012

ID for New record

IAS Logging

Wednesday, March 28, 2012

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

Monday, March 26, 2012

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

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

Friday, March 23, 2012

I want to use multiple select queries in a stored procedure

I want to use multiple select queries in a stored procedure

I want to use multiple select queries in a stored procedure

I want to use in a stored procedures two databases

Wednesday, March 21, 2012

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

I want to do paging in a stored procedure, but have some problem with it

i want to convert sql stored procedures to dll

Monday, March 19, 2012

I want to avoid using a cursor, please!

help emergency

Blog Archive

About Me