MS SQL

Help BCP The Following Query

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Mar 2007

Posts: 158

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

Help BCP The Following Query

Nov 2nd, 2009

Good Day All

I have the Following Query

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
DECLARE @CurrentTime DATETIME
SET @CurrentTime = CURRENT_TIMESTAMP
SELECT tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
tu.name [Requested BY] FROM tbl_booking tb INNER JOIN tbl_resource tr
ON tb.resources = tr.id
INNER JOIN tbl_user tu ON tu.id = tb.RequestedByUser
WHERE (day(startdate) = day(@CurrentTime))AND(month(startdate)=month(@CurrentTime))AND(year(startdate)=year(@CurrentTime))AND(tb.STATUS=1)
ORDER BY [Room],[Start Time]
FOR xml raw
DECLARE @CurrentTime DATETIME
SET @CurrentTime = CURRENT_TIMESTAMP
select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
tu.name [Requested by] from tbl_booking tb inner join tbl_resource tr
on tb.resources = tr.id
inner join tbl_user tu on tu.id = tb.RequestedByUser
where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
order by [Room],[Start Time]
for xml raw

This creates an XML, but now i want to store it in

•

C:\X.XML

how can i BCP this Query

Thanks

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

•

Join Date: Feb 2009

Posts: 3,473

Reputation: sknake has much to be proud of

Solved Threads: 630

sknake

Offline

.NET Enthusiast

Nov 2nd, 2009

Why don't you handle this task at the application layer instead of the DB layer? Often time the MSSQL Service is not running with administrative rights and does not have filesystem access to the root drive of the disk, ie C:\

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Mar 2007

Posts: 158

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

Nov 3rd, 2009

Good Morning

This is not an Application, so i will not have a BLL or DAL. i have all the Rights to write to the File System. all i need is help to BCP the given query