January 10, 2017 at 6:15 am
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
January 10, 2017 at 7:06 am
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
Lowell
January 10, 2017 at 7:07 am
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
January 10, 2017 at 7:49 am
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:)
January 10, 2017 at 9:39 am
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
January 12, 2017 at 7:15 am
Thom A - Tuesday, January 10, 2017 9:39 AMIf 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
January 12, 2017 at 7:32 am
ben.brugman - Thursday, January 12, 2017 7:15 AMThom A - Tuesday, January 10, 2017 9:39 AMIf 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
January 12, 2017 at 8:27 am
Thom A - Tuesday, January 10, 2017 9:39 AMAlthough 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
January 13, 2017 at 12:55 pm
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).
----------------------------------------------------
January 13, 2017 at 1:00 pm
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.
----------------------------------------------------
January 31, 2017 at 9:49 am
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
January 31, 2017 at 12:18 pm
What's wrong with the BCP solution, Ben?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2017 at 8:47 am
Jeff Moden - Tuesday, January 31, 2017 12:18 PMWhat'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 BF | 239 187 191 |  |
| UTF-16 (BE) | FE FF | 254 255 | þÿ |
| UTF-16 (LE) | FF FE | 255 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
February 8, 2017 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2017 at 8:15 am
Jeff Moden - Wednesday, February 8, 2017 4:45 AMHeh... 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