Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Exporting a View to a CSV

Author  Topic 

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 03:08:42
Hello ,
I have a query to export a view to a csv and the query say successfull but there is nothing coming out as exported
bellow is the query

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
order by ServiceConsumer_T.Name desc, ServiceUsage_T.UsageBegin desc, ServiceUsage_T.copycount desc"
queryout E:\bcp\REPORT.csv -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql



Please any helps will be much appreciated

Joel Muhindo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:11:59
Is sql server installed on your machine or are you connecting to remote server?

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 03:21:24
The Sql is installed on my local Machine , and tried both from the network and local Machine still doesn t give any output but still the query executed successfull
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:24:50
try the query first in SSMS and see if it gives you correct output

select * from DsPcDb..REPORT
order by ServiceConsumer_T.Name desc, ServiceUsage_T.UsageBegin desc, ServiceUsage_T.copycount desc

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 03:33:38
It s giving me query complete with error
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-11 : 03:35:44
In your view, column names are CostCenter,PrintDate,Count
So you have to use those names in ORDER BY clause

select * from DsPcDb..REPORT
order by CostCenter desc, PrintDate desc,Count desc


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:39:10
quote:
Originally posted by joelmuhindo

It s giving me query complete with error


The reason for error is wrong aliases

Once view is created you dont need tablename again in columns

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 03:48:17
I have change the tables name with columms names and its saying successful but still not output
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:50:21
first go to command line and run this

bcp "select * from DsPcDb..REPORT order by ServiceConsumer_T.Name desc, ServiceUsage_T.UsageBegin desc, ServiceUsage_T.copycount desc" queryout E:\bcp\REPORT.csv -c -t -T -S <servername>

and see if it gives you CSV output

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 03:56:32
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'queryout'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:58:49
what about this?
bcp "select * from DsPcDb..REPORT order by ServiceConsumer_T.Name desc, ServiceUsage_T.UsageBegin desc, ServiceUsage_T.copycount desc" queryout "E:\bcp\REPORT.csv" -c -t -T -S <servername>

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 04:11:23
I tried and still the same , I even tried it before your reply ....eish this command is really troubling me
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 04:19:28
try this too

bcp "select * from DsPcDb..REPORT order by Name desc, UsageBegin desc, copycount desc" queryout E:\bcp\REPORT.txt -c -t, -T -S <servername>

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

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-11 : 04:19:55
First see the output of SELECT @@SERVERNAME

If this is showing NULL, then no ouput will come

--
Chandu
Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 04:25:59
Giving the computer name ...........but when I execute the all script on line 12 it saying NULL
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-11 : 04:35:35
Whether this query( select * from DsPcDb..REPORT order by Name desc, UsageBegin desc, copycount desc ) gives any output or not?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 04:40:12
quote:
Originally posted by joelmuhindo

Giving the computer name ...........but when I execute the all script on line 12 it saying NULL


NULL for what?

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 04:48:12
correct statement ( select * from DsPcDb..REPORT order by CostCenter desc, PrintDate desc, count desc) its working ,
the all scrip say excute but on theb out put line 12 say NULL and there is no result on the folder bcp on the c drive or E drive
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 04:51:31
thats ok...but you've other rows containing data also right?

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

Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 04:51:36
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
Go to Top of Page

joelmuhindo
Starting Member

15 Posts

Posted - 2013-01-11 : 04:56:31
the CostCenter has value , PrintDate and Count are null value but that should not be the issue
Go to Top of Page
    Next Page

- Advertisement -