Solved

Stored Procedure??

Posted on 2008-10-02
10
407 Views
Last Modified: 2013-12-11
I have someone running MAS500. There is a "custom procedure" which imports daily stock qoutes into the application. Basically, it appears to import a Excel spreadsheet into MAS500. The problem is there is only one user who can actually run the "procedure" from within MAS500. If anyone else tries to execute it the import fails, I don't beleive there is a error it just doesn't import the quotes. According to the user everyone within MAS500 has the same rights, so it shouldn't be due to program level rights. How and where would I begin to determine permissions for this type of procedure.? I know this is a minmal amount of info to go off of, but any details you guys may need, let me know and I'll answer them the best I can...

*In SQL under Data Transformation Services -> Local Packages -> the procedure "Transfer Stock Quotes" is listed. Again, not being a SQL guru or MAS500 guru for that matter, I'm not sure if running the procedure from within MAS500 is simply calling this package or what.....

Any help is appreciated...
0
Comment
Question by:hookssystems
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22626582
look at the procedure and see where it is pulling the xls from.  Verify that the users running the procedure, have rights to the location where that xls file is stored.  It is probably on a network location that some users don't have access to.
0
 

Author Comment

by:hookssystems
ID: 22627294
I may have used improper wording above. It appears that this is a MAS500 "Task" which I can't seem to see many details about. The steps for this whole process are:

1). User retrieves four .csv files with stock quotes from the Internet, places them in specific directory.
2). Batch file is ran from within that directory which compiles the four .csvs into one csv.
3)  From within MAS500 a "IM Custom Import" task is ran which imports the csv into MAS500

* I beleive step 3 is when it calls the SQL Local Package  "TransferStockQuotes".
When I look at the diagram for the local package I can see where it is using the compiled CSV as the input source, it then appears to attach to the MAS500 database, then executes a SQL task "execute spUpdateStockQuotes go"

The directories where the compiled csvs and the batch file exist have full rights for the required users.

Where would this "spUpdateStockQuotes" exist??
0
 

Author Comment

by:hookssystems
ID: 22627308
Also, how can a look at details for the MAS500 task "IM Custom Import"?
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22635017
The spUpdateStockQuotes sounds like a store procedure - so look there in your database - use Enterprise Manager, browse to your database, expand that and you will see a "stored procedures" folder - should be in there... Could also use Query Analyser (use the "show objects" for the object explorer on the left), when you locate it right click and open to new query window as a "create" - that way it will not save again if you accidentally go to run it , or change it (the create will fail because it already exists).

I would say it is likely set up with Windows Authentication - or some kind of profile that SQL server is acknowledging for that one person...

Cannot help you with MAS500 - sorry, but if there is a manual...

0
 

Author Comment

by:hookssystems
ID: 22636317
I was able to find the procedure "spUpdateStockQuotes". When I look at permissions, the only object that has EXEC permissions is "ApplicationDBRole". The user who is able to run the procedure does not have the EXEC permission enabled on her account. Maybe she is a member of "ApplicationDBRole"???
0
 

Author Comment

by:hookssystems
ID: 22636357
I have just found that all users are members of "ApplicationDBRole"???
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22636360
Yep, sounds as if you are on the right track...
0
 

Author Comment

by:hookssystems
ID: 22650482
Well after digging through the "mechanism" I found that everyone has the same permissions within SQL in regards to the task. So I began to look back at MAS500 and found some differences in program level permissions. Changed those for a user to test and still have the issue. The error generated when he clicks the MAS500 "task" is:

"Run-Time error-2147418105 (80010007)
Automation error
The callee (Server [not server application]) is not available and disappeared; all connections are invalid.  The call may have executed."

I'm assuming the "task" is written in VB but from the looks of things this is a generic error that I can't seem to find any specifics about..
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22651593
Ummmm, cannot really help with the MAS side of things, can you place a support call ? I cannot login, but you should be able to : http://www.sagesoftwareonline.com/eServices/Main/frmLogin.aspx

But you are likely correct it is VB or VBA - and that error does exist in winerror.h, and yes, it is not very detailed...  see : http://support.microsoft.com/kb/186063

It should be possible to run those steps externally (or from a SQL query window) and see it it does execute.

0
 

Accepted Solution

by:
hookssystems earned 0 total points
ID: 22841950
Thanks for the input guys. The consulting company that originally designed the mechanism will be taking care of it.
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 82
rolling count by date, hour query 7 31
Do not display comma when no last name 8 48
*** Windows Server 2012 Websites Set Up *** 17 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question