July 10, 2006 at 12:42 pm
I know that there is a thread opened under SQL Server Agent but there was really no end result in that thread. I have a similar problem, a SQL Agent Job that runs this SP, exec Prc_GetDBList, which calls another SP, Prc_DBDataImport, and that one calls the 3rd SP, Prc_PostDataImportValidation.
How this job works is this:
1. Find the DB that is qualified to be imported into the reporting server and run a backup and restore of that DB as TempSurvey DB. 2. Import data from this TempSurvey DB with Prc_ DBDataImport. 3. Validate data import, and upon success commit tran in the previous step, else rollback tran. 4. If 3 is successful, delete the TempSurvey DB and restart from step 1 again.
The job used to run beautifully without error, then a month and a half ago it was giving this error each night the job runs in SQL Server Agent:
(Message 0) Prc_DataDefinitionValidation : s123456 [SQLSTATE 01000] (Message 0) Prc_DBDataImport : s123456 [SQLSTATE 01000] (Message 0) Prc_PostDataImportValidation : s123456 [SQLSTATE 01000] (Message 0) Deleting database file 'I:\Temp\TempSurveyLog\tempsurvey.ldf'. [SQLSTATE 01000] (Message 3738) Deleting da... The step failed.
In the output file from SQL Server Agent job, it gives this message:
Deleting database file 'I:\Temp\TempSurveyLog\tempsurvey.ldf'. [SQLSTATE 01000]
Deleting database file 'I:\Temp\TempSurveyData\tempsurvey.mdf'. [SQLSTATE 01000]
Processed 2256 pages for database 'TempSurvey', file 's123455_dat' on file 1. [SQLSTATE 01000]Processed 1 pages for database 'TempSurvey', file 's123455_log' on file 1. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 2257 pages in 0.208 seconds (88.856 MB/sec). [SQLSTATE 01000]
===================================================== [SQLSTATE 01000]
Prc_DataDefinitionValidation : s123455 [SQLSTATE 01000]
Prc_DBDataImport : s123455 [SQLSTATE 01000]
Prc_PostDataImportValidation : s123455 [SQLSTATE 01000]
output ----------------------------------------------------------------------------------------------------------(null)
Deleting database file 'I:\Temp\TempSurveyLog\tempsurvey.ldf'. [SQLSTATE 01000]
Deleting database file 'I:\Temp\TempSurveyData\tempsurvey.mdf'. [SQLSTATE 01000]
Processed 1256 pages for database 'TempSurvey', file 's123456_dat' on file 1. [SQLSTATE 01000]
Processed 1 pages for database 'TempSurvey', file 's123456_log' on file 1. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 1257 pages in 0.122 seconds (84.345 MB/sec). [SQLSTATE 01000]
===================================================== [SQLSTATE 01000]
Prc_DataDefinitionValidation : s123456 [SQLSTATE 01000]
Prc_DBDataImport : s123456 [SQLSTATE 01000]
Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]
Msg 0, Sev 16: Prc_PostDataImportValidation : s123456 [SQLSTATE 01000]
All the @sql statements in the SPs are set to NVARCHAR(4000). The table column sizes are matching the original database table sizes. I tried putting PRINT throughout all three SPs wherever there's a @sql statement and run them in Query Analyzer and I get the same error as above. Which SP is it failed on? Is it failing on s123456 DB or is it failing on the next DB? I have run this DB individually with the SPs and it would run successfully without an error. I'm very new to SQL and not sure anymore what other tools I can use to troubleshoot this problem.
Any suggestion would be greatly appreciated!
Thanks in advance.
G
July 12, 2006 at 2:24 am
G
You don't provide your table definitions, but I imagine that if you try to fit nvarchar(4000) data into several columns in the same table, it is unlikely to fit. nvarchar(4000) has a data size of 8000 bytes, so it would be a badly-designed table that had more than one such column.
The first thing I would try is to change the variables in your stored procedures (they start with @) to varchar. This will halve their size at a stroke.
If you're still having problems then please post your SPs and table definitions and somebody should be able to help.
John
June 26, 2007 at 2:51 pm
As a work around I run the store procedure as a Operating System Command for SQL Server Agent, calling the store procedure using OSQL
osql -U dblogin -P dbpassword -S myservername\instance -d myDB -Q "exec sp_giving_prob"
January 11, 2008 at 9:52 am
Very simple.
Recreate the job vis QA.
This will solved the issue. Sometimes you simply cannot explain why MS products crash the way they do.
Cheers
Yohan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply