Results 1 to 8 of 8

Thread: SQL Jobs...

  1. #1
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    27

    Unhappy Unanswered: SQL Jobs...

    1. If i do a select on sysjobs i get all the jobs created in the DB along with the job owner_sid. But i want to get the actual name of the job owner. How do i get it???

    2. On creating a batch file using the BCP query i can get the output into an external file. But is there any way i can give the columns also for the same??
    Query is like this...
    bcp "select name as Jobs, owner_sid as JobOwner from msdb..sysjobs" queryout d:\Jobs.xls.

    But in my excel sheet output i wont the get the column heading as Jobs and JobOwner which i get in OSQL.

    Is there any solution???

    --Sandu
    Last edited by sandu_bangalore; 10-16-03 at 16:28.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: SQL Jobs...

    How about:

    Code:
        SELECT l.Name, j.Name FROM msdb..sysjobs j
    INNER JOIN master..syslogins l 
            ON l.sid = j.owner_sid
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    i thought the poster was asking how to include field name in the output of bcp, not how to get the login name for the owner sid...isn't it?

  4. #4
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    27
    Originally posted by ms_sql_dba
    i thought the poster was asking how to include field name in the output of bcp, not how to get the login name for the owner sid...isn't it?


    Actually there were two questions.
    And the answer for the first one which was posted was the correct one. Thanks for the same.

    The second one is still pending.. as to how will i include the feld name in the output of BCP.

    --Sandu.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ms_sql_dba
    i thought the poster was asking how to include field name in the output of bcp, not how to get the login name for the owner sid...isn't it?
    Yeah...my bad...but I would still think he'd want the real owner name...

    damn identity columns...

    Since your creating a file, it's probably going in to Excel..

    So here ya go...You can expand on it

    Code:
    SELECT Data_Row FROM (
        SELECT '"Owner","Job"' As Data_Row, 1 AS Row_Group
     UNION ALL
        SELECT '"' + l.Name+ '","' + j.Name + '"', 2 AS Row_Group 
          FROM msdb..sysjobs j
    INNER JOIN master..syslogins l 
            ON l.sid = j.owner_sid
    ) AS XXX
    ORDER BY Row_Group
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious...did that float your boat?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    27
    Originally posted by Brett Kaiser
    Just curious...did that float your boat?


    The above mentioned query is working perfectly when ran thrugh ISQL. But if tries to run as a BCP file it is failing with the error as mentioned below :-


    Copy direction must be either 'in', 'out' or 'format'.
    usage: bcp {dbtable | query} {in | out | queryout | format} datafile
    [-m maxerrors] [-f formatfile] [-e errfile]
    [-F firstrow] [-L lastrow] [-b batchsize]
    [-n native type] [-c character type] [-w wide character type]
    [-N keep non-text native] [-V file format version] [-q quoted identifier]
    [-C code page specifier] [-t field terminator] [-r row terminator]
    [-i inputfile] [-o outfile] [-a packetsize]
    [-S server name] [-U username] [-P password]
    [-T trusted connection] [-v version] [-R regional enable]
    [-k keep null values] [-E keep identity values]
    [-h "load hints"]
    Done..Please check the output log files for the results.

    --Sandu

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's the bcp...

    Why not create it as a view and DTS it out...

    Or bcp out the view...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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