Solved

SQL 2005 import/export data

Posted on 2009-05-04
13
777 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can Unique column have more than one Null? 8 60
Loops and updating in SQL Query 9 64
Parse this column 6 34
Why is this SQL bringing back extra rows? (parsing XML data) 4 34
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 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