How to direct the output of 'select' query to a flat/data file

[tmurugan]

Hi,

I need the data of Sybase database to SQL server database. I write a Sybase stored procedure, which will retrieve the data. I need the data to be put in a flat/data/text file, so that I can use that data for SQL server database.

I am not able to find the way to direct 'select' results to a file in a sybase stored procedure. Please throw ur suggestions.

regards,
murugan

 

[JeanNiBee]

Hi

I'll assume you HAVE to use that stored procedure to get your data. If so, store that data into a table and then use the command line BCP utility.

If, you don't really need the stored procedure, you can just create a view that get's your data and then use the command line bcp tool to 'bcp out' the view.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[tmurugan]

Thanks Jean..

I am new to Sybase. First, I thought abt BCP utility. I felt that it may not be convenient to my requirement. Here, I give the full requirement, please suggest me what I have to do?

In sybase one table is there. To that table, daily some 1000s of records are being inserted. For analysis purpose, daily the particular day records alone have to be brought to the MS SQL server database. This process has to triggered by the Power Builder application. This is the requirement.

So, I thought of keeping one single sybase procedure, which will create one temporary table and the particular day records will be inserted into that table. Then that table record will be transfered to a data file by that procedure itself. I don't know how much it is possible. Please guide me, how it can be done. If u feel any better way of doing this, please suggest me..

thanks,
murugan

 

[JeanNiBee]

Hi

If you are trying to get the information from a table (or tables) INTO A file then BCP Is the only way to go, that I know of. (If you are looking at a Sybase specific way)

Otherwise you will be stuck doing a SELECT in Powerbuilder and then writing the file yourself.

Sybase does have a product called Sybase_XFS that is a license for ASE that allows you to have more complete access to your file system for queries. If you REALLY need this functionality this can be another option for you.

Hope this helps.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[tmurugan]

Thanks again Jean....

Ok.. BCP utility will help for this. And I don't want use PowerBuilder to write into file. As I know, BCP utility can be used in command prompt. Can you suggest me, how to use BCP utility in a sybase procedure? I will be so thankful, if you give sample code.

Thanks,
Murugan

 

[JeanNiBee]

Okay here's the syntax and an 'example'.

First off ou can't call 'bcp' in a procedure, you call it from some application.

Here's what I would do.

CREATE TABLE UserList (
    firstName varchar(30),
    lastName  varchar(30),
    active char(1) DEFAULT "Y" NULL
)
go

INSERT INTO UserList ("Jean", "Nibee", "Y");
INSERT INTO UserList ("Joe", "Blow", "Y");
INSERT INTO UserList ("Hulk", "Hogan", "N");

go

Now let's say I want to get a daily list of all active users.

I can do this.

CREATE VIEW ActiveUsers AS
SELECT * FROM UserList WHERE active = "Y"

Then I can 'bcp' the active list out by running bcp on the command line and storing the information in a file called "active_users.dat"

bcp DBNAME.dbo.UserList out ./active_users.dat -eerrors.out -c -t\| -r\\n -UsomeUser -PusersPassword -SLOGICAL_SERVER_NAME

When you run this, you will have your file that will contain the following:

Jean|Nibee|Y
Joe|Blow|Y

If, you need more functionality or flexibility when making the view (like you need dynamic ranges). Just have your stored procedure store your resultset into a any table like temp_resultsFromStoredProc and then BCP this table out from the DB.

bcp DBNAME.dbo.temp_resultsFromStoredProc out ./active_users.dat -eerrors.out -c -t\| -r\\n -UsomeUser -PusersPassword -SLOGICAL_SERVER_NAME

As a final explanation here's what each parameter of bcp means. (You can get a better explanation in the Utility Guide for ASE on the Sybase site at

[URL unfurl="true"]http://sybooks.sybase.com/asg1250e.html[/URL]

DBNAME.dbo.table/view name

= The name of the database you are connecting to, the ower of the object/table/view and the name of the view or table. You use the "DBNAME" and "dbo" if your user does not have the proper default database option set.

out

= Export the data (in means import the data)

./active_users.dat

= The file to export the information.

-eerrors.out

= If any errors, put them in this file.

-c

= Store data as character format.

-t\|

= Each field should be 'terminated' by what character/series of characters. This could be "," (for comma delimited) or even a 'custom' string like "~@~". This means that your file would look something like:

Jean~@~NiBee~@~Y
Joe~@~Blow~@~Y

-r\\n

= Each 'row' should be terminated by a 'newline' (in windows OS it would be \\r\\n for linefeed AND carriage return.

-UsomeUser -PusersPassword

= the username and password to connect as.

SLOGICAL_SERVER_NAME

= The name of the server to connect to. (As defined in the interfaces file).

Hopwe this helps.

In addition, check out the "Sybase: Adaptive (SQL) Server" forum on Tek Tips, it's got much more 'how to' information than in forum.




-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

Sorry but I found a TYPO in my previous post.

Right after I show how to create a view I mention the following:
Then I can 'bcp' the active list out by running bcp on the command line and storing the information in a file called "active_users.dat"

bcp DBNAME.dbo.UserList out ./active_users.dat -eerrors.out -c -t\| -r\\n -UsomeUser -PusersPassword -SLOGICAL_SERVER_NAME


The BCP command should ACTUALLY be written as...

bcp DBNAME.dbo.ActiveUsers out ./active_users.dat -eerrors.out -c -t\| -r\\n -UsomeUser -PusersPassword -SLOGICAL_SERVER_NAME


We are BCP'ing the VIEW (ActiveUsers) info out of the db, not the db (UserList) directly.

Sorry about that.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[tmurugan]

Thanks a lot jean..

Because of complex in this approach, I have changed my approache to use DTS utility of MS SQL to get the data from Sybase to MS SQL server.. DTS package method works fine and it's simple.. Thanks again for you..

Murugan

 

[ppg111]

Anybody knows how to convert oracle table to flat file as it does with bcp in sybase?.....

 

[sybaseguru]

Try Oracle export (exp) utility