how to use parameters between sqlserver & oracle

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

how to use parameters between sqlserver & oracle

Author

Topic

css_jay99
Starting Member

9 Posts

Posted - 2006-04-07 : 10:04:27

Hi all

I am writing a dts to retrive data from oracle based on a query

so doing something like
SELECT x, y, z
FROM tables
WHERE j= 3

is fine

BUT I want to do something like
SELECT x, y, z
FROM tables
WHERE j in (a,b,c,d...)
However the values (a,b,c,d...) are to be comming from sql server rather than oracle

How do i do this ?

Thanks

css_jay99

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-07 : 10:16:30

Construct the query in an activex script as a string then update the query string in the task you ae using for the extract.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

css_jay99
Starting Member

9 Posts

Posted - 2006-04-07 : 12:18:48

Thanks,

is it possible to write an example based on my scenario above.
Since the values in the where clause are not known until runtime, i cant see how it will work.

Thanks

css_jay99

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-08 : 17:47:09

Have a look at
http://www.nigelrivett.net/DTS/SetDTSRunTimeValues.html

You can do the same for a suery string in the task.
I usually find it it easier to develop scripts in VB then copy to an activez task.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

css_jay99
Starting Member

9 Posts

Posted - 2006-04-11 : 07:08:49

Hi, thanks for the help.

I was able to solve it by :-

(1) creating a task that uses tsql to select and build my list like ('a','b','c'....)
which is then passed out as a global variable
i.e
DECLARE @var1 varchar(8000)
SELECT @var1 = ''
SELECT @var1 = @var1 + DISTINCT ''''+ customerID + ''''
FROM Customers
SELECT @var1 = "("+ substring(@var1 ,1, len(@var1 )-1) + ")"
SELECT @var1

(2) then wrote an activex task to attach the select part of the query to the above. and
then dynamically replace the query in the task
i.e
****************************************************************
Function Main()

Dim selectString1, selectString2, selectString3, completeString

selectString1 = "SELECT a, b, c, ....... FROM w,x,y,z WHERE cusID IN"
selectString2 = DTSGlobalVariables("gloCustomerList").Value
selectString3 =" AND w.s_id = x.s_id ........AND y.k_id= z.k_id "

completeString = selectString1 + selectString2 + selectString3

Set oPkg = DTSGlobalVariables.Parent

Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

oDataPump.SourceSQLStatement = completeString

' Cleanup
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function
*******************************************************************

(3) Then the transformation connects to Oracle to retrive the data

The easiest way of doing this would have been to
(1) Create a tranformation task to use the initial query to retrieve id to connect to oracle and build a temporary table
which exists only for the session
(2) and then using the same connection, to create another transformation task that connects to oracle referencing my temporary table. QED

BUT unfortunately we are only allowed query access alone, so Best & easiest solution is out of the window !

RECAP

This initial solution works fine but i find it hard to believe as good as DTS is, that there is no 'cleaner' way of doing this.
Reason I say this is that my complete query which is assinged to a Dim variable 'completeString' is about 9000 characters in length !

Now that is huge considering that the IN parameter list of the query could grow in time.

My new quetions are :
(1) Is there no better way than this?. what is the max a dim variable/DTS global string Variable can Hold ?
(2) I noticed that i had to put all the query in a single line for it to work because activex complained otherwise ....unterminated string .. any work around for this?
(3) is is possible to build the list into a file and then load it for the query

Please any suggestion with sample code would be very appreciated

Thanks !

css_jay99

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources