Application has nothing to do with table structure.

It must use ONLY SP calls.

If you run ad-hoc queries it's probably too late to optimise performance of your application(s).

Only one point:

remote query will copy the whole remote table to local server. No use of index, no way for optimiser to do its job.

So, on big tables this query...

You may:

1) get it changed;

2) get out of that job (project)

You to choose.

I would not even touch such c..p.

There is no way to succeed using this kind of database...

That many columns in one table means that you missed to read or/and understand some basic educational things about relational databases.

Not sure any "ROBUST PLAN" can help you with this.

Sorry.

Yes, it is.

So, there is implicit conversion.

And there is datatype issue indeed.

But not only.

Matching to comma separated list does not work the way you made it.

You need to use "split" function to...

That's terribly WRONG!!!

What you see in EM is not what you store in DB!!!

EM shows you not actual date value but result of conversion to varchar with...

DATEPART returns int (see BOL)

So, there is no point in using

CONVERT(int, DATEPART(dd, s.dtDate))

WHEN (CHARINDEX(' ', ad_str1) < 10) AND (CHARINDEX(' ', ad_str1) <> 0

I'm wondering what must happen to give you CHARINDEX () < 0 ?

Use @@Rowcount

Varchar ( - what?)

There is no datatype "varchar". There are varchar(20), varchar(50), varchar(100), varchar(500), varchar(1000).

What is datatype of your column LedgersID?

There is no right or wrong. It depends.

But processing data using remote server functionality is definitely wrong.

Imagine, to apply some function from remote server you need pump all data to...

Did you try?

This works perfectly:

DECLARE @tbl TABLE (CUser VARCHAR(10),Software VARCHAR(30))

INSERT INTO @tbl SELECT 'SBauer','Microsoft Excel'                                             

INSERT INTO @tbl SELECT 'SBauer','Microsoft Access'

INSERT INTO @tbl SELECT 'GSmith','Microsoft Word'

INSERT INTO @tbl SELECT 'GSmith','Microsoft...

Don't know if mine is better, but I'm too lazy to deal with those slices.

Some advanced feachure are missed here, but it not...

This will work if to make it this way:

[Microsoft Excel]  = COUNT(CASE WHEN SoftWare = 'Microsoft Excel' THEN SoftWare ELSE NULL END),

same for others.