Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts

Tuesday, March 27, 2012

attach db file copied previously

I got a mdf file from my co-worker
who just stopped SQL server and delete ldf file
and give me a mdf file.
if it was detached, it would work fine
but it was just copied.

Is there any way to attach this mdf file?
I tried sp_attach_db and sp_attach_single_file_db
and both failed.

thanks,second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311241337.7338362d@.posting.google.com>...
> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_single_file_db
> and both failed.
> thanks,

If sp_attach_single_file_db failed, then there is no other way to
attach the file. In future, it would be better to ask your colleague
to give you a backup of the database, not the database file(s). This
also means your colleague will not have to stop SQL Server.

Simon|||What error do you get back from the sp_attach_single_file_db command?

> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_single_file_db
> and both failed.
> thanks,

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index...epartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||I ran
sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
and got

Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'd:\Spectrum\Data\Spectrum_log.LDF' may be incorrect.

Neil Pike <neilpike@.compuserve.com> wrote in message news:<VA.00001010.008366e0@.compuserve.com>...
> What error do you get back from the sp_attach_single_file_db command?
> > I got a mdf file from my co-worker
> > who just stopped SQL server and delete ldf file
> > and give me a mdf file.
> > if it was detached, it would work fine
> > but it was just copied.
> > Is there any way to attach this mdf file?
> > I tried sp_attach_db and sp_attach_single_file_db
> > and both failed.
> > thanks,
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or http://www.ntfaq.com/Articles/Index...epartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq|||[posted and mailed, please reply in news]

neo (second714@.hotmail.com) writes:
>> > I got a mdf file from my co-worker
>> > who just stopped SQL server and delete ldf file
>> > and give me a mdf file.
>> > if it was detached, it would work fine
>> > but it was just copied.
> I ran
> sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
> and got
> Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'd:\Spectrum\Data\Spectrum_log.LDF' may be incorrect.

Tell your co-worker to never do that again! Deleting LDF files is a
very stupid thing to do!

It is not clear how you co-worker shut down SQL Server, but it does
not seem that this database was shut down cleanly. If this database
was in the midst of the transaction, this transaction needs to be
rolled back, to bring the database to a consistent state. But to
roll back the transaction, you need the transaction log.

Yes, there *are* ways to attach a single MDF file in such a situation,
but these ways are not documented with a very good reason. It follows
from the previous paragraph that the database you get might be grossly
corrupt. On the other hand, if there was no activity in the database
at the time of the shutdown,

If you have a backup of this database, I suggest that you restore it.
If the database contains data which is not on a backup, and you really
need to save as much as possible, I would suggest that you open a
case with Microsoft. That is not going to be cheap, but neither is it
cheap to lose the data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Thursday, March 22, 2012

Atomic Transaction

Hi all,

If I have 2 database operations
1. Update ...
2. Delete ...

I want these 2 operations to execute atomically, thus I make use of

BEGIN Transaction ...
Update ...
Delete ...
COMMIT Transaction ...

But if one of these operation fails, it locks up the database. Does anyone have a resolution for this? How do I detect sql Exceptions to release the transaction so that it doesn't lock up the database?

Thanx

James :)I assume this is written in a stored procedure.

In that case

After your first UPDATE you need to chek

@.@.ERROR

If its Zero continue otherwise you need to ROLLBACK the transaction as the update failed. The same is true for your Delete statement. You should only COMMIT when both succeed. You should report or log the error somehow of course.

Have a look in Books Online under the topic

Using @.@.ERROR

its under Accessing and Changing relational Data :: Advanced Query Concepts :: Error Handling

regards

Steve|||Thanx, I will give that a try.
I love dbforums, people here's always got the answer.

James :D

Thursday, March 8, 2012

assign truncate rights to a user

i have a user who has delete rights on a table, but when i call truncate
table statement it says not enough permission.
how to assign truncate rightsIn 2000, you can't grant this. You need to be table owner or higher. In 2005
, you have some options.
From 2005 Books Online, TRUNCATE TABLE:
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permi
ssions default to the
table owner, members of the symin fixed server role, and the db_owner and
db_ddladmin fixed
database roles, and are not transferable. However, you can incorporate the T
RUNCATE TABLE statement
within a module, such as a stored procedure, and grant appropriate permissio
ns to the module using
the EXECUTE AS clause. For more information, see Using EXECUTE AS to Create
Custom Permission Sets.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vikram" <aa@.aa> wrote in message news:u2lvh83FGHA.1288@.TK2MSFTNGP09.phx.gbl...ed">
>i have a user who has delete rights on a table, but when i call truncate
> table statement it says not enough permission.
> how to assign truncate rights
>|||BOL says:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
symin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
"Vikram" <aa@.aa> wrote in message
news:u2lvh83FGHA.1288@.TK2MSFTNGP09.phx.gbl...
>i have a user who has delete rights on a table, but when i call truncate
> table statement it says not enough permission.
> how to assign truncate rights
>

Thursday, February 16, 2012

ASP.Net Unleashed Listing 12.16 Example

I am trying to get the following code to work but I keep getting an error.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__title__060DEAE8'. The conflict occurred in database 'pubs', table 'titleauthor', column 'title_id'.

Has anyone else experienced a problem with this example? Let me know what is wrong
with it.

Thanks,
Ralph


<%@. Page Language="VB" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %>
<script runat="server"
Sub Page_Load
Dim dstPubs As DataSet
Dim conPubs As SqlConnection
Dim dadTitles As SqlDataAdapter
Dim dtblTitles As DataTable
Dim drowTitle As DataRow
Dim objCommandBuilder As New SqlCommandBuilder

' Grab Titles Table
dstPubs = New DataSet()
conPubs = New SqlConnection( "Server='(local)';Database=Pubs;trusted_connection=true" )
dadTitles = New SqlDataAdapter( "Select * from Titles", conPubs )
dadTitles.Fill( dstPubs, "Titles" )
dtblTitles = dstPubs.Tables( "Titles" )

' Display Original Titles Table
dgrdOriginalTitles.DataSource = dstPubs
dgrdOriginalTitles.DataBind()

' Add a Row
drowTitle = dtblTitles.NewRow()
drowTitle( "Title_id" ) = "xxxx"
drowTitle( "Title" ) = "ASP.NET Unleashed"
drowTitle( "Price" ) = 1200.00
drowTitle( "Type" ) = "Mystery"
drowTitle( "PubDate" ) = #12/25/1966#
dtblTitles.Rows.Add( drowTitle )

' Delete the First Row
dtblTitles.Rows( 0 ).Delete()

' Double the price of the Second Row
drowTitle = dtblTitles.Rows( 2 )
drowTitle( "Price" ) *= 2

' Generate the SQL Commands
objCommandBuilder = New SqlCommandBuilder( dadTitles )

' Update Titles Table
dadTitles.Update( dstPubs, "Titles" )

' Display New Titles Table
dgrdNewTitles.DataSource = dstPubs
dgrdNewTitles.DataBind()
End Sub

</script>
<html>
<head>
<title>UpdateDataSet</title>
</head>
<body>
<h2>Original Titles Table
</h2>
<asp:DataGrid id="dgrdOriginalTitles" Runat="Server"></asp:DataGrid>
<h2>New Titles Table
</h2>
<asp:DataGrid id="dgrdNewTitles" Runat="Server"></asp:DataGrid>
</body>
</html>

change this line

dgrdOriginalTitles.DataSource = dstPubs

to this

dgrdOriginalTitles.DataSource = dtblTitles

see if that works|||Thanks for the suggestion. I tried something else...I remarked out the following code and then it worked fine.

Thanks,
RT


' Update Titles Table
dadTitles.Update( dstPubs, "Titles" )