Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Stored Procedures

    Hi

    Im calling a stored procedure from a web browser. In the stored procedure there is a exec statement that calls another stored procedure that inserts into a table.

    The problem that the original stored procedure has a select statement after the exec statement, which dosent get executed.

    I believe from testing that it dosent return from the second stored procedure even though i have return in it. I think it is do with the insert statement.

    Any ideas/thoughts would be great.

    Cheers

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Can you post DDL (sample) involved in the SPs.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Stored Procedure 1 contains:

    CREATE Proc upWebSelObjectDocuments
    @intObjectId int,
    @strNTLogon varchar(56)
    as

    BEGIN
    EXECUTE MakeLoginInfo @strNTLogon
    END

    Select distinct [FileName],
    FriendlyName
    From vwWebSelObjectDocuments
    Where BUID = @intObjectId
    Order By FriendlyName
    GO

    Stored Procedure 2 contains:

    CREATE PROCEDURE MakeLoginInfo]
    @strNtLogon as varchar(50)

    AS

    DROP TABLE loginInfo

    CREATE TABLE loginInfo(

    accName VARCHAR(50),
    accType VARCHAR(50),
    privilege VARCHAR(50),
    mappedloginName VARCHAR(50),
    permissionPath VARCHAR(64)
    )

    INSERT dbo.loginInfo Values('Test', 'Test','Test','Test','Test')
    GO

    When i was testing i was commenting out parts to see what worked and everything worked apart from the insert line.

    cheers

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess would be that your spid has a user that is not dbo. When you issue the CREATE TABLE, it would then create the table for the current user, not for dbo. If you are a member of db_owner, you could create the table using:
    Code:
    CREATE TABLE dbo.loginInfo(
    
    accName VARCHAR(50),
    accType VARCHAR(50),
    privilege VARCHAR(50),
    mappedloginName VARCHAR(50),
    permissionPath VARCHAR(64)
    )
    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    15
    Cheers

    Ive just tried that and still the same problem : (

    Any other ideas?

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Did you get any errors while execution?
    Any trace from PROFILER?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Mar 2004
    Posts
    15
    Theres no errors and when i use profiler it says completed ok. And if i run the same query in query analyser it completes ok. It just dosent return anything when i run it from the web browser

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try monitoring the activity using PROFILER.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    21

    wrong place of end statement

    i think your problem is that the select statement is not between begin end statement, try to place it between it...
    let me know if it worked...
    Last edited by wuadko; 03-30-04 at 10:36.

  10. #10
    Join Date
    Mar 2004
    Posts
    15

    Re: wrong place of end statement

    Nope that didnt that either : (

    Ive tried placing BEGIN and END in most places and that didnt work.

  11. #11
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    21
    try to run it like this:

    CREATE Proc upWebSelObjectDocuments
    @intObjectId int,
    @strNTLogon varchar(56)
    as

    BEGIN
    EXEC MakeLoginInfo @strNTLogon

    Select distinct [FileName],
    FriendlyName
    From vwWebSelObjectDocuments
    Where BUID = @intObjectId
    Order By FriendlyName

    END

    GO

    Stored Procedure 2 contains:

    CREATE PROCEDURE MakeLoginInfo
    @strNtLogon as varchar(50)

    AS
    BEGIN
    DROP TABLE loginInfo

    CREATE TABLE loginInfo(

    accName VARCHAR(50),
    accType VARCHAR(50),
    privilege VARCHAR(50),
    mappedloginName VARCHAR(50),
    permissionPath VARCHAR(64)
    )

    INSERT dbo.loginInfo Values('Test', 'Test','Test','Test','Test')
    END
    GO

    write what doesnt work...
    wuadko

  12. #12
    Join Date
    Mar 2004
    Posts
    15
    Ive just tried that one and it still dosent return anything

  13. #13
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    21
    once more, what is wrong? what doesnt return anything?
    wuadko

  14. #14
    Join Date
    Mar 2004
    Posts
    15
    Sorry

    The stored procedure dosent return anything to the web browser when their is an insert statement in their. It completes the insert ok but then dosent do anything after that.

  15. #15
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    21
    and which of stored procedures do you mean?
    wuadko

Posting Permissions

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