I'm using SQL 2000 in my VB sales related application. Application will records daily consumption in the database. At the end of month, sales summary(.CSV format) is generated based on the data recorded in the database. Sales summary includes day by day total credits, profits, COGS, Grand Total etc....
The application takes much time (around 3 min for generating 3 reports) if item consumption is more..
During that time, All request to the server resulting an SQL timeout error.
Pls help me how to solve this problem..
Ive tried using secondary database for sales summary generation . But it is not work out..
' Total Days
TotalDays = DateDiff("d", FromDate, Todate)
' Create View - View_totalDisputes - for a month (fromdate and ToDate)
adoConnection1.Execute ("if exists (select * from sysobjects where id = object_id(N'[dbo].[View_totalDisputes]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[View_totalDisputes]")
adoConnection1.Execute ("create view View_totalDisputes as select sum(Price) + (Sum(Price) * Tax / 100 ) as Total , Date_Time from TblSalesReport where ItemId <> " & MyESafeId & " and Date_Time Between '" & FromDate & "' and '" & Todate & "' " & _
" group by Price ,Tax , Date_Time ")
DoEvents
TotalDays = TotalDays + 1
' Find Sum Total of each day
For i = 1 To TotalDays
DoEvents
Todate = CDate(FromDate) + 1
Todate = Format(Todate, "yyyy-mm-dd HH:MMs")
MyDays(i) = Format(FromDate, "MM-DD-YYYY")
FromDate = Format(FromDate, "yyyy-mm-dd HH:MMs")
' Days Total
Set rTot = adoConnection1.Execute("Select Sum(Total) as Tot from View_TotalDisputes where Date_Time Between '" & FromDate & "' and '" & Todate & "'")
If Not rTot.EOF Then
If Not IsNull(rTot!tot) Then
MyTotal(i) = Format(rTot!tot, "Fixed")
DoEvents
Else
MyTotal(i) = 0
Do_DoEvents
End If
DoEvents
Else
MyTotal(i) = 0
DoEvents
End If
DoEvents
' Disputes
Set rDispute = adoConnection1.Execute("Select sum(Price + ((price * tax) / 100)) as Tot from tblitemlog where ItemId <> " & MyESafeId & " and comments is Not null and TobeCharged = 0 and date_time between '" & FromDate & "' and '" & Todate & "' ")
If Not rDispute.EOF Then
If Not IsNull(rDispute!tot) Then
MyDisputes(i) = Format(rDispute!tot, "Fixed")
DoEvents
Else
MyDisputes(i) = 0
DoEvents
End If
DoEvents
Else
MyDisputes(i) = 0
DoEvents
End If
' total occupancy
'' TotOccupancy = TotOccupancy + MyOccupancy(i)
' Date changing
FromDate = CDate(FromDate) + 1
Todate = CDate(Todate) + 1
Next i
DoEvents
DoEvents
' Total Rooms in the hotel
Set rRoms = adoConnection1.Execute("Select Count(*) as Tot from TblRooms")
If Not rRoms.EOF Then
If Not IsNull(rRoms!tot) Then
TotalRooms = rRoms!tot
DoEvents
Else
TotalRooms = 0
DoEvents
End If
DoEvents
End If
DoEvents
' Total and avg. of Gross Ttoal
Set rNet = adoConnection1.Execute("Select Sum(Total) as SumTotal From View_TotalDisputes ")
If Not rNet.EOF Then
If Not IsNull(rNet!SumTotal) Then SumToal = Format(rNet!SumTotal, "Fixed")
AvgTotal = Format(SumToal / TotalDays, "Fixed")
DoEvents
End If
' 14/09/06
' Totals and Avg for Disput and profit columns
Dim GTotDispute As Double
Dim GTotProfit As Double
Dim AvgDispute As Double
Dim AvgProfit As Double
Dim inc As Integer
' G Total
For inc = 0 To TotalDays
GTotDispute = GTotDispute + Val(MyDisputes(inc))
GTotProfit = GTotProfit + Val(MyProfit(inc))
Next inc
I'm trying to learn about SQL databases and use them for my asp.net app sine I cant get the hang of MySQL in ASP. Every time I go to create a new SQL Database it saids: Would you like to place it in the App_Data folder basicly and I say yes. Then the error:Unable to find DbProivderFactory for type System.Data.SqlClient.SqlConnection
Hi all, Now i am using vs1.1 and ms sql 2000. Through a web page that i created, i got the error when the function runs. The funcation will run 9 store procedures. Also, in some store procedue, it will run other stored procedures.
But, sometimes, i got the database time out error, the error message return is General network error. Check your network documentation. I already check the query time out value which is 0. In machine.config, the execution time out is 90 which i support is quite enough. I really nervous about that. Please help me to find out the error. Regards,
I get an error when trying to update abt 9 million records in my database.All the conenction objects are closed proeprly in the finally block, An error has occured: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.What could be the reason, ? Sometimes it works fine and then it suddenly gives me this error.I have set the connect timeout to 0 ( unlimited ) in the connection string, and also in the connection tab , of the server properties i have set the query timeout to 0(unlimited) .Is there any other setting in sql server 2000 to be changed ?Thanks
I am fairly new to the SQL language so if you could help me with what I am doing wrong I would greatly appreciate it. I am using coldfusion and MSSQL I am using coldfusion to call a stored procedure.
I figure I am doing something really bad because I get a timeout error, which makes me think it is in a loop or trying to call a whole bunch of records.
What I am trying to do is display a record if the status code is M in LQSPERS or LQSMATE or LQSABUT this is how I was trying to do it.
Please help, thanks
SELECT LQSPERS_MEMBER_ID, LQSPERS_FIRST_NAME, LQSPERS_LAST_NAME, LQSPERS_FIRST_NAME, LQSPERS_AGE, LQSPERS_PROFESSION, LQSPHTO_FILE_NAME FROM LQSPERS, LQSPHTO, LQSABUT, LQSMATE WHERE (LQSPERS_MEMBER_ID IN (SELECT LQSPERS_MEMBER_ID FROM LQSPERS WHERE LQSPERS_STUS_CODE = 'M') OR LQSPERS_MEMBER_ID IN (SELECT LQSABUT_MEMBER_ID FROM LQSABUT WHERE LQSABUT_STUS_CODE = 'M') OR LQSABUT_MEMBER_ID IN (SELECT LQSMATE_MEMBER_ID FROM LQSMATE WHERE LQSMATE_STUS_CODE = 'M')) AND LQSPERS_MEMBER_ID = LQSPHTO_MEMBER_ID AND LQSPERS_MEMBER_TYPE = 'PO' AND LQSPHTO_PRIMARY_PHOTO = 'Y' ORDER BY LQSPERS_MEMBER_ID DESC
This query is running from the C++ application and gives time out error. when I run this from Query Analyser I get Msg 170, Level 15, State 1 Line 4: Incorrect syntax near '!'.
select CPV1.*, CPV2.Name, CPV2.Side, CPV2.Shares, CPV2.Price, CPV2.AllocatedTo from CurrentPortfolioViewPT CPV1, CurrentPortfolioViewPT CPV2 whereCPV2.Name like '<(bPAll ? "%" : strPortName)>' and CPV1.PortId <(!bPAll ? "!=" : (bTAll ? "<" : "="))> CPV2.PortId and CPV1.Ticker like '<(bTAll ? "%" : strTicker)>' and CPV1.Ticker = CPV2.Ticker and CPV1.Side <(bCross ? '!' : ' ')>= CPV2.Side order by CPV1.Name, CPV1.Side, CPV1.Ticker, CPV1.Acronym
I am a beginner to intermediate user of SQL (the most dangerous kind) and am receiving the error of Server: Msg 11, Level 16, State 1, Line 0 while running a query.
I have been to Microsofts website and download SP4 with the hotfix and it did not seem to help.
Hello All: I am in desparate need of assistance. We are using an application called PaperVision Enterprise which takes scanned invoices and puts them into a database for the purpose of easy reference to any scanned invoice in the db. 2 weeks ago while trying to perform a 'match and merge' of the newly scanned docs, we received a Run-time error '-2147217871 (80040e31)": Timeout expired message. After being told by the company who supports this software that they couldn't help me since it is using a MS SQL Server Table Lookup I started googling the error with minimal success. I am not very proficient with SQL so I'm kind of stuck. I have narrowed this down to an ODBC run-time error, but looking for some kind of guidance as to how I can resolve it. Any info would be greatly appreciated. We are using MS SQL Server 2000.
I am pulling in data from a source table and converting datetime columns in that table into a nvarchar(50) datatype. When I execute just this segment / container, the script runs and pulls data. When I execute the entire package, I get the following error:
[DataReader Source [1]] Error: The data type of "output column "pt_part" (54)" does not match the data type "System.String" of the source column "pt_part".
I have been going around in circles trying to identify the source of this issue but in vain. Any one has any suggestions for me?
MsgBox "Click yes when prompted to delete records" Dim sqldel As String sqldel = "DELETE tbl_physical_main.*, tbl_physical_main.[Material Number] FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not Null))" DoCmd.RunSQL sqldel, no sqldel = "DELETE tbl_noDataForTag.* FROM tbl_noDataForTag" DoCmd.RunSQL sqldel, no sqldel = "DELETE tbl_SAPmaterials.* FROM tbl_SAPmaterials" DoCmd.RunSQL sqldel, no MsgBox "All previous inventory data has been deleted and the next inventory load may continue."
End Sub
-------------------------- Run-Time Error '94' Invalid use of Null
Had a program installed in 9-07 ran fine. Tried using it last week and got
run time error 339 MSCOMCTZ.OCX or one of it's dependencies not correctly registered: a file is missing or invalid.
Have tried to uninstall and reinstall a # of times. No go. Searched in system 32 dir no extention there. Called and talk to the company and thier programer. They spent days looking at an issue on thier end. There is not an .ocx in the program. They have gotten 3 complaints on this issue all from Vista uses. Like I said it worked fine then the run time error. Must be from some kind of Microsoft update. Need the program Please help Jim
Hi, I get the below error (after clicking login in my home page, so home page works well) when I open my website for the first time after I restart my server or my client machine. " Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." From the next time onwards it is connecting without any problems. can anyone figure out what could be the problem here?
rsReport!EXTRAFOREOS = 0 ---at this point it gives the error - run-time error '2147217887(80040e21) Multiple step opertion generated errors - check each value status rsReport.Update
But if I use sql 2000 it is working fine..
So please suggest some way to execute it in sql 2005.
I installed a program that put Microsoft SQL Server on my system, Windows XP. The next day I tried to load a MSDOS Database program, Nutplus (Ultrapls), now I get a Run-Time error, R6009. The program will not load. Please help!!!!!, Thank you in advance.
What do you think of a query that generates aSystem.Data.SqlClient.SqlException when submitted via a application,but when run through QueryAnalyzer or EnterpriseManager doesn'tgenerate a error? Here are 2 examples of the query:SELECT table1.*,table2.field1,table2.field2,table2.field3 FROMtable1,table2 WHERE table1.field1 = table2.field1 ANDtable1.field2='103' ANDtable2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStampSystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.SELECT table1.*,table2.field1,table2.field2,table2.field3 FROMtable1,table2 WHERE table1.field1 = table2.field1 ANDtable1.field2='103' ANDtable2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStampSystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.I believe the error is referencing the hour portion of the timestamp.When I run these queries through QA/EM I don't get a result set ... somaybe those apps just trap the exception and ignore it. Still, seeanything wrong with the TStamp?
My application having connection string which having Server Name --Erlier it is working fine. Now IP got chaged Server name is same but now Iam unable to connect server.
Hi,There are about 30 millions records on my mssql server and I want to access 2 million of them at one time. However, when I try to access with sql command I get time out error. I want to select first 100 record and select the other 100 and so on. May I obtain this?For example;select * from tbl_Customer where name = @name_ ->time out errorSomeone has said that you can solve this problem with < cursors > but I can't find enough article. Thanks...
The database connection was ok without specify any pooling parameter, however, after I decide to put pooling parameter to the connection string. I frequently expierence "Time out expired" exception.Here is the connection string: "Pooling = True; Min Pool Size = 5; Max Pool Size = 100;Server = servername; AttachDbFilename = database_file; Integrated Security = True;"As you see, it is not much different from default value. I was thinking may be I should give a "Time out" value to the connection, will this help?
Hi, I have a problem in my .net application. I am executing a stored procedure from my .net application. The scope of this stored procedure is, 1.It should filter the records using several conditions(say it is Customer) 2.Using the filtered records, again some details are fetched(say it is transaction details) 3.For each and every transaction , Some of the details has to be updated in more than three tables, meanwhile the data to be updated is to be calculated (like tax amount, commision amount)which in turn calls two more stored procedures.
my problem is .. if there are few records like 1000 or 2000, it is working fine... but if it exceeds like say 6000 records, i am getting Time out expired error in my application . If i catch the stored procedure exectution statement in sql profiler and execute it in Sql Query analizer , the stored procedure is executing properly but it takes nearly 6 minutes. How can i solve this problem. Please suggest me if i can work with any of the following options. 1)go for web services 2)go for windows services 3)optimize the stored procedure
If any one know some better answer to solve my problem..please post it.
Public Function CalcOrderSubTotal(prmOrderID As Integer, _ Optional CallFromMacro As Boolean = True) Dim varOrderSubTotal As Integer Dim db As DAO.Database Dim rs As Recordset Dim strSQL As String
