BCP IN: create table on-the-fly

Hello!
I have a BCP file (.BCP) and I want to do a BCP IN but I don't have a
table associated with it. (Lets say table got dropped). I am curious if
there is a way to create a table on-the-fly while doing BCP IN? Any neat
solution?
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***Whats wrong with CREATE TABLE xxx?
Andrew J. Kelly SQL MVP
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:eTq4h7FGGHA.3080@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I have a BCP file (.BCP) and I want to do a BCP IN but I don't have a
> table associated with it. (Lets say table got dropped). I am curious if
> there is a way to create a table on-the-fly while doing BCP IN? Any neat
> solution?
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Lets say ..I dont know the table structure. All I have found is a .BCP
file which is a binary file and can not be opened in text editor.
*** Sent via Developersdex http://www.examnotes.net ***|||Test Test (farooqhs_2000@.yahoo.com) writes:
> Lets say ..I dont know the table structure. All I have found is a .BCP
> file which is a binary file and can not be opened in text editor.
You now have a challenge. You see, there is not one possible table for
this file - but many. Some of them, makes more sense than others.
The file itself contains records only - there is no metadata. So all
you can to is to analyse combinations of bytes. Hm, here is a long
sequence of bytes with the ASCII codes of letter. Probably a character
string. And here is a sequence where every second byte is 0. Might
be Unicode.
In short, this will keep you busy a couple of days!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to what Erland suggested, I'd start looking for the format file
-
if there ever was one, or some documentation to what this BCP file was used
for.
ML
http://milambda.blogspot.com/

BCP Command - Login fails

Dear all,

I have a curious problem using BCP to import and export data. First of all I export data as XML file with the command:

bcp "SELECT * FROM [006]..hopibon FOR XML EXPLICIT" queryout c:\ortec\misc\data.xml -c -r "" -SMMOSSURPBM09 –T

Now this works fine. The table data is exported as data.xml. I use the exported data in an external tool and then the result of that is a csv file. This file needs to be imported again in the SQL database, so I can run some queries. I use the following commandline;

bcp "[606]..ORTECPlanning" IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

When I use this command I get the following error;

SQLState = 37000, NativeError = 4060

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[606]’. Login fails.

I tested the following:

1) Run the same command on local PC (SQL Installed) and then no problem at all. So the command seems to be correct.

2) Then I tried to use the –U –P parameters instead of –T. Then the error message changes and states login failed…

3) Changed the command to bcp "[606]..ORTECPlanning" IN "c:\ortec\misc\planning.csv" -c -F1 -t; -S"MMOSSURPBM09" –T

4) Changed the command to bcp [MMOSSURPBM09].[606]..ORTECPlanning IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

5) Remove –t; (suggestion) but this is causing the same error on the server and causing a new error unexpected EOF on the local machine

But no luck… I’m using the administrator to login and perform the command. Exactly the same user and situation as in the queryout command…. So then the question is what is wrong with the command;

bcp [606]..ORTECPlanning IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

bcp "SELECT * FROM [006]..hopibon FOR XML EXPLICIT" queryout c:\ortec\misc\data.xml -c -r "" -SMMOSSURPBM09 –T

Now this works fine

I notice that the commands that fail seem to be hitting a different server ([606]) -NOT [006].

bcp "[606]..ORTECPlanning" IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

When I use this command I get the following error;

SQLState = 37000, NativeError = 4060

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[606]’. Login fails.

Did I mis-read that?