| Author |
 Topic  |
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
 Posted - 01/11/2013 : 04:57:23
|
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 |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/11/2013 : 05:00:24
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/11/2013 : 05:03:33
|
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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 07:55:43
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/11/2013 : 08:57:30
|
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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 08:59:55
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/14/2013 : 03:20:10
|
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/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/14/2013 : 23:38:12
|
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." |
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/16/2013 : 08:50:52
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/16/2013 : 22:42:19
|
you've select part repeated twice inside bcp command statement. Is this intentional?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/17/2013 : 09:22:27
|
| I have remove the repeated part inside the bcp, still nothing is coming up |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/20/2013 : 21:51:18
|
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 |
|
|
|
Topic |
|
Exporting a View to a CSV
