WSH write Chinese to SQLServer 1

[dexeloper]

I have a WSH vbscript that reads one SQLServer table and writes to another. Just one nvarchar column per table. Some of the 'from' data is in Chinese and a wscript.echo shows the Chinese values fine. However, when I write to the second table Chinese characters come out as '???????'.
This is, essentially, the code:

set impConn = Wscript.CreateObject("ADODB.Connection")
call impConn.Open("Provider=SQLOLEDB;charset=utf8;Data Source=S1;database=SQLS;User ID=a;Password=b")
set impRS = Wscript.CreateObject("ADODB.RecordSet")

call impRS.Open("select * from T1", impConn)
while not impRS.eof
	wscript.echo(impRS("title"))
	impConn.execute("insert into T2 (title) values('" & impRS("title") & "')")
	call impRS.MoveNext()
wend
call impRS.Close()

Any ideas gratefully accepted

 

[MakeItSo]

Why first pull the full data and then insert the only title field into a new table?
Anyway: I recommend you simply do a "Select Into" query and let SQLServer handle the Chinese.
;-)


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[dexeloper]

What I've shown is just a cutdown of a much bigger script. The point, I think, is that the 'read' brings in the Chinese fine but the 'write' doesn't. If I run an 'insert select' in SQLServer query analyser it works fine.

 

[strongm]

What happens if you do, as I rather suspect VBA's string concatentation:

wscript.echo "insert into T2 (title) values('" & impRS("title") & "')"

 

[strongm]

(or, more accurately, VBscripts inability to handle Unicode strings properly)

 

[dexeloper]

The 'insert' instruction appears fine.

 

[strongm]

Hmm. Technically VBscript should mangle it the moment it retrieves it from the ADO object (ADO maintains the Unicode)

 

[MakeItSo]

Ah, me dummy!
If I'm not very mistaken, you need to add the Unicode "N":

impConn.execute("insert into T2 (title) values([COLOR=#CC0000]N[/color]'" & impRS("title") & "')")

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[dexeloper]

MakeItSo you're a bloody star. One in a million. Everything they say about you is lies. The crate of ale is in the post and the lobster sandwiches will follow shortly.
I WILL have a good weekend. Thanks again.

 

[MakeItSo]

*munch*
Greatly appreciated, thanks!


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.