December 19, 2005 at 3:07 pm
I have been tasked with trying to find a way to programmatically create a format file to be burned to DVD when the BCP out command runs. This way, we will have a format file with every time we archive a table. I cannot seem to find a way to do this. Has anyone else tried this?
Cory
-- Cory
December 19, 2005 at 3:58 pm
I know that you can use BCP to generate a format file. Since BCP is a command line utility, you may want to try to generate an input file programatically. You can then, through your program, call bcp and pass it the input file. exmaple bcp [databasename] format -f [format file to create] < input.file.
Walk through the BCP create format file process without an input file and you will find out what kind of info you need in your input file. You could then, programatically, generate a file that has the same info. Clear as mud?
December 19, 2005 at 4:51 pm
I hesitate a little to send the following code, because I have to caveat it so heavilly. It was built for Sybase, and it was never really finished, so there might be a significant amount of tweaking needed, but as a place to start, it's better than a poke in the eye with a sharp stick.
IF OBJECT_ID('dbo.sp_formatfilegen') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_formatfilegen
IF OBJECT_ID('dbo.sp_formatfilegen') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_formatfilegen >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_formatfilegen >>>'
END
go
/* Sp_Formatfilegen - generate BCP format file */
/* */
/* */
/* BHR 4/97 (rework of Dave Webb version) */
set quoted_identifier off
go
create procedure sp_formatfilegen @objname varchar(32), @fdelimeter varchar(2) = "\t"
AS
DECLARE @col_cnt int
DECLARE @col_length int
DECLARE @col_prec int
DECLARE @col_name varchar(35)
DECLARE @col_type varchar(35)
DECLARE @default_len int
DECLARE @delim varchar(2)
DECLARE @num_col int
DECLARE @tab char(1)
SELECT @col_cnt = 0
SELECT @default_len = 25
SELECT @tab = char(09)
set nocount on
/* Declare cursor */
DECLARE curscol CURSOR FOR
SELECT syscolumns.name, syscolumns.prec, systypes.name, syscolumns.length
FROM sysobjects, syscolumns, systypes
WHERE sysobjects.type = 'U' AND
sysobjects.name = @objname AND
sysobjects.id = syscolumns.id AND
syscolumns.usertype = systypes.usertype
order by syscolumns.colid
FOR READ ONLY
/* Retrieve number of columns in target table */
SELECT @num_col = count(*)
FROM sysobjects, syscolumns, systypes
WHERE sysobjects.type ='U' AND
sysobjects.name = @objname AND
sysobjects.id = syscolumns.id AND
syscolumns.usertype = systypes.usertype
/* Print Sybase version number (needs to be 10) and number of columns) */
select '8.90'
select @num_col
/* Fetch first row */
OPEN curscol
FETCH curscol into @col_name, @col_prec, @col_type, @col_length
/* Cursor loop */
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @col_cnt = @col_cnt + 1
IF @col_length < @default_len
SELECT @col_length = @default_len
IF @col_prec > @default_len
SELECT @col_length = @col_prec + 2
IF @col_cnt = @num_col
SELECT @delim = "\n"
ELSE
SELECT @delim = @fdelimeter
select
@col_cnt,
@tab,
'DBCHAR',
@tab,
@tab,
@col_length,
@tab,
'"' + @delim + '"',
@tab,
@col_cnt,
@tab,
@col_name
FETCH curscol into @col_name, @col_prec, @col_type, @col_length
END
CLOSE curscol
DEALLOCATE curscol
RETURN
go
IF OBJECT_ID('dbo.sp_formatfilegen') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_formatfilegen >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_formatfilegen >>>'
go
GRANT EXECUTE ON dbo.sp_formatfilegen TO public
go
HTH
December 20, 2005 at 9:07 am
This should be close to what you need. Just make sure you use the format file generated for the export so the column widths are correct.
declare
@maxcol smallint, @table varchar(128)
declare @Width smallint, @Ord smallint, @Column varchar(128), @Collation varchar(128)
declare @format varchar(500)
set @table = '<table name>'
select @maxcol = max(ORDINAL_POSITION)
from information_schema.columns where table_name = @table
print '8.0'
print @maxcol
declare colcurs cursor local fast_forward for
select COLUMN_NAME as [Name],
ISNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) as Width,
ORDINAL_POSITION as Ord,
ISNULL(COLLATION_NAME,'') as Collation
from information_schema.columns where table_name = @table
open colcurs
while 1=1 begin
fetch next from colcurs into @Column, @Width, @Ord, @Collation
if @@fetch_status <> 0 break
set @format = 'SQLCHAR' + char(9) + '0' + char(9)
+ cast(@Width as varchar) + char(9)
+ case when @Ord = @maxcol then '"\r\n"' else '""' end + char(9)
+ cast(@Ord as varchar) + char(9)
+ @Column + char(9)
+ @Collation
print @format
end
close colcurs
deallocate colcurs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply