Query out parameter

  • 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

  • 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

  • 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

  • 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)

  • 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