help needed for Recovring database

I wanted to move my database log file to another drive, but unfortunately I
executed wrong command .
alter database ABC modify file
(name = 'ABC_log',filename= 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\ABC_log.LDF')
instead of
alter database ABC2 modify file
(name = 'ABC2_log',filename= 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\ABC2_log.LDF')
After this, I found my database in Suspect mode, so I executed these commands
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_resetstatus 'ABC2'
And restarted DB services, but database was still in suspect mode, after
checking every possibility, I detached that database, I don’t know why I did
this.
But now I am not able to attach database back. When I try doing this it’s
give me following error
Error 5173: Cannot associate files with different databases.
By the way when I attach this mdf file, it changes the logical file name to
ABC_Data.mdf
But when I execute following query, it displays different logical file name
with ABC.mdf
dbcc checkprimaryfile (N'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\ABC2.mdf', 3)
I believe if I change this logical file name I’ll be able to attach database
back.
Kindly help me the recovering database.
Note: I do not have the transaction log backup & current DB backup.
Farhan Iqbal
Error 5173 means, you are trying to attach files that belong to another
database. Check the file names that you are using for your attach.
"Farhan" <Farhan@.discussions.microsoft.com> wrote in message
news:89333AE6-EE72-4863-A73B-C9419112318C@.microsoft.com...
>I wanted to move my database log file to another drive, but unfortunately I
> executed wrong command .
> alter database ABC modify file
> (name = 'ABC_log',filename= 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\ABC_log.LDF')
> instead of
> alter database ABC2 modify file
> (name = 'ABC2_log',filename= 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\ABC2_log.LDF')
>
> After this, I found my database in Suspect mode, so I executed these
> commands
> sp_configure 'allow updates', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> EXEC sp_resetstatus 'ABC2'
> And restarted DB services, but database was still in suspect mode, after
> checking every possibility, I detached that database, I don't know why I
> did
> this.
> But now I am not able to attach database back. When I try doing this it's
> give me following error
> Error 5173: Cannot associate files with different databases.
> By the way when I attach this mdf file, it changes the logical file name
> to
> ABC_Data.mdf
> But when I execute following query, it displays different logical file
> name
> with ABC.mdf
> dbcc checkprimaryfile (N'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ABC2.mdf', 3)
> I believe if I change this logical file name I'll be able to attach
> database
> back.
> Kindly help me the recovering database.
> Note: I do not have the transaction log backup & current DB backup.
> Farhan Iqbal
>

Help me with this query

Hi,

Below is the query

Alter Proc Spr_FadDataPull
As
SET NOCOUNT ON
Begin
select distinct tbs.sessionid,
(Case
when PageDataTitle='Find A Doctor-Results' then '1'
else '0'
end) as Flag, tbPD.PageDataPath ,
convert(varchar(10),SessionCD,101) as Effective_Date,
(select tbS.SessionPageViewCount from (select count(S.SessionPageViewCount) from tbSession S
inner join tbPageAction as PA
on S.sessionid=PA.sessionid
inner join tbPageData as PD
on PD.PageDataid=PA.PageDataid
where PD.PageDateTitle='Find A Doctor-Results'
Group by S.SessionPageViewCount )) as PageViewCount
from tbSession as tbS
inner join tbPageAction as tbPA
on tbS.sessionid=tbPA.sessionid
inner join tbPageData as tbPD
on tbPD.PageDataid=tbPA.PageDataid
where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'
where tbS.SessionCD between '05/15/2007' and '06/18/2007'
End

SET NOCOUNT OFF

the error is

Line 17: Incorrect syntax near ')'. near the Group by clause

can anyone pls help me to fix this error.

Thanks in Advance,

sg

you need an alias on your derived table within the PageViewCount item

Code Snippet

Begin

selectdistinct tbs.sessionid,

(Case

when PageDataTitle='Find A Doctor-Results'then'1'

else'0'

end)as Flag, tbPD.PageDataPath ,

convert(varchar(10),SessionCD,101)as Effective_Date,

(select tbS.SessionPageViewCount from(selectcount(S.SessionPageViewCount)from tbSession S

innerjoin tbPageAction as PA

on S.sessionid=PA.sessionid

innerjoin tbPageData as PD

on PD.PageDataid=PA.PageDataid

where PD.PageDateTitle='Find A Doctor-Results'

Groupby S.SessionPageViewCount )as x

)as PageViewCount

from tbSession as tbS

innerjoin tbPageAction as tbPA

on tbS.sessionid=tbPA.sessionid

innerjoin tbPageData as tbPD

on tbPD.PageDataid=tbPA.PageDataid

where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'

where tbS.SessionCD between'05/15/2007'and'06/18/2007'

End

|||

Hi Dane,

thanks a lot,

i named the derived column as you Said its givinf this error

error:

No column was specified for column 1 of 'x'.

Please help.

Regards,

Vani.

|||

Your count(...) needs a valid name to be referenced.

Code Snippet

selectdistinct tbs.sessionid,

(Case

when PageDataTitle='Find A Doctor-Results'then'1'

else'0'

end)as Flag, tbPD.PageDataPath ,

convert(varchar(10),SessionCD,101)as Effective_Date,

(select tbS.SessionPageViewCount from(selectcount(S.SessionPageViewCount)as SessionPageViewCount

from tbSession S

innerjoin tbPageAction as PA

on S.sessionid=PA.sessionid

innerjoin tbPageData as PD

on PD.PageDataid=PA.PageDataid

where PD.PageDateTitle='Find A Doctor-Results'

Groupby S.SessionPageViewCount )as x

)as PageViewCount

from tbSession as tbS

innerjoin tbPageAction as tbPA

on tbS.sessionid=tbPA.sessionid

innerjoin tbPageData as tbPD

on tbPD.PageDataid=tbPA.PageDataid

where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'

where tbS.SessionCD between'05/15/2007'and'06/18/2007'

Help me please i have a problem in ALTER command

Dear all

I need to add new column in table TranPay , I use ALTER command on PDA on Button Click

Dim SqlStm = "ALTER TABLE TranPay ALTER COLUMN TranKey IDENTITY (1, 1)"

Dim Dcmd As SqlCeCommand = New SqlCeCommand

Dcmd.Connection = Conn

Dcmd.CommandText = SqlStm

Dcmd.ExecuteNonQuery()

But cannot create new Field , Help me please

Brg ,

Tingnong

Try this:

ALTER TABLE TranPay ALTER COLUMN TranKey int IDENTITY(1,1)

You must specify the column data type.|||

Try this:

Code Snippet

ALTER TABLE TranPay ADD TranKey int IDENTITY (1,1)