| Author |
 Topic  |
|
joelmuhindo
Starting Member
South Africa
15 Posts |
 Posted - 01/11/2013 : 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
India
47023 Posts |
Posted - 01/11/2013 : 03:11:59
|
Is sql server installed on your machine or are you connecting to remote server?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 03:33:38
|
| It s giving me query complete with error |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/11/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 03:48:17
|
| I have change the tables name with columms names and its saying successful but still not output |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 03:56:32
|
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'queryout'.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 04:11:23
|
| I tried and still the same , I even tried it before your reply ....eish this command is really troubling me |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 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/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/11/2013 : 04:19:55
|
First see the output of SELECT @@SERVERNAME
If this is showing NULL, then no ouput will come
--
Chandu |
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 04:25:59
|
| Giving the computer name ...........but when I execute the all script on line 12 it saying NULL |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/11/2013 : 04:35:35
|
Whether this query( select * from DsPcDb..REPORT order by Name desc, UsageBegin desc, copycount desc ) gives any output or not?
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 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/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 04:51:31
|
thats ok...but you've other rows containing data also right?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 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 |
|
|
|
joelmuhindo
Starting Member
South Africa
15 Posts |
Posted - 01/11/2013 : 04:56:31
|
| the CostCenter has value , PrintDate and Count are null value but that should not be the issue |
|
|
|
Topic |
|
Exporting a View to a CSV
