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.
Runtime ErrorI 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?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COACHMAN99Commented:
have you tried adding a test record manually via the linked table?
and have you tried adding the new data directly?
mlcktmguyAuthor Commented:
<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
COACHMAN99Commented:
are you using the syswow (32 bit) ODBC connection?
and 32-bit Access?

I have to go now but found the following text on the web - no guarantee it works, or doesn't damage anything :-)

This runtime error 3146 occurs when a SQL server not return a message which indicate the number of rows returned by a statement. If the SQL server unable to return message indicate the number of rows which affect the statements after the below given commands execute on the SQL

server.sp_configure "user options", 512

SET NOCOUNT ON

---------------------------
To get solution from the runtime error 3146 you have to run the below given command which will retrieve the global SQL server user option. Start the ISQL-W and run the command

sp_configure 'user options'

Start ISQL-W and you have to run the below give command  sp_configure 'user options',512

   GO

   RECONFIGURE

   GO

   SET NOCOUNT ON

   GO

You can create a new Visual Basic Project and add three text boxes, two command buttons to form 1 and you have to add a reference to the Microsoft DAO 3.5 Object Library.

You must have to change the username and password by the correct value to run this below given code and it is most important to the user have permission to perform on the database.

Dim wk As Workspace

   Dim rs As Recordset

   Dim cn As Connection

 

   Private Sub Form_Load()

   Dim strConnect As String

   Set wk = DBEngine.CreateWorkspace("ODBCDirect", "", "", dbUseODBC)

            strConnect = "ODBC;DATABASE=PUBS;UID=<user name>;PWD=<strong password>;DSN=MY_DSN"

   Set cn = wk.OpenConnection("Connect1", dbDriverNoPrompt, _

            False, strConnect)

   Set rs = cn.OpenRecordset("SELECT * FROM STORES", dbOpenDynamic, _

            0, dbOptimistic)

   rs.MoveFirst

          Text1.Text = rs.Fields("STOR_ID")

          Text2.Text = rs.Fields("STOR_NAME")

          Text3.Text = rs.Fields("STOR_ADDRESS")

       End Sub

 

  Private Sub Form_Unload(Cancel As Integer)

       rs.Close

       cn.Close

       wk.Close

  End Sub

 

  Private Sub Command1_Click()

       rs.Edit

       rs.Fields("STOR_NAME") = Text2.Text

       rs.Fields("STOR_ADDRESS") = Text3.Text

       rs.Update

  End Sub

 

  Private Sub Command2_Click()

      Unload Me

  End Sub

Copy the above given commands and run the form and just click on command button 1 then you will see

"Run-time error '3146': ODBC call failed" then you have to restore the global SQL Server setting and you can resolve 3146 runtime error odbc call failed

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you check if the record has been inserted?
Also, run the following in the SQL Server instance and post here the result, please:
sp_configure 'user options'

By the way, why are you using a dbOpenDynaset type and not dbOpenTable type?
mlcktmguyAuthor Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why would I use dbOpenTable vs dbOpenDynaset?
Because you're working with full table and not with a set of records:
Set rsOut = db.OpenRecordset("tblExecutionLog", dbOpenDynaset, dbSeeChanges)

I'm trying to run the 'sp_configure 'user options'' but obviously doing it wrong.
It's very simple. Just run the following line of code and nothing else:
exec sp_configure 'user options'

Open in new window

mlcktmguyAuthor Commented:
Thanks, the values returned when I run sp_configure

Name                   User Options
Minimum             0
Maximum            32767
Config_Value        0
run_Value             0
mlcktmguyAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.