Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Run Time Error 3146, Writing SQl Record

Yesterday, I added a table to my SQL database to use as an execution log.  In the meantime I have run several processes in the MS Access 2013 application using the table that successfully created records in the table.  At the time of the error, there were over 25,000 records in the table.

I am trying to write another record to the table and getting the 3146 runtime 'ODBC Call Failed' error.
User generated imageI checked the values of all passed fields and they are solid and contain expected values.

All of the records added to the table are added in this routine.  I am getting the error on statement
rsOut.AddNew
Public Sub logExecutionInfo(passedMessage As String, passedStartTime As Date, passedEndTime As Date)
'
wkDateTime = Now
wkUser = GimmeUserName
'
Dim wkDuration As String
wkDuration = getDuration(passedStartTime, passedEndTime)
'

Dim db As DAO.Database
Set db = getCurrentDbC
'
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("tblExecutionLog", dbOpenDynaset, dbSeeChanges)
'
rsOut.AddNew
'
rsOut![Operation] = passedMessage
rsOut![Duration] = wkDuration
rsOut![DateTimeAdded] = wkDateTime
rsOut![UserAdded] = wkUser
'
'
rsOut.Update
'
rsOut.Close
Set rsOut = Nothing
'
End Sub

Open in new window


I created the table in MS Access then used the SSMA For Access to migrate the table to SQL Server.  I have done this successfully with dozens of other tables.  I noticed when this table was migrated a Time Stamp wasn't added by SSMA.
Here is a print of the SQL Table definition
USE [JTSConversion]
GO

/****** Object:  Table [dbo].[tblExecutionLog]    Script Date: 1/22/2016 2:20:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblExecutionLog](
	[ExecID] [int] IDENTITY(1,1) NOT NULL,
	[Operation] [nvarchar](255) NULL,
	[Duration] [nvarchar](255) NULL,
	[DateTimeAdded] [datetime] NULL,
	[UserAdded] [varchar](50) NULL,
 CONSTRAINT [tblExecutionLog$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[ExecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[ExecID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'COLUMN',@level2name=N'ExecID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[Operation]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'COLUMN',@level2name=N'Operation'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[Duration]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'COLUMN',@level2name=N'Duration'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ImportTest_wSQLLinks.[tblExecutionLog].[PrimaryKey]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblExecutionLog', @level2type=N'CONSTRAINT',@level2name=N'tblExecutionLog$PrimaryKey'
GO

Open in new window



This is a single user machine.

Any idea what is causing the error or how to correct the issue?
Avatar of COACHMAN99
COACHMAN99

have you tried adding a test record manually via the linked table?
and have you tried adding the new data directly?
Avatar of mlcktmguy

ASKER

<have you tried adding a test record manually via the linked table?
 and have you tried adding the new data directly? >

Yes both were successful
ASKER CERTIFIED SOLUTION
Avatar of COACHMAN99
COACHMAN99

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the responses.  I am fairly new to SQL Server.  

Vitor Montalvão <<By the way, why are you using a dbOpenDynaset type and not dbOpenTable type? ">>
I am also fairly new to DAO as I've always used ADO in the past.  Why would I use dbOpenTable vs dbOpenDynaset?  What are other options that might be applicable in other situations?

I'm trying to run the 'sp_configure 'user options'' but obviously doing it wrong.

I opened Sql Server Management Studio and created a new query that looks like this

use JTSConversion

server.sp_configure "user options"

GO

RECONFIGURE

GO

Set nocount on

GO

Open in new window


But I am getting a compile error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'server'.

Obviously I'm doing something very wrong but not sure what it is.  Can anyone help?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, the values returned when I run sp_configure

Name                   User Options
Minimum             0
Maximum            32767
Config_Value        0
run_Value             0
I was researching 'dbOpenTable' and saw a comment that 'dbOpenTable' only applied to Jet tables.  Is that true?
These are SQL tables.
What would be the advantage of 'dbOpenTable' vs 'dbOpenDynaset' on a SQL table?  dbOpenDynaset seems to be working fine on other SQL tables I am reading or writing.