Solved

Transfering data from Sybase to MS SQL

Posted on 2004-08-24
6
199 Views
Last Modified: 2013-12-24
Hi folks,

I am not sure if this is a database issue or an application issue, but my problem is this:

I have a cold fusion application that pulls out data from our Sybase database, and pushes it to a MS SQL database. The data pulled out from sybase is filtered on the date range entered by the users. Sometimes a user enters a date range that pulls out close to 2 million records from sybase, and the application dies (or odbc times out etc..) before it is able to push the data to MS SQL.

I was wondering if there is an alternate to achieve this......may be by writing a stored procedure in MS SQL server that can pull the data directly from Sybase. If it is possible, then how do I call such a procedure from cold fusion application and pass on the date range entered by a user??

Please let me know if I should post this question at MS SQL section.

Thanks in advance.
0
Comment
Question by:Raj_IN
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 11888424
Using storedproc in sybase and coldfusion. Storedproc in sybase and mssql are the same. but calling storedproc in mssqlserver then storein in sybase? er... i think you need to create remote servers.

cfstoredproc
http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags98.htm

my example using sybase storedproc

Create PROCEDURE "pGETNEWID"(@xfieldname varCHAR(30), out @xnewid varchar(30))
BEGIN
DECLARE lcID varCHAR(30);
If datalength(trim(@xfieldname)) != 0 AND @xfieldname !='*' THEN
    Update autonumbers SET valuecount = .....;
end if;

SELECT .....
FROM autonumbers
WHERE UCASE(keyname) = UCASE(@xfieldname);

set @xnewid=lcID;
END


Now in coldfusion... call it like this

<cfstoredproc procedure="pGetNEWID" datasource="#dsn#" debug="yes" returncode="yes">
       <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@xfieldname" value="INVOICENO">
       <cfprocparam type="out" cfsqltype="cf_sql_varchar" dbvarname="@xnewid" variable="lcNewID">
</cfstoredproc>      

Regards,
jerry
0
 

Author Comment

by:Raj_IN
ID: 11892856
I am not sure if I got that correctly...

Here is what I intend to to.....I would like to have a stored procedure in MS SQL that selects data from Sybase and inserts it in a table in MS SQL. I can not write a stored procedure in Sybase because it is a bought out third party product.

Create PROCEDURE "myProc"(@date_min smallDateTime, @date_max smallDateTime)
BEGIN

insert into myMSSQL_table(a, b, c, d)
select a, b, c, d
from sybase_table
where d1 > @date_min and d1 < @date_max

END

The above syntax may be incorrect, but you are getting the idea, right? Please let me know if a stored procedure like above is possible. If yes, please  forward the correct syntax.

Thanks
0
 
LVL 9

Assisted Solution

by:shooksm
shooksm earned 125 total points
ID: 11893932
Open up MSSQL Books online and lookup OPENROWSET, OPENQUERY or OPENDATASET.  All three functions allow you to connect to remote servers.  OPENROWSET allows you to specify connection information directly inisde of the function.  OPENQUERY allows you to query a linked server.  OPENDATASET allows you to query of a preconfigured data set on the server.  So really it is a matter of figuring out what your setup is and using the appropriate OPEN function to access the Sybase data store.

You could also use these OPEN commands to do a quick check of how many records it will attempt to pull over before doing the insert.  What I would do is run:

SELECT COUNT(a) FROM sybase_table where d1 > @date_min and d1 < @date_max

Establish a limit like anything over 150k rows is to large, stop processing the procedure and return an error message to the user to let them know to select a smaller date range.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 11899288
In sybase (sorry - just to demonstrate)
First, i add a remote server name - MYSERVER
Next, Craete a NEW PROXY TABLE that connects using the MYSERVER name myMSSQL_table for example

then walah!
all you have to do is

insert into myMSSQL_table(a, b, c, d)
select a, b, c, d
from sybase_table
where d1 > @date_min and d1 < @date_max

now in MSSQL, i could not find this (yet -i hope). the only solution i found was shooksm suggestion using the openquery & openrowset

sample
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

i'll let you know if i can i find it.

0
 
LVL 9

Accepted Solution

by:
Jerry_Pang earned 125 total points
ID: 11899397
in MSSql
using this query
select * from linkservername.database.username.tablename
note you have to execute sp_addlinkedserver first. (Online book Linked Servers - Clustered SQL Server)

i used linkedservers but i cant get it to work properly. Remote server in sybase is different in MsSQL. Remoteserver(msSQl) are for executing storeprocedures in different server from what i read in online books.


I created a new linkedserver - name myserver
then i queried

select top 10 * from myserver.mydatabase.sa.tablename

it says, access denied or server does not exist. im almost there but i cant get it to work.


Online books - SQLServer
Linked Servers - Clustered SQL Server
  the test i did above, no more openrowset, openquery & opendataset
Linked Servers - Executing SQL Queries  
   Using Shooks suggestion using OpenRowset, OpenQuery & OpenDataset
0
 

Author Comment

by:Raj_IN
ID: 11913434
I thank both of you for the tips/suggestions. I also apologize for my delayed reponse. Actually, my programming priorities has changed over the past couple days, and I have been asked to keep the current coldfusion, ms sql and sybase based project on suspension for the time being. But I'll definitely try out the your suggestions for my own knowledge whenever time permits...

Thanks again
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Remove Index.php in Codeigniter 12 62
IIS url rewrite rule for web application 3 56
http to https 3 60
How to fix Redundant Hostnames notification from google analytic? 5 32
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question