none
bcp - error in query

    질문

  • Hello,

    I have written this query before but this time I get an error message that not clear for me where the problem lies.

    The select statement functions fine on it own.

    Maybe you guys can help.

    The following query give the following error;

    Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @staffVisPres = 'SELECT * FROM (SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'') as t order by t.PersonalNr desc'
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + ''<ROOT>'' + ('+@staffVisPres+') + ''</ROOT>'''
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    EXEC xp_cmdshell @strdata

    When i remove the ORDER BY 

    I get the following error;

    Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    I do prefer to have the ORDER BY keyword.

    I stripped down the select statement to the following and still i get this message.

    SELECT *FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID

    2018년 6월 13일 수요일 오후 8:35

답변

  • Looks like what you're after is something like this

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @staffVisPres = 'SELECT * FROM (SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'') as t order by t.PersonalNr desc FOR XML PATH(''Details'')'
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + CAST(CAST(''<ROOT>'' + ('+@staffVisPres+') AS [*] + ''</ROOT>'' as xml) AS varchar(max))'
    
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    EXEC xp_cmdshell @strdata


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 Phyxius7 2018년 6월 14일 목요일 오후 12:23
    2018년 6월 14일 목요일 오전 8:10
  • Visakh16 to the rescue,

    Thank you for pointing out that I missed the mandatory clause FOR XML. that needs to be set at the end of the select statement.

    Here is the final working query;

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @staffVisPres = 'SELECT dbo.hPerson.PersonalNr, dbo.hPerson.FirstName, dbo.hPerson.LastName, dbo.hPerson.TelNr AS TelephoneWork, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'' order by PersID desc FOR XML PATH (''Person'')'
    
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + ''<Roor>'' + ('+@staffVisPres+') + ''</root>'''
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    EXEC xp_cmdshell @strdata

    • 답변으로 표시됨 Phyxius7 2018년 6월 14일 목요일 오후 12:23
    2018년 6월 14일 목요일 오후 12:23

모든 응답

  • You can test your select query first:

    Set @strdata = 'bcp "'+@staffVisPres+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''

    2018년 6월 13일 수요일 오후 8:45
    중재자
  • This gives me a file with TAB separated content.

    and a warning:

    Error = [Microsoft][ODBC Driver 13 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.

    • 편집됨 Phyxius7 2018년 6월 13일 수요일 오후 9:01
    2018년 6월 13일 수요일 오후 8:57
  • Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + ''<ROOT>'' + ('+@staffVisPres+') + ''</ROOT>'''

    I'm not sure what you had in mind, but this does not make much sense to me. That can only work out if the query returns a single column and row.

    To form XML out of your result set, use FOR XML PATH.

    If you need the prolog, you should consider doing this from a client program or a CLR procedure instead. That will not be smoothly done with BCP.

    2018년 6월 13일 수요일 오후 9:40
  • This gives me a file with TAB separated content.

    and a warning:

    Error = [Microsoft][ODBC Driver 13 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.

    Per the code script above, it seems that you would like to export data in xml format by using BCP command. For this purpose, please try to execute the following script and see if it works.

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    --Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    --Set @staffVisPres = 'SELECT * FROM (SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'') as t order by t.PersonalNr desc'
    Set @staffVisPres = 'SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'' order by PersonalNr desc FOR XML AUTO'
    
    --Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + ''<ROOT>'' + ('+@staffVisPres+') + ''</ROOT>'''
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    --Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    Set @strdata = 'bcp "'+@staffVisPres+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    --print @strdata
    
    
    EXEC xp_cmdshell @strdata

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 3:34
  • Will, 

    Thank you for your reply.

    Your request gives me the following result;

    <dbo.hPerson PersonalNr="Xtentional (ICK)" FirstName="john" LastName="do, van" MUL011="Calamiteitenlijst"/>
    <dbo.hPerson PersonalNr="trainee" FirstName="Bratt" LastName="Pitt" MUL011="Calamiteitenlijst"/>
    

    I created a similar query before which returned multiple columns, here is my copy of it and the result;

    So do not understand why it doesnot function this time.

    DECLARE 
    	@xmlProlog VARCHAR(max),
    	@visitordata VARCHAR(max),
    	@staffcontratordata VARCHAR(max),
    	@XMLcontent  VARCHAR(max),
    	@dir VARCHAR(max),
    	@preFix  VARCHAR(max),
    	@timestamp VARCHAR(max),
    	@fileExten VARCHAR(max),
    	@DirData VARCHAR(max),
    	@servername VARCHAR(max),
    	@dbname VARCHAR(max),
    	@strdata VARCHAR(8000)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @visitordata = 'SELECT CASE WHEN PersonType = ''2'' THEN ''Vistor'' ELSE '''' END as Type, hPerson.PersonalNr, hPerson.LastName, hPerson.FirstName, visVisitor.TelNr, hPerson.RoomLastACFK FROM dbo.visVisitor INNER JOIN dbo.hPerson ON visVisitor.VisitorID = hPerson.PersID INNER JOIN dbo.visVisit INNER JOIN dbo.cLocation ON visVisit.LocationFK = cLocation.LocationID INNER JOIN dbo.visVisitorVisit ON visVisit.VisitID = visVisitorVisit.VisitFK ON visVisitor.VisitorID = visVisitorVisit.VisitorVisitID WHERE LocationID = ''1'' and DateIn < getdate() and DateOut is NULL ORDER BY hPerson.PersonalNr DESC FOR XML PATH(''record'')'
    Set @staffcontratordata = 'select CASE WHEN PersonalNr like ''IV%'' THEN ''Contractor'' WHEN PersonalNr not like ''I%'' THEN ''Staff'' ELSE '''' END as Type, isnull(cast(PersonalNr as varchar(11)), '''') as PersonalNr, isnull(cast(LastName as varchar(11)), '''') as LastName, isnull(cast(FirstName as varchar(11)), '''') as FirstName, isnull(cast(TelNr as varchar(11)), '''') as TelNr from dbo.hPerson where RoomLastACFK = ''1'' order by PersID asc FOR XML PATH(''record'')'
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + ''<ROOT>'' + ('+@staffcontratordata+') + ('+@visitordata+') + ''</ROOT>'''
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    EXEC xp_cmdshell @strdata

    <?xml version="1.0" encoding="UTF-8" ?>
    <ROOT>
    	<record>
    		<Type>Staff</Type>
    		<PersonalNr>179</PersonalNr>
    		<LastName>DO</LastName>
    		<FirstName>John</FirstName>
    		<TelNr/>
    	</record>
    	<record>
    		<Type>Vistor</Type>
    		<PersonalNr>{5}</PersonalNr>
    		<LastName>Future</LastName>
    		<FirstName>Ghost</FirstName>
    		<TelNr/>
    	</record>
    </ROOT>
    

    2018년 6월 14일 목요일 오전 5:25
  • Hi Phyxius7,

    Is this what you want ?

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    --Set @staffVisPres = 'SELECT * FROM (SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'') as t order by t.PersonalNr desc'
    Set @staffVisPres = 'SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'' order by PersonalNr desc FOR XML RAW(''record''),ELEMENTS, ROOT(''ROOT'')'
    
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' +('+@staffVisPres+')'
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    --Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    --print @strdata
    
    
    EXEC xp_cmdshell @strdata

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 5:45
  • Looks like what you're after is something like this

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @staffVisPres = 'SELECT * FROM (SELECT PersonalNr, FirstName, LastName, TelNr, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'') as t order by t.PersonalNr desc FOR XML PATH(''Details'')'
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + CAST(CAST(''<ROOT>'' + ('+@staffVisPres+') AS [*] + ''</ROOT>'' as xml) AS varchar(max))'
    
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    EXEC xp_cmdshell @strdata


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 Phyxius7 2018년 6월 14일 목요일 오후 12:23
    2018년 6월 14일 목요일 오전 8:10
  • Visakh16 to the rescue,

    Thank you for pointing out that I missed the mandatory clause FOR XML. that needs to be set at the end of the select statement.

    Here is the final working query;

    DECLARE 
    	@xmlProlog VARCHAR(600),
    	@staffVisPres VARCHAR(600),
    	@staffcontratordata VARCHAR(600),
    	@XMLcontent  VARCHAR(600),
    	@dir VARCHAR(600),
    	@preFix  VARCHAR(600),
    	@timestamp VARCHAR(600),
    	@fileExten VARCHAR(600),
    	@DirData VARCHAR(600),
    	@servername VARCHAR(600),
    	@dbname VARCHAR(600),
    	@strdata VARCHAR(600)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @staffVisPres = 'SELECT dbo.hPerson.PersonalNr, dbo.hPerson.FirstName, dbo.hPerson.LastName, dbo.hPerson.TelNr AS TelephoneWork, CASE when dbo.aRoom.Outside = ''0'' THEN ''Calamiteitenlijst'' END as MUL011 FROM dbo.hPerson INNER JOIN dbo.aRoom ON dbo.hPerson.RoomLastACFK = dbo.aRoom.RoomID WHERE dbo.aRoom.Outside = ''0'' order by PersID desc FOR XML PATH (''Person'')'
    
    Set @XMLcontent = 'SELECT '''+@xmlProlog+''' + ''<Roor>'' + ('+@staffVisPres+') + ''</root>'''
    
    Set @dir = 'C:\test\'
    Set @preFix = 'mlbl_ba_'
    Set @timestamp = (SELECT FORMAT(getdate(), 'yyyyMMdd_HHmmss'))
    Set @fileExten = '.xml'
    Set @DirData = (SELECT ''+@dir+''+@preFix+''+@timestamp+''+@fileExten+'')
    
    Set @servername = 'WIN-2FDMP8NGCGK\SQLEXPRESS'
    Set @dbname = 'PSlk'
    
    Set @strdata = 'bcp "'+@XMLcontent+'" queryout '+@dirData+' -c -T -S '+@servername+' -d '+@dbname+''
    
    EXEC xp_cmdshell @strdata

    • 답변으로 표시됨 Phyxius7 2018년 6월 14일 목요일 오후 12:23
    2018년 6월 14일 목요일 오후 12:23