How to write a string to a file in SQL.

  • Hello All,

    I want to write a varchar(max) string to a file.

    The file should be UTF-8 (and not in a multi byte character format).

    Thanks for your time and attention,

    Ben

  • this knowledge base from MS says that support for bcp.exe to use UTF-8 was added to SQL2014 in service pack 2, so if you are in SQL2012, i think you'll want to use SSIS to export data instead.

    https://support.microsoft.com/en-us/kb/3136780

    SSIS and UTF-8

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d4eb033-2c45-47e4-9e29-f20214122dd3/ssis-change-code-page-1252-ansi-to-utf8-65001?forum=sqlintegrationservices

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ben.brugman (1/10/2017)


    Hello All,

    I want to write a varchar(max) string to a file.

    The file should be UTF-8 (and not in a multi byte character format).

    Thanks for your time and attention,

    Ben

    You will have to elaborate further on the requirements, UTF-8 is unicode hence multi byte!

    😎

    AFAIK, native support for UTF-8 in SQL Server is only on version 2016

  • Eirikur Eiriksson (1/10/2017)

    You will have to elaborate further on the requirements, UTF-8 is unicode hence multi byte!

    😎

    Oops, my bad, I want an 8 bit character set and not a 2 byte or multibyte character set.

    There are a number of reasons for this:

    1. More compact.

    2. On import it should fit within VARCHAR(MAX).

    3. A limited number of characters and therefore a limited number of things that can go wrong.

    Sorry that I used the wrong term (UTF-8), where I meant 1 byte characters.

    I want to use this from within a stored procedure for multiple functions. (Parameters are : path, filename, string with content. So I do not see how to use SSIS from within the stored procedure).

    Ben

    On multiple places on the internet the stored procedure SPWriteStringToFile can be found, this is what I use at the moment, but my current procedure to read the data (OPENROWSET) does produce errors. Stil have to investigate the nature of the errors :Whistling:)

  • ben.brugman (1/10/2017)


    Eirikur Eiriksson (1/10/2017)

    You will have to elaborate further on the requirements, UTF-8 is unicode hence multi byte!

    😎

    Oops, my bad, I want an 8 bit character set and not a 2 byte or multibyte character set.

    There are a number of reasons for this:

    1. More compact.

    2. On import it should fit within VARCHAR(MAX).

    3. A limited number of characters and therefore a limited number of things that can go wrong.

    Sorry that I used the wrong term (UTF-8), where I meant 1 byte characters.

    I want to use this from within a stored procedure for multiple functions. (Parameters are : path, filename, string with content. So I do not see how to use SSIS from within the stored procedure).

    Ben

    On multiple places on the internet the stored procedure SPWriteStringToFile can be found, this is what I use at the moment, but my current procedure to read the data (OPENROWSET) does produce errors. Stil have to investigate the nature of the errors :Whistling:)

    If you are using the SSIS Catalog to deploy your projects, then running them from SQL is relatively easy.

    This is using SSMS's generated script:

    Declare @execution_id bigint

    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'SendSFTPFile.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'My SSIS Project', @use32bitruntime=False, @reference_id=Null

    Select @execution_id

    DECLARE @var0 sql_variant = N'C:\testFile.txt' --Parameter 1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'FilePath', @parameter_value=@var0

    DECLARE @var1 sql_variant = N'TestSTFPServer' --Parameter 2

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'SFTPAccount', @parameter_value=@var1

    DECLARE @var2 smallint = 1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var2

    EXEC [SSISDB].[catalog].[start_execution] @execution_id

    GO

    If you are saving files, you will need to ensure that your kerboros accepts doubling hopping from your SQL Server to use this though.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Tuesday, January 10, 2017 9:39 AM

    If you are using the SSIS Catalog to deploy your projects, then running them from SQL is relatively easy.This is using SSMS's generated script:

    If you are saving files, you will need to ensure that your kerboros accepts doubling hopping from your SQL Server to use this though.

    Thanks for your response, but No I am not using the SSIS Catalog. For this situation I like to deploy only Scripts containing Stored Procedures.

    This is what I understand (sorry) and more important; this is what the people who use my code do understand.

    Maybe I should educate myself more on deployment of the SSIS Catalog. But education myself won't be enough. The code is to be used for 'simple' problems and quite a lot on Ad Hoc basis on different systems and (depending) is removed after usage.

    (Example I do not understand the kerboros part, <very said emoticon to be inserted here> )

    Thanks for you contribution,
    Ben

  • ben.brugman - Thursday, January 12, 2017 7:15 AM

    Thom A - Tuesday, January 10, 2017 9:39 AM

    If you are using the SSIS Catalog to deploy your projects, then running them from SQL is relatively easy.This is using SSMS's generated script:

    If you are saving files, you will need to ensure that your kerboros accepts doubling hopping from your SQL Server to use this though.

    Thanks for your response, but No I am not using the SSIS Catalog. For this situation I like to deploy only Scripts containing Stored Procedures.

    This is what I understand (sorry) and more important; this is what the people who use my code do understand.

    Maybe I should educate myself more on deployment of the SSIS Catalog. But education myself won't be enough. The code is to be used for 'simple' problems and quite a lot on Ad Hoc basis on different systems and (depending) is removed after usage.

    (Example I do not understand the kerboros part, <very said emoticon to be inserted here> )

    Thanks for you contribution,
    Ben

    SSIS might not always be your solution, don't worry. Some people don't even have it installed on their SQL instance, it was more an example that you can start an SSIS package via SQL.

    With regards to kerboros, my point was due to something that is refered to as "double hopping", as you would be saving a file in SSIS via a package you started via SQL. i'm not going to pretend I'm a Network Admin, and therefore explain it. Although I understand it, i'm pretty sure my explanation would be incorrect, or "misguided". If you want to have a look, I found an article here
    about it. In thea rticle they define Double hop as:

    Kerberos Double Hop is a term used to describe our method of maintaining the client’s Kerberos authentication credentials over two or more connections. In this fashion we can retain the user’s credentials and act on behalf of the user in further connections to other servers.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Thursday, January 12, 2017 7:32 AM

    ben.brugman - Thursday, January 12, 2017 7:15 AM

    Thom A - Tuesday, January 10, 2017 9:39 AM

     Although I understand it, i'm pretty sure my explanation would be incorrect, or "misguided". If you want to have a look, I found an article here

    Maybe a problem with the new forum format, but when I click on the 'here', I get into this current thread. Cutting and pasting does work though. Probably a teething problem with the new forum format ?
    Did check the article. Bit out of my comfort zone.

    Ben

  • To export a column to a file>
    I took this example from 
    http://mytechmantra.com/LearnSQLServer/How-to-Export-records-from-SQL-Server-to-Text-File-using-BCP/

    EXEC xp_cmdshell 'bcp "SELECT Name FROM [AdventureWorks2012].[Person].[CountryRegion]" queryout "C:\Temp\CountryRegion.txt" -T -c -t '

    You can read about what the different switches are here : https://msdn.microsoft.com/en-us/library/ms162802.aspxOn the topic of kerberos I understand it to be a pass for a user to gain access to a resource not at the place at which you are pulling data. Example.. 
    if you call a stored proc that you have access to and access to the data, and that proc also uses a linked server to pull a column, that extra hop may not work under your credentials likely. So Kerberos allows you when you connected to the server where the stored proc is to have a temporary pass to access the column from that linked server if the stored proc has access to it (the column). I'm no expert but that is my understanding. You can actually do a Youtube search and there are videos on the topic (some lengthy).

    ----------------------------------------------------

  • And of course let me not forget... In SSMS you can simply type CTLR+SHIFT+F and that will put the results of the query to a file you define at run time. To automate this of course refer to the BCP example above. You can encapsulate it in a agent job and run on a schedule.

    ----------------------------------------------------

  • Still searching for a solution, but I have a (small) bit of information.

    Using:
    exec SP_WritebinaryToFile
    or
    exec SP_WriteStringToFile

    I can write anything to a file, or a textfile. But the problem is that the file starts with (FF FE). This is the Bom 'prefix' which tells that the file is UCS-2 Little Endian. For the WriteStringToFile this is correct, there are two bytes for each character.

    The SP_WritebinaryToFile, a string (varchar(max)) is converted to a varbinary, which is then written with this stored procedure. The resulting file contains the string as it was, but there is a prefix (FF FE). In Notepad++ the text looks Chinese.
    The code for the SP_WritebinaryToFile is attached, I got this from somewhere on the Web.

    Any suggestions how to get a 'normal' text file. This is without the 'prefix' or with the prefix 'EF BB BF', which denotes that this is an UTF-8 textfile ? 

    Thanks for your time and attention,
    Ben

    This code comes from the web, I made small 'alterations' so that a binairy file could be written.
    Problem the Bom 'FF FE' gets added in front of the file.

    USE [master]
    GO
    /****** Object: StoredProcedure [dbo].[SP_WriteBinaryToFile]  Script Date: 01/31/2017 17:47:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SP_WriteBinaryToFile]
    (
    -- @String Varchar(max), --8000 in SQL Server 2000
    @BinaryString varbinary(max), --8000 in SQL Server 2000
    @Path VARCHAR(255),
    @Filename VARCHAR(100)

    --
    )
    AS
    BEGIN
    DECLARE @objFileSystem int
       ,@objTextStream int,
    @objErrorObject int,
    @strErrorMessage Varchar(1000),
      @Command varchar(1000),
      @hr int,
    @fileAndPath varchar(80)
    set nocount on

    select @strErrorMessage='opening the File System Object'
    EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

    -- sp_Helptext sp_OAMethod
    Select @FileAndPath=@path+'\'+@filename
    if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
    , @objTextStream OUT, @FileAndPath,2,True

    ------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------

    if @HR=0 Select @objErrorObject=@objTextStream,
    @strErrorMessage='writing to the file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @BinaryString

    if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

    if @hr<>0
    begin
    Declare
    @Source varchar(255),
    @Description Varchar(255),
    @Helpfile Varchar(255),
    @HelpID int

    EXECUTE sp_OAGetErrorInfo @objErrorObject,
    @source output,@Description output,@Helpfile output,@HelpID output
    Select @strErrorMessage='Error whilst '
    +coalesce(@strErrorMessage,'doing something')
    +', '+coalesce(@Description,'')
    raiserror (@strErrorMessage,16,1)
    end
    EXECUTE sp_OADestroy @objTextStream
    EXECUTE sp_OADestroy @objFileSystem

    END

  • What's wrong with the BCP solution, Ben?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden - Tuesday, January 31, 2017 12:18 PM

    What's wrong with the BCP solution, Ben?

    The short anwser to that is : It's not in my comfort zone.

    Next to :
    this knowledge base from MS says that support for bcp.exe to use UTF-8 was added to SQL2014 in service pack 2, so if you are in SQL2012, i think you'll want to use SSIS to export data instead.
    And I would like a solution within the SQL-server environment. For example compleet within a stored procedure.

    Some month's ago I used one of the above stored procedures to extract over a 100 000 pdf files from a document system, which was implemented using SQL-server. So my thoughts were if a 'complex' document like a PDF can be extracted, how difficult can it be to extract a Ascii (Or UTF-8) file.

    So I was looking for an 'internal'/'simple' fix and I think I have found a solution, been testing with simple files and have still to do some additional testing. But see the code change below:


    -- Change the code:
    if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
    , @objTextStream OUT, @FileAndPath,2,True

    -- Into :
    if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
    , @objTextStream OUT, @FileAndPath,2,False

    A BOM (Byte Order Mark) can be appended to the beginning of the string.

    UTF-8[t 1]EF BB BF239 187 191
    UTF-16 (BE)FE FF254 255þÿ
    UTF-16 (LE)FF FE255 254ÿþ

    And this seems to work for me.

    All thanks for your time and attention.

    For others reading this thread and seeking a similar solution:
    Use the SP_WriteBinaryToFile change the True into a False.
    Convert a number of characters. (For example a number of chars can be converted by inserting a char(195) value and then subtracting 64 of the character value). A UTF-8 Bom marker (239,187,191) can be appended in front of the string.
    This give more (or full) control over the content of the file to be written.
    And do some extensive testing. (I stil have to perform the extensive testing).

    Ben

  • Heh... if that's all there is to UTF-8, you have to wonder what the hell MS has been waiting on.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden - Wednesday, February 8, 2017 4:45 AM

    Heh... if that's all there is to UTF-8, you have to wonder what the hell MS has been waiting on.

    No this is not all there is to UTF-8. But at least this gives the building block to write an UTF-8 file from a varchar string. You still have to include/convert/adjust/replace a number of characters which are represented different in the varchar than in UTF-8.
    But because there are only 256 different possible values for a character in a varchar string. And most map directly (values under 128 I would say), a number of values map to 195 followed by the same value minus 64. I haven't investigated beyond that.
    I would say that a 100 percent conversion from varchar to UTF-8 is possible, and this helps to output that string.

    Thanks for your support,
    Ben

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply