Run Time Error 3146, Writing SQl Record

mlcktmguy
mlcktmguy used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
have you tried adding a test record manually via the linked table?
and have you tried adding the new data directly?

Author

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
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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?

Author

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ãoIT Engineer
Distinguished Expert 2017
Commented:
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

Author

Commented:
Thanks, the values returned when I run sp_configure

Name                   User Options
Minimum             0
Maximum            32767
Config_Value        0
run_Value             0

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial