Sql Case sensitivity: populate

Wednesday, March 7, 2012

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)

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

Tuesday, February 14, 2012

Catalog Not populating

I can not get the full text catalog to populate.
I have tried it on the most basic datababase and table.
The catalog creates, and it says it successfully
populates, but yet the item count is 0.
I have restored the same DB on a different server, and it
works fine. I have tried so many KB articles with no
luck. Has anyone every seen this?
Any help is appreciated.
The most obvious causes of this problem is:
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&Product=sql2k
Have you already followed the advice in these kb's?
Also please post the any messages you are getting in the application log
from MSSearch or MSSCI.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <scusick@.datapipe.com> wrote in message
news:023601c46f54$2f102620$3a01280a@.phx.gbl...
> I can not get the full text catalog to populate.
> I have tried it on the most basic datababase and table.
> The catalog creates, and it says it successfully
> populates, but yet the item count is 0.
> I have restored the same DB on a different server, and it
> works fine. I have tried so many KB articles with no
> luck. Has anyone every seen this?
> Any help is appreciated.
|||Here are the warnings
The crawl on project <SQLServer SQL0015500005> cannot be
started. All of the crawl seeds have been ignored because
of host, extension, or other URL restrictions. Error:
80040d07 - The specified URL is excluded and will not be
cataloged. The URL restriction rules may have to be
modified to include this URL.
The crawl seed <MSSQL75://SQLServer/6497e884> in project
<SQLServer SQL0015500005> cannot be accessed. Error:
80040d07 - The specified URL is excluded and will not be
cataloged. The URL restriction rules may have to be
modified to include this URL
One or more warnings or errors for Gatherer project
<SQLServer SQL0015500005> were logged to file
<e:\MSSQL\FTDATA\SQLServer\GatherLogs\SQL001550000 5.1.gthr>
.. If you are interested in these messages, please, look at
the file using the gatherer log query object (gthrlog.vbs,
log viewer web page).
I also ran thhrlog.vbs and got the following.
7/21/2004 4:10:56 PM Add The gatherer has
started
7/21/2004 4:10:56 PM Add The initialization
has completed
7/21/2004 4:11:10 PM Add Started Full
crawl
7/21/2004 4:11:10 PM MSSQL75://SQLServer/6497e884
Add URL is excluded
because the URL protocol is not recognized or restricted
7/21/2004 4:11:10 PM Add Completed Full
crawl
Anything anyone can do, would be amazing. I have been
trying for days!
Thanks

>--Original Message--
>The most obvious causes of this problem is:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;317746&Product=sql2k
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;295772&Product=sql2k
>Have you already followed the advice in these kb's?
>Also please post the any messages you are getting in the
application log[vbcol=seagreen]
>from MSSearch or MSSCI.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Steve" <scusick@.datapipe.com> wrote in message
>news:023601c46f54$2f102620$3a01280a@.phx.gbl...
it
>
>.
>
|||Steve,
The primary error is "URL is excluded because the URL protocol is not
recognized or restricted", and a quick search on Google turned up:
"Troubleshooting and Checking Protocol Handler Performance" at
http://msdn.microsoft.com/library/de... formance.asp
Problem:
Gatherer log error "URL is excluded because the URL protocol is not
recognized or restricted"
Solution:
Protocol handler registration may have failed. Run regedit to look at
HKLM\Software\Microsoft\Search\1.0\ProtocolHandler s or the DllRegisterServer
function.
Does this server have both SQL Server and SharePoint installed on it? Is
this an upgrade from SPPS to SQL Server from WSS?
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:19a801c46f5f$cf3cefb0$a301280a@.phx.gbl...[vbcol=seagreen]
> Here are the warnings
> --
> The crawl on project <SQLServer SQL0015500005> cannot be
> started. All of the crawl seeds have been ignored because
> of host, extension, or other URL restrictions. Error:
> 80040d07 - The specified URL is excluded and will not be
> cataloged. The URL restriction rules may have to be
> modified to include this URL.
> The crawl seed <MSSQL75://SQLServer/6497e884> in project
> <SQLServer SQL0015500005> cannot be accessed. Error:
> 80040d07 - The specified URL is excluded and will not be
> cataloged. The URL restriction rules may have to be
> modified to include this URL
> One or more warnings or errors for Gatherer project
> <SQLServer SQL0015500005> were logged to file
> <e:\MSSQL\FTDATA\SQLServer\GatherLogs\SQL001550000 5.1.gthr>
> . If you are interested in these messages, please, look at
> the file using the gatherer log query object (gthrlog.vbs,
> log viewer web page).
> I also ran thhrlog.vbs and got the following.
> 7/21/2004 4:10:56 PM Add The gatherer has
> started
> 7/21/2004 4:10:56 PM Add The initialization
> has completed
> 7/21/2004 4:11:10 PM Add Started Full
> crawl
> 7/21/2004 4:11:10 PM MSSQL75://SQLServer/6497e884
> Add URL is excluded
> because the URL protocol is not recognized or restricted
> 7/21/2004 4:11:10 PM Add Completed Full
> crawl
> Anything anyone can do, would be amazing. I have been
> trying for days!
> Thanks
> us;317746&Product=sql2k
> us;295772&Product=sql2k
> application log
> it
|||can you try to regregister the various SQL FTS components?
Here is a script to do this:
save this to a batch file and run it from a command window:
cd "C:\Program Files\Common Files\System\MSSearch\Bin\"
%windir%\system32\Regsvr32 mssadmin.dll
%windir%\system32\Regsvr32 mssmmcsi.dll
%windir%\system32\Regsvr32 offfilt.dll
%windir%\system32\Regsvr32 %WINDIR%\System32\athprxy.dll
%windir%\system32\Regsvr32 ftsqlpar.dll
%windir%\system32\Regsvr32 msscntrs.dll
%windir%\system32\Regsvr32 mssmsg.dll
%windir%\system32\Regsvr32 mssmulpi.dll
%windir%\system32\Regsvr32 mssph.dll
%windir%\system32\Regsvr32 mssrch.dll
%windir%\system32\Regsvr32 msstools.dll
%windir%\system32\Regsvr32 objcreat.dll
%windir%\system32\Regsvr32 propdefs.dll
%windir%\system32\Regsvr32 srchadm.dll
%windir%\system32\Regsvr32 srchidx.dll
%windir%\system32\Regsvr32 tquery.dll
%windir%\system32\Regsvr32 %WINDIR%\Cluster\gathercl.dll
%windir%\system32\Regsvr32 nlhtml.dll
cd "C:\Program Files\Common Files\System\MSSearch\common\"
%windir%\system32\Regsvr32 mssitlb.dll
%windir%\system32\Regsvr32 %windir%\System32\athprxy.dll
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23YkfjZ4bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Steve,
> The primary error is "URL is excluded because the URL protocol is not
> recognized or restricted", and a quick search on Google turned up:
> "Troubleshooting and Checking Protocol Handler Performance" at
>
http://msdn.microsoft.com/library/de... formance.asp
> Problem:
> Gatherer log error "URL is excluded because the URL protocol is not
> recognized or restricted"
> Solution:
> Protocol handler registration may have failed. Run regedit to look at
> HKLM\Software\Microsoft\Search\1.0\ProtocolHandler s or the
DllRegisterServer
> function.
> Does this server have both SQL Server and SharePoint installed on it? Is
> this an upgrade from SPPS to SQL Server from WSS?
> Regards,
> John
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:19a801c46f5f$cf3cefb0$a301280a@.phx.gbl...
>

Wednesday, March 7, 2012

Challenging Join question

Tuesday, February 14, 2012

Catalog Not populating

Sql Case sensitivity

Blog Archive

About Me