Error: -2147217900: Syntax error or access violation

  • Thread starter Thread starter Jordan Ferrier
  • Start date Start date
J

Jordan Ferrier

I'm getting the above error executing an ADODB command
from an Access 2000 (SP3) ADP connected to SQLServer 2000
Standard Edition (SP3). This is something of a data
warehousing application, so I'm posting it in the SQL
groups as well.

What's occuring is:
I have a stored procedure that inserts data into another
database on the same server. When I run the SP alone
(query analyzer or double click on the SP in Access SP
screen) it executes great. All data moves right over to
the other database. When I try to execute it through a
form using ADO and VBA it throws the above error. I have
the same code working well for another very similar SP (I
dynamically determine which SP is used and they all have
the same parameter structure), but it doesn't go for this
one... It's simple SQLServer-to-SQLServer... argh!!! The
tables are almost carbon copies!!!

Below is the pre-execute state and properties of the ado
command object:
State: 0
Properties: IAccessor: True
Properties: IChapteredRowset: True
Properties: IColumnsInfo: True
Properties: IColumnsRowset: True
Properties: IConnectionPointContainer: True
Properties: IConvertType: True
Properties: ILockBytes: False
Properties: IRowset: True
Properties: IDBAsynchStatus: False
Properties: IParentRowset: True
Properties: IRowsetChange: False
Properties: IRowsetExactScroll: True
Properties: IRowsetFind: True
Properties: IRowsetIdentity: True
Properties: IRowsetInfo: True
Properties: IRowsetLocate: True
Properties: IRowsetRefresh: True
Properties: IRowsetResynch: False
Properties: IRowsetScroll: True
Properties: IRowsetUpdate: False
Properties: IRowsetView: True
Properties: IRowsetIndex: False
Properties: ISequentialStream: True
Properties: IStorage: False
Properties: IStream: False
Properties: ISupportErrorInfo: True
Properties: Preserve on Abort: True
Properties: Access Order: 2
Properties: Append-Only Rowset: False
Properties: Blocking Storage Objects: False
Properties: Use Bookmarks: True
Properties: Skip Deleted Bookmarks: True
Properties: Bookmark Type: 1
Properties: Cache Deferred Columns: False
Properties: Fetch Backwards: True
Properties: Hold Rows: True
Properties: Scroll Backwards: True
Properties: Change Inserted Rows: True
Properties: Column Privileges: False
Properties: Command Time Out: 30
Properties: Preserve on Commit: True
Properties: Defer Column: False
Properties: Delay Storage Object Updates: True
Properties: Private1:
Properties: Filter Operations: 27
Properties: Find Operations: 27
Properties: Hidden Columns: 0
Properties: Immobile Rows: True
Properties: Literal Bookmarks: True
Properties: Literal Row Identity: True
Properties: Maximum Open Rows: 0
Properties: Maximum Pending Rows: 0
Properties: Maximum Rows: 0
Properties: Column Writable: False
Properties: Memory Usage: 0
Properties: Notification Granularity: 1
Properties: Notification Phases: 31
Properties: Column Set Notification: 3
Properties: Row Delete Notification: 3
Properties: Row First Change Notification: 3
Properties: Row Insert Notification: 3
Properties: Row Resynchronization Notification: 3
Properties: Rowset Release Notification: 3
Properties: Rowset Fetch Position Change Notification: 3
Properties: Row Undo Change Notification: 3
Properties: Row Undo Delete Notification: 3
Properties: Row Undo Insert Notification: 3
Properties: Row Update Notification: 3
Properties: Bookmarks Ordered: True
Properties: Others' Inserts Visible: False
Properties: Others' Changes Visible: False
Properties: Own Inserts Visible: True
Properties: Own Changes Visible: True
Properties: Quick Restart: True
Properties: Reentrant Events: True
Properties: Remove Deleted Rows: True
Properties: Report Multiple Changes: False
Properties: Return Pending Inserts: True
Properties: Row Privileges: False
Properties: Asynchronous Rowset Processing: 0
Properties: Row Threading Model: 1
Properties: Server Cursor: False
Properties: Strong Row Identity: True
Properties: Objects Transacted: False
Properties: Unique Rows: False
Properties: Updatability: 7
Properties: Batch Size: 15
Properties: Update Criteria: 2
Properties: Background Fetch Size: 15
Properties: Initial Fetch Size: 50
Properties: Background thread Priority: 3
Properties: Cache Child Rows: True
Properties: Maintain Change Status: False
Properties: Auto Recalc: 1
Properties: Unique Table:
Properties: Unique Schema:
Properties: Unique Catalog:
Properties: Resync Command:
Properties: Cursor Engine Version: 27
Properties: Reshape Name:
Properties: Update Resync: 1
Properties: Bookmarkable: True

Any help would be greatly appreciated!

Jordan Ferrier
 
OK, I figured it out. The stored procedure, which I'd
generated from CreateProcedure code had a dash in the
procedure name (because one of the legacy systems had a
table name with a dash in it). This somehow didn't
process right when I called it from VBA/ADO... oh well.

At least now I know better.
 
Back
Top