sql server job puzzle

 job called c# exe

the job failed but not in the logs

the answer - the bug was in the finally block - no error handling

Crazy date stuff with system read range

 if your format is mm/dd/yyy system read range will read it correctly 

if its dd-MMM-yyyy it will read an excel number

bad bug

keep formatting in read range

 will not read hidden columns

 

Ideas to deal with Excel Hanging

 

Problem

if you use excel scope activity, excel is supposed to close after the scope is closed. It takes a while for excel to close. In windows 10 and 0365 it takes a very long time to close. When reopening excel, excel can hang.

 

Solutions

 

Kill

Before each scope make sure to kill excel processes

Issue – the kill doesn’t always work

 

Keep scope alive

Open excel scope only once and keep the session in a shared variable throughout the bot

 

Use system file read range

Instead of using scope use the following:

 

 

Issues

1)

If the spreadsheet contains email addresses you will get invalid url issue – go into excel and remove the links

tgermazvi@herbert.com

 

2)

If there are dates, this will pick up the excel numeric date

You need to convert back to date:

DateTime conv = DateTime.FromOADate(d);

delete range in file workbook activities

 

There is no delete range

Use write cell (write a value of “”) instead

trim in excel

 removes multiple spaces on between words

get current connection string- cute

 select

    'data source=' + @@servername +

    ';initial catalog=' + db_name() +

    case type_desc

        when 'WINDOWS_LOGIN' 

            then ';trusted_connection=true'

        else

            ';user id=' + suser_name()

    end

from sys.server_principals

where name = suser_name()