bcp to ignore text image - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp to ignore text image

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-29 : 14:40:07

So what do you do with text and image data type when you bcp them?

I'm just trying to ignore them...

The format card below doesn't export on rows...this is against NorthWind and the Categories Table.

The last 2 columns are ntext, then image


7.0 
4 
1	SQLCHAR	0	14	""	1	CategoryID 
2	SQLCHAR	0	30	""	2	CategoryName 
3	SQLCHAR	0	0	""	0	Description 
4	SQLCHAR	0	0	"\r\n"	0	Picture

I'm building some sprocs that will:

gen all the format cards
, then a sproc that will bcp all the data (fixed width)
, ftp the data to the mainframe (THANK YOU NIGEL)

...and then reverse the process.

And I want to build it so I can use it for any database.

I get the following (AND THANKS AGAIN NIGEL) For the



		insert #a
		exec master..xp_cmdshell @cmd

idea...it wouldn't redirect for some reason...

like it lost it after the >

The bcp is echoed to a bat file and the executed. Here's the bcp:


bcp Northwind.[dbo].[Categories] out d:\Data\Northwind\DATA\dbo_Categories.dat  -Sxxxxxxx -Usa -Pxxx  -fd:\Data\Northwind\FORMAT\dbo_Categories.fmt  > d:\Data\Northwind\DATA\dbo_Categories.log

And the results....


id          ouputtmp                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           NULL
2           C:\WINNT\system32>bcp Northwind.[dbo].[Categories] out d:\Data\Northwind\DATA\dbo_Categories.dat  -SNJROS1D136 -Usa -Pxxx  -fd:\Data\Northwind\FORMAT\dbo_Categories.fmt   
3           SQLState = S1000, NativeError = 0
4           Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server
5           NULL

It does work for other tables...well because they're not be skipped.

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-30 : 05:55:54

Think it's impossible... Only queryout can help...
But obviously you don't like this option.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-30 : 09:06:52

quote:
Originally posted by Stoad

Think it's impossible... Only queryout can help...
But obviously you don't like this option.

Thanks for the reply Stoad...

And ANYTHING to help me get through this is on the table...

I've never used queryout before....never had to deal with text or image really either..

Was lucky enough to design the systems without them...

Inhertance is not always a good thing...

(just curious? Why would you presume that?)

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-30 : 09:23:29

LOL... Not sure what is the 'inhertance',
I just thought you didn't want to complicate
your super-procs with this option and just sought
for a more plain solution.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-30 : 09:42:58

What's a little more dynamic sql..cursors...ahhhhhhh

No choice...all admin stuff though


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
	  @dbName sysname
	, @fp varchar(255)
	, @User varchar(255)
	, @Pwd varchar(255)
AS
/* 
	EXEC isp_bcp_out_database
		  'Northwind'
		, 'd:\Data\Northwind'
		, 'sa'
		, 'xxx'

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
	SELECT  -- 'EXEC Master..xp_cmdshell ' +
--		  '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
		  'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
		+ 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat  '
		+ '-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
		+ '-f'+@fp+'\FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt ' 
		+ ' > ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
		-- + ', no_output' AS CMD
	  FROM    INFORMATION_SCHEMA.Tables
	 WHERE    TABLE_TYPE = 'BASE TABLE'

DECLARE @CMD varchar(8000)

create table #a (id int identity(1,1), s varchar(1000))

OPEN bcpout

FETCH NEXT FROM bcpout INTO @CMD

WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @CMD
		SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
		EXEC master..xp_cmdshell @CMD 
		SELECT @CMD = @fp + '\bcpout.bat'
		SELECT @CMD
		insert #a
		exec master..xp_cmdshell @cmd


		FETCH NEXT FROM bcpout INTO @CMD
	END

CLOSE bcpout
DEALLOCATE bcpout

	select id, ouputtmp = s from #a

SET NOCOUNT OFF

Not so super....format files are gen'ed by another sproc, so I have to make sure the openquery and it are in synch..

I guess no one ever cbp's image (whats the point) or text data out.

I guess I could export text with its key and save it for an update on the return trip...

If I have a key...

Thanks for your help

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-30 : 13:38:58

Can't see what's the problem here. Just think up some
unusual field / row terminators (in case of image / text
columns) and... lucky bcping.

And what key are you talking about?

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-30 : 14:21:22

Problem is it just bcp's out the table...text and image columns as well

I'm currently modifying the format card gener (needs to be in fixed width for the mainframe), and the I'll make the bcp sproc using openquery using the gen'ed format file..it's more a matter of making sure all of the pieces fit...

And all of this effort just to create test data...

In the Old days....

Here's the format file generator (no error handling yet):

[Code]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO

CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, REPLACE(TABLE_NAME,' ','_'), TABLE_SCHEMA, SQLGroup, RowGrouping
FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),COUNT(*)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
AND DATA_TYPE NOT IN ('text','ntext','image')
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT 'XXX'+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
-- SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','ntext','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)
+CASE WHEN DATA_TYPE IN ('text','ntext','image') THEN '0' ELSE 'XXX' END
-- +CASE WHEN DATA_TYPE IN ('text','ntext','image') THEN '0' ELSE CONVERT(varchar(3),ORDINAL_POSITION) END
+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE DATA_TYPE NOT IN ('text','ntext','image')
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping

DECLARE @Card_Old varchar(200), @Card varchar(200), @TABLE_NAME sysname, @TABLE_SCHEMA sysname, @cmd varchar(200), @x char(2)
, @Command_String varchar(8000)
, @TABLE_NAME_OLD sysname, @TABLE_SCHEMA_OLD sysname, @SQLGroup int, @RowGrouping int, @cntr int, @linecntr int

OPEN FormatCard

FETCH NEXT FROM FormatCard INTO @Card_Old, @TABLE_NAME_Old, @TABLE_SCHEMA_Old, @SQLGroup, @RowGrouping

SELECT @x = '> ', @cntr = -1, @linecntr = 1

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM FormatCard INTO @Card, @TABLE_NAME, @TABLE_SCHEMA, @SQLGroup, @RowGrouping

IF @TABLE_SCHEMA+@TABLE_NAME <> @TABLE_SCHEMA_OLD+@TABLE_NAME_OLD
OR @@FETCH_STATUS <> 0
BEGIN

SET @cmd = 'echo '
+ REPLACE(REPLACE(
@Card_Old
,'""','"\r\n"')
,'XXX',CONVERT(varchar(3),@cntr))
+ ' '+ @x +' d:\Data\Northwind\Format\'
+@TABLE_SCHEMA_OLD+'_'+@TABLE_NAME_OLD+'.fmt'
SET @Command_string = 'EXEC master..xp_cmdshell '''
+ @cmd + ''', NO_OUTPUT'

Exec(@Command_String)
PRINT @Command_String

SELECT @TABLE_SCHEMA_OLD = @TABLE_SCHEMA
, @TABLE_NAME_OLD = @TABLE_NAME
, @Card_Old = @Card
, @x = '> '
, @cntr = -1
END
ELSE
BEGIN

SET @cmd = 'echo '
+ REPLACE(@Card_Old,'XXX',CONVERT(varchar(3),@cntr))
+ ' '+ @x +' d:\Data\Northwind\Format\'
+@TABLE_SCHEMA+'_'+@TABLE_NAME+'.fmt'
SET @Command_string = 'EXEC master..xp_cmdshell '''
+ @cmd + ''', NO_OUTPUT'

Exec(@Command_String)
PRINT @Command_String

SELECT @TABLE_SCHEMA_OLD = @TABLE_SCHEMA
, @TABLE_NAME_OLD = @TABLE_NAME
, @Card_Old = @Card
, @x = '>>'
, @cntr = @cntr + 1
END

END

CLOSE FormatCard
DEALLOCATE FormatCard

GO
[/code]

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-30 : 15:25:28

Just have tested your s-proc. Worked fine but completely
ignores text/image fields.

dbo_dtproperties.fmt


7.0 btw, version is hardcoded
5 
1	SQLCHAR	0	14	""	1	id 
2	SQLCHAR	0	14	""	2	objectid 
3	SQLCHAR	0	64	""	3	property 
4	SQLCHAR	0	255	""	4	value 
5	SQLCHAR	0	14	"\r\n"	5	version

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-30 : 15:39:09

quote:
Originally posted by Stoad

Just have tested your s-proc. Worked fine but completely
ignores text/image fields.

dbo_dtproperties.fmt

7.0 btw, version is hardcoded
5
1 SQLCHAR 0 14 "" 1 id
2 SQLCHAR 0 14 "" 2 objectid
3 SQLCHAR 0 64 "" 3 property
4 SQLCHAR 0 255 "" 4 value
5 SQLCHAR 0 14 "\r\n" 5 version

Yeah, thankls for the tip...it's suppose to ignore text and image fields...

EDIT: Columns out of order....man I'm fried....

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-30 : 15:50:40

Is there an option in bcp for special chars? they're not coming out correctly now...the rest of the pices are in place...

(Wonder how the mainframe will handle them anyway)

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-30 : 16:10:06

For example

Québec --From DTS

comes out of the bcp as

Qu‚bec

And there don't seem to be any option in BOL

I execute the QueryOut syntax and it comes out fine in QA?

So close....YET....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources