Help! Got an error while do the replication update

Dear all,
I got the following error while doing the replication in updating the date,
I found that a column's datatype is NTEXT, but i have other table also are
have columns set to NTEXT datatype and it works.
Does anyone have any idea on it?
"Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table."
can you post your schema here for the problem table? Also do you recall what
update/insert/delete caused this problem?
Perhaps try to restart your agent and log according to:
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Madstan" <stanley.chong@.hk.mrspedag.com> wrote in message
news:uQdRmGKvEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> I got the following error while doing the replication in updating the
date,
> I found that a column's datatype is NTEXT, but i have other table also are
> have columns set to NTEXT datatype and it works.
> Does anyone have any idea on it?
> "Only text pointers are allowed in work tables, never text, ntext, or
image
> columns. The query processor produced a query plan that required a text,
> ntext, or image column in a work table."
>

Help with XQuery

I am trying to query an XML Datatype column in SQL Server, but getting no data back, and I suspect the XQuery but don't know what I am doing wrong:

SELECT

nref.value('AdvTxnID[1]', 'integer') AdvTxnID,

nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode

FROM

myData CROSS APPLY [xmldata].nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">

<AdvanceTxn>

<AdvTxnID>4125281</AdvTxnID>

<SvcSysCode>MS</SvcSysCode>

<LoanID>8484664</LoanID>

<AdvTypeCode>P&amp;I</AdvTypeCode>

<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>

<AdvTxnAmt>.04</AdvTxnAmt>

<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>

</AdvanceTxn>

<TxnCount>1</TxnCount>

</AdvanceTxnDoc>

What am I doing wrong?

Need to declare namespace in your xquery. Two way to do it as following example:

declare @.x xml
set @.x =
N'<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">
<AdvanceTxn>
<AdvTxnID>4125281</AdvTxnID>
<SvcSysCode>MS</SvcSysCode>
<LoanID>8484664</LoanID>
<AdvTypeCode>P&amp;I</AdvTypeCode>
<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>
<AdvTxnAmt>.04</AdvTxnAmt>
<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>
</AdvanceTxn>
<TxnCount>1</TxnCount>
</AdvanceTxnDoc>'

with xmlnamespaces(default 'http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc')
SELECT
nref.value('AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

SELECT
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc";/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

|||

Worked great. Thanks!

-Kory

Help with user defined functions

Dear all,

I was given a project to transfer our database into sql server database.

In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .

Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \\let int4 be a user definded function.

If i can’t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?

Thanks in advance,

Best regards,

Hellen

Then you are out of luck, the call to functions in SQL Server always have to use the owner prefix (in your case appearantly dbo).

Jens K. Suessmeyer.

http://www.sqlserver2005.de