- Remove From My Forums
-
Вопрос
-
We are running SQL Server 2000 SP4 with patches (build 2187).
There is an in-house developed application that is used by most people within our company. We have noticed that some users have performance issues, whereas others do not.
When I have run a SQL Profiler Trace, it appears that, for those users who are having performance issues, a stored procedure that is heavily used, is always recompiled. However, this does not occur for users who do not have a performance issue.
It is always the same users who have the issue and the same users who do not.
I know that running a stored procedure within different environments, or not fully qualified with the owner name, can cause recompiles, but in this case, all users are running the same program and the program calls the stored procedure using the owner name.
As far as I can see, there isn’t a permission problem, so what could be causing this? Could there be something specific in these user’s profiles which is causing this?
I think the only other thing I can mention at this point is that the application connects to SQL Server using Windows Authentication.
Thank you in advance for any help.
P.S. I’ve tried posting the Stored Procedure, but the forum keeps falling over when I do. Maybe it’s too big. Basically, the stored procedure creates a tempory table, inserts data into it, creates another tempory table, inserts data into it, and then finally runs a long select statement which includes both of these temporary tables as well as other permanent tables. The Stored Procedure accepts just one parameter which is used to filter the data.
- Remove From My Forums
-
Question
-
I have a database on a SQL 2005 box and a subscriber (Transactional Repl) on a SQL2000 box. Inserts/Updates on the 2005 (publisher) are going to the 2000 just fine. Attempts to update a row on the 2000 box are failing with syntax errors that I assume must be coming from the replication stored procs. For example:
UPDATE <TableName> SET VIP = ‘Y’ WHERE id = 40111253
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ‘)’.
Msg 137, Level 15, State 2, Line 5
Must declare the variable ‘@c92o’.It’s failing on the same code and program that run fine on the publisher database. Even simple SQL like the above fail with the same message.
In this article we explain about ERROR 208, why it occurs and how to deal with it.
We often come across situations which seem to be very complicated and challenging but are actually very easy to get out of; provided we think logically. One such situation a lot of SQL Server DBA’s come across is the SSMS error 208, i.e. the ‘Invalid Object Name’ error. This is a very commonly occurring error which ends up confusing a lot of DBAs. So today, we will not only provide you with a solution to this problem, but also suggest you ways to avoid it from occurring in future.
What is Error 208 in SSMS
A lot of us or those DBAs who are not very experienced with using SQL Server, usually opt for exploring the software with the help of SQL Server Management Studio (SSMS). And while you are at it, you might click at a given object to know more about it, and suddenly come across a pop up stating ERROR 208! This is a point where a lot of you might get stuck. But after reading this article we hope things would get better as you would get a greater understanding of the subject.
What is most perplexing about this error to majority of DBAs is the fact that the object is visible to them in SSMS Object Explorer window but when they attempt to select it using T- SQL, they witness the error. This is the point where a lot of DBAs switch to extreme and desperate measures like restarting SSMS multiple times to furiously refreshing queries, restarting databases, etc. But to their sorrow, none of this will provide them with the solution they are looking for.
These practices fail to work because none of these is addressing the root cause of the error which is nothing but simple mismatch in database context.
How to Solve Error 208 in SSMS
Now that you know what is causing the error, finding the solution should not take long. The most common reason for this context mismatching error to occur is the fact that the default database context has been set to master. Well in 99 out of 100 cases, this ends up being the cause. And if this really is what is causing the error to appear in your system, then a very basic thing for you to do is changing the context of the database. And while you are at it, change it in a way that the context becomes similar to the current database object that is in use.
Once you perform this step, you will be glad to see that the error will appear no longer, and all other functions will also happen smoothly. This is one of those errors that almost every single DBA would have experienced, but few would actually know a way out of this situation without considering a repair sql action. This article would hopefully be of help to all of them. Apart from the solution given above, there is one more option that DBAs can try out; this involves prefixing multipart database name to the database that is in use. Or you might also want to change the database that opens by default once you login SSMS.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted mdb and sql recovery software products. For more information visit https://www.datanumen.com/