SQL Server Performance

sp not working for application

Discussion in 'General DBA Questions' started by aaronsandy, Apr 4, 2005.

  1. aaronsandy New Member

    Hi!
    I am experiencing a strange problem.I have some sp which do simple select some colums from a table.The application which use this sp was working fine before we moved the db from old server.If I replace the proc name with the query then the application works fine.I guess none of the sp are working with this db.Although when I exec these proc by QA they are perfect.What is the problem?
  2. aaronsandy New Member

    I checked and concluded that all sp are giving similar trouble.
  3. satya Moderator

    Monitor the status using PROFILER while usign the application.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. Adriaan New Member

    Looks like the permissions on the sp's are missing for the login that the client app uses. Do you still have access to the old server to see how permissions were organized?
  5. aaronsandy New Member

    I have checked the permission they are fine
  6. Adriaan New Member

    What happens if ...
    (1) you set up the connection in QA using the same login + pwd as in the client app
    (2) in that QA session, you call the sp like the client app does
  7. derrickleggett New Member

    And are you sure the permissions are fine. Run

    EXEC sp_change_users_login 'REPORT'

    on each of the databases and see if it returns any records.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  8. aaronsandy New Member

    1) you set up the connection in QA using the same login + pwd as in the client app

    connected to QA no connection issue
    (2) in that QA session, you call the sp like the client app does
    Procedure executed and given desired output.

  9. bfarr23 New Member

    I checked and concluded that all sp are giving similar trouble.

    whats the error message?
  10. aaronsandy New Member

    EXEC sp_change_users_login 'REPORT' returning records
    username and userSID
  11. derrickleggett New Member

    Ok. You have orphaned users then. This is pretty common when you do this type of thing. You can fix them with:<br /><br />DECLARE @username VARCHAR(256)<br /><br />SELECT @username = 'whatevertheusernameis'<br /><br />EXEC sp_change_users_login 'Auto_Fix', @username <br /><br />Sucks, doesn't it. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Next time you do a migration, read this:<br /><br /<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;246133>http://support.microsoft.com/default.aspx?scid=kb;en-us;246133</a><br /><br />That will avoid the situation you're currently in.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  12. aaronsandy New Member

    I have done that only; username and sid are those of dbo.But still same error.Pls help
  13. derrickleggett New Member

    Do you still have the old server up? If you do, run the script from that link I gave you. Also, right-click on the database in enterprise manager and tell me the name of the user who owns it. I can't say I've actually ever seen dbo be listed from that query.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  14. aaronsandy New Member

    owner is sa i.e dbo is the user
  15. aaronsandy New Member

    The error which i get for most of the sp is like
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Could not find stored procedure 'loginname.procedurename'.

    /index.asp, line 278
  16. derrickleggett New Member

    Ok. That's really weird. I'm probably missing something stupid because I'm tired. Run this:



    SELECT su.name, sl.name
    FROM
    sysusers su
    LEFT OUTER JOIN master..syslogins sl ON su.sid = sl.sid
    WHERE
    su.roles = 0x01


    and post the results.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  17. derrickleggett New Member

    When you run that sp_change_users_login 'REPORT' are you still getting a result back?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  18. aaronsandy New Member

    No I am not getting any result back
  19. derrickleggett New Member

    Ok. I would then point to the application. Is it asking for dbo.objectname, or is it specifying whatever username you are using with the application.objectname? We definitely fixed one issue with the sp_change_users_login.

    Did you run the other query I gave you? If so, please post the results of that.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  20. aaronsandy New Member

    Here is the result of the script given by you
    name name
    dbosa
  21. aaronsandy New Member

    When I run this query in QA its working fine<br />select col1,b.col2,col3,col4,col5,col6 from username.tbl a,username.tbl2 b where a.colx=b.coly order by a.colx <br />but the same query is give error msg when it is executed by a asp application.<br />Before moving from old server .this application was working fine.<br />When I do a modification in query ie remove the "username." part from the avove query , I m getting desired output.<br />But I cant go and change in each and every page.This is not the right solution.<br /> What is the problem here.more than 24 hrs passed ..but I am unable to correct it..sigh <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />
  22. aaronsandy New Member

    I guess the problem is somewhere with username..loginname..But I m unable to get the root <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  23. derrickleggett New Member

    You really, really need to run a statement trace in profiler and see exactly what's happening, including the errors and security events Aaron. This should fairly quickly point out your issue.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  24. aaronsandy New Member

    Which events and data columns are to be used in this trace?
  25. derrickleggett New Member

    Well, if you can capture a SPID, use that as a filter. For events, track:<br />Security Audit:Audit Login Failed<br />Security Audit:Audit Object Permissions Event<br />TSQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtStarting<br />TSQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br /><br />Data Columns:<br />All the normal plus:<br />DBUserName<br />DatabaseID<br />Error<br />HostName<br />LoginSid<br />NTDomainName --Just for kicks and grins<br />Success<br /><br />That's big time overkill. Should get you what you need though.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  26. aaronsandy New Member

    How can I run a trace for a single db only,instead of the capturing events of all the dbs on the server.The trace which I used currently havent gave any useful info for the this db.instead it gave info abt other dbs whose apps are working fine.
  27. aaronsandy New Member

    Even if I use filter with db id and name ..trace captures datacolumns for other dbs on server.
  28. aaronsandy New Member

    At last I got the bug.huh
    It was all due to the connection string problem.I changed the ip in the connection string and now its working fine.Still I am wondering ..how it was connecting to the application before.

Share This Page