January 22, 2007 at 6:06 am
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
I am trying to read a sysbase table and insert all values for that table into a sql server
table. The name of the sysbase table will change every day with the date appended
to the end of the table name.
This is the code I am using... I am getting this error when executing??
Error Source : ADODB.Recordset
Error Description: Error Code : 0
Error Source=ADODB.Recordset
Error Description: Item cannot be found in collection corresponding to the requested name or ordinal.
Error on Line 53
Item cannot be found in collection corresponding to the requested name or ordina.
FYI Line 53 is this line " strINSERT = "INSERT INTO eCallByCallStat " & _" after the for statement.
Thanks, Jeff C
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim dtPreviousDate
Dim strFileDate
Dim strTableName
Dim strSQLTableName
Dim Result
Dim cnSybase
Dim cnSymposium
Dim rsSybaseSYM
Dim rsSQLSYM
Dim rsCallByCallStat
Dim rsCallByCallStatSQL
Dim strConn1
Dim strConn2
Dim strSQL
Dim strINSERT
dtPreviousDate = DateAdd("d", -1 , Date() )
strFileDate = Year(dtPreviousDate) & Right("0" & Month(dtPreviousDate), 2) & Right("0" & Day(dtPreviousDate), 2)
strTableName = "eCallByCallStat" & strFileDate
strSQLTableName = "eCallByCallStat"
strSQL = "SELECT * FROM blue.dbo."& strTableName
Set cnSybase = CreateObject("ADODB.Connection")
Set rsSybaseSYM = CreateObject("ADODB.Recordset")
Set cnNortelSymposium = CreateObject("ADODB.Connection")
Set rsNortelSymposium = CreateObject("ADODB.Recordset")
Const adOpenKeyset = 1
cnSybase.Open "Provider=MSDASQL.1;Password=xxxxxxx;Persist Security Info=True;User ID=xxxxx;Data Source=symposium;Initial Catalog=blue"
cnNortelSymposium.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NortelSymposium;Data Source=VELA"
' Create and open first Recorfdset using Connection - execute
rsSybaseSYM.Open strSQL, cnSybase, adOpenKeyset
' msgbox(rsSybaseSYM.RecordCount)
If rsSybaseSYM.RecordCount > 0 Then
For countr = 1 To rsSybaseSYM.RecordCount
strINSERT = "INSERT INTO eCallByCallStat " & _
"(Timestamp,CallEvent,CallEventName,CallId,TelsetLoginID,AssociateData,Destination,VentData,Source,Time,SiteID,Site) " & _
"VALUES ( " & _
" ' " & rsSybaseSYM.Fields("Timestamp").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("CallEvent").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("CallEventName").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("CallID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("TelsetLoginID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("AssociatedData").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Destination").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("VentData").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Source").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Time").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("SiteID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Site").Value & " ' " & _
")"
cnNortelSymposium.Execute strINSERT, , adCmdText
rsSybaseSYM.MoveNext
Next
End If
cnNortelSymposium.Close
cnSybase.Close
End Function
jcollins
January 22, 2007 at 7:32 am
try changing the line....
strSQL = "SELECT * FROM blue.dbo."& strTableName
to
strSQL = "SELECT [Timestamp], [CallEvent] .... ,[Site] FROM blue.dbo."& strTableName
January 23, 2007 at 6:00 am
If Mike's solution doesn't work, try (where I put square brackets around the T-SQL key word Timestamp):
strINSERT = "INSERT INTO eCallByCallStat " & _
"(Timestamp,CallEvent,CallEventName,CallId,TelsetLoginID,AssociateData,Destination,VentData,Source,Time,SiteID,Site) " & _
"VALUES ( " & _
" ' " & rsSybaseSYM.Fields("[Timestamp]").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("CallEvent").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("CallEventName").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("CallID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("TelsetLoginID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("AssociatedData").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Destination").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("VentData").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Source").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Time").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("SiteID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Site").Value & " ' " & _
")"
Russel Loski, MCSE Business Intelligence, Data Platform
January 23, 2007 at 9:09 am
Thanks Mike your suggestion worked.
FYI -This is the working Code
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim dtPreviousDate
Dim strFileDate
Dim strTableName
Dim strSQLTableName
Dim Result
Dim cnSybase
Dim cnSymposium
Dim rsSybaseSYM
Dim rsSQLSYM
Dim rsCallByCallStat
Dim rsCallByCallStatSQL
Dim strConn1
Dim strConn2
Dim strSQL
Dim strINSERT
dtPreviousDate = DateAdd("d", -1 , Date() )
strFileDate = Year(dtPreviousDate) & Right("0" & Month(dtPreviousDate), 2) & Right("0" & Day(dtPreviousDate), 2)
strTableName = "eCallByCallStat" & strFileDate
strSQLTableName = "eCallByCallStat"
strSQL = "SELECT Timestamp, CallEvent, CallEventName, CallID, TelsetLoginID, AssociatedData, Destination, EventData, Source, Time, SiteID, Site FROM blue.dbo."& strTableName
Set cnSybase = CreateObject("ADODB.Connection")
Set rsSybaseSYM = CreateObject("ADODB.Recordset")
Set cnNortelSymposium = CreateObject("ADODB.Connection")
Set rsNortelSymposium = CreateObject("ADODB.Recordset")
Const adOpenKeyset = 1
cnSybase.Open "Provider=MSDASQL.1;Password=xxxxxx;Persist Security Info=True;User ID=xxxxx;Data Source=symposium;Initial Catalog=blue"
cnNortelSymposium.Open "Provider=SQLOLEDB.1;Data Source=VELA;Initial Catalog=NortelSymposium;user id = xxxxxxx;Trusted_Connection=Yes"
' Create and open first Recorfdset using Connection - execute
rsSybaseSYM.Open strSQL, cnSybase, adOpenKeyset
' msgbox(rsSybaseSYM.RecordCount)
If rsSybaseSYM.RecordCount > 0 Then
For countr = 1 To rsSybaseSYM.RecordCount
strINSERT = "INSERT INTO eCallByCallStat " & _
"(Timestamp,CallEvent,CallEventName,CallID,TelsetLoginID,AssociatedData,Destination,EventData,Source,Time,SiteID,Site) " & _
"VALUES ( " & _
" ' " & rsSybaseSYM.Fields("Timestamp").Value & " ', " & _
" " & rsSybaseSYM.Fields("CallEvent").Value & " , " & _
" ' " & rsSybaseSYM.Fields("CallEventName").Value & " ', " & _
" " & rsSybaseSYM.Fields("CallID").Value & " , " & _
" ' " & rsSybaseSYM.Fields("TelsetLoginID").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("AssociatedData").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Destination").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("EventData").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Source").Value & " ', " & _
" ' " & rsSybaseSYM.Fields("Time").Value & " ', " & _
" " & rsSybaseSYM.Fields("SiteID").Value & " , " & _
" ' " & rsSybaseSYM.Fields("Site").Value & " ' " & _
")"
' msgbox(strINSERT)
cnNortelSymposium.Execute strINSERT
rsSybaseSYM.MoveNext
Next
End If
cnNortelSymposium.Close
cnSybase.Close
Main = DTSTaskExecResult_Success
End Function
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
jcollins
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply