Change default input parameter drop down list size

I am working on a report which uses a multi value input parameter. The value
set is depend on the other input parameter, so the size and value are
different when the value of the other parameter has changed. The problem is
sometime it works perfectly, but the other time when there are only two
value, the drop down list will show "Select All" and first item and gives me
a scroll bar. This is very annoying. Anyone know how to make the drop down
list to disply more desirous number of items. In addition, is there a way to
control the parameter text box length?I have the same problem but even a worse scenario. My list is filtered by
another and can end up with only one entry. While two entries at least lets
you scroll through the list ('cause the scroll buttons are shown), a list of
only one value doesn't even show the scroll buttons.
I also need a way to allow/force a list this short to be at least long
enough to show the scrollbars.
Jon

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Challenging Join question

I have a query in which I have 2 tables, but I join one of the tables to twice. I use the result to populate a drop down list

here is the data in table 1 (BaselineControlPairings)

ID SITEID BaselineID ControlID Description

2 4495 2 1 Jones - Jun 07

here is the data in table 2 (BaselineLog)

TESTID SITEID StartDate EndDate DataSetID

4 4495 2007-05-30 2007-06-07 1

5 4495 2007-06-09 2007-06-15 2

I want the query to do the following.. select all records from

BaselineControlPairings where the site id = 4495,

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.BaselineID = BaselineLog.DataSetID

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.ControlID = BaselineLog.DataSetID

Now I string the result together to be displayed.

Here is my query code

Code Snippet

SELECT BaselineControlPairings.TestID

,CONVERT(varchar(12), BaselineLog.StartDate, 110)as BLStart

,CONVERT(varchar(12), BaselineLog.EndDate, 110)as BLEnd

,CONVERT(varchar(12), BaselineLog_1.StartDate, 110)as CTRLStart

,CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as CTRLEnd

,BaselineControlPairings.Description +' Baseline: '+CONVERT(varchar(12)

, BaselineLog.StartDate, 110)+' - '+CONVERT(varchar(12)

, BaselineLog.EndDate, 110)+' -- '+'Control: '+CONVERT(varchar(12)

, BaselineLog_1.StartDate, 110)+' - '+CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as dates

FROM BaselineControlPairings INNERJOIN

BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID

INNERJOIN

BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID

WHERE(BaselineControlPairings.siteid = 4495)

here is the result of my query, which we know is wrong because it should contain only 1 result

Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-30-2007 - 06-07-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-30-2007 - 06-07-2007

I should only have one record in the result because I should only have the number of results that are in the

BaselineControlPairings with a matching SiteID

I know the issue is occuring in the join, but I have no idea on how to resolve it and I have spent numerous hours on this. any suggestions?

hi, your sample data and your desired output doesn't seem to match.

anyway here's my hunch,

SELECT BaselineControlPairings.ID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12)
, BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12)
, BaselineLog.EndDate, 110) + ' -- ' + 'Control: ' + CONVERT(varchar(12)
, BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM BaselineControlPairings INNER JOIN
BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)|||Thanks for the reply. I tried your solution, and although better its still broken. I am still getting some duplications but not as many. Also here is my actual code and data:

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd

,BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates

FROM BaselineControlPairings INNER JOIN
BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
WHERE (BaselineControlPairings.siteid = 4495)

The data I am using:

BaselineLog Table

WhoProvisTestID SiteID StartDate EndDate Results DataSetID BLorCTRL

NULL 55 4495 2007-05-27 2007-06-04 NULL 5 0

NULL 56 4495 2007-05-27 2007-06-04 NULL 2 0

NULL 57 4495 2007-05-27 2007-06-04 NULL 3 1

NULL 58 905 2007-05-28 2007-05-31 NULL 3 0

NULL 59 905 2007-05-28 2007-05-31 NULL 1 0

NULL 60 907 2007-05-27 2007-05-29 NULL 17 0

BaselineControlPairings Table

TestID SiteID BaselineID ControlID Description

-- -- -- -- -

25 905 3 1 hgc

26 4495 5 3 df

27 4495 2 5 df

against thes tables I should receive 1 result for the query using ID 905 and 2 for 4495, BUT
for 905 I receive 2 rows both for testID 25, yet there is only 1 test id.

here are the results for 905:
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
25 05-27-2007 06-04-2007 05-28-2007 05-31-2007 hgc Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
25 05-28-2007 05-31-2007 05-28-2007 05-31-2007 hgc Baseline: 05-28-2007 - 05-31-2007 -- Control: 05-28-2007 - 05-31-2007

and for 4495 I should get 2 rows, 1 for test id 26 and one for id 27. Yet I get (2) for 26 and one for 27.

ARGH ! ! !

Results for id 4495
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
26 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007
26 05-27-2007 06-04-2007 05-28-2007 05-31-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
27 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007

any help is greatly appreciated!|||hi

i'm not sure on what you've meant was still broken, but based on your test data and expected result, if you add BaselineControlPairings.SiteID = BaselineLog_1.SiteID and BaselineControlPairings.SiteID = BaselineLog.SiteID respectively on your joins would result to what you're expecting.

select *
into #BaseLog
from (
select NULL as WhoProvis
, 55 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 5 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 56 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 2 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 57 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 3 as DataSetID
, 1 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 58 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 3 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 59 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 1 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 60 as TestID
, 907 as SiteID
, '2007-05-27' as StartDate
, '2007-06-29' as EndDate
, NULL as Results
, 17 as DataSetID
, 0 as BLorCTRL
) BaseLog

select *
into #BaselineControlPairings
from (
select 25 as TestID
, 905 as SiteID
, 3 as BaselineID
, 1 as ControlID
, 'hgc' as Description
UNION ALL
select 26 as TestID
, 4495 as SiteID
, 5 as BaselineID
, 3 as ControlID
, 'df' as Description
UNION ALL
select 27 as TestID
, 4495 as SiteID
, 2 as BaselineID
, 5 as ControlID
, 'df' as Description
) BaselineControlPairings

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 905)

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)

drop table #BaseLog
drop table #BaselineControlPairings|||Thanks, this fixed it... I think the mistake I was making was not writing to a temp file.|||hi, i'm sorry i don't think that writing to a temp file made the difference. maybe you might have misplaced the ON filter for the SiteID