Schedule a job to run T-SQL 1

[fafrazh99]

I tried to create a job containing only one step. In the first time, the T-SQL script is like this:

select count(1) from transaction

The job was manually triggered and scheduled to run. It run successfully. In the second time, the script to run is like following:

use int
go
update transaction
set name = 'good',
addr1 = 'first',
transDate = null
where invalid = 0
go
delete from transaction
where tranDate between '1/1/2002' and '2/1/2002'
go

This time, the job can not be run manually or scheduled.
The error log shows me following message:

Executed as user: sa. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

It seems like my script is too long. Is there any max length I should consider here? Did anybody experience similar problem before?

By the way, I'm using SQL 2K with SP3.

Thanks for your help!

Frank

 

[m0nkey]

would you mind scripting the table out for us and posting it....

 

[tlbroadbent]

The error indicates you are trying to update a column with data that is longer than the column allows. What is the size of the name and addr1 columns? If these columns are large enough to hold the data being inserted then you should look for a trigger on the transaction table that is updating a column more data than the column can hold.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[fafrazh99]

Thanks Terry!

The interesting things is when I tried to run the same scripts in Query Analyzer. The table was updated successfully. No warning and error message.

Frank

 

[tlbroadbent]

There is an ansi_warnings setting that can be turned off in Query Analyzer. If ansi_warnings is off then the message is not and the date is truncated and inserted. You can check the setting by clicking Query | Current connection properties on the main QA menu.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[fafrazh99]

Thanks Terry for explanation! Appreciate it.