Login failed error when conneting to mirrored db.

Hi All,

I have been struggling to make my classic ASP to call mirrored db successfully for the last few days. I am using a ODBC SQL Native Client. The front end application works perfectly untill the database is switched from principal to mirror server. I am getting the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

[Microsoft][SQL Native Client][SQL Server]Cannot open database "testDb" requested by the login. The login failed.

I will briefly explain what I have done. I hae created a SQL Login testUser and this login has the dbowner rights to testDb. This is being done in Principal server. Till now the application works fine. Once I switch it to failover, the mirror server acts as a principal - this is what I was expecting. On database level it works. But my ASP application doesnot seem to like it. When I closely looked into the security, I found that testUser does exist in the testDb but the real problem is testUser is not associated with testUser Login. It's not possible to change as well.

Any sort of pointer is appreciated.

Many thanks.

Sincerely,

Milan G

To correct the issue with the user not being tied properly to the Login, take a look at sp_change_users_login in BOL. When failing over, this will need to be run against all SQL users to tie them back to their logins. If you're able to use Windows authentication, that's preferable since the SIDs are the same and you don't need to mess with doing this.

|||

Hi M,

Many thanks for the pointer. Finally, I succeeded with your pointer. I found the this link a useful. http://www.mssqltips.com/tip.asp?tip=1166

cheers,

Milan G

Login failed error when conneting to mirrored db.

Hi All,

I have been struggling to make my classic ASP to call mirrored db successfully for the last few days. I am using a ODBC SQL Native Client. The front end application works perfectly untill the database is switched from principal to mirror server. I am getting the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

[Microsoft][SQL Native Client][SQL Server]Cannot open database "testDb" requested by the login. The login failed.

I will briefly explain what I have done. I hae created a SQL Login testUser and this login has the dbowner rights to testDb. This is being done in Principal server. Till now the application works fine. Once I switch it to failover, the mirror server acts as a principal - this is what I was expecting. On database level it works. But my ASP application doesnot seem to like it. When I closely looked into the security, I found that testUser does exist in the testDb but the real problem is testUser is not associated with testUser Login. It's not possible to change as well.

Any sort of pointer is appreciated.

Many thanks.

Sincerely,

Milan G

To correct the issue with the user not being tied properly to the Login, take a look at sp_change_users_login in BOL. When failing over, this will need to be run against all SQL users to tie them back to their logins. If you're able to use Windows authentication, that's preferable since the SIDs are the same and you don't need to mess with doing this.

|||

Hi M,

Many thanks for the pointer. Finally, I succeeded with your pointer. I found the this link a useful. http://www.mssqltips.com/tip.asp?tip=1166

cheers,

Milan G

Logic for Stored Procedure(s)

I have a table containing a 'queue' of rows waiting to be processed by my application.

Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?

If not, what is the best logic for doing it with two stored procedures ?

The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.

Any suggestions appreciated.

Steve.A single stored prodedure can have many lines of code, so sure, what you are asking is possible.

I'd do it like this:

SET NOCOUNT ON
BEGIN TRANSACTION
 SELECT TOP 1 @.myID = uniqueID FROM myTable ORDER BY datetime DESC
 SELECT someFields FROM myTable WHERE uniqueID = @.myID
 DELETE FROM myTable WHERE uniqueID = @.myID
COMMIT

I'd also put some error checking code in there and perform a ROLLBACK if there's an error.

Terri|||I might create a flag on myTable indicating that the record has processed, move the tran to the client, and update the flag once the process has sucessfully completed.

If the server commits the tran before the client finishes processing and the client error you will have no "easy" way of recovering a deleted record.|||Thanks guys - that helps a lot.

Steve.

Logging stored procedure use with MSDE (2000)

I have a app connected to MSDE that creates a report. The DB has over 300
stored procedures, with various args. I'm 9.9% sure the report must call a
SP. Can I somehow start a trace or log and have MSDE capture everything?
I'm hoping this way, I can discover the SP being run, along with the args.
I need to be able to run the report from the command line to get the output
into VBScript. (I know how to run a SP from VBScript, and massaging the
output shouldn't be a problem.)
Marco
You should be able to capture this using a server-side trace. Read about Profiler in Books Online,
and also read about, for instance sp_trace_create (etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:u14RnmN4GHA.2420@.TK2MSFTNGP02.phx.gbl...
>I have a app connected to MSDE that creates a report. The DB has over 300
> stored procedures, with various args. I'm 9.9% sure the report must call a
> SP. Can I somehow start a trace or log and have MSDE capture everything?
> I'm hoping this way, I can discover the SP being run, along with the args.
> I need to be able to run the report from the command line to get the output
> into VBScript. (I know how to run a SP from VBScript, and massaging the
> output shouldn't be a problem.)
> Marco
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You should be able to capture this using a server-side trace. Read about
Profiler in Books Online,
> and also read about, for instance sp_trace_create (etc).
Am just using MSDE which doesn't seem to have Profiler. Looking in to
sp_trace_create.
Thanks.
|||I just replied to your other post. Try to get hold of a "full" SQL Server, if only for a minute, and
script a trace from there. Here's an example, btw:
/************************************************** **/
/* Created by: SQL Server Profiler 2005 */
/* Date: 2006/09/26 16:21:52 */
/************************************************** **/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere', @.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 15, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 1, @.on
exec sp_trace_setevent @.TraceID, 17, 9, @.on
exec sp_trace_setevent @.TraceID, 17, 6, @.on
exec sp_trace_setevent @.TraceID, 17, 10, @.on
exec sp_trace_setevent @.TraceID, 17, 14, @.on
exec sp_trace_setevent @.TraceID, 17, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 16, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 17, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 18, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 6, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 16, @.on
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 17, @.on
exec sp_trace_setevent @.TraceID, 12, 6, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 18, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 13, 12, @.on
exec sp_trace_setevent @.TraceID, 13, 1, @.on
exec sp_trace_setevent @.TraceID, 13, 9, @.on
exec sp_trace_setevent @.TraceID, 13, 6, @.on
exec sp_trace_setevent @.TraceID, 13, 10, @.on
exec sp_trace_setevent @.TraceID, 13, 14, @.on
exec sp_trace_setevent @.TraceID, 13, 11, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Server Profiler -
74e4bb26-8675-4e74-90d0-5793d57489d1'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:e2RVkeX4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Profiler in Books Online,
> Am just using MSDE which doesn't seem to have Profiler. Looking in to
> sp_trace_create.
> Thanks.
>