jwcolby
jwcolby at colbyconsulting.com
Thu Jun 24 08:11:33 CDT 2010
Mark, I designed a log stored procedure that I could pass in the dbname, tablename, operation, memo etc and have it logged to a general log table. I do have things like an error number, number of records etc passed back so it is some effort to set up initially as I have to have variables for all of those items. I designed that to be called from all of my other stored procedures (and it is) so that as I do my processes, the status of each SP that performs each step is logged. That works quite nicely. What I was doing in this case is a one off process that is iterating through the fields converting single spaces to nulls for the fields with nothing but a space or a Y. Each field is taking between 20 minutes and 1/2 hour, and I am using a cursor to iterate the fields. I just wanted the sp to print the results - time, field name and number of records - to the message window so that I could see the status as it proceeded. As we know, SQL Server doesn't do that. It appears that it can be done using the vb DLL and actually calling the DoEvents method of that lib. I haven't tried it, though I might today. John W. Colby www.ColbyConsulting.com Mark Breen wrote: > Hello John, > > I sometimes use that idea, and sometimes, i initially create a variable > named @DebugMode > > I then only output my results if Debug = 1, which is nice for when I am > finished, but know I may have to come back sometime. > > For your review of progress, you could also insert into a log table instead > of, or in addition to PRINT ing. > > I love the log table, as I can review it later and see what the hell has > been happening. > > If you log, you only have to open another windows and select * Order by > Desc and you can see the action in real time. > > Mark > > > On 23 June 2010 15:48, Heenan, Lambert > <Lambert.Heenan at chartisinsurance.com>wrote: > >> Take a look here, John.. >> >> http://www.sqlservercentral.com/articles/Advanced+Querying/doevents/784/ >> >> You'll need to register (free) if you don't already use that site. >> >> Lambert >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com [mailto: >> accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Wednesday, June 23, 2010 9:34 AM >> To: Sqlserver-Dba; Access Developers discussion and problem solving >> Subject: [AccessD] DoEvents equivalent >> >> Is there a DoEvents equivalent in SQL Server? >> >> I perform a set of update queries (dynamic SQL) in a cursor, printing a >> status for each loop of the cursor. The stuff prints but only when the >> entire thing finishes. I would like to print the results as each update >> occurs. >> >> -- >> John W. Colby >> www.ColbyConsulting.com >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >>