No, the dataset is not of fixed length and format. there may be a length of 1 and then there may be 116. So I go by proposed solution of...

Thanks a lot both of you. it worked. I was thinking that something like this will also work but it didn't 🙁

create table testing(ID int identity,

big_number VARCHAR(116) CONSTRAINT CK_big_number CHECK...

but will the sorting work correctly on varchar datatype?

Sorry for replying late. I am connected to MYSERVER while running the openrowset

yes, permissions are there. If i run the merge statement without OPENROWSET, it works.

So i was querious if it is possible at all?

Thanks So much Chris and it works. I recognize the deep thought you might have put in it. Thanks for your time...

Correct Sir...

Problem is :

I have hard coded the query for ForumId=9. But I need the SUM of hits for All

ForumIds individually. Something like:

ForumId SumOfHitCount

9 ...

Sorry for confusion. I am trying to remove a user defined function already built, not the sum function

I got this from BOL:

If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by...

Although it works fine but why why do we need the UNION ALL part in the CTE?

mickyT (10/8/2012)

Here's another variation that should do the trick. No function this...

Thank you for putting in time for this...

This is quite impressive but it doesn't work for some scenarios. e.g. this one :

insert into t1

select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'

EDIT: I meant it for Chris response:

SELECT

t.name, x.New_Name

FROM #t1...

Hi Chris.

the numbers are not consecutive as you have assumed. And they may be of length greater than 6. I think it is not so straight as it looks.

This is really good but problem is that my table has multiple rows and this is a table function. So it has to be a scalar function for me to...