one for the SQL experts - dare I say TRICKY SQL!

Guys,
Hopefully someone can help.
We have a monitoring program that has threads which start and stop
monitoring at various times. There are two tables:
THREADLIFECYCLE
unique_id
start_time (always populated)
end_time (not populated until the thread ends)
MONITORRESULTS
unique_id
time_of_measurement
value
What I am trying to do is find the average value for each of the
numbers of running threads. To explain further, threads will start,
stop independently and overlap each other.
I want an output that says:
When 1 thread was running: average value was x
When 3 threads were running: average value was y
Due to the start and stop nature there could be 1 thread running at the
beginning of the test, mid way through, a number of occassions, etc.
Also, the number of threads does not necessarily ramp sequantially -
the number running at any time could be like this sequence: 1, 5, 10,
7, 12, 4, 2
ANY help would be much appreciated - it really has stumped me but looks
like it should be so simple ... But aren't they always the hard ones
;-(
Thanks
GrahamWhy don't you include some sample data so that people here don't have
to do that part also?|||Some DDL and sample data would be useful. Here's an untested
shot in the dark...
CREATE TABLE THREADLIFECYCLE (unique_id INT,
start_time DATETIME NOT NULL,
end_time DATETIME)
CREATE TABLE MONITORRESULTS(unique_id INT,
time_of_measurement DATETIME NOT NULL,
value DECIMAL(10,2))
SELECT t.unique_id AS ThreadID,AVG(m.value) AS AverageValue
FROM MONITORRESULTS m
INNER JOIN THREADLIFECYCLE t ON m.time_of_measurement BETWEEN
t.start_time and t.end_time
GROUP BY t.unique_id|||> What I am trying to do is find the average value for each of the
> numbers of running threads. To explain further, threads will start,
> stop independently and overlap each other.
Please include DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Aaron Bertrand [SQL Server MVP] wrote:
> Please include DDL, sample data, and desired results.
> http://www.aspfaq.com/5006
OP already described desired results in the original post. DDL while
OK isn't hard to do.
As I already requested, yes sample data is something that most people
won't take the time to fudge up by themselves.
What exactly is a "SQL Server MVP?"|||> What exactly is a "SQL Server MVP?"
http://mvp.support.microsoft.com/mvpexecsum
http://tinyurl.com/79hu8|||just add:
count(m.value) as NumThreads
to the select list and I think you will be all set with the code below..
<markc600@.hotmail.com> wrote in message
news:1139318246.514858.196180@.o13g2000cwo.googlegroups.com...
> Some DDL and sample data would be useful. Here's an untested
> shot in the dark...
>
> CREATE TABLE THREADLIFECYCLE (unique_id INT,
> start_time DATETIME NOT NULL,
> end_time DATETIME)
> CREATE TABLE MONITORRESULTS(unique_id INT,
> time_of_measurement DATETIME NOT NULL,
> value DECIMAL(10,2))
>
> SELECT t.unique_id AS ThreadID,AVG(m.value) AS AverageValue
> FROM MONITORRESULTS m
> INNER JOIN THREADLIFECYCLE t ON m.time_of_measurement BETWEEN
> t.start_time and t.end_time
> GROUP BY t.unique_id
>|||Graham has asked me to post this on his behalf.
CREATE TABLE [dbo].[threadstart] (
[threadid] numeric(20,0) NOT NULL,
[startstamp] datetime NOT NULL,
[stopstamp] datetime NULL
)
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(1, '2006-2-7 2:3:0.0', '2006-2-7 2:7:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(2, '2006-2-7 2:4:0.0', '2006-2-7 2:5:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(3, '2006-2-7 2:6:0.0', '2006-2-7 2:7:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(4, '2006-2-7 2:8:0.0', '2006-2-7 2:10:0.0')
GO
CREATE TABLE [dbo].[result] (
[threadid] numeric(20,0) NOT NULL,
[scriptid] numeric(6,0) NOT NULL,
[startstamp] datetime NOT NULL,
[measurement] numeric(38,15) NOT NULL,
[errorcount] numeric(5,0) NOT NULL,
CONSTRAINT [PK_result] PRIMARY KEY([scriptid],[threadid])
)
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 1, 1, '2006-2-7 2:3:44.0', 10, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 1, 2, '2006-2-7 2:4:44.0', 10, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 2, 3, '2006-2-7 2:4:44.0', 20, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 2, 4, '2006-2-7 2:4:54.0', 20, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 3, 5, '2006-2-7 2:6:44.0', 30, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 3, 6, '2006-2-7 2:7:44.0', 30, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 4, 7, '2006-2-7 2:8:44.0', 40, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 4, 8, '2006-2-7 2:9:44.0', 40, 0)
GO
Expected Results
Count of Users Avg Rsp Time
1 50
2 30
3 40|||Please explain how an average responce time may be 50 when none of the
measurements exceed 40|||There are 2 occassions when the user count is 1 at the start of
Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
Therefore 100 measurements in total. Two threads therefore average =
50.

one for the SQL experts - dare I say TRICKY SQL!

Guys,

Hopefully someone can help.

We have a monitoring program that has threads which start and stop
monitoring at various times. There are two tables:

THREADLIFECYCLE

unique_id
start_time (always populated)
end_time (not populated until the thread ends)

MONITORRESULTS

unique_id
time_of_measurement
value

What I am trying to do is find the average value for each of the
numbers of running threads. To explain further, threads will start,
stop independently and overlap each other.

I want an output that says:

When 1 thread was running: average value was x
When 3 threads were running: average value was y

Due to the start and stop nature there could be 1 thread running at the
beginning of the test, mid way through, a number of occassions, etc.

Also, the number of threads does not necessarily ramp sequantially -
the number running at any time could be like this sequence: 1, 5, 10,
7, 12, 4, 2

ANY help would be much appreciated - it really has stumped me but looks
like it should be so simple ... But aren't they always the hard ones
;-(

Thanks

GrahamWhy don't you include some sample data so that people here don't have
to do that part also?|||Some DDL and sample data would be useful. Here's an untested
shot in the dark...

CREATE TABLE THREADLIFECYCLE (unique_id INT,
start_time DATETIME NOT NULL,
end_time DATETIME)

CREATE TABLE MONITORRESULTS(unique_id INT,
time_of_measurement DATETIME NOT NULL,
value DECIMAL(10,2))

SELECT t.unique_id AS ThreadID,AVG(m.value) AS AverageValue
FROM MONITORRESULTS m
INNER JOIN THREADLIFECYCLE t ON m.time_of_measurement BETWEEN
t.start_time and t.end_time
GROUP BY t.unique_id|||> What I am trying to do is find the average value for each of the
> numbers of running threads. To explain further, threads will start,
> stop independently and overlap each other.

Please include DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Aaron Bertrand [SQL Server MVP] wrote:
> > What I am trying to do is find the average value for each of the
> > numbers of running threads. To explain further, threads will start,
> > stop independently and overlap each other.
> Please include DDL, sample data, and desired results.
> http://www.aspfaq.com/5006

OP already described desired results in the original post. DDL while
OK isn't hard to do.

As I already requested, yes sample data is something that most people
won't take the time to fudge up by themselves.

What exactly is a "SQL Server MVP?"|||> What exactly is a "SQL Server MVP?"

http://mvp.support.microsoft.com/mvpexecsum

http://tinyurl.com/79hu8|||Graham has asked me to post this on his behalf.

CREATE TABLE [dbo].[threadstart] (
[threadid] numeric(20,0) NOT NULL,
[startstamp] datetime NOT NULL,
[stopstamp] datetime NULL
)

INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])

VALUES(1, '2006-2-7 2:3:0.0', '2006-2-7 2:7:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])

VALUES(2, '2006-2-7 2:4:0.0', '2006-2-7 2:5:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])

VALUES(3, '2006-2-7 2:6:0.0', '2006-2-7 2:7:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])

VALUES(4, '2006-2-7 2:8:0.0', '2006-2-7 2:10:0.0')
GO

CREATE TABLE [dbo].[result] (
[threadid] numeric(20,0) NOT NULL,
[scriptid] numeric(6,0) NOT NULL,
[startstamp] datetime NOT NULL,
[measurement] numeric(38,15) NOT NULL,
[errorcount] numeric(5,0) NOT NULL,
CONSTRAINT [PK_result] PRIMARY KEY([scriptid],[threadid])
)

INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 1, 1, '2006-2-7 2:3:44.0', 10, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 1, 2, '2006-2-7 2:4:44.0', 10, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 2, 3, '2006-2-7 2:4:44.0', 20, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 2, 4, '2006-2-7 2:4:54.0', 20, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 3, 5, '2006-2-7 2:6:44.0', 30, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 3, 6, '2006-2-7 2:7:44.0', 30, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 4, 7, '2006-2-7 2:8:44.0', 40, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 4, 8, '2006-2-7 2:9:44.0', 40, 0)
GO

Expected Results

Count of UsersAvg Rsp Time
150
230
340|||Please explain how an average responce time may be 50 when none of the
measurements exceed 40|||There are 2 occassions when the user count is 1 at the start of
Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
Therefore 100 measurements in total. Two threads therefore average =
50.|||paulspratley@.yahoo.co.uk wrote:
> There are 2 occassions when the user count is 1 at the start of
> Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
> 10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
> Therefore 100 measurements in total. Two threads therefore average =
> 50.

Then the column name in your sample report is misleading:
Expected Results

Count of Users Avg Rsp Time
1 50
2 30
3 40|||If you had posted DDL, would it look like this?

Since thread_id might actually be a key instead of a non-relational
physical sequence number.

CREATE TABLE Threads
(thread_id INTEGER NOT NULL PRIMARY KEY,
start_stamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
stop_stamp DATETIME NULL,
CHECK(start_stamp < stop_stamp));

INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
02:07:00');
INSERT INTO Threads VALUES(2, '2006-02-07 02:04:00', '2006-02-07
02:05:00');
INSERT INTO Threads VALUES(3, '2006-02-07 02:06:00', '2006-02-07
02:07:00');
INSERT INTO Threads VALUES(4, '2006-02-07 02:08:00', '2006-02-07
02:10:00');

The measurements clearly have a key in their time stamp.

CREATE TABLE Measurements
(meas_stamp DATETIME NOT NULL PRIMARY KEY,
meas_value DECIMAL (5,2) NOT NULL);

INSERT INTO Measurements VALUES('2006-02-07 02:03:44', 10.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:44', 10.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:45', 20.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:54', 20.0);
INSERT INTO Measurements VALUES('2006-02-07 02:06:44', 30.0);
INSERT INTO Measurements VALUES('2006-02-07 02:07:44', 30.0);
INSERT INTO Measurements VALUES('2006-02-07 02:08:44', 40.0);
INSERT INTO Measurements VALUES('2006-02-07 02:09:44', 40.0);

Now you can use a between preidcate to place each measurement inside an
on-going event.

CREATE VIEW Summary (meas_stamp, active, meas_tot)
AS
SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
SUM(M.meas_value) AS meas_tot
FROM Threads AS T, Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
GROUP BY M.meas_stamp;

meas_stamp active_tally meas_tot
=================================
2006-02-07 02:03:44.000110.00
2006-02-07 02:04:44.000220.00
2006-02-07 02:04:45.000240.00
2006-02-07 02:04:54.000240.00
2006-02-07 02:06:44.000260.00
2006-02-07 02:08:44.000140.00
2006-02-07 02:09:44.000140.00

Put this VIEW (or derived table or CTE) into a another query:

SELECT active_tally, SUM(meas_tot / COUNT(*)) AS meas_avg
FROM Summary
GROUP BY active_tally;|||On 7 Feb 2006 08:40:14 -0800, paulspratley@.yahoo.co.uk wrote:

>There are 2 occassions when the user count is 1 at the start of
>Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
>10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
>Therefore 100 measurements in total. Two threads therefore average =
>50.

Hi paulspratley,

I still can't figure this one out.

First, I'm surprised that you want to factor in both measurements of
thread 1. After all, one of those measurements was taken when a total of
two threads was running. The initial post by Graham suggests to me that
this measurement should not be used here. But maybe I'm misreading the
vague description Graham posted?

Second, with the logic outline above, I can explain the first line of
the expected results, but neither the second, not the third.

>Count of Users Avg Rsp Time
>1 50
>2 30
>3 40

There are two active threads when during the lifecycle of thread 2
(overlaps with 1) and 3 (overlaps with 1 as well). According to the
logic above, we'll have to use 2*10=20 for thread 1, 2*20=40 for thread
2, and 2*30=60 for thread 3. A total of 120, for three threads - this
averages out to 40, not 30 as you state in the expected results.

There isn't even one single occasion with three (or more) threads
simultaneously active. So where does the third row come from?

BTW, You posted to both SQL Server and Oracle groups - what DB are you
actually running on? These DBMSes are not 100% compatible.

--
Hugo Kornelis, SQL Server MVP|||(paulspratley@.yahoo.co.uk) writes:
> There are 2 occassions when the user count is 1 at the start of
> Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
> 10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
> Therefore 100 measurements in total. Two threads therefore average =
> 50.

It's very difficult to suggest a query, when the sample data does not
really match the description, and when there is not really any any
good description of the business problems.

In the sample data, the result for scriptid = 6 is from an occassion
when no thread was running, not even the thread that was said to be
running.

Why the two measurements for threadid = 1 should count for one user
is beyond me, as when the second measurement is record, there is another
thread.

At no occassion there are three threads running what I can see.

I composed this query, but it does not give the desired result.

SELECT cnt, avg(summeasurement)
FROM (SELECT cnt, threadid, summeasurement = sum(measurement)
FROM (SELECT r.measurement, r.threadid,
cnt = (SELECT COUNT(*)
FROM threadstart t
WHERE r.startstamp BETWEEN t.startstamp AND
coalesce(t.stopstamp,
'99991231'))
FROM result r) AS x
GROUP BY threadid, cnt) AS b
GROUP BY cnt
ORDER BY cnt

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
> 02:07:00');

This is very dangerous code, its worse than SELECT * and relies columns
being in order which we know in a set is just not the case.

ALWAYS specify the columns on your INSERT...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
'2006-02-07 02:03:00', '2006-02-07 02:07:00')

Also, use standard formatting for the dates - '2006-02-07T02:07:00'

> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
> SUM(M.meas_value) AS meas_tot
> FROM Threads AS T, Measurements AS M
> WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> GROUP BY M.meas_stamp;

Stop using that outdated column syntax nobody except oldbies unwilling to
change use.

SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally, SUM(M.meas_value)
AS meas_tot
FROM Threads AS T
CROSS JOIN Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
GROUP BY M.meas_stamp;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139340784.592141.244420@.o13g2000cwo.googlegr oups.com...
> If you had posted DDL, would it look like this?
> Since thread_id might actually be a key instead of a non-relational
> physical sequence number.
> CREATE TABLE Threads
> (thread_id INTEGER NOT NULL PRIMARY KEY,
> start_stamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> stop_stamp DATETIME NULL,
> CHECK(start_stamp < stop_stamp));
> INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
> 02:07:00');
> INSERT INTO Threads VALUES(2, '2006-02-07 02:04:00', '2006-02-07
> 02:05:00');
> INSERT INTO Threads VALUES(3, '2006-02-07 02:06:00', '2006-02-07
> 02:07:00');
> INSERT INTO Threads VALUES(4, '2006-02-07 02:08:00', '2006-02-07
> 02:10:00');
> The measurements clearly have a key in their time stamp.
> CREATE TABLE Measurements
> (meas_stamp DATETIME NOT NULL PRIMARY KEY,
> meas_value DECIMAL (5,2) NOT NULL);
> INSERT INTO Measurements VALUES('2006-02-07 02:03:44', 10.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:04:44', 10.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:04:45', 20.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:04:54', 20.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:06:44', 30.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:07:44', 30.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:08:44', 40.0);
> INSERT INTO Measurements VALUES('2006-02-07 02:09:44', 40.0);
> Now you can use a between preidcate to place each measurement inside an
> on-going event.
> CREATE VIEW Summary (meas_stamp, active, meas_tot)
> AS
> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
> SUM(M.meas_value) AS meas_tot
> FROM Threads AS T, Measurements AS M
> WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> GROUP BY M.meas_stamp;
> meas_stamp active_tally meas_tot
> =================================
> 2006-02-07 02:03:44.000 1 10.00
> 2006-02-07 02:04:44.000 2 20.00
> 2006-02-07 02:04:45.000 2 40.00
> 2006-02-07 02:04:54.000 2 40.00
> 2006-02-07 02:06:44.000 2 60.00
> 2006-02-07 02:08:44.000 1 40.00
> 2006-02-07 02:09:44.000 1 40.00
> Put this VIEW (or derived table or CTE) into a another query:
> SELECT active_tally, SUM(meas_tot / COUNT(*)) AS meas_avg
> FROM Summary
> GROUP BY active_tally;|||Comments embedded.
Tony Rogerson wrote:
> > INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
> > 02:07:00');
> This is very dangerous code, its worse than SELECT * and relies columns
> being in order which we know in a set is just not the case.
> ALWAYS specify the columns on your INSERT...
> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
> '2006-02-07 02:03:00', '2006-02-07 02:07:00')

To this I heartily agree.

> Also, use standard formatting for the dates - '2006-02-07T02:07:00'

Standard to which DBMS? Certainly not Oracle. I will submit passing
date strings without a proper format specifier is poor coding:

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, to_date( '2006-02-07 02:03:00', 'YYYY-MM-DD HH24:MI:SS'),
to_date( '2006-02-07 02:07:00', 'YYYY-MM-DD
HH24:MI:SS'))

For those using SQL Server:

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, convert(datetime, '2006-02-07 02:03:00', 120),
convert(datetime, '2006-02-07 02:07:00', 120))

One should never assume a universal date/time format.

> > SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
> > SUM(M.meas_value) AS meas_tot
> > FROM Threads AS T, Measurements AS M
> > WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> > GROUP BY M.meas_stamp;
> Stop using that outdated column syntax nobody except oldbies unwilling to
> change use.

Nothing wrong with using it as it returns the proper results. I will
admit once one is accustomed to using the ANSI join syntax it is easier
to write and prettier to view. But, ugliness doesn't make it wrong.

> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally, SUM(M.meas_value)
> AS meas_tot
> FROM Threads AS T
> CROSS JOIN Measurements AS M
> WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> GROUP BY M.meas_stamp;
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials

And, unfortunately for the SQL Server crowd this was also posted to
comp.databases.oracle.server. Unfortunate because the modified example
provided throws an error from SQL*Plus:

ERROR at line 3:
ORA-00933: SQL command not properly ended

and is the result of using AS to declare the table aliases. Oracle
simply doesn't accept it, and I'm fairly certain SQL Server can get by
without it as well. To make the previously posted code 'palatable' to
SQL*Plus:

SQL> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
SUM(M.meas_value)
2 AS meas_tot
3 FROM Threads T
4 CROSS JOIN Measurements M
5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
6 GROUP BY M.meas_stamp;

MEAS_STAM ACTIVE_TALLY MEAS_TOT
--- ---- ----
07-FEB-06 1 10
07-FEB-06 2 20
07-FEB-06 2 40
07-FEB-06 2 40
07-FEB-06 2 60
07-FEB-06 1 40
07-FEB-06 1 40

7 rows selected.

Note it's still using the 'prettier' ANSI syntax (and, again, simply
because it's possibly ugly doesn't make the old style join syntax
wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
declaring the table aliases.

David Fitzjarrell|||fitzjarrell@.cox.net wrote:

>And, unfortunately for the SQL Server crowd this was also posted to
>comp.databases.oracle.server. Unfortunate because the modified example
>provided throws an error from SQL*Plus:
>ERROR at line 3:
>ORA-00933: SQL command not properly ended
>and is the result of using AS to declare the table aliases. Oracle
>simply doesn't accept it, and I'm fairly certain SQL Server can get by
>without it as well. To make the previously posted code 'palatable' to
>SQL*Plus:
>SQL> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
>SUM(M.meas_value)
> 2 AS meas_tot
> 3 FROM Threads T
> 4 CROSS JOIN Measurements M
> 5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> 6 GROUP BY M.meas_stamp;
>
>MEAS_STAM ACTIVE_TALLY MEAS_TOT
>--- ---- ----
>07-FEB-06 1 10
>07-FEB-06 2 20
>07-FEB-06 2 40
>07-FEB-06 2 40
>07-FEB-06 2 60
>07-FEB-06 1 40
>07-FEB-06 1 40
>7 rows selected.
>Note it's still using the 'prettier' ANSI syntax (and, again, simply
>because it's possibly ugly doesn't make the old style join syntax
>wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
>declaring the table aliases.
>
Just a quick (minor) note: 'AS' is defined in the ANSI SQL-99 standard
(ISO/IEC 9075-2:1999) as an optional keyword in the FROM clause between
the object name (table, view, derived table, whatever) and the alias for
it. So, while it's true that SQL Server can get by without it (it's
optional in the T-SQL grammar, as it is in SQL-99) and it's not defined
in the PL/SQL grammar, technically speaking, Tony's code was right in
that it conformed to SQL-99.

--
*mike hodgson*
http://sqlnerd.blogspot.com|||>>Standard to which DBMS? <<

Unh? That senternce makes no sense in the database world. ANSI/ISO
Standards apply to all vendor products. The product either meets or
fails them. Vendors do not set their own privates Standards (note the
capital S).

>> Certainly not Oracle. <<

Oracle is still a nightmare of non-conformance to ANSI/ISO, X/Open, etc
Standards, as well as expensive and hard to use. It is a kind of
"Hillbilly dialect" of SQL :)

>> I will submit passing date strings without a proper format specifier is poor coding: <<

Did you know that SQL has one and only one allowed date format?
Apparently not. It is based on ISO-8601, a Standard used in many other
ISO standards.

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898|||>> 'AS' is defined in the ANSI SQL-99 Standard ... an optional keyword in the FROM clause between the object name (table, view, derived table, whatever) and the alias for
it. <<

I like it because it separates things nicely for the eye. In real old
days, leaving out a comma in a FROM clause could accidently create an
alias for a table. I find it funny that people who use the wordy
infixed join syntax for INNER JOINs often skip the AS keyword.|||> Standard to which DBMS? Certainly not Oracle. I will submit passing
> date strings without a proper format specifier is poor coding:

The ISO standard rather than vendor specific.

> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
> VALUES(1, convert(datetime, '2006-02-07 02:03:00', 120),
> convert(datetime, '2006-02-07 02:07:00', 120))

You do not and would not code it like that in SQL Server, you would simply
write...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, '2006-02-07T02:03:00', '2006-02-07T02:07:00')

The rest of the post, basically I was refering to the ANSI 92 INNER JOIN,
CROSS JOIN syntax over the ANSI 89 comma syntax.

We got the ANSI 92 syntax in version 6.5 of MS SQL Server which was around
96/97, the majority 99.9% of people in the MS SQL Server space using ANSI 92
now and convert what I term the 'out-dated' syntax to ANSI 92.

I didn't see the cross posting news groups so the syntax specific stuff
refers to MS SQL Server, not sure Oracle and Sybase got it until the last
few years so you'll go through a similar curver imho.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<fitzjarrell@.cox.net> wrote in message
news:1139538033.974291.324400@.g14g2000cwa.googlegr oups.com...
> Comments embedded.
> Tony Rogerson wrote:
>> > INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
>> > 02:07:00');
>>
>> This is very dangerous code, its worse than SELECT * and relies columns
>> being in order which we know in a set is just not the case.
>>
>> ALWAYS specify the columns on your INSERT...
>>
>> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
>> '2006-02-07 02:03:00', '2006-02-07 02:07:00')
>>
> To this I heartily agree.
>> Also, use standard formatting for the dates - '2006-02-07T02:07:00'
>>
> Standard to which DBMS? Certainly not Oracle. I will submit passing
> date strings without a proper format specifier is poor coding:
> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
> VALUES(1, to_date( '2006-02-07 02:03:00', 'YYYY-MM-DD HH24:MI:SS'),
> to_date( '2006-02-07 02:07:00', 'YYYY-MM-DD
> HH24:MI:SS'))
> For those using SQL Server:
> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
> VALUES(1, convert(datetime, '2006-02-07 02:03:00', 120),
> convert(datetime, '2006-02-07 02:07:00', 120))
> One should never assume a universal date/time format.
>> > SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
>> > SUM(M.meas_value) AS meas_tot
>> > FROM Threads AS T, Measurements AS M
>> > WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
>> > GROUP BY M.meas_stamp;
>>
>> Stop using that outdated column syntax nobody except oldbies unwilling to
>> change use.
>>
> Nothing wrong with using it as it returns the proper results. I will
> admit once one is accustomed to using the ANSI join syntax it is easier
> to write and prettier to view. But, ugliness doesn't make it wrong.
>> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
>> SUM(M.meas_value)
>> AS meas_tot
>> FROM Threads AS T
>> CROSS JOIN Measurements AS M
>> WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
>> GROUP BY M.meas_stamp;
>>
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlserverfaq.com - free video tutorials
>>
>>
> And, unfortunately for the SQL Server crowd this was also posted to
> comp.databases.oracle.server. Unfortunate because the modified example
> provided throws an error from SQL*Plus:
> ERROR at line 3:
> ORA-00933: SQL command not properly ended
> and is the result of using AS to declare the table aliases. Oracle
> simply doesn't accept it, and I'm fairly certain SQL Server can get by
> without it as well. To make the previously posted code 'palatable' to
> SQL*Plus:
> SQL> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
> SUM(M.meas_value)
> 2 AS meas_tot
> 3 FROM Threads T
> 4 CROSS JOIN Measurements M
> 5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> 6 GROUP BY M.meas_stamp;
>
> MEAS_STAM ACTIVE_TALLY MEAS_TOT
> --- ---- ----
> 07-FEB-06 1 10
> 07-FEB-06 2 20
> 07-FEB-06 2 40
> 07-FEB-06 2 40
> 07-FEB-06 2 60
> 07-FEB-06 1 40
> 07-FEB-06 1 40
> 7 rows selected.
> Note it's still using the 'prettier' ANSI syntax (and, again, simply
> because it's possibly ugly doesn't make the old style join syntax
> wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
> declaring the table aliases.
>
> David Fitzjarrell|||--CELKO-- wrote:
> Oracle is still a nightmare of non-conformance to ANSI/ISO, X/Open, etc
> Standards, as well as expensive and hard to use. It is a kind of
> "Hillbilly dialect" of SQL :)

Not to disparage standards but to be intellectually honest you should
acknolwedge that all SQL RDBMS's are non-conformant in one manner or
another. If they weren't they would have a product that was only
marginally capable of handling the real-world environment.

> Did you know that SQL has one and only one allowed date format?
> Apparently not. It is based on ISO-8601, a Standard used in many other
> ISO standards.

An good example of precisely what I meant by my statement above.

> as well as expensive and hard to use.

But please let me strongly dispute the above. How can you claim Oracle
as expensive when it provides functionality not available in SQL Server
for any price. Need RAC? No price will get it. Want on-line object
redefinition. No price will get it for you. Want humongous numbers of
other high-end capabilities. Better start writing them yourself in C#.
If all you need is tables and indexes then I'd suggest MySQL. I wouldn't
pay either Microsoft or Oracle a dime.

And hard to use? Maybe a decade ago. Fly on into Seattle and I'll get
you both the best scotch you've ever had and a good lesson on using
the Grid.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||Tony Rogerson wrote:
> > Standard to which DBMS? Certainly not Oracle. I will submit passing
> > date strings without a proper format specifier is poor coding:
> The ISO standard rather than vendor specific.
> > INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
> > VALUES(1, convert(datetime, '2006-02-07 02:03:00', 120),
> > convert(datetime, '2006-02-07 02:07:00', 120))
> You do not and would not code it like that in SQL Server, you would simply
> write...
> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
> VALUES(1, '2006-02-07T02:03:00', '2006-02-07T02:07:00') [snip]

Not sure which difference you mean.

The whole purpose of using the ISO 8601 format is that you don't need to
specify the converting mode. So I would omit it too.

But I would probably also specify the datetime as '2006-02-07 02:07:00'.
I still use SQL 7.0, and this format is upwards compatible. The format
'2006-02-07T02:07:00' is harder to read, and needs at least SQL Server
2000.

Gert-Jan|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> But I would probably also specify the datetime as '2006-02-07 02:07:00'.
> I still use SQL 7.0, and this format is upwards compatible. The format
> '2006-02-07T02:07:00' is harder to read, and needs at least SQL Server
> 2000.

But '2006-02-07 02:07:00' is subject to different interpretations depending
on dateformat settings. For instance try:

SET LANGUAGE Dutch
go
SELECT convert(datetime , '2006-02-07 02:07:00')
go
SET LANGUAGE Swedish
go
SELECT convert(datetime , '2006-02-07 02:07:00')
go

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > But I would probably also specify the datetime as '2006-02-07 02:07:00'.
> > I still use SQL 7.0, and this format is upwards compatible. The format
> > '2006-02-07T02:07:00' is harder to read, and needs at least SQL Server
> > 2000.
> But '2006-02-07 02:07:00' is subject to different interpretations depending
> on dateformat settings. For instance try:
> SET LANGUAGE Dutch
> go
> SELECT convert(datetime , '2006-02-07 02:07:00')
> go
> SET LANGUAGE Swedish
> go
> SELECT convert(datetime , '2006-02-07 02:07:00')
> go

You are right, I made a mistake. I would have written '20060207
02:07:00', which has no such side effects. So Tony was right: under
normal circumstances, no one would use "convert(datetime, '2006-02-07
02:03:00', 120)" on SQL Server.

Gert-Jan

OlyMars help?

Hi guys,
It seems that my last question was a little involved :)

Has any one got experience of working with OlyMars on Windows XP?

If so did you have problems with the OlyMars Tutorial in particular running the batch scripts?

I'm stuck on the section of Generation of Ready to Use Objects

Any help is appreciated :)

FBI think your best bet on this is one of the resources on this page:OlyMars: SQL Server Centric .NET Code Generator.

In particular there is a newsgroup that should help you: news://msnews.microsoft.com/microsoft.public.fr.dotnet.olymars. I see questions posted there in both French and English.

Terri|||..Good Stuff! Thanks Terri, I'll check it out now :)

OLEDB & Recordset

Hi Guys,
I'm busy trawling through documentation trying to puzzle out an issue I'm
having. Am about to tackle the SDK.
OK, I'm using MDAC 2.8. Visual Basic 6 and SQL2000 SP3a.
If I execute the following code through a normal ODBC connection (In Admin
tools), I get a result. Works fine.
If I execute the same code, using a UDL file using the "OLE DB
Provider for SQL Server", I get the following error when trying to grab the
value "Item cannot be found in the collection corresponding to the requested
name or ordinal". Also, if i assign a watch to the recordset using the OLEDB
provider,
a lot of the values are unavailable as the object is "Closed"
Dim rs1 As New ADODB.Recordset
Dim BarCode_Database As New ADODB.Connection
Dim sSql As String
sSql = "EXEC SP_Next_Position"
On Error GoTo Error
BarCode_Database.Open Connection_String
Set rs1 = BarCode_Database.Execute(sSql)
Get_Next_Barcode_Position = CStr(rs1!Current_Position)

I've tried messing around with Cursor possition and the like. Maybe I just
havn't hit the right combination :P .
Can anyone steer me in the right direction please?
MUCH appreciated,
Glynn
And what is Connection_string? From the code snippet and
from what you describe, it doesn't look like you are
actually opening a connection.
You'd open the connection using a UDL file with something
like the following on one line:
BarCode_Database.Open
"File Name=c:\PathTo\YourFile.udl"
Refer to the following for more information:
How To Use Data Link Files with ADO
http://support.microsoft.com/?id=189680
-Sue
On Fri, 28 Jan 2005 17:40:27 +0200, "Glynn"
<wwgze@.woolworths.co.za> wrote:

>Hi Guys,
>I'm busy trawling through documentation trying to puzzle out an issue I'm
>having. Am about to tackle the SDK.
>OK, I'm using MDAC 2.8. Visual Basic 6 and SQL2000 SP3a.
>If I execute the following code through a normal ODBC connection (In Admin
>tools), I get a result. Works fine.
>If I execute the same code, using a UDL file using the "OLE DB
>Provider for SQL Server", I get the following error when trying to grab the
>value "Item cannot be found in the collection corresponding to the requested
>name or ordinal". Also, if i assign a watch to the recordset using the OLEDB
>provider,
>a lot of the values are unavailable as the object is "Closed"
>--
>Dim rs1 As New ADODB.Recordset
>Dim BarCode_Database As New ADODB.Connection
>Dim sSql As String
>sSql = "EXEC SP_Next_Position"
>On Error GoTo Error
>BarCode_Database.Open Connection_String
>Set rs1 = BarCode_Database.Execute(sSql)
>
>Get_Next_Barcode_Position = CStr(rs1!Current_Position)
>
>--
>I've tried messing around with Cursor possition and the like. Maybe I just
>havn't hit the right combination :P .
>Can anyone steer me in the right direction please?
>MUCH appreciated,
>Glynn
>

OLEDB & Recordset

Hi Guys,
I'm busy trawling through documentation trying to puzzle out an issue I'm
having. Am about to tackle the SDK.
OK, I'm using MDAC 2.8. Visual Basic 6 and SQL2000 SP3a.
If I execute the following code through a normal ODBC connection (In Admin
tools), I get a result. Works fine.
If I execute the same code, using a UDL file using the "OLE DB
Provider for SQL Server", I get the following error when trying to grab the
value "Item cannot be found in the collection corresponding to the requested
name or ordinal". Also, if i assign a watch to the recordset using the OLEDB
provider,
a lot of the values are unavailable as the object is "Closed"
Dim rs1 As New ADODB.Recordset
Dim BarCode_Database As New ADODB.Connection
Dim sSql As String
sSql = "EXEC SP_Next_Position"
On Error GoTo Error
BarCode_Database.Open Connection_String
Set rs1 = BarCode_Database.Execute(sSql)
Get_Next_Barcode_Position = CStr(rs1!Current_Position)
I've tried messing around with Cursor possition and the like. Maybe I just
havn't hit the right combination :P .
Can anyone steer me in the right direction please?
MUCH appreciated,
GlynnAnd what is Connection_string? From the code snippet and
from what you describe, it doesn't look like you are
actually opening a connection.
You'd open the connection using a UDL file with something
like the following on one line:
BarCode_Database.Open
"File Name=c:\PathTo\YourFile.udl"
Refer to the following for more information:
How To Use Data Link Files with ADO
http://support.microsoft.com/?id=189680
-Sue
On Fri, 28 Jan 2005 17:40:27 +0200, "Glynn"
<wwgze@.woolworths.co.za> wrote:

>Hi Guys,
>I'm busy trawling through documentation trying to puzzle out an issue I'm
>having. Am about to tackle the SDK.
>OK, I'm using MDAC 2.8. Visual Basic 6 and SQL2000 SP3a.
>If I execute the following code through a normal ODBC connection (In Admin
>tools), I get a result. Works fine.
>If I execute the same code, using a UDL file using the "OLE DB
>Provider for SQL Server", I get the following error when trying to grab th
e
>value "Item cannot be found in the collection corresponding to the requeste
d
>name or ordinal". Also, if i assign a watch to the recordset using the OLED
B
>provider,
>a lot of the values are unavailable as the object is "Closed"
>--
>Dim rs1 As New ADODB.Recordset
>Dim BarCode_Database As New ADODB.Connection
>Dim sSql As String
>sSql = "EXEC SP_Next_Position"
>On Error GoTo Error
>BarCode_Database.Open Connection_String
>Set rs1 = BarCode_Database.Execute(sSql)
>
>Get_Next_Barcode_Position = CStr(rs1!Current_Position)
>
>--
>I've tried messing around with Cursor possition and the like. Maybe I just
>havn't hit the right combination :P .
>Can anyone steer me in the right direction please?
>MUCH appreciated,
>Glynn
>

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error

Hi guys,

Anyone encountered this type of error " OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error"

The error you saw is quit common and it can be mapped to many cases. In most cases, the error happens because the server closes the connection for some reason when client is expecting data from the server. Can you described more about you app? Does the error happen during a long running query? Is the error consistent or intermittent.|||

Hello!!!

The application is a Business Intelligence application, wherein lots of extract transformation and loading process are involved before processing the cube. The error is intermittent and happens during the dimension processing. Our server is running Win2k3 sp1 sql server 2005 sp1 also configured with 8 GB memory and AWE enabled.

What we are doing in order to continue the process is to either restart sql server service or the analysis service and process the dimension that failed.

any idea on what is causing the error?

|||

Hi Larry,

General Network Error is a infamous error and one of the toughest cases MS PSS faces nowadays. This is due to there can be various cause of this problem.

There is a webcast on this topic:

http://support.microsoft.com/kb/875285/en-us

I remember there is a common cause of GNE for windows 2003 sp1, but can not find the KB now. Maybe I will find and post it here when I get back to work on Monday for your reference.

Thx, -Justin

|||

Tnx Justin, It will be great and I'll appreciate it if you can post the KB here.

Thanks,

Larry

|||

Here is the KB. This is a general cause of GNE on Win2003 SP1.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;899599

Basically, GNE occurs when the client connection is unexpectedly closed. This mostly due to temporarily network hardware failure or firewall related issue.

|||

In case of your cube application, one of the possibility is that the server decided to close a connection for one of the following reason. (1) query exection was too long, you can extend the connection timeout value to mitigate the issue. (2) your failed query, thus the connection, was choosen as a deadlock victim and killed by the server.

If you are using SQL Server 2005, you might find interesting entries in ERRORLOG to correlate your GNE. For two reasons that I listed above, the better forum to ask how to identify them is from sql server engine forum.

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network erro

Hi guys,

Anyone encountered this type of error " OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error"

The error you saw is quit common and it can be mapped to many cases. In most cases, the error happens because the server closes the connection for some reason when client is expecting data from the server. Can you described more about you app? Does the error happen during a long running query? Is the error consistent or intermittent.|||

Hello!!!

The application is a Business Intelligence application, wherein lots of extract transformation and loading process are involved before processing the cube. The error is intermittent and happens during the dimension processing. Our server is running Win2k3 sp1 sql server 2005 sp1 also configured with 8 GB memory and AWE enabled.

What we are doing in order to continue the process is to either restart sql server service or the analysis service and process the dimension that failed.

any idea on what is causing the error?

|||

Hi Larry,

General Network Error is a infamous error and one of the toughest cases MS PSS faces nowadays. This is due to there can be various cause of this problem.

There is a webcast on this topic:

http://support.microsoft.com/kb/875285/en-us

I remember there is a common cause of GNE for windows 2003 sp1, but can not find the KB now. Maybe I will find and post it here when I get back to work on Monday for your reference.

Thx, -Justin

|||

Tnx Justin, It will be great and I'll appreciate it if you can post the KB here.

Thanks,

Larry

|||

Here is the KB. This is a general cause of GNE on Win2003 SP1.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;899599

Basically, GNE occurs when the client connection is unexpectedly closed. This mostly due to temporarily network hardware failure or firewall related issue.

|||

In case of your cube application, one of the possibility is that the server decided to close a connection for one of the following reason. (1) query exection was too long, you can extend the connection timeout value to mitigate the issue. (2) your failed query, thus the connection, was choosen as a deadlock victim and killed by the server.

If you are using SQL Server 2005, you might find interesting entries in ERRORLOG to correlate your GNE. For two reasons that I listed above, the better forum to ask how to identify them is from sql server engine forum.

OLE DB Command and Stored Procedure that returns value and/or error

Guys,

could someone please tell me : am I supposed to use the OLE DB

Command in a dataflow to call a stored procedure to return a value? Or

is it just supposed to be used to call a straightforward insert

statement only?

What I am hoping to do:

I have a table with a few columns and one identity column. In a

dataflow I would like to effect an insert of a record to this table and

retrieve the identity value of the inserted record... and I'd like to

store the returned identity in a user variable.

If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.

In the Advanced Editor any time I try to add an output column (by

clicking on Add Column) I just get an error dialog that says "the

component does not allow adding columns to this input or output)

So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?

Thanks

PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OLE DB Command and Stored Procedure that returns value and/or error

Guys,
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OldValuesParameterFormatString SqlDataSource Update

OK guys, I'm sure I'm doing something stupid here but after 2 days I find myself extremely frusted... Any help would be appreciated

I'm tring to write an update procedure for an SqlDataSource control that will allow me to chage the primary key values of a record in the table. Everything seems to work fine with the exception of obtaining the old values (the primary key before its changed by the end user) during the update. I've read several articles on the web an in the forums on this topic. Much of it has to do with the .NET beta version alteration from "original_{0}" to "{0}". I believe those conversations do not describe my problem.

Anyhow, onto some source code. Here's the ASP source code for a grid view and sql data source.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="productName,productURL" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="productName" HeaderText="productName" ReadOnly="True" SortExpression="productName" /> <asp:BoundField DataField="productURL" HeaderText="productURL" ReadOnly="True" SortExpression="productURL" /> <asp:BoundField DataField="productSupportURL" HeaderText="productSupportURL" SortExpression="productSupportURL" /> <asp:BoundField DataField="description" HeaderText="description" SortExpression="description" /> <asp:BoundField DataField="modified" HeaderText="modified" SortExpression="modified" /> <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" /> <asp:BoundField DataField="useridDomain" HeaderText="useridDomain" SortExpression="useridDomain" /> <asp:BoundField DataField="sortOrder" HeaderText="sortOrder" SortExpression="sortOrder" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SBAAdminConnectionString1%>" DeleteCommand="DELETE FROM [homePageList] WHERE [productName] = @.old_productName AND [productURL] = @.old_productURL" InsertCommand="INSERT INTO [homePageList] ([productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder]) VALUES (@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrder)" ProviderName="<%$ ConnectionStrings:SBAAdminConnectionString1.ProviderName%>" SelectCommand="SELECT [productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder] FROM [homePageList]" UpdateCommand="UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)" OldValuesParameterFormatString="original_{0}"> <DeleteParameters> <asp:Parameter Name="old_productName" Type="String" /> <asp:Parameter Name="old_productURL" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productName" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productURL" PropertyName="SelectedValue" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> </InsertParameters> </asp:SqlDataSource>

I added a text box for every field in the grid to the form and update the fields when the user selects a record in the GridView. I then added an Update button to the form and tied the event to this procedure: 

Protected Sub bUpdate_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles bUpdate.Click'if the user has not entered key fields!If missingKeyFields()Then Exit Sub End If Dim count, soAs Integer Dim uDomain, uidAs String'parse out the user id and domain.Try If HttpContext.Current.User.Identity.Name =""Then uid ="unknow" uDomain ="unknown"Else uid = HttpContext.Current.User.Identity.Name uid = uid.Substring(uid.IndexOf("\") + 1) uDomain = uid.Substring(0, uid.IndexOf("\") - 1)End If Catch exAs Exception uid ="unknow" uDomain ="unknown"End Try'fill in the parameters '@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrderMe.SqlDataSource1.UpdateParameters.Item("productName").DefaultValue =Me.tbProductName.TextMe.SqlDataSource1.UpdateParameters.Item("productURL").DefaultValue =Me.tbProductURL.TextMe.SqlDataSource1.UpdateParameters.Item("productSupportURL").DefaultValue =Me.tbProductSupportURL.TextMe.SqlDataSource1.UpdateParameters.Item("description").DefaultValue =Me.tbProductDescription.TextMe.SqlDataSource1.UpdateParameters.Item("modified").DefaultValue = NowMe.SqlDataSource1.UpdateParameters.Item("userid").DefaultValue = uidMe.SqlDataSource1.UpdateParameters.Item("useridDomain").DefaultValue = uDomainMe.SqlDataSource1.UpdateParameters.Item("sortOrder").ConvertEmptyStringToNull =True If Integer.TryParse(Me.tbSortOrder.Text, so)Then Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = soElse Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue =""End If'insert the record. count =Me.SqlDataSource1.Update()Me.lCommandInfo.Text = count.ToString +" record was updated."End Sub
 
The net result of the procedure is the message "0 record was updated." when I select a record in the gridView and then edit the "productName" field and click Update.

In an attempt to trace down the problem I added this code:

Protected Sub SqlDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Updating Message.Text = e.Command.CommandText Message.Text +="<br>"For lpAs Integer = 0To e.Command.Parameters.Count - 1 Message.Text += e.Command.Parameters(lp).ParameterName +"=" + e.Command.Parameters(lp).Value Message.Text +="<br>"Next End Sub

The message label displays the following text:

UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)@.description=interesting!@.modified=3/15/2007 4:07:31 PM@.userid=unknow@.useridDomain=unknown@.sortOrder=@.productName=ok22@.productURL=http@.productSupportURL=http@.original_productName=ok@.original_productURL=ok

Notice how the "@.original_productURL" has the same value as the "@.original_productName"? This should not be the case. The @.original_productURL should equal "http" since I didn't update the field's value. It seems like the second field of the primary key is not getting updated or passed to the @.original_productURL parameter. Since .NET handles the @.original_{0} parameters I don't know why the field isn't being properly updated.

Any thoughts on how to track down the root cause of my problem... Or better yet any thoughts on a work around to my problem?

Thanks,
Johnny

way back in school i was taught update is the combination of select ,delete & insert....since u r changing the primarykey........select the row in temp variables,make the changes in the varioable,delete original from database & insert the modified row.....

this can be done by stored procedure or a datareader object easily.

|||

That does sound like a work around for my current situation, but I would hate to re-invent the wheel by creating temp variables that mimic the functionality of the OldValues parameter array.

Any other thoughts?

Johnnny

|||if u find another way let me know|||

OK here's the easiest work around I could find.

1) I removed the control binding for the parameters "original_productName" and "original_productURL". This was set to "GridView1.SelectedValue".
2) I manually set the parameters in my button click subroutine:
Me.SqlDataSource1.UpdateParameters.Item("original_productName").DefaultValue =Me.GridView1.SelectedDataKey.Item(0)
Me.SqlDataSource1.UpdateParameters.Item("original_productURL").DefaultValue =Me.GridView1.SelectedDataKey.Item(1)

I hope this helps others that run into issues with using more then 1 field as a primary key.

Now onto my complaint. While I'm willing to be called a newbie at ASP.NET and will bow to anyone who can show me the proper way to use OLDValues, this problem appears to be a bug in the GridViews control or SqlDataSource control. If I had the source code for these controls I would debug them for Microsoft. To summorize the issue...

1) Create a table with 2 fields as the primary key.
2) Add an SqlDataSource control for the table to an ASP.NET page. Be sure to add SQL code for the Insert, Update, Delete methods with parameters that correspond to the OldValuesParameterFormatString.
3) Add a GridView to the ASP.NET page and set its datasource to the SqlDataSource control.
4) In the Insert, Update, and Delete queries of the SqlDatasource control, bind the old value parameters to the GridView's SelectedValue.
5) Add a label to the ASP.NET page.
6) Add a Button to the ASP.NET page.
7) In the Button click procedure add code to update the record and execute the update method of the SqlDataSource. i.e. Sqldatasource1.updateParameters.item(0).defaultValue = "new value". updatedCount = Sqldatasource1.Update().
8) Display the updated count in the label.

When you test the page you'll find that 0 records are updated. This problem seesm to stem from the fact that only the first column of the GridView is used as the selectedValue. Meaning both key fields for the database table have been set to the first column's value of the GridView. Its almost enough to make me start a blog! lol Good luck people :)

Johnny

|||

what is the tbProductName...

is it the id of the some control.

Old SQL Design Tools

Hi Guys...

I was familiar to use sqlserver2000 more than 2 years, and Utilized from beneficial Tools Like Enterprise Manager and Query Analyzer which was so helpfull... .

afterthen I decide to get knowledge on SqlServer 2005 Enterpise Edition.

and after install 2CD's and SP1 , I try to find the Old Tools mentioned above , or find any DataBase as example , but I find nothing ... so is there any tool do the role to related tools in the last version , or should I get another vesion instead like Express or ...etc.

Thanks

Basel

There should be SQL Server Management Studio, which is both EM and QA at once (an many other features as well).

If only it's not an Express version, either.

|||

just a little word of inspiration

"Don't get intimidated by the new UI"

everything has changed. though you can find it out easily

|||

Inspiration is all what I need to start great new day

Tnax to send your helpful space

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIAWait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in you
r
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:

> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 a
s
> well as in SQL2005
>
> TIA|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
[vbcol=seagreen]
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in y
our
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:
>

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIA
Wait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in your
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:

> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> well as in SQL2005
>
> TIA
|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
[vbcol=seagreen]
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in your
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIAWait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in your
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:
> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> well as in SQL2005
>
> TIA|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in your
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:
> > Hi guys,
> > I find out that if you use DMP and change the backup path from default and
> > set the settings to delete old backup , say after 3 days, it doesnt really
> > delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> > well as in SQL2005
> >
> >
> > TIA