{"id":854,"date":"2015-01-02T21:39:39","date_gmt":"2015-01-02T21:39:39","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=854"},"modified":"2015-01-10T23:16:09","modified_gmt":"2015-01-10T23:16:09","slug":"bcp-with-no-output-no-xp_cmdshell","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=854","title":{"rendered":"BCP with No Output &#038; No xp_cmdshell"},"content":{"rendered":"<p>My problem was that I needed to create a cvs file with an older version of a SQL Server database.<\/p>\n<p>My plan was to use the BCP utility to create the file, and run it though our enterprise scheduling system.\u00a0 By default, BCP outputs a line for every one thousand rows.\u00a0 This could be bad for the the enterprise scheduler console output if the file will contain millions of rows.<\/p>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_Normal.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-860 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_Normal.jpg\" alt=\"BCP_Normal\" width=\"742\" height=\"235\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_Normal.jpg 742w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_Normal-300x95.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_Normal-210x66.jpg 210w\" sizes=\"(max-width: 742px) 100vw, 742px\" \/><\/a><\/p>\n<p>I quick search of the internet only recommends using xp_cmdshell to resolve my issue.\u00a0 However I am not going to enable xp_cmdshell for security reasons.<\/p>\n<p>Resolution:<\/p>\n<p>Because BCP is going to run in a command prompt window, I would be able to redirect the STDOUT to NUL.<\/p>\n<pre>1&gt;NUL<\/pre>\n<p>Example:<\/p>\n<pre>bcp \"select * from [DW2008].[DBO].[Sales]\" queryout \"C:\\Reports\\sales.csv\" -T -t\",\" -c 1&gt;NUL<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_with_no_output.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-859 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/12\/BCP_with_no_output.jpg\" alt=\"BCP_with_no_output\" width=\"778\" height=\"116\" \/><\/a><\/p>\n<p>This will send any of the BCP output to NUL, however if their is an error with command line syntax, it would be displayed.<\/p>\n<p>Limitation:\u00a0 Any SQL Server errors are considered normal output, and will be suppressed.\u00a0 If there is a concern for SQL errors, it would be better to redirect the output to a file.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>My problem was that I needed to create a cvs file with an older version of a SQL Server database. My plan was to use the BCP utility to create &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=854\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":859,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,10],"tags":[45,64],"class_list":["post-854","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","category-windows","tag-bcp","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/854"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=854"}],"version-history":[{"count":9,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/854\/revisions"}],"predecessor-version":[{"id":1089,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/854\/revisions\/1089"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/859"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}