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
Showing posts with label software. Show all posts
Showing posts with label software. Show all posts
Friday, March 23, 2012
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
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
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
Tuesday, March 20, 2012
No "Maintenance Plans" under SQL Server 2005
Running "SQL Server Mgmt Studio"
For the software "SQL Server 2005"
Under the SERVER instance, there's Mgmt -> Maint Plans.
Under SERVER\INSTANCE, there's no Maintenance Plans.
The DBs I need to back up are under SERVER\INSTANCE.
Any idea why my server instance does not have the option?
Thanks,
ScottThere are none by default, you have to create them. Right click and say "New
Maintenance Plan" or choose the wizard.
--
Andrew J. Kelly SQL MVP
"Scott Moseman" <scmoseman@.gmail.com> wrote in message
news:1157491039.842488.194100@.m79g2000cwm.googlegroups.com...
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>|||Scott Moseman wrote:
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>
Skip the maintenance plan and use a scheduled T-SQL script to do your
backups. See
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html for a
script that will backup every database on your server, all you have to
do is schedule it to run.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not talking about it missing a maintenance plan, I'm talking
about the Management folder not even having the Maintenance
Plan subfolder under which you'd add any new plans. Adding a
maintenance plan is not a problem, but there's no place to do it.
Thanks,
Scott
Andrew J. Kelly wrote:
> There are none by default, you have to create them. Right click
> and say "New Maintenance Plan" or choose the wizard.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Moseman" <scmoseman@.gmail.com> wrote:
> > Running "SQL Server Mgmt Studio"
> > For the software "SQL Server 2005"
> >
> > Under the SERVER instance, there's Mgmt -> Maint Plans.
> > Under SERVER\INSTANCE, there's no Maintenance Plans.
> >
> > The DBs I need to back up are under SERVER\INSTANCE.
> > Any idea why my server instance does not have the option?
> >
> > Thanks,
> > Scott
> >|||Hi,
I had the same problem.
You need to install Integration Services also for that instance
SERVER\INSTANCE.
HTH
Flor
Tracy McKibben a =E9crit :
> Scott Moseman wrote:
> > Running "SQL Server Mgmt Studio"
> > For the software "SQL Server 2005"
> >
> > Under the SERVER instance, there's Mgmt -> Maint Plans.
> > Under SERVER\INSTANCE, there's no Maintenance Plans.
> >
> > The DBs I need to back up are under SERVER\INSTANCE.
> > Any idea why my server instance does not have the option?
> >
> > Thanks,
> > Scott
> >
> Skip the maintenance plan and use a scheduled T-SQL script to do your
> backups. See
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html for a
> script that will backup every database on your server, all you have to
> do is schedule it to run.
> > > -- > Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
For the software "SQL Server 2005"
Under the SERVER instance, there's Mgmt -> Maint Plans.
Under SERVER\INSTANCE, there's no Maintenance Plans.
The DBs I need to back up are under SERVER\INSTANCE.
Any idea why my server instance does not have the option?
Thanks,
ScottThere are none by default, you have to create them. Right click and say "New
Maintenance Plan" or choose the wizard.
--
Andrew J. Kelly SQL MVP
"Scott Moseman" <scmoseman@.gmail.com> wrote in message
news:1157491039.842488.194100@.m79g2000cwm.googlegroups.com...
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>|||Scott Moseman wrote:
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>
Skip the maintenance plan and use a scheduled T-SQL script to do your
backups. See
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html for a
script that will backup every database on your server, all you have to
do is schedule it to run.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not talking about it missing a maintenance plan, I'm talking
about the Management folder not even having the Maintenance
Plan subfolder under which you'd add any new plans. Adding a
maintenance plan is not a problem, but there's no place to do it.
Thanks,
Scott
Andrew J. Kelly wrote:
> There are none by default, you have to create them. Right click
> and say "New Maintenance Plan" or choose the wizard.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Moseman" <scmoseman@.gmail.com> wrote:
> > Running "SQL Server Mgmt Studio"
> > For the software "SQL Server 2005"
> >
> > Under the SERVER instance, there's Mgmt -> Maint Plans.
> > Under SERVER\INSTANCE, there's no Maintenance Plans.
> >
> > The DBs I need to back up are under SERVER\INSTANCE.
> > Any idea why my server instance does not have the option?
> >
> > Thanks,
> > Scott
> >|||Hi,
I had the same problem.
You need to install Integration Services also for that instance
SERVER\INSTANCE.
HTH
Flor
Tracy McKibben a =E9crit :
> Scott Moseman wrote:
> > Running "SQL Server Mgmt Studio"
> > For the software "SQL Server 2005"
> >
> > Under the SERVER instance, there's Mgmt -> Maint Plans.
> > Under SERVER\INSTANCE, there's no Maintenance Plans.
> >
> > The DBs I need to back up are under SERVER\INSTANCE.
> > Any idea why my server instance does not have the option?
> >
> > Thanks,
> > Scott
> >
> Skip the maintenance plan and use a scheduled T-SQL script to do your
> backups. See
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html for a
> script that will backup every database on your server, all you have to
> do is schedule it to run.
> > > -- > Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
No "Maintenance Plans" under SQL Server 2005
Running "SQL Server Mgmt Studio"
For the software "SQL Server 2005"
Under the SERVER instance, there's Mgmt -> Maint Plans.
Under SERVER\INSTANCE, there's no Maintenance Plans.
The DBs I need to back up are under SERVER\INSTANCE.
Any idea why my server instance does not have the option?
Thanks,
ScottThere are none by default, you have to create them. Right click and say "New
Maintenance Plan" or choose the wizard.
Andrew J. Kelly SQL MVP
"Scott Moseman" <scmoseman@.gmail.com> wrote in message
news:1157491039.842488.194100@.m79g2000cwm.googlegroups.com...
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>|||Scott Moseman wrote:
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>
Skip the maintenance plan and use a scheduled T-SQL script to do your
backups. See
http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html for a
script that will backup every database on your server, all you have to
do is schedule it to run.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not talking about it missing a maintenance plan, I'm talking
about the Management folder not even having the Maintenance
Plan subfolder under which you'd add any new plans. Adding a
maintenance plan is not a problem, but there's no place to do it.
Thanks,
Scott
Andrew J. Kelly wrote:[vbcol=seagreen]
> There are none by default, you have to create them. Right click
> and say "New Maintenance Plan" or choose the wizard.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Moseman" <scmoseman@.gmail.com> wrote:
>|||Hi,
I had the same problem.
You need to install Integration Services also for that instance
SERVER\INSTANCE.
HTH
Flor
Tracy McKibben a =E9crit :
> Scott Moseman wrote:
> Skip the maintenance plan and use a scheduled T-SQL script to do your
> backups. See
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html for a
> script that will backup every database on your server, all you have to
> do is schedule it to run.
>=20
>=20
> --=20
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
For the software "SQL Server 2005"
Under the SERVER instance, there's Mgmt -> Maint Plans.
Under SERVER\INSTANCE, there's no Maintenance Plans.
The DBs I need to back up are under SERVER\INSTANCE.
Any idea why my server instance does not have the option?
Thanks,
ScottThere are none by default, you have to create them. Right click and say "New
Maintenance Plan" or choose the wizard.
Andrew J. Kelly SQL MVP
"Scott Moseman" <scmoseman@.gmail.com> wrote in message
news:1157491039.842488.194100@.m79g2000cwm.googlegroups.com...
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>|||Scott Moseman wrote:
> Running "SQL Server Mgmt Studio"
> For the software "SQL Server 2005"
> Under the SERVER instance, there's Mgmt -> Maint Plans.
> Under SERVER\INSTANCE, there's no Maintenance Plans.
> The DBs I need to back up are under SERVER\INSTANCE.
> Any idea why my server instance does not have the option?
> Thanks,
> Scott
>
Skip the maintenance plan and use a scheduled T-SQL script to do your
backups. See
http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html for a
script that will backup every database on your server, all you have to
do is schedule it to run.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not talking about it missing a maintenance plan, I'm talking
about the Management folder not even having the Maintenance
Plan subfolder under which you'd add any new plans. Adding a
maintenance plan is not a problem, but there's no place to do it.
Thanks,
Scott
Andrew J. Kelly wrote:[vbcol=seagreen]
> There are none by default, you have to create them. Right click
> and say "New Maintenance Plan" or choose the wizard.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Moseman" <scmoseman@.gmail.com> wrote:
>|||Hi,
I had the same problem.
You need to install Integration Services also for that instance
SERVER\INSTANCE.
HTH
Flor
Tracy McKibben a =E9crit :
> Scott Moseman wrote:
> Skip the maintenance plan and use a scheduled T-SQL script to do your
> backups. See
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html for a
> script that will backup every database on your server, all you have to
> do is schedule it to run.
>=20
>=20
> --=20
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Subscribe to:
Posts (Atom)