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,field3

[Terry Kreft]

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(smalldatetime, '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" <ka******@yahoo.co.uk> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

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,field3

[KayC]

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 conversion
properly.

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

DECLARE @cob smalldatetime

SELECT @cob = convert(smalldatetime, '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" <ka******@yahoo.co.uk> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

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,field3

[Terry Kreft]

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(smalldatetime, '20060616', 112)
Change this to

SET NOCOUNT ON
SELECT @cob = convert(smalldatetime, '20060616', 112)
And then at the end put
SET NOCOUNT OFF

--

Terry Kreft
"KayC" <ka******@yahoo.co.uk> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

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 conversion properly.

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

DECLARE @cob smalldatetime

SELECT @cob = convert(smalldatetime, '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" <ka******@yahoo.co.uk> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

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,field3

[Terry Kreft]

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,field3

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" <te*********@mps.co.uk> wrote in message
news:zh********************@karoo.co.uk...

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(smalldatetime, '20060616', 112)
Change this to

SET NOCOUNT ON
SELECT @cob = convert(smalldatetime, '20060616', 112)
And then at the end put
SET NOCOUNT OFF

--

Terry Kreft
"KayC" <ka******@yahoo.co.uk> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

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 conversion properly.

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

DECLARE @cob smalldatetime

SELECT @cob = convert(smalldatetime, '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" <ka******@yahoo.co.uk> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...
> 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,field3
>

[KayC]

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:

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,field3

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" <te*********@mps.co.uk> wrote in message
news:zh********************@karoo.co.uk...

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(smalldatetime, '20060616', 112)
Change this to

SET NOCOUNT ON
SELECT @cob = convert(smalldatetime, '20060616', 112)
And then at the end put
SET NOCOUNT OFF

--

Terry Kreft
"KayC" <ka******@yahoo.co.uk> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

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

conversion

> properly.
>
> Assuming your date is in the format yyyymmdd then you would do something > like:-
>
> DECLARE @cob smalldatetime
>
> SELECT @cob = convert(smalldatetime, '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" <ka******@yahoo.co.uk> wrote in message
> news:11**********************@g10g2000cwb.googlegr oups.com...
> > 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,field3
> >