none
Save SQL Result in XML File on drive RRS feed

  • Question

  • Hi All,

    How could I save query XML result into physical file on hard drive? I am preparing multiple files having 500 records each. 

    I tried following commands but didn't work and prompt this error 

    SQLState = S1000, NativeError = 0

    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
    NULL'

    DECLARE @cmd1 VARCHAR(4000)='bcp "SELECT * FROM [PAYMENT_MODE_CODE] FOR XML PATH(''object''),ROOT(''objects'');" queryout "c:\Work\test1.xml" -T -c -S' + @@SERVERNAME;
    EXECUTE master..xp_cmdshell @cmd1;

    DECLARE @tbl TABLE ([contract] INT, proposal VARCHAR(100));

    DECLARE @start int = 1; DECLARE @end int = 500; DECLARE @cnt int;
    SELECT @cnt = max(row_id) + 500 FROM #ProdTemp
    WHILE @end < @cnt
    BEGIN

    INSERT INTO @tbl VALUES (10000,'AB234532/223532532'), (10001 ,'AB234532/223532533'), (10002 ,'AB234532/223532534'), (10003 ,'AB234532/223532535'), (10004 ,'AB234532/223532536'), (10005 ,'AB234532/223532537'), (10006 ,'AB234532/223532538'), (10007 ,'AB234532/223532539'), (10008 ,'AB234532/223532540'), (10009 ,'AB234532/223532541'), (10010 ,'AB234532/223532542'), (10011 ,'AB234532/223532543'), (10012 ,'AB234532/223532544'), (10013 ,'AB234532/223532545'); ;WITH rs (xmlData) AS ( SELECT [contract], proposal FROM @tbl FOR XML PATH('row'), TYPE ) SELECT xmlData.query('<import> <node type="document" action="update"> <location>Enterprise:Documentation:Example</location> <category name="Content Server Categories:Transportation">{ for $row in /row return (<attribute name="contract">{data($row/contract)}</attribute> , <attribute name="proposal">{data($row/proposal)}</attribute>) }</category></node></import>') FROM rs;

    set @start = @start + 500; set @end = @end + 500;
    END;


    • Edited by tams1151 Wednesday, July 31, 2019 10:25 PM
    Wednesday, July 31, 2019 10:17 PM

All replies

  • Hi tams1151,

    Here is a working code sample for you.

    You would need to make adjustments based on your environment:

    1. Fully qualified output XML file name.
    2. Fully qualified path to the bcp.exe
    3. SQL statement.
      It could be a stored procedure with parameters too.

    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');';
    
    -- SQL Server 2016 onwards
    SET @SQLCmd = FORMATMESSAGE('START "" "%s" "%s" queryout "%s" -T -x -c -C 1252 -a 32768 -S %s'
    				, @bcp
    				, @SQL
    				, @outputFileName
    				, @@SERVERNAME);
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;
    Wednesday, July 31, 2019 11:12 PM
  • Hi,

    I made the adjustments according to my environment and getting this error while executing statement. here is sql version details Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) Build 7601: Service Pack 1

    Msg 8116, Level 16, State 1, Line 7
    Argument data type varchar is invalid for argument 1 of formatmessage function.

    DECLARE @SQLCmd VARCHAR(8000)
    , @outputFileName VARCHAR(256) = 'c:\Work\SampleXMLOutput.xml'
    , @bcp VARCHAR(256) = 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe'
    , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT * FROM [UAT].[dbo].[PAYMENT_MODE_CODE]  FOR XML PATH(''r''), TYPE, ROOT(''root'');';

    -- SQL Server 2016 and later (it works even in 2012 onwards but undocumented)
    SET @SQLCmd = FORMATMESSAGE('START "" "%s" "%s" queryout "%s" -T -x -c -C 1252 -a 32768 -S %s'
    , @bcp
    , @SQL
    , @outputFileName
    , @@SERVERNAME);

    -- just to see it
    SELECT @SQLCmd AS [Command to execute];

    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;




    • Edited by tams1151 Thursday, August 1, 2019 12:30 AM
    Wednesday, July 31, 2019 11:30 PM
  • Hi tams1151,

    It means that your SQL Server version is old and doesn't support the FORMATMESSAGE() function.

    No worry, here is an adjusted version for you, compatible with the earlier versions of SQL Server:

    DECLARE @SQLCmd VARCHAR(8000) , @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml' , @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe' , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'; -- older versions of SQL Server
    SET @SQLCmd = 'Start "" "' + @bcp + '" '
        + '"' + @SQL + '"'
        + ' queryout "' 
        + @outputFileName
        + '" -T -x -c -C 1252 -a 32768 -S ' + @@SERVERNAME;

    -- just to see it SELECT @SQLCmd AS [Command to execute]; -- create the XML file on the file system EXECUTE master.sys.xp_cmdshell @SQLCmd;

    • Proposed as answer by A2605 Thursday, August 1, 2019 12:59 AM
    • Edited by Yitzhak Khabinsky Thursday, August 1, 2019 2:20 AM
    Thursday, August 1, 2019 12:54 AM
  • Hi  Yitzhak,

    Thank you for your help and prompt response but when I run this query in SQL it is taking too much time after 9 minutes I am unable to see the results, still in progress. Further this table contains 5 columns and 23 records only and it takes less than a sec. if I execute 'Select * from payment_mode_code'. Any special reason please?

    Thursday, August 1, 2019 1:47 AM
  • Hi tams1151,

    It is very strange. For the table of  your size it should be an instant result. It takes 31 ms on my machine.

    1. What is your SQL Server version?
    2. It could be your computer has multiple versions of bcp.exe. My machine has 5 versions of it.
      Please make sure that you are running the latest version of the bcp.exe
    3. You can open a command prompt as administrator and execute there directly the output of the following:
      -- just to see it
      SELECT @SQLCmd AS [Command to execute];
    4. Additionally, you can install the latest available version of bcp.exe, regardless what SQL server version you have.

    Install latest bcp (64-bit)
    =======================================================
    https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017
    Pre-requsisit: Microsoft ODBC Driver 17.3 for SQL Server:
    https://www.microsoft.com/download/details.aspx?id=56567

    Thursday, August 1, 2019 2:06 AM
  • Hi  Yitzhak,

    Due to some limitation I can't install utility and updated driver. Is there any other way please to do so? 

    Thanks!

    Thursday, August 1, 2019 4:08 AM
  • Hi tams1151,

    What about ##1-3?

    Pease share a screen shot of the #3.
    Thursday, August 1, 2019 4:11 AM
  • Hi,

    #1 : SQL version is 

    Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) Build 7601: Service Pack 1

    #3 - Do I need to copy paste below whole query on command prompt or ?

    DECLARE @SQLCmd VARCHAR(8000)
    , @outputFileName VARCHAR(256) = 'c:\Work\SampleXMLOutput.xml'
    , @bcp VARCHAR(256) = 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe'
    , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT * FROM [UAT].[dbo].[PAYMENT_MODE_CODE]  FOR XML PATH(''r''), TYPE, ROOT(''root'');';

    -- SQL Server 2016 and later (it works even in 2012 onwards but undocumented)
    SET @SQLCmd = FORMATMESSAGE('START "" "%s" "%s" queryout "%s" -T -x -c -C 1252 -a 32768 -S %s'
    , @bcp
    , @SQL
    , @outputFileName
    , @@SERVERNAME);

    -- just to see it
    SELECT @SQLCmd AS [Command to execute];

    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    Thursday, August 1, 2019 4:14 AM
  • Hi tams1151,

    For the #3 you need to copy the outcome of the 

    -- just to see it
    SELECT @SQLCmd AS [Command to execute];

    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');';
    
    -- older versions of SQL Server
    SET @SQLCmd = 'Start "" "' + @bcp + '" ' 
        + '"' + @SQL + '"' 
        + ' queryout "'  
        + @outputFileName 
        + '" -T -x -c -C 1252 -a 32768 -S ' + @@SERVERNAME;
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];


    Thursday, August 1, 2019 4:21 AM
  • Here is the result but it doesn't produce anything (no file). Once I pressed enter a new black window opens for a moment and closed off after that I checked destination folder but no file produced.

    
    Thursday, August 1, 2019 4:53 AM
  • Hi tams1151,

    There are two things to pay attention:

    1. Last parameter is SPACESHIP. It is my machine name.
      This parameter is picked up on the fly by the @@SERVERNAME parameter.
      So it shall be your machine name.
    2. Please try to remove the beginning of the statement, i.e. Start ""
      And re-run the rest of it.
      It will provide some additional diagnostic info on the execution.
      Again, please share its screen shot.

    Here is how it looks like on my machine:


    Thursday, August 1, 2019 5:03 AM
  • Hi - Please seee

    Thursday, August 1, 2019 5:14 AM
  • Hi tams1151,

    Finally, it is a good thing to see the explicit errors.

    You need to share the entire command prompt, starting with the command prompt window title and what you are executing.

    As I already mentioned, I doubt that your machine has the same name as mine.


    Thursday, August 1, 2019 5:19 AM
  • Hi tams1151,

    Here is one more additional setting to check on  your machine.

    It is possible that SQL Server is listening on some non-default port.

    You can check it by launching the SQL Server Configuration Manager. Please see a screen shot below. On my machine TCP port is 1433.

    I enhanced the SQL to include the port parameter.

    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
    	, @tcpPort VARCHAR(10) = '1433';
    
    -- older versions of SQL Server
    SET @SQLCmd = 'START "" "' + @bcp + '" ' 
    				+ '"' + @SQL + '"' 
    				+ ' queryout "'  
    				+ @outputFileName 
    				+ '" -T -x -c -C 1252 -a 32768 -S "' + @@SERVERNAME
    				+',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;





    Thursday, August 1, 2019 6:22 AM
  • Hi,

    Unfortunately still not working at my end, please see screen shots. In SQL Query editor it get hanged on this command '

    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    Thursday, August 1, 2019 10:48 PM
  • Hi tams1151,

    It seems that you didn't use the latest version of the SQL statements.

    The command prompt execution shows 2 issues:

    1. With literal server name.
    2. With port parameter.


    You can find me on Skype.

    Thursday, August 1, 2019 10:52 PM
  • Hi tams1151,

    What's the latest?

    Did you make any progress?

    You are almost there.

    Friday, August 2, 2019 4:00 AM
  • I noticed that it says

      TABLE_NAME FROM [        ].master.Information_schema.tables

    I can sense that there is something that has been masked between the brackets, but still, why would you connect to server A to BCP out data from server B? Why not connect to server B directly?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 2, 2019 8:29 AM
  • Hi  Yitzhak,

    Sorry for late response actually 'comma' after machine name (C825LX714, 1433) doesn't work at my end that's why replaced with ':' . My DB server is hosted on another machine which is not accessible to me (I can't work on that machine) that's why using that server name.


    • Edited by tams1151 Sunday, August 4, 2019 10:01 PM
    Sunday, August 4, 2019 9:31 PM
  • Hi tams1151,

    No problem.

    • The comma means that you are asking to use TCP protocol while connecting to the SQL Server.
      Please issue the following in the SSMS on your machine: SELECT @@SERVERNAME;
      Pease check the screen shot taken on my machine.
    • Are you connected to SQL Server via Windows Authentication or SQL Server account?
      I am using Windows Authentication.
      If you are connected via SQL Server account, we need to chance slightly the bcp command line parameters.

    So bcp connection has two options:

    1. -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    2. -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.


    Here is an updated version of the SQL script.

    It controls authentication mode via @WindowsAuth variable.

    If it is =1, it means Windows Authentication.

    If it is =0, it means SQL Server account.

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
    	, @tcpPort VARCHAR(10) = '1433'
    	, @WindowsAuth BIT = 1
    	, @loginID VARCHAR(100) = 'loginID'
    	, @password VARCHAR(100) = 'password';
    
    -- older versions of SQL Server
    SET @SQLCmd = 'START "" "' + @bcp + '"' 
    				+ ' "' + @SQL + '"' 
    				+ ' queryout "' + @outputFileName + '"'
    				+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
    				+ ' -x -c -C 1252 -a 32768'
    				+ ' -S "' + @@SERVERNAME + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    If you are on the unsupported anymore SQL Server 2008, you would need to replace IIF() with the CASE statement as follows:

    CASE WHEN @WindowsAuth  = 1 THEN ' -T'
    ELSE ' -U '+ @loginID + ' -P ' + @password
    END
    Sunday, August 4, 2019 9:51 PM
  • Hi Yitzhak,

    I am connected to SQL through SQL authentication. IIF didn't work then I replaced with case but again it is hanged  as previously.

    Monday, August 5, 2019 12:01 AM
  • Hi tams1151,

    To troubleshoot, please manually remove at the very beginning Start "", and run it at the command prompt, and share its screen shot.

    P.S. You can find me on Skype.

    Monday, August 5, 2019 12:09 AM
  • Dear - Here is the result for both windows/sql authentication

    Monday, August 5, 2019 1:38 AM
  • Hi tams1151,

    As usual, we need the entire command prompt, starting with the actual statement.

    P.S. To expedite the process please find me on Skype.
    Monday, August 5, 2019 1:49 AM
  • Hi,

    I do apologies Skype is not allowed. Here is entire CMD result

    Monday, August 5, 2019 2:03 AM
  • Hi tams1151,

    If you are using SQL Server authentication, you need to assign zero (0) to the following parameter:@WindowsAuth BIT = 0

    Also, for such case you need to assign the real values to the following parameters:

    , @loginID VARCHAR(100) = 'loginID'
     , @password VARCHAR(100) = 'password';

    As end result, you command line will be along the following:

    "c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe" "SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH('r'), TYPE, ROOT('root');" queryout "e:\Temp\SampleXMLOutput.xml" -U loginID -P password -x -c -C 1252 -a 32768 -S "SPACESHIP,1433"

    SQL script:

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
    	, @tcpPort VARCHAR(10) = '1433'
    	, @WindowsAuth BIT = 0	-- 1 is for Windows Authentication, 0 is for SQL Server Authentication
    	, @loginID VARCHAR(100) = 'loginID'
    	, @password VARCHAR(100) = 'password';
    
    -- older versions of SQL Server
    SET @SQLCmd = 'START "" "' + @bcp + '"' 
    				+ ' "' + @SQL + '"' 
    				+ ' queryout "' + @outputFileName + '"'
    				--+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
    				+ CASE WHEN @WindowsAuth  = 1 THEN ' -T'
    					ELSE ' -U '+ @loginID + ' -P ' + @password
    					END
    				+ ' -x -c -C 1252 -a 32768'
    				+ ' -S "' + @@SERVERNAME + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;


    Monday, August 5, 2019 2:07 AM
  • Hi,

    Same error 

    here is command

    "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe" "SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM [P825LB417].master.Information_Schema.Tables FOR XML PATH('r'), TYPE, ROOT('root');" queryout "c:\work\SampleXMLOutput.xml" -U loginID -P password -x -c -C 1252 -a 32768 -S "P825LB417,1433"

    Same result if I use SQL authentication

    • Edited by tams1151 Monday, August 5, 2019 2:23 AM
    Monday, August 5, 2019 2:22 AM
  • Hi tams1151,

    You need to assign the real values to the following parameters:

    , @loginID VARCHAR(100) = 'YourRealLifeLoginID'
     , @password VARCHAR(100) = '
    YourRealLifePassword';

    Go in the Internet browser to the following URL: https://appear.in/yku

    It is my conference room.

    Monday, August 5, 2019 2:24 AM
  • Hi,

    I am so sorry again I can't open provided link due to internet restriction at office and also sorry for not using real  windows credentials I retried with real windows user and Password but still same error 

    Monday, August 5, 2019 2:39 AM
  • Hi tams1151,

    1. As usual, we need the entire command prompt screen shot, starting with the actual statement.
    2. Please execute, and share its screen shot, the following in the SSMS on your machine: SELECT @@SERVERNAME;
    3. Additionally, please share what protocols SQL Server listening to.
      Please share its screen shot.
    4. "...also sorry for not using real  windows credentials I retried with real windows user and Password but still same error...".
      You need to use SQL Server account, not Windows.
      The same account that you are using to connect to the database in SSMS.

    Here is protocols on my SQL Server, and TCP/IP is Enabled:


    Monday, August 5, 2019 2:48 AM
  • 1. SELECT @@SERVERNAME;

    • P825LB417

    2. Default port 1433

    3. I did use my SQL Server account, same error

    4. Protocol screen shot 


    Monday, August 5, 2019 4:37 AM
  • Hi 

    Many many thanks Good news I have done it 

    

    Bad news in SQL server still get hang on this command 

    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    How could i resolve this issue?

    Monday, August 5, 2019 5:16 AM
  • Hi tams1151,

    Congratulations on your first successful execution of bcp.

    What made it working?


    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.
    Monday, August 5, 2019 5:28 AM
  • Hi - Thank you for you help. I followed the instructions mentioned on this page 

    Executing xp_cmdshell with Non SysAdmin Account 

    I can see output after executing this statement  

    master..xp_cmdshell 'whoami'

    but still get hand on on this statement in the query

    EXECUTE master.tmughal.xp_cmdshell @SQLCmd;

    Monday, August 5, 2019 9:34 PM
  • Hi tams1151,

    1. I asked you a question: "...Congratulations on your first successful execution of bcp. What made it working?..."
      Please share the details.
    2. Please share a screen shot of the successful execution at the command prompt.
    3. Just as an idea,  please try to remove the starting point, i.e. Start "" from the string concatenation. So it will start with "c:\Program etc...
      And execute it via EXECUTE master.sys.xp_cmdshell @SQLCmd;
    4. Is it possible that your account doesn't have file system OS permissions on the directory and actual bcp.exe file:
      "c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
      "c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe"

    Monday, August 5, 2019 9:44 PM
  •  I have full permission on Bin folder and after removing start I got this error 'C:\Program' is not recognized as an internal or external command,

    here is full @SQLCmd.

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
    , @outputFileName VARCHAR(256) = 'c:\work\SampleXMLOutput.xml'
    , @bcp VARCHAR(256) = 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe'
    , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM P825LB417.master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
    , @tcpPort VARCHAR(10) = '1433'
    , @WindowsAuth BIT = 0
    , @loginID VARCHAR(100) = 'tams115'
    , @password VARCHAR(100) = 'Password*2019'
    , @Server Varchar(100) = 'P825LB417';

    -- older versions of SQL Server
    SET @SQLCmd = --'START "" 
    '"' + @bcp + '"' 
    + ' "' + @SQL + '"' 
    + ' queryout "' + @outputFileName + '"'
    + CASE WHEN @WindowsAuth  = 1 THEN ' -T'
    ELSE ' -U '+ @loginID + ' -P ' + @password
    END
    + ' -x -c -C 1252 -a 32768'
    + ' -S "' + @Server + ',' + @tcpPort + '"';

    -- just to see it
    SELECT @SQLCmd AS [Command to execute];

    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;





    • Edited by tams1151 Monday, August 5, 2019 10:30 PM
    Monday, August 5, 2019 10:21 PM
  • Hi tams1151,

    1. You didn't provide details.
    2. You didn't share a screen shot.
    3. It didn't work as expected.
      Please restore the Start "" like it was.
    4. Please share a screen shot with permissions, like I did.
    5. Please make one more adjustment to the SQL Script. Let's give it a window title as follows:
      SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
      This way output will be visible directly in the SSMS.


    The most important is #5. Please give it a try.

    I hope this a last adjustment we have to make.


    Monday, August 5, 2019 10:36 PM
  • Hi,

    I am sorry, here we go

    New command still hanged

    START  /B "WindowTitle"" "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe" "SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM P825LB417.master.Information_Schema.Tables FOR XML PATH('r'), TYPE, ROOT('root');" queryout "c:\work\SampleXMLOutput.xml" -U tams115 -P Password*2019 -x -c -C 1252 -a 32768 -S "P825LB417,1433"

    Monday, August 5, 2019 11:17 PM
  • Hi tams1151,

    What a weirdness.

    Please share a screen shot of the SQL script execution in SSMS like I did.

    And one more thing to check in SSMS as follows:

    1. Right-click the server
    2. Choose Facets
    3. Select in the Facet dropdown: Surface Area Configuration
    4. Set property XPCmdShellEnabled to True.


    Monday, August 5, 2019 11:26 PM
  • I am sorry. here is SQL screen print. No result after 5 mins.




    • Edited by tams1151 Monday, August 5, 2019 11:58 PM
    Monday, August 5, 2019 11:45 PM
  • Hi tams1151,

    1. I asked to check facet. Please see above.
    2. Comment out the actual execution, and share with me the text of the @SQLCmd variable.
    3. You have an extra not needed double quote after "WindowTitle""
      Please check the following correct way:
      SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
    Tuesday, August 6, 2019 12:01 AM
  • Hi,
    1. Yes I saw your facet check comments and also copied that screen print in last message. 

    2. after double quote correction - Still no luck

    SET @SQLCmd =  'START /B "WindowTitle" "'  + @bcp + '"' 
    + ' "' + @SQL + '"' 
    + ' queryout "' + @outputFileName + '"'
    + CASE WHEN @WindowsAuth  = 1 THEN ' -T'
    ELSE ' -U '+ @loginID + ' -P ' + @password
    END
    + ' -x -c -C 1252 -a 32768'
    + ' -S "' + @Server + ',' + @tcpPort + '"';

    Tuesday, August 6, 2019 12:07 AM
  • Hi tams1151,

    1. Comment out the actual execution (very last line), run the SQL script, and share with me the text of the @SQLCmd variable:
      -- just to see it
      SELECT @SQLCmd AS [Command to execute];

    On my machine it is here:

    START /B "WindowTitle" "c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe" "SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH('r'), TYPE, ROOT('root');" queryout "e:\Temp\SampleXMLOutput.xml" -T -x -c -C 1252 -a 32768 -S "SPACESHIP,1433"


    Tuesday, August 6, 2019 12:12 AM
  • Hi,

    Please see

    START  /B "WindowTitle"" "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe" "SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM P825LB417.master.Information_Schema.Tables FOR XML PATH('r'), TYPE, ROOT('root');" queryout "c:\work\SampleXMLOutput.xml" -U tams115 -P Password*2019 -x -c -C 1252 -a 32768 -S "P825LB417,1433"


    • Edited by tams1151 Tuesday, August 6, 2019 12:23 AM
    Tuesday, August 6, 2019 12:21 AM
  • Hi tams1151,

    1. You still have an extra not needed double quote after "WindowTitle""
      Just compare with what I provided from my machine.
    2. Again, here is a complete SQL script.
      Ctrl+C and Ctrl+V, nothing else.
      Just modify variable values at the top. And leave everything else as-is.

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
    	, @tcpPort VARCHAR(10) = '1433'
    	, @WindowsAuth BIT = 1	-- 1 is for Windows Authentication, 0 is for SQL Server Authentication
    	, @loginID VARCHAR(100) = 'loginID'
    	, @password VARCHAR(100) = 'password';
    
    -- older versions of SQL Server
    -- /B "WindowTitle" parameters produce output in the SSSMS !!!
    SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
    				+ ' "' + @SQL + '"' 
    				+ ' queryout "' + @outputFileName + '"'
    				--+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
    				+ CASE WHEN @WindowsAuth = 1 THEN ' -T'
    					ELSE ' -U '+ @loginID + ' -P ' + @password
    					END
    				+ ' -x -c -C 1252 -a 32768'
    				+ ' -S "' + @@SERVERNAME + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create the XML file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;



    Tuesday, August 6, 2019 12:25 AM
  • Hi,

    I am so sorry, I am not good in coding and has already been taken your too much time.

    here is modified SQLMD, SQL server get hanged.

    I also tried with @@SERVERNAME rather @Server parameter

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
    	, @outputFileName VARCHAR(256) = 'c:\Work\SampleXMLOutput.xml'
    	, @bcp VARCHAR(256) = 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe'
    	, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM P825LB417.master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
    	, @tcpPort VARCHAR(10) = '1433'
    	, @WindowsAuth BIT = 0	-- 1 is for Windows Authentication, 0 is for SQL Server Authentication
    	, @loginID VARCHAR(100) = 'tams115'
    	, @password VARCHAR(100) = 'Password*2019'
    	, @Server Varchar(100) = 'P825LB417';
    
    -- older versions of SQL Server
    -- /B "WindowTitle" parameters produce output in the SSSMS !!!
    SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
    				+ ' "' + @SQL + '"' 
    				+ ' queryout "' + @outputFileName + '"'
    				--+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
    				+ CASE WHEN @WindowsAuth = 1 THEN ' -T'
    					ELSE ' -U '+ @loginID + ' -P ' + @password
    					END
    				+ ' -x -c -C 1252 -a 32768'
    				+ ' -S "' + @Server + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    EXECUTE master.sys.xp_cmdshell @SQLCmd;



    Tuesday, August 6, 2019 1:32 AM
  • Hi tams1151,

    1. Please try with one more modification:
      SET @SQLCmd = 'cmd /c START /B "WindowTitle" "' + @bcp + '"'
    2. I asked you few times to run the following in SSMS (and share its output):
      SELECT @@SERVERNAME;
    3. What Operating System (OS) is on your machine?
    Tuesday, August 6, 2019 1:36 AM
  • No luck 

    SET @SQLCmd = 'cmd /c START /B "WindowTitle" "' + @bcp + '"' 
    + ' "' + @SQL + '"' 
    + ' queryout "' + @outputFileName + '"'
    --+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
    + CASE WHEN @WindowsAuth = 1 THEN ' -T'
    ELSE ' -U '+ @loginID + ' -P ' + @password
    END
    + ' -x -c -C 1252 -a 32768'
    + ' -S "' + @Server + ',' + @tcpPort + '"'; -- also tried with @@SERVERNAME

    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    EXECUTE master.sys.xp_cmdshell @SQLCmd;

    Tuesday, August 6, 2019 1:45 AM
  • Hi tams1151,

    • Please restart your SSMS as Administrator. It will show that word in its window title.
    • I hope you noticed that my responses have a numbered list.
      Please answer on all of the ##.
    • And always you need to provide the result of 
      -- just to see it
      SELECT @SQLCmd AS [Command to execute];
    • What about https://appear.in/yku

    Tuesday, August 6, 2019 1:48 AM