Bulk copy out of text and image data

From SybaseWiki
Revision as of 07:14, 17 May 2006 by 1147847836 (Talk | contribs)

Jump to: navigation, search

When you want to export a database with bcp, you need to check if there is any text or image data (blobs) in the database, check the maximum size of these blobs and use this to specify the value for the -T argument in bcp command. Here are some usefull queries.

To determine the tables and columns in the database that use text/image data:

select user_name(o.uid) as "owner",
       o.name as "table",
       c.name as "column"
       from   sysobjects o,
              syscolumns c
       where  o.type = "U"
       and    o.id = c.id
       and    c.type in (34,35)
       order  by 1,2,3

Once you know which columns are used to store blobs, run the following query to select the maximum length of a blob in bytes:

select max(datalength(<column_name>)) from <table_name>

Here is a stored procedure to check a database. You need to use ASE 12.0 or later for this.

create proc sp_blobsize
as

set nocount on

declare myCursor cursor for
  select user_name(o.uid),o.name,c.name
  from sysobjects o,syscolumns c
  where o.type = "U" and o.id = c.id and c.type in (34,35)
  order by 1,2,3

declare @user varchar(30),@table varchar(30),@column varchar(30),@cmd varchar(512),@first tinyint,@db varchar(30)

select @first = 0,@db = db_name()

print "This utility looks for text and image data in tables and"
print "calculates the maximum size in bytes."
print "Checking database %1!",@db
print ""

open myCursor
if @@error != 0 return  1

fetch myCursor into @user, @table, @column

while @@sqlstatus = 0
begin
  if @first  = 0
  begin
    print "Output is shown in the format owner.table.column - maximum size"
    print ""
    select @first  = 1
  end

  select @cmd = "declare @l int select @l=max(datalength(" + rtrim(@column) + ")) from " + rtrim(@user) + "."
    + rtrim(@table) + " print '%1!.%2!.%3! - %4!','" + @user + "','" + @table + "','" + @column + "',@l"

  exec (@cmd)

  if  @@error != 0 break

  fetch myCursor into @user, @table, @column
end

close myCursor
deallocate cursor myCursor

if @first = 0 print "No text/image data found in this database."

return  0
go











[We are delicate. We do not delete your content.] [l_sp2008]

ringtone maker verizon ringtone US Cellular Ringtone bcbg shoes waterford crystal swarovski crystal bead mesothelioma lawsuits mesothelioma symptoms mesothelioma diagnosis Naturalizer Shoes Free Kyocera Ringtone Sexy Prom Dresses Naturalizer Shoes Aero Bed Free Sprint Ringtones Free Verizon Ringtones free nextel ringtones sexy prom dress Formal Prom Dresses cheap prom dresses Plus Size Prom Dresses tiffany prom dresses erotic games strip poker pokemon trading card game rom hoyle card games buy ativan online buy ativan buy alprazolam online buy alprazolam cheap tramadol buy bontril online fentermine how to buy fioricet on line cheap ultram without prescription buy ultram without prescription teen bra Bra Teen Cleavage Micro Bikini Teens Bra sexy bras bulma bra sheer bra auto loan calculator Federal Student Loan Consolidation private student loan consolidation acs student loans countrywide home loans refinance home loan st louis wacoal bras teen bra unsecured signature loan Countrywide Home Loans







[We are delicate. We do not delete your content.] [l_sp1]

ringtone maker verizon ringtone US Cellular Ringtone bcbg shoes waterford crystal swarovski crystal bead mesothelioma lawsuits mesothelioma symptoms mesothelioma diagnosis Sexy Prom Dresses Naturalizer Shoes Aero Bed Free Sprint Ringtones Free Verizon Ringtones free nextel ringtones sexy prom dress Formal Prom Dresses cheap prom dresses Plus Size Prom Dresses tiffany prom dresses erotic games strip poker pokemon trading card game rom hoyle card games teen bra Bra Teen Cleavage Micro Bikini Teens Bra sexy bras bulma bra sheer bra auto loan calculator Federal Student Loan Consolidation private student loan consolidation acs student loans countrywide home loans refinance home loan st louis wacoal bras teen bra unsecured signature loan Countrywide Home Loans Formal Prom Dresses Sexy Prom Dress cocktail dresses TMobile water softener tankless water heater oscar dresses mother of the bride dresses bridesmaid dresses cocktail dresses formal dresses easter dresses evening dresses evening gowns ball gowns formal gowns plus size wedding gowns rockport shoes reverse osmosis water filter merrell shoes casino royale throat pokers free strip poker crazy game of poker poker chips texas holdem poker game online poker aide online poker assistant casino directory gambling online online pai gow poker hooters casino atlantic city casinos buy ativan online buy oxycontin online buy alprazolam online buy alprazolam cialis no prescription xanax xr ultram er buy ultram online generic fioricet buy fioricet online phentermine 37 5mg bontril sr cheap bontril ambien cr buy didrex online adipex without a prescription buy tamiflu buy clonazepam purchase tramadol without a prescription buy acyclovir buy codeine private poker tournament freeroll poker tournament caribbean stud poker paradise poker play free strip poker erotic games strip poker online poker calculator online poker tracker buy diazepam didrex buy didrex adipex without prescription adipex p clonazepam buy clonazepam diethylpropion celexa paxil cr lorazepam hydrocodone apap lortab klonopin norco buy propecia order propecia phendimetrazine Butalbital cheap xenical wellbutrin xl valtrex vicodin without prescription las vegas casino coupons las vegas palms casino hooters casino las vegas microgaming casino bonus playtech casino bonus rtg casino bonus grand casino tunica grand casino mille lacs grand casino coushatta grand casino hinckley grand casino hinkley