db connection via odbc

[mightycpa]

First, I'm sure that this problem is not macro scheduler related, but I thought I might ask in order to solve my issue.

I'm having some problems with SQL Server database connectivity. I suspect that there's been some change in the configuration of my server. I wonder if there is a way that MS will let me look a little closer under the covers so that I can understand what is going on.

First, my Microsoft SQL Server ODBC driver stopped working for no apparent reason. I noticed it in MS when I saw this in my log:

7/24/2018 13:56:24:237 - Error In Line: 70 - No valid connection

and then I gave up on it when I discovered the driver didn't work from the Microsoft ODBC Administrator window.

Next, I tried my Native Client ODBC driver that ships with SQL Server. That worked from the ODBC Administrator window. I changed the Provider name in the DB connection string, which I keep in an ini file. Then I ran the following script:

Code: Select all

//Read the ini file to get connection info
Let>conn_path=D:\apps_to_compile\GLIM\GLIMCONN.INI
Let>SQL=select count(*) from stg.dbo.xx_sum_table

ReadIniFile>%conn_path%,DBConnection,DB_Server,v_SERVER
Let>DB_SERVER=%v_SERVER%
//MessageModal>%DB_SERVER%
Wait>1
//read the command line ini file
ReadIniFile>%conn_path%,DBConnection,IMAPSSTG,v_conn_IMAPSSTG
ReadIniFile>%conn_path%,DBConnection,IMAPS,v_conn_IMAPS
ReadIniFile>%conn_path%,DBConnection,DEBUG,v_debug
ReadIniFile>%conn_path%,DBConnection,LOGGING,v_log

//connect to and query db
DBConnect>%v_conn_IMAPSSTG%,dbH
DBQUERY>dbH,%SQL%,sequence_ra,v_numrec1,v_numcol1,0
If>v_debug=HIGH
  MessageModal>on %computer_name% ==> %v_numrec1% and %v_numcol1%
Endif
MessageModal>sequence_ra_1_1
DBClose>dbH

That code runs fine, and returns the number of rows in the table.

Then I ran my original script. As far as the database is concerned, it is the same code using the same connection file, except that the queries are more complicated. This code ran fine until the MS ODBC driver stopped working. Now, using Native Client, I'm getting another problem with my SQL. This is from the log:

7/26/2018 13:28:13:163 - Error In Line: 70 - Query timeout expired

A Google search reveals a similar problem somebody else had where the table was being locked by the same process trying to read it. A reboot solved his problem. It did not solve mine. My timeout is set to infinity.

I wonder if there is a way to get more detail than this error conveys. I really need to understand what's going on.

[mightycpa]

Also, I have noticed that the script works when called from the command line, but not when called from SQL Server in a job. There is no entry in the SQL Server log.

[psiquetp]

Maybe, you could try to change the Isolation level so your read goes "dirty" and therefore you avoid any clashes with other processes.

https://docs.microsoft.com/en-us/sql/t- ... erver-2017

[mightycpa]

Thanks, that was a great suggestion, although I'm not sure why it works. I've done some querying to see if I could find out what is locking the table, but nothing shows up. There is only one table giving me problems, job user permissions are ok, nobody else is using the table and I've rebooted a couple times. Everything looks right.

I did a little reading, and used the WITH (NOLOCK) hint instead. It does a dirty read too, but immediately reverts back once the query is finished. The SET TRANSACTION LEVEL is broader in scope and I thought it might be a problem getting that query to execute, given that I was already having problems with queries.

Here's the syntax: select mycol from table WITH (NOLOCK) WHERE THIS = 'THAT'

[mightycpa]

Now there's one last problem. The very last SQL statement in my script updates this "locked" table. The query timeout is reappearing for that one query only.

I'm beginning to wonder if it is a peculiarity with the driver.