Having problems using BCP for CSV output

I have been using SQL Server 2005 for a total of 2 days and am trying to
transfer table data from one server to another.

For whatever reason the original operator only gave us the DB in a BAK
file. The new server for security purpose won't accept that for a
restore unless it was made on their server.

I installed 2005 express and was able to restore on my computer. I was
then able to brute force recover the table 'structure' on the new server.


Now I need to recover the data however I need to do this with either a
brute force SQL insert or import a CSV.

So maybe if someone can help me with 2 questions. Is there a way to
output an SQL insert statement that includes the data? So far I only get
a template insert.

#2 I was able to get BCP and xp_cmdshell running but when I run the
samples I see on the net I only get the BCP syntax table in the pane,
which I think is telling me I am doing something wrong.

Here is the code I am using just to see if I can get it working.that I
got from the site:

http://www.simple-talk.com/sql/datab...ed-procedures/

**********************************************
declare @sql varchar(8000)

select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername

exec master..xp_cmdshell @sql
***********************************************

Thanks in advance for any help that may come my way.

Scotty

re: Having problems using BCP for CSV output

There are some third party tools you might consider for helping with
the transfer of the data. Look into the Red-Gate Software's SQL
Compare tool. A trial version will probably do the trick:

http://www.red-gate.com/index2.htm

Yet another option might be to select another host that will allow you
to use an existing SQL Server database.

As far as the BCP syntax, you were missing a space after the uppercase
S. I was able to test this script successfully:

--------------------------------------------------------
DECLARE @sql VARCHAR(8000)

SELECT @sql = 'bcp master..sysobjects out c:\sysobjects.txt -c -t, -T -
S ' + @@servername

EXEC master..xp_cmdshell @sql
--------------------------------------------------------

-Eric Isaacs

re: Having problems using BCP for CSV output

You can use these scripts by Narayana Vyas Kondreddi to generate INSERT
statements for your data:
http://vyaskn.tripod.com/code/generate_inserts_2005.txt
http://vyaskn.tripod.com/code/generate_inserts.txt

HTH,

Plamen Ratchev
http://www.SQLStudio.com

re: Having problems using BCP for CSV output

FutureShock (futureshock@att.net) writes:I think you should look for a new hosting service, and tell this service
that you are no longer interested in them.

The absolutely best way to move a database between one server to another
is through backup/restore. To wit, this reduces the risk that something
gets mangled in the copying.

Security issues? Of course, the database could include that is malicious
to the server, if their procedures are open to SQL injection. But it
would be no different if you load the server from scripts.

If you want to pursue this operator, you can try the Database
Publishing Wizard,
http://www.microsoft.com/downloads/d...1C5-BF17-42E0-
A410-371A838E570A&displaylang=en

It's not a bad idea to supplement with Red Gate's SQL Compare and SQL
Data Compare, to check that the wizard did not introduce changes.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: Having problems using BCP for CSV output

Eric Isaacs wrote:Thanks Eric

That seemed to have fixed my BCP problem and was able to recover most of
the tables. Some of the text in the remaining files have a mixture of
the delimitation marks so I will have to use the SQL insert statements
on those.
I learned a great deal in the last couple days.

Scotty

re: Having problems using BCP for CSV output

Erland Sommarskog wrote:I agree with the changing of host but I cannot convince the owner of
such. I was able to use the BCP for most files I will have to explore
the SQL Scripts and wizard for the rest.

Thanks for your help and effort.

Scotty

re: Having problems using BCP for CSV output

Plamen Ratchev wrote:Thanks

I will give these scripts a ringing out.

I am so used to MySQL and admin interface, this stuff hurts my lil mind.

Scotty

re: Having problems using BCP for CSV output

Erland Sommarskog wrote:Ok I downloaded this wizard and it worked perfectly. It allowed me to
finish up the rest of my restoration efforts.