Mitesh Oswal (12/21/2012)

---

DECLARE @tbl TABLE

(

ID INT IDENTITY(1,1),

SomeData VARCHAR(100)

)

INSERT INTO @tbl SELECT 'NA11345'

INSERT INTO @tbl SELECT 'NA113456'

INSERT INTO @tbl SELECT 'RA11345'

UPDATE @tbl SET SomeData = SUBSTRING(SomeData,CHARINDEX('NA',SomeData)+2,LEN(SomeData))

WHERE CHARINDEX('NA',SomeData) = 1

select *...

Possibly not relevant here, but if the 'COLS' column is indexed, a better option would be

where cols like 'NA%'

As this will...

Someone at MS will write a conversion utility for sure if this is implemented. I don't think it's worth worrying about - there is no agreement as to exactly when...

Koen Verbeeck (12/20/2012)

---

A very elaborate workaround:

you could use the Export column to export each location to a seperate flat file (you'd need to combine all the data columns you need...

Another way:

where len(Col) > len(replace(Col, '''', ''))

venkatareddy.mora (12/20/2012)

---

Hi,

I have a flatfile which contains 100 diff locations, i want split the data based on the name of city

Ex:-

cid,cname,loc

1,aa,us

2,bb,uk

3,cc,russaia

........ upto 100

so want split the data based...

In relation to the convert, it actually did work. I thought this would work as my perception of running a linked server to MySQL still means I am making the...

10e5x (12/19/2012)

---

Hi phil,

I heed ur advice and did a debug. It was strange it doesnt return me any data. Hence i read through my script and realize it may...

Brad Marsh (12/18/2012)

---

Perfect - now getting

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "name" is a duplicate.

Owh the joys of...

Steve Jones - SSC Editor (12/18/2012)

---

The rules are a mess. You can place them (almost) anywhere, which is crazy.

I don't like the idea of them after BEGIN-like statements. I'd see...

I saw an example only today of over-zealous use of the semi-colon terminator, with something like this:

Begin try;

--do stuff

End try

Begin Catch

--error handling stuff here

End Catch;

This does not generate an error...

..which you will quickly diagnose when running in debug. Sounds like the datareader may not be getting populated at all.

Single quotes within single quotes may be causing issues. Try this instead:

select *

from openquery([connection], '

-- Forum Messages by User

use databasebname;

select u.name, convert_tz(from_unixtime(m.modificationDate/1000),''+00:00'',''-08:00'') as TimePST, m.subject, m.body, m.messageID, f.forumID, f.name

from jiveMessage...

Have you tried setting a breakpoint and debugging line by line? It might illuminate the problem.

Try running the queries one at a time and counting the columns ...