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 ...

Lisa Liel (12/14/2012)

---

Thanks, Phil. I thought NOLOCK prevented table locks, and made the code run faster. Is that not the case?

Also... doing the subqueries with "With X as"...

Someone had a lot of fun writing that query.

I believe that error means that there are unequal numbers of columns in the different SELECTs - so things just don't match...

Something like this (untested) should get you started.

with invs

as (

select CustKey

,sum(balance) invbalance

from tarInvoice

group by CustKey

)

,payments

as (

select CustKey

,Sum(UnappliedAmt) UnappliedAmt

from tarCustPmt

group by CustKey

)

select c.CustKey

,c.CustId

,etc etc

,IsNull(invs.invbalance, 0)

,isNull(payments.UnappliedAmt)

from tarCustomer c

left join invs on c.CustKey...

Steady on, don't overdo it :hehe:

mikes84 (12/11/2012)

---

Hi,

I have a FULL OUTER JOIN with an OR condition that's running very slowly. The reason is because I'm trying to join a users table (id, username1, username2) where...