SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 2008 for xml path('') not working on 2005		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
181 Posts
Posted - 11/15/2012 :  05:33:24  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi ALl,

Got this statement running perfect on 2008, but doesn't seem to work on 2005. i think it is because "for xml path('')"
what changes do i have to make to get it to work?

SELECT @subject= + Subject, @ordernr=ordernr, @body= + (SELECT top 100 mailbody +'<BR>----- OTHER EMAIL -----<BR>' + Subject + '<BR>' as mailbody
FROM T_Customer_Mailbox
WHERE (userid = @userID) AND (datereply IS NULL) AND (ToUser <> 'system')
ORDER BY id for xml path('')), @from=useremail , @lang =lang
FROM T_Customer_Mailbox
where userid=@userid


thanks a lot

stepson
Yak Posting Veteran

Romania
95 Posts
Posted - 11/15/2012 :  06:00:59  Show Profile  Reply with Quote
Hi,

may be 2005 is not cmpLevel 90 (2005)

check by this select

select compatibility_level from sys.databases where name=DBName

or with sp_dbcmptlevel

to be right it must be 90 lvl
Edited by - stepson on 11/15/2012 06:01:53
Go to Top of Page

mike13
Posting Yak Master

Netherlands
181 Posts
Posted - 11/15/2012 :  08:28:22  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
this it the result:
Valid values of the database compatibility level are 60, 65, 70, 80, or 90.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/15/2012 :  08:46:50  Show Profile  Reply with Quote
You have to specify the database name to see its compatibility level:
sp_dbcmptlevel 'YourDatabaseNameHere'
'
This may not be the problem, but I didn't quite follow what the plus sign in the statements is doing
SELECT @subject= + Subject, @ordernr=ordernr,@body= + (SELECT top 100
Go to Top of Page

mike13
Posting Yak Master

Netherlands
181 Posts
Posted - 11/15/2012 :  08:50:22  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
IT is adding all the email body from the user into 1 output parameter
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/15/2012 :  09:17:00  Show Profile  Reply with Quote
I thought you would need to do "@subject +=" rather than "@subject = +". In any case, the += operator is supported only in SQL 2008 or later. If that indeed is the objective you should change it. The other alternatives are 1 and 3 in the example code below. 
CREATE TABLE #tmp(a VARCHAR(32));
INSERT INTO #tmp VALUES ('abc'),('def');

DECLARE @x VARCHAR(32) = '',
	@y VARCHAR(32) = '',
	@z VARCHAR(32) = ''
	
-- 1
SELECT @x = @x + a FROM #tmp;
-- 2
SELECT @y += a FROM #tmp;
-- 3
SELECT @z = (SELECT a AS [text()] FROM #tmp FOR XML PATH(''));

SELECT @x,@y,@z;

DROP TABLE #tmp;
Go to Top of Page

mike13
Posting Yak Master

Netherlands
181 Posts
Posted - 11/15/2012 :  09:45:33  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
suddenly it works, must have copy n paste wrong stuff to server
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000