General ODBC Error – Why

344995 pts.
Tags:
ODBC
ODBC Errors
SQL
I get an gernal ODBC error when running the below code. It looks like the sqlt is too long, but how can I get it to work as I need the query to be dynamic based on date. sqlt = "SELECT SCHED_TASK_EXEC.TASK_ID, SCHED_TASK_EXEC.TASK_TYPE, SCHED_TASK_EXEC.EXEC_TIME, SCHED_TASK_EXEC.END_TIME, SCHED_TASK_EXEC.EXEC_STATUS_B, SCHED_TASK.DESCRIPTION FROM CPSLIVE.SCHED_TASK SCHED_TASK, CPSLIVE.SCHED_TASK_EXEC SCHED_TASK_EXEC WHERE SCHED_TASK.TASK_ID = SCHED_TASK_EXEC.TASK_ID and sched_task_exec.exec_time >=to_timestamp('" & yday & "') AND sched_task_exec.exec_time <=to_timestamp('" & tday & "') order by 1" MsgBox (sqlt) With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DRIVER={Oracle in OraClient10g_home2};SERVER=CPSLIVEB;UID=cpslive;;DBQ=CPSLIVEB;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10" _ ), Array( _ ";FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;" _ )), Destination:=Range("A1"), Sql:=sqlt) .Name = "Query from CPSLIVEB" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    While this is not an direct answer to the question, I will point out that I recently encountered a general (or generic) ODBC error from MS Access trying to query a DB2 for z/OS table. It turned out to be that MS Access could not handle a table with a data type of ROWID. I mention this only to point out that ODBC errors are not always what they appear to be. Steve
    5,220 pointsBadges:
    report
  • carlosdl
    Are you able to successfully execute other SQL commands ? I don't think the command is too long, but if it was, you could use shorter table aliases and use those aliases to qualify columns, to shorten it a little. Also, have you tried the same command, but using fixed values instead of the tday and yday variables ?
    69,160 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following