Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dirwin26
Yak Posting Veteran
81 Posts |
 Posted - 2007-03-23 : 10:02:44
|
| I have built a SP that pulls data from excel into SQL server, it works fine when I run it in QA, but running the SP as a job, so it is automated, craps out, I am assuming b/c of the ANSI defaults. Is there a way to run query analzer within a job, or instead of just putting t-sql in a job, tell the job to execute a SP? as in execute sp [mySP]? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-03-23 : 10:10:01
|
| >> Is there a way to run query analzer within a job, or instead of just putting t-sql in a jobDoubt it - qa is interactive.What is the error you are getting?Is it because of the profile the agent is runnnig under? Try logging on as that user and see what happens.As to the ansi defaults - you can set those.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-23 : 10:13:22
|
| Several ways to approach this. One you can add lines to the sp to set the environment that same as QA, using sp_dboptions. That way regardless of what the 'current' settings are your procedure will always have the same settings. Also could this be a security issue. The connection from QA and the context that the SP is running could have different access or accounts. What kind of error message are you getting. What step is it failing on when you look at the detailed history?Raymond LaubertMCDBA, MCITP:Administration, MCT |
|
|
|
dirwin26
Yak Posting Veteran
81 Posts |
Posted - 2007-03-23 : 10:14:12
|
| there is no error given on the job, it just says 'failed'. I am assuming it is a permissions problem maybe? |
|
|
|
dirwin26
Yak Posting Veteran
81 Posts |
Posted - 2007-03-23 : 10:15:59
|
| I know it is failing on this:select * into taleA FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\filedirectory\BOOK4.xls;HDR=YES', 'SELECT * FROM [SHEET1$]') |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-03-26 : 05:49:56
|
| When running as a job under SQLAgent its running as the SQL Agent Account, by default this is teh system account which more than likely does not have permissions to access the directory where the spreadsheet is located. Uder Query Analyser its running as your account which does have permissions.In EM Right Click SQLAgent, Select Properties and check what account its running as. It may very well be that you need to change it to a (low Privilege) Account on the local machine, and then give this account permission to the Directory, and also login permission to SQL Server, as well as assigning appropriate permissions to the various DB's.-- RegardsTony The DBA |
|
|
|
|
|
|
|
|
running Query Analyzer