authentication problem with SQL 2000

Hello guys... I have been struggling for quite some time trying to have the authentication page on my website work :-( and I'm now desperate for some help.

I have used VWD to create a website and I have a login.aspx page that authentiocate users based on their credential... all of it created with the web admin tool.
Now guess what! I have an instance of SQL 2005 running on my PC and it all works smoothly (users are screened and redirected to pages according to their status) but when I'm uploading the website to the company's server which is only authorized to run on SQL 200 I get the following error message:

Server Error in '/newmedical/webtools' Application.

---

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684883 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +890 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +601 System.Data.SqlClient.SqlInternalConnectionTds..ctor(SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +159 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +108 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +445 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +304 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +126 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +239 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

Thanks in advance for your tips and advices
JY

Jeewai:

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

No, you can use aspnet_regsql.exe (should be in C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxx\) to created the database objects required by the membership in any SQL database. Certaintly you can use SQL2000, however you have to do some configuration. Please take a look at this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Thanks so muchIori_Jay for your link to Scott's weblog. I really appreciate the help.
OK, I have run the aspnet_regsql to change my Application Services to use Sql Server 2000 (not Express) on the company's webserver and modified the web.config file to point the machine config file look up the SQL 2000 db for user credentials. I run VWD locally and the application runs fine and is able to connect to the remote company's server to authenticate users.(the newly created tables...aspnet_member and so on... in the webserver db are getting the credentials I've set up using the asp.net web admin tool)

Now after ftping the app to the company's webserver (running SQl 2000) and trying to login on the browser this time I get :

Server Error in '/newmedical' Application.

---

Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +786258 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684870 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1751 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +37 System.Data.SqlClient.SqlDataReader.get_MetaData() +58 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +213 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +570 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +134 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Do I have to runaspnet_regsql.exe on the web server or check the .NET framework version which my may not be the same as the one in my development environment? I'm at a loss and any tips would be greatly welcome.

Thanks again in advance for your help. I do believe that others may have encountered the same error and then any help would benefit us all.

Jeewai

|||The new error is a .NET error, not related to the database schema. It indicates to excute the 'aspnet_Membership_GetPasswordWithFormat' stored procedure, you need to give values to the parameters. Can you find where you call the 'aspnet_Membership_GetPasswordWithFormat' procedure? Or it is implictly called by some control?|||

Success ! I found out the technical glitch that was preventing the authentication on the company's webserver. Indeed I updated the webserver's framework to version :v 2.0.50727 which was the one used in my development environment. Then I ran the aspnet_reg utility on the webserver to update the tables and voila...the faulty aspnet_Membership_GetPassword... was fixed and now the application is running wonderfully.

So I hope this short explanation will help a lot of struggling programmers regarding the aforementioned issue. Thanks again to Iori_Jay.

JY

Authentication Problem

Hy guys,

I'm really out of the box, and totally have no idea what else i can do or try.
Weird sh*t is going on with my Sql server management studio.

Situation of the problem:

At work we have different servers. Servernames are (sv01, sv02, sv03, ...)
On sv04 is running a complete SQL Server Enterprise edition 2005. We are all logged in on our domain, and connect to the SQL Database Engine on sv04, using Windows Authentication, using the Sql server management studio.

This worked fine, until a few days ago.
Now, when i try to connect with "Windows Authentication", (and yes, the user i'm logging in with has the correct permissions and/or server rights on the domain and on the database).

After logging in while using windows authentication, i get the following error.
<Click here for screenshot>

I already tried:
*) clearing all my temporary files, cookies, cache.
*) Reboot
*) System restore to an earlier point where i was'nt having the problem
*) Searched in registry for "Testuser1" (found nothing)
*) Completely removed everything from Microsoft SQL (Control panel > add/remove programs > ...)

Nothing helped...
Anyone with a solution?

Thx 4 the one who helps me out !

Greetzz Hephie

Hi,

I assume HQ is your domain name.

Can you contact your network administrator and find out if someone has added the user "testuser1" and if it is not needed, can it be removed.

TimDickHarry.

|||hi,

thanks for the fast reply.
HQ is indeed the domain name.

Testuser1 is indeed also a domain name user. But it was there before, and it has always been there. No problems before.

The user can not be deleted, because it's a domain user for testing and developing our applications.

Testuser1 has no rights on the database, but it's not used to connect to the database.
But it think we have to look in another direction. In some way when we choose "Windows authentication" in the sql server management studio, he takes his own way and uses another user to connect to the database.

Possible causes might be, a problem with caching or a stored file somehwere that remembers a login or ... But like i said before, i already removed all of those temp/cache/cookies files.

Anyone else?

|||

In your screenshot, Thomasg is a SQL login, and it is NOT being used -Windows Authentication is being used, and Windows is attempting to log in as [Testuser1].

Are you certain that you are not logged into your local machine as 'testuser1'?

What happens when you completely log out of Windows, and then log back in?

|||Hy,

Yes i'm 100% sure that i'm NOT logged in as testuser1.
In my first post you can see I already try to reboot, re?nstall the complete SQL things..., etc...

Logging out and logging back in didn't solve the problem neither.

Greetzz

attribute search in a database

Hi guys,

I'm trying to find some attributes within a database I'm working with. I'm working with SQL Server 2000, and I'm trying to find table which have a certain attribute in them. for example, If I want to see tables from my database which have an attribute called 'home_address', what would the query be? Or is there an inbuilt function in SQL Server 2000 which allows me to search this?

Thank you!

You can query the syscolumns table directly.

SELECT object_name(id) AS objectname
FROM syscolumns
WHERE name = 'Home_Address'

HTH!

|||

Or have a look at INFORMATION_SCHEMA.columns view.

Code Snippet

SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'Home_Address'

|||

Of course. A much better suggstion that directly accessing the system tables.

Nice one!

Attribute key cannot be found issue after migration

Hi Guys,

I have migrated my cubes from 2000 to 2005. My cubes are getting processed fine (after fixing known issues) but it looks there is problem with table join with date dimension so lots of rows coming as not found.

This is the error/warning I get while processing however cube is processing fine.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: <fact table>, Column: DT_ID, Value: 2005010208. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DT_ID of Dimension: Date from Database: Risk Reports, Cube: <cube name>, Measure Group: <name>, Partition: <name>, Record: 6.

The error is very simple and I know what is happing. I have date dimensions which is 30 days only and while processing cube is reading 2005 data. Now in AS 2000 when I check SQL while processing cube I see join between fact table and date dimension. but in 2005 I don't see that join. In 2005 it just select from fact table.

The partition is build using fact table. I think I can fix this issue by making partition as query and join with date dimension but I feel it should be done at data source view level not at partition level.

I am not able to find how I can do at data source view level any idea what I am talking about?

Thank you - Ashok

Hey ashok, in your DSV, you can change your fact table to a Named Query, and limit the pull to only have the related data in your date dimension table..

something like:

select field1,field2,field3 from FACT_TABLE where DT_ID in(
select distinct DT_ID from dim_DATE)

I'm not sure if that's what you want to do, or if that's what you meant.. If you want the unrelated data, you could always just ignore the error in your error configuration, too..

Hope that helps?

C.|||

Named Query will work fine. What I was thinking and I am not sure it is possible or not. In Data Source View when we create relation between fact and dimension tables is there any was that we can set that some join will be taken while cube processing.

In other words in my case I want my fact table should always join with date dimension while processing cube even my cube partition is created using fact table only not query or fact table as named query. I like to have some properties setting in data source view so that while processing cube it should join fact and date dimension based on join key set in data source view.

Thank you - Ashok

attaching mdf & ldf files

Hi,
Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
database, formatted that server with the help of a sys admin.
I am aware that attach wont work 90% times without a detach. Now the dev.
guys say that they have some sp codes in that db and expecting me to make DB
available.
I am sure that chances are less but I am giving a try and this is the
present situation.
step1:
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'd:\backup\MyDB_Data.mdf',
@.filename2 = N'd:\backup\MyDB_log.ldf'
output:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
step2:
EXEC sp_attach_single_file_db @.dbname = 'MyDB',
@.physname = 'd:\bkup\MyDB_Data.mdf'
output:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000028840000 in
file 'd:\backup\MyDB_Data.mdf'.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
New log file 'd:\backup\MyDB_log.LDF' was created.
Connection Broken
Whats happening & what else i can try.
Thanks
reddi
Reddi,
Have you recreated the exact same paths that were on the original build?
Have you got a backup you can restore from?
My hopes are not high that you will be able to recover this database. If
you are really desperate you should give MS PSS a call.
http://www.mssqlserver.com/faq/general-pss.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Reddi wrote:
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>
|||Hi Mark,
Have you recreated the exact same paths that were on the original build?
--These are just mdf and ldf files which were moved onto other server by
stopping the SQL Server. Do you mean to say that I need to create a similar
directory path as in source server and try to attach?
Have you got a backup you can restore from?
--yes but a old one..
Thanks
Reedi
"Mark Allison" wrote:

> Reddi,
> Have you recreated the exact same paths that were on the original build?
> Have you got a backup you can restore from?
> My hopes are not high that you will be able to recover this database. If
> you are really desperate you should give MS PSS a call.
> http://www.mssqlserver.com/faq/general-pss.asp
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Reddi wrote:
>
|||On the second step, did you check if your database was restored?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>
|||Hi Peter,
Thanks for the input.. i havent check that.. request u to keep this thread
on watch
will be back soon
reedi
"Peter Yeoh" wrote:

> On the second step, did you check if your database was restored?
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> a
> DB
> Files\Microsoft
>
>
|||Hi Peter,
Extremely sorry for the delay.. after executing step 2, I dont see that
database created in EM.
Thanks
Reedi.
"Reddi" wrote:
[vbcol=seagreen]
> Hi Peter,
> Thanks for the input.. i havent check that.. request u to keep this thread
> on watch
> will be back soon
> reedi
>
> "Peter Yeoh" wrote:
|||Hi Reddi,
This might of help to you
http://www.spaceprogram.com/knowledg...leted_log.html
http://www.spaceprogram.com/knowledg..._received.html
http://forums1.itrc.hp.com/service/f...hreadId=653793
Regards
Ravi
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message news:<A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com>...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi

attaching mdf & ldf files

Hi,
Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
database, formatted that server with the help of a sys admin.
I am aware that attach wont work 90% times without a detach. Now the dev.
guys say that they have some sp codes in that db and expecting me to make D
B
available.
I am sure that chances are less but I am giving a try and this is the
present situation.
step1:
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'd:\backup\MyDB_Data.mdf',
@.filename2 = N'd:\backup\MyDB_log.ldf'
output:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
step2:
EXEC sp_attach_single_file_db @.dbname = 'MyDB',
@.physname = 'd:\bkup\MyDB_Data.mdf'
output:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000028840000 in
file 'd:\backup\MyDB_Data.mdf'.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
New log file 'd:\backup\MyDB_log.LDF' was created.
Connection Broken
Whats happening & what else i can try.
Thanks
reddiReddi,
Have you recreated the exact same paths that were on the original build?
Have you got a backup you can restore from?
My hopes are not high that you will be able to recover this database. If
you are really desperate you should give MS PSS a call.
http://www.mssqlserver.com/faq/general-pss.asp
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Reddi wrote:
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>|||Hi Mark,
Have you recreated the exact same paths that were on the original build?
--These are just mdf and ldf files which were moved onto other server by
stopping the SQL Server. Do you mean to say that I need to create a similar
directory path as in source server and try to attach?
Have you got a backup you can restore from?
--yes but a old one..
Thanks
Reedi
"Mark Allison" wrote:

> Reddi,
> Have you recreated the exact same paths that were on the original build?
> Have you got a backup you can restore from?
> My hopes are not high that you will be able to recover this database. If
> you are really desperate you should give MS PSS a call.
> http://www.mssqlserver.com/faq/general-pss.asp
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Reddi wrote:
>|||On the second step, did you check if your database was restored?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>|||Hi Peter,
Thanks for the input.. i havent check that.. request u to keep this thread
on watch
will be back soon
reedi
"Peter Yeoh" wrote:

> On the second step, did you check if your database was restored?
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> a
> DB
> Files\Microsoft
>
>|||Hi Peter,
Extremely sorry for the delay.. after executing step 2, I dont see that
database created in EM.
Thanks
Reedi.
"Reddi" wrote:
[vbcol=seagreen]
> Hi Peter,
> Thanks for the input.. i havent check that.. request u to keep this thread
> on watch
> will be back soon
> reedi
>
> "Peter Yeoh" wrote:
>|||Hi Reddi,
This might of help to you
http://www.spaceprogram.com/knowled...s_received.html
http://forums1.itrc.hp.com/service/...threadId=653793
Regards
Ravi
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message news:<A0C79772-6A2F-471D-B842-ECD
98AC9F244@.microsoft.com>...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi

attaching mdf & ldf files

Hi,
Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
database, formatted that server with the help of a sys admin.
I am aware that attach wont work 90% times without a detach. Now the dev.
guys say that they have some sp codes in that db and expecting me to make DB
available.
I am sure that chances are less but I am giving a try and this is the
present situation.
step1:
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'd:\backup\MyDB_Data.mdf',
@.filename2 = N'd:\backup\MyDB_log.ldf'
output:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
step2:
EXEC sp_attach_single_file_db @.dbname = 'MyDB',
@.physname = 'd:\bkup\MyDB_Data.mdf'
output:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000028840000 in
file 'd:\backup\MyDB_Data.mdf'.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
New log file 'd:\backup\MyDB_log.LDF' was created.
Connection Broken
Whats happening & what else i can try.
Thanks
reddiReddi,
Have you recreated the exact same paths that were on the original build?
Have you got a backup you can restore from?
My hopes are not high that you will be able to recover this database. If
you are really desperate you should give MS PSS a call.
http://www.mssqlserver.com/faq/general-pss.asp
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Reddi wrote:
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>|||Hi Mark,
Have you recreated the exact same paths that were on the original build?
--These are just mdf and ldf files which were moved onto other server by
stopping the SQL Server. Do you mean to say that I need to create a similar
directory path as in source server and try to attach?
Have you got a backup you can restore from?
--yes but a old one..
Thanks
Reedi
"Mark Allison" wrote:
> Reddi,
> Have you recreated the exact same paths that were on the original build?
> Have you got a backup you can restore from?
> My hopes are not high that you will be able to recover this database. If
> you are really desperate you should give MS PSS a call.
> http://www.mssqlserver.com/faq/general-pss.asp
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Reddi wrote:
> > Hi,
> >
> > Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> > database, formatted that server with the help of a sys admin.
> >
> > I am aware that attach wont work 90% times without a detach. Now the dev.
> > guys say that they have some sp codes in that db and expecting me to make DB
> > available.
> >
> > I am sure that chances are less but I am giving a try and this is the
> > present situation.
> >
> > step1:
> >
> > EXEC sp_attach_db @.dbname = N'MyDB',
> > @.filename1 = N'd:\backup\MyDB_Data.mdf',
> > @.filename2 = N'd:\backup\MyDB_log.ldf'
> >
> >
> > output:
> >
> > Server: Msg 5173, Level 16, State 2, Line 1
> > Cannot associate files with different databases.
> >
> >
> > step2:
> >
> > EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> > @.physname = 'd:\bkup\MyDB_Data.mdf'
> >
> > output:
> > Server: Msg 823, Level 24, State 2, Line 1
> > I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> > file 'd:\backup\MyDB_Data.mdf'.
> > Device activation error. The physical file name 'C:\Program Files\Microsoft
> > SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> > New log file 'd:\backup\MyDB_log.LDF' was created.
> >
> > Connection Broken
> >
> > Whats happening & what else i can try.
> >
> > Thanks
> >
> > reddi
> >
>|||On the second step, did you check if your database was restored?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>|||Hi Peter,
Thanks for the input.. i havent check that.. request u to keep this thread
on watch
will be back soon
reedi
"Peter Yeoh" wrote:
> On the second step, did you check if your database was restored?
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> > Hi,
> >
> > Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
> a
> > database, formatted that server with the help of a sys admin.
> >
> > I am aware that attach wont work 90% times without a detach. Now the dev.
> > guys say that they have some sp codes in that db and expecting me to make
> DB
> > available.
> >
> > I am sure that chances are less but I am giving a try and this is the
> > present situation.
> >
> > step1:
> >
> > EXEC sp_attach_db @.dbname = N'MyDB',
> > @.filename1 = N'd:\backup\MyDB_Data.mdf',
> > @.filename2 = N'd:\backup\MyDB_log.ldf'
> >
> >
> > output:
> >
> > Server: Msg 5173, Level 16, State 2, Line 1
> > Cannot associate files with different databases.
> >
> >
> > step2:
> >
> > EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> > @.physname = 'd:\bkup\MyDB_Data.mdf'
> >
> > output:
> > Server: Msg 823, Level 24, State 2, Line 1
> > I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> > file 'd:\backup\MyDB_Data.mdf'.
> > Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> > SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> > New log file 'd:\backup\MyDB_log.LDF' was created.
> >
> > Connection Broken
> >
> > Whats happening & what else i can try.
> >
> > Thanks
> >
> > reddi
> >
>
>|||Hi Peter,
Extremely sorry for the delay.. after executing step 2, I dont see that
database created in EM.
Thanks
Reedi.
"Reddi" wrote:
> Hi Peter,
> Thanks for the input.. i havent check that.. request u to keep this thread
> on watch
> will be back soon
> reedi
>
> "Peter Yeoh" wrote:
> > On the second step, did you check if your database was restored?
> >
> > --
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
> >
> >
> > "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> > news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> > > Hi,
> > >
> > > Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
> > a
> > > database, formatted that server with the help of a sys admin.
> > >
> > > I am aware that attach wont work 90% times without a detach. Now the dev.
> > > guys say that they have some sp codes in that db and expecting me to make
> > DB
> > > available.
> > >
> > > I am sure that chances are less but I am giving a try and this is the
> > > present situation.
> > >
> > > step1:
> > >
> > > EXEC sp_attach_db @.dbname = N'MyDB',
> > > @.filename1 = N'd:\backup\MyDB_Data.mdf',
> > > @.filename2 = N'd:\backup\MyDB_log.ldf'
> > >
> > >
> > > output:
> > >
> > > Server: Msg 5173, Level 16, State 2, Line 1
> > > Cannot associate files with different databases.
> > >
> > >
> > > step2:
> > >
> > > EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> > > @.physname = 'd:\bkup\MyDB_Data.mdf'
> > >
> > > output:
> > > Server: Msg 823, Level 24, State 2, Line 1
> > > I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> > > file 'd:\backup\MyDB_Data.mdf'.
> > > Device activation error. The physical file name 'C:\Program
> > Files\Microsoft
> > > SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> > > New log file 'd:\backup\MyDB_log.LDF' was created.
> > >
> > > Connection Broken
> > >
> > > Whats happening & what else i can try.
> > >
> > > Thanks
> > >
> > > reddi
> > >
> >
> >
> >|||Hi Reddi,
This might of help to you
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
http://www.spaceprogram.com/knowledge/sqlserver_recover_emails_received.html
http://forums1.itrc.hp.com/service/forums/questionanswer.do?admit=716493758+1098084882912+28353475&threadId=653793
Regards
Ravi
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message news:<A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com>...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi