0

I'm trying to run a stored procedure that I'm passing some variables to.

The output is being writted to a CSV file on a local file share.

The CSV file gets created, but I'm hitting some errors that I'm not sure how to resolve. Any help would be appreciated.

SQL

DECLARE @tmp1 DATETIME
SET @tmp1 = DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)

DECLARE @tmp2 DATETIME
SET @tmp2 = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

DECLARE @fac_ids NVARCHAR(500)
SET @fac_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,30,32,33,35,36,37,40,41,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,79,80,81,83,84,85,86,87,89,90,91,92,93,94,95,97,98,135,39,29,31,34'

DECLARE @start_date DATETIME
SET @start_date = @tmp1

DECLARE @end_date DATETIME
SET @end_date = @tmp2

DECLARE @sql VARCHAR(max)
SET @sql = 'Exec [dbo].[sproc_ar_reconciliation] @fac_ids, @start_date, @end_date'

DECLARE @CsvFile NVARCHAR(500)
SET @CsvFile = 'C:\DOMO\AROS\ar_recon.csv'

DECLARE @cmd NVARCHAR(4000) 
SET @cmd = 
'bcp '+CHAR(34)+@sql+CHAR(34)+' queryout '+CHAR(34)+@CsvFile+CHAR(34)+' -S '+@@servername
+' -c -t'+CHAR(34)+','+CHAR(34)+' -r'+CHAR(34)+'\n'+CHAR(34)+' -T'

exec master.dbo.xp_cmdshell @cmd

Errors

NULLStarting copy... SQLState = 37000, NativeError = 137 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]

Must declare the scalar variable "@fac_ids".

SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]`

Unable to resolve column level collations NULL BCP copy out failed NULL

  • 5
    the problem is that the string that forms is something like this bcp "Exec [dbo].[sproc_ar_reconciliation] '@fac_ids, '@start_date, '@end_date" queryout "C:\DOMO\AROS\ar_recon.csv" so the parameters have no data – Edgar Vazquez Jul 26 at 23:27
  • ...and the collation issues probably have something to do with the queries inside the procedure, which we can't help with unless we can see that... – Aaron Bertrand Jul 27 at 14:46
  • Any suggestions on how I should build the string? I dont think the issue is with the queries inside the SP. When I run "exec [dbo].[sproc_ar_reconciliation] with '@fac_ids, '@start_date, '@end_date". I receive the correct data output. – Douglas Owens Jul 28 at 16:51
  • '@start_date, '@end_date I think you could calculate it within the sp and just send '@fac_ids in your string with a small modification it all depends if '@start_date,' '@end_date you can calculate it inside – Edgar Vazquez Jul 29 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.