none
Run-time error '-2147217887 (80040e21)'

    Question

  • Welcome

    When you export the tables from MS Access to SQLServer request some time it correctly. But after a week of proper work suddenly realize you can not query for the last days. For instance when Data_od_fu = "2014-04-04" and Data_do_fu = "2014-04-08" query is good. When I Data_od_fu = "2014-04-07" and Data_do_fu = "2014-04-08" I have a problem. In general, I have no idea what is wrong and where to look for solutions. Please pmomoc. Thank you.

    "SELECT " & _
                        "Towar.Id_towaru AS Id_towaru, " & _
                        "Towar.Nazwa AS Nazwa_towaru, " & _
                        "ROUND(SUM(Towar_wydanie.Waga),3) AS Ilosc_wydania, " & _
                        "SUM(ROUND(Towar_wydanie.Waga*Towar_wydanie.Aktualna_cena,2)) AS Wartosc_wydania " & _
                    "FROM " & _
                        "Wydanie, " & _
                        "Towar_wydanie, " & _
                        "Odbiorca, " & _
                        "Towar " & _
                    "WHERE " & _
                        "Wydanie.Id_wydania = Towar_wydanie.Id_wydania " & _
                        "AND Wydanie.Data >= '" & Data_od_fu & "' " & _
                        "AND Wydanie.Data <= '" & Data_do_fu & "' " & _
                        "AND Odbiorca.Id_odbiorcy = Wydanie.Id_odbiorcy " & _
                        "AND Odbiorca.Id_odbiorcy <> 427 " & _
                        "AND Odbiorca.Typ_odbiorcy = '" & Typ_odbiorcy_fu & "' " & _
                        "AND Towar_wydanie.Id_towaru = Towar.Id_towaru " & _
                        "AND Towar.Wedlina = " & GetIntBoolean(Wedliny_lo) & " " & _
                    "GROUP BY " & _
                        "Towar.Id_towaru, Towar.Nazwa"

    Tuesday, April 08, 2014 8:45 AM

Answers

  • "UPDATE STATISTICS Wydanie"

    Now it's super !


    • Edited by Tom366 Saturday, April 12, 2014 2:43 PM
    • Marked as answer by Tom366 Saturday, April 19, 2014 3:38 PM
    Saturday, April 12, 2014 2:41 PM

All replies

  • Best solution is to use stored procedures:

    http://technet.microsoft.com/en-us/library/ms187926.aspx

    https://www.google.com/#q=stored+procedure+site:+sqlusa.com

    You can call the stored procedure with parameters from the applications.

    Stored procedure can be tested in SS Management Studio independently from the application.

    BOL: "Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

    Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. "

    LINK: http://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Tuesday, April 08, 2014 9:32 AM
  • ALTER FUNCTION dbo.Function1
    (
    @Data_od_fu nchar(10),
    @Data_do_fu nchar(10),
    @Typ_odbiorcy_fu nchar(10)
    )
    RETURNS TABLE
    AS
    RETURN SELECT
    Towar.Id_towaru AS Id_towaru,
    Towar.Nazwa AS Nazwa_towaru,
    ROUND(SUM(Towar_wydanie.Waga),3) AS Ilosc_wydania,
    SUM(ROUND(Towar_wydanie.Waga*Towar_wydanie.Aktualna_cena,2)) AS Wartosc_wydania
                    FROM
    Wydanie,
                        Towar_wydanie,
                        Odbiorca,
                        Towar
                    WHERE
                        Wydanie.Id_wydania = Towar_wydanie.Id_wydania
                        AND Wydanie.Data >= @Data_od_fu
                        AND Wydanie.Data <= @Data_do_fu
                        AND Odbiorca.Id_odbiorcy = Wydanie.Id_odbiorcy
                        AND Odbiorca.Id_odbiorcy <> 427
                        AND Odbiorca.Typ_odbiorcy = @Typ_odbiorcy_fu
                        AND Towar_wydanie.Id_towaru = Towar.Id_towaru
                        AND Towar.Wedlina = 1
                    GROUP BY
                        Towar.Id_towaru, Towar.Nazwa

    getting:

    Running [dbo].[Function1] ( @Data_od_fu = 2014-04-10, @Data_do_fu = 2014-04-12, @Typ_odbiorcy_fu = Sklep ).

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    but for getting:

    Running [dbo].[Function1] ( @Data_od_fu = 2014-04-08, @Data_do_fu = 2014-04-12, @Typ_odbiorcy_fu = Sklep ). - IS OK !

    Thursday, April 10, 2014 11:15 AM
  • "UPDATE STATISTICS Wydanie"

    Now it's super !


    • Edited by Tom366 Saturday, April 12, 2014 2:43 PM
    • Marked as answer by Tom366 Saturday, April 19, 2014 3:38 PM
    Saturday, April 12, 2014 2:41 PM
  • Hi Tom366,

    d like to mark this issue as "Answered". That way, other community members could benefit from your sharing. Please also feel free to unmark the issue, with any new findings or concerns you may have.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support


    Thursday, April 17, 2014 1:46 AM