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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

joelmuhindo
Starting Member

South Africa
15 Posts

Posted - 01/11/2013 :  03:08:42  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  03:11:59  Show Profile  Reply with Quote
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

South Africa
15 Posts

Posted - 01/11/2013 :  03:21:24  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  03:24:50  Show Profile  Reply with Quote
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

South Africa
15 Posts

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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/11/2013 :  03:35:44  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  03:39:10  Show Profile  Reply with Quote
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

South Africa
15 Posts

Posted - 01/11/2013 :  03:48:17  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  03:50:21  Show Profile  Reply with Quote
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

South Africa
15 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/11/2013 :  03:58:49  Show Profile  Reply with Quote
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

South Africa
15 Posts

Posted - 01/11/2013 :  04:11:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  04:19:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/11/2013 :  04:19:55  Show Profile  Reply with Quote
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

South Africa
15 Posts

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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/11/2013 :  04:35:35  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  04:40:12  Show Profile  Reply with Quote
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

South Africa
15 Posts

Posted - 01/11/2013 :  04:48:12  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/11/2013 :  04:51:31  Show Profile  Reply with Quote
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

South Africa
15 Posts

Posted - 01/11/2013 :  04:51:36  Show Profile  Reply with Quote
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

South Africa
15 Posts

Posted - 01/11/2013 :  04:56:31  Show Profile  Reply with Quote
the CostCenter has value , PrintDate and Count are null value but that should not be the issue
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.17 seconds. Powered By: Snitz Forums 2000