Is there a system table containing the information if a package has been success

Hi all,
Im not really sure if this is a PHP or and SQL problem but here goes.

Im using MSSQL and have developed a webpage that enables users to run various PACKAGES manually, however I need to display if the package has been successfully run.

Is there a system table that logs package information or is there a PHP function that I can use.

Thanks

P.s I know there are some system tables with the information for jobs but I do not want to create a job for each package.If you say about DTS... then you can find it in msdb database
msdb..sysdtspackagelog... and etc.

Is there a System Stored Procedure that scripts tables?

Hello
I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.
Thanks a lot.Hello

I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.

Thanks a lot.

I think you need the Server Management Objects (SMO) to do what you are thinking of. SMO works in SQL 2005. There's an earlier version of it in SQL 2000, but for the life of me, I can't remember the name right now.

Regards,

hmscott|||I imagine you could create one by using Profiler to examine what happens when you right click a table in Enterprise Manager select "All Tasks -> Generate SQL Script -> Preview". Be aware ahead of time, it's not a one-step process...|||This free console app I wrote may be of use to you. it will generate scripts for all objects in any 2000 or 2005 database. open source so you can tweak it if you want. it uses SMO.

I wrote it as a way to easily export the DDL for a database for checkin to a source control system.

http://www.elsasoft.org/tools.htm

hope it's useful to you!|||Thanks a lot!sql

Is there a system generated row id in SQL Server 2000?

Hi,
I wanted to perform a select and a delete of a row based on a system generat
ed row id (like what Oracle has). However after researching this issue I've
found that there is no equivalent of Oracle Rowid in SQL Server.
I know I can generate my own row numbers using identity columns and then pro
cess the results but I was curious to know whether or not SQL Server actuall
y had a system generated row id. My thinking was that each row has to have a
row id because otherwise h
ow will b-tree indexes work (especially on non unique columns)?
Documentation doesn't really give out much information on what the Row struc
ture looks like in SQL Server (or at least I haven't been able to find it) n
ot have I found information on exactly what does a row pointer looks like in
a B-Tree index leaf page.
Can anyone suggest a source of information on the above? Also, if there is a
system generated row id in SQL Server, is any way at all of accessing it?hi;
am not a SQL expert here, but i think there is a datatype call IDENTITY that
auto generate an sequencing row number. that cld be what u need.
amnyone can verify ?
"SJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1196BF60-EF0E-4E47-853F-18E79C95279E@.microsoft.com...
> Hi,
> I wanted to perform a select and a delete of a row based on a system
generated row id (like what Oracle has). However after researching this
issue I've found that there is no equivalent of Oracle Rowid in SQL Server.
> I know I can generate my own row numbers using identity columns and then
process the results but I was curious to know whether or not SQL Server
actually had a system generated row id. My thinking was that each row has to
have a row id because otherwise how will b-tree indexes work (especially on
non unique columns)?
> Documentation doesn't really give out much information on what the Row
structure looks like in SQL Server (or at least I haven't been able to find
it) not have I found information on exactly what does a row pointer looks
like in a B-Tree index leaf page.
> Can anyone suggest a source of information on the above? Also, if there is
a system generated row id in SQL Server, is any way at all of accessing it?|||You will have to generate your own rowid... SQL does not expose one ( as
some other DBMS's do.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1196BF60-EF0E-4E47-853F-18E79C95279E@.microsoft.com...
> Hi,
> I wanted to perform a select and a delete of a row based on a system
generated row id (like what Oracle has). However after researching this
issue I've found that there is no equivalent of Oracle Rowid in SQL Server.
> I know I can generate my own row numbers using identity columns and then
process the results but I was curious to know whether or not SQL Server
actually had a system generated row id. My thinking was that each row has to
have a row id because otherwise how will b-tree indexes work (especially on
non unique columns)?
> Documentation doesn't really give out much information on what the Row
structure looks like in SQL Server (or at least I haven't been able to find
it) not have I found information on exactly what does a row pointer looks
like in a B-Tree index leaf page.
> Can anyone suggest a source of information on the above? Also, if there is
a system generated row id in SQL Server, is any way at all of accessing it?|||Yes I think the keywords here are "sql does not expose one". I have been sus
pecting that although there may be a system generated row id, it wouldn't be
accesible to external users.|||To the best of my knowledge there really isn't one.
I don't worry about internal page strcutures all that much, so I could be
wrong. I'm sure Kalen's book Inside SQL Server 2000 would have a lot of
great info on this topic if you're really interested.
But to the best of my knowledge, internal id's are based on both page number
and row offset (or a row number) on that specific page. I do not believe
there is an internal ID that is unique across a table space. The short
answer is that it doesn't matter since there definitely isn't one exposed.
But I don't think one even exists...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"SJ" <anonymous@.discussions.microsoft.com> wrote in message
news:56E22894-9ACE-4654-B93B-7E375D21A367@.microsoft.com...
> Yes I think the keywords here are "sql does not expose one". I have been
suspecting that although there may be a system generated row id, it wouldn't
be accesible to external users.|||Brian is correct - there is no exposed unique RID for a row.
There are two types of RIDs we use internally, physical and logical.
Physical RIDs are F:P:S, where F is the file ID, P is the page number in the
file and S is the slot number on the page. These are only used for locating
heap rows from non-clustered indexes over heaps (i.e. each row in a
non-clustered index over a heap contains the physical RID of the
corresponding row in the heap itself). Physical RIDs are not exposed,
although you will see them sometimes in DBCC CHECK* error messages.
Logical RIDs are the keys of the index the row belongs too. Each row in a
non-clustered index has a logical RID. Each row in a non-clustered index
over a clustered index also contains the logical RID of the corresponding
row in the clustered index).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:eSCl$8zEEHA.2404@.TK2MSFTNGP11.phx.gbl...
> To the best of my knowledge there really isn't one.
> I don't worry about internal page strcutures all that much, so I could be
> wrong. I'm sure Kalen's book Inside SQL Server 2000 would have a lot of
> great info on this topic if you're really interested.
> But to the best of my knowledge, internal id's are based on both page
number
> and row offset (or a row number) on that specific page. I do not believe
> there is an internal ID that is unique across a table space. The short
> answer is that it doesn't matter since there definitely isn't one exposed.
> But I don't think one even exists...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "SJ" <anonymous@.discussions.microsoft.com> wrote in message
> news:56E22894-9ACE-4654-B93B-7E375D21A367@.microsoft.com...
> suspecting that although there may be a system generated row id, it
wouldn't
> be accesible to external users.
>|||Thank you all for you responses.|||The only thing I would add is the reason that SQL Server doesn't expose row
ids. Systems that expose row ids require more offline and manual
maintenance than systems that don't expose row ids. That's because systems
that expose row ids can't just move rows around without breaking
applications. Systems that don't expose row ids can dynamically reorganize
data at any time since no user will ever request the data by rowid.
First generation systems such as Rdb and Oracle exposed rowids, second
generation systems such as Tandem and Sybase saw the errors in this and did
not expose them. Of course once exposed it's almost impossible to take away
the feature, so Oracle still has it.
Hal Berenson, SQL Server MVP
VP, Yukon Readiness
Scalability Experts, Inc.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uyL9fz3EEHA.1456@.TK2MSFTNGP09.phx.gbl...
> Brian is correct - there is no exposed unique RID for a row.
> There are two types of RIDs we use internally, physical and logical.
> Physical RIDs are F:P:S, where F is the file ID, P is the page number in
the
> file and S is the slot number on the page. These are only used for
locating
> heap rows from non-clustered indexes over heaps (i.e. each row in a
> non-clustered index over a heap contains the physical RID of the
> corresponding row in the heap itself). Physical RIDs are not exposed,
> although you will see them sometimes in DBCC CHECK* error messages.
> Logical RIDs are the keys of the index the row belongs too. Each row in a
> non-clustered index has a logical RID. Each row in a non-clustered index
> over a clustered index also contains the logical RID of the corresponding
> row in the clustered index).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:eSCl$8zEEHA.2404@.TK2MSFTNGP11.phx.gbl...
be
> number
exposed.
been
> wouldn't
>

is there a syntax t-sql for generate sql scripts.

Is there syntaxs t-sql, function or others to generate
sql script of objects (table, view, stored procedure,..)?
I know we can do manualy from right click and then click
generate all scripts ? but can we do it with programming ?
thanks.For most stored procedures (assuming they are <= 8000 characters) you can
use sp_helptext, you can also SELECT ROUTINE_DEFINITION FROM
INFORMATION_SCHEMA.ROUTINES in your script.
For tables, it's a little more complicated, especially if you have
primary/foreign keys, constraints, default values, etc. I think if you're
going about it this way: create the tables, then script them properly, you
should think about doing it the other way around. :-)
"kresna rudy kurniawan" <kresna_rk@.hotmail.com> wrote in message
news:03e901c39c3b$d15a5eb0$a401280a@.phx.gbl...
> Is there syntaxs t-sql, function or others to generate
> sql script of objects (table, view, stored procedure,..)?
> I know we can do manualy from right click and then click
> generate all scripts ? but can we do it with programming ?
> thanks.|||> generate all scripts ? but can we do it with programming ?
Your best bet is SQL-DMO. Though you can invoke SQL-DMO objects and their
Script methods via T-SQL's sp_OAxxx procedures, you are much better off
using a scripting language such as VB or Perl.
Of course, if you like to do it the hard way and like to re-invent the
wheel, you can always query the system tables and construct the scripts
entirely in T-SQL. That won't be pretty though.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"kresna rudy kurniawan" <kresna_rk@.hotmail.com> wrote in message
news:03e901c39c3b$d15a5eb0$a401280a@.phx.gbl...
> Is there syntaxs t-sql, function or others to generate
> sql script of objects (table, view, stored procedure,..)?
> I know we can do manualy from right click and then click
> generate all scripts ? but can we do it with programming ?
> thanks.

Is there a style sheet or theme sheet available for reports

I'm currently building several reports which need an identical look/feel to them. I know that I could create a template which would incorporate any of its properties into any new report that uses it. However, it doesn't look like those properties are inherited by the child report. I need to be able to modify the look/feel of all my reports and I'd rather not have to make updates to each individual report. Is there a way to either set up the template or create a style sheet to accomodate this?No. This is a great suggestion for future versions of the product. You can add a report as a template in Report Designer, but there is no template that can be applied to reports already deployed to modify common report items.

Is there a statement to view table description in MS SQL

In Oracle I could type "Desc <table name> " to get the table and column information. Is there a similar statement in SQL 2000 to view a table description?right click the table and select "Design Table"|||I am using Query Analyzer to execute the SQL statment. I can not find Design Table in Query Analyzer. Please provide me more info.

Thanks|||In query analyzer, you can use SP_COLUMNS [table name] to get a list of all the columns of a table, along with all the properties of those columns.

I would recommend using "results in grid" to see the results.

Or for far more details about your table, you could use SP_HELP [table name]. This would give you several results about the table.

But I believe SP_COLUMNS will answer your question.sql

Is there a SQLXML 4 version in the works?

Saw a post on another message board dated Feb '02 from Michael Brundage:
"FWIW, the next version of SQLXML will interoperate much better with the rest of .Net.
The .Net classes we released in SQLXML3 were only a partial solution. And of course
as you yourself observed, the .Net frameworks themselves are just in their very first
version, and will evolve over time to better handle your scenarios."
Is there a V4 coming that integrates better with .NET, and doesnt do so much interop?
"Paul Hester" <phester@.microsoft.com> wrote in message
news:C9EB80D2-B29F-4963-AFA2-E1DD68D69DA0@.microsoft.com...
[snip]
> Is there a V4 coming that integrates better with .NET, and doesnt do so
much interop?
Yes. SqlXml 4.0 will also be know as SQL Server 2005.
Bryant
|||Obviously Yukon will have fantastic XML support compared to SQLXML 3Sp2. But Yukon being deployed in large numbers is still over at least a year and a half away, likely more.
|||We are planning a release with Sql Server 2005, but it will be updates to
the current technology.
Are you having any issues with interop? Or is it just that interop is in
fact there?
Irwin Dolobowsky
Program Manager - SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Hester" <phester@.microsoft.com> wrote in message
news:C9EB80D2-B29F-4963-AFA2-E1DD68D69DA0@.microsoft.com...
> Saw a post on another message board dated Feb '02 from Michael Brundage:
> "FWIW, the next version of SQLXML will interoperate much better with the
> rest of .Net.
> The .Net classes we released in SQLXML3 were only a partial solution. And
> of course
> as you yourself observed, the .Net frameworks themselves are just in their
> very first
> version, and will evolve over time to better handle your scenarios."
> Is there a V4 coming that integrates better with .NET, and doesnt do so
> much interop?
|||We're considering using SQLXML as the primary interface to SQL for our new .NET OLTP architecture, as it provides the very attractive means of using XSD'd as a layer over the database. Obviously we'd like our new applications to perform. However, it seems
there is considerable overhead to this. XML parsing and XSD mapping (which we understand is inherent in using XML), combined with the interop and OLEDB layers, and that the more versatile and compact updategrams aren't directly supported in DataSets (we
considered developing our own methods to iterate over a DataSet's schema and tables, etc. to generate) has left us questioning the viability of using SQLXML. It's my sense SQLXML could be much more intergrated with .NET, with more (or all) of it implement
ed natively in .NET, and directly supporting updategrams as the means to update the database from a DataSet. All if this lead to my original post.

Is there a Sql Svr 2003 Reporting Services version?

I am hosted by an office in MN. At our end we will have report browser and
VS 2003 for development.
The host office is on sql 2003, according to the logo that comes up.
I was wondering if they have RS 2003 (i asked and have not received a
response)?
Is there such a thing? All I see in the forum are msgs about 2000 and 2005.
Thanks.
--
MichaelMHi
No, RS 2000 was released as an add-on for SQL Server 2000 about 2 years ago.
The next release will be SQL Server 2005 RS on 7 November 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0E9157D4-9408-4EEC-B854-38D3C411DE4A@.microsoft.com...
>I am hosted by an office in MN. At our end we will have report browser and
> VS 2003 for development.
> The host office is on sql 2003, according to the logo that comes up.
> I was wondering if they have RS 2003 (i asked and have not received a
> response)?
> Is there such a thing? All I see in the forum are msgs about 2000 and
> 2005.
> Thanks.
> --
> MichaelM|||Thanks, Mike.
That answers my question.
--
MichaelM
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No, RS 2000 was released as an add-on for SQL Server 2000 about 2 years ago.
> The next release will be SQL Server 2005 RS on 7 November 2005.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
> news:0E9157D4-9408-4EEC-B854-38D3C411DE4A@.microsoft.com...
> >I am hosted by an office in MN. At our end we will have report browser and
> > VS 2003 for development.
> >
> > The host office is on sql 2003, according to the logo that comes up.
> > I was wondering if they have RS 2003 (i asked and have not received a
> > response)?
> > Is there such a thing? All I see in the forum are msgs about 2000 and
> > 2005.
> >
> > Thanks.
> > --
> > MichaelM
>
>

Is there a SQL Server equivalent of the Oracle 'decode' function?

I am trying to redeploy an Oracle system using a SQL server database, but am
getting a SQL error saying that 'decode' is not a vaild SQL command.
Is there a SQL Server equivalent of the Oracle function 'decode'?
IIRC, the equivalent is the CASE construct. Check it out in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"elaine" <elaine@.discussions.microsoft.com> wrote in message
news:AA50F50A-EB1A-463E-BC93-0E6073108A75@.microsoft.com...
I am trying to redeploy an Oracle system using a SQL server database, but am
getting a SQL error saying that 'decode' is not a vaild SQL command.
Is there a SQL Server equivalent of the Oracle function 'decode'?
|||Use CASE function
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZ0eFHPvEHA.592@.TK2MSFTNGP15.phx.gbl...
> IIRC, the equivalent is the CASE construct. Check it out in the BOL.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "elaine" <elaine@.discussions.microsoft.com> wrote in message
> news:AA50F50A-EB1A-463E-BC93-0E6073108A75@.microsoft.com...
> I am trying to redeploy an Oracle system using a SQL server database, but
> am
> getting a SQL error saying that 'decode' is not a vaild SQL command.
> Is there a SQL Server equivalent of the Oracle function 'decode'?
>

Is there a SQL Server equivalent of the Oracle 'decode' function?

I am trying to redeploy an Oracle system using a SQL server database, but am
getting a SQL error saying that 'decode' is not a vaild SQL command.
Is there a SQL Server equivalent of the Oracle function 'decode'?IIRC, the equivalent is the CASE construct. Check it out in the BOL.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"elaine" <elaine@.discussions.microsoft.com> wrote in message
news:AA50F50A-EB1A-463E-BC93-0E6073108A75@.microsoft.com...
I am trying to redeploy an Oracle system using a SQL server database, but am
getting a SQL error saying that 'decode' is not a vaild SQL command.
Is there a SQL Server equivalent of the Oracle function 'decode'?|||Use CASE function
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZ0eFHPvEHA.592@.TK2MSFTNGP15.phx.gbl...
> IIRC, the equivalent is the CASE construct. Check it out in the BOL.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "elaine" <elaine@.discussions.microsoft.com> wrote in message
> news:AA50F50A-EB1A-463E-BC93-0E6073108A75@.microsoft.com...
> I am trying to redeploy an Oracle system using a SQL server database, but
> am
> getting a SQL error saying that 'decode' is not a vaild SQL command.
> Is there a SQL Server equivalent of the Oracle function 'decode'?
>

Is there a SQL Server equivalent of the Oracle 'decode' function?

I am trying to redeploy an Oracle system using a SQL server database, but am
getting a SQL error saying that 'decode' is not a vaild SQL command.
Is there a SQL Server equivalent of the Oracle function 'decode'?IIRC, the equivalent is the CASE construct. Check it out in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"elaine" <elaine@.discussions.microsoft.com> wrote in message
news:AA50F50A-EB1A-463E-BC93-0E6073108A75@.microsoft.com...
I am trying to redeploy an Oracle system using a SQL server database, but am
getting a SQL error saying that 'decode' is not a vaild SQL command.
Is there a SQL Server equivalent of the Oracle function 'decode'?|||Use CASE function
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZ0eFHPvEHA.592@.TK2MSFTNGP15.phx.gbl...
> IIRC, the equivalent is the CASE construct. Check it out in the BOL.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "elaine" <elaine@.discussions.microsoft.com> wrote in message
> news:AA50F50A-EB1A-463E-BC93-0E6073108A75@.microsoft.com...
> I am trying to redeploy an Oracle system using a SQL server database, but
> am
> getting a SQL error saying that 'decode' is not a vaild SQL command.
> Is there a SQL Server equivalent of the Oracle function 'decode'?
>sql

Is there a SQL Server equivalent for Oracle synonyms?

I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.I found out that if the tables belong to 'dbo' they can be accessed without the fully qualified names.|||Specifying schema owner is a good practice. It releaves the optimizer to do it EVERY time you choose to be lazy about it.

Is there a SQL Server API to flush all buffers to disk?

I am looking for an API to flush all data in memory held by SQL Server
to disk. Also, is there a tool for SQL Server like eseutil for
Exchange that lets you correct a SQL database?"SK" <sriharik@.hotmail.com> wrote in message
news:5c9e9c28.0309032019.43b6050b@.posting.google.c om...
> I am looking for an API to flush all data in memory held by SQL Server
> to disk. Also, is there a tool for SQL Server like eseutil for
> Exchange that lets you correct a SQL database?

What do you mean by "correct"

And DBCC DROPCLEANBUFFERS may be something you're looking for.
Otherwise not sure what you mean by flush buffers to disk?

Or do you mean Checkpoint?|||Look for CKECKPOINT command in BOL

"SK" <sriharik@.hotmail.com> a crit dans le message de news:
5c9e9c28.0309032019.43b6050b@.posting.google.com...
> I am looking for an API to flush all data in memory held by SQL Server
> to disk. Also, is there a tool for SQL Server like eseutil for
> Exchange that lets you correct a SQL database?|||"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<lEz5b.34219$yG2.18169@.twister.nyroc.rr.com>...
> "SK" <sriharik@.hotmail.com> wrote in message
> news:5c9e9c28.0309032019.43b6050b@.posting.google.c om...
> > I am looking for an API to flush all data in memory held by SQL Server
> > to disk. Also, is there a tool for SQL Server like eseutil for
> > Exchange that lets you correct a SQL database?
> What do you mean by "correct"
>
> And DBCC DROPCLEANBUFFERS may be something you're looking for.
> Otherwise not sure what you mean by flush buffers to disk?
> Or do you mean Checkpoint?

Well, if I asynchronously replicate (note that there is no
checkpointing in async replication) a SQL Server database, to bring up
the replicated copy of SQL Server, is there a utility that will help
me verify the integrity of the database and correct any "correctable"
portions of the database. eseutil does this for the Exchange JET
database format. Is there an equivalent tool for SQL Server?|||"SK" <sriharik@.hotmail.com> wrote in message
news:5c9e9c28.0309041725.6ad6a1d5@.posting.google.c om...
> "Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message
news:<lEz5b.34219$yG2.18169@.twister.nyroc.rr.com>...
> > "SK" <sriharik@.hotmail.com> wrote in message
> > news:5c9e9c28.0309032019.43b6050b@.posting.google.c om...
> > > I am looking for an API to flush all data in memory held by SQL Server
> > > to disk. Also, is there a tool for SQL Server like eseutil for
> > > Exchange that lets you correct a SQL database?
> > What do you mean by "correct"
> > And DBCC DROPCLEANBUFFERS may be something you're looking for.
> > Otherwise not sure what you mean by flush buffers to disk?
> > Or do you mean Checkpoint?
> Well, if I asynchronously replicate (note that there is no
> checkpointing in async replication) a SQL Server database, to bring up
> the replicated copy of SQL Server, is there a utility that will help
> me verify the integrity of the database and correct any "correctable"
> portions of the database. eseutil does this for the Exchange JET
> database format. Is there an equivalent tool for SQL Server?

I'm not sure what you mean exactly by asynchronously replicate the database?

Do you mean the transactional replication that SQL Server does, or log
shipping or what?

Unless you use clustering, any failover solution will be necessarily
slightly behind the original live DB.

As for verifying the integrity, DBCC CHECKDB will do that. But I don't
think that's what you mean here.

If you mean making sure non-committed transactions are rolled forward or
backward as appropriate, depending on what you're doing, that's basically
done automatically.

Keep in mind, unlike JET, SQL Server is designed from the ground up to be
ACID compliant.

I'd also at this point recommend Kalen Delany's Inside SQL Server 2000 as a
good starting point.|||SK (sriharik@.hotmail.com) writes:
> "Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message
news:<lEz5b.34219$yG2.18169@.twister.nyroc.rr.com>...
>> "SK" <sriharik@.hotmail.com> wrote in message
>> news:5c9e9c28.0309032019.43b6050b@.posting.google.c om...
>> > I am looking for an API to flush all data in memory held by SQL Server
>> > to disk. Also, is there a tool for SQL Server like eseutil for
>> > Exchange that lets you correct a SQL database?
>>
>> What do you mean by "correct"
>>
>>
>> And DBCC DROPCLEANBUFFERS may be something you're looking for.
>> Otherwise not sure what you mean by flush buffers to disk?
>>
>> Or do you mean Checkpoint?
> Well, if I asynchronously replicate (note that there is no
> checkpointing in async replication) a SQL Server database,

In SQL Server parlance CHECKPOINT is a process where SQL Server writes
all updated data pages to disk. This happens automatically about once
a minute, or if you request it with the CHECKPOINT command.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Is there a sql database function to check connection string?

Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?
Thanks,
CedricHmm. Well you could try something like SELECT GETDATE() or SELECT @.@.VERSION, which are not database schema-specific.

Is there a SP4 for SQL Server 2000 for 32 bits Servers?

Hi.
Is there a SP4 for SQL Server 2000 for 32 bits Servers?
Thanks.Yes
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Sql2k32sp4.exe
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Monillo" <jcgalarzar@.hotmail.com> wrote in message news:OcuAzu2oFHA.1048@.tk2msftngp13.phx.gbl...
> Hi.
> Is there a SP4 for SQL Server 2000 for 32 bits Servers?
> Thanks.
>
>|||Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió
en el mensaje news:uYt2dy2oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> Yes
> http://www.microsoft.com/sql/downloads/2000/sp4.mspx
> Sql2k32sp4.exe
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Monillo" <jcgalarzar@.hotmail.com> wrote in message
> news:OcuAzu2oFHA.1048@.tk2msftngp13.phx.gbl...
>> Hi.
>> Is there a SP4 for SQL Server 2000 for 32 bits Servers?
>> Thanks.
>>

Is there a SP4 for SQL Server 2000 for 32 bits Servers?

Hi.
Is there a SP4 for SQL Server 2000 for 32 bits Servers?
Thanks.
Yes
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Sql2k32sp4.exe
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Monillo" <jcgalarzar@.hotmail.com> wrote in message news:OcuAzu2oFHA.1048@.tk2msftngp13.phx.gbl...
> Hi.
> Is there a SP4 for SQL Server 2000 for 32 bits Servers?
> Thanks.
>
>
|||Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uYt2dy2oFHA.3828@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Yes
> http://www.microsoft.com/sql/downloads/2000/sp4.mspx
> Sql2k32sp4.exe
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Monillo" <jcgalarzar@.hotmail.com> wrote in message
> news:OcuAzu2oFHA.1048@.tk2msftngp13.phx.gbl...
sql

Is there a SP4 for SQL Server 2000 for 32 bits Servers?

Hi.
Is there a SP4 for SQL Server 2000 for 32 bits Servers?
Thanks.Yes
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Sql2k32sp4.exe
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Monillo" <jcgalarzar@.hotmail.com> wrote in message news:OcuAzu2oFHA.1048@.tk2msftngp13.phx.g
bl...
> Hi.
> Is there a SP4 for SQL Server 2000 for 32 bits Servers?
> Thanks.
>
>|||Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uYt2dy2oFHA.3828@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Yes
> http://www.microsoft.com/sql/downloads/2000/sp4.mspx
> Sql2k32sp4.exe
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Monillo" <jcgalarzar@.hotmail.com> wrote in message
> news:OcuAzu2oFHA.1048@.tk2msftngp13.phx.gbl...

Is there a sp_trace method to read input from a template file?

Is there a way to pass to template file to an sp_trace method to specify the
trace parameters to the profiler?
Thanks,
Reshma.Reshma wrote:
> Is there a way to pass to template file to an sp_trace method to
> specify the trace parameters to the profiler?
> Thanks,
> Reshma.
You can specify the following on the Profiler command-line:
/S Server
/D Database
/B Trace table to load
/E Trusted Connection
/U Login Name
/P Password
/F Trace File to load
/T Template file to load or use when starting a new trace
You can also script your own traces using the SQL Trace API (see
sp_trace* procs in BOL).
David Gugick
Imceda Software
www.imceda.com|||Yes, Do you know what sp_trace api can be used to read in the trace
parameters from a template. I did not find such a functionality with any of
sp_trace methods.
Is there any other way of doing it?
Thanks,
Reshma.
"David Gugick" wrote:
> Reshma wrote:
> > Is there a way to pass to template file to an sp_trace method to
> > specify the trace parameters to the profiler?
> >
> > Thanks,
> > Reshma.
> You can specify the following on the Profiler command-line:
> /S Server
> /D Database
> /B Trace table to load
> /E Trusted Connection
> /U Login Name
> /P Password
> /F Trace File to load
> /T Template file to load or use when starting a new trace
> You can also script your own traces using the SQL Trace API (see
> sp_trace* procs in BOL).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Reshma wrote:
>> Is there a way to pass to template file to an sp_trace method to
>> specify the trace parameters to the profiler?
>> Thanks,
>> Reshma.
When you define a server-side trace, you do not use templates.
Everything is added to the trace programmatically using the sp_trace*()
procedures:
sp_trace_create
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
You can query running trace information with the fn_trace*() system
functions:
fn_trace_geteventinfo
fn_trace_getfilterinfo
fn_trace_getinfo
You can query data from a trace table (created with a server-side trace)
using:
fn_trace_gettable
The easiest way to see the generated server-side trace T-SQL, is to
define the trace and filters in Profiler, start the trace, and then
select the File --> Script Trace menu option. Profiler creates the
script to create and start the trace. Stopping the trace is your
responsibility. Keep in mind that Profiler binds all columns to all
selected events. It's not necessary to do this programmatically. You can
select the columns you're interested in for each event and only bind
those.
You might also want to take a look at our Coefficient product, which
provides an easy method to perform SQL performance analysis. If you have
any questions about Coefficient, our support staff is available to
assist you.
--
David Gugick
Imceda Software
www.imceda.com

Is there a sp_trace method to read input from a template file?

Is there a way to pass to template file to an sp_trace method to specify the
trace parameters to the profiler?
Thanks,
Reshma.
Reshma wrote:
> Is there a way to pass to template file to an sp_trace method to
> specify the trace parameters to the profiler?
> Thanks,
> Reshma.
You can specify the following on the Profiler command-line:
/S Server
/D Database
/B Trace table to load
/E Trusted Connection
/U Login Name
/P Password
/F Trace File to load
/T Template file to load or use when starting a new trace
You can also script your own traces using the SQL Trace API (see
sp_trace* procs in BOL).
David Gugick
Imceda Software
www.imceda.com

Is there a sp_trace method to read input from a template file?

Is there a way to pass to template file to an sp_trace method to specify the
trace parameters to the profiler?
Thanks,
Reshma.Reshma wrote:
> Is there a way to pass to template file to an sp_trace method to
> specify the trace parameters to the profiler?
> Thanks,
> Reshma.
You can specify the following on the Profiler command-line:
/S Server
/D Database
/B Trace table to load
/E Trusted Connection
/U Login Name
/P Password
/F Trace File to load
/T Template file to load or use when starting a new trace
You can also script your own traces using the SQL Trace API (see
sp_trace* procs in BOL).
David Gugick
Imceda Software
www.imceda.com

Is there a SP that will logout users from a database?

Hi,
I looked everywhere in System Stored Procedure docs for MSDE 2005 and I cant find a stored procedure that will force logout users from a database.
Is there such a thing? If not, is there another way?
Thanks,Depending on what you mean by "force logout" check either KILL (http://msdn2.microsoft.com/en-us/library/aa933230(SQL.80).aspx) OR ALTER DATABASE ... WITH ROLLBACK IMMEDIATE (http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx).

-PatP|||Are you sa on the box?

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE|||hmm... what you see here is what i get from the sp_who command followed by the sp_lock command. I "looks" like I can figure out a way to identify process 52 and then KILL it, but its not going to be simple. thanks guys.

(processes 1-50 are system processes)
51 0 sleeping
QOR\williams
QOR
master
AWAITING COMMAND 0
52 0 runnable
QOR\williams
QOR
master
SELECT 0

(21 rows affected)
1> exec sp_lock
2> go
spid dbid ObjId IndId Type Resource Mode Status
-- -- ---- -- -- ---------- --- --
52 1 1115151018 0 TAB IS GRANT|||I discoverd that sp_who2 is way better than sp_who.

Ok, excuse the totally invalid SQL language below, but what I REALLY REALLY need is something like this:

KILL spid of processes where exec sp_lock.DBName = "aaa"

Is this possible with a osql command? Am I overlooking an easy way to do this or do I need to do it a hard way?|||I've got an "old friend" that I haven't used in a long time, but I recreated from memory. It might help you, but I'll leave the decision of whether to use it or not up to you!IF EXISTS (SELECT * FROM dbo.sysobjects AS o WHERE 'p_KillCulprits' = name) DROP PROCEDURE p_KillCulprits
GO
-- ptp 20071003 Kill spids that are blocking others, but not blocked

CREATE PROCEDURE p_KillCulprits
AS

DECLARE @.cCmd VARCHAR(40)

DECLARE zCulprits CURSOR FOR
SELECT 'KILL ' + CAST(c.spid AS VARCHAR(6))
FROM master.dbo.sysprocesses AS c -- Culprit
WHERE 0 = c.blocked -- Is not blocked
AND EXISTS (SELECT * -- and blocks at least one spid
FROM master.dbo.sysprocesses AS v
WHERE v.blocked = c.spid
AND v.blocked != v.spid) -- Watch for sp2 "feature" !

OPEN zCulprits
FETCH zCulprits INTO @.cCmd

WHILE 0 = @.@.fetch_status
BEGIN
EXECUTE (@.cCmd)
FETCH zCulprits INTO @.cCmd
END

CLOSE zCulprits
DEALLOCATE zCulprits

RETURN
GO-PatP|||We had a huge problem here with blocking locks on a sh*tty system called Advisorware, the nolock hint cleared that problem right up.sql

Is there a smart set-based solution?

I have a column of starting times below (converted to seconds for
simplicity):
500
505
510
535
910
939
944
977
I need to assign a Group ID to the above values, based on a time
interval of 30 seconds, so the correct result would be:
Group ID
500, 1
505, 1
510, 1
535, 2
910, 3
939, 3
944, 4
977, 5
The value of Group ID isn't important (could be 100, 200, etc.) as long
as values in the same 30-second interval have the same Group ID. When a
new group is started, a new 30-second interval is also started.
I have created a solution using SQL loops (row-based processing) but
was wondering if there is an efficient/elegant set-based solution when
there are thousands of records?
Thanks
MowgliNo. Any set based solution will involve nested loops because the value of
the group id is dependent on the values of the starting time in the previous
rows. This is one of the rare cases in which a cursor will actually perform
faster than a set based solution because it can accomplish what you want in
a single pass through the table.
"Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
> I have a column of starting times below (converted to seconds for
> simplicity):
> 500
> 505
> 510
> 535
> 910
> 939
> 944
> 977
> I need to assign a Group ID to the above values, based on a time
> interval of 30 seconds, so the correct result would be:
> Group ID
> 500, 1
> 505, 1
> 510, 1
> 535, 2
> 910, 3
> 939, 3
> 944, 4
> 977, 5
> The value of Group ID isn't important (could be 100, 200, etc.) as long
> as values in the same 30-second interval have the same Group ID. When a
> new group is started, a new 30-second interval is also started.
> I have created a solution using SQL loops (row-based processing) but
> was wondering if there is an efficient/elegant set-based solution when
> there are thousands of records?
>
> Thanks
> Mowgli
>|||If there are a lot of indexes on the table, you may want to cache the
results generated by the cursor in a table variable so that they can be
committed in a single set-based statement.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uf4PvFKlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> No. Any set based solution will involve nested loops because the value of
> the group id is dependent on the values of the starting time in the
previous
> rows. This is one of the rare cases in which a cursor will actually
perform
> faster than a set based solution because it can accomplish what you want
in
> a single pass through the table.
> "Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
> news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
>|||>> I have created a solution using SQL loops (row-based processing) but was wonde
ring if there is an efficient/elegant set-based solution when there are thousands
of records [sic]? <<
Rows are not records. If you keep thinking in the wrong terms, you
will always think that you have to use loops. Create a simple table
for each group and then use a BETWEEN predicate. You can get an entire
year into about a million row table.
CREATE TABLE TimeGroups
(group_id INTEGER NOT NULL PRIMARY KEY
start_time INTEGER NOT NULL,
end_time INTEGER NOT NULL,
CHECK (start_time +30 = end_time));
You should find that this is about 10 times faster than a cursor as the
set gets larger.|||Unfortunately, your solution doesn't answer the original question. How do
you build the table in the first place? In this case a cursor is the
fastest way to find the group id's, because the alternative is a self-join,
which will always require more than one pass through the table. Not every
cursor is evil. Actually, you'll find that the time that it takes for the
cursor based solution increases linearly with the number of rows, whereas
the time that it takes for the set-based solution increases geometrically.
By the way, E. F. Codd used the term "record" in some of his papers. I
wonder, do you add [sic] when you refer to his work?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122689807.624405.127910@.g49g2000cwa.googlegroups.com...
was wondering if there is an efficient/elegant set-based solution when there
are thousands of records [sic]? <<
> Rows are not records. If you keep thinking in the wrong terms, you
> will always think that you have to use loops. Create a simple table
> for each group and then use a BETWEEN predicate. You can get an entire
> year into about a million row table.
> CREATE TABLE TimeGroups
> (group_id INTEGER NOT NULL PRIMARY KEY
> start_time INTEGER NOT NULL,
> end_time INTEGER NOT NULL,
> CHECK (start_time +30 = end_time));
> You should find that this is about 10 times faster than a cursor as the
> set gets larger.
>|||Hi Mr. Celko
You wrote... Rows are not records. If you keep thinking in the wrong terms,
you
will always think that you have to use loops.
Could you elaborate on that a bit.
many thanks
Shahriar
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122689807.624405.127910@.g49g2000cwa.googlegroups.com...
> Rows are not records. If you keep thinking in the wrong terms, you
> will always think that you have to use loops. Create a simple table
> for each group and then use a BETWEEN predicate. You can get an entire
> year into about a million row table.
> CREATE TABLE TimeGroups
> (group_id INTEGER NOT NULL PRIMARY KEY
> start_time INTEGER NOT NULL,
> end_time INTEGER NOT NULL,
> CHECK (start_time +30 = end_time));
> You should find that this is about 10 times faster than a cursor as the
> set gets larger.
>|||Mowgli
I think this may just do it.
Table1 contains the field times.
Table2 contains 2 fields, Times and groupid
insert into table2 (times,groupid) (select times,times/30 from Table1)
Shahriar
"Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
>I have a column of starting times below (converted to seconds for
> simplicity):
> 500
> 505
> 510
> 535
> 910
> 939
> 944
> 977
> I need to assign a Group ID to the above values, based on a time
> interval of 30 seconds, so the correct result would be:
> Group ID
> 500, 1
> 505, 1
> 510, 1
> 535, 2
> 910, 3
> 939, 3
> 944, 4
> 977, 5
> The value of Group ID isn't important (could be 100, 200, etc.) as long
> as values in the same 30-second interval have the same Group ID. When a
> new group is started, a new 30-second interval is also started.
> I have created a solution using SQL loops (row-based processing) but
> was wondering if there is an efficient/elegant set-based solution when
> there are thousands of records?
>
> Thanks
> Mowgli
>|||I tried that: it doesn't work correctly. The start time for each group
after the first depends on whether the start time of the first row for that
group is more than 30 seconds past the start time of the first row for the
previous group. If you use times/30 the interval that includes 500 runs
from 480 through 509, so the row with the 510 start time is in the next
interval. The expected results clearly show that the rows with 500 and 510
are in the same interval.
There is no set-based operation that can accurately calculate the group id
in a single pass. With a cursor, you can cache the start time of the
current group in a variable, and bump the current group id if the start time
for the current cursor row is more than 30 seconds past the cached start
time for the current group.
"Shahriar" <HelloShahriar@.hotmail.com> wrote in message
news:IqEGe.97$4e6.80@.trnddc04...
> Mowgli
> I think this may just do it.
> Table1 contains the field times.
> Table2 contains 2 fields, Times and groupid
>
> insert into table2 (times,groupid) (select times,times/30 from Table1)
> Shahriar
>
> "Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
> news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
>|||hi,
if u are not particular about the groupID, i have a solution for u.
but as per the requirement, group ID definately falls in 30sec intervals.
select num, (num-(select min(num)from #test))/30 GroupID from #test
please let me know if this answers your question
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Mowgli" wrote:

> I have a column of starting times below (converted to seconds for
> simplicity):
> 500
> 505
> 510
> 535
> 910
> 939
> 944
> 977
> I need to assign a Group ID to the above values, based on a time
> interval of 30 seconds, so the correct result would be:
> Group ID
> 500, 1
> 505, 1
> 510, 1
> 535, 2
> 910, 3
> 939, 3
> 944, 4
> 977, 5
> The value of Group ID isn't important (could be 100, 200, etc.) as long
> as values in the same 30-second interval have the same Group ID. When a
> new group is started, a new 30-second interval is also started.
> I have created a solution using SQL loops (row-based processing) but
> was wondering if there is an efficient/elegant set-based solution when
> there are thousands of records?
>
> Thanks
> Mowgli
>|||Brian, Shahriar
Thanks for your input and for concentrating on the problem rather than
on my terminology!
The example I gave uses a time interval of 30 seconds but this can also
be variable (20, 40, 60, etc.).
Brian, perhaps you could demonstrate the inefficient set-based solution
to illustrate your point?
... As the issues may not be clear to anyone who hasn't attempted this
type of "real-life" problem.

Is there a single string or other function in SQL Server that retu

Hello:
Is there a single, undocumented function in SQL Server 2000 or 2005 that
returns the first letter of each word in a sentence or group of words as
upper case and the remaining letters in lower case? We can do this with
custom programming using a combination of string functions but for our
purposes using string functions in combination is too slow with the huge dat
a
sets we deal with.
For Example:
We may receive a name in the following format: mr. john r. doe jr. within a
CSV file. Has Microsoft developed a new string function that will produce
Mr. John R. Doe Jr. by simply feeding the string "mr. john r. doe jr.” to
the
function?No, there is not an undocumented function that does this (or a documented
one for that matter).
Perhaps you should build your function using SQLCLR rather than T-SQL.
SQLCLR tends to outperform T-SQL quite a bit when it comes to string
manipulation.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Chandler Miller" <Chandler Miller@.discussions.microsoft.com> wrote in
message news:72DD96C2-0AFB-4AD2-AF7A-A3B5F6023C2E@.microsoft.com...
> Hello:
> Is there a single, undocumented function in SQL Server 2000 or 2005 that
> returns the first letter of each word in a sentence or group of words as
> upper case and the remaining letters in lower case? We can do this with
> custom programming using a combination of string functions but for our
> purposes using string functions in combination is too slow with the huge
> data
> sets we deal with.
> For Example:
> We may receive a name in the following format: mr. john r. doe jr. within
> a
> CSV file. Has Microsoft developed a new string function that will produce
> Mr. John R. Doe Jr. by simply feeding the string "mr. john r. doe jr." to
> the
> function?
>
>|||A function if there is one, which I highly doubt, would run through the same
steps you probalby are doing already. Can you split the data first, then
upper(data), then combine again ?
or better yet use dts package to import with query (steps of upper, trim,
etc.).
dts packs fly on large datasets.
"Chandler Miller" wrote:

> Hello:
> Is there a single, undocumented function in SQL Server 2000 or 2005 that
> returns the first letter of each word in a sentence or group of words as
> upper case and the remaining letters in lower case? We can do this with
> custom programming using a combination of string functions but for our
> purposes using string functions in combination is too slow with the huge d
ata
> sets we deal with.
> For Example:
> We may receive a name in the following format: mr. john r. doe jr. within
a
> CSV file. Has Microsoft developed a new string function that will produce
> Mr. John R. Doe Jr. by simply feeding the string "mr. john r. doe jr.” t
o the
> function?
>
>|||You could try the function here, though it wasn't produced by Microsoft:
http://www.aspfaq.com/2299
"Chandler Miller" <Chandler Miller@.discussions.microsoft.com> wrote in
message news:72DD96C2-0AFB-4AD2-AF7A-A3B5F6023C2E@.microsoft.com...
> Hello:
> Is there a single, undocumented function in SQL Server 2000 or 2005 that
> returns the first letter of each word in a sentence or group of words as
> upper case and the remaining letters in lower case? We can do this with
> custom programming using a combination of string functions but for our
> purposes using string functions in combination is too slow with the huge
> data
> sets we deal with.
> For Example:
> We may receive a name in the following format: mr. john r. doe jr. within
> a
> CSV file. Has Microsoft developed a new string function that will produce
> Mr. John R. Doe Jr. by simply feeding the string "mr. john r. doe jr.”
> to the
> function?
>
>

Is there a simple way to upload databases to SQLServer2005?

I host more than 40 websites on my server, and I am starting a migration from another hosting company where I used SQLServer2000. I have brought all the databases onto my Dev server, which last night I upgraded to SQLServer2005. Now I'm wanting to upload all those databases (which total about 350 tables in 35 databases) to the new server

In the old SQL2000 I just made a DTS package which was a pretty simple task once I'd figured out what to do. I'm guessing the equivalent process is pretty simple too in SQL2005 but I'm stumped as to how I go about it. Uploading databases to the server is a pretty regular thing for me and I'm going to have hosting clients who wont want to spend a heap of dosh on getting the full blown SQLServer2005 just to upload their data to the server.

What's the trick I'm missing? So far I've spent more than 9 hours and haven't been able to upload a single table.

I see that SSIS is the place to look, (i think). but there are no examples for uploading a table from one SQLServer to another, overwriting the data that's there, which is telling me that perhaps SSIS isnt the place I should be looking for how to do this. And in any case it's a pretty laborious process writing that whole project every time I want to upload a new copy of the database. (or conversely download the onsite database to my local dev machine)

Can anyone tell me (or direct me to a resource somewhere) that tells me how I should be doing this common task?

The task I'm trying to work is to upload a database from a dev server to a production server where the database is already created and registered with the web server as a datasource. So I need to create the tables, indexes, dependencies, foreign keys, storedprocs etc on the production server, dropping any existing ones that might already be there, then populating them with the latest data from the dev machine.

In the future I'm going to need to do the reverse, bring the whole shebang down from the production server to the dev machine to be worked on and tested etc then reloaded back to the production machine as modified.

Guess you will have to use the "Transfer Database Task" browse through -

http://msdn2.microsoft.com/en-us/library/ms141204.aspx - Transfer Database Task
http://www.databasejournal.com/features/mssql/article.php/3587066 - Database Management Tasks

Additionally to transfer only specific SQL Server Objects browse through - http://msdn2.microsoft.com/en-us/library/ms142159.aspx - Transfer SQL Server Objects Task

Hope this helps.

Thanks,
Loonysan

sql

Is there a simple way to print the following

This is so stupid of a question but is there a simple way to print (to a printer) the results of SELECT * FROM SkillGoals -- To show all data(including column headings?)

Are you trying to do this from Query Analyzer? There's no waythat I know of. I usually copy and paste to Excel and print itfrom there.
I would LOVE an easy way for my copy-and-paste to also include thecolumn headings, but I haven't been able to find a way to do that.
|||yeh my boss suggested pulling it into access and that's to much work--they really should have a way to both print from QA (you are correct -- I'm trying to print from QA), and pull in the column headings. I ended up just copy & paste to Excel and Added my own column headings. Thanks though for the response.|||You might want to change the status of this back to Not Resolved. Just because I don't know doesn't mean there's not a way, or thatsomeone doesn't have a better solution :-) Personally I'd give ita day or 2 for more input.
|||

Good Idea!

|||I havent tried it but wouldnt the column headings show up if you change the status to "print results to file" ?
|||

ndinakar wrote:

I havent tried it but wouldnt the columnheadings show up if you change the status to "print results to file" ?

Sorry, I am not following. Change what status, and where?
|||Couldnt remember off the top of my head..in QA under Query -> Results to File. Doesnt that work ?
|||

ndinakar wrote:

Couldnt remember off the top of my head..in QA under Query -> Results to File. Doesnt that work ?

OK, in QA if you click into the results pane, and do a File -- Save As, thereis indeed an option to save as a CSV file or as a tab delimited file. But unforunately neither option saves the column headings.

Is there a simple way to get everything there is to get from an XmlReader?

THE TASK
Using Visual Studio .NET 2003 and SQL Server 2000, I want to create a .NET application that gets SQL Server data obtained from a SELECT query with a FOR XML clause. I have seen the solution a long time ago in the MSCD .NET book "Developing Windows-Based Applications" for 70-306 and 70-316 and I thought, this looks really simple, I don't need to try it out. Now that I need to do it I am surprised how difficult it is.

TO RUN THE EXAMPLE
The code below demonstrates the expected solution based on the book and the failure of that solution. To run this example, create a Windows Forms project, put a TextBox called textBox1 and two Buttons called button1 and button2 on the Form. Double-click each button to generate the methods that handle the click events. Replace "MySQLServer" and "MyWorkstation" with your own names. The pubs database should be present, otherwise modify the connection string and the command text as well. Compile it and click button1.

THE OUTCOME
The text in textBox1 shows four XML elements but there are eight records in the publishers database. The pub_ID values show that every second record has been omitted.

THE PROBLEM
The code should be self-explanatory, so let's look directly at the while loop and the ReadOuterXml method. That's where the problem occurs.
The framework documentation reveals a bad surprise. If the XmlReader is positioned on a node at the leaf level, ReadOuterXml behaves like Read. This means the reader advances to the next position. In combination with Read this means that the reader advances by 2 positions although the while loop has only looped once. This may or may not happen depending on whether the reader happens to be on a leaf node or node. Try to play with nested parent and child records and you will see how funny this can be.
Apparently, this means there is no easy way to reliably loop through everything.
I find this behavior of the ReadOuterXml method very disappointing especially since there seems to be no straightforward way to control this behavior. When I research the subject, I only find articles that simply say "//Do some parsing here" in the while loop. I disagree. One should not do any parsing there. One should not query the node type and then reconstruct every aspect of the XML for every theroetically possible node type and write a lot of code to that end. Instead, one should trust that SQL Server has produced correct data and go ahead and save the data to a file. Compare how well-behaved the SqlDataReader is!
I wish the XmlReader had a ReadEverythingThereIsToReadAndReturnItAsATextStream method. Is there a known and simple solution to accomplish what that method whoud do?

THE CODE
private void button1_Click(object sender, System.EventArgs e)
{
string connectionString = "data source=MySQLServer;initial catalog=pubs;integrated security=SSPI;persist security info=False;workstation id=MyWorkStation;packet size=4096";
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
string cmdText = "SELECT * FROM publishers FOR XML RAW";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(cmdText, connection);
this.textBox1.Text = String.Empty;
connection.Open();
System.Xml.XmlReader reader = cmd.ExecuteXmlReader();
while (reader.Read())
{
this.textBox1.AppendText(reader.ReadOuterXml());
}
reader.Close();
connection.Close();
}

private void button2_Click(object sender, System.EventArgs e)
{
string filename = Application.StartupPath + System.IO.Path.DirectorySeparatorChar + "publishers.xml";
System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(filename, System.Text.Encoding.UTF8);
writer.Formatting = System.Xml.Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("Publishers");
writer.WriteRaw(this.textBox1.Text);
writer.WriteEndDocument();
writer.Close();
}

If you want to write out everything the XmlReader returns then open up an XmlTextWriter and simply do xmlTextWriter.WriteNode(xmlReader, false).|||

Thank you Martin Honnen. What you wrote is useful, indeed.

HOWEVER!

I figured out a much simpler solution.

If there is no need to do any parsing on the XML that SQL Server returns, don't use an XmlReader and a while loop at all. Instead, do this:

string xmlResult = (string)cmd.ExecuteScalar();

I still think that the ReadOuterXml method (ReadInnerXml as well) should behave in a more controlled way, because as it is it's quite useless. But anyway, we have learned enough about this. Let's move on to new problems.

Is There A Simple Way To Drop All Permissions Of A Role

There has to be a simple way to remove all the permissions of a given
database role without generating a pagefull of messages. REVOKE ALL... will
do the job but outputs redundant messages telling you what the ALL really
means. Is there a more appropriate way of removing permissions (short of
DROP'ing the role outright - which would first entail dropping all the
members)
--
Michael HocksteinHi Michael,
My understanding of your issue is that:
You want to find a simple way to remove all permissions of a given database
role without generating a page full of messages. You don't want to use
REVOKE ALL because of its producing redundant messages.
If I have misunderstood, please let me know.
As far as I know, REVOKE ALL is the simplest way to remove all permissions
on a role. For the warning, I am afraid that this is an issue by design. I
recommend that you give Microsoft feedback on this issue which will be
routed to SQL Team so that this issue can be improved in the next release.
I recommend that you give Microsoft feedback via:
http://connect.microsoft.com
Your feedback will be routed to SQL team so that this issue can be resolved
in the next release.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks for your insight. No problem using REVOKE ALL, even with the messages
.
Michael Hockstein
"Charles Wang[MSFT]" wrote:

> Hi Michael,
> My understanding of your issue is that:
> You want to find a simple way to remove all permissions of a given databas
e
> role without generating a page full of messages. You don't want to use
> REVOKE ALL because of its producing redundant messages.
> If I have misunderstood, please let me know.
> As far as I know, REVOKE ALL is the simplest way to remove all permissions
> on a role. For the warning, I am afraid that this is an issue by design. I
> recommend that you give Microsoft feedback on this issue which will be
> routed to SQL Team so that this issue can be improved in the next release.
> I recommend that you give Microsoft feedback via:
> http://connect.microsoft.com
> Your feedback will be routed to SQL team so that this issue can be resolve
d
> in the next release.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Michael,
Thank you very much for your understanding.
I had thought that if you were very concerned with the warning, you could
write a program and use SMO library to enumerate the permissions on an
user, and then revoke all the permissions one by one. However I noticed
that you wanted a simple way, so I thought the warning may be not your much
concerned issue.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

Is there a simple way to achieve this using MDX?

Hi all,

I want to simply take a value that is returned in my fact table for a
measure and copy is across all cells for that measure. (Analysis services 2000)

Current result

Date Measure
1/1/06 0
2/1/06 0
3/1/06 100 -- there will only ever be 1 value for any day. Everything else empty/0
4/1/06 0

Desired result

Date Measure
1/1/06 100
2/1/06 100
3/1/06 100 -- just copy this value to all cells and allow it to
roll up to 400 when drilled up.
4/1/06 100

Regards
ImmyHere's one idea - assuming you're using AS 2000 Enterprise Edition, suppose you create both "sum" and "max" measures from the fact table measure, like SumMeas and MaxMeas. Then you could create a calculated cell for SumMeas only at the daily (leaf) level of the Date dimension, which overwrites values with something like ([Measures].[MaxMeas], [Date].[All]). Then, these values should get summed at higher levels of the Date hierarchy.|||

Hi Deeps,

I'm not using Ent Edt. Do you know if there are any other options available to me?

Or maybe I can deal with it server side instead, but i know it will slow down my cubing process if i have to come up with a server side (data source) solution.

Immy

|||Can you create a view for your fact table, which adds a field for the maximum value across all records with the same dimension values other than date? This field could be used for the "sum" measure.

Is there a set-based solution for this? <long message>

I am trying hard to avoid writing a cursor to drive a report, but I can't
see a set-based solution. Can some of you?
The table ScanLog records the time, operator, and machine at which a certain
operation was performed as entered by a barcode scanning application.
CREATE TABLE [dbo].[ScanLog] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Ord_Num] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WO_Suf] nvarchar(10) NULL,
[OpCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAction] [datetime] NULL ,
[TimeAction] [datetime] NULL ,
[Action] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OpID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MachineCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PiecesCompleted] [int] NULL ,
[DTCreated] [datetime] not NULL default(getdate())
) ON [PRIMARY]
GO
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:57AM', 'FINISH',
'JSW', 'M-06', 39, 'Jun 16 2005 6:57AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:49AM', 'FINISH',
'JSW', 'M-06', 97, 'Jun 16 2005 6:49AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:00PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 3:00PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:40PM', 'FINISH',
'TAC', 'M-07', 72, 'Jun 6 2005 3:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:41PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 3:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'FINISH',
'TAC', 'M-07', 22, 'Jun 6 2005 4:00PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 4:00PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:10PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 4:10PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:34PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 4:34PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:43PM', 'FINISH',
'TAC', 'M-07', 9, 'Jun 6 2005 4:43PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:48PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 4:48PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:17PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 5:17PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:36PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 5:36PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:36PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 6:37PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:41PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 6:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:56PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 6:56PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:18PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 7:18PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:31PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 7:31PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:47PM', 'FINISH',
'TAC', 'M-08', 39, 'Jun 6 2005 7:47PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:06PM', 'FINISH',
'TAC', 'M-07', 52, 'Jun 6 2005 8:06PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:24PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 9:24PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:21AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:22AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:28AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 8:28AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106181',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:15AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 9:15AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:50AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 9:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:41PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 9:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:04PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 10:05PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:30PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 10:30PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:01PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 11:01PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:00AM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 12:00AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:09AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 7 2005 12:09AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:12AM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 12:12AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:30AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 7 2005 12:30AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:13AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 6:13AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106341',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 3:58PM', 'START',
'TAC', 'M-07', 11, 'Jun 7 2005 3:58PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:04PM', 'FINISH',
'TAC', 'M-07', 11, 'Jun 7 2005 4:04PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:12PM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 4:12PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:42PM', 'START',
'TAC', 'M-07', 0, 'Jun 7 2005 4:42PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 9:50PM', 'FINISH',
'TAC', 'M-08', 135, 'Jun 7 2005 9:50PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 10:23PM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 10:23PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:21AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 8 2005 12:21AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:44AM', 'STOP',
'TAC', 'M-07', 168, 'Jun 8 2005 12:45AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96317 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:57PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 20 2005 5:05PM' )
GO
I then must create a report that tells for a given operator and day how much
time the operator worked and how many pieces he completed.
Operator Date Hours Pieces
TAC 6/6/05 8.5 400
TAC 6/7/05 8 350
JSW 6/6/05 8.5 350
JSW 6/7/05 8 400
It gets difficult when you have to deduct break times and realize that an
operator may be running more than one machine at a time. The ScanLog_Pairs
view helps display this:
Create View dbo.ScanLog_TimeResolved
AS
Select ID, Ord_Num, WO_Suf, OpCode,
DateAdd(ms, DatePart(ms, TimeAction),
DateAdd(ss, DatePart(ss, TimeAction),
DateAdd(n,datepart(n, TimeAction),
DateAdd(hh, datepart(hh,TimeAction), DateAction)))) as dtAction ,
Action, OpID, MachineCode, PiecesCompleted, DTCreated
From ScanLog
GO
Create View dbo.ScanLog_Starts
As
Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
Action, OpID, MachineCode, PiecesCompleted,
DTCreated
From ScanLog_TimeResolved
Where Action = 'START'
GO
Create View dbo.ScanLog_StopsFinishes
As
Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
Action, OpID, MachineCode, PiecesCompleted,
DTCreated
From ScanLog_TimeResolved
Where Action in ('STOP', 'FINISH')
go
Create View dbo.ScanLog_Pairs
As
Select IsNull(starts.Ord_Num, stops.Ord_Num) as Ord_Num ,
IsNull(starts.WO_Suf, stops.WO_Suf) as WO_Suf ,
IsNull(starts.OpCode, stops.OpCode) as OpCode ,
IsNull(starts.MachineCode, stops.MachineCode) as MachineCode ,
starts.dtAction as StartDT,
stops.dtAction as StopDT,
starts.Action as StartAction, --probably irrelevant
stops.Action as StopAction,
Starts.OpID as StartOperator,
stops.OpID as StopOperator,
SecondsElapsed = Case when starts.dtAction is NULL then Null
when stops.dtAction is NULL then NULL
else DateDiff(ss, starts.dtAction, stops.dtAction)
END,
stops.PiecesCompleted
from
ScanLog_Starts starts full join ScanLog_StopsFinishes stops on
starts.dtAction < stops.dtAction And starts.Ord_Num = stops.Ord_Num and
starts.WO_Suf = stops.WO_Suf and starts.OpCode = stops.OpCode
Left Join Scanlog_StopsFinishes early on
early.dtAction > starts.dtaction and early.dtAction < stops.dtAction And
early.Ord_Num = stops.Ord_Num and early.WO_Suf = stops.WO_Suf and
early.OpCode = stops.OpCode
Where early.ID is null
go
Select * From ScanLog_Pairs Order By StartOperator, StartDT
Operator TAC starts order 88375 on M-07 at 6:41 PM. Before it finishes at
8:06 PM, he has started and finished 2 orders on M-08. The number of pieces
he has completed should be summed, but the amount of time elapsed should
not.
So, is there a set-based solution to this time-gap / time-overlap problem,
or do I need to write a cursor?
Thanks in advance.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyWhy did you split date and time apart? Why is there no key and all the
columns are NULL-able to prevent having a relational key? Why are
there so many varying national character columns when most codes are
fixed length and in simple Latin characters, not Chinese? It is also
unusual that they are all multiples of five; you did research the
proper lengths for all your data elements instead of just pulling this
out of the air, didn't you?
Why do you have a creation date in a base table - auditing is not
done in the table itself. Why do you have a temporal model without
durations? Why did you use AM/PM instead of ISO-8601 for your temporal
data? Why are there no DRI or CHECK() clauses?
You have made a common Newbie design error. You the data
collection forms with the data model. Your table is basically a
clipboard with log sheets and numbered lines (hence IDENTITY). If you
want a set-based DML, you need to have a proper, relational DDL. Here
is my guess, sans any specs:
CREATE TABLE ScanLog - weird name, standard in your industry?
(ord_nbr CHAR(25) NOT NULL
REFERENCES Orders(ord_nbr),
wo_suf CHAR(10) NOT NULL,
op_code CHAR(10) NOT NULL,
action_start_time DATETIME NOT NULL,
action_end_time DATETIME, -- null is currently active
CHECK(action_start_time < action_end_time),
action_code CHAR(10) NOT NULL,
op_id CHAR(10) NOT NULL
REFERENCES Operators (op_id),
machine_code CHAR(20) NOT NULL
REFERENCES Machines(machine_code),
pieces_completed INTEGER NOT NULL
CHECK (pieces_completed >= 0));
You can now write a simple query to find the MIN(action_start_time) and
MAX(action_end_time)to get the total elapsed time, something like
this:.
SELECT op_id, SUM(pieces_completed) AS pieces_total,
DATEDIFF(mm, MIN(action_start_time), MAX(action_end_time)) AS
elapsed_time
FROM ScanLog
WHERE op_id = 'TAC'
GROUP BY
I did oneof these applications for a company that makes a timeclock
device for the construction trades. Watch the data scrubbing when you
build the durations.|||> (ord_nbr CHAR(25) NOT NULL
> REFERENCES Orders(ord_nbr),
Joe, can you tell us:
(a) how is ord_nbr generated in your ideal world?
(b) what is wrong with using order_number?
A|||There is a set-based solution, but it is extremely difficult and convoluted
to write. In this case a cursor will be a lot simpler to write, will be a
lot more understandable--that is, easier to maintain, and will perform a lot
better, because you can do all of the work in a single pass through the
table, instead of using four or five self-joins.
You should probably consider creating a separate table to store the results
for reporting purposes. That way the calculation only has to happen once.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:Onku8nOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> I am trying hard to avoid writing a cursor to drive a report, but I can't
> see a set-based solution. Can some of you?
> The table ScanLog records the time, operator, and machine at which a
certain
> operation was performed as entered by a barcode scanning application.
>
> CREATE TABLE [dbo].[ScanLog] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Ord_Num] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WO_Suf] nvarchar(10) NULL,
> [OpCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateAction] [datetime] NULL ,
> [TimeAction] [datetime] NULL ,
> [Action] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OpID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MachineCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PiecesCompleted] [int] NULL ,
> [DTCreated] [datetime] not NULL default(getdate())
> ) ON [PRIMARY]
> GO
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:57AM', 'FINISH',
> 'JSW', 'M-06', 39, 'Jun 16 2005 6:57AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:49AM', 'FINISH',
> 'JSW', 'M-06', 97, 'Jun 16 2005 6:49AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:00PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 3:00PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:40PM', 'FINISH',
> 'TAC', 'M-07', 72, 'Jun 6 2005 3:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:41PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 3:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'FINISH',
> 'TAC', 'M-07', 22, 'Jun 6 2005 4:00PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:00PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:10PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:10PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:34PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:34PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:43PM', 'FINISH',
> 'TAC', 'M-07', 9, 'Jun 6 2005 4:43PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:48PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:48PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:17PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 5:17PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:36PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 5:36PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:36PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 6:37PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:41PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 6:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:56PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 6:56PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:18PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 7:18PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:31PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 7:31PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:47PM', 'FINISH',
> 'TAC', 'M-08', 39, 'Jun 6 2005 7:47PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:06PM', 'FINISH',
> 'TAC', 'M-07', 52, 'Jun 6 2005 8:06PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:24PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 9:24PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:21AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:22AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:28AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 8:28AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106181',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:15AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 9:15AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:50AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 9:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:41PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 9:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:04PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 10:05PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:30PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 10:30PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:01PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 11:01PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:00AM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:00AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:09AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:09AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:12AM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:12AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:30AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:30AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:13AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 6:13AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106341',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 3:58PM', 'START',
> 'TAC', 'M-07', 11, 'Jun 7 2005 3:58PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:04PM', 'FINISH',
> 'TAC', 'M-07', 11, 'Jun 7 2005 4:04PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:12PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 4:12PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:42PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 7 2005 4:42PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 9:50PM', 'FINISH',
> 'TAC', 'M-08', 135, 'Jun 7 2005 9:50PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 10:23PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 10:23PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:21AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 8 2005 12:21AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:44AM', 'STOP',
> 'TAC', 'M-07', 168, 'Jun 8 2005 12:45AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96317 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:57PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 20 2005 5:05PM' )
> GO
>
> I then must create a report that tells for a given operator and day how
much
> time the operator worked and how many pieces he completed.
> Operator Date Hours Pieces
> TAC 6/6/05 8.5 400
> TAC 6/7/05 8 350
> JSW 6/6/05 8.5 350
> JSW 6/7/05 8 400
> It gets difficult when you have to deduct break times and realize that an
> operator may be running more than one machine at a time. The ScanLog_Pairs
> view helps display this:
> Create View dbo.ScanLog_TimeResolved
> AS
> Select ID, Ord_Num, WO_Suf, OpCode,
> DateAdd(ms, DatePart(ms, TimeAction),
> DateAdd(ss, DatePart(ss, TimeAction),
> DateAdd(n,datepart(n, TimeAction),
> DateAdd(hh, datepart(hh,TimeAction), DateAction)))) as dtAction ,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated
> From ScanLog
> GO
> Create View dbo.ScanLog_Starts
> As
> Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
> Action, OpID, MachineCode, PiecesCompleted,
> DTCreated
> From ScanLog_TimeResolved
> Where Action = 'START'
> GO
> Create View dbo.ScanLog_StopsFinishes
> As
> Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
> Action, OpID, MachineCode, PiecesCompleted,
> DTCreated
> From ScanLog_TimeResolved
> Where Action in ('STOP', 'FINISH')
> go
> Create View dbo.ScanLog_Pairs
> As
> Select IsNull(starts.Ord_Num, stops.Ord_Num) as Ord_Num ,
> IsNull(starts.WO_Suf, stops.WO_Suf) as WO_Suf ,
> IsNull(starts.OpCode, stops.OpCode) as OpCode ,
> IsNull(starts.MachineCode, stops.MachineCode) as MachineCode ,
> starts.dtAction as StartDT,
> stops.dtAction as StopDT,
> starts.Action as StartAction, --probably irrelevant
> stops.Action as StopAction,
> Starts.OpID as StartOperator,
> stops.OpID as StopOperator,
> SecondsElapsed = Case when starts.dtAction is NULL then Null
> when stops.dtAction is NULL then NULL
> else DateDiff(ss, starts.dtAction, stops.dtAction)
> END,
> stops.PiecesCompleted
> from
> ScanLog_Starts starts full join ScanLog_StopsFinishes stops on
> starts.dtAction < stops.dtAction And starts.Ord_Num = stops.Ord_Num and
> starts.WO_Suf = stops.WO_Suf and starts.OpCode = stops.OpCode
> Left Join Scanlog_StopsFinishes early on
> early.dtAction > starts.dtaction and early.dtAction < stops.dtAction And
> early.Ord_Num = stops.Ord_Num and early.WO_Suf = stops.WO_Suf and
> early.OpCode = stops.OpCode
> Where early.ID is null
> go
>
> Select * From ScanLog_Pairs Order By StartOperator, StartDT
> Operator TAC starts order 88375 on M-07 at 6:41 PM. Before it finishes at
> 8:06 PM, he has started and finished 2 orders on M-08. The number of
pieces
> he has completed should be summed, but the amount of time elapsed should
> not.
> So, is there a set-based solution to this time-gap / time-overlap problem,
> or do I need to write a cursor?
> Thanks in advance.
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>|||Again your "solution" doesn't answer the question posted. It appears that
the poster was assigned to write a report given raw data from a barcode
scanner. Your "solution" is the obvious table design for a reporting table,
but that doesn't give the poster any direction on how to load it.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123599943.935337.113690@.g43g2000cwa.googlegroups.com...
> Why did you split date and time apart? Why is there no key and all the
> columns are NULL-able to prevent having a relational key? Why are
> there so many varying national character columns when most codes are
> fixed length and in simple Latin characters, not Chinese? It is also
> unusual that they are all multiples of five; you did research the
> proper lengths for all your data elements instead of just pulling this
> out of the air, didn't you?
> Why do you have a creation date in a base table - auditing is not
> done in the table itself. Why do you have a temporal model without
> durations? Why did you use AM/PM instead of ISO-8601 for your temporal
> data? Why are there no DRI or CHECK() clauses?
> You have made a common Newbie design error. You the data
> collection forms with the data model. Your table is basically a
> clipboard with log sheets and numbered lines (hence IDENTITY). If you
> want a set-based DML, you need to have a proper, relational DDL. Here
> is my guess, sans any specs:
> CREATE TABLE ScanLog - weird name, standard in your industry?
> (ord_nbr CHAR(25) NOT NULL
> REFERENCES Orders(ord_nbr),
> wo_suf CHAR(10) NOT NULL,
> op_code CHAR(10) NOT NULL,
> action_start_time DATETIME NOT NULL,
> action_end_time DATETIME, -- null is currently active
> CHECK(action_start_time < action_end_time),
> action_code CHAR(10) NOT NULL,
> op_id CHAR(10) NOT NULL
> REFERENCES Operators (op_id),
> machine_code CHAR(20) NOT NULL
> REFERENCES Machines(machine_code),
> pieces_completed INTEGER NOT NULL
> CHECK (pieces_completed >= 0));
>
of time elapsed should not. So, is there a set-based solution to this
time-gap / time-overlap problem or do I need to write a cursor? <<
> You can now write a simple query to find the MIN(action_start_time) and
> MAX(action_end_time)to get the total elapsed time, something like
> this:.
> SELECT op_id, SUM(pieces_completed) AS pieces_total,
> DATEDIFF(mm, MIN(action_start_time), MAX(action_end_time)) AS
> elapsed_time
> FROM ScanLog
> WHERE op_id = 'TAC'
> GROUP BY
> I did oneof these applications for a company that makes a timeclock
> device for the construction trades. Watch the data scrubbing when you
> build the durations.
>|||Thanks for the reply. As is often the case in medium-large projects, a good
deal of table structure is inherited even if it is not defensible. I may,
however, be able to convert it into something very close to what you
recommend.
I will defend the use of NVARCHAR. Our application imports from our
customers' ERP systems. Since they run a wide range of ERP systems, the
order numbers they use vary significantly in width. Thus CHAR would not
work well for us since prepended spaces make a real mess of things.
Allowing unicode means we are not restricted to a Latin character set when
we want to sell elsewhere in the world.
Your solution handles simultaneous work on multiple machines, which is where
my table structure gives me major problems. How would I handle breaks with
your structure?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123599943.935337.113690@.g43g2000cwa.googlegroups.com...
> Why did you split date and time apart? Why is there no key and all the
> columns are NULL-able to prevent having a relational key? Why are
> there so many varying national character columns when most codes are
> fixed length and in simple Latin characters, not Chinese? It is also
> unusual that they are all multiples of five; you did research the
> proper lengths for all your data elements instead of just pulling this
> out of the air, didn't you?
> Why do you have a creation date in a base table - auditing is not
> done in the table itself. Why do you have a temporal model without
> durations? Why did you use AM/PM instead of ISO-8601 for your temporal
> data? Why are there no DRI or CHECK() clauses?
> You have made a common Newbie design error. You the data
> collection forms with the data model. Your table is basically a
> clipboard with log sheets and numbered lines (hence IDENTITY). If you
> want a set-based DML, you need to have a proper, relational DDL. Here
> is my guess, sans any specs:
> CREATE TABLE ScanLog - weird name, standard in your industry?
> (ord_nbr CHAR(25) NOT NULL
> REFERENCES Orders(ord_nbr),
> wo_suf CHAR(10) NOT NULL,
> op_code CHAR(10) NOT NULL,
> action_start_time DATETIME NOT NULL,
> action_end_time DATETIME, -- null is currently active
> CHECK(action_start_time < action_end_time),
> action_code CHAR(10) NOT NULL,
> op_id CHAR(10) NOT NULL
> REFERENCES Operators (op_id),
> machine_code CHAR(20) NOT NULL
> REFERENCES Machines(machine_code),
> pieces_completed INTEGER NOT NULL
> CHECK (pieces_completed >= 0));
>
of time elapsed should not. So, is there a set-based solution to this
time-gap / time-overlap problem or do I need to write a cursor? <<
> You can now write a simple query to find the MIN(action_start_time) and
> MAX(action_end_time)to get the total elapsed time, something like
> this:.
> SELECT op_id, SUM(pieces_completed) AS pieces_total,
> DATEDIFF(mm, MIN(action_start_time), MAX(action_end_time)) AS
> elapsed_time
> FROM ScanLog
> WHERE op_id = 'TAC'
> GROUP BY
> I did oneof these applications for a company that makes a timeclock
> device for the construction trades. Watch the data scrubbing when you
> build the durations.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123599943.935337.113690@.g43g2000cwa.googlegroups.com...
> Why did you split date and time apart? Why is there no key and all the
> columns are NULL-able to prevent having a relational key? Why are
> there so many varying national character columns when most codes are
> fixed length and in simple Latin characters, not Chinese? It is also
> unusual that they are all multiples of five; you did research the
> proper lengths for all your data elements instead of just pulling this
> out of the air, didn't you?
> Why do you have a creation date in a base table - auditing is not
> done in the table itself. Why do you have a temporal model without
> durations? Why did you use AM/PM instead of ISO-8601 for your temporal
> data? Why are there no DRI or CHECK() clauses?
While I (mostly) agree with the above, I'm having
difficulty with the statement:

> auditing is not done in the table itself.
Why would it not be? Seems to me the easiest,
most convenient, and best place to put it. I've
always done it this way. Do you have suggestions
for something better?|||there are several solutions, neither is easy, such as:
create table scanlog(order_number char(2), event_dt datetime,
event_name char(5))
insert into scanlog values('AA','11/11/2005 8:00AM', 'START')
insert into scanlog values('AB','11/11/2005 9:00AM', 'START')
insert into scanlog values('AA','11/11/2005 10:00AM', 'STOP')
insert into scanlog values('AA','11/11/2005 11:00AM', 'START')
insert into scanlog values('AB','11/11/2005 12:00PM', 'STOP')
insert into scanlog values('AA','11/11/2005 1:00PM', 'STOP')
--lunch 1PM-2PM
insert into scanlog values('AA','11/11/2005 2:00PM', 'START')
insert into scanlog values('AB','11/11/2005 2:00PM', 'START')
insert into scanlog values('AA','11/11/2005 3:00PM', 'STOP')
insert into scanlog values('AA','11/11/2005 4:00AM', 'START')
insert into scanlog values('AB','11/11/2005 5:00PM', 'STOP')
insert into scanlog values('AA','11/11/2005 5:00PM', 'STOP')
go
create view work_interval
as
select s.order_number, s.event_dt start_dt, e.event_dt end_dt
from scanlog s join scanlog e
on s.order_number=e.order_number and s.event_dt<e.event_dt
and s.event_name='START' and e.event_name='STOP'
where not exists(select 1 from scanlog s1
where s1.order_number=e.order_number
and s.event_dt<s1.event_dt and s1.event_dt < e.event_dt)
select * from work_interval
order_number start_dt
end_dt
-- ---
---
AA 2005-11-11 08:00:00.000
2005-11-11 10:00:00.000
AB 2005-11-11 09:00:00.000
2005-11-11 12:00:00.000
AA 2005-11-11 11:00:00.000
2005-11-11 13:00:00.000
AA 2005-11-11 14:00:00.000
2005-11-11 15:00:00.000
AB 2005-11-11 14:00:00.000
2005-11-11 17:00:00.000
AA 2005-11-11 16:00:00.000
2005-11-11 17:00:00.000
(6 row(s) affected)
select identity(int,1,1) as interval_number, start_dt
into #work_start
from (select distinct start_dt from work_interval w where not exists(
select 1 from work_interval w1
where w1.start_dt < w.start_dt and w.start_dt < w1.end_dt
)) t
select * from #work_start
interval_number start_dt
-- ---
1 2005-11-11 08:00:00.000
2 2005-11-11 14:00:00.000
(2 row(s) affected)
select identity(int,1,1) as interval_number, end_dt
into #work_end
from (select distinct end_dt from work_interval w where not exists(
select 1 from work_interval w1
where w1.start_dt < w.end_dt and w.end_dt < w1.end_dt
)) t
select * from #work_end
interval_number end_dt
-- ---
1 2005-11-11 13:00:00.000
2 2005-11-11 17:00:00.000
(2 row(s) affected)
select start_dt, end_dt
from #work_start join #work_end on #work_start.interval_number =
#work_end.interval_number
start_dt end_dt
---
---
2005-11-11 08:00:00.000 2005-11-11
13:00:00.000
2005-11-11 14:00:00.000 2005-11-11
17:00:00.000
(2 row(s) affected)
drop table scanlog
Easier to use row_number()|||>> (a) how is ord_nbr generated in your ideal world? <<
No, no! People who use IDENTITY live in a magical world where
identifiers fall from the hardware and they do not have to work because
they have a magical answer to all the problems.
In my world, I have to look for industry and legal standards (ex.
government contracts define the order numbers to be used), I have to
talk to the "Trolls in Accounting" (a la Dilbert) to find company
policies, etc. This is just the research! Then I have to set up
validation and verification rules. I have to set up audit trails and
account for every order number issues -- just like they were a valuable
company resource (kinda like checks)!
If I have to design an identifier, then i will consider various check
digit schemes (Dihedral five is the best) and additive congruential
generators if I need to guarantee 99.999% data accuracy. If I can live
with 99.99%, I might use weaker tools.
I just find long names to be harder to post in a newsgroup. I also
like "_nbr" as a postfix rather than spelling it out or using "_no"
(too much like "yes/no" in English) or "_num" (not as universal as
nbr)|||>> Seems to me the easiest, most convenient, and best place to put it. I've
always done it this way. Do you have suggestions for something better? <<
You have never had an accounting class, have you? This is the same
reason that two different clerks look at everything. This is the same
reason that you do not put the log files and back up on the same hard
drive as the database. You want to physically keep some things away
from the people that can be held liable for problems
Get a third party tool that is designed for auditing. They will not
degrade performance and they will give you nice reports. Get the right
tool for the job.sql