Job failure

I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc
Is the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc
|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.

>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>
|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...[vbcol=seagreen]
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> changed recently? Are the
> message
|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
simple[vbcol=seagreen]
stored[vbcol=seagreen]
database.table[vbcol=seagreen]
at
>
>.
>

Job Failure

I have a job that runs stored procedures. I ran the stored
procedures in the correct order myself and they ran fine.
But when I try to run them in a job the job fails
immediately. I tried changing the user for the job so
that it matches the creator of the database and stored
procedures, and the job still failed. So then I wrote the
output of the job to a file, and this is the error that is
happening.
Msg 7399, Sev 16: OLE DB provider 'SQLOLEDB' reported an
error. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Does anyone have any ideas?
Thank you for your assistance,
AdamIs the job owner sysadmin? Are you accessing linked server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <anonymous@.discussions.microsoft.com> wrote in message news:fed501c411be$81b90020$a6
01280a@.phx.gbl...
> I have a job that runs stored procedures. I ran the stored
> procedures in the correct order myself and they ran fine.
> But when I try to run them in a job the job fails
> immediately. I tried changing the user for the job so
> that it matches the creator of the database and stored
> procedures, and the job still failed. So then I wrote the
> output of the job to a file, and this is the error that is
> happening.
> Msg 7399, Sev 16: OLE DB provider 'SQLOLEDB' reported an
> error. [SQLSTATE 42000]
> Msg 7312, Sev 16: [SQLSTATE 01000]
> Does anyone have any ideas?
> Thank you for your assistance,
> Adam|||Yes, I am accessing several linked servers. I have tried
running the job with the owner as sysadmin and as another
user. Both fail.
>--Original Message--
>Is the job owner sysadmin? Are you accessing linked
server?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Adam" <anonymous@.discussions.microsoft.com> wrote in
message news:fed501c411be$81b90020$a601280a@.phx.gbl...
stored
fine.
the
is
>
>.
>|||Do you get the same error regardless of which login owns the job?
You will get problems is the job owner isn't sysadmin, as Agent then tries t
o emulate the job owner's login's
user name in the database using the SETUSER command. And after executing SET
USER, you are not allowed to do
operations at the server level. accessing linked server is a server (long) l
evel operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:12d7401c411df$be50c4c0$a401280a
@.phx.gbl...
> Yes, I am accessing several linked servers. I have tried
> running the job with the owner as sysadmin and as another
> user. Both fail.
> server?
> message news:fed501c411be$81b90020$a601280a@.phx.gbl...
> stored
> fine.
> the
> is|||Yes, I have tried running the job as the database's owner
and as the 'sa' system account. Both fail, yet both are
set up to use a valid account on the linked servers. I
have this same setup on another server linked to the same
servers, and it works just fine.
>--Original Message--
>Do you get the same error regardless of which login owns
the job?
>You will get problems is the job owner isn't sysadmin, as
Agent then tries to emulate the job owner's login's
>user name in the database using the SETUSER command. And
after executing SETUSER, you are not allowed to do
>operations at the server level. accessing linked server
is a server (long) level operation.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
><anonymous@.discussions.microsoft.com> wrote in message
news:12d7401c411df$be50c4c0$a401280a@.phx
.gbl...
tried
another
so
stored
wrote
that
reported an
>
>.
>|||I see... There goes my theory. I'm afraid I'm out of ideas, then. If you hav
e searched KB and are on current
service pack, and you don't get other suggestions here, I suppose its MS PSS
time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <anonymous@.discussions.microsoft.com> wrote in message news:1339b01c411ec$094cd200$a
101280a@.phx.gbl...
> Yes, I have tried running the job as the database's owner
> and as the 'sa' system account. Both fail, yet both are
> set up to use a valid account on the linked servers. I
> have this same setup on another server linked to the same
> servers, and it works just fine.
> the job?
> Agent then tries to emulate the job owner's login's
> after executing SETUSER, you are not allowed to do
> is a server (long) level operation.
> news:12d7401c411df$be50c4c0$a401280a@.phx
.gbl...
> tried
> another
> so
> stored
> wrote
> that
> reported an

Job failure

I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx
.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.

>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:244ad01c45f4f$3aef6880$a501280a@.phx
.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx
.gbl...[vbcol=seagreen]
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
>
> changed recently? Are the
> message|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2415501c45f52$18baf880$a301280a@.phx
.gbl...
simple[vbcol=seagreen]
stored[vbcol=seagreen]
database.table[vbcol=seagreen]
at[vbcol=seagreen]
>
>.
>

JDBC Issue with Varchar

OK..Ive successfully connected to sql server and can execute stored
procedures...if the result set is an integer it prints the result set
but has a bunch of errors:
Execute: com.microsoft.jdbc.base.BaseResultSet@.fc9944
Got from result set: 12345
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Invalid parameter binding(s).
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at
com.microsoft.jdbc.base.BaseCallableStatement.getA ndValidateOutParameter(Unknown
Source)
at com.microsoft.jdbc.base.BaseCallableStatement.getS tring(Unknown
Source)
at JDBC_Test.<init>(JDBC_Test.java:24)
at JDBC_Test.main(JDBC_Test.java:31)
If the result set returns a varchar I get:
Execute: com.microsoft.jdbc.base.BaseResultSet@.fc9944
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Value
can not be converted to requested type.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseData.getInteger(Unknow n Source)
at com.microsoft.jdbc.base.BaseResultSet.getInt(Unkno wn Source)
at JDBC_Test.<init>(JDBC_Test.java:22)
at JDBC_Test.main(JDBC_Test.java:31)
Heres my code:
import java.sql.*;
public class JDBC_Test {
private String msDbUrl =
"jdbc:microsoft:sqlserver://localhost:1433;databaseName=KB;selectMethod=cursor ;";
private java.sql.Connection mcDbAccess;
private CallableStatement msProcedure;
public JDBC_Test() {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
mcDbAccess = java.sql.DriverManager.getConnection( msDbUrl,
"kbuser", "password" );
msProcedure = mcDbAccess.prepareCall("{call STP_test(?)
}");
msProcedure.setInt( 1, 1);
ResultSet lrsReturn = null;
System.out.println( "Execute: " + (lrsReturn =
msProcedure.executeQuery() ) );
while( lrsReturn.next() ) {
System.out.println( "Got from result set: " +
lrsReturn.getInt( 1 ) );
}
System.out.println( "Got from stored procedure: " +
msProcedure.getString( 1 ) );
} catch( Throwable e ) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new JDBC_Test();
}
}
Jimbo wrote:
> OK..Ive successfully connected to sql server and can execute stored
> procedures...if the result set is an integer it prints the result set
> but has a bunch of errors:
Ok, and what were you expecting? When an ResultSet containing an
integer is returned, you process that and then you try to get a String
output parameter, which you did not declare as output parameter in the
first place (and probably it isn't a String either, as you pass an
integer as input value for that parameter).
In the second case your procedure returns a ResultSet containing a
character value and you try to read it using getInt()?
Alin.
|||I fixed it...sorry about all the questions..Im still learning this
stuff..thanks for all your help..now that I got the recordset
object...do you have a good link that shows me how to pass that
recordset object to a bean so I can view it in a jsp?
thanks
-Jim