October 19, 2009 at 3:25 pm
I inherited a query and the last part of it looks to send the file to my c drive -
queryout "C:\PWC\PWC_TRANSACTION_REQUEST_' + @COMP + '_' + CAST(@YR AS CHAR(4)) + '_' + CAST(@MONTH AS NVARCHAR(2)) + '.csv" -T -SVSLPBOPSDB01\GREATPLAINS -c '
but I can't find the files anywhere
October 19, 2009 at 3:27 pm
here's the whole proc -
USE [MASTER]
GO
DECLARE@DBNVARCHAR(10)
DECLARE@SQLNVARCHAR(4000)
DECLARE@COMPNVARCHAR(75)
DECLARE@YRSMALLINT
DECLARE@MONTHTINYINT
SELECT@YR=2009
DECLARE DBS CURSOR FAST_FORWARD FOR
SELECT LTRIM(RTRIM([NAME]))
FROM SYS.[DATABASES]
WHERE NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB','DYNAMICS','DYNAMICSGP_UTILITIES','LPB_GREATPLAINSUTILS','SETUP')
ORDER BY NAME
OPEN DBS
FETCH NEXT FROM DBS INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT@COMP=SUBSTRING(LTRIM([CMPNYNAM]),1,2) + '_' + RTRIM(LTRIM(@DB))
FROM[DYNAMICS].[dbo].[SY01500]
WHERE[INTERID]=@DB
SELECT@MONTH=1
WHILE@MONTH <= 9
BEGIN
SELECT@SQL='BCP "' +
'SELECTA.ACTNUMBR_1,' +
'A.ACTNUMBR_2,' +
'A.ACTNUMBR_3,' +
'A.ACTNUMBR_4,' +
'A.ACTNUMBR_5,' +
'A.ACTNUMBR_6,' +
'A.ACTDESCR,' +
'B.JRNENTRY,' +
'B.SOURCDOC,' +
'B.DSCRIPTN,' +
'B.TRXDATE,' +
'B.SEQNUMBR,' +
'B.CRDTAMNT,' +
'B.DEBITAMT, ' +
'B.REFRENCE ' +
'FROM ' +
@DB + '.DBO.GL00100 A ' +
'INNER JOIN ' +
@DB + '.DBO.GL30000 B ' +
'ON ' +
'B.ACTINDX=A.ACTINDX ' +
'AND ' +
'B.TRXDATEBETWEEN ''' + CAST(@MONTH AS CHAR(2)) + '/1/' + CAST(@YR AS CHAR(4)) + ''' AND DATEADD(dd,-1,DATEADD(mm,1,''' + CAST(@MONTH AS CHAR(2)) + '/1/' + CAST(@YR AS CHAR(4)) + '''))" queryout "C:\PWC\PWC_TRANSACTION_REQUEST_' + @COMP + '_' + CAST(@YR AS CHAR(4)) + '_' + CAST(@MONTH AS NVARCHAR(2)) + '.csv" -T -VSMIADBGP01\GREATPLAINS -c '
--EXEC SP_CMDSHELL @SQL
PRINT @SQL
SELECT@MONTH = @MONTH + 1
END
FETCH NEXT FROM DBS INTO @DB
END
CLOSE DBS
DEALLOCATE DBS
October 19, 2009 at 7:59 pm
I'm assuming you commented the following EXEC and replaced it with the PRINT statement before sending us the code, and that in production it is uncommented.
--EXEC SP_CMDSHELL @SQL
PRINT @SQL
Remember that the C: drive being referenced in the command must be the C: drive for the server where the query is executing. Can you find the C:\PWC directory?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 6:34 am
I didn't see that...thank you..
but when I remove the rem I get
I get zero byte files!!!
Can't understand why they are zero byte!
here's the output:
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 78
NULL
(7 row(s) affected)
October 20, 2009 at 7:16 am
You need to examine the final query which is to be executed. It is obviously returning no rows when it runs. I can't see your source data to understand why that would be the case.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply