SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Exporting a View to a CSV
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/11/2013 :  04:57:23  Show Profile  Reply with Quote
Have you seen output in the file E:\bcp\REPORT.csv ?


Anyway, the output on resultspage looks like this........
NULL
Starting copy...
NULL
107 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (107000.00 rows per sec.)
NULL



--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/11/2013 :  05:00:24  Show Profile  Reply with Quote
quote:
Originally posted by joelmuhindo

Hi Visakh,
The scrip is execute fine but on the Resultpage it s on the line 12 because only 12 rows where affected but it say on line 12 NULL,
may be that why there is no result


No that is not the problem. Check output in your REPORTS.csv file........

--
Chandu

Edited by - bandi on 01/11/2013 05:01:04
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/11/2013 :  05:03:33  Show Profile  Reply with Quote
if get output in SSMS then query is correct.
the only other thing to see is what message you get when you run bcp command at command prompt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

joelmuhindo
Starting Member

South Africa
15 Posts

Posted - 01/11/2013 :  07:55:43  Show Profile  Reply with Quote
this is the result that I get once the query is executed but not result in the folder bpc\REPORT.csv nothing is sitting there



Changed database context to 'DsPcDb'.
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

(12 rows affected)


Edited by - joelmuhindo on 01/11/2013 08:03:54
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/11/2013 :  08:57:30  Show Profile  Reply with Quote
that means bcp threw an error. whats the login you're executing this under? is it windows authentication?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

joelmuhindo
Starting Member

South Africa
15 Posts

Posted - 01/11/2013 :  08:59:55  Show Profile  Reply with Quote
Yes I m using the windows authentication,

do I have to create and account for this or not ?

Edited by - joelmuhindo on 01/14/2013 01:42:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/14/2013 :  03:20:10  Show Profile  Reply with Quote
quote:
Originally posted by joelmuhindo

Yes I m using the windows authentication,

do I have to create and account for this or not ?


no need if account you execute has sufficient priviledges to invoke xp_cmdshell and also in destination folder where you save the file.

the error message suggests issue is with one of bcp parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/14/2013 :  23:38:12  Show Profile  Reply with Quote
quote:
Originally posted by joelmuhindo

this is the result that I get once the query is executed but not result in the folder bpc\REPORT.csv nothing is sitting there



Changed database context to 'DsPcDb'.
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

(12 rows affected)





What's the full command you're using?

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

joelmuhindo
Starting Member

South Africa
15 Posts

Posted - 01/16/2013 :  08:50:52  Show Profile  Reply with Quote
quote:
Originally posted by Jeff Moden

quote:
Originally posted by joelmuhindo

this is the result that I get once the query is executed but not result in the folder bpc\REPORT.csv nothing is sitting there



Changed database context to 'DsPcDb'.
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

(12 rows affected)





What's the full command you're using?

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."




this his the full command


use DsPcDb
go
alter view REPORT
(CostCenter,PrintDate,Count)
AS
SELECT
ServiceConsumer_T.Name AS 'Name',
ServiceUsage_T.UsageBegin AS 'UsageBegin',
ServiceUsage_T.copycount As 'copycount'
FROM ServiceUsage_T
left join Service_T
ON ServiceUsage_T.Service=Service_T.ID
full join ServiceConsumer_T
ON ServiceConsumer_T.ID=ServiceUsage_T.ServiceConsumer
go
declare @sql varchar(8000)
select @sql = 'bcp "select * from DsPcDb..REPORT
select * from DsPcDb..REPORT
order by CostCenter desc, PrintDate desc,Count desc"
queryout C:\bcp\REPORT.csv -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql



looking forward to hearing from you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/16/2013 :  22:42:19  Show Profile  Reply with Quote
you've select part repeated twice inside bcp command statement. Is this intentional?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

joelmuhindo
Starting Member

South Africa
15 Posts

Posted - 01/17/2013 :  09:22:27  Show Profile  Reply with Quote
I have remove the repeated part inside the bcp, still nothing is coming up
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/20/2013 :  21:51:18  Show Profile  Reply with Quote
The problem is simple. The BCP command is supposed to be on a single line but is not.

Try this exact code...
declare @sql varchar(8000)
select @sql = 'bcp "select * from DsPcDb..REPORT order by CostCenter desc, PrintDate desc,Count desc" queryout "C:\bcp\REPORT.csv" -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql


--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

Edited by - Jeff Moden on 01/20/2013 21:54:23
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000