March 25, 2003 at 1:20 pm
I have a stored procedure that access a table in a linked server through a local view. Every once in a while, the following error will occur "SQL Server does not exist or access denied". I am calling the stored procedure from an MS Access 2000 front end, using ADO 2.6. Any feedback will be appreciated.
March 25, 2003 at 2:00 pm
What seciurity settings are you using for the linked server? Can you post the query you execute?
March 25, 2003 at 2:06 pm
We authenticate by aliasing all remote users to a single user in the linked server. I will post the code but I do not know if it going to help. This is an issue that will only occur every once in a while., it runs fine otherwise.
This is the view that is called from the stored procedure
SELECTa.loan_id,
b.misc_field_value AS LN_INV_ID,
a.billing_code,
a.int_rate,
a.loan_interest_type
FROM FICSDBSRV.FICS.dbo.cs_loan_information a
INNER JOIN FICSDBSRV.FICS.dbo.cs_loan_misc_fields b ON a.loan_id = b.loan_id
WHERE b.misc_field_id = 10000 -
AND a.cs_co_info_id = 1
ANDa.paid_in_full_yn = 'Y'
March 25, 2003 at 2:06 pm
We authenticate by aliasing all remote users to a single user in the linked server. I will post the code but I do not know if it going to help. This is an issue that will only occur every once in a while., it runs fine otherwise.
This is the view that is called from the stored procedure
SELECTa.loan_id,
b.misc_field_value AS LN_INV_ID,
a.billing_code,
a.int_rate,
a.loan_interest_type
FROM FICSDBSRV.FICS.dbo.cs_loan_information a
INNER JOIN FICSDBSRV.FICS.dbo.cs_loan_misc_fields b ON a.loan_id = b.loan_id
WHERE b.misc_field_id = 10000 -
AND a.cs_co_info_id = 1
ANDa.paid_in_full_yn = 'Y'
March 25, 2003 at 2:13 pm
How stable is your network? Check whether you are able to login to SQL Server from QA each time you have this issue and ensure SQL Server is up.
March 25, 2003 at 2:23 pm
Let me than you first for your help.
We had a job that runs every 10 minutes and does a very simple select on the linked server to ensure that the link server info is refreshed. This SQL Server is also used by other applications with quite a few transactions happening in it.
March 25, 2003 at 2:41 pm
I am confused by the term 'linked server' here. Are you running Access 2000 as front end to access linked tables via OLEDB/ODBC to database in SQL Server? How do you run your stored procedure from Access, through pass-through query? Do you ever have this issue with that simple query?
March 25, 2003 at 3:02 pm
We have an MS Acess 2000 front end calling stored procedures in an instance of SQL Server through ADO (MS data access component library). The view resides in this instance of SQL Server. The table that the view selects from resides in a different instance of SQL Server, the second SQL Server is available by means of a linked server in the first instance of SQL Server. I hope that I am explaining this correctly. Thanks again for your help
March 26, 2003 at 7:20 am
I would run same stored procedure from QA when the error message occurs from your Aceess to see any possible issues between these two SQL Server.
And ask your netwrok administrators to trace the connection between them too.
March 26, 2003 at 8:22 am
Thanks for the advice, we suspect it is a network issue. If I run the stored procedure right away it works fine. Thanks for your help.
March 26, 2003 at 5:59 pm
The cross-server join in your view could be a concern. If the 2 tables are large, the select may experience time-out. Take a look of the execution plan of the query and start the Profiler to see the performance of each bit of the query path. You may be able to narrow down whether the problem is related to the network, or the cross-server query itself.
March 27, 2003 at 8:13 am
Thanks for the feedback. The tables are not that big, a few hundred records at most, but locking could be an issue that we have considered.
March 28, 2003 at 6:15 pm
You can do one thing, check if the Link Server connection is Active, if not EXIT gracefully from your Stored Procedure
You might want to see the section "Testing the link" in this URL
http://www.databasejournal.com/features/mssql/article.php/1438991
Hope this helps.
paul
paul
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply