0

I have a stored procedure that uses bcp to create csv files executed in the cmdshell. When I run the stored procedure in sql server 2012 it executes like I need it to. But when I run it from excel VBA it only catches the first execution of the loop then stops. There is no error, but I can tell it didn't run completely.

I posted the Vba and Sql stored procedure code below. I am struggling to find anything helpful online so I figured I'd ask stack overflow! Any help would be much appreciated. Thank you!

Dim i As Long, folder As String, year1 As String, month1 As String, day1 As String,      data As String, hour1 As String, minute1 As String
Dim cn As New ADODB.Connection

'Open the connection
strCon23 = "Driver={SQL Server};Server=*******"
cn.CommandTimeout = 0
cn.Open strCon23

s = "EXEC SendNewRates"
cn.Execute s
cn.Close 






DECLARE @Date2 datetime
SET @Date2=getdate()
Declare @sql varchar(1250)
DECLARE @hour varchar(25), @min varchar(25), @sec varchar(25)
Declare @start VARCHAR(25), @Interval bigint, @num varchar(200), @max bigint,@end    varchar(200)
Set @Hour=datepart(hh,@Date2)
Set @Min=datepart(Mi,@Date2)
Set @Sec=datepart(ss,@Date2)
Set @start=1
Set @Interval=50000
Set @End=@Interval
Set @num=1
Set @max = (SELECT max(ROWNUM) FROM UPLOADTable )

While @Start<@Max

Begin

set @SQL='bcp "Select Data From ******* Where [RowNum]>='+@start+' and [RowNum]<='+@end    +' order by [RowNum]" '+'queryout    C:\Test\Upload_NextGen_'+@Hour+'_'+@Min+'_'+@sec+'_'+@num+'_.csv  -c -T -t -S PRK-   PR\EMULATION' 
EXEC master..XP_CMDSHELL @sql 
Set @num=@num+1
Set @Start=Convert(bigint,@Start)+convert(bigint,@Interval)
Set @End=Convert(bigint,@end)+convert(bigint,@Interval)

End
Community
  • 1
  • 1
user2383360
  • 1
  • 1

1 Answers1

0

this is probably related to permissions.

Does your Windows User (if using Windows Integrated Security with MS SQL) or SQL Server user id/login (if using MS SQL Server authentication) have permission to execute xp_CMDShell

When using SMSS I think you are using a higher level of permissions that if you are executing over ADO from Exscel using Windows Integrated Security.

I think you need to ensure login from Excel has higher level rights on the server.

Using xp_cmdShell / bcp require higher levels of authority than just execute stored proc.

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
  • I do not think it is a permission issue because my manager, the one who encountered the problem and created the process, is the system admin. She is out of the office today, but I will show her your response asap. Thank you for the response and I'll keep you posted on our progress. – user2383360 May 15 '13 at 17:03
  • yes, but her Winodws account still won't be a member of the SysAdmins role on the SQL Server *unless it is explicitly added* – Our Man in Bananas May 16 '13 at 08:17