Re: archieving and deleting SQL 2000 table

From: Narayana Vyas Kondreddi (answer_me@hotmail.com)
Date: 10/25/02 

From: "Narayana Vyas Kondreddi" <answer_me@hotmail.com>
Date: Fri, 25 Oct 2002 17:00:35 +0100

you don't need IDENTITY property on the database you are archiving into. So,
you might want to remove the identity property for that column on the copied
database.

Or as the error message is suggesting, use SET IDENTITY_INSERT. More info in
Books Online.

Check out xp_smtp_sendmail at www.sqldev.net, that lets you send emails from
stored procedures. 

--
HTH,
Vyas, MVP (SQL Server)
SQL Server FAQ, articles, code samples, interview questions and more @
http://vyaskn.tripod.com/
"A. Tolga KILINĒ" <kilinc@tis.havelsan.com.tr> wrote in message
news:un7VqiCfCHA.2540@tkmsftngp12...
Hi,
I really find your site useful. I tried the stored procedure you give but
there are some problems with it. First, I created a similar database for
copying but I have such an error: "Error 8101: An explicit value for the
identity column in table 'Master.dbo.COPYTABLE' can only be specified when a
column list is used and IDENTITY_INSERT is ON" .
I set the IDENTITY_INSERT on but it did not worked.
How to solve this?
Secondly, can I get an email alert when this procedure runs or when someone
deletes the original table? how?
Regards,
Tolga
"Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
news:e2LFZTCfCHA.2240@tkmsftngp10...
> For info on archiving, try this link:
> http://vyaskn.tripod.com/sql_archive_data.htm
>
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server FAQ, articles, code samples, interview questions and more @
> http://vyaskn.tripod.com/
>
>
> "A. Tolga KILINĒ" <kilinc@tis.havelsan.com.tr> wrote in message
> news:Otut8Y$eCHA.2544@tkmsftngp11...
> > Hi,
> > there is a strict requirement from one of my customer that some SQL
tables
> > never deleted before they are archieved. First problem is how to achieve
> > archieving? Can I do backup instead??
> > I can set a user as backup operator, but then he can not delete the
table.
> > If I give rights to delete it, he can do it without backing it up!!
> > Please suggest a solution about the requirement.
> > Regards,
> > Tolga
> >
> >
>
>

Relevant Pages

  • Re: SqlServer 2005: Importing Data: Idenitity fields getting made into non-identities
    ... SqlServer database into another the identity fields get switched off ... Seems like the way to go is to click Edit SQL and add the IDENTITY ... I hope not all of them are using the IDENTITY property, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: VS2005 DB Tools cant set Identity prop for Primary Key columns?
    ... I also test with a SQL server 2000 database and got same result. ... designer or you just cannot change the identity property after you save ... Prev by Date: ...
    (microsoft.public.vsnet.ide)
  • Re: ODBC error 3151 (Connection failed, Access <-> SQL Server)
    ... We are using SQL Server and Access for a database application. ... access is via ODBC. ... This error is only raised by one function in VBA (when archiving the ...
    (microsoft.public.sqlserver.odbc)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)