Category: ETL

Scribe Workbench: Conversion failed when converting date and/or time from character string.

Problem:

During an insert step in Scribe Workbench, I wanted to populate a CreatedDateTime field with the current time. This field is a datetime2 in the SQL server. So I put in NOW( ) like this:

image

But, instead of capturing a timestamp and putting it in, I got the following error in the execution log:

image

Solution:

Change NOW( ) to the following: TEXT( NOW( ), “MM-dd-YYYY hh:mm:ss”)

Like this:

image

This will take the value captured from the NOW( ) as a string and convert it into type datetime2. No more errors! 🙂

Filed under: ETL

Clean up ScribeInternal DB for Scribe Insight to reduce disk size

If the hard drive on your Scribe Insight integration server gets full, conduct the following operation to reduce the size of the ScribeInternal DB. This database will get full approximately once every 2-3 months.

Procedure:

  1. Run scribemaintenance.sql
  2. Run the following command:
    USE  master
    GO
    EXEC sp_clean_db_free_space @dbname= N'ScribeInternal';
  3. Go to the Object Explorer pane in SSMzS and right click on the database in question. Choose tasks -> shrink -> files. Change the file type option to Log, click the “Reorganize pages before releasing unused space” option, and set the value to 1 MB. Hit OK.

If this doesn’t work, check to see if your database is set up with a Full database recover model. Right click the database and go to properties. Choose Options, and check the Recover model option. Set to simple (if you can!!!), then shrink the logs.

Filed under: ETL