Jason Norsworthy (12/19/2010)

---

I imagine there are better ways to do this, but this is what I was able to come up with...

select * from

(

select *,

row_number() over(partition by postcode...

Fantastic, used COALESCE(job_number_20+char(34),char(34)) and this has rectified the problem thanks for the help 😀

What?

The data has been output like this:

"Hello","This","Is","What","I","Mean

I need this to look like this:

"Hello","This","Is","What","I","Mean"

The format file is as above,...

oh dear, 1 new problem now...This works fine, however on my last field which needed to have quote qualifiers not only has the 1st quote e.g. "hello. The Header...

Fantasic Chrissy321, works a treat, decided to update the source table prior to outputting via BCP!

Problem solved! 😀

BCP Command as follows:

declare @bcpcommand nvarchar(1000)

SET @bcpcommand = 'bcp "select CHAR(34)+urn,title,firstname,lastname,company,ad1,ad2,ad3,ad4,ad5,postcode,

phone1,phone2,mobile,email,dateofbirth,source,sourceapp,cpsagent,cpsreference,cpspartner,reactiv_bdm,

reactiv_qmq_rdate,job_number_1,job_number_2,job_number_3,job_number_4,job_number_5,job_number_6,

job_number_7,job_number_8,job_number_9,job_number_10,job_number_11,job_number_12,job_number_13,

job_number_14,job_number_15,job_number_16,job_number_17,job_number_18,job_number_19,job_number_20+char(34)

from ASHLEY.DBO.outputtable " queryout "c:\outputs\reactive_med_'+replace(CONVERT(VARCHAR(10), GETDATE(), 104),'.','')+'.csv" -f c:\outputs\emailvision.fmt -T -k'

EXEC master..xp_cmdshell @bcpCommand

Also Format file as follows:

9.0

43

1...

Anybody able to help??

Thanks for the reply, its not actually null its an empty string...

Fantastic!! Works a treat 🙂

The rule is the one with the lowest distance. Sorry for not making this clear

Thanks

Works a treat, thanks very much! This will save me lots of time...

🙂

I also need to do this (altough from another server)...any ideas anyone?

Both Domain and SQL Admin...

I have checked what ther server is running as and its using the logon details from the SQL Administrator so surely they should have full rights?

Is there a way...

Thanks for the replies!

How do I check what the server is running as (whether its local host or not)

Thanks