• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

SQL 2005 import/export data

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
bhgewilson
Asked:
bhgewilson
  • 8
  • 5
1 Solution
 
SStoryCommented:
Have you tried DBCC?

Here's a good article on it:
http://articles.techrepublic.com.com/5100-10878_11-6142604.html
0
 
SStoryCommented:
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
 
bhgewilsonAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
bhgewilsonAuthor Commented:
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
 
SStoryCommented:
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
 
bhgewilsonAuthor Commented:
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
 
bhgewilsonAuthor Commented:
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
 
SStoryCommented:
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
 
bhgewilsonAuthor Commented:
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
 
bhgewilsonAuthor Commented:
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
 
bhgewilsonAuthor Commented:
I also know it is only failing on the very top or first table.
0
 
SStoryCommented:
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
 
bhgewilsonAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now