Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    ODBC call failed to SQL Server (Access 2003 SP2)

    I keep getting this error but only after i deleted a column from this particular query.
    The query is:

    SELECT Script.sdrugcode, Script.sdrugdesc, drug.dpacksize, Mid([dformulary],3) AS Form, drug.dmanfcode, Left([dpdenumber],3) & " " & Right([dpdenumber],3) AS Symbion, Sum(Script.sqty) AS SumOfsqty, IIf(Fix(Sum([sqty])/[dpacksize])<(Sum([sqty])/[dpacksize]),Fix(Sum([sqty])/[dpacksize])+1,Fix(Sum([sqty])/[dpacksize])) AS Packs, drug.dwsprice, [packs]*[dwsprice] AS Total, Script.Server
    FROM Script INNER JOIN drug ON (Script.sdrugcode = drug.dcode) AND (Script.Server = drug.Server)
    WHERE (((Script.sdispdate) Between [forms]![frm Drug Usage]![DateFrom] And [forms]![frm Drug Usage]![DateTo]) AND ((Script.sstatus) In ("A","O","H"))) OR (((Script.sdispdate) Between [forms]![frm Drug Usage]![DateFrom] And [forms]![frm Drug Usage]![DateTo]) AND ((Script.sstatus) In ("A","O","H")))
    GROUP BY Script.sdrugcode, Script.sdrugdesc, drug.dpacksize, Mid([dformulary],3), drug.dmanfcode, Left([dpdenumber],3) & " " & Right([dpdenumber],3), drug.dwsprice, Script.Server
    HAVING (((Script.sdrugcode) Like "BLNK*") AND ((Script.Server)=[forms]![frm Drug Usage]![Server])) OR (((Script.sdrugcode) Like "BLNK*") AND (([forms]![frm Drug Usage]![Server]) Is Null))
    ORDER BY Script.sdrugdesc;

    Both tables are in SQL Server 2005.

    If I take out the column Symbion I get the error. Any ideas??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed to SQL Server (Access 2003 SP2)

    Did you remove the column in design view or from the SQL statement?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ODBC call failed to SQL Server (Access 2003 SP2)

    In design view.

    It happens instantly i run the query so it's not the timeout period.

    If i put the column back it works fine.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed to SQL Server (Access 2003 SP2)

    Sorry, I don't know what could cause this error.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ODBC call failed to SQL Server (Access 2003 SP2)

    I solved it, if i replace

    Mid([dformulary],3)
    with
    nz(Mid([dformulary],3),"")

    it seems to be fine.
    The field in SQL Server is 3 spaces and has been defined as a Char(3).

    I guess i'll put that down to an idiosychrasy (do you spell it like that). of the ODBC driver..

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed to SQL Server (Access 2003 SP2)

    Glad you solved it, but I still don't see how changing the definition of Form would make removing the Symbion column OK...

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ODBC call failed to SQL Server (Access 2003 SP2)

    You are surprised, well i was flabbergasted at what was happening.
    I decided to imethodically delete ooe column after another to see if i could locate the problem.

    I thought to put the Nz function on it and see what it would do. Pure fluke of course.

    Anyway it's one for the future.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •