Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with SQL Server2000 SQL in Access Pass Through Query

Discussion in 'Information Technology' started by Kay, Jul 28, 2006.

  1. Kay

    Kay
    Expand Collapse
    Guest

    Hi
    I use SQLServer2000 and MS Access2000
    I have the below SQL query which will not work as a Access PassThrough
    Query, I get the error message "Pass-Through query with ReturnsRecords
    property set to True did not return any records

    I have tried replacing the @COB with a date string but the pas through
    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Kay wrote:
    > Hi
    > I use SQLServer2000 and MS Access2000
    > I have the below SQL query which will not work as a Access PassThrough
    > Query, I get the error message "Pass-Through query with ReturnsRecords
    > property set to True did not return any records
    >
    > I have tried replacing the @COB with a date string but the pas through
    > 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


    In a passthrough you cannot have multiple statements like you have with your
    variable declaration and assignment. You get one statement that returns at most
    one result set.

    If you need multiple statements you need to create a Stored Procedure on the
    server and just use a passthrough query to call it.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     

Share This Page