Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    29

    Unanswered: out put to text file issue

    DECLARE @cdr_date datetime
    SET @cdr_date = getdate()-1
    SET NOCOUNT ON
    select *
    from call
    where year(starttime)=year(@cdr_date) and month(starttime)=month(@cdr_date) and day(starttime)=day(@cdr_date)

    I want to run this query with output file like g_YYMMDD ( where YYMMDD will come from @cdr_date

    How can I do that

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    For this to work reliably you need to create a couple of procedures:

    Code:
    create proc dbo.sp_test
    @cdr_date datetime = current_timestamp
    as
    SET NOCOUNT ON
    select * 
    from call 
    where year(starttime)=year(@cdr_date) and month(starttime)=month(@cdr_date) and day(starttime)=day(@cdr_date)
    return (0)
    go
    Then create another procedure that will create the output file:
    Code:
    create proc dbo.sp_bcp_queryout as
    @cdr_date datetime = current_timestamp
    as
    declare @cmd varchar(8000)
    --assuming the db name is MYDB, server name - MYSERVER
    set @cmd = 'bcp "exec MYDB.dbo.sp_test ' + char(39) + 
       convert(char(10), @cdr_date, 101) + char(39) + '" queryout D:\g_' + 
       convert(char(6), @cdr_date, 12) + ' -S MYSERVER -T -c'
    
    exec master.dbo.xp_cmdshell @cmd
    return (0)
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2003
    Posts
    29
    Thank you very much

  4. #4
    Join Date
    Feb 2003
    Posts
    29
    now I get following error

    SQLState = 37000, NativeError = 8146
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure sp_call_get_cdr has no parameters and arguments were supplied.
    NULL

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    At what point are you getting the error? I also recommend to use "-e Error-ExceptionFile.ERR" on BCP so that you can check what record(-s) caused the error.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2003
    Posts
    29
    Thanks for the quick reply

    I got it. it works now. the only quesitons remains is that if I want to create csv file can I do that with column name as headers.

    please advice.

    thanks for your help in advance

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yes you can, but you'll need to switch from BCP to OSQL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2003
    Posts
    29
    so can you please be specific. which command I need to put

  9. #9
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    You should be able to create a CSV with BCP by using Column terminators:
    8.0
    4
    1 SQLSMALLINT 0 2 "" 1 smallintField ","
    2 SQLNCHAR 2 100 "'" 2 charField1 "',"
    3 SQLNCHAR 2 100 "'" 3 charField2 "',"
    4 SQLDATETIME 0 8 "'" 4 datetimeField "',"
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •