Difference between revisions of "Bulk copy out of text and image data"

From SybaseWiki
Jump to: navigation, search
 
(5 intermediate revisions by 5 users not shown)
Line 20: Line 20:
 
   
 
   
 
  set nocount on
 
  set nocount on
 +
set flushmessage on
 
   
 
   
 
  declare myCursor cursor for
 
  declare myCursor cursor for
 
   select user_name(o.uid),o.name,c.name
 
   select user_name(o.uid),o.name,c.name
 
   from sysobjects o,syscolumns c
 
   from sysobjects o,syscolumns c
   where o.type = "U" and o.id = c.id and c.type in (34,35)
+
   where o.type = "U" and o.id = c.id and c.type in (34,35,174)
 
   order by 1,2,3
 
   order by 1,2,3
 
   
 
   
  declare @user varchar(30),@table varchar(30),@column varchar(30),@cmd varchar(512),@first tinyint,@db varchar(30)
+
  declare @user varchar(30),@table varchar(255),@column varchar(255),@cmd varchar(1024),@first tinyint,@db varchar(30)
 
   
 
   
 
  select @first = 0,@db = db_name()
 
  select @first = 0,@db = db_name()
Line 38: Line 39:
 
  open myCursor
 
  open myCursor
 
  if @@error != 0 return  1
 
  if @@error != 0 return  1
 +
 +
create table #output
 +
(user_name      varchar(30)    not null,
 +
table_name      varchar(255)    not null,
 +
column_name    varchar(255)    not null,
 +
max_size        int            not null)
 
   
 
   
 
  fetch myCursor into @user, @table, @column
 
  fetch myCursor into @user, @table, @column
Line 43: Line 50:
 
  while @@sqlstatus = 0
 
  while @@sqlstatus = 0
 
  begin
 
  begin
   if @first  = 0
+
   select @first = 1
  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) + "."
+
   print "Calculating %1!.%2!.%3!",
    + rtrim(@table) + " print '%1!.%2!.%3! - %4!','" + @user + "','" + @table + "','" + @column + "',@l"
+
        @user,@table,@column
 
   
 
   
 +
  select @cmd  = "insert into #output select '" + @user + "','" + @table + "','"
 +
                + @column + "',isnull(max(datalength(" + rtrim(@column)
 +
                + ")),0) from " + rtrim(@user) + "." + rtrim(@table)
 
   exec (@cmd)
 
   exec (@cmd)
 
   
 
   
Line 63: Line 68:
 
  deallocate cursor myCursor
 
  deallocate cursor myCursor
 
   
 
   
  if @first = 0 print "No text/image data found in this database."
+
  if @first = 0
 +
begin
 +
  print "No text/image data found in this database."
 +
end
 +
else
 +
begin
 +
  set nocount off
 +
  select convert(varchar(70),rtrim(user_name) + "." + rtrim(table_name) + "." + rtrim(column_name)),max_size
 +
    from #output
 +
    where max_size > 0
 +
    order by max_size
 +
  if @@rowcount = 0
 +
  begin
 +
    print "Maximum size is 0 bytes"
 +
  end
 +
end
 +
 +
drop table #output
 
   
 
   
 
  return  0
 
  return  0
Line 69: Line 91:
  
 
[[Category:ASE]]
 
[[Category:ASE]]
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
<div  style="display:none">
 
[We are delicate. We do not delete your content.]
 
[l_sp2008]
 
 
[http://ringtonemaker.blogs.eurosport.com/ ringtone maker]
 
[http://verizonringtone.forumco.com/ verizon ringtone]
 
[http://uscellularringtone.forumco.com US Cellular Ringtone]
 
[http://blog.investing.com/bcbgshoes/ bcbg shoes]
 
[http://www.buddyprofile.com/viewprofile.php?username=waterfordcrystal waterford crystal]
 
[http://www.buddyprofile.com/viewprofile.php?username=swarovskicrystal swarovski crystal bead]
 
[http://www.buddyprofile.com/viewprofile.php?username=mesotheliomalawsuits mesothelioma lawsuits]
 
[http://www.buddyprofile.com/viewprofile.php?username=mesotheliomasymptoms mesothelioma symptoms]
 
[http://www.buddyprofile.com/viewprofile.php?username=mesotheliomadiag mesothelioma diagnosis]
 
[http://www.totalvideogames.com/blog/naturalizershoes/ Naturalizer Shoes]
 
[http://www.totalvideogames.com/blog/freekyocerarington/ Free Kyocera Ringtone]
 
[http://www.missoula.com/blog/sexypromdresses/ Sexy Prom Dresses]
 
[http://www.justachat.com/blog/?w=naturalizershoes Naturalizer Shoes]
 
[http://www.toutelapoesie.com/blog/aerobed/ Aero Bed]
 
[http://www.totalvideogames.com/blog/freesprintringtones/ Free Sprint Ringtones]
 
[http://www.totalvideogames.com/blog/freeverizonringtones/ Free Verizon Ringtones]
 
[http://www.totalvideogames.com/blog/freenextelringtones/ free nextel ringtones]
 
[http://www.totalvideogames.com/blog/sexypromdresses/ sexy prom dress]
 
[http://www.totalvideogames.com/blog/formalpromdresses/ Formal Prom Dresses]
 
[http://www.totalvideogames.com/blog/cheappromdresses/ cheap prom dresses]
 
[http://www.totalvideogames.com/blog/plussizepromdress/ Plus Size Prom Dresses]
 
[http://www.totalvideogames.com/blog/tiffanypromdresses/ tiffany prom dresses]
 
[http://www.totalvideogames.com/blog/strippoker/ erotic games strip poker]
 
[http://www.totalvideogames.com/blog/pokemoncardgame/ pokemon trading card game rom]
 
[http://www.totalvideogames.com/blog/hoylecardgames/ hoyle card games]
 
[http://topsites.blog.expedia.fr/files/buy-ativan-online.2.htm buy ativan online]
 
[http://topsites.blog.expedia.fr/files/buy-ativan.htm buy ativan]
 
[http://topsites.blog.expedia.fr/files/buy-alprazolam-online.htm buy alprazolam online]
 
[http://topsites.blog.expedia.fr/files/buy-alprazolam.htm buy alprazolam]
 
[http://topsites.blog.expedia.fr/files/cheap-tramadol.htm cheap tramadol]
 
[http://topsites.blog.expedia.fr/files/buy-bontril-online.htm buy bontril online]
 
[http://topsites.blog.expedia.fr/files/fentermine.htm fentermine]
 
[http://topsites.blog.expedia.fr/files/how-to-buy-fioricet-on-line.htm how to buy fioricet on line]
 
[http://topsites.blog.expedia.fr/files/cheap-ultram-without-prescription.htm cheap ultram without prescription]
 
[http://topsites.blog.expedia.fr/files/buy-ultram-without-prescription.htm buy ultram without prescription]
 
[http://www.totalvideogames.com/blog/teenbra/ teen bra]
 
[http://www.totalvideogames.com/blog/brateencleavage/ Bra Teen Cleavage]
 
[http://www.totalvideogames.com/blog/microbiniki/ Micro Bikini]
 
[http://www.totalvideogames.com/blog/teensbra/ Teens Bra]
 
[http://www.totalvideogames.com/blog/sexybras/ sexy bras]
 
[http://www.totalvideogames.com/blog/bulmabra/ bulma bra]
 
[http://www.totalvideogames.com/blog/sheerbra/ sheer bra]
 
[http://www.totalvideogames.com/blog/autoloancalculator/ auto loan calculator]
 
[http://www.totalvideogames.com/blog/loanconsolidation/ Federal Student Loan Consolidation]
 
[http://www.totalvideogames.com/blog/privatestudentloan/ private student loan consolidation]
 
[http://www.totalvideogames.com/blog/acsstudentloans/ acs student loans]
 
[http://www.totalvideogames.com/blog/countrywidehomeloans/ countrywide home loans]
 
[http://www.totalvideogames.com/blog/refinancehomeloan/ refinance home loan st louis]
 
[http://www.buddyprofile.com/viewprofile.php?username=wacoalbras wacoal bras]
 
[http://www.buddyprofile.com/viewprofile.php?username=teenbra teen bra]
 
[http://www.buddyprofile.com/viewprofile.php?username=unsecuredloan unsecured signature loan]
 
[http://www.buddyprofile.com/viewprofile.php?username=homeloans Countrywide Home Loans]
 
 
</div>
 

Latest revision as of 19:24, 29 January 2012

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
set flushmessage 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,174)
  order by 1,2,3

declare @user varchar(30),@table varchar(255),@column varchar(255),@cmd varchar(1024),@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

create table #output
(user_name      varchar(30)     not null,
table_name      varchar(255)    not null,
column_name     varchar(255)    not null,
max_size        int             not null)

fetch myCursor into @user, @table, @column

while @@sqlstatus = 0
begin
  select @first = 1

  print "Calculating %1!.%2!.%3!",
        @user,@table,@column

  select @cmd   = "insert into #output select '" + @user + "','" + @table + "','"
                + @column + "',isnull(max(datalength(" + rtrim(@column)
                + ")),0) from " + rtrim(@user) + "." + rtrim(@table)
  exec (@cmd)

  if  @@error != 0 break

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

close myCursor
deallocate cursor myCursor

if @first = 0
begin
  print "No text/image data found in this database."
end
else
begin
  set nocount off
  select convert(varchar(70),rtrim(user_name) + "." + rtrim(table_name) + "." + rtrim(column_name)),max_size
    from #output
    where max_size > 0
    order by max_size
  if @@rowcount = 0
  begin
    print "Maximum size is 0 bytes"
  end
end

drop table #output

return  0
go