|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
VB App fails when log reaches 65536Greetings,
We purchased a VB tool that reads records from SQL Server for reporting purposes, but the tool fails, due to restriction in writing to the log file – which appears that it can only accommodate only 65536 rows only. I have limited VB experience, mostly Java background. Would you kindly help me find a robust solution to this urgent problem we are having? -- CrownMan Sounds like a variable is reachinga limit. maybe its a 16 bit integer.
Why dont you talk to the developer of the tool? AGP Show quoteHide quote "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > Greetings, > > We purchased a VB tool that reads records from SQL Server for reporting > purposes, but the tool fails, due to restriction in writing to the log > file - > which appears that it can only accommodate only 65536 rows only. > > I have limited VB experience, mostly Java background. Would you kindly > help > me find a robust solution to this urgent problem we are having? > > -- > CrownMan "AGP" <sindizzy.***@softhome.net> wrote in message Never had the need to use if it can but: Can VB6 use 16 bit unsigned news:R4Wul.21205$c45.16436@nlpi065.nbdc.sbc.com... > Sounds like a variable is reachinga limit. maybe its a 16 bit integer. > Why dont you talk to the developer of the tool? > > AGP DataType? Ivar Directly, No
-- Show quoteHide quoteRandem Systems Your Installation Specialist The Top Inno Setup Script Generator http://www.randem.com/innoscript.html Disk Read Error Press Ctl+Alt+Del to Restart http://www.randem.com/discus/messages/9402/9406.html?1236319938 "Ivar" <Ivar.ekstromer***@ntlworld.com> wrote in message news:HYXul.84509$IC4.56460@newsfe13.ams2... > > "AGP" <sindizzy.***@softhome.net> wrote in message > news:R4Wul.21205$c45.16436@nlpi065.nbdc.sbc.com... >> Sounds like a variable is reachinga limit. maybe its a 16 bit integer. >> Why dont you talk to the developer of the tool? >> >> AGP > > Never had the need to use if it can but: Can VB6 use 16 bit unsigned > DataType? > > Ivar > 1. Disable logging.
2. Stop and restart logging at regular intervals. If the tool does not provide one or other of these options then there's nothing you can do other than telling the supplier to fix it. Show quoteHide quote "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > Greetings, > > We purchased a VB tool that reads records from SQL Server for reporting > purposes, but the tool fails, due to restriction in writing to the log > file – > which appears that it can only accommodate only 65536 rows only. > > I have limited VB experience, mostly Java background. Would you kindly > help > me find a robust solution to this urgent problem we are having? > > -- > CrownMan Thanks, thanks indeed. I am stuck fixing, they won't pay the priginal
developers to fix, it was a long time ago. This is what they have: Private Sub writeToLogFile(ByVal logentry As String) Dim fn As Integer fn = FreeFile Open log_location & log_file For Append As #fn Write #fn, Now & ": " & logentry Close #fn End Sub Everything that happens with the tool gets written to the log, mostly a log of each record that gets retrieved, it gets called a lot through out the app: writeToLogFile strQuery writeToLogFile "started operation" writeToLogFile "Retrieved & RecID" Are we in need of checking the size of the file, and then roll-over, or count the number of rows in the log and roll-over. I would imagine that this is nothing new right?. I mean, there must be existing code/samples/sound techniques to be able to check the size of the log, roll-over, etc?. I wonder though, is this something that can perhaps be addressed at the OS level? Looks like this code needs some tweaks... -- Show quoteHide quoteCrownMan "James Hahn" wrote: > 1. Disable logging. > 2. Stop and restart logging at regular intervals. > > If the tool does not provide one or other of these options then there's > nothing you can do other than telling the supplier to fix it. > > "CrownMan" <Crown***@discussions.microsoft.com> wrote in message > news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > > Greetings, > > > > We purchased a VB tool that reads records from SQL Server for reporting > > purposes, but the tool fails, due to restriction in writing to the log > > file – > > which appears that it can only accommodate only 65536 rows only. > > > > I have limited VB experience, mostly Java background. Would you kindly > > help > > me find a robust solution to this urgent problem we are having? > > > > -- > > CrownMan > > CrownMan wrote:
.... > Private Sub writeToLogFile(ByVal logentry As String) That part's ok --> Dim fn As Integer > fn = FreeFile > Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn > End Sub .... > Are we in need of checking the size of the file, and then roll-over, or Not really in that manner, though, methinks.> count the number of rows in the log and roll-over. I would imagine that this > is nothing new right?. I mean, there must be existing code/samples/sound > techniques to be able to check the size of the log, roll-over, etc?. What would undoubtedly help would be to post an exact error message when the app dies--there may sufficient information available you've no provide for knowledgeable folks here to point you directly to the failing code. What is happening is that somewhere they're incrementing a 16-bit integer (a variable DIM'ed As Integer instead of As Long) and that variable is used somewhere (as an array index, maybe) and being incremented continuously until it overflows. That's the crash. You need to find that variable and turn it into a Long. > I wonder though, is this something that can perhaps be addressed at the OS No, it's the app that's dying.> level? -- There is nothing in the code you suppied that wold stop the log where you
say it stops. BTW: why would you pass a string to the routine using ByVal instead of ByRef? ByRef is more efficient. -- Show quoteHide quoteRandem Systems Your Installation Specialist The Top Inno Setup Script Generator http://www.randem.com/innoscript.html Disk Read Error Press Ctl+Alt+Del to Restart http://www.randem.com/discus/messages/9402/9406.html?1236319938 "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:44939788-21D8-4F45-9C43-360C63417218@microsoft.com... > Thanks, thanks indeed. I am stuck fixing, they won't pay the priginal > developers to fix, it was a long time ago. > > This is what they have: > > Private Sub writeToLogFile(ByVal logentry As String) > > Dim fn As Integer > fn = FreeFile > Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn > > End Sub > > > Everything that happens with the tool gets written to the log, mostly a > log > of each record that gets retrieved, it gets called a lot through out the > app: > > writeToLogFile strQuery > writeToLogFile "started operation" > writeToLogFile "Retrieved & RecID" > > Are we in need of checking the size of the file, and then roll-over, or > count the number of rows in the log and roll-over. I would imagine that > this > is nothing new right?. I mean, there must be existing code/samples/sound > techniques to be able to check the size of the log, roll-over, etc?. > > I wonder though, is this something that can perhaps be addressed at the OS > level? > > Looks like this code needs some tweaks... > > > > > -- > CrownMan > > > "James Hahn" wrote: > >> 1. Disable logging. >> 2. Stop and restart logging at regular intervals. >> >> If the tool does not provide one or other of these options then there's >> nothing you can do other than telling the supplier to fix it. >> >> "CrownMan" <Crown***@discussions.microsoft.com> wrote in message >> news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... >> > Greetings, >> > >> > We purchased a VB tool that reads records from SQL Server for reporting >> > purposes, but the tool fails, due to restriction in writing to the log >> > file - >> > which appears that it can only accommodate only 65536 rows only. >> > >> > I have limited VB experience, mostly Java background. Would you kindly >> > help >> > me find a robust solution to this urgent problem we are having? >> > >> > -- >> > CrownMan >> >> What kind of message do you get that makes you feel the problem is in the
writing of the file? -- Show quoteHide quoteRandem Systems Your Installation Specialist The Top Inno Setup Script Generator http://www.randem.com/innoscript.html Disk Read Error Press Ctl+Alt+Del to Restart http://www.randem.com/discus/messages/9402/9406.html?1236319938 "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:44939788-21D8-4F45-9C43-360C63417218@microsoft.com... > Thanks, thanks indeed. I am stuck fixing, they won't pay the priginal > developers to fix, it was a long time ago. > > This is what they have: > > Private Sub writeToLogFile(ByVal logentry As String) > > Dim fn As Integer > fn = FreeFile > Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn > > End Sub > > > Everything that happens with the tool gets written to the log, mostly a > log > of each record that gets retrieved, it gets called a lot through out the > app: > > writeToLogFile strQuery > writeToLogFile "started operation" > writeToLogFile "Retrieved & RecID" > > Are we in need of checking the size of the file, and then roll-over, or > count the number of rows in the log and roll-over. I would imagine that > this > is nothing new right?. I mean, there must be existing code/samples/sound > techniques to be able to check the size of the log, roll-over, etc?. > > I wonder though, is this something that can perhaps be addressed at the OS > level? > > Looks like this code needs some tweaks... > > > > > -- > CrownMan > > > "James Hahn" wrote: > >> 1. Disable logging. >> 2. Stop and restart logging at regular intervals. >> >> If the tool does not provide one or other of these options then there's >> nothing you can do other than telling the supplier to fix it. >> >> "CrownMan" <Crown***@discussions.microsoft.com> wrote in message >> news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... >> > Greetings, >> > >> > We purchased a VB tool that reads records from SQL Server for reporting >> > purposes, but the tool fails, due to restriction in writing to the log >> > file - >> > which appears that it can only accommodate only 65536 rows only. >> > >> > I have limited VB experience, mostly Java background. Would you kindly >> > help >> > me find a robust solution to this urgent problem we are having? >> > >> > -- >> > CrownMan >> >> "CrownMan" <Crown***@discussions.microsoft.com> wrote The only thing wrong with that code is the lacking error handling.> This is what they have: > > Private Sub writeToLogFile(ByVal logentry As String) > > Dim fn As Integer > fn = FreeFile > Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn > > End Sub It would not be the cause of your problem. When working in the IDE (the VB environment) errors pop up a messagebox and highlight the errant line of code. See if you can see the error when the program runs in the VB environment, and post back what the error number is, as well as the line that is highlighted. If you can't get it to error in the VB IDE, then add error handling and post back the error number: > Private Sub writeToLogFile(ByVal logentry As String) On Error GoTo Handler> > Dim fn As Integer > fn = FreeFile If Err.Number then MsgBox "Error " & Cstr(Err.Number), ,"Log File"> Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn Handler: > Add the handler above and compile the project. Then run it until> End Sub it errors.... LFS Wow, I am very impressed with this forum; the quality of its people and
responses. I can't beleive the number of responses and good advise, many thanks. I will run the tool on Monday (afraid can't do it remotely) and post the error, as well as add the error handling suggested. I hope that you remember to come back and stay with me on this one, point me in the right direction...it's a big problem you know... -- Show quoteHide quoteCrownMan "Larry Serflaten" wrote: > > "CrownMan" <Crown***@discussions.microsoft.com> wrote > > This is what they have: > > > > Private Sub writeToLogFile(ByVal logentry As String) > > > > Dim fn As Integer > > fn = FreeFile > > Open log_location & log_file For Append As #fn > > Write #fn, Now & ": " & logentry > > Close #fn > > > > End Sub > > > The only thing wrong with that code is the lacking error handling. > It would not be the cause of your problem. When working in > the IDE (the VB environment) errors pop up a messagebox and > highlight the errant line of code. > > See if you can see the error when the program runs in the VB > environment, and post back what the error number is, as well > as the line that is highlighted. > > If you can't get it to error in the VB IDE, then add error handling > and post back the error number: > > > Private Sub writeToLogFile(ByVal logentry As String) > > > > Dim fn As Integer > On Error GoTo Handler > > fn = FreeFile > > Open log_location & log_file For Append As #fn > > Write #fn, Now & ": " & logentry > > Close #fn > Handler: > If Err.Number then MsgBox "Error " & Cstr(Err.Number), ,"Log File" > > > > End Sub > > Add the handler above and compile the project. Then run it until > it errors.... > > LFS > > > Are you able to see how many records are in the log file? You could than
start a new log file when you reach a lower limit. Or based on how many entries are added daily you can start a new log file daily, weekly or monthly. CrownMan wrote: Show quoteHide quote > > Wow, I am very impressed with this forum; the quality of its people and > responses. I can't beleive the number of responses and good advise, many > thanks. > > I will run the tool on Monday (afraid can't do it remotely) and post the > error, as well as add the error handling suggested. > > I hope that you remember to come back and stay with me on this one, point me > in the right direction...it's a big problem you know... > > -- > CrownMan > > "Larry Serflaten" wrote: > > > > > "CrownMan" <Crown***@discussions.microsoft.com> wrote > > > This is what they have: > > > > > > Private Sub writeToLogFile(ByVal logentry As String) > > > > > > Dim fn As Integer > > > fn = FreeFile > > > Open log_location & log_file For Append As #fn > > > Write #fn, Now & ": " & logentry > > > Close #fn > > > > > > End Sub > > > > > > The only thing wrong with that code is the lacking error handling. > > It would not be the cause of your problem. When working in > > the IDE (the VB environment) errors pop up a messagebox and > > highlight the errant line of code. > > > > See if you can see the error when the program runs in the VB > > environment, and post back what the error number is, as well > > as the line that is highlighted. > > > > If you can't get it to error in the VB IDE, then add error handling > > and post back the error number: > > > > > Private Sub writeToLogFile(ByVal logentry As String) > > > > > > Dim fn As Integer > > On Error GoTo Handler > > > fn = FreeFile > > > Open log_location & log_file For Append As #fn > > > Write #fn, Now & ": " & logentry > > > Close #fn > > Handler: > > If Err.Number then MsgBox "Error " & Cstr(Err.Number), ,"Log File" > > > > > > End Sub > > > > Add the handler above and compile the project. Then run it until > > it errors.... > > > > LFS > > > > > > Look at the variable log_file. If that is being incremented somewhere else
and it is wrapping around at 65535 then the next log file that is written is trying to ovewrite an existing log file. Change the size of that variable, or implement a different means of generating a new file name. Show quoteHide quote "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:44939788-21D8-4F45-9C43-360C63417218@microsoft.com... > Thanks, thanks indeed. I am stuck fixing, they won't pay the priginal > developers to fix, it was a long time ago. > > This is what they have: > > Private Sub writeToLogFile(ByVal logentry As String) > > Dim fn As Integer > fn = FreeFile > Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn > > End Sub > > > Everything that happens with the tool gets written to the log, mostly a > log > of each record that gets retrieved, it gets called a lot through out the > app: > > writeToLogFile strQuery > writeToLogFile "started operation" > writeToLogFile "Retrieved & RecID" > > Are we in need of checking the size of the file, and then roll-over, or > count the number of rows in the log and roll-over. I would imagine that > this > is nothing new right?. I mean, there must be existing code/samples/sound > techniques to be able to check the size of the log, roll-over, etc?. > > I wonder though, is this something that can perhaps be addressed at the OS > level? > > Looks like this code needs some tweaks... > > > > > -- > CrownMan > > > "James Hahn" wrote: > >> 1. Disable logging. >> 2. Stop and restart logging at regular intervals. >> >> If the tool does not provide one or other of these options then there's >> nothing you can do other than telling the supplier to fix it. >> >> "CrownMan" <Crown***@discussions.microsoft.com> wrote in message >> news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... >> > Greetings, >> > >> > We purchased a VB tool that reads records from SQL Server for reporting >> > purposes, but the tool fails, due to restriction in writing to the log >> > file – >> > which appears that it can only accommodate only 65536 rows only. >> > >> > I have limited VB experience, mostly Java background. Would you kindly >> > help >> > me find a robust solution to this urgent problem we are having? >> > >> > -- >> > CrownMan >> >> Is there any timer in the code. Timer.interval has a limit close to that.
Show quoteHide quote "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:44939788-21D8-4F45-9C43-360C63417218@microsoft.com... > Thanks, thanks indeed. I am stuck fixing, they won't pay the priginal > developers to fix, it was a long time ago. > > This is what they have: > > Private Sub writeToLogFile(ByVal logentry As String) > > Dim fn As Integer > fn = FreeFile > Open log_location & log_file For Append As #fn > Write #fn, Now & ": " & logentry > Close #fn > > End Sub > > > Everything that happens with the tool gets written to the log, mostly a > log > of each record that gets retrieved, it gets called a lot through out the > app: > > writeToLogFile strQuery > writeToLogFile "started operation" > writeToLogFile "Retrieved & RecID" > > Are we in need of checking the size of the file, and then roll-over, or > count the number of rows in the log and roll-over. I would imagine that > this > is nothing new right?. I mean, there must be existing code/samples/sound > techniques to be able to check the size of the log, roll-over, etc?. > > I wonder though, is this something that can perhaps be addressed at the OS > level? > > Looks like this code needs some tweaks... > > > > > -- > CrownMan > > > "James Hahn" wrote: > >> 1. Disable logging. >> 2. Stop and restart logging at regular intervals. >> >> If the tool does not provide one or other of these options then there's >> nothing you can do other than telling the supplier to fix it. >> >> "CrownMan" <Crown***@discussions.microsoft.com> wrote in message >> news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... >> > Greetings, >> > >> > We purchased a VB tool that reads records from SQL Server for reporting >> > purposes, but the tool fails, due to restriction in writing to the log >> > file ¨C >> > which appears that it can only accommodate only 65536 rows only. >> > >> > I have limited VB experience, mostly Java background. Would you kindly >> > help >> > me find a robust solution to this urgent problem we are having? >> > >> > -- >> > CrownMan >> >> "CrownMan" <Crown***@discussions.microsoft.com> wrote in message Look for "65535" or "65536" in the code, it maybe hardcoded value. VB6 news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > Greetings, > > We purchased a VB tool that reads records from SQL Server for reporting > purposes, but the tool fails, due to restriction in writing to the log > file - > which appears that it can only accommodate only 65536 rows only. > > I have limited VB experience, mostly Java background. Would you kindly > help > me find a robust solution to this urgent problem we are having? doesn't have a data types that overflows or rollover at 65536. Integer has a range of -32768 to +32767. Long has a range of -2G to +2G. VB6 doesn't have unsigned 16 or 32 bits integers, so nothing ends at 64K or 4G. Also, VB6 built-in file I/O has a limit of 2GB of file sizes, even when using Append mode. When you reach that limit, it will through error 52: "Bad file name or number." If this is the case, you have to use the Windows API to append to the file, which can be 4GB+ on NTFS. In my opinion the SQL Server probably have a limit to the number of records it returns, and this is set somewhere to 65536. In MS SQL Server for instance, one could use "Select TOP 65536 ..." to limit the number of records returned to that value. The maximum value can also be set by "SET ROWCOUNT". Setting it to zero returns all rows. Uhmmm....I see, this actually makes sense. I mean, I've created 2 Gig files
programatically in Java, doing "dumps" of many documents/objects into one huge file...and I've dealt with Tomcat Server logs that grew huge (gigs) and had to be rollded over, but I had never seen a simple log file quit at that precise number. On the other hand, I kmow that the app is running in "optimized mode"...and what they did is, they checked all the options under Project -> Advanced Properties so the app would run faster (Remove Array Bound Checks, Remove Floating Point Error Checks, etc). You see, the SQL Server where we are trying to query against contains over 5 million records, but it is 7 years old...a pentium class old server that does not perform very well. So...the app creates client side cursors, goes on to do a lot of processing and finally, it begins to spit out records and writes to the log. Well, it takes like 20 minutes to process the query...and when it finally does....it quits at that number... This is the other problem I am trying to fix; the legacy SQL server. My job is to make this reporting tool run and get the records coupled with actual files that sit on yet another server (it is like an old document management system that uses sql server and files that just sit on a server. They keep track of versions on tables; each file has "properties" and the like). The only way to know where the files are is to query the database and match records to files, hence the writeToFile to create the report/log. We basically need to writeToFile 6 million rows, but at the rate the tool runs, it would take 4 months, given other variables at play. So I think I need to try to run the app concurrently, like 10 at once, if I am to do this in a reasonable ammount of time. Problem is, the sql server can't handle it. It just hangs. Now I have to split that database and try to divide and conquer... You've been awfull kind gentlemen, inspirational even - your guidance has given me optimism and a great deal of motivation. Nice to know that folks like you exist; -- Show quoteHide quoteCrownMan "Nobody" wrote: > "CrownMan" <Crown***@discussions.microsoft.com> wrote in message > news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > > Greetings, > > > > We purchased a VB tool that reads records from SQL Server for reporting > > purposes, but the tool fails, due to restriction in writing to the log > > file - > > which appears that it can only accommodate only 65536 rows only. > > > > I have limited VB experience, mostly Java background. Would you kindly > > help > > me find a robust solution to this urgent problem we are having? > > Look for "65535" or "65536" in the code, it maybe hardcoded value. VB6 > doesn't have a data types that overflows or rollover at 65536. Integer has a > range of -32768 to +32767. Long has a range of -2G to +2G. VB6 doesn't have > unsigned 16 or 32 bits integers, so nothing ends at 64K or 4G. > > Also, VB6 built-in file I/O has a limit of 2GB of file sizes, even when > using Append mode. When you reach that limit, it will through error 52: "Bad > file name or number." If this is the case, you have to use the Windows API > to append to the file, which can be 4GB+ on NTFS. > > In my opinion the SQL Server probably have a limit to the number of records > it returns, and this is set somewhere to 65536. In MS SQL Server for > instance, one could use "Select TOP 65536 ..." to limit the number of > records returned to that value. The maximum value can also be set by "SET > ROWCOUNT". Setting it to zero returns all rows. > > > > CrownMan wrote:
> Uhmmm....I see, this actually makes sense. I mean, I've created 2 Gig files I think you're focusing in the wrong spot likely. I think it's more > programatically in Java, doing "dumps" of many documents/objects into one > huge file...and I've dealt with Tomcat Server logs that grew huge (gigs) and > had to be rollded over, but I had never seen a simple log file quit at that > precise number. likely as "nobody" pointed out there's another limit being reached. I did make a mistake in that I assigned rollover to a 16-bit integer at 64K instead of for a signed integer but I'm in agreement it's in something more similar to what is suggested in his reply and it's simply showing up as that number of records has been written in the log; not the logging function itself that has anything to do with the actual failure--in other words, it's only the messenger. > On the other hand, I kmow that the app is running in "optimized mode"...and Again, almost certainly wrong place to look to try to assign blame. You > what they did is, they checked all the options under Project -> Advanced > Properties so the app would run faster (Remove Array Bound Checks, Remove > Floating Point Error Checks, etc). ... might be well served, however, to recompile and reactivate the bounds checks specifically just in case. Other than in some coded upper limit as suggested, the possibility exists that there's an unsigned 16-bit integer in a 3rd party extension that is still overflowing but as noted VB doesn't have an unsigned 16-bit value. --
Show quote
Hide quote
"CrownMan" <Crown***@discussions.microsoft.com> wrote in message I don't think you have a 2G limit problem right now, but it could have news:6C27011E-86F3-461E-BA12-90908DBD327B@microsoft.com... > Uhmmm....I see, this actually makes sense. I mean, I've created 2 Gig > files > programatically in Java, doing "dumps" of many documents/objects into one > huge file...and I've dealt with Tomcat Server logs that grew huge (gigs) > and > had to be rollded over, but I had never seen a simple log file quit at > that > precise number. > > On the other hand, I kmow that the app is running in "optimized > mode"...and > what they did is, they checked all the options under Project -> Advanced > Properties so the app would run faster (Remove Array Bound Checks, Remove > Floating Point Error Checks, etc). You see, the SQL Server where we are > trying to query against contains over 5 million records, but it is 7 years > old...a pentium class old server that does not perform very well. > So...the > app creates client side cursors, goes on to do a lot of processing and > finally, it begins to spit out records and writes to the log. Well, it > takes > like 20 minutes to process the query...and when it finally does....it > quits > at that number... > > This is the other problem I am trying to fix; the legacy SQL server. My > job > is to make this reporting tool run and get the records coupled with actual > files that sit on yet another server (it is like an old document > management > system that uses sql server and files that just sit on a server. They > keep > track of versions on tables; each file has "properties" and the like). > The > only way to know where the files are is to query the database and match > records to files, hence the writeToFile to create the report/log. We > basically need to writeToFile 6 million rows, but at the rate the tool > runs, > it would take 4 months, given other variables at play. > > So I think I need to try to run the app concurrently, like 10 at once, if > I > am to do this in a reasonable ammount of time. Problem is, the sql server > can't handle it. It just hangs. Now I have to split that database and > try > to divide and conquer... > > You've been awfull kind gentlemen, inspirational even - your guidance has > given me optimism and a great deal of motivation. Nice to know that folks > like you exist; > -- > CrownMan effect later. Another thing to check out is to see if the tool closes handles to things that it opens. Windows in some cases limits the number of handles a given process can open to the SAME OBJECT, and I found that this number is around 65536 handles for XP, which is too handles to the same object. This is easy to verify. Look at the total handles in Task Manager and run the tool and see if the total handles goes up by about 65536, if so, then the software is not closing handles somewhere. To view the handles per process, use View-->Select Column when you are viewing the Processes tab. Search for the following functions and make sure that they are being closed: FindFirstFile FindFirstFileEx RegOpenKey RegCreateKey RegCreateKeyEx A runtime error could lead to a branch that doesn't go through the closing handle code, so make sure that you close valid handles. Example: FindClose hFind hFind = 0 =?Utf-8?B?Q3Jvd25NYW4=?= <Crown***@discussions.microsoft.com> wrote in
news:6C27011E-86F3-461E-BA12-90908DBD327B@microsoft.com: Are you sure it's creating a client-side cursor ?> see, the SQL Server where we are trying to query against contains over > 5 million records, but it is 7 years old...a pentium class old server > that does not perform very well. So...the app creates client side > cursors, goes on to do a lot of processing and finally, it begins to > spit out records and writes to the log. Well, it takes like 20 > minutes to process the query...and when it finally does....it quits > at that number... I haven't done a database app in a while, but IIRC, when a client-side cursor is used, the db server does nothing except hand off the entire db to the client program and then the client app generates the query locally. That means that if the query resultant set is let's say 10,000 records, the client still gets handed all 5 million records over the network, which could be a problem with a slow connection. To the contrary, with a server-side cursor, the SQLServer will run the query and hand over only the 10,000 records to the client. Maybe it's time to spend $200 to update the SQL server's MB,CPU and RAM. You can get a motherboard, near 3Ghz dual core AMD CPU, and 2 Gigs of RAM for that much moolah.
Show quote
Hide quote
"DanS" <t.h.i.s.n.t.h.a.t@r.o.a.d.r.u.n.n.e.r.c.o.m> wrote in message I don't think that's right, Dan. If you use a client side cursor and the news:Xns9BD2C41C38926thisnthatroadrunnern@85.214.105.209... > =?Utf-8?B?Q3Jvd25NYW4=?= <Crown***@discussions.microsoft.com> wrote in > news:6C27011E-86F3-461E-BA12-90908DBD327B@microsoft.com: > >> see, the SQL Server where we are trying to query against contains over >> 5 million records, but it is 7 years old...a pentium class old server >> that does not perform very well. So...the app creates client side >> cursors, goes on to do a lot of processing and finally, it begins to >> spit out records and writes to the log. Well, it takes like 20 >> minutes to process the query...and when it finally does....it quits >> at that number... > > Are you sure it's creating a client-side cursor ? > > I haven't done a database app in a while, but IIRC, when a client-side > cursor is used, the db server does nothing except hand off the entire db > to > the client program and then the client app generates the query locally. > > That means that if the query resultant set is let's say 10,000 records, > the > client still gets handed all 5 million records over the network, which > could be a problem with a slow connection. > > To the contrary, with a server-side cursor, the SQLServer will run the > query and hand over only the 10,000 records to the client. > > Maybe it's time to spend $200 to update the SQL server's MB,CPU and RAM. > You can get a motherboard, near 3Ghz dual core AMD CPU, and 2 Gigs of RAM > for that much moolah. query retrieves 10,000 records, all 10,000 records will be retrieved before the recordset open will return. If you use a server side cursor, only a subset of the records are retrieved and more are retrieved as necessary as you move through the recordset. With a client side cursor you will usually get a valid Recordcount because all records have been retrieved and a valid count is known at the client side. That is not true for a server side cursor. -- Al Reid
Show quote
Hide quote
"Al Reid" <ar***@reidDASHhome.com> wrote in After a little research, I've concluded my info was a bit off-base.news:O0PpbLCqJHA.2124@TK2MSFTNGP05.phx.gbl: > > "DanS" <t.h.i.s.n.t.h.a.t@r.o.a.d.r.u.n.n.e.r.c.o.m> wrote in message > news:Xns9BD2C41C38926thisnthatroadrunnern@85.214.105.209... >> =?Utf-8?B?Q3Jvd25NYW4=?= <Crown***@discussions.microsoft.com> wrote >> in news:6C27011E-86F3-461E-BA12-90908DBD327B@microsoft.com: >> >>> see, the SQL Server where we are trying to query against contains >>> over 5 million records, but it is 7 years old...a pentium class old >>> server that does not perform very well. So...the app creates client >>> side cursors, goes on to do a lot of processing and finally, it >>> begins to spit out records and writes to the log. Well, it takes >>> like 20 minutes to process the query...and when it finally >>> does....it quits at that number... >> >> Are you sure it's creating a client-side cursor ? >> >> I haven't done a database app in a while, but IIRC, when a >> client-side cursor is used, the db server does nothing except hand >> off the entire db to >> the client program and then the client app generates the query >> locally. >> >> That means that if the query resultant set is let's say 10,000 >> records, the >> client still gets handed all 5 million records over the network, >> which could be a problem with a slow connection. >> >> To the contrary, with a server-side cursor, the SQLServer will run >> the query and hand over only the 10,000 records to the client. >> >> Maybe it's time to spend $200 to update the SQL server's MB,CPU and >> RAM. You can get a motherboard, near 3Ghz dual core AMD CPU, and 2 >> Gigs of RAM for that much moolah. > > I don't think that's right, Dan. If you use a client side cursor and > the query retrieves 10,000 records, all 10,000 records will be > retrieved before the recordset open will return. If you use a server > side cursor, only a subset of the records are retrieved and more are > retrieved as necessary as you move through the recordset. With a > client side cursor you will usually get a valid Recordcount because > all records have been retrieved and a valid count is known at the > client side. That is not true for a server side cursor. ......but that doesn't mean the server shouldn't be updated! Long-shot-off-the-wall
Is Excel involved? I guess you would have said and only mention because 65536 is the rows limit in pre '2007 that often bites SQL reporting stuff. Regards, Peter T Show quoteHide quote "CrownMan" <Crown***@discussions.microsoft.com> wrote in message news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > Greetings, > > We purchased a VB tool that reads records from SQL Server for reporting > purposes, but the tool fails, due to restriction in writing to the log > file - > which appears that it can only accommodate only 65536 rows only. > > I have limited VB experience, mostly Java background. Would you kindly > help > me find a robust solution to this urgent problem we are having? > > -- > CrownMan Mr. Peter T,
You were right, in a way. Get this: the VB.exe tool/app would fail after many hours of processing and the person running the tool (which was not me at the time), actually took the log and copy/pasted into Excell to try to sort it out, or whatever. She then, observed that Excell produced the error that I, in turn, posted here. I wasn't informed about this, spent hours talking to the DBA's, Sys Admins, trying to get some logs; some hint, somewhere. None were found....until finally, the tool failed again. Thanks to the folks that helped me out above, I added the additional ErrorHandlers and caught this execption: The error in the VB MsgBox: "Permission Denied" All I could find in MSDN Libs that come close to what I am doing deals with SQL Server 6.5, having a problem with procedures inside cursors that try to update something, not having SA priviledges. Thing is, this code is not supposed to update anything, or is there stuff in memory that happens...that is also subject to security?. What the heck? -- Show quoteHide quoteCrownMan "Peter T" wrote: > Long-shot-off-the-wall > Is Excel involved? > > I guess you would have said and only mention because 65536 is the rows limit > in pre '2007 that often bites SQL reporting stuff. > > Regards, > Peter T > > > "CrownMan" <Crown***@discussions.microsoft.com> wrote in message > news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > > Greetings, > > > > We purchased a VB tool that reads records from SQL Server for reporting > > purposes, but the tool fails, due to restriction in writing to the log > > file - > > which appears that it can only accommodate only 65536 rows only. > > > > I have limited VB experience, mostly Java background. Would you kindly > > help > > me find a robust solution to this urgent problem we are having? > > > > -- > > CrownMan > > > Mr. Peter T,
You were right, in a way. Get this: the VB.exe tool/app would fail after many hours of processing and the person running the tool (which was not me at the time), actually took the log and copy/pasted into Excell to try to sort it out, or whatever. She then, observed that Excell produced the error that I, in turn, posted here. I wasn't informed about this, spent hours talking to the DBA's, Sys Admins, trying to get some logs; some hint, somewhere. None were found....until finally, the tool failed again. Thanks to all the folks that helped me out above, I added the additional ErrorHandlers and caught this execption: The error in the VB MsgBox: "Permission Denied" All I could find in MSDN Libs that come close to what I am doing deals with SQL Server 6.5, having a problem with procedures inside cursors that try to update something, not having SA priviledges. Thing is, this code is not supposed to update anything, or is there stuff in memory that happens...that is also subject to security?. What the heck? I still haven't changed to ByRef instead of ByVal, will try all the suggestions and again, God bless your kindness - thanks a ton. -- Show quoteHide quoteCrownMan "Peter T" wrote: > Long-shot-off-the-wall > Is Excel involved? > > I guess you would have said and only mention because 65536 is the rows limit > in pre '2007 that often bites SQL reporting stuff. > > Regards, > Peter T > > > "CrownMan" <Crown***@discussions.microsoft.com> wrote in message > news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > > Greetings, > > > > We purchased a VB tool that reads records from SQL Server for reporting > > purposes, but the tool fails, due to restriction in writing to the log > > file - > > which appears that it can only accommodate only 65536 rows only. > > > > I have limited VB experience, mostly Java background. Would you kindly > > help > > me find a robust solution to this urgent problem we are having? > > > > -- > > CrownMan > > > I want to thank everybody who responded to this question. Every single one
of the responses taught me a heck of a lot and helped me deal with this problem much more effectively. Still not sure what happened to the job I left running yesterday, it was still going when I left. The app was not releasing open handles, so I reboot before every job...using BeRef, rolling over log at specified file size...everything you all suggested I did. Looks good, so far, so good... Thanks! I hope to repay someday, somehow... -- Show quoteHide quoteCrownMan "CrownMan" wrote: > Mr. Peter T, > You were right, in a way. Get this: the VB.exe tool/app would fail after > many hours of processing and the person running the tool (which was not me at > the time), actually took the log and copy/pasted into Excell to try to sort > it out, or whatever. She then, observed that Excell produced the error that > I, in turn, posted here. I wasn't informed about this, spent hours talking > to the DBA's, Sys Admins, trying to get some logs; some hint, somewhere. > None were found....until finally, the tool failed again. > > Thanks to all the folks that helped me out above, I added the additional > ErrorHandlers and caught this execption: > > The error in the VB MsgBox: "Permission Denied" > > All I could find in MSDN Libs that come close to what I am doing deals with > SQL Server 6.5, having a problem with procedures inside cursors that try to > update something, not having SA priviledges. Thing is, this code is not > supposed to update anything, or is there stuff in memory that happens...that > is also subject to security?. > > What the heck? I still haven't changed to ByRef instead of ByVal, will try > all the suggestions and again, God bless your kindness - thanks a ton. > > -- > CrownMan > > > "Peter T" wrote: > > > Long-shot-off-the-wall > > Is Excel involved? > > > > I guess you would have said and only mention because 65536 is the rows limit > > in pre '2007 that often bites SQL reporting stuff. > > > > Regards, > > Peter T > > > > > > "CrownMan" <Crown***@discussions.microsoft.com> wrote in message > > news:04534F9B-9CB0-428D-8100-C1BEDFF1D031@microsoft.com... > > > Greetings, > > > > > > We purchased a VB tool that reads records from SQL Server for reporting > > > purposes, but the tool fails, due to restriction in writing to the log > > > file - > > > which appears that it can only accommodate only 65536 rows only. > > > > > > I have limited VB experience, mostly Java background. Would you kindly > > > help > > > me find a robust solution to this urgent problem we are having? > > > > > > -- > > > CrownMan > > > > > > CrownMan wrote:
> You were right, in a way. Get this: the VB.exe tool/app would fail after Her name didn't happen to be Kathy, did it?> many hours of processing and the person running the tool (which was not me at > the time), actually took the log and copy/pasted into Excell to try to sort > it out, or whatever. She then, observed that Excell produced the error that > I, in turn, posted here. "Karl E. Peterson" <k***@mvps.org> wrote Where do you get that mean streak from???> > You were right, in a way. Get this: the VB.exe tool/app would fail after > > many hours of processing and the person running the tool (which was not me at > > the time), actually took the log and copy/pasted into Excell to try to sort > > it out, or whatever. She then, observed that Excell produced the error that > > I, in turn, posted here. > > Her name didn't happen to be Kathy, did it? <gd&r> LFS Larry Serflaten wrote:
> "Karl E. Peterson" <k***@mvps.org> wrote Typical male logic... <harumph>>> > You were right, in a way. Get this: the VB.exe tool/app would fail after >> > many hours of processing and the person running the tool (which was not me at >> > the time), actually took the log and copy/pasted into Excell to try to sort >> > it out, or whatever. She then, observed that Excell produced the error that >> > I, in turn, posted here. >> >> Her name didn't happen to be Kathy, did it? > > Where do you get that mean streak from???  
Other interesting topics
Internal String Visibility
Help with Binary Compatibility Fonts HOW IS Memory Used by a VB App vb.net executing on (OT) false positives on my files Can I loop through only SELECTED items in a multi-select FileListBox? Should I distribute Winsock? moving controls around on a form How to preview text file like DOS command TYPE |
|||||||||||||||||||||||
