does sp_xml_concat work

Hi All,
I am trying to get the sp_xml_concat
(http://www.awprofessional.com/artic...02307&seqNum=16)
to work will no luck. Here is the errors I am getting:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.c0'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.
Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@.c0'.
Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 48
A cursor with the name 'hdlcursor' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 49
A cursor with the name 'hdlcursor' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 50
A cursor with the name 'hdlcursor' does not exist.
Server: Msg 8179, Level 16, State 5, Line 23
Could not find prepared statement with handle 0.
Server: Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument,
Line 26
sp_xml_removedocument: The value supplied for parameter number 1 is
invalid.
Can anyone get this bloody thing to work?
Thanks in advance
WoodyHi
There seems to be a missing space before nvarchar(4000).
Try
USE master
GO
IF OBJECT_ID('sp_xml_concat','P') IS NOT NULL
DROP PROC sp_xml_concat
GO
CREATE PROC sp_xml_concat
@.hdl int OUT,
@.table sysname,
@.column sysname
AS
EXEC('
SET TEXTSIZE 4000
DECLARE
@.cnt int,
@.c nvarchar(4000)
DECLARE
@.declare varchar(8000),
@.assign varchar(8000),
@.concat varchar(8000)
SELECT @.c = CONVERT(nvarchar(4000),'+@.column+') FROM '+@.table+'
SELECT @.declare = ''DECLARE'',
@.concat = '''',
@.assign = '''',
@.cnt = 0
WHILE (LEN(@.c) > 0) BEGIN
SELECT @.declare = @.declare + '' @.c''+CAST(@.cnt as nvarchar(15))
+'' nvarchar(4000),'',
@.assign = @.assign + ''SELECT @.c''+CONVERT(nvarchar(15),@.cnt)
+''= SUBSTRING(' + @.column+',''+ CONVERT(nvarchar(15),
1+@.cnt*4000)+ '', 4000) FROM '+@.table+' '',
@.concat = @.concat + ''+@.c''+CONVERT(nvarchar(15),@.cnt)
SET @.cnt = @.cnt+1
SELECT @.c = CONVERT(nvarchar(4000),SUBSTRING('+@.colu
mn+',
1+@.cnt*4000,4000)) FROM '+@.table+'
END
IF (@.cnt = 0) SET @.declare = ''''
ELSE SET @.declare = SUBSTRING(@.declare,1,LEN(@.declare)-1)
SET @.concat = @.concat + ''+''
EXEC(@.declare+'' ''+@.assign+'' ''+
''EXEC(
''''DECLARE @.hdl_doc int
EXEC sp_xml_preparedocument @.hdl_doc OUT, ''+@.concat+''
DECLARE hdlcursor CURSOR GLOBAL FOR SELECT @.hdl_doc AS
DocHandle'''')''
)
')
OPEN hdlcursor
FETCH hdlcursor INTO @.hdl
DEALLOCATE hdlcursor
GO
John
"woody hagar" wrote:

> Hi All,
> I am trying to get the sp_xml_concat
> (http://www.awprofessional.com/artic...02307&seqNum=16)
> to work will no luck. Here is the errors I am getting:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.
> Server: Msg 137, Level 15, State 1, Line 1
> Must declare the variable '@.c0'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'a'.
> Server: Msg 137, Level 15, State 1, Line 3
> Must declare the variable '@.c0'.
> Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 48
> A cursor with the name 'hdlcursor' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 49
> A cursor with the name 'hdlcursor' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 50
> A cursor with the name 'hdlcursor' does not exist.
> Server: Msg 8179, Level 16, State 5, Line 23
> Could not find prepared statement with handle 0.
> Server: Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument,
> Line 26
> sp_xml_removedocument: The value supplied for parameter number 1 is
> invalid.
> Can anyone get this bloody thing to work?
> Thanks in advance
> Woody
>|||I still can't get this to work in my sql server...
Does anyone have any clues?
Frank

Documenting Permissions on SQL Server 7.0 and 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;179158&Product=sq
I found the above article which shows a stored procedure for SQL Server 6.5 which documents all permissions for a particular Database
Is there a way to document permissions on SQL Server 7 and 2000 instances?
Thank you in advancewhat is wrong with exec sp_helprotect (sql 2000).
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Janice Potucek" <janice.potucek@.nextiraone.com> wrote in message
news:89BABFBC-3EC8-4D17-BB3D-51DF05A7820E@.microsoft.com...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;179158&Product=sql
> I found the above article which shows a stored procedure for SQL Server
6.5 which documents all permissions for a particular Database.
> Is there a way to document permissions on SQL Server 7 and 2000
instances?
> Thank you in advance|||Thank you. That was what I was looking for!!!!
I have been trying different combinations of parameters ... what I
would like to see is only the default database, Server Roles and
Database access permissions for a particular user. Is there a way to
get only that information? It is very tedious documenting this
manually.
Thank you in advance,
J Potucek
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!