This still doesn't help.  We don't have any information about Company.Schema.GeneralParameter, Company.Schema.ClaimMain,  Company.Schema.PolicyCover or any other table.  Stopped looking after a while.  I'm not going to try to create the table structures...

This works if there are up to 4 levels

select Case when C4 is not null
                then Space(12)
            when C3 is not null
                            then Space(9)

can you provide DDL and sample data as well as what you're expected outcome should look like?

This is calling an SSIS package.  You'll need to check connection strings to make sure you are actually using the databases you think you are.  Also, when you debug, are...

I looked at the link.  It is a 9 year old solution that does need modifying.  I would use SSIS and as opposed to this script, but here's a quick...

are they all the same format?  You could setup an SSIS package to process all xls file in the folder.  That import data function creates an SSIS package. At the...

Stop the deletion!!!  Are you just doing a straight delete from ....?  This will lock the table, even worse if there are foreign keys with cascade delete.  You should put...

Is this a 1 time thing or does it need to be repeated in each environment?  You can use the import wizard in SSMS to point to the source and...

can you grab the actual query that the app is sending (the sp_cursorprepexec statement)?  Run that in ssms to see how it performs. At least then it's apples to apples.  Then...

It's always helpful to provide DDL and data

drop table if exists #temp
create table #Temp (AgentID int,
                 PhoneNumber varchar(20),
                 Description varchar(20))

insert into...

Damn you  Dense_Rank. Should've known Jeff wrote something about this.   Thanks for all your help

do you get any errors when running?  You should try putting a try/catch around it instead of using @@Error.  It could be permissions, but it could also be the mapped...

It was just the way I read it.  I apologize and I'm glad it was me who misinterpreted it and not your intent.  As far as dropping tables, those we...

here is what I think the ddl  is that I asked you for, but your search criteria doesn't make any sense.  can you elaborate?  Also, Phil's been on this forum...

Please provide ddl, sample data and expected output.  Looking at your query, the coalesce function on the where clause isn't good