exec master..xp_cmdshell @data

[DrSql]

I am trying to run this statement but I am getting Access Denied. I was able to run this job from the DOS without any issues. ANy suggestions...

declare @data
select
@data= 'copy F:\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak'

exec master..xp_cmdshell @data

\= /

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[mrdenny]

Make sure that the account that the SQL Server starts under has rights to both locations.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[amrita418]

Shot in the dark. Can you try if either of these work?

exec master..xp_cmdshell 'copy F:\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak'

or

declare @data sysname
set 	@data = 'copy F:\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak'

exec 	master..xp_cmdshell @data

Regards,
AA

 

[DrSql]

Thanks..
I have tried amrita's suggestion, but I think as Denny suggested its has to be a security issue. My SQL agent runs as Windows account and that account is part of local admin group. SQL server is running under sa i guess. Is thaere any script that I can find the user that running sql server.
Thanks

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[mrdenny]

You'll need to look at the actual SQL Service. Open Control Pannel, Admin Tools, Services. Find the SQL Server service and see what the run-as account it setup for. Check the SQL Server Agent as well.

Based on your copy command the account that runs the SQL Server will need admin rights on the PAOAKHFMSQL01 server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

On my SQL Service there is no logon account is setup.
But the SQL agent from server is running with a domain account is a part of local admin in both servers.
Here is the script below. I am just trying to backup tarns action log and ship to a diff server. Is there any alternate method available let me know then

[tt]
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER proc sp_upstream_tranlogbak
as
declare @time as char(2)
declare @ampm as char(4)
declare @todisk as char(255)
declare @name as char(255)
declare @filename as char(255)
declare @dbname as char(50)
Declare @Data1 char(255)
Declare @Data2 char(255)
Declare @Data3 char(255)

select @time =datepart(hh,getdate())
select
@ampm =case when (@time between 4 and 8) then '04am'
when (@time between 8 and 11) then '08am'
when (@time between 12 and 15) then '12pm'
when (@time between 16 and 19) then '04pm'
when (@time between 20 and 23) then '08pm'
end
print 'time: '+ @time
print 'ampm: '+@ampm


select @todisk='f:/mssql/backup/upstream/comcast1wldb_log_'+rtrim(ltrim(@ampm))+'.bak'
select @name='comcast1wldb log '+rtrim(ltrim(@ampm))+' backup'
select @dbname='comcast1wldb'
select @data1='copy F:\MSSQL\Backup\Upstream\'+rtrim(ltrim(@dbname))+'_Log_'+rtrim(ltrim(@AMPM))+'.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\'+rtrim(ltrim(@dbname))+'_Log_'+@AMPM+'.bak'

backup log comcast1wldb
to disk= @todisk
with init, stats = 10, name = @name
-------------------------------------------------------------------------------
select @todisk='f:/mssql/backup/upstream/comcastwldb_log_'+rtrim(ltrim(@ampm))+'.bak'
select @name='comcastwldb log '+rtrim(ltrim(@ampm))+' backup'
select @dbname='comcastwldb'
select @data2='copy F:\MSSQL\Backup\Upstream\'+rtrim(ltrim(@dbname))+'_Log_'+rtrim(ltrim(@ampm))+'.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\'+rtrim(ltrim(@dbname))+'_Log_'+@AMPM+'.bak'

backup log comcastwldb
to disk= @todisk
with init, stats = 10, name = @name
-----------------------------------------------------------------------
select @todisk='f:/mssql/backup/upstream/comcasttx5dm_log_'+rtrim(ltrim(@ampm))+'.bak'
select @name='comcasttx5dm log '+rtrim(ltrim(@ampm))+' backup'
select @dbname='comcasttx5dm'
select @data3='copy F:\MSSQL\Backup\Upstream\'+rtrim(ltrim(@dbname))+'_Log_'+rtrim(ltrim(@ampm))+'.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\'+rtrim(ltrim(@dbname))+'_Log_'+@AMPM+'.bak'

backup log comcasttx5dm
to disk= @todisk
with init, stats = 10, name = @name
------------------------------------------------------------------------


print @data1
print @data2
print @data3
exec master..xp_cmdshell @data1;
exec master..xp_cmdshell @data2;
exec master..xp_cmdshell @data3;

exec master..xp_cmdshell 'copy F:\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\comcast1wldb_Log_04pm.bak'
exec master..xp_cmdshell 'copy F:\MSSQL\Backup\Upstream\comcastwldb_Log_04pm.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\comcastwldb_Log_04pm.bak'
exec master..xp_cmdshell'copy F:\MSSQL\Backup\Upstream\comcasttx5dm_Log_04pm.bak \\PAOAKHFMSQL01\F$\MSSQL\Backup\Upstream\comcasttx5dm_Log_04pm.bak'


--sp_upstream_tranlogbak


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/tt]

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[mrdenny]

Try setting the SQL Server to run under the same account that the agent is running under.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)