SQL,tables in a DB: bcp

Showing posts with label bcp. Show all posts

Thursday, March 8, 2012

"large value" data type(bcp)

I want to store some binary things(pic and so on), so I create a table which contain a a "varbinary" data-type column.

but 1. I used OPENROWSET to insert the large file in this table. 2. I used master..xp_cmdshell to retrieve data out as a file. One strange thing happened: the size of the input and output is really different(output is 1k bigger than the input file).

and it seems that the file is broken with different file format......

I really don't know why....

Any help would be appriciated.....

kavin

Could you please post a simple repro script? What command or utility are you using with xp_cmdshell to create the file? Is it BCP or OSQL with SELECT or BCP with queryout option and so on?|||

yes, sure.

EXEC master..xp_cmdshell 'bcp " SELECT column1 FROM Products where id =1111111117 " queryout fileName -n -U sa -P -S yourserver.

Is it any problem?

Anyway, thanks.

BR,

kavin

|||and when I compare the two files, I found that it just the 8-bytes at the beginning of file are extra filled. So I'm afraid something wrong with the created file?

Sunday, February 19, 2012

"Additional information: System error" message

Hello,
I needed to look at some .BCP files, so I thought I'd create a small tool for that.
I decided to create a temporary DB, attach empty mdf, ldf files with predefined structure and then insert the BCP files, so I can view the data as DB tables .

Here is the procedure I follow:
1) I drop the existing (if exists) DB ("Drop DBName") - to be prepared for the next set of .BCP files.
2) Then I create a new DB using sp_attach_db,
3) Then I populate the db via "Bulk Insert".

1st time this works just fine, but when I try to execute step 1 (drop DB) after step 3 I get a message that there is still open connection and the DB can't be dropped.

Here I can do two different things:
1) Find the SPID of the open connection to my DB and KILL the process or
2) Instead of killing the process, set the DB to "SINGLE_USER WITH ROLLBACK IMMEDIATE" and then drop the DB.

In both cases I was able to Drop the DB and attach the new one, however the Bulk Insert (via ExecuteNonQuery() ) for some reason closes the current connection and brakes with the "System Error" message.

I simulated this procedure on Query Analyser and on SQL Server Studio Express, but everything worked just fine there.

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

To make it short, here is the SQL Query procedure (which worked, though):

USE Master
if exists
(select name
from master.dbo.sysdatabases
where has_dbaccess(name)=1 and name='MyDB')
BEGIN
Drop database MyDB
END
exec sp_who2

-- Before the next command I have to manually restore the .ndf, .ldf files, deleted by the "Drop DB" command

sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'D:\CurrSchema\DB_Patient.ndf',
@.filename2 = N'D:\CurrSchema\DB_Definitions.mdf',
@.filename3 = N'D:\CurrSchema\DB_Log.ldf',
@.filename4 = N'D:\CurrSchema\DB_Data.ndf'
exec sp_who2

-- WAITFOR DELAY '00:00:04'
-- need to wait manually here. Waitfor won't work with "USE DB"

USE MyDB
BULK INSERT CAL
FROM '\\srv\D$\CurrFiles\CAL.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)

BULK INSERT CAP
FROM '\\srv\CurrFiles\CAP.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)
exec sp_who2

Has anybody seen something like this?

Thank you.

Haven't seen it personally. However, I suggest you insert a 'go' between each command to seperate them into batches. Also, be sure to change the database to 'master' for your connection before sending/executing 'drop db'.|||

Yes, Thank you - you were right. I did not need the 'GO', but the change to 'master' was the key. I should have been more careful when using somebody else's code - no matter how encapsulated, it's never context-free - in my case I had to add context switching with 'USE master'.

George.

|||

Hi... you mentioned you had the below source sample...

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

Can you please post or sent to billbbellevue@.hotmail.com

Kind regards,

Bill Bezick