Problem with sp_OAMethod 'WriteLine for special characters

I use sp_OAMethod 'WriteLine' to write some VARCHAR data into a file. The problem is, that when the VARCHAR data contains special characters like "?ü?", they not not correctly written and the file, if its a xml file is invalid.

VARCHAR data is "Datei abholen\Dateiname prüfen"

Statements are:

SET @.XMLComment = '<!-- TestCase :' + @.param_TestCase + ' -->'

execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', Null, @.XMLComment

Output is: <!-- TestCase Datei abholen\Dateiname pr?->

Is there any workaround to have it read/write special characters correctly in the file?

Any help is appreciated.

Thanks.

Added later:

I found a way to do the same with bcp, but also that utility has problems with "??ü" characters.

Try passing the charecters by using the CHAR function to the @.XMLcomment variable.

For instance

CHAR(228)

would produce ?

Problem with sp_executesql

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @.au_lname
',
N'@.au_lname nVarChar',
@.au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

ThanksChange your code as follows:

N'@.au_lname nVarChar', -->>> N'@.au_lname nVarChar(5)',|||Thank you very much for feedback!

Problem with select into querry

I have two database on same server. I want write a querry,witch qering the data from 1st database, than create a new table on the 2nd database.

But the in clause after the into clause permanently generate error.

Here is the query’s little part:

Select …..

into Backup in ’Test.mdb’ from …

I try wrote other ways

in ’Test’

in ’dbo.Test’

in dbo.Test

but none of them work

its only work, when i not want pass for the second database

How can i pass the result for the 2nd Database

What database engine are you using? You're posting this in the MS SQL forum, yet I see the "Test.mdb" which looks like an Access database.

If it's MS SQL, use the syntax like:

SELECT ... INTO SERVER2.DBname.dbo.TableName

You will also need to add SERVER2 as a linked server to SERVER1 (read BOL on sp_addlinkedserver).

|||

May you post your complete SQL Statement? Perhaps you have an syntax mistake (I never user into to query a access-database).

ralph

|||

I use MS SQL 2005

here the query, sorry the column names is hungarian

the server name is sysnapsys and synapsys have the two database

select

t1a.azonosító,

t1a.nv,

t1a.lakcím_irsz,

t1a.lakcím_helysg,

t1a.lakcím_uhea,

t1a.szlacím_nv,

t1a.szlacím_irsz,

t1a.szlacím_helysg,

t1a.szlacím_uhea,

t3.telepíts_id?pontja,

t4.szerzodes_megszunt_idopont,

t9.befizetett_eloleg,

t9.havi_díj_fizetsi_mód

into SzerzWorth

from t0, t1a, t3, t4, t9

where t0.t1a_id = t1a.idn and t0.t4_id = t4.idn

and t0.t9_id = t9.idn and t0.t3_id = t3.idn

this querry is working. If i want add the in clause after the "into SzerzWorth", then the server throw me an error. I try this methods:

" into SzerzWorth in 'Teszt' "

" into SzerzWorth in 'dbo.Teszt' "

" into SzerzWorth in 'synapsys.Teszt' "

" into SzerzWorth in 'synapsys.db.Teszt' "

but permanently throw me the error:

"Msg 156, Level 15, State 1, Line 15

Incorrect syntax near the keyword 'in'. "

|||I posted above|||hi mr. saint,

a SELECT INTO <table> IN is not a correct syntax. The IN keyword expects a list(s) of values e.g: IN (1, 2, 3) and usually used in WHERE clauses to check for data w/c matches the lists in IN.i.e.: SELECT * FROM table WHERE field1 IN (n1, n2.. n2n).. the query should be:

SELECT <fields>
INTO SzerzWorth
FROM t0, t1a, t4, t9

INSERT INTO synapsys.db.Teszt (fields...)
SELECT fields..
FROM SzerzWorth

or. simply:
INSERT INTO sysnapsys.db.Teszt(fields..)
SELECT <fields>
FROM t0, t1a, t4, t9

HTH|||I don′t know if that is the problem, but as I never use umlauts or special characters in the object names you should put the names in brackets, because the syntax look quite ok:

select

t1a.[azonosító],

t1a.[nv],

t1a.[lakcím_irsz],

t1a.[lakcím_helysg],

t1a.lakcím_uhea,

t1a.[szlacím_nv],

t1a.[szlacím_irsz],

t1a.[szlacím_helysg],

t1a.[szlacím_uhea],

t3.[telepíts_id?pontja],

t4.[szerzodes_megszunt_idopont],

t9.[befizetett_eloleg],

t9.[havi_díj_fizetsi_mód]

into SzerzWorth

from t0, t1a, t3, t4, t9

where t0.t1a_id = t1a.idn and t0.t4_id = t4.idn

and t0.t9_id = t9.idn and t0.t3_id = t3.idn

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem with PIVOT

I am currently trying to write a query that I can use in C# to produce a score table for MotorCycle races - as I know that using the PIVOT command you have to specify the column names.

I have 3 seperate tables - which I join to produce a table which I then try to rearrange with PIVOT as below.

SELECT Name,RaceName,Position FROM

(SELECT dbo.Rider.Name, dbo.Race.RaceName, dbo.Position.Position FROM

dbo.Rider INNER JOIN dbo.Position ON dbo.Rider.ID = dbo.Position.Rider_ID INNER JOIN dbo.Race ON dbo.Race.ID = dbo.Position.Race_ID) AS A

PIVOT(A.Name FOR A.RaceName IN ([Brands Hatch],[Silverstone])) AS P

When I try to run the query it says I have an error near FOR. I have taken the syntax from msdn and other sites.

Hopefully someone can help!!!!

Thanks!

PIVOT requires an aggregate function to be in there before the 'FOR' bit. So if you had MAX(A.Name), then you might get what you want.

Rob