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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy