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 " & _
Best solution is to use stored procedures:
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. "
Kalman Toth Database & OLAP Architect Free T-SQL Scripts
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
- Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator Wednesday, April 09, 2014 7:24 AM
ALTER FUNCTION dbo.Function1
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
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
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 !
- Edited by Sofiya LiMicrosoft contingent staff, Moderator Thursday, April 17, 2014 1:47 AM