SQL Query Translate to Pass Through Query Problem

[KayC]

Hi
I use SQLServer2000 and MS Access2000
I have the below SQL query which will not work as a Access PassThrough
Query, I have tried replacing the @COB with a date string but the query
hangs forever.
Can anyone direct me the right way?
Thanks
Kay

DECLARE @cob smalldatetime
SELECT @cob = '20060616'

SELECT field1,
field2,
field3,
@cob as report_date
FROM table1
WHERE business_date = @cob
AND field1 in (
SELECT field1
FROM table1
WHERE business_date = @cob
AND field9 = 'N'
AND field10 <> -1
GROUP BY field1)
GROUP BY field1,field2,f ield3

[Terry Kreft]

Re: SQL Query Translate to Pass Through Query Problem

You need to use the convert function to handle string to dates conversion
properly.

Assuming your date is in the format yyyymmdd then you would do something
like:-

DECLARE @cob smalldatetime

SELECT @cob = convert(smallda tetime, '20060616', 112)


Look in BOL at the "CAST and CONVERT" topic for valid values for the third
argument of convert and how tey relate to the date format you're converting
from.


--

Terry Kreft


"KayC" <kay_chua@yahoo .co.uk> wrote in message
news:1150734342 .533725.266380@ g10g2000cwb.goo glegroups.com.. .[color=blue]
> Hi
> I use SQLServer2000 and MS Access2000
> I have the below SQL query which will not work as a Access PassThrough
> Query, I have tried replacing the @COB with a date string but the query
> hangs forever.
> Can anyone direct me the right way?
> Thanks
> Kay
>
> DECLARE @cob smalldatetime
> SELECT @cob = '20060616'
>
> SELECT field1,
> field2,
> field3,
> @cob as report_date
> FROM table1
> WHERE business_date = @cob
> AND field1 in (
> SELECT field1
> FROM table1
> WHERE business_date = @cob
> AND field9 = 'N'
> AND field10 <> -1
> GROUP BY field1)
> GROUP BY field1,field2,f ield3
>[/color]

[KayC]

Re: SQL Query Translate to Pass Through Query Problem

Hi Terry
Thanks for the suggestion
I have tried this and Access still complains with the same error
message below:
Pass-Through query with ReturnsRecords property set to True did not
return any records

Regards
Kay

Terry Kreft wrote:
[color=blue]
> You need to use the convert function to handle string to dates conversion
> properly.
>
> Assuming your date is in the format yyyymmdd then you would do something
> like:-
>
> DECLARE @cob smalldatetime
>
> SELECT @cob = convert(smallda tetime, '20060616', 112)
>
>
> Look in BOL at the "CAST and CONVERT" topic for valid values for the third
> argument of convert and how tey relate to the date format you're converting
> from.
>
>
> --
>
> Terry Kreft
>
>
> "KayC" <kay_chua@yahoo .co.uk> wrote in message
> news:1150734342 .533725.266380@ g10g2000cwb.goo glegroups.com.. .[color=green]
> > Hi
> > I use SQLServer2000 and MS Access2000
> > I have the below SQL query which will not work as a Access PassThrough
> > Query, I have tried replacing the @COB with a date string but the query
> > hangs forever.
> > Can anyone direct me the right way?
> > Thanks
> > Kay
> >
> > DECLARE @cob smalldatetime
> > SELECT @cob = '20060616'
> >
> > SELECT field1,
> > field2,
> > field3,
> > @cob as report_date
> > FROM table1
> > WHERE business_date = @cob
> > AND field1 in (
> > SELECT field1
> > FROM table1
> > WHERE business_date = @cob
> > AND field9 = 'N'
> > AND field10 <> -1
> > GROUP BY field1)
> > GROUP BY field1,field2,f ield3
> >[/color][/color]

[Terry Kreft]

Re: SQL Query Translate to Pass Through Query Problem

Well, this isn't the same error is it, because you weren't getting an error
before acording to your original post.

Anyway, where you have

SELECT @cob = convert(smallda tetime, '20060616', 112)


Change this to

SET NOCOUNT ON
SELECT @cob = convert(smallda tetime, '20060616', 112)


And then at the end put
SET NOCOUNT OFF



--

Terry Kreft


"KayC" <kay_chua@yahoo .co.uk> wrote in message
news:1150792417 .864165.84850@y 41g2000cwy.goog legroups.com...[color=blue]
> Hi Terry
> Thanks for the suggestion
> I have tried this and Access still complains with the same error
> message below:
> Pass-Through query with ReturnsRecords property set to True did not
> return any records
>
> Regards
> Kay
>
> Terry Kreft wrote:
>[color=green]
> > You need to use the convert function to handle string to dates[/color][/color]
conversion[color=blue][color=green]
> > properly.
> >
> > Assuming your date is in the format yyyymmdd then you would do something
> > like:-
> >
> > DECLARE @cob smalldatetime
> >
> > SELECT @cob = convert(smallda tetime, '20060616', 112)
> >
> >
> > Look in BOL at the "CAST and CONVERT" topic for valid values for the[/color][/color]
third[color=blue][color=green]
> > argument of convert and how tey relate to the date format you're[/color][/color]
converting[color=blue][color=green]
> > from.
> >
> >
> > --
> >
> > Terry Kreft
> >
> >
> > "KayC" <kay_chua@yahoo .co.uk> wrote in message
> > news:1150734342 .533725.266380@ g10g2000cwb.goo glegroups.com.. .[color=darkred]
> > > Hi
> > > I use SQLServer2000 and MS Access2000
> > > I have the below SQL query which will not work as a Access PassThrough
> > > Query, I have tried replacing the @COB with a date string but the[/color][/color][/color]
query[color=blue][color=green][color=darkred]
> > > hangs forever.
> > > Can anyone direct me the right way?
> > > Thanks
> > > Kay
> > >
> > > DECLARE @cob smalldatetime
> > > SELECT @cob = '20060616'
> > >
> > > SELECT field1,
> > > field2,
> > > field3,
> > > @cob as report_date
> > > FROM table1
> > > WHERE business_date = @cob
> > > AND field1 in (
> > > SELECT field1
> > > FROM table1
> > > WHERE business_date = @cob
> > > AND field9 = 'N'
> > > AND field10 <> -1
> > > GROUP BY field1)
> > > GROUP BY field1,field2,f ield3
> > >[/color][/color]
>[/color]

[Terry Kreft]

Re: SQL Query Translate to Pass Through Query Problem

By the way, I don' really understand why you are using such a convoluted
piece of SQL.

Where you have
SELECT
field1,
field2,
field3,
@cob report_date
FROM
table1
WHERE
business_date = @cob
AND
field1 in (
SELECT
field1
FROM
table1
WHERE
business_date = @cob
AND
field9 = 'N'
AND
field10 <> -1
GROUP BY field1
)
GROUP BY field1,field2,f ield3

I would have
SELECT DISTINCT
field1,
field2,
field3,
@cob report_date
FROM
table1
WHERE
business_date = @cob
AND
field9 = 'N'
AND
field10 <> -1

which would be faster to execute.

--

Terry Kreft


"Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
news:zhmcnSv1o7 FGVwrZSa8jmw@ka roo.co.uk...[color=blue]
> Well, this isn't the same error is it, because you weren't getting an[/color]
error[color=blue]
> before acording to your original post.
>
> Anyway, where you have
>
> SELECT @cob = convert(smallda tetime, '20060616', 112)
>
>
> Change this to
>
> SET NOCOUNT ON
> SELECT @cob = convert(smallda tetime, '20060616', 112)
>
>
> And then at the end put
> SET NOCOUNT OFF
>
>
>
> --
>
> Terry Kreft
>
>
> "KayC" <kay_chua@yahoo .co.uk> wrote in message
> news:1150792417 .864165.84850@y 41g2000cwy.goog legroups.com...[color=green]
> > Hi Terry
> > Thanks for the suggestion
> > I have tried this and Access still complains with the same error
> > message below:
> > Pass-Through query with ReturnsRecords property set to True did not
> > return any records
> >
> > Regards
> > Kay
> >
> > Terry Kreft wrote:
> >[color=darkred]
> > > You need to use the convert function to handle string to dates[/color][/color]
> conversion[color=green][color=darkred]
> > > properly.
> > >
> > > Assuming your date is in the format yyyymmdd then you would do[/color][/color][/color]
something[color=blue][color=green][color=darkred]
> > > like:-
> > >
> > > DECLARE @cob smalldatetime
> > >
> > > SELECT @cob = convert(smallda tetime, '20060616', 112)
> > >
> > >
> > > Look in BOL at the "CAST and CONVERT" topic for valid values for the[/color][/color]
> third[color=green][color=darkred]
> > > argument of convert and how tey relate to the date format you're[/color][/color]
> converting[color=green][color=darkred]
> > > from.
> > >
> > >
> > > --
> > >
> > > Terry Kreft
> > >
> > >
> > > "KayC" <kay_chua@yahoo .co.uk> wrote in message
> > > news:1150734342 .533725.266380@ g10g2000cwb.goo glegroups.com.. .
> > > > Hi
> > > > I use SQLServer2000 and MS Access2000
> > > > I have the below SQL query which will not work as a Access[/color][/color][/color]
PassThrough[color=blue][color=green][color=darkred]
> > > > Query, I have tried replacing the @COB with a date string but the[/color][/color]
> query[color=green][color=darkred]
> > > > hangs forever.
> > > > Can anyone direct me the right way?
> > > > Thanks
> > > > Kay
> > > >
> > > > DECLARE @cob smalldatetime
> > > > SELECT @cob = '20060616'
> > > >
> > > > SELECT field1,
> > > > field2,
> > > > field3,
> > > > @cob as report_date
> > > > FROM table1
> > > > WHERE business_date = @cob
> > > > AND field1 in (
> > > > SELECT field1
> > > > FROM table1
> > > > WHERE business_date = @cob
> > > > AND field9 = 'N'
> > > > AND field10 <> -1
> > > > GROUP BY field1)
> > > > GROUP BY field1,field2,f ield3
> > > >[/color]
> >[/color]
>
>[/color]

[KayC]

Re: SQL Query Translate to Pass Through Query Problem

Terry,
It looks like the pass through query was taking so long due to the
'convoluted' sql, works now with the nested sql taken out
Thanks again
Kay


Terry Kreft wrote:
[color=blue]
> By the way, I don' really understand why you are using such a convoluted
> piece of SQL.
>
> Where you have
> SELECT
> field1,
> field2,
> field3,
> @cob report_date
> FROM
> table1
> WHERE
> business_date = @cob
> AND
> field1 in (
> SELECT
> field1
> FROM
> table1
> WHERE
> business_date = @cob
> AND
> field9 = 'N'
> AND
> field10 <> -1
> GROUP BY field1
> )
> GROUP BY field1,field2,f ield3
>
> I would have
> SELECT DISTINCT
> field1,
> field2,
> field3,
> @cob report_date
> FROM
> table1
> WHERE
> business_date = @cob
> AND
> field9 = 'N'
> AND
> field10 <> -1
>
> which would be faster to execute.
>
> --
>
> Terry Kreft
>
>
> "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> news:zhmcnSv1o7 FGVwrZSa8jmw@ka roo.co.uk...[color=green]
> > Well, this isn't the same error is it, because you weren't getting an[/color]
> error[color=green]
> > before acording to your original post.
> >
> > Anyway, where you have
> >
> > SELECT @cob = convert(smallda tetime, '20060616', 112)
> >
> >
> > Change this to
> >
> > SET NOCOUNT ON
> > SELECT @cob = convert(smallda tetime, '20060616', 112)
> >
> >
> > And then at the end put
> > SET NOCOUNT OFF
> >
> >
> >
> > --
> >
> > Terry Kreft
> >
> >
> > "KayC" <kay_chua@yahoo .co.uk> wrote in message
> > news:1150792417 .864165.84850@y 41g2000cwy.goog legroups.com...[color=darkred]
> > > Hi Terry
> > > Thanks for the suggestion
> > > I have tried this and Access still complains with the same error
> > > message below:
> > > Pass-Through query with ReturnsRecords property set to True did not
> > > return any records
> > >
> > > Regards
> > > Kay
> > >
> > > Terry Kreft wrote:
> > >
> > > > You need to use the convert function to handle string to dates[/color]
> > conversion[color=darkred]
> > > > properly.
> > > >
> > > > Assuming your date is in the format yyyymmdd then you would do[/color][/color]
> something[color=green][color=darkred]
> > > > like:-
> > > >
> > > > DECLARE @cob smalldatetime
> > > >
> > > > SELECT @cob = convert(smallda tetime, '20060616', 112)
> > > >
> > > >
> > > > Look in BOL at the "CAST and CONVERT" topic for valid values for the[/color]
> > third[color=darkred]
> > > > argument of convert and how tey relate to the date format you're[/color]
> > converting[color=darkred]
> > > > from.
> > > >
> > > >
> > > > --
> > > >
> > > > Terry Kreft
> > > >
> > > >
> > > > "KayC" <kay_chua@yahoo .co.uk> wrote in message
> > > > news:1150734342 .533725.266380@ g10g2000cwb.goo glegroups.com.. .
> > > > > Hi
> > > > > I use SQLServer2000 and MS Access2000
> > > > > I have the below SQL query which will not work as a Access[/color][/color]
> PassThrough[color=green][color=darkred]
> > > > > Query, I have tried replacing the @COB with a date string but the[/color]
> > query[color=darkred]
> > > > > hangs forever.
> > > > > Can anyone direct me the right way?
> > > > > Thanks
> > > > > Kay
> > > > >
> > > > > DECLARE @cob smalldatetime
> > > > > SELECT @cob = '20060616'
> > > > >
> > > > > SELECT field1,
> > > > > field2,
> > > > > field3,
> > > > > @cob as report_date
> > > > > FROM table1
> > > > > WHERE business_date = @cob
> > > > > AND field1 in (
> > > > > SELECT field1
> > > > > FROM table1
> > > > > WHERE business_date = @cob
> > > > > AND field9 = 'N'
> > > > > AND field10 <> -1
> > > > > GROUP BY field1)
> > > > > GROUP BY field1,field2,f ield3
> > > > >
> > >[/color]
> >
> >[/color][/color]