sp not working for application | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sp not working for application

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?
I checked and concluded that all sp are giving similar trouble.
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.
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?
I have checked the permission they are fine
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

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
[email protected] When life gives you a lemon, fire the DBA.
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.
I checked and concluded that all sp are giving similar trouble. whats the error message?

EXEC sp_change_users_login ‘REPORT’ returning records
username and userSID
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 />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
I have done that only; username and sid are those of dbo.But still same error.Pls help
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
[email protected] When life gives you a lemon, fire the DBA.
owner is sa i.e dbo is the user
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

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
[email protected] When life gives you a lemon, fire the DBA.
When you run that sp_change_users_login ‘REPORT’ are you still getting a result back? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
No I am not getting any result back
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
[email protected] When life gives you a lemon, fire the DBA.
Here is the result of the script given by you
name name
dbosa

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 />
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=’:(‘ />
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
[email protected] When life gives you a lemon, fire the DBA.
Which events and data columns are to be used in this trace?
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 />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
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.
Even if I use filter with db id and name ..trace captures datacolumns for other dbs on server.
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.
]]>