Category Archives: SQL Server

Excel Source Output. The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

I came across this error when I was attempting to import data from an Excel spreadsheet into a SQL database using SQL Server Integration Services (SSIS) 2005.  The error occurs because by default the Excel driver only reads the first eight rows of the spreadsheet when it determines the column data type and data length.

If you have a spreadsheet where the first eight rows aren’t representative of the data in the spreadsheet, you can increase the number of rows that are sampled by editing the TypeGuessRows value under the following registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

See here for more details http://technet.microsoft.com/en-us/library/ms141683(v=sql.90).aspx.

Advertisements

The case of the failed SQL backup

I enabled a maintenance plan on a SQL server which creates a full backup of all databases.  When checking the job history I noticed that the job had failed.  Upon further investigation I could see that although the job had failed, all the databases had been backed up.  Opening the job log revealled the following error:

Failed:(-1073548784) Executing the query “BACKUP DATABASE [master] TO  DISK = N’I:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\master\\master_backup_201010260000.bak’ WITH NOFORMAT, NOINIT,  NAME = N’master_backup_20101026000019′, SKIP, REWIND, NOUNLOAD,  STATS = 10
” failed with the following error: “Transaction (Process ID 580) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.

I checked the SQL job schedule and noticed that another “Cleanup History” maintenance plan was set to run at the same time.  In order for this maintenance plan to cleanup the job history is needs to access MSDB, which caused the deadlock.  Re-scheduling the Full Backup plan to run 5 minutes later cured the problem