Need some kind of run sp order

Hi all,
I have a report that uses three stored procs as 3 datasets. Two of these
stored procs further down the report are large select statements drawn over
a table which is populated with data by the first stored proc (i.e.
dataset). The problem is that when I preview the report, I know the first sp
has ran because the table is populated with data but the second and third
run but don't find any data.
Any takes on this one?
Regards
John.There is no guarantee of order. To do this you should use a subreport
instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <a@.b.c> wrote in message
news:OQ6VpFHWFHA.2768@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a report that uses three stored procs as 3 datasets. Two of these
> stored procs further down the report are large select statements drawn
> over a table which is populated with data by the first stored proc (i.e.
> dataset). The problem is that when I preview the report, I know the first
> sp has ran because the table is populated with data but the second and
> third run but don't find any data.
> Any takes on this one?
> Regards
> John.
>|||While it is true that there is no 100% guaranteed order (it may change in
future releases), this is what you can do in RS 2000:
* make all 3 datasets use the same data source
* check "use transaction" on the data source properties dialog
The datasets will then get executed sequentially (within the same
transaction) in the order they are defined in the RDL (which is typically
the order in which they show up in the dataset drop-down list in report
designer).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eBke1wIWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> There is no guarantee of order. To do this you should use a subreport
> instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <a@.b.c> wrote in message
> news:OQ6VpFHWFHA.2768@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report that uses three stored procs as 3 datasets. Two of these
>> stored procs further down the report are large select statements drawn
>> over a table which is populated with data by the first stored proc (i.e.
>> dataset). The problem is that when I preview the report, I know the first
>> sp has ran because the table is populated with data but the second and
>> third run but don't find any data.
>> Any takes on this one?
>> Regards
>> John.
>

Need some help with dropping certain rows in a sql table ****HELP! ****

Hello,
I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:
Current data:
Vendor Item Frequency Price
AA 101 25 10.50
AA 102 10 20.50
AA 103 2 9.75
AA 104 2 10.99
AA 105 1 10.99
BB 101 25 10.50
BB 102 1020.50
BB 103 29.75
BB 104 210.99
BB 105 221.25
BB 106 121.25
Desired result:
Vendor Item Frequency Price
AA 101 25 10.50
AA 102 10 20.50
AA 104 2 10.99
BB 101 2510.50
BB 102 1020.50
BB 105 221.25
The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.
I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.
Thank you in advance!
Here is one way to accomplish this using a single statement (SQL Server
2005):
CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));
INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);
;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Need some help with dropping certain rows in a sql table ****

Hello,
I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:
Current data:
Vendor Item Frequency Price
----
AA 101 25 10.50
AA 102 10 20.50
AA 103 2 9.75
AA 104 2 10.99
AA 105 1 10.99
BB 101 25 10.50
BB 102 10 20.50
BB 103 2 9.75
BB 104 2 10.99
BB 105 2 21.25
BB 106 1 21.25
----
Desired result:
Vendor Item Frequency Price
----
AA 101 25 10.50
AA 102 10 20.50
AA 104 2 10.99
BB 101 25 10.50
BB 102 10 20.50
BB 105 2 21.25
----
The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.
I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.
Thank you in advance!Here is one way to accomplish this using a single statement (SQL Server
2005):
CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));
INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);
;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Need help with update/select statements please.

Hi all,
I have the following data and need to clean up all the junk data and keep
the valid Lastname and FirstName. Please
see the result want below. Thank you very much in advance.
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
LastName VARCHAR(25) NULL,
FirstName VARCHAR(25) NULL
)
GO
INSERT #Temp VALUES ('##Jairdullo', '##Mary')
INSERT #Temp VALUES ('(6957)BARAHONA', '(6957)MARIA')
INSERT #Temp VALUES ('(ds)Ah', '(ds)Sati')
INSERT #Temp VALUES ('(ds)BURNS', '(ds)CHRISTA')
INSERT #Temp VALUES ('*ALCARAZ', '*MICHAEL')
INSERT #Temp VALUES ('*CERRATO 95066454', '*GLENDA')
INSERT #Temp VALUES ('.', '23CURT')
INSERT #Temp VALUES ('-GREENE', '34DARLENE')
INSERT #Temp VALUES ('/', '?John')
INSERT #Temp VALUES ('~~~FREEMAN', '@.@.@.ANDREW')
INSERT #Temp VALUES ('123Zargarian', '97Lisa')
INSERT #Temp VALUES ('12DE LA CRUZ', 'SANDRA')
INSERT #Temp VALUES ('6957/ Wolfe', '6957/ Donald')
INSERT #Temp VALUES ('!ABBY', 'ABBY')
INSERT #Temp VALUES ('@.ABBOUD FAOUR', 'PARIS')
INSERT #Temp VALUES ('#ABBOTT', '$MONICA')
INSERT #Temp VALUES ('%ABBENHUYS', '^Abbath')
INSERT #Temp VALUES ('&AAMODT', '(MARILYN')
INSERT #Temp VALUES (')%aaland', '-8052160336')
INSERT #Temp VALUES ('a', 'Ksd')
INSERT #Temp VALUES ('ABD-EL-SHAID', 'DELIA')
INSERT #Temp VALUES ('12HARRISON-PEREZ', '@.#3CHRISTINA')
go
SELECT *
FROM #Temp
go
LastName FirstName
-- --
##Jairdullo ##Mary
(6957)BARAHONA (6957)MARIA
(ds)Ah (ds)Sati
(ds)BURNS (ds)CHRISTA
*ALCARAZ *MICHAEL
*CERRATO 95066454 *GLENDA
. 23CURT
-GREENE 34DARLENE
/ ?John
~~~FREEMAN @.@.@.ANDREW
123Zargarian 97Lisa
12DE LA CRUZ SANDRA
6957/ Wolfe 6957/ Donald
!ABBY ABBY
@.ABBOUD FAOUR PARIS
#ABBOTT $MONICA
%ABBENHUYS ^Abbath
&AAMODT (MARILYN
)%aaland -8052160336
a Ksd
ABD-EL-SHAID DELIA
12HARRISON-PEREZ @.#3CHRISTINA
Result want:
--Note: Remove hypen between the Lastname ABD-EL-SHAID with ABDELSHAID
LastName FirstName
-- --
Jairdullo Mary
BARAHONA MARIA
Ah Sati
BURNS CHRISTA
ALCARAZ MICHAEL
CERRATO GLENDA
GREENE DARLENE
FREEMAN ANDREW
Zargarian Lisa
LACRUZ SANDRA
Wolfe Donald
ABBY ABBY
ABBOUDFAOUR PARIS
ABBOTT MONICA
ABDELSHAID DELIA
12HARRISONPEREZ CHRISTINAUPDATE Foobar
SET last_name
= REPLACE( REPLACE( ..
REPLACE(last_name, '-', '')(
'#','')
..);
first_name
= REPLACE( REPLACE( ..
REPLACE(last_name, '-', '')(
'#','')
..);
You can nest the REPLACE() funciton 32 levels deep. This avoids cursors
and other non-relational code.