No Commit at end of program processing on 2005

Hi All,
I have written some ETL software that allows the user to turn off
autocommit and then set the commit frequency. It is C++ talking via
ODBC.
We are testing on an SQL Server 2005 machine and the behavior we are
seeing is that the rows written after the last commit issued by the
program are not being reflected into the database. These are the
error messages...
It seems like 2005 is not performing a commit when the program
terminates successfully. So rows written between the last commit
issued by the program and program close are not showing up in the
database.
This seems strange as 2000 did issue a commit for these message at the
end of processing...that is...if we used autocommit turned off and
committed every say 10,000 rows...the messages that were written
after the last commmit have always been there...same on oracle and
other databases..
Is there a database level option to make a commit happen at the end of
processing? Or perhaps an option for the MSFT SQL Server ODBC driver
to commit when the connection is closed?
Thanks
Peter
www.peternolan.com
Hi Peter
"Peter Nolan" wrote:

> Hi All,
> I have written some ETL software that allows the user to turn off
> autocommit and then set the commit frequency. It is C++ talking via
> ODBC.
> We are testing on an SQL Server 2005 machine and the behavior we are
> seeing is that the rows written after the last commit issued by the
> program are not being reflected into the database. These are the
> error messages...
> It seems like 2005 is not performing a commit when the program
> terminates successfully. So rows written between the last commit
> issued by the program and program close are not showing up in the
> database.
> This seems strange as 2000 did issue a commit for these message at the
> end of processing...that is...if we used autocommit turned off and
> committed every say 10,000 rows...the messages that were written
> after the last commmit have always been there...same on oracle and
> other databases..
> Is there a database level option to make a commit happen at the end of
> processing? Or perhaps an option for the MSFT SQL Server ODBC driver
> to commit when the connection is closed?
> Thanks
> Peter
> www.peternolan.com
>
I am not sure why you are setting autocommit off!! If you have not
explicitly started a transaction or you are using implicit transaction any
data changed after the transaction (implicitly or explicitly) started will be
rolled back. If you allowed autocommit to be on and you did not explicitly
start the transaction then the data changed will be committed.
I don't think this behaviour has changed, but you it is not good practice to
assume that a feature of the driver is going be a shortcut to handling the
transactions properly.
John

No Commit at end of program processing on 2005

Hi All,
I have written some ETL software that allows the user to turn off
autocommit and then set the commit frequency. It is C++ talking via
ODBC.
We are testing on an SQL Server 2005 machine and the behavior we are
seeing is that the rows written after the last commit issued by the
program are not being reflected into the database. These are the
error messages...
It seems like 2005 is not performing a commit when the program
terminates successfully. So rows written between the last commit
issued by the program and program close are not showing up in the
database.
This seems strange as 2000 did issue a commit for these message at the
end of processing...that is...if we used autocommit turned off and
committed every say 10,000 rows...the messages that were written
after the last commmit have always been there...same on oracle and
other databases..
Is there a database level option to make a commit happen at the end of
processing? Or perhaps an option for the MSFT SQL Server ODBC driver
to commit when the connection is closed?
Thanks
Peter
www.peternolan.comHi Peter
"Peter Nolan" wrote:
> Hi All,
> I have written some ETL software that allows the user to turn off
> autocommit and then set the commit frequency. It is C++ talking via
> ODBC.
> We are testing on an SQL Server 2005 machine and the behavior we are
> seeing is that the rows written after the last commit issued by the
> program are not being reflected into the database. These are the
> error messages...
> It seems like 2005 is not performing a commit when the program
> terminates successfully. So rows written between the last commit
> issued by the program and program close are not showing up in the
> database.
> This seems strange as 2000 did issue a commit for these message at the
> end of processing...that is...if we used autocommit turned off and
> committed every say 10,000 rows...the messages that were written
> after the last commmit have always been there...same on oracle and
> other databases..
> Is there a database level option to make a commit happen at the end of
> processing? Or perhaps an option for the MSFT SQL Server ODBC driver
> to commit when the connection is closed?
> Thanks
> Peter
> www.peternolan.com
>
I am not sure why you are setting autocommit off!! If you have not
explicitly started a transaction or you are using implicit transaction any
data changed after the transaction (implicitly or explicitly) started will be
rolled back. If you allowed autocommit to be on and you did not explicitly
start the transaction then the data changed will be committed.
I don't think this behaviour has changed, but you it is not good practice to
assume that a feature of the driver is going be a shortcut to handling the
transactions properly.
Johnsql

No Commit at end of program processing on 2005

Hi All,
I have written some ETL software that allows the user to turn off
autocommit and then set the commit frequency. It is C++ talking via
ODBC.
We are testing on an SQL Server 2005 machine and the behavior we are
seeing is that the rows written after the last commit issued by the
program are not being reflected into the database. These are the
error messages...
It seems like 2005 is not performing a commit when the program
terminates successfully. So rows written between the last commit
issued by the program and program close are not showing up in the
database.
This seems strange as 2000 did issue a commit for these message at the
end of processing...that is...if we used autocommit turned off and
committed every say 10,000 rows...the messages that were written
after the last commmit have always been there...same on oracle and
other databases..
Is there a database level option to make a commit happen at the end of
processing? Or perhaps an option for the MSFT SQL Server ODBC driver
to commit when the connection is closed?
Thanks
Peter
www.peternolan.comHi Peter
"Peter Nolan" wrote:

> Hi All,
> I have written some ETL software that allows the user to turn off
> autocommit and then set the commit frequency. It is C++ talking via
> ODBC.
> We are testing on an SQL Server 2005 machine and the behavior we are
> seeing is that the rows written after the last commit issued by the
> program are not being reflected into the database. These are the
> error messages...
> It seems like 2005 is not performing a commit when the program
> terminates successfully. So rows written between the last commit
> issued by the program and program close are not showing up in the
> database.
> This seems strange as 2000 did issue a commit for these message at the
> end of processing...that is...if we used autocommit turned off and
> committed every say 10,000 rows...the messages that were written
> after the last commmit have always been there...same on oracle and
> other databases..
> Is there a database level option to make a commit happen at the end of
> processing? Or perhaps an option for the MSFT SQL Server ODBC driver
> to commit when the connection is closed?
> Thanks
> Peter
> www.peternolan.com
>
I am not sure why you are setting autocommit off!! If you have not
explicitly started a transaction or you are using implicit transaction any
data changed after the transaction (implicitly or explicitly) started will b
e
rolled back. If you allowed autocommit to be on and you did not explicitly
start the transaction then the data changed will be committed.
I don't think this behaviour has changed, but you it is not good practice to
assume that a feature of the driver is going be a shortcut to handling the
transactions properly.
John

Next_run_time and next_run_date of sysjobschedules

I have written some code to query the next_run_time and next_run_date of the sysjobschedules table. It works well with the exception that when a new schedule is created or an existing one is changed, these two columns are set to zero. They will eventually be initialized to the appropriate values but that may take a while. That means that the code will not work until then.

Is there a way to FORCE the initialization of the two columns ?How does your code looks like?
Tried initializing those 2 values in the code itself?|||The code is simply selecting the two columns into variables and then do further processing.

The problem is the two columns (next_run_date and next_run_time) of sysjobschedules table are set to zero whenever a new schedule is created or changed. You can try it yourselef.

Create a job (xxxxx) with a schedule.

select a.name, b.name,next_run_date, next_run_time from msdb..sysjobs a inner join msdb..sysjobschedules b
on a.job_id=b.job_id
where a.name='xxxxx'

and you will see those two columns are zeros.

However, after a while they will updated to the appropriate values by SQL.

My question, how do we force SQL to update these two columns immediately? It seems SQL keeps them in cache somewhere and periodically updates the sysjobschedules table.|||True, what you said is right.

I believe as soon as the job finishes the execution these values will be updated internally and only with manual updation you can set other values if required.

May refer to book 'Inside SQL Server 2000' by Kalen Daleney.

Next IsNull help required

Hi,

I have written a simple formula to identify the last address in a sequence, in a multiple address report which is sorted in alphabetical order:

If next ({address}) <> {address} then 1 else 0

This works fine, however, the very last record in the report 'should' have a value of 1 but doesn't because obviously there is no Next record.

I have therefore tried adding the following line (and variations on the same theme!) but cannot achieve what I want.

If Next ({address}) <> {address} then 1 else
If Next IsNull ({address}) then 1 else 0

I presume this is not possible as there is no Next record. Can anyone suggest a way around this?

Thanks.OnLastRecord|||Thanks. My report now works. Apologies for being a numbskull!