May 8, 2002 at 3:08 am
Hi all
For some very strange reason, my once working linked server via ole-db sqlserver provider doesnt work, this in on all of my server (dev/test/support/prod). The linked server is from a ss2k sp2 db on win2k AS with sp2 to a boddgy old nt 4 box running ss7 sp2.
The error: queries lockup, never return
Other : setting timeout and connect timeout properties on the LS i get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: Timeout expired]
Also: with EE, if I click on tables and views, it lists all tables and views i expect to see?!
Even funnier, i translated the linked server to an OPENROWSET and it works perfectly.
In the mean time, ive altered my views utilising the linked server to OPENQUERY and encrypted them.
What ive tried:
a) re-create the LS
b) link to another server (worked fine) but going back to the ss7 db fails
c) command line (query analyser) create and GUI (EE) create, all failed.
There are no (apparently) networking issues.
Ideas??
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 8, 2002 at 7:09 am
Have you made any recetn changes to structures on the 7 box? Found a metadata issue with 2000 linking to 7 and it could be related to this. Also how do you maintain your DBs on the 7 box. Do you update stats, usage and rebuild indexes periodically? Could be a cached plan of some kind somewhere (which I didn't think these should occurr with linked servers but strange stuff does happen) that is killing performance do to sutle changes on the 7 box. I am not sure and just theorizing what possiblities there are.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 8:49 am
Hi there, around 2 months back i had a so-called corrupt problem, ran checkdb and the errors disappeared with the fix options. I backed up and recovered the db with no problems. I will reindex, re-collect stats again (done weekly at present) and see what happens.
Very frustrating 🙂
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 8, 2002 at 10:07 am
If you still get failures I would start a trace in Profiler to see if the link is even reachin gthe box and what may be failing.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 9, 2002 at 8:14 pm
Found it.
The query wasnt being submitted to the remote server at all. I checked the remote box after some political hoo-harr and found that there were 3 DTS batch load jobs running, one that was running for 67hrs. I checked the locks and they were all intent to share and one exclusive on a table the linked server does use, even so, the job is a large stored proc that did, at some point refer to the tables the linked server was using.
We cancelled the jobs, and everything is working fine now. CPU was running around 50-70% avg at the time.
The openrowset command worked perfectly!
Anyhow Ive stuck with openrowset for the time being with an encrypted (although poorly) view whilst I drill into this problem a little more.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 10, 2002 at 5:37 am
GLad to here you found the root issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply