Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TerryKC
Starting Member
4 Posts |
 Posted - 2011-10-27 : 04:22:04
|
| I am excuting a Stored Procedure through an application to export data.The data is not exported, but if I execute the SP directly fisrt thing, then it exports through the application for the rest of the day. The next day it fails to execute through the application again unless the SP is executed directly first.Any thoughts will be greatly appreciatedTerryKC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 04:45:51
|
| whats the sp using? is it using bcp? if yes, are you calling it using xp_cmdshell in proc? i've doubt whether this is caused due to access restriction on execution of xp_cmdshell.Also whats the login application uses to execute it? does it have required permissions to execute xp_cmdshell?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
TerryKC
Starting Member
4 Posts |
Posted - 2011-10-27 : 05:00:37
|
| SP uses BCP and calls xp_cmdshell.The SP exected throgh the application doesn't do the BCP export unless the SP has been executed directly first. Once that has been done, the SP is executed through the application for the whole day. But next day it doesn't until the SP is executed directly first. So some permissions appear to being reset overnight.TerryKC |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 05:05:20
|
quote:
Originally posted by TerryKC
SP uses BCP and calls xp_cmdshell.The SP exected throgh the application doesn't do the BCP export unless the SP has been executed directly first. Once that has been done, the SP is executed through the application for the whole day. But next day it doesn't until the SP is executed directly first. So some permissions appear to being reset overnight.TerryKC
ok. what about other questions?whats login application uses?what all priviledges does it have?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TerryKC
Starting Member
4 Posts |
Posted - 2011-10-27 : 05:38:02
|
| The application accesses the database which is set up in mixed mode authentication and it uses a login which is set up as database owner,etc.I can't access the customer site at the moment to check other settings.The BCP that it is trying to execute is:declare @se varchar(5000)set @se = 'bcp "select [EXPORTFIELD] from [Progress].[dbo].[FB_SOP_EXPORT] order by rownumber" queryout \\progress\fiskbrett\progress4\Marston\Customer.txt -c -q -t -SPROGRESS -Ufb -Psoftware 'exec master.dbo.xp_cmdshell @se , no_outputI have also tried adding this into the SPEXEC sp_xp_cmdshell_proxy_account NULLEXEC sp_xp_cmdshell_proxy_account 'progress\marsdenexport','susususu'TerryKC |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 06:08:10
|
| does login application uses have access to below path?\\progress\fiskbrett\progress4\Marston------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TerryKC
Starting Member
4 Posts |
Posted - 2011-10-27 : 06:16:41
|
| I can't check at the moment as I can't access the customer site.However I assume it has, because as I said above, once the SP has been directly executed, the application then executes the SP and exports correctly to that location any number of times during the dayTerryKC |
|
|
|
|
|
|
|
|
BCP export