Raj_IN
asked on
Transfering data from Sybase to MS SQL
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.
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.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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','sea ttle1';'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.
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','sea
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
i'll let you know if i can i find it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks again
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(@xfieldnam
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"
<cfprocparam type="out" cfsqltype="cf_sql_varchar"
</cfstoredproc>
Regards,
jerry