February 6, 2015 at 3:17 pm
I have really struggled trying to get this to work.
What I am doing -
I create a script of the data that is currently in a SQL DB. That will be used to update or insert data in a SQL CE DB. There are several tables and lots of data. Depending on the data count I create multiple files to run. Here is an example of what I am trying to run
SET NOCOUNT ON
BEGIN
DECLARE @Q char(1)
SET @Q = ''''
DECLARE @SQL as Nvarchar(Max)=''
DECLARE @OutputFile varchar(1500)
SET @OutputFile = 'FooHeader.sqlce'
SET @OutputFile = @path+@OutputFile
DECLARE @TempTable TABLE (TheId uniqueidentifier, IsPosted bit)
INSERT INTO @TempTable
SELECT
[ID],
0 as IsPosted
FROM dbo.foo
DECLARE @Name [nvarchar](50), @ID uniqueidentifier
WHILE EXISTS (
SELECT 1
FROM @TempTable
WHERE IsPosted = 0)
BEGIN
SELECT @ID=[ID]
,@Name=[Name]
FROM [dbo].[foo] M
INNER JOIN @TempTable T ON T.TheId=M.ID AND T.IsPosted = 0
SET @SQL =@SQL+'
UPDATE [SQLCETableFoo]
SET [Name] = '+@Q+CONVERT(Varchar(500),@Name)+@Q+'
WHERE [D] = '+@Q+CONVERT(Varchar(500),@ID)+@Q+';
GO
INSERT INTO [SQLCETableFoo]
([ID]
,[Name])
SELECT '+@Q+CONVERT(Varchar(500),@ID)+@Q+'
, '+@Q+CONVERT(Varchar(500),@Name)+@Q+'
WHERE NOT EXISTS (SELECT 1 FROM SQLCETableFoo WHERE [ID] = '+@Q+CONVERT(Varchar(500),@ID)+@Q+');
GO'
UPDATE @TempTable
SET IsPosted =1
WHERE TheId =@ID
END --Outer While
EXEC FILE_WRITE_TEXT @file_name =@OutputFile
,@text =@SQL
,@append = 0
This works. I can put special characters in the table data, it gets into the file, and then when I run this using SQLCECMD I can push the UTF-8 unicode format into the SQLCE DB. Characters like degree ° and cool tilda ≈ show up.
The File Write Sproc looks like the following. I have already started hacking at it to get it to be unicode vs ansi. Thing is I am missing something on the append.
ALTER procedure [dbo].[file_write_text](@file_name varchar(1000),
@text nvarchar(max),
@append bit = 0)
as
/*
exec file_write_text 'c:\foo.txt','hello'
exec file_write_text 'c:\foo.txt',' world',1
*/
declare @fso int
declare @ts int
declare @rv int
exec @rv = sp_oacreate "scripting.filesystemobject", @fso OUTPUT, 1
if @rv <> 0 goto ErrorCode
if @append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @rv = sp_oamethod @fso,"opentextfile", @ts OUTPUT, @file_name, 8,true
if @rv <> 0 goto ErrorCode
end
else
begin
--create a new text file, overwriing if necessary
exec @rv = sp_oamethod @fso,"createtextfile", @ts OUTPUT, @file_name, 2,true
if @rv <> 0 goto ErrorCode
end
exec @rv = sp_oamethod @ts,"write",null ,@text
if @rv <> 0 goto ErrorCode
exec @rv = sp_oamethod @ts,"close"
if @rv <> 0 goto ErrorCode
exec sp_oadestroy @ts
exec sp_oadestroy @fso
return 0
ErrorCode:
declare @es varchar(512)
declare @ed varchar(512)
exec sp_oageterrorinfo null, @es OUTPUT, @ed OUTPUT
raiserror(@ed,16,1)
exec sp_oadestroy @ts
exec sp_oadestroy @fso
return 1
The process above works as I said. If I switch the file write from posting once vs posting each time I have a record I get 䑅㘹〲䔭䅁ⴱ〱㘰㠭㔷ⴸ㘱䌶㔶䘱䐰㉆⤧഻ ††††佇
I believe the append or "write" is where the issue is. I just can't seem to figure out what I am doing wrong. Here is what I want the procedure to look like -
SET NOCOUNT ON
BEGIN
DECLARE @Q char(1)
SET @Q = ''''
DECLARE @SQL as Nvarchar(Max)
DECLARE @OutputFile varchar(1500)
SET @OutputFile = 'FooHeader.sqlce'
SET @OutputFile = @path+@OutputFile
EXEC FILE_WRITE_TEXT @file_name =@OutputFile
,@text =''
,@append = 0
DECLARE @TempTable TABLE (TheId uniqueidentifier, IsPosted bit)
INSERT INTO @TempTable
SELECT
[ID],
0 as IsPosted
FROM dbo.foo
DECLARE @Name [nvarchar](50), @ID uniqueidentifier
WHILE EXISTS (
SELECT 1
FROM @TempTable
WHERE IsPosted = 0)
BEGIN
SELECT @ID=[ID]
,@Name=[Name]
FROM [dbo].[foo] M
INNER JOIN @TempTable T ON T.TheId=M.ID AND T.IsPosted = 0
SET @SQL =@SQL+'
UPDATE [SQLCETableFoo]
SET [Name] = '+@Q+CONVERT(Varchar(500),@Name)+@Q+'
WHERE [D] = '+@Q+CONVERT(Varchar(500),@ID)+@Q+';
GO
INSERT INTO [SQLCETableFoo]
([ID]
,[Name])
SELECT '+@Q+CONVERT(Varchar(500),@ID)+@Q+'
, '+@Q+CONVERT(Varchar(500),@Name)+@Q+'
WHERE NOT EXISTS (SELECT 1 FROM SQLCETableFoo WHERE [ID] = '+@Q+CONVERT(Varchar(500),@ID)+@Q+');
GO'
EXEC FILE_WRITE_TEXT @file_name =@OutputFile
,@text =@SQL
,@append = 1
UPDATE @TempTable
SET IsPosted =1
WHERE TheId =@ID
END --Outer While
END --Procedure Wrapper
I apologize about the length of the post. I believe the issue is here:
if @append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @rv = sp_oamethod @fso,"opentextfile", @ts OUTPUT, @file_name, 8,true
if @rv <> 0 goto ErrorCode
end
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply