Solved

SQL 2005 import/export data

Posted on 2009-05-04
13
748 Views
Last Modified: 2012-06-27
I am pretty much a novice with SQL 2005/2008.  I am working with a new client that has GFI Mail Archiver.  From what I can tell the database that is setup to archive the messages is corrupt.  The DB is 2.6GB and I have a very large binary attachments file.  No big deal but this one DB will not work.  

I can't figure out what is going on.  
1- What are my options to datacheck?
2- I have tried exporting to a newly created DB and it still fails?
3- I have tried to export to a csv file and it still fails.  

Basically I need help all over.  Any suggestions.  This is time critical.
0
Comment
Question by:bhgewilson
  • 8
  • 5
13 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 24296987
Have you tried DBCC?

Here's a good article on it:
http://articles.techrepublic.com.com/5100-10878_11-6142604.html
0
 
LVL 25

Expert Comment

by:SStory
ID: 24297036
I'm thinking if it is corrupt you are not going to be able to export import.  Try DBCC to see if it can be repaired.  By all means before you try anything make a backup of the db.  There are many way to do that.
If it is a server that you can shutdown for a few minutes, just stop the SQLServer service and copy the .ldb and .mdb files for that database somewhere else.  Then restart SQLServer service.

There is also the

    bcp

command-line tool:
http://technet.microsoft.com/en-us/library/ms187042.aspx

0
 

Author Comment

by:bhgewilson
ID: 24297077
Thanks for the quick reply.  I have tried the following.  I did a complete backup from the working server and setup another server in shop to test with.

I restrored the data and started here.

EXEC sp_resetstatus db_bad;

ALTER DATABASE db_bad SET EMERGENCY

DBCC checkdb (db_bad)

ALTER DATABASE db_bad SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (db_bad, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE db_bad SET MULTI_USER


I was hoping this would fix it but still it will not work for Archiver.  Any other suggestions.
0
 

Author Comment

by:bhgewilson
ID: 24297212
Also in trying to import/export the data from the bad db into a good one I keep getting the following.

- Execute the transfer with the TransferProvider. (Error)
Messages
ERROR : errorCode=-1073548784 description=Executing the query "drop table [db3].[sys].[arc_add]
" failed with the following error: "Cannot drop the table 'db3.sys.arc_add', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 
ERROR : errorCode=-1073548784 description=Executing the query "CREATE TABLE [db3].[sys].[arc_add] (
[id] int NOT NULL,
[email] nvarchar(255) NOT NULL,
[type] tinyint,
[name] nvarchar(255)
)
" failed with the following error: "The specified schema name "sys" either does not exist or you do not have permission to use it.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 
ERROR : errorCode=-1073548784 description=Executing the query "ROLLBACK TRANSACTION" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 
ERROR : errorCode=-1073548784 description=Executing the query "drop table [db3].[sys].[arc_add]
" failed with the following error: "Cannot drop the table 'db3.sys.arc_add', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 



0
 
LVL 25

Expert Comment

by:SStory
ID: 24298950
this may be a dumb question, but you are logged in as

 sa

right?

If so, I can't imagine why you wouldn't have permissions unless the system tables are damaged or something.  have you tried something like bcp?
It was always a pain for me to use, but I know it will do exports.

is the new table named the same as the old one/same db?
If so probably the script tries to do a DROP to drop the table before creating it and then importing.  If it is the same name, then perhaps it is trying to drop the table it is also trying to use or something--yes I am just guessing.  What it you edited the script to remove or comment out the DROP command preceding the CREATE TABLE for that table which is giving your grief?
0
 

Author Comment

by:bhgewilson
ID: 24299196
I am way more novice than you think.  

Yes I am logging in as SA.  what is BCP?

Yes it is the same db name.  Should I start a blank database without any tables and try that?

Not sure about your last line but if you can provide some assistance maybe I can get through it.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:bhgewilson
ID: 24299226
I created a brand new db and started the import and here is what I got.


TITLE: SQL Server Import and Export Wizard

------------------------------
 

Error 0xc002f210: Drop table(s) SQL Task: Executing the query "drop table [db5].[dbo].[arc]

" failed with the following error: "Cannot drop the table 'db5.dbo.arc', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 
 

------------------------------

BUTTONS:
 

OK

------------------------------

Open in new window

0
 
LVL 25

Accepted Solution

by:
SStory earned 500 total points
ID: 24299275
OK. I imagine you are trying to use something to do an export. Perhaps that something is just generating a big text file ( a script) that can recreate the database by issues TSQL commands
commands like
CREATE TABLE Mytable.....etc
will create a new table

DROP TABLE Mytable
will delete the table from the database

One the table is created, relationships can be created, etc.
Then a bunch of INSERT statements could be issued to insert every single row in the table.
like...

INSERT INTO MyTable (field1,field2,field3) VALUES(1253,'SomeText',23.45)

syntax might not be exactly right--this from memory...but these statements can be used to create the db.

many times in a script, right before the
CREATE TABLE,

you will see a
DROP TABLE

this should drop the existing table ..if there is one and then
CREATE TABLE
will not have a conflict in creating a table with the same name.

I don't know if the statement is checking for table existence before doing a DROP TABLE and if not, is that what causes it to bomb.  

If you are trying to export like this to a script (text file) and then import that to the new database, then the script file generated is text and could be viewed in WordPad or some editor  (notepad++ is my favorite)

You could find the place where it is creating that table that it bombs out on by searching for its name.

table names also are preceded by the owner and maybe the name of the db --I don't remember
Most of the times the table is owned by "dbo"  so "dbo." something

bcp is a bulk import export utility that runs from the command-line. It was always a pain for me to use, but I did send you a link.

>Should I start a blank database without any tables and try that?
First, please tell me exactly what you are doing to try to do the import export.
0
 

Author Comment

by:bhgewilson
ID: 24299318
My current db is bad with mail archiver I was going to try to import the data from that set into a new db to see if this fixes the error.  I am going into the new db newly created and clicking import data.  From there I import from the bad mounted db.  

I have tried with both a new db with all the right tables\columns and one without and I keep getting this error.  

Error 0xc002f210: Drop table(s) SQL Task: Executing the query "drop table [db5].[dbo].[arc]
" failed with the following error: "Cannot drop the table 'db5.dbo.arc', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  

I am going to try the drop right now.
0
 

Author Comment

by:bhgewilson
ID: 24299360
This is what the code snippet looks like as I try to do table by table.  There is only about 20.

 

CREATE TABLE [db5].[dbo].[arc_add] (

[id] int NOT NULL,

[email] nvarchar(255) NOT NULL,

[type] tinyint,

[name] nvarchar(255)

)

Open in new window

0
 

Author Comment

by:bhgewilson
ID: 24299380
I also know it is only failing on the very top or first table.
0
 
LVL 25

Expert Comment

by:SStory
ID: 24304323
Is there a DROP command inside this script?
Is there a CREATE DATABASE command in there?

if there is a DROP command is there some like
DROP IF EXISTS([db5].[dbo].[arc]

or just
DROP [db5].[dbo].[arc]

if there is a CREATE DATABASE statement, you don't need to create the database first...if not, you do.

if there is no exist statement with the drop, then that is why the error.

BTW, this script can be run piece by piece in Query Analyzer (browser) or whatever that is called. I have been using MySQL for a while and forgotten.  You could start at the top of the script, copy, paste and run each command (top to bottom) and see where it is failing.
0
 

Author Closing Comment

by:bhgewilson
ID: 31577631
The db looked good all the way around.  Archiver was not having it.  I had to break it apart by months with GFI support.  Thanks,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now