Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unanswered: Cannot pass SQL from Access to SQL Server

    Hi All

    Not sure if this belongs here or in the SQL Server forum, but I spend more time here, so...

    Anyway, I have a need to automate a process so that we can bundle it up and pass it to the end users. Said process is extremely convoluted, and involves MS Access and SQL Server (both 2000 - please don't all laugh at once). The Access db outputs some text files. These text files then get transferred to the SQL Server machine and a job is run via Enterprise Manager. The job picks up the text files, unites them with data from one core system, translates them for the other core system and spits out some more files.

    I've automated the process of transferring the files to the SQL machine, and it works fine. I've then tried to adapt some code written by a colleague to pass a job name and server name to a remote server for execution:
    Code:
        Dim objConn As New ADODB.Connection
        Dim rstTest As New ADODB.Recordset
        Dim strConn As String
        Dim strSQL As String
        
        strConn = "Provider=SQLOLEDB; Data Source=NOTT-LINK-01; Initial Catalog=OracleTables; User ID=$User; Password=$Password;"
        objConn.Open strConn
        
        'Clear out the job log table
        strSQL = "DELETE tbl_check_job_execution WHERE JobName = 'OracleTables_JBA_FinalData_Processing'"
        objConn.Execute strSQL
        
        'Fire off the job
        strSQL = "exec msdb.dbo.sp_start_job @job_name='OracleTables_JBA_FinalData_Processing',@server_name='NOTT-LINK-01'"
        objConn.Execute strSQL
    This code snippet connects to the remote machine and executes the first statement without a problem. However, the second SQL statement will not execute. Instead, it returns the error message:
    Run-time error '-2147217900 (80040e14)'
    The specified @job_name ('OracleTables_JBA_FinalData_Processing') does not exist.

    Given that the job *does* exist, this is a little disheartening. If I try running that SQL statement directly in the Query Analyzer window, it works!

    Does anyone have any idea?
    Last edited by weejas; 01-26-11 at 05:34.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It looks to me like you are missing a single quote:

    ,@server_name='NOTT-LINK-01"

    should probably be:

    ,@server_name='NOTT-LINK-01'"

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Blast - typo. In the actual code, it does have the trailing apostrophe. Corrected.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From SQL Server Online Help: "The names of extended stored procedures are always case-sensitive, regardless of the collation of the server." You should perhaps verify that.
    Have a nice day!

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    That's all well and good, except that when I ran the same SQL statement in the Query Analyzer, it worked. This implies to me that I typed the code up (I copied and pasted into the QA), I got the cases right.

    One of my colleagues (whose assistance I can't normally get for love nor money) has suggested that it might be down to user/logon permissions. Once he's been around to see, I'll report back.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Problem evaded

    Rather than amend the permissions on the login that I created within SQL, I was told simply to use the admin user ID and password in the code, on the grounds that we know that one works.

    Therefore, I've also password-protected the VBA code module!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Using an admin account is a very unsecure practice and many database administrators would problably have a fit when hearing about it.

    You should try to convince the owner or the administrator of the database to create a dedicated account your procedure could use.

    'sa' and other logins that are members of the sysadmin fixed server role should be reserved for administrative and maintenance tasks only.
    Have a nice day!

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're preaching to the choir here! However, this instruction effectively came from the DBA, so there's not a lot that I can do right now.

    Given that I did create a user specifically for this process, as soon as I have the time I'll check out which permissions I need to add and amend the VBA script accordingly.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If your DBA's are suggesting you connect using the SA account, then thats fine. but docuement that you are not happy woith that because of th4e potential security breach. explain why its not a smart idea, then if it does go pete tong, you are covered.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Oh, I am covered. The DBAs report to one side of the IT department; I work in the other (don't ask...). The head of my side has recently sent out a message the dangers of this sort of thing...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just thinking on...
    That's all well and good, except that when I ran the same SQL statement in the Query Analyzer, it worked. This implies to me that I typed the code up (I copied and pasted into the QA), I got the cases right.
    check against the original in the server, don't assume anything. You don't know if the query browser is doing soemthign off its own bat....
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm not entirely sure what you mean here, healdem. I've double-checked the name of the job in VBA against SQL Server, and I copied the statement to execute the job so as to avoid typos. Finally, running the job when connected to SQL as sa works.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I meant check the spellimng, check for typos, check for correct case

    however if as you report it works OK when usign the SA account I'm starting to wonder if its sa permissions thing whn you try to connect when runnign the program you are using a userid with insufficient authority
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This is what I'm led to believe, especially after our pseudo-DBA said it!

    Hopefully I'll be able to rectify the setup of the new account on the server before too long.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "pseudo-DBA": I like that!
    Have a nice day!

Posting Permissions

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