April 15, 2004 at 1:45 pm
Below mentioned is the piece of code that i am trying to execute in DTS ActiveX script task.
I have a view which returns one column with Binary datatype of length 8. Based on that value, i want to execute another SQL statement in DTS ActiveX script.
The problem is, i am not able to append the binary value in the second SQL statement itself.
It gives me Datatype mismatch error. I tried conversion functions also. but, no use.
could anyone suggest some solution?
Code:
set conn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")
conn.Open = "Provider='SQLOLEDB';Data Source='SERVER1_NAME';Initial Catalog='DATABASE_NAME';Integrated Security='SSPI';"
strSQL1 = "Select col1 from vw_ABC"
rs.Open strSQL1, conn, adOpenDynamic
Do While Not rs.EOF
set rs1 = CreateObject("ADODB.Recordset")
strSQL2 = "select * from table1 where id in (select id from table2 where binary_key = " & rs.Fields("col1").value & ")"
'strSQL2 = "select * from table1 where id in (select id from table2 where binary_key = 0x0000000000002FAC)"
'this is the how the sql text should look like after appending the binary value. This one works for me in Query Analyzer
rs1.Open strSQL2, conn, adOpenDynamic
.
.
.
Loop
April 16, 2004 at 9:41 am
I'm not an expert, however, did you try to put an extra quote in you where statement?
strSQL2 = "select * from table1 where id in (select id from table2 where binary_key = '" & rs.Fields("col1").value & "')"
JFB
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply