Monday, January 23, 2012

BCP for Sybase ASE

As I promised to keep my blog green with cool stuff, below is my first posting for 2012.

Scenario: We had a weird problem with Sybase ASE 1503 just for one database where in if we try to dump a database, ASE will throw stack trace. This was weird as out of all the databases on the server, this was the only one having problem. I will call this database as “problem_db”.

Approach: First and the most common approach was to restart backup server which did not helped much and the stack trace was still thrown in the error log.

Secondly, we contacted Sybase and asked for help with this situation and they recommended to delete statistics and recreate them back and we ran dbcc to make sure there were no errors reported. We followed that and still the situation was same.

Until Sybase comes back to us with a proper fix, we decided to bcp out the data and bcp in the data to a new database. Lets call this new database as “healthy_db

Below are the scripts that I used for BCP out and BCP in the data. More info on bcp command can be found here.

Below parameters are quiet useful for BCPing the data into the database:

Parameter Explanation
-n performs the copy operation using native (operating system) formats. Specifying the -n parameter means bcp will not prompt for each field. Files in native data format are not human-readable.
-Y specifies that character-set conversion is disabled in the server, and is instead performed by bcp on the client side when using bcp IN.
-E bcp reads the Identity value from the data file and sends it to the server which inserts the value into the table.
-T allows you to specify, in bytes, the maximum length of text or image data that Adaptive Server sends. The default is 32K. If a text or an image field is larger than the value of -T or the default, bcp does not send the overflow.

You can find this value by doing: SELECT @@TEXTSIZE
-e This is the error log file created by bcp and it will log only the first 10 rows that caused the errors as the default for bcp is 10 errors and after that bcp will fail.

Note that ASE was running 1503 on a Solaris platform.


select 'bcp problem_db.dbo.' + name + ' out /bcp_out_files/' + name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n'
from sysobjects
where type='U'
order by name

Now for the bcp in script, there are two different scripts used for Tables having Identity columns and the ones which did not have Identity columns:


--  (this will be for tables not having identity columns)
select 'LANG=C' + char(10)+'bcp healthy_db.dbo.' + + ' in /bcp_out_files/' + + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -T 64512 -e /bcp/bcp_error.out' + char(10) +
'echo '+ +'  done ...' from sysobjects so where not in (
from sysobjects so, syscolumns sc
where =
and sc.status & 0x80 = 0x80)
and so.type = 'U'
order by

--  (this will be for tables having identity columns, –E parameter is used for bcp)
select 'LANG=C' + char(10)+'bcp healthy_db.dbo.' + + ' in /bcp_out_files/' + + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -E -T 64512 -e /bcp/bcp_error.out' + char(10) +
'echo '+ +'  done ...'
from sysobjects so, syscolumns sc
where =
and sc.status & 0x80 = 0x80
and so.type = 'U'
order by

The above scripts will generate code for bcp out and in to run using isql (command prompt) on Solaris server.

Also, the schema and other dependent objects were created before hand.



No comments:

Post a Comment

This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.