Generating a Text file using sp_OAMethod does not writeline if the database has Chinese character
-
I am generating a file using sp_OAMethod. The file gets generated with the Chinese character as long as the Data that is being read from the database does not have any Chinese character. Procedure is :
/********************************************************
** Grant Permissions to user or to the Group
** grant all on sp_OADestroy to public
** grant all on sp_OAMethod to public
** grant all on sp_OACreate to public
** grant all on sp_OAGetErrorInfo to public
********************************************************
** The above is only for giving permissions on these system stored procedures.
********************************************************/
create proc createfile
as
begin
Set concat_null_yields_null OFF
set ansi_padding ON
declare @gid int,
@place_of_birth nchar(20),
@body_character nchar(20),
@last_name nchar(30),
@first_name nchar(30)
declare @ID nvarchar(50),
@BIRTHPLACE nvarchar(50),
@BODYCHARACTER nvarchar(50),
@LAST_NAME nvarchar(50),
@FIRST_NAME nvarchar(50),
@record_deli nchar(2),
@text nvarchar(2000)
select @ID = 'ID NO. =',
@BIRTHPLACE = N'Ô¼®=',
@BODYCHARACTER = N'ÌåòÌØÕ÷=',
@LAST_NAME = N'Ó¢ÎÄÐÕ=',
@FIRST_NAME = N'Ó¢ÎÄÃû='
select @record_deli = N'¡£'
create table #visitors
( gid int null,
place_of_birth char(20) null,
body_character char(20) null,
last_name char(30) null,
first_name char(30) null)
insert into #guests
( gid ,
place_of_birth ,
body_character ,
last_name ,
first_name
 
select
gid,
place_of_birth,
body_character ,
last_name ,
first_name
from visitors
while 0 = 0
begin
select @gid = min(gid)
from #visitors
where gid > @gid
if @gid is NULL or @@rowcount = 0
begin
break
end
select @place_of_birth = place_of_birth,
@body_character = body_character,
@last_name = last_name,
@first_name = first_name
from #visitors
where gid = @gid
select @text = @ID + convert(char(6),@gid) + @record_deli
select @text = @text + @BIRTHPLACE + rtrim(@place_of_birth) + @record_deli
select @text = @text + @BODYCHARACTER + rtrim(@body_character) + @record_deli
select @text = @text + @LAST_NAME + rtrim(@name_last) + @record_deli
select @text = @text + @FIRST_NAME + rtrim(@name_first) + @record_deli
execute sp_appendtofile @filename = "c:\visit.txt", @text1 = @text /* The file will get generated on C: where SQL is installed */
end
end
create proc sp_appendtofile( @filename varchar(255),
@text1 nvarchar(2000) )
as
begin
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @filename, 8, 1
--IF @OLEResult <> 0 PRINT 'OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @text1
--IF @OLEResult <> 0 PRINT 'WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
end
go
************************************************************
Once I have a data with Chinese Name, then that particular record is not written on to the file. My database collation is
Collation=SQL_Latin1_General_CP1_CS_AS, SQLSortOrder=51
I cannot change the collation of the database.
Can someone help......
-
This seems shaky at best, though more like outright dangerous. Are you really granting public access to the sp_OAxxx procs? You might as well give the server away on a silver platter.
At first glance it seem like a very complicated way to write data to the filesystem...
To the question then.. I'm sorry to say I have no experience with chinese characters at all, but.. have you tried using unicode for the columns with chinese chars?
/Kenneth
-
Dear Kenneth
It is not actually giving sp_OAxxxx to public. As an example I have given. I have tried with Unicode, it does not work. However, I have managed to find an alternate way to handle the same using bcp.
Thanks.
-
Glad you found a way that works.
bcp would have been my choice as well
/Kenneth
-
Hi,
I want to use sp_OACreate to write text file on a network drive.
Does any one have any idea if this is achievable?
thanks
Nutan
-
Hi Sandeep.
Thanks for your reply 🙂
I am able to write text file on machines in LAN using sp_OACreate and other objects. but i want to write text files on network mapped drive. I get this error when i try to write the file.
The 'XXXSP_Name' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
Please let me know if have input on this.
Thanks,
Nutan.
-
Hi,
May I know how did u achive this using BCP.
I am trying to do the same using sp_OACreate 'Scripting.FileSystemObject'
But when there are Chinese characters, it writes ????? in the text file.
Here is my code:
CREATE PROCEDURE dbo.sp_CountryData_AppendToFile(@FileName varchar(255), @Text1 nvarchar(4000))
AS
SET nocount on -- to enhance performance
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, -1
IF @OLEResult <> 0 PRINT 'OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply