February 2, 2005 at 1:03 pm
I have an asp.net application where a button click calls a stored proc X that starts a process in my SQL db. The process is:
X runs a dts package to import 12 text files into 12 temporary tables. Then runs other stored proc Y to take the data from the temporary tables to main tables with some modifications.
from the application I am logging in as User1/Pass1 to connect to the db. User1 has perission to execute the X.
the problem is when I call X from the application it only runs the dts and not the other stored proc Y. BUT when i log in as User1 on Query Analizer and call X. everything runs the way it should: dts then Y. even if not all the files are present.
i cannot figure out the different behaviors.
February 2, 2005 at 1:08 pm
Does the DTS package produce a log file, if so does it indicate any errors ?
Does the DTS packahe have full explicit path names to the files, or is it relying on some notion of "current working directory" which may differ depending on how called ?
February 2, 2005 at 1:13 pm
I didnt set the DTS package to produce an error log. but if there is any files missing (which happens often) then there is errors (file not found) but that does not seem to matter when I am running the process from Query Analyzer.
Also the path names are explicitly defined in the package.
Thanx for your help.
February 2, 2005 at 2:42 pm
OK, this is a wild guess:
Did you SET NOCOUNT ON in the proc first thing?
I don't know if MS fixed this issue with odbc/ado/etc.
Aside from that, how are you calling the DTS package from within the proc?
February 2, 2005 at 3:19 pm
NOCOUNT is set to default (OFF).
in the stored proc, I used the following line to call DTS:
EXEC master.dbo.xp_cmdshell 'dtsrun /S myServer /N myPackage /E '
should i set the NOCOUNT to ON? why does that matter?
thanx again.
February 3, 2005 at 8:35 am
I am also having problems where I have a query that modifies a number of tables. one of them (A) has more then 2.25 million records. all the other tables get modified but A does not get modified. is it timing out? i am not sure what is going on. Thanx for your help.
February 3, 2005 at 12:13 pm
My thoughts:
1) you said it was asp.net
2) You said you were logging in as user1/pass1 and it has the requisite permissions.. do you mean logging in as that is in your connect string, or as in the asp.net app is using windows authentication and that is the userid you are then using
3) You said you use EXEC storeprocname from with the first proc.
Observations:
Normally, asp.net applications run using the anonymous userid for the asp_net worker process... IUSR_servermachinename. If you are not really causing a windows login as above then you have to ensure that the IUSR userid has the correct permission to run your stored procs.
Also, and I am reaching here, I believe that even if you are running Proc x correctly with all correct permissions, then subordinate procs called by executing EXEC STILL kick to running with the anonymous userid, which will still then need permissions on the underlying objects.
Also, isn't it true that temporary tables are visible only to their own connection, so that your subordinate proc has no way to see them?
Now if anyone understands whatever it is I am trying to say, jump in here.
February 3, 2005 at 1:53 pm
Grasshopper,
I am using user1/pass1 in my connection string. I have an user1 in my db who has permission to exe stored proc that I am calling. Also i m using the same user/pass combination throughout the asp.net app and this is the only place that i m having a problem.
Also, when i said temporary table what i should have said is, there are 12 permanent tables where the data is loaded into from text files. they are used as temporary files because they are overwritten everytime new data comes in. but they are regular tables.
"I believe that even if you are running Proc x correctly with all correct permissions, then subordinate procs called by executing EXEC STILL kick to running with the anonymous userid, which will still then need permissions on the underlying objects."
are you saing that when i call (using exec) another stored proc from inside a stored proc like so:
CREATE PROCEDURE [tempStoredProc]
AS
Exec tempStoredProc2
GO
that tempStoredProc2 will run under anonymous userID? if this is the case then how can i fix it so it runs under user1?
thanx for your help.
February 3, 2005 at 2:07 pm
Regarding this code:
>>EXEC master.dbo.xp_cmdshell 'dtsrun /S myServer /N myPackage /E '
Have you tested this in isolation ? Can xp_cmdshell actually find dtsrun.exe ?
There is another method to execute DTS packages from with a stored proc that doesn't require spawning an external command shell. The code below is for such a procedure - and I have to apologise to the original author, because I obtained this from a website a while ago and can't recall who to give credit to.
CREATE PROCEDURE dbo.uspRunDTSPackage
@PackageName varchar(255),
@PackageServer varchar(255)
AS
SET NOCOUNT ON
DECLARE @DTSPkg int
DECLARE @hr int
DECLARE @src varchar(50)
DECLARE @Descr varchar(100)
--Create a reference to the Package
EXEC @hr = sp_OACreate 'DTS.Package',@DTSPkg OUT
if @hr <> 0
GOTO E_OAError
--Connect to it
EXEC @hr = sp_OAMethod @DTSPkg, 'LoadFromSQLServer',NULL,@ServerName = @PackageServer,@Flags = 256,
@PackageName = @PackageName
if @hr <> 0
GOTO E_OAError
--Kick it off
EXEC @hr = sp_OAMethod @DTSPkg, 'Execute'
if @hr <> 0
GOTO E_OAError
--Clear Up
EXEC @hr = sp_OAMethod @DTSPkg ,'Uninitialize'
-- Destroy
Exec @hr = sp_OADestroy @DTSPkg
E_OAError:
if @hr <> 0
begin
-- Return -1
RAISERROR ('The package encountered an error!' , 18, 1)
end
GO
February 3, 2005 at 3:07 pm
I mentioned in the beganing of the post that the way the DTS is set up that the package will finishe even if there is files missing. meaning if we are supposed to have files A...F and we actually have files A...F, the package will load files A...F. If we are missing file D but we have all the rest then the package will still load all the other ones. but it will give an error (file not found) and move on to load the next file.
The above process works fine. no matter where i call the stored proc X from. the way i have it is:
IF @RegionID = 'MW' BEGIN
EXEC master.dbo.xp_cmdshell 'dtsrun /S MYSERVER /N MYPACKAGE_mw /E '
END
IF @RegionID = 'VA' BEGIN
EXEC master.dbo.xp_cmdshell 'dtsrun /S MYSERVER /N MYPACKAGE_VA /E '
END
EXEC IMPORT_TO_REGULAR_TABLES @RegionID
for whatever reason IMPORT_TO_REGULAR_TABLES stored proc is not running when i call from the .net app.
February 3, 2005 at 4:08 pm
I may have misled you a bit. Here's what I was talking about:
"Normally, SQL Server allows access to any object referenced in a stored procedure as long as the user has execute permissions for that stored procedure.
The exception to this is when the stored procedure executes SQL code from a variable (dynamic SQL). In these cases, it checks the permissions for objects in the dynamic SQL as well as permissions to execute the stored procedure.
Therefore, if you have execute permissions for a stored procedure and the stored procedure executes SQL code from a variable to do a SELECT on a table then the user must also have SELECT permissions for that table. If the dynamic SQL also does an INSERT then you must have INSERT permissions for the table. The same for DELETE and UPDATE. "
This came from:
http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=170
I was recalling that even though you have exec permissions on a stored proc, when you get to the nested EXEC, not all is the same.
In order to avoid wild goose chases, it is best if you improve the stored proc to give you back any error messages.
If you can, run it under a userid with limited permissions in Query Analyzer, maybe there's some clues there.
February 4, 2005 at 7:46 am
Phill,
i am sooooooo confused now. So far in all my apps. I only have exec permission on the stored procedure for the user that is logging in. in all of my stored procs i am passing in variables like so:
CREATE PROCEDURE [return_SOMETHING]
@userid int
AS
SELECT * FROM viewUser WHERE User_ID = @userid
GO
i m logging in to the database with user1/pass1 (in the connection string) and user1 can only exec this procedure. user1 has no explicit permission to the viewUser view or the table that view is looking at.
AND IT HAS BEEN WORKING FINE!! ????? if I didnt have exec permission on the stored proc then i got an error (access denied)...I thought the permissions got cascaded.
February 4, 2005 at 8:51 am
I think PhilM99 hit it right with the following: "In order to avoid wild goose chases, it is best if you improve the stored proc to give you back any error messages."
Remember, @@ERROR should be passed to a @Variable, as you lose it every time a statement is executed.
I wasn't born stupid - I had to study.
February 4, 2005 at 1:07 pm
when i login as user1 and exec the stored proc in query analyzer i dont get any errors. i have print statements in the stored proc to print out the @@ERROR and there is none. when i go through the app, it just execs part of the code but not all.
February 4, 2005 at 1:58 pm
Probably not a permissions prob then. One thing I recall is when you run a DTS package in Query Analyzer it tends to run sequentially but if you run it through an app, it can actually do some things in parallel. So I'd be staring at the DTS package making sure that it is not possible that one action that depends on another being finished, is not actually starting up too soon and finding an empty table, thus not doing anything and not reporting an error.
Just an idea...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply