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 DsPcDbgoalter view REPORT(CostCenter,PrintDate,Count)ASSELECT ServiceConsumer_T.Name AS 'Name', ServiceUsage_T.UsageBegin AS 'UsageBegin',ServiceUsage_T.copycount As 'copycount'FROM ServiceUsage_Tleft join Service_TON ServiceUsage_T.Service=Service_T.IDfull join ServiceConsumer_TON ServiceConsumer_T.ID=ServiceUsage_T.ServiceConsumer godeclare @sql varchar(8000)select @sql = 'bcp "select * from DsPcDb..REPORTorder by ServiceConsumer_T.Name desc, ServiceUsage_T.UsageBegin desc, ServiceUsage_T.copycount desc"queryout E:\bcp\REPORT.csv -c -t, -T -S'+ @@servernameexec master..xp_cmdshell @sqlPlease any helps will be much appreciatedJoel 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 outputselect * from DsPcDb..REPORTorder by ServiceConsumer_T.Name desc, ServiceUsage_T.UsageBegin desc, ServiceUsage_T.copycount desc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
joelmuhindo
Starting Member
15 Posts |
Posted - 2013-01-11 : 03:33:38
|
It s giving me query complete with error |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-11 : 03:35:44
|
In your view, column names are CostCenter,PrintDate,CountSo you have to use those names in ORDER BY clauseselect * from DsPcDb..REPORTorder by CostCenter desc, PrintDate desc,Count desc --Chandu |
|
|
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 aliasesOnce view is created you dont need tablename again in columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 03:50:21
|
first go to command line and run thisbcp "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 MVPhttp://visakhm.blogspot.com/ |
|
|
joelmuhindo
Starting Member
15 Posts |
Posted - 2013-01-11 : 03:56:32
|
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'queryout'. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 04:19:28
|
try this toobcp "select * from DsPcDb..REPORT order by Name desc, UsageBegin desc, copycount desc" queryout E:\bcp\REPORT.txt -c -t, -T -S <servername>------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-11 : 04:19:55
|
First see the output of SELECT @@SERVERNAMEIf this is showing NULL, then no ouput will come--Chandu |
|
|
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 |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 |
|
|
Previous Page&nsp;
Next Page
|