Minute table?

I got no responses to the 'Complex query' thread, which probably
contained too much information. I'll try simplifying it:
I have 2 tables
create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
smalldatetime, MI_RequiredStaff tinyint)
this table contains the required staff between certain time.
MI_RequiredStaff is the column that contains the number of employees
required at that time.
create table PlannedWork (PW_ID int, PW_Date smalldatetime,
PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
this table contains times when an employee will work.
Now I'm looking for a query which can give me the Minima which aren't
fullfilled for a certain day. Of course if an employee is used to fill
a certain minima he/she can't be used for another one.
I have no clue on how to begin with this. But I was thinking maybe
it's possible to use something like a minute-table (like you have a
table with all the days). I don't know if that has ever been used and
if it's appropriate for this.
Thanks in advance,
Stijn Verrept.Stijn,
There is no 'easy' fix for this. I feel you need to identify the smallest
period of time to use as slots, unless you have already defined this in the
XX_BeginTime and XX_EndTime inasmuch that such times will always match for
any time of the day (e.g. a standard 3 shift system commencing at 0700 and
running a stright 8 hours each). Any such system will need to be the same fo
r
both the employee and required time slots.
Once identified, you can create a temp table of the required slots for a
given period of time (be it a single shift, day, w, etc) and then update
those records with the counts of staff matching that shift.
Example: I use a straight 3 shift system of 0700-1500, 1500-2300 and
2300-0700.
Day 1 requires 3,5,2 (respectively).
The Temp table will have Date, Start, End, Required, Planned columns.
The Date, Start, End, Required columns are populated immediately from the
Minima table, and then each row is updated with the count of Employtees
matching those times/dates from the PlannedWork table.
The final act is to calculate against the Required and Planned columns to
determine overages or shortfalls in the numbers.
The whole code would be contained in a Stored Procedure with the relevent
Params to determine any period of time.
This is a *very* basic and simplified version of what you may find to the a
solution, but you could find it beneficial to break the problem down into
steps as above to assist you.
Hope this assists,
Tony
"Stijn Verrept" wrote:

> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
>|||Stijn Verrept wrote:
> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
Hi Stijn,
No keys at all in your tables? Is every column really nullable or do
you expect us to guess? Surely you could do better...
Assuming you add some sensible keys and constraints and assuming you
don't allow PlannedWork for any employee to overlap (easily prevented
with a trigger), try something like the following query.
I don't know why you would put date and time in separate columns so
I've ignored PW_Date altogether.
SELECT mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
SUM(DATEDIFF(MINUTE,work_start,work_end)
) AS work_time
FROM
(SELECT M.mi_id, M.mi_begintime, M.mi_endtime, M.mi_requiredstaff,
mi_requiredstaff*DATEDIFF(MINUTE, mi_begintime, mi_endtime)
AS required_time,
CASE WHEN M.mi_begintime > P.pw_begintime
THEN M.mi_begintime ELSE P.pw_begintime END AS work_start,
CASE WHEN M.mi_endtime < P.pw_endtime
THEN M.mi_endtime ELSE P.pw_endtime END AS work_end
FROM Minima AS M
LEFT JOIN PlannedWork AS P
ON M.mi_begintime < P.pw_endtime
AND M.mi_endtime > P.pw_begintime) AS T
GROUP BY mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
required_time
HAVING SUM(DATEDIFF(MINUTE,work_start,work_end)
)< required_time ;
However, this won't prevent an employee being allocated to more than
one Minima. To do that I think you will have to pre-allocate employees
to each Minima on a best-fit basis. I don't think you'll be able to
find the best-fits using only declarative SQL because I'm pretty
certain this is an NP-complexity problem - I think there's even a
mathematical name for it.
I discussed a logically similar best-fit problem in the following
thread. My solution could help you get an arbitrary match between
Minima and PlannedWork but an arbitrary match I'd suggest isn't what
you are looking for. I guess you'll need to build a table of possible
assignments and then pull out the minimum cost ones. If you post some
sample data someone may be able to help further.
http://groups.google.co.uk/group/co...2c682dab331565c
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tony Scott wrote:

> There is no 'easy' fix for this. I feel you need to identify the
> smallest period of time to use as slots, unless you have already
> defined this in the XX_BeginTime and XX_EndTime inasmuch that such
> times will always match for any time of the day (e.g. a standard 3
> shift system commencing at 0700 and running a stright 8 hours each).
> Any such system will need to be the same for both the employee and
> required time slots.
This could indeed simplify things (if the employee and required slots
are the same. I'm going to ask if this will be the case.

> This is a very basic and simplified version of what you may find to
> the a solution, but you could find it beneficial to break the problem
> down into steps as above to assist you.
True, thanks a lot!
Stijn Verrept.|||David Portas wrote:

> No keys at all in your tables? Is every column really nullable or do
> you expect us to guess? Surely you could do better...
Indeed I can, and I also did (see the 'complex query' thread) however I
got no replies there and now I get 2. Sometimes it's better to provide
less info ;). The tables are of course completely fictional, just to
give the general idea of the problem.

> However, this won't prevent an employee being allocated to more than
> one Minima.

> To do that I think you will have to pre-allocate employees
> to each Minima on a best-fit basis. I don't think you'll be able to
> find the best-fits using only declarative SQL because I'm pretty
> certain this is an NP-complexity problem - I think there's even a
> mathematical name for it.
Thank you very much, I'm going to look up that NP-complexity problem
(which sounds complex ;). And look into the thread you provided! The
possible assignments indeed seems a good idea, the benefit is I would
only need to do this for the Minima and the ServiceGratings which will
speed up the final query.
Thanks again,
Stijn Verrept.|||No problem at all.
Just as a sideline to this, if you should find that the 'slots' do not
match, then you may be forced to use 'compromise' or 'lowest denominator'
slots, those being the smallest unit of time that can fit within both sets o
f
data. Example would be that if the Employees and shifts always start on the
hour and work in hours, then the unit would be hours, and you would then nee
d
to use a single row per hour. If the employees or shifts could start on the
half-hour, then that would be the unit of measure.
I would be interested in your findings and final solution, as this is the
type of problem I deal with day-in and day-out
Tony
"Stijn Verrept" wrote:

> Tony Scott wrote:
>
> This could indeed simplify things (if the employee and required slots
> are the same. I'm going to ask if this will be the case.
>
> True, thanks a lot!
> --
> Stijn Verrept.
>

Min except for zero

We have a complex query with many aggregate functions.
For one column where we return MIN, we would like to return the minimum
non-zero value.
I don't think using HAVING is going to work, because we don't want to
exclude any rows from the result set.
We're thinking of doing something like MIN(case...) but haven't found
something
that works.
An example:
TAB1
NUM1 NUM2
3 0
5 1
7 2
We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.Try
MIN(CASE WHEN NUM2 <> 0 THEN NUM2 END)
or
MIN(NULLIF(NUM2,0))
Steve Kass
Drew University
jhcorey@.yahoo.com wrote:

>We have a complex query with many aggregate functions.
>For one column where we return MIN, we would like to return the minimum
>non-zero value.
>I don't think using HAVING is going to work, because we don't want to
>exclude any rows from the result set.
>We're thinking of doing something like MIN(case...) but haven't found
>something
>that works.
>An example:
>TAB1
>NUM1 NUM2
>3 0
>5 1
>7 2
>
>We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>
>|||One way is to use CASE to convert the 0s to NULL:
SELECT MAX(a), MIN(CASE b WHEN 0 THEN NULL ELSE b END) AS b
FROM
(
SELECT 3 AS a, 0 AS b
UNION
SELECT 5, 1
UNION
SELECT 7, 2
) AS i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhcorey@.yahoo.com> wrote in message news:1108483393.537431.133510@.c13g2000cwb.googlegroups
.com...
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Correction,
select max(num1), min(case when num2 > 0 then num2 else null end) from tab1
go
AMB
"Alejandro Mesa" wrote:
> You are in the correct path. Try:
> select max(num1), min(case when num2 > 2 then num2 else null end) from tab
1
> go
>
> AMB
> "jhcorey@.yahoo.com" wrote:
>|||You are in the correct path. Try:
select max(num1), min(case when num2 > 2 then num2 else null end) from tab1
go
AMB
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Hi
WHERE (Num1 > 0 OR Num2 > 0)
Regards
Mike
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||MIN(NULLIF(num2,0))
David Portas
SQL Server MVP
--|||Whew, that was quick!
Thanks to all.
As a note, I was trying this in the Northwind database and
having some difficulty (all values are decimals between 0 and 1):
select max(discount),min(discount),min(case when discount = 0 then null
else discount end) from [order details]
Jim

mildly complex query. need senior dba advise.

Hi there,
I have this stored procedure I am trying to optimize:
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
CREATE TABLE #ResultsOutput (
rowID int not null identity(1,1),
Rank int,
MemberID int,
MemberCode varchar(50),
State varchar(5),
MemberName varchar(510),
StateID int,
TotalScore int,
TotalEvents int,
PRIMARY KEY (TotalScore, MemberID, rowID),
UNIQUE(TotalScore, MemberID, rowID)
)
CREATE TABLE #TotalScoreCount (
rowID int not null identity(1,1),
TotalScore int,
TotalScoreCount int
PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
UNIQUE (TotalScoreCount, TotalScore, rowID)
)
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' + Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID = Results.MemberID
INNER JOIN Event ON Results.EventID = Event.EventID
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
INNER JOIN State ON Region.StateID = State.StateID
WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
@.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID
ORDER BY TotalScore DESC, Results.MemberID ASC
INSERT INTO #TotalScoreCount
SELECT TotalScore,
COUNT(TotalScore) AS TotalScoreCount
FROM #ResultsOutput
GROUP BY TotalScore
ORDER BY TotalScore DESC
UPDATE #ResultsOutput
SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
#TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
FROM #ResultsOutput RO
SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
DROP table #ResultsOutput
DROP table #TotalScoreCount
END
I did a SQL Execution plan and saw no table scans on my selects/updates
(Only on my inserts ofcourse)
The query still runs poorly it takes up to 7 seconds to run!
Members has 44621 rows
Results has 121158
Venue has 336
Event has 3298
Region has 58
I am quite new and poor at SQL so any help would be appreciated.
Thanks!to be perfectly honest... its not 'that' bad already.
You could try using table variable (sql2k +) instead of temp table and also
remove the order by on the insert as you are ordering the select after
anyway.
<robert@.relate.com.au> wrote in message
news:1147350229.650320.155550@.g10g2000cwb.googlegroups.com...
> Hi there,
> I have this stored procedure I am trying to optimize:
>
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
>
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = Results.MemberID
> INNER JOIN Event ON Results.EventID = Event.EventID
> INNER JOIN Venue ON Event.VenueID = Venue.VenueID
> INNER JOIN Region ON Venue.RegionID = Region.RegionID
> INNER JOIN State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
>
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
>
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
>
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!
>|||Well, 7 seconds is hardly poor.
Anyway, make sure you have the following indexes:
- on Members(MemberID)
- on Results(EventID, MemberID, Valid)
- on Results(MemberID, EventID, Valid)
- on Event(EventID, VenueID, EventType)
- on Event(VenueID, EventID, EventType)
- on Venue(VenueID, RegionID)
- on Venue(RegionID, VenueID)
- on Region(RegionID, StateID)
- on Region(StateID, RegionID)
- on State(StateID)
and then try the SP below. Of course, you can remove the indexes that
you created above if they are not used in the query below.
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ResultsOutput
(MemberID int
,MemberCode varchar(50)
,State varchar(5)
,MemberName varchar(510)
,StateID int
,TotalScore int
,TotalEvents int
)
INSERT INTO #ResultsOutput (MemberID, MemberCode, State, MemberName,
StateID, TotalScore, TotalEvents)
SELECT Results.MemberID
, MAX(Members.MemberCode)
, MAX(Members.State)
, MAX(Members.FirstName + ' ' + Members.Surname)
, MAX(State.StateID)
, SUM(Results.Score)
, COUNT(Results.ResultsID)
FROM Members
INNER JOIN Results ON Results.MemberID = Members.MemberID
INNER JOIN Event ON Event.EventID = Results.EventID
INNER JOIN Venue ON Venue.VenueID = Event.VenueID
INNER JOIN Region ON Region.RegionID = Venue.RegionID
INNER JOIN State ON State.StateID = Region.StateID
WHERE Results.Valid = 1
AND Event.EventType = 0
AND State.StateID = @.StateID
GROUP BY Results.MemberID
Declare @.SQL varchar(1000)
Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
as varchar(12))+' ON #ResultsOutput(TotalScore)'
EXEC (@.SQL)
SELECT
ISNULL((
SELECT COUNT(*)
FROM #ResultsOutput R2
WHERE R2.TotalScore > R1.TotalScore
),0)+1 AS Rank
,MemberID
,MemberCode
,State
,MemberName
,StateID
,TotalScore
,TotalEvents
FROM #ResultsOutput R1
ORDER BY TotalScore DESC, MemberID ASC
DROP TABLE #ResultsOutput
END
Hope this helps,
Gert-Jan
"robert@.relate.com.au" wrote:
> Hi there,
> I have this stored procedure I am trying to optimize:
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = R
esults.MemberID
> INNER JOIN Event ON Results.EventI
D = Event.EventID
> INNER JOIN Venue ON Event.
VenueID = Venue.VenueID
> INNER JOIN Region
ON Venue.RegionID = Region.RegionID
> INNER JOIN
State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0
AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FRO
M
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID AS
C
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!|||Try moving the filters from the where clause because they get applied
after building the set of rows from the JOIN statements. Also, the
JOIN to States doesn't appear to be needed because the StateID can be
filltered directly from Results.
This may be faster:
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' +
Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID =
Results.MemberID AND Results.Valid = 1
INNER JOIN Event ON Results.EventID = Event.EventID AND
Event.EventType = 0
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
AND Region.StateID = @.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID|||On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:

> Declare @.SQL varchar(1000)
> Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
>as varchar(12))+' ON #ResultsOutput(TotalScore)'
> EXEC (@.SQL)
Hi Gert-Jan,
Why are you addind the spid to the index name here? I have always been
under the assumption that you only have to prefix indexes on temp tables
with the # symbol, just like you do with temp tables.
In fact, I just tested this. I executed the code below from two
different connections in SSMS and didn't get an error:
create table #test (a int)
go
create nonclustered index #ind on #test(a)
go
Hugo Kornelis, SQL Server MVP|||Thanks for the tip. It was about time that I learnt something new :-)
Gert-Jan
Hugo Kornelis wrote:
> On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:
>
> Hi Gert-Jan,
> Why are you addind the spid to the index name here? I have always been
> under the assumption that you only have to prefix indexes on temp tables
> with the # symbol, just like you do with temp tables.
> In fact, I just tested this. I executed the code below from two
> different connections in SSMS and didn't get an error:
> create table #test (a int)
> go
> create nonclustered index #ind on #test(a)
> go
> --
> Hugo Kornelis, SQL Server MVP|||Thanks for everyone's help everyone. Immy, i originally was using table
variables but was gettign table scans because of lack of indexing.
I guess i'm going to have to setup a scheduled task to run a stored
proc that will run every half an hour to do that 1 query and select it
all into a table with every field in one row and already sorted, that
way when my website application goes to run it only has to do a simple
select.
Thanks again all for confirming nothings majorly wrong.

Migration of SQL 7.0 package to SQL 2005

Hi,
We are migrating from SQL server 7.0 to SQL server 2005.
In 7.0 I have made a DTS package that does a complex task on pre-scheduled
times, started by a SQL job.
This is a brief description of the complex task:
-Copy files from several computers in the network to a local "working
directory"
-Verify the contents of the files (pre-formatted plain text files)
-Move files with size or content errors to a different directory
-Load the files that have no errors into a SQL server table (tblImport)
-Mark records in tblImport that already exist in tblHistory
-Gather user information about the files that have been succesfully loaded
into tblImport
-Create several reports (plain text files) based on the information in the
files
-Print the reports to different printers
-Add the non-marked records in tblImport to tblHistory
-Delete the non-marked records in tblImport
-Send e-mails to several recipients with statistical information about the
task
This package is made with a number of activex scripts and SQL tasks.
Because the server this package is running on now is outdated, I have the
challenge to make this task work in SQL server 2005.
The database it is using is already transferred to SQL 2005.
I have seen that creating packages in 2005 is totally different than
creating packages in 7.0.
In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can only
load a .dtsx file.
To me it is ok if I have to build the package in 2005 from scratch or build
it in a totally different way, just as long as it is not taking me too much
time. A different way could be that I copy and paste the scripts and SQL
statements into several steps of a 2005 job, with some minor changes, but
I'm not sure if that is a good solution. Especially when something goes
wrong, I would like to know exactly where things went wrong and what steps
have to be executed to finish the job.
Any help or ideas are appreciated!Did you try to migrate this package to SQL 2005. The migration process will
migrate the DTS package into SSIS, and encapsulate all functionality it
can't upgrade into mini DTS 2000 packages which will run as subpackages of
the SSIS Package. You can then at your leisure redesign the mini DTS 2000
packages into your SSIS package.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We are migrating from SQL server 7.0 to SQL server 2005.
> In 7.0 I have made a DTS package that does a complex task on pre-scheduled
> times, started by a SQL job.
> This is a brief description of the complex task:
> -Copy files from several computers in the network to a local "working
> directory"
> -Verify the contents of the files (pre-formatted plain text files)
> -Move files with size or content errors to a different directory
> -Load the files that have no errors into a SQL server table (tblImport)
> -Mark records in tblImport that already exist in tblHistory
> -Gather user information about the files that have been succesfully loaded
> into tblImport
> -Create several reports (plain text files) based on the information in the
> files
> -Print the reports to different printers
> -Add the non-marked records in tblImport to tblHistory
> -Delete the non-marked records in tblImport
> -Send e-mails to several recipients with statistical information about the
> task
> This package is made with a number of activex scripts and SQL tasks.
> Because the server this package is running on now is outdated, I have the
> challenge to make this task work in SQL server 2005.
> The database it is using is already transferred to SQL 2005.
> I have seen that creating packages in 2005 is totally different than
> creating packages in 7.0.
> In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
> only
> load a .dtsx file.
> To me it is ok if I have to build the package in 2005 from scratch or
> build
> it in a totally different way, just as long as it is not taking me too
> much
> time. A different way could be that I copy and paste the scripts and SQL
> statements into several steps of a 2005 job, with some minor changes, but
> I'm not sure if that is a good solution. Especially when something goes
> wrong, I would like to know exactly where things went wrong and what steps
> have to be executed to finish the job.
> Any help or ideas are appreciated!
>
>|||Thanks for your response Hilary.
Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It gives
me an error saying "This SQL Server version (7.0) is not supported."
Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> Did you try to migrate this package to SQL 2005. The migration process
will
> migrate the DTS package into SSIS, and encapsulate all functionality it
> can't upgrade into mini DTS 2000 packages which will run as subpackages of
> the SSIS Package. You can then at your leisure redesign the mini DTS 2000
> packages into your SSIS package.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "a" <a@.b.c> wrote in message
news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> >
> > We are migrating from SQL server 7.0 to SQL server 2005.
> > In 7.0 I have made a DTS package that does a complex task on
pre-scheduled
> > times, started by a SQL job.
> > This is a brief description of the complex task:
> > -Copy files from several computers in the network to a local "working
> > directory"
> > -Verify the contents of the files (pre-formatted plain text files)
> > -Move files with size or content errors to a different directory
> > -Load the files that have no errors into a SQL server table (tblImport)
> > -Mark records in tblImport that already exist in tblHistory
> > -Gather user information about the files that have been succesfully
loaded
> > into tblImport
> > -Create several reports (plain text files) based on the information in
the
> > files
> > -Print the reports to different printers
> > -Add the non-marked records in tblImport to tblHistory
> > -Delete the non-marked records in tblImport
> > -Send e-mails to several recipients with statistical information about
the
> > task
> >
> > This package is made with a number of activex scripts and SQL tasks.
> > Because the server this package is running on now is outdated, I have
the
> > challenge to make this task work in SQL server 2005.
> > The database it is using is already transferred to SQL 2005.
> > I have seen that creating packages in 2005 is totally different than
> > creating packages in 7.0.
> > In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
> > only
> > load a .dtsx file.
> > To me it is ok if I have to build the package in 2005 from scratch or
> > build
> > it in a totally different way, just as long as it is not taking me too
> > much
> > time. A different way could be that I copy and paste the scripts and SQL
> > statements into several steps of a 2005 job, with some minor changes,
but
> > I'm not sure if that is a good solution. Especially when something goes
> > wrong, I would like to know exactly where things went wrong and what
steps
> > have to be executed to finish the job.
> >
> > Any help or ideas are appreciated!
> >
> >
> >
> >
>|||Can you save the dts packages as structured storage and then open them up in
SQL 2000 Package Designed and save them there and then try to use the
migration wizard?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> Thanks for your response Hilary.
> Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> gives
> me an error saying "This SQL Server version (7.0) is not supported."
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
>> Did you try to migrate this package to SQL 2005. The migration process
> will
>> migrate the DTS package into SSIS, and encapsulate all functionality it
>> can't upgrade into mini DTS 2000 packages which will run as subpackages
>> of
>> the SSIS Package. You can then at your leisure redesign the mini DTS 2000
>> packages into your SSIS package.
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "a" <a@.b.c> wrote in message
> news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
>> > Hi,
>> >
>> > We are migrating from SQL server 7.0 to SQL server 2005.
>> > In 7.0 I have made a DTS package that does a complex task on
> pre-scheduled
>> > times, started by a SQL job.
>> > This is a brief description of the complex task:
>> > -Copy files from several computers in the network to a local "working
>> > directory"
>> > -Verify the contents of the files (pre-formatted plain text files)
>> > -Move files with size or content errors to a different directory
>> > -Load the files that have no errors into a SQL server table (tblImport)
>> > -Mark records in tblImport that already exist in tblHistory
>> > -Gather user information about the files that have been succesfully
> loaded
>> > into tblImport
>> > -Create several reports (plain text files) based on the information in
> the
>> > files
>> > -Print the reports to different printers
>> > -Add the non-marked records in tblImport to tblHistory
>> > -Delete the non-marked records in tblImport
>> > -Send e-mails to several recipients with statistical information about
> the
>> > task
>> >
>> > This package is made with a number of activex scripts and SQL tasks.
>> > Because the server this package is running on now is outdated, I have
> the
>> > challenge to make this task work in SQL server 2005.
>> > The database it is using is already transferred to SQL 2005.
>> > I have seen that creating packages in 2005 is totally different than
>> > creating packages in 7.0.
>> > In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
>> > only
>> > load a .dtsx file.
>> > To me it is ok if I have to build the package in 2005 from scratch or
>> > build
>> > it in a totally different way, just as long as it is not taking me too
>> > much
>> > time. A different way could be that I copy and paste the scripts and
>> > SQL
>> > statements into several steps of a 2005 job, with some minor changes,
> but
>> > I'm not sure if that is a good solution. Especially when something goes
>> > wrong, I would like to know exactly where things went wrong and what
> steps
>> > have to be executed to finish the job.
>> >
>> > Any help or ideas are appreciated!
>> >
>> >
>> >
>> >
>>
>|||I saved the package as a DTS file and then tried to open it with right
clicking on the Data Transformations Services Folder.
It opened succesfully and an icon was created in DTS folder.
When I right clicked on the icon I had the options to Open, Export, Migrate
or Delete the package.
The Open option showed me a messagebox telling me that the SQL Server 2000
DTS Designer Component need to be installed to edit the package. I
downloaded this designer and installed it. Now I am able to view and edit
the package the way I did in SQL 7.0. But I have not found a way to start
this package on scheduled times with the SQL Jobs. The package can be
started manually from the menu or with the button on the toolbar, so I think
there is a possibillity to start it with an Operating System command.
In SQL 7.0 you can right click a package to schedule it and then an
Operating System command is created to start the package.
The command (for example) then looks like this: (between the dashes)
--
DTSRun /~S 0xDEC29263B482BF0654C6653D6E68736D /~N
0x1787C8739479E8EEE3EDD35BDD12403EAF86F119B7E6BB7D71C055D523BCAF9B80C874A881
ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F469E8326E6839D2B482D22143EC03E /E
--
I don't know exactly what this command does and I also do not know how to
create a similar command in SQL 2005.
Unfortunately in SQL 2005 the option to schedule a package is not available.
I also tried to migrate the package with the Migrate option. It did the
migration job without any errors. So now I can see in the Integration
Services an icon representing the migrated package. But...
When I right click on the icon I do not have an option to view or modify the
package. I can Run the package, but because I cannot check if the migration
is succesfull in my opinion and I can not modify the package, I don't think
I am going to use the migrated package.
So 3 questions are still open:
1 How can schedule the execution of the (original) package? (Probably with
Job running a complex operating System Command, but I don't know how to
create that command)
2 Is there a way to view, modify and schedule the migrated package?
3 How do I create a new package (or something else) that can do similar
tasks like the one I described below. (because there are more packages I
need to migrate to SQL 2005)
Thanks,
John.
Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
news:#ULCMy1MHHA.4912@.TK2MSFTNGP02.phx.gbl...
> Can you save the dts packages as structured storage and then open them up
in
> SQL 2000 Package Designed and save them there and then try to use the
> migration wizard?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> > Thanks for your response Hilary.
> > Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> > gives
> > me an error saying "This SQL Server version (7.0) is not supported."
> >
> >
> >
> > Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> > news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> >> Did you try to migrate this package to SQL 2005. The migration process
> > will
> >> migrate the DTS package into SSIS, and encapsulate all functionality it
> >> can't upgrade into mini DTS 2000 packages which will run as subpackages
> >> of
> >> the SSIS Package. You can then at your leisure redesign the mini DTS
2000
> >> packages into your SSIS package.
> >>
> >> --
> >> Hilary Cotter
> >>
> >> Looking for a SQL Server replication book?
> >> http://www.nwsu.com/0974973602.html
> >>
> >> Looking for a FAQ on Indexing Services/SQL FTS
> >> http://www.indexserverfaq.com
> >>
> >>
> >>
> >> "a" <a@.b.c> wrote in message
> > news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> >> > Hi,
> >> >
> >> > We are migrating from SQL server 7.0 to SQL server 2005.
> >> > In 7.0 I have made a DTS package that does a complex task on
> > pre-scheduled
> >> > times, started by a SQL job.
> >> > This is a brief description of the complex task:
> >> > -Copy files from several computers in the network to a local "working
> >> > directory"
> >> > -Verify the contents of the files (pre-formatted plain text files)
> >> > -Move files with size or content errors to a different directory
> >> > -Load the files that have no errors into a SQL server table
(tblImport)
> >> > -Mark records in tblImport that already exist in tblHistory
> >> > -Gather user information about the files that have been succesfully
> > loaded
> >> > into tblImport
> >> > -Create several reports (plain text files) based on the information
in
> > the
> >> > files
> >> > -Print the reports to different printers
> >> > -Add the non-marked records in tblImport to tblHistory
> >> > -Delete the non-marked records in tblImport
> >> > -Send e-mails to several recipients with statistical information
about
> > the
> >> > task
> >> >
> >> > This package is made with a number of activex scripts and SQL tasks.
> >> > Because the server this package is running on now is outdated, I have
> > the
> >> > challenge to make this task work in SQL server 2005.
> >> > The database it is using is already transferred to SQL 2005.
> >> > I have seen that creating packages in 2005 is totally different than
> >> > creating packages in 7.0.
> >> > In SQL 7.0 I can save the packages into a .dts file, but in 2005 I
can
> >> > only
> >> > load a .dtsx file.
> >> > To me it is ok if I have to build the package in 2005 from scratch or
> >> > build
> >> > it in a totally different way, just as long as it is not taking me
too
> >> > much
> >> > time. A different way could be that I copy and paste the scripts and
> >> > SQL
> >> > statements into several steps of a 2005 job, with some minor changes,
> > but
> >> > I'm not sure if that is a good solution. Especially when something
goes
> >> > wrong, I would like to know exactly where things went wrong and what
> > steps
> >> > have to be executed to finish the job.
> >> >
> >> > Any help or ideas are appreciated!
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||I have found all the answers myself:
1)
Create a job with the following activex script:
Const DTSSQLStgFlag_UseTrustedConnection = 256
Dim dts
Set dts = CreateObject("dts.Package")
dts.LoadFromSQLServer "MyServer", , , DTSSQLStgFlag_UseTrustedConnection, ,
, , "MyPackage"
dts.Execute
Set dts = Nothing
Make sure that logging is enabled in the package and specify an error log
file.
Actually quite simple!
2 and 3)
In the SQL Server Business Intelligence Development Studio you can create an
Integration Services Project.
From the Project menu select 'Add Existing Package'
Specify where the package can be found. Click Ok. Now the icon of the
package appears in the Solution Explorer.
Right click on the icon of the package and select 'View designer.'
In the designer you can find all the SQL 7.0 options (and much more!) to
edit packages.
a <a@.b.c> wrote in message news:OkVNdF9MHHA.4992@.TK2MSFTNGP04.phx.gbl...
> I saved the package as a DTS file and then tried to open it with right
> clicking on the Data Transformations Services Folder.
> It opened succesfully and an icon was created in DTS folder.
> When I right clicked on the icon I had the options to Open, Export,
Migrate
> or Delete the package.
> The Open option showed me a messagebox telling me that the SQL Server 2000
> DTS Designer Component need to be installed to edit the package. I
> downloaded this designer and installed it. Now I am able to view and edit
> the package the way I did in SQL 7.0. But I have not found a way to start
> this package on scheduled times with the SQL Jobs. The package can be
> started manually from the menu or with the button on the toolbar, so I
think
> there is a possibillity to start it with an Operating System command.
> In SQL 7.0 you can right click a package to schedule it and then an
> Operating System command is created to start the package.
> The command (for example) then looks like this: (between the dashes)
> --
> DTSRun /~S 0xDEC29263B482BF0654C6653D6E68736D /~N
>
0x1787C8739479E8EEE3EDD35BDD12403EAF86F119B7E6BB7D71C055D523BCAF9B80C874A881
> ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F469E8326E6839D2B482D22143EC03E /E
> --
> I don't know exactly what this command does and I also do not know how to
> create a similar command in SQL 2005.
> Unfortunately in SQL 2005 the option to schedule a package is not
available.
> I also tried to migrate the package with the Migrate option. It did the
> migration job without any errors. So now I can see in the Integration
> Services an icon representing the migrated package. But...
> When I right click on the icon I do not have an option to view or modify
the
> package. I can Run the package, but because I cannot check if the
migration
> is succesfull in my opinion and I can not modify the package, I don't
think
> I am going to use the migrated package.
> So 3 questions are still open:
> 1 How can schedule the execution of the (original) package? (Probably with
> Job running a complex operating System Command, but I don't know how to
> create that command)
> 2 Is there a way to view, modify and schedule the migrated package?
> 3 How do I create a new package (or something else) that can do similar
> tasks like the one I described below. (because there are more packages I
> need to migrate to SQL 2005)
> Thanks,
> John.
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:#ULCMy1MHHA.4912@.TK2MSFTNGP02.phx.gbl...
> > Can you save the dts packages as structured storage and then open them
up
> in
> > SQL 2000 Package Designed and save them there and then try to use the
> > migration wizard?
> >
> > --
> > Hilary Cotter
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "a" <a@.b.c> wrote in message
news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> > > Thanks for your response Hilary.
> > > Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> > > gives
> > > me an error saying "This SQL Server version (7.0) is not supported."
> > >
> > >
> > >
> > > Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> > > news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> > >> Did you try to migrate this package to SQL 2005. The migration
process
> > > will
> > >> migrate the DTS package into SSIS, and encapsulate all functionality
it
> > >> can't upgrade into mini DTS 2000 packages which will run as
subpackages
> > >> of
> > >> the SSIS Package. You can then at your leisure redesign the mini DTS
> 2000
> > >> packages into your SSIS package.
> > >>
> > >> --
> > >> Hilary Cotter
> > >>
> > >> Looking for a SQL Server replication book?
> > >> http://www.nwsu.com/0974973602.html
> > >>
> > >> Looking for a FAQ on Indexing Services/SQL FTS
> > >> http://www.indexserverfaq.com
> > >>
> > >>
> > >>
> > >> "a" <a@.b.c> wrote in message
> > > news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> > >> > Hi,
> > >> >
> > >> > We are migrating from SQL server 7.0 to SQL server 2005.
> > >> > In 7.0 I have made a DTS package that does a complex task on
> > > pre-scheduled
> > >> > times, started by a SQL job.
> > >> > This is a brief description of the complex task:
> > >> > -Copy files from several computers in the network to a local
"working
> > >> > directory"
> > >> > -Verify the contents of the files (pre-formatted plain text files)
> > >> > -Move files with size or content errors to a different directory
> > >> > -Load the files that have no errors into a SQL server table
> (tblImport)
> > >> > -Mark records in tblImport that already exist in tblHistory
> > >> > -Gather user information about the files that have been succesfully
> > > loaded
> > >> > into tblImport
> > >> > -Create several reports (plain text files) based on the information
> in
> > > the
> > >> > files
> > >> > -Print the reports to different printers
> > >> > -Add the non-marked records in tblImport to tblHistory
> > >> > -Delete the non-marked records in tblImport
> > >> > -Send e-mails to several recipients with statistical information
> about
> > > the
> > >> > task
> > >> >
> > >> > This package is made with a number of activex scripts and SQL
tasks.
> > >> > Because the server this package is running on now is outdated, I
have
> > > the
> > >> > challenge to make this task work in SQL server 2005.
> > >> > The database it is using is already transferred to SQL 2005.
> > >> > I have seen that creating packages in 2005 is totally different
than
> > >> > creating packages in 7.0.
> > >> > In SQL 7.0 I can save the packages into a .dts file, but in 2005 I
> can
> > >> > only
> > >> > load a .dtsx file.
> > >> > To me it is ok if I have to build the package in 2005 from scratch
or
> > >> > build
> > >> > it in a totally different way, just as long as it is not taking me
> too
> > >> > much
> > >> > time. A different way could be that I copy and paste the scripts
and
> > >> > SQL
> > >> > statements into several steps of a 2005 job, with some minor
changes,
> > > but
> > >> > I'm not sure if that is a good solution. Especially when something
> goes
> > >> > wrong, I would like to know exactly where things went wrong and
what
> > > steps
> > >> > have to be executed to finish the job.
> > >> >
> > >> > Any help or ideas are appreciated!
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Migration of SQL 7.0 package to SQL 2005

Hi,
We are migrating from SQL server 7.0 to SQL server 2005.
In 7.0 I have made a DTS package that does a complex task on pre-scheduled
times, started by a SQL job.
This is a brief description of the complex task:
-Copy files from several computers in the network to a local "working
directory"
-Verify the contents of the files (pre-formatted plain text files)
-Move files with size or content errors to a different directory
-Load the files that have no errors into a SQL server table (tblImport)
-Mark records in tblImport that already exist in tblHistory
-Gather user information about the files that have been succesfully loaded
into tblImport
-Create several reports (plain text files) based on the information in the
files
-Print the reports to different printers
-Add the non-marked records in tblImport to tblHistory
-Delete the non-marked records in tblImport
-Send e-mails to several recipients with statistical information about the
task
This package is made with a number of activex scripts and SQL tasks.
Because the server this package is running on now is outdated, I have the
challenge to make this task work in SQL server 2005.
The database it is using is already transferred to SQL 2005.
I have seen that creating packages in 2005 is totally different than
creating packages in 7.0.
In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can only
load a .dtsx file.
To me it is ok if I have to build the package in 2005 from scratch or build
it in a totally different way, just as long as it is not taking me too much
time. A different way could be that I copy and paste the scripts and SQL
statements into several steps of a 2005 job, with some minor changes, but
I'm not sure if that is a good solution. Especially when something goes
wrong, I would like to know exactly where things went wrong and what steps
have to be executed to finish the job.
Any help or ideas are appreciated!Did you try to migrate this package to SQL 2005. The migration process will
migrate the DTS package into SSIS, and encapsulate all functionality it
can't upgrade into mini DTS 2000 packages which will run as subpackages of
the SSIS Package. You can then at your leisure redesign the mini DTS 2000
packages into your SSIS package.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We are migrating from SQL server 7.0 to SQL server 2005.
> In 7.0 I have made a DTS package that does a complex task on pre-scheduled
> times, started by a SQL job.
> This is a brief description of the complex task:
> -Copy files from several computers in the network to a local "working
> directory"
> -Verify the contents of the files (pre-formatted plain text files)
> -Move files with size or content errors to a different directory
> -Load the files that have no errors into a SQL server table (tblImport)
> -Mark records in tblImport that already exist in tblHistory
> -Gather user information about the files that have been succesfully loaded
> into tblImport
> -Create several reports (plain text files) based on the information in the
> files
> -Print the reports to different printers
> -Add the non-marked records in tblImport to tblHistory
> -Delete the non-marked records in tblImport
> -Send e-mails to several recipients with statistical information about the
> task
> This package is made with a number of activex scripts and SQL tasks.
> Because the server this package is running on now is outdated, I have the
> challenge to make this task work in SQL server 2005.
> The database it is using is already transferred to SQL 2005.
> I have seen that creating packages in 2005 is totally different than
> creating packages in 7.0.
> In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
> only
> load a .dtsx file.
> To me it is ok if I have to build the package in 2005 from scratch or
> build
> it in a totally different way, just as long as it is not taking me too
> much
> time. A different way could be that I copy and paste the scripts and SQL
> statements into several steps of a 2005 job, with some minor changes, but
> I'm not sure if that is a good solution. Especially when something goes
> wrong, I would like to know exactly where things went wrong and what steps
> have to be executed to finish the job.
> Any help or ideas are appreciated!
>
>|||Thanks for your response Hilary.
Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It gives
me an error saying "This SQL Server version (7.0) is not supported."
Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> Did you try to migrate this package to SQL 2005. The migration process
will
> migrate the DTS package into SSIS, and encapsulate all functionality it
> can't upgrade into mini DTS 2000 packages which will run as subpackages of
> the SSIS Package. You can then at your leisure redesign the mini DTS 2000
> packages into your SSIS package.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "a" <a@.b.c> wrote in message
news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
pre-scheduled[vbcol=seagreen]
loaded[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
but[vbcol=seagreen]
steps[vbcol=seagreen]
>|||Can you save the dts packages as structured storage and then open them up in
SQL 2000 Package Designed and save them there and then try to use the
migration wizard?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> Thanks for your response Hilary.
> Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> gives
> me an error saying "This SQL Server version (7.0) is not supported."
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> will
> news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> pre-scheduled
> loaded
> the
> the
> the
> but
> steps
>|||I saved the package as a DTS file and then tried to open it with right
clicking on the Data Transformations Services Folder.
It opened succesfully and an icon was created in DTS folder.
When I right clicked on the icon I had the options to Open, Export, Migrate
or Delete the package.
The Open option showed me a messagebox telling me that the SQL Server 2000
DTS Designer Component need to be installed to edit the package. I
downloaded this designer and installed it. Now I am able to view and edit
the package the way I did in SQL 7.0. But I have not found a way to start
this package on scheduled times with the SQL Jobs. The package can be
started manually from the menu or with the button on the toolbar, so I think
there is a possibillity to start it with an Operating System command.
In SQL 7.0 you can right click a package to schedule it and then an
Operating System command is created to start the package.
The command (for example) then looks like this: (between the dashes)
--
DTSRun /~S 0xDEC29263B482BF0654C6653D6E68736D /~N
0x1787C8739479E8EEE3EDD35BDD12403EAF86F1
19B7E6BB7D71C055D523BCAF9B80C874A881
ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F
469E8326E6839D2B482D22143EC03E /E
--
I don't know exactly what this command does and I also do not know how to
create a similar command in SQL 2005.
Unfortunately in SQL 2005 the option to schedule a package is not available.
I also tried to migrate the package with the Migrate option. It did the
migration job without any errors. So now I can see in the Integration
Services an icon representing the migrated package. But...
When I right click on the icon I do not have an option to view or modify the
package. I can Run the package, but because I cannot check if the migration
is succesfull in my opinion and I can not modify the package, I don't think
I am going to use the migrated package.
So 3 questions are still open:
1 How can schedule the execution of the (original) package? (Probably with
Job running a complex operating System Command, but I don't know how to
create that command)
2 Is there a way to view, modify and schedule the migrated package?
3 How do I create a new package (or something else) that can do similar
tasks like the one I described below. (because there are more packages I
need to migrate to SQL 2005)
Thanks,
John.
Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
news:#ULCMy1MHHA.4912@.TK2MSFTNGP02.phx.gbl...
> Can you save the dts packages as structured storage and then open them up
in
> SQL 2000 Package Designed and save them there and then try to use the
> migration wizard?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
2000[vbcol=seagreen]
(tblImport)[vbcol=seagreen]
in[vbcol=seagreen]
about[vbcol=seagreen]
can[vbcol=seagreen]
too[vbcol=seagreen]
goes[vbcol=seagreen]
>|||I have found all the answers myself:
1)
Create a job with the following activex script:
Const DTSSQLStgFlag_UseTrustedConnection = 256
Dim dts
Set dts = CreateObject("dts.Package")
dts.LoadFromSQLServer "MyServer", , , DTSSQLStgFlag_UseTrustedConnection, ,
, , "MyPackage"
dts.Execute
Set dts = Nothing
Make sure that logging is enabled in the package and specify an error log
file.
Actually quite simple!
2 and 3)
In the SQL Server Business Intelligence Development Studio you can create an
Integration Services Project.
From the Project menu select 'Add Existing Package'
Specify where the package can be found. Click Ok. Now the icon of the
package appears in the Solution Explorer.
Right click on the icon of the package and select 'View designer.'
In the designer you can find all the SQL 7.0 options (and much more!) to
edit packages.
a <a@.b.c> wrote in message news:OkVNdF9MHHA.4992@.TK2MSFTNGP04.phx.gbl...
> I saved the package as a DTS file and then tried to open it with right
> clicking on the Data Transformations Services Folder.
> It opened succesfully and an icon was created in DTS folder.
> When I right clicked on the icon I had the options to Open, Export,
Migrate
> or Delete the package.
> The Open option showed me a messagebox telling me that the SQL Server 2000
> DTS Designer Component need to be installed to edit the package. I
> downloaded this designer and installed it. Now I am able to view and edit
> the package the way I did in SQL 7.0. But I have not found a way to start
> this package on scheduled times with the SQL Jobs. The package can be
> started manually from the menu or with the button on the toolbar, so I
think
> there is a possibillity to start it with an Operating System command.
> In SQL 7.0 you can right click a package to schedule it and then an
> Operating System command is created to start the package.
> The command (for example) then looks like this: (between the dashes)
> --
> DTSRun /~S 0xDEC29263B482BF0654C6653D6E68736D /~N
>
0x1787C8739479E8EEE3EDD35BDD12403EAF86F1
19B7E6BB7D71C055D523BCAF9B80C874A881[vbc
ol=seagreen]
> ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F
469E8326E6839D2B482D22143EC03E /E
> --
> I don't know exactly what this command does and I also do not know how to
> create a similar command in SQL 2005.
> Unfortunately in SQL 2005 the option to schedule a package is not[/vbcol]
available.
> I also tried to migrate the package with the Migrate option. It did the
> migration job without any errors. So now I can see in the Integration
> Services an icon representing the migrated package. But...
> When I right click on the icon I do not have an option to view or modify
the
> package. I can Run the package, but because I cannot check if the
migration
> is succesfull in my opinion and I can not modify the package, I don't
think
> I am going to use the migrated package.
> So 3 questions are still open:
> 1 How can schedule the execution of the (original) package? (Probably with
> Job running a complex operating System Command, but I don't know how to
> create that command)
> 2 Is there a way to view, modify and schedule the migrated package?
> 3 How do I create a new package (or something else) that can do similar
> tasks like the one I described below. (because there are more packages I
> need to migrate to SQL 2005)
> Thanks,
> John.
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:#ULCMy1MHHA.4912@.TK2MSFTNGP02.phx.gbl...
up[vbcol=seagreen]
> in
news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
process[vbcol=seagreen]
it[vbcol=seagreen]
subpackages[vbcol=seagreen]
> 2000
"working[vbcol=seagreen]
> (tblImport)
> in
> about
tasks.[vbcol=seagreen]
have[vbcol=seagreen]
than[vbcol=seagreen]
> can
or[vbcol=seagreen]
> too
and[vbcol=seagreen]
changes,[vbcol=seagreen]
> goes
what[vbcol=seagreen]
>