DTS problem with ODBC Driver - SQL Server Forums

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

SQL Server Development (2000)

DTS problem with ODBC Driver

Author

Topic

san
Starting Member

26 Posts

Posted - 2002-09-04 : 11:16:34

I could succesfully connect to a Sybase Database from a DTS package
using sybase ASE ODBC Driver and export the data to a SQL server. Moreover, I could execute a stored procedure to filter the source data that has to be exported.

But I have to pass a parameter to this source sybase stored procedure. It
is not working. When I say 'Preview' in my source SQL statemet, it is
giving the error 'Null acessors are not supported by this provider'.

Folks, any idea how to get around with this problem ? I am using
Sybase ASE ODBC Driver version 3.7. What is the latest version for Sybase ASE ODBC provider ? Where can I get one ?

FYI - I could execute the stored procedure with parameter from a VB
program using the same ODBC Driver.

rharmon
Starting Member

41 Posts

Posted - 2002-09-04 : 14:01:51

I've had the same problem with Sybase and DTS. What I ended up doing is putting the parameter value in a global variable using a dynamic properties task. Then I created the source sql query for the data pump using a vbscript task and put it in another global variable like:

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

' Visual Basic ActiveX Script" & vbCrLf
'************************************************************************
Function Main()
dim sqlstring
sqlstring = "SELECT * " & vbcrlf
sqlstring = sqlstring & " FROM dbo.SO_HEADER " & vbCrLf

sqlstring = sqlstring & " WHERE (dbo.SO_HEADER.Order_Date >= ' "
sqlstring = sqlstring & DTSGlobalVariables("load_date").Value
sqlstring = sqlstring & "') "

DTSGlobalVariables("sqlstring").Value = sqlstring
Main = DTSTaskExecResult_Success
end function

Once that step completes, I set the source query for the pump to the value of the second global variable using another dynamic properties task.

Not the prettiest solution, but it does work.

ROb

san
Starting Member

26 Posts

Posted - 2002-09-10 : 12:59:48

rharmon! Thanks a lot buddy. You saved me !!!

rharmon
Starting Member

41 Posts

Posted - 2002-09-10 : 15:38:17

Did you get the email I sent you? It seemed to bounce around a bit.

san
Starting Member

26 Posts

Posted - 2002-09-10 : 16:56:27

Rharmon - Thanks for your effort. Since all junks come to my mail account it wud have bounced.
Anyway, the work is done. Thanks again.

rharmon
Starting Member

41 Posts

Posted - 2002-09-10 : 22:00:52

I asked as I think I have a little more eloquent solution. I'll post it in the morning as the code is at work.

Glad it worked for you and good on ya for figerin it out!

ROb

san
Starting Member

26 Posts

Posted - 2002-09-17 : 13:33:54

Rharmon,
I was waiting for your new post.
The fine-tuned code is ready or u got in to some unexpected problems ?
Please update me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources