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
>
Showing posts with label path. Show all posts
Showing posts with label path. Show all posts
Friday, March 9, 2012
Problem creating XML with FOR XML PATH -Resolved
Using Northwind as an example this is the XML I would like to create:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
<Order orderID="10692" orderdate="10/03/1997" />
<Order orderID="10702" orderdate="10/13/1997" />
<Order orderID="10835" orderdate="01/15/1998" />
<Order orderID="10952" orderdate="03/16/1998" />
<Order orderID="11011" orderdate="04/09/1998" />
</Orders>
</Customer>
</CustomerOrders>
However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?
use northwind
select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
orderID 'Customer/Orders/Order/@.orderID',
convert(nvarchar(10),OrderDate,101) 'Customer/Orders/Order/@.orderdate'
from Customers
join Orders on Customers.CustomerID =Orders.CustomerID
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')
Partial Result of query:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
</Orders>
</Customer>
</CustomerOrders>
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10692" orderdate="10/03/1997" />
</Orders>
</Customer>
</CustomerOrders>...
Mickey:
My first pass at the problem looks like this:
Code Snippet
declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'
declare @.orders table
( customerId varchar(12),
orderId integer,
orderDate datetime
)
insert into @.orders
select 'ALFKI', 10643, '8/25/1997' union all
select 'ALFKI', 10692, '10/3/1997' union all
select 'ALFKI', 10702, '10/13/1997' union all
select 'ALFKI', 10835, '1/15/98' union all
select 'ALFKI', 10952, '3/17/98' union all
select 'ALFKI', 11011, '4/9/98'
--select * from @.orders
select replace(replace(
(
select customerId 'Customer/@.CustomerID',
companyName 'Customer/@.CompanyName',
( select orderId 'Order/@.OrderID',
convert(varchar, orderDate, 101) 'Order/@.orderdate'
from @.orders b
for xml path('')
) 'Customer/Orders'
from @.customers
for xml path(''), root('CustomerOrders')
), '<', '<'), '>', '>')
as theXml
/*
theXml
<CustomerOrders><Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"><Orders><Order OrderID="10643" orderdate="08/25/1997"/><Order OrderID="10692" orderdate="10/03/1997"/><Order OrderID="10702" orderdate="10/13/1997"/><Order OrderID="10835" orderdate="01/15/1998"/><Order OrderID="10952" orderdate="03/17/1998"/><Order OrderID="11011" orderdate="04/09/1998"/></Orders></Customer></CustomerOrders>
*/
/* Manually Reformatted:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order OrderID="10643" orderdate="08/25/1997"/>
<Order OrderID="10692" orderdate="10/03/1997"/>
<Order OrderID="10702" orderdate="10/13/1997"/>
<Order OrderID="10835" orderdate="01/15/1998"/>
<Order OrderID="10952" orderdate="03/17/1998"/>
<Order OrderID="11011" orderdate="04/09/1998"/>
</Orders>
</Customer>
</CustomerOrders>
*/
After some research I was able to find a solution using a subselect.
Here is the code. Thanks to anyone who spent any time on this.
use northwind
select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
(select
orderID '@.orderID',
convert(nvarchar(10),OrderDate,101) '@.orderdate'
from Orders
where Customers.CustomerID =Orders.CustomerID
FOR XML PATH ('Order'), Type ) 'Customer/Orders'
from Customers
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')
Problem creating XML with FOR XML PATH
Using Northwind as an example this is the XML I would like to create:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
<Order orderID="10692" orderdate="10/03/1997" />
<Order orderID="10702" orderdate="10/13/1997" />
<Order orderID="10835" orderdate="01/15/1998" />
<Order orderID="10952" orderdate="03/16/1998" />
<Order orderID="11011" orderdate="04/09/1998" />
</Orders>
</Customer>
</CustomerOrders>
However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?
use northwind
select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
orderID 'Customer/Orders/Order/@.orderID',
convert(nvarchar(10),OrderDate,101) 'Customer/Orders/Order/@.orderdate'
from Customers
join Orders on Customers.CustomerID =Orders.CustomerID
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')
Partial Result of query:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
</Orders>
</Customer>
</CustomerOrders>
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10692" orderdate="10/03/1997" />
</Orders>
</Customer>
</CustomerOrders>...
Mickey:
My first pass at the problem looks like this:
Code Snippet
declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'
declare @.orders table
( customerId varchar(12),
orderId integer,
orderDate datetime
)
insert into @.orders
select 'ALFKI', 10643, '8/25/1997' union all
select 'ALFKI', 10692, '10/3/1997' union all
select 'ALFKI', 10702, '10/13/1997' union all
select 'ALFKI', 10835, '1/15/98' union all
select 'ALFKI', 10952, '3/17/98' union all
select 'ALFKI', 11011, '4/9/98'
--select * from @.orders
select replace(replace(
(
select customerId 'Customer/@.CustomerID',
companyName 'Customer/@.CompanyName',
( select orderId 'Order/@.OrderID',
convert(varchar, orderDate, 101) 'Order/@.orderdate'
from @.orders b
for xml path('')
) 'Customer/Orders'
from @.customers
for xml path(''), root('CustomerOrders')
), '<', '<'), '>', '>')
as theXml
/*
theXml
<CustomerOrders><Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"><Orders><Order OrderID="10643" orderdate="08/25/1997"/><Order OrderID="10692" orderdate="10/03/1997"/><Order OrderID="10702" orderdate="10/13/1997"/><Order OrderID="10835" orderdate="01/15/1998"/><Order OrderID="10952" orderdate="03/17/1998"/><Order OrderID="11011" orderdate="04/09/1998"/></Orders></Customer></CustomerOrders>
*/
/* Manually Reformatted:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order OrderID="10643" orderdate="08/25/1997"/>
<Order OrderID="10692" orderdate="10/03/1997"/>
<Order OrderID="10702" orderdate="10/13/1997"/>
<Order OrderID="10835" orderdate="01/15/1998"/>
<Order OrderID="10952" orderdate="03/17/1998"/>
<Order OrderID="11011" orderdate="04/09/1998"/>
</Orders>
</Customer>
</CustomerOrders>
*/
After some research I was able to find a solution using a subselect.
Here is the code. Thanks to anyone who spent any time on this.
use northwind
select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
(select
orderID '@.orderID',
convert(nvarchar(10),OrderDate,101) '@.orderdate'
from Orders
where Customers.CustomerID =Orders.CustomerID
FOR XML PATH ('Order'), Type ) 'Customer/Orders'
from Customers
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')
Subscribe to:
Posts (Atom)