Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    232

    Arrow Unanswered: can we pass some info from sql query to dos

    suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time
    bigfoots

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, there are several possible scenarios that allow you to pass a date from SQL Server to the operating system. They are all quite different from each other, and there is no commonality (one solution doesn't work in another circumstance). Can you explain what you want to do so that we can figure out which of the scenarios applies to what you want to do?

    Is this related to your very similar Perl question?

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    232

    Arrow

    Yes exactly similar to Perl question and i have installed active state active Perl without the software key
    bigfoots

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What software key?

    For the code to create and use the name as you specified it, I'd use:
    Code:
    DECLARE
       @cmd		NVARCHAR(255)
    ,  @filename	VARCHAR(5)
    
    SELECT @filename = SubString('123456789abc'
    ,  Month(DateAdd(day, -1, GetDate())), 1)
    +  SubString(Convert(CHAR(10), DateAdd(day, -1, GetDate()), 121), 9, 2)
    
    SELECT @cmd = N'dir c:\temp\' + @filename + N'.txt'
    
    SELECT @cmd  -- Show the command we've built
    
    EXECUTE master.dbo.xp_cmdshell @cmd
    -PatP

  5. #5
    Join Date
    Oct 2003
    Posts
    232
    gives me an error

    Msg 195, Level 15, State 10
    'Month' is not a recognized built-in function name.
    Msg 170, Level 15, State 1
    Line 9: Incorrect syntax near 'dir c:\temp\'.

    my batch file is name let say mybatch and lies on say x drive

    and i run this file manually each day as

    mybatchfile MDD

    Month will be 1-9 and for 10,11 and 12 it will be A, B or C

    and DD Will be from previous day

    all this as a scheduled task at say 1 am
    Last edited by sjumma; 10-20-04 at 16:20.
    bigfoots

  6. #6
    Join Date
    Oct 2003
    Posts
    232

    does not work

    can someone help????
    bigfoots

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try to copy and paste, I re-verified that the code that I posted works correctly on my machine. It just produced a command of:
    Code:
    dir c:\temp\a20.txt
    -PatP

  8. #8
    Join Date
    Oct 2003
    Posts
    232

    Arrow

    i did copy and paste ,
    use what database as default master or pubs i tried both
    may be because of 6.5 that i am currently on


    From unix i could get this so i am posting there but do answer this for me
    for than it would be less pain??
    Last edited by sjumma; 10-22-04 at 12:52.
    bigfoots

  9. #9
    Join Date
    Jul 2004
    Posts
    52
    Month() is a SQL 2000 function. Change Pat's Month() reference to;

    , DatePart(Month,DateAdd(day, -1, GetDate())), 1)

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Although I'd strongly encourage an upgrade to a newer SQL version, to run on MS-SQL 6.5, I'd use:
    Code:
    DECLARE
       @cmd		VARCHAR(255)
    ,  @filename	VARCHAR(5)
    
    SELECT @filename = SubString('123456789abc'
    ,  DatePart(Month, DateAdd(day, -1, GetDate())), 1)
    +  SubString(Convert(CHAR(10), DateAdd(day, -1, GetDate()), 121), 9, 2)
    
    SELECT @cmd = N'dir c:\temp\' + @filename + N'.txt'
    
    SELECT @cmd  -- Show the command we've built
    
    EXECUTE master.dbo.xp_cmdshell @cmd
    -PatP

  11. #11
    Join Date
    Oct 2003
    Posts
    232
    I am about to go for 2000

    i still get error on this one (6.5) even when i did copy and paste
    i ran it on master


    Msg 170, Level 15, State 1
    Line 8: Incorrect syntax near 'dir c:\temp\'.
    bigfoots

  12. #12
    Join Date
    Jul 2004
    Posts
    52
    In case Pat is not nearby, you need double quotes around the filename if it contains spaces (which it apparently does)

    SELECT @cmd = N'dir "c:\temp\' + @filename + N'.txt"'

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh piddle! I didn't proofread very well.

    6.5 doesn't do unicode, so the N prefix on the string was a bad thing!

    -PatP

  14. #14
    Join Date
    Oct 2003
    Posts
    232
    still getting the same error
    DECLARE
    @cmd VARCHAR(255),
    @filename VARCHAR(5)

    SELECT @filename = SubString('123456789abc',DatePart(Month, DateAdd(day, -1, GetDate())), 1)
    + SubString(Convert(CHAR(10), DateAdd(day, -1, GetDate()), 121), 9, 2)

    SELECT @cmd = 'dir"c:\temp\' + @filename +'.txt"'

    SELECT @cmd

    EXECUTE master.dbo.xp_cmdshell @cmd


    errror
    Msg 281, Level 16, State 1
    121 is not a valid style number when converting from DATETIME to a character string.
    Msg 241, Level 16, State 1
    Syntax error converting DATETIME from character string.
    bigfoots

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Boy, it has been WAY too long since I've used the 6.5 syntax! Let's try using:
    Code:
    DECLARE
       @cmd		VARCHAR(255)
    ,  @filename	VARCHAR(5)
    
    SELECT @filename = SubString('123456789abc'
    ,  DatePart(Month, DateAdd(day, -1, GetDate())), 1)
    +  SubString(Convert(CHAR(10), DateAdd(day, -1, GetDate()), 103), 1, 2)
    
    SELECT @cmd = 'dir c:\temp\' + @filename + '.txt'
    
    SELECT @cmd  -- Show the command we've built
    
    EXECUTE master.dbo.xp_cmdshell @cmd
    -PatP

Posting Permissions

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