Problem getting select data set for XML Auto, Elements into a table variable -

I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.If I put it like

select @.l_variable = (select my sql statement for Auto, Elements)
it gives me syntax error.

I can't do a select into a temp table from my select statement for XML Auto.

I can't use select into as well.

I can't enclose my entire select statment in paranthesis to put it in a table variable.

How do I proceed?|||Here is the actual query. I am trying to get the result of the select statement into the table variable:

declare @.i_Customer_Id int
,@.i_Role_ID int
,@.i_Base_URL varchar(255)

declare @.l_XML_Table table (XML_String varchar (8000))

select @.i_Customer_Id = 10
,@.i_Role_ID = 1
,@.i_Base_URL = 'http://www.NewWebSite.com'

select
MM.Module_Description Topic_Type
,PM.Page_Description Title
,@.i_Base_URL + PM.Page_URL URL
from
Role_Page_Map RPM
,Module_Master MM
,Page_Master PM

where RPM.Role_ID = @.i_Role_ID
and RPM.Customer_Id = @.i_Customer_Id

and RPM.Page_ID = PM.Page_ID
and PM.Module_ID = MM.Module_ID

for XML Auto, Elements|||You can't select FOR XML into a table. See BOL for more information.|||That's true. However can't the resultant xml string be taken in a varchar variable as well?

problem deleting file

Hi all,
I need to delete files using UNC path of the file in xp_cmdshell
procedure.
The @.FileUNCPath variable contains the UNC path of the file and it has
correct value.
The SQL server service and Sql server agent service are running under
CSQL5 windows account. CSQL5 has full access to the files I am trying to
delete. But when I try to delete the file using the following statements,
@.Result is always 1 and the file does not get deleted.
So please help. Thanks in advance.
SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
EXEC @.Result = master..xp_cmdshell @.ShellCommandYour OS use "delete" or "del", check the correct command to be used.
AMB
"sql" wrote:

> Hi all,
> I need to delete files using UNC path of the file in xp_cmdshell
> procedure.
> The @.FileUNCPath variable contains the UNC path of the file and it has
> correct value.
> The SQL server service and Sql server agent service are running under
> CSQL5 windows account. CSQL5 has full access to the files I am trying to
> delete. But when I try to delete the file using the following statements,
> @.Result is always 1 and the file does not get deleted.
> So please help. Thanks in advance.
> SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
> EXEC @.Result = master..xp_cmdshell @.ShellCommand
>
>|||Try these examples to see if you get any more info on what the command is
actually doing:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (FName)
exec @.Return = master..xp_cmdshell @.Cmd
DECLARE @.cmd sysname, @.var sysname
SET @.var = 'Hello world'
SET @.cmd = 'echo ' + @.var + ' > var_out.txt'
EXEC master..xp_cmdshell @.cmd
Andrew J. Kelly SQL MVP
"sql" <donotspam@.nospaml.com> wrote in message
news:eKsGUWsOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I need to delete files using UNC path of the file in xp_cmdshell
> procedure.
> The @.FileUNCPath variable contains the UNC path of the file and it has
> correct value.
> The SQL server service and Sql server agent service are running under
> CSQL5 windows account. CSQL5 has full access to the files I am trying to
> delete. But when I try to delete the file using the following statements,
> @.Result is always 1 and the file does not get deleted.
> So please help. Thanks in advance.
> SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
> EXEC @.Result = master..xp_cmdshell @.ShellCommand
>

Problem correctly setting a local_variable

Hi,

I've been trying to write a stored proc to no avail. I'm trying to set a local variable and every time I try to execute the stored proc I get the following error:

Running [dbo].[StoredProcedure2].

Conversion failed when converting the nvarchar value 'TEST2' to data type int.

No rows affected.

(0 row(s) returned)

@.RETURN_VALUE =

Finished running [dbo].[StoredProcedure2].

Here's the code (which I skimmed down to try and pinpoint where the issue was).

Initially, I was trying to assign a numeric string value stored as nvarchar(10) and anytime the value would be over '2147999999' (in that area) it would bomb... Now even this simple code won't work and I can't understand why. If anyone would be kind enough to enlighten me ;-)

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

RETURN @.ME

u must use Print/Select instead of Return.

fromBOL

RETURN

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Syntax

RETURN [ integer_expression ]

alter PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

select @.me

Madhu

|||

The value following the RETURN keyword MUST be an integer value.

Strings are not allowable RETURN values.

Instead use:

Code Snippet

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

SELECT @.Me

GO

|||

It always better to use OUTPUT parameter, or Select statement to get the single row & column data from the SP. Return only supports integer.

NOTE:

PRINT – you can’t get the result on the UI. It will get suppressed.