Link to home
Start Free TrialLog in
Avatar of John Okulski
John Okulski

asked on

Connection to SQL Server database being used as a Linked server

I developed software recently for a company where the software I developed is using C# to write to/read from a database attached to a SQL Server Express instance installed on the local machine while the customer is connecting to that instance as a linked server to push/pull information to and from it.  

Well, it seems that after the customer pushed some data to the SQL Server Express instance database while the software was open and connected, I was no longer able to access the data correctly until I close the software down and restarted it (thereby closing the connection and re-opening it).  

I don't know the exact sequence of steps preceding this event and this was the first connection to the local sql server database through the linked server and there had been no issues prior.  So, is there anything that anyone can think of that would disrupt a connection this way?  Anything that might be missing in my connection string, for example?  

Thanks.
Avatar of lcohan
lcohan
Flag of Canada image

Could you please elaborate a bit "I was no longer able to access the data correctly"? were there any locks/block maybe on the SQL Express database table(s)? SQL own sp_who2 executed via SSMS should be able to let you see what is happening at the time including if there are any blocks in the system.
Well, it really depends on how that other person is connecting to the SQL Express instance as a Linked Server.

It sounds suspiciously like the linked server is a problem (but out of your control ???)


Or locking and timing out... But would expect some kind of messaging.

It could also be disallowing ad hoc queries or maybe server options for RPC or DATA ACCESS e.g.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE

-- AND/OR

exec sp_serveroption 'LinkedServerName', 'DATA ACCESS', TRUE;

 

Open in new window

Bit hard to tell without error messages - which should be happening (somewhere).
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.