Could not find stored procedure.....SQL 2000

We are setting up a new server to replace a currently running SQL 2000
server. After restoring the database from a backup, when we attempt to log in
using a custom application, we get an error message stating "[Microsoft][ODBC
SQL Server Driver][SQL Server]Cound not find stored procedure
'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in the
Stored Procedures subfolder in the restored database. I ran the restore from
Enterprise Manager just using the wizzard and had no issues when restoring.
Any ideas?
What user are you connecting as? What is that user's default database? Are
you sure you're executing in the right database? (A common problem is that
a connection string without a database specified would default to a user's
default database, but if the database is dropped/re-created/renamed...) Who
is the owner of the stored procedure? If it is dbo, did you try EXEC
dbo.drs_AuthenticateCurrentUser instead of EXEC drs_AuthenticateCurrentUser?
It is always a good idea to prefix object names both when creating and when
executing.
Aaron Bertrand
SQL Server MVP
"Jason Carter" <JasonCarter@.discussions.microsoft.com> wrote in message
news:0A96F5C3-EB54-46B7-A7B2-3EBAD3E9842B@.microsoft.com...
> We are setting up a new server to replace a currently running SQL 2000
> server. After restoring the database from a backup, when we attempt to log
> in
> using a custom application, we get an error message stating
> "[Microsoft][ODBC
> SQL Server Driver][SQL Server]Cound not find stored procedure
> 'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in
> the
> Stored Procedures subfolder in the restored database. I ran the restore
> from
> Enterprise Manager just using the wizzard and had no issues when
> restoring.
> Any ideas?
|||Thanks for the reply Aaron....
I have tried a number of users including sa. sa's default database is
'master', but the other users I have tried have their default databases set
to MTCCashRecon which is where the stored procedure is located. I have also
verified the permissions for each of those users in that database is the same
as the ones on the old server. The owner of the stored procedure is dbo. I
have not tried to manually execute that procedure; I have only lanched this
from the application. I am nowhere near a SQL DBA and kind of got forced into
this one. I do not have access to the code and do not know if the stored proc
is being called as dbo.drs_authenticateCurrentUser or if it is being called
simply by the name; although I can say that the database was simply restored
from a .BAK file and I would have hoped that it would have just worked.
I have considered using the Data Transformation Sets to get the stored
procedures from the old server, but am not sure how that affects the old
server since it is still in use; does it lock the server or move any stored
procedures vs. just copying them.

Could not find stored procedure.....SQL 2000

We are setting up a new server to replace a currently running SQL 2000
server. After restoring the database from a backup, when we attempt to log i
n
using a custom application, we get an error message stating "[Microsoft]
[ODBC
SQL Server Driver][SQL Server]Cound not find stored procedure
'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in the
Stored Procedures subfolder in the restored database. I ran the restore from
Enterprise Manager just using the wizzard and had no issues when restoring.
Any ideas?What user are you connecting as? What is that user's default database? Are
you sure you're executing in the right database? (A common problem is that
a connection string without a database specified would default to a user's
default database, but if the database is dropped/re-created/renamed...) Who
is the owner of the stored procedure? If it is dbo, did you try EXEC
dbo.drs_AuthenticateCurrentUser instead of EXEC drs_AuthenticateCurrentUser?
It is always a good idea to prefix object names both when creating and when
executing.
Aaron Bertrand
SQL Server MVP
"Jason Carter" <JasonCarter@.discussions.microsoft.com> wrote in message
news:0A96F5C3-EB54-46B7-A7B2-3EBAD3E9842B@.microsoft.com...
> We are setting up a new server to replace a currently running SQL 2000
> server. After restoring the database from a backup, when we attempt to log
> in
> using a custom application, we get an error message stating
> "[Microsoft][ODBC
> SQL Server Driver][SQL Server]Cound not find stored procedure
> 'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in
> the
> Stored Procedures subfolder in the restored database. I ran the restore
> from
> Enterprise Manager just using the wizzard and had no issues when
> restoring.
> Any ideas?|||Thanks for the reply Aaron....
I have tried a number of users including sa. sa's default database is
'master', but the other users I have tried have their default databases set
to MTCCashRecon which is where the stored procedure is located. I have also
verified the permissions for each of those users in that database is the sam
e
as the ones on the old server. The owner of the stored procedure is dbo. I
have not tried to manually execute that procedure; I have only lanched this
from the application. I am nowhere near a SQL DBA and kind of got forced int
o
this one. I do not have access to the code and do not know if the stored pro
c
is being called as dbo.drs_authenticateCurrentUser or if it is being called
simply by the name; although I can say that the database was simply restored
from a .BAK file and I would have hoped that it would have just worked.
I have considered using the Data Transformation Sets to get the stored
procedures from the old server, but am not sure how that affects the old
server since it is still in use; does it lock the server or move any stored
procedures vs. just copying them.

Could not find stored procedure.....SQL 2000

We are setting up a new server to replace a currently running SQL 2000
server. After restoring the database from a backup, when we attempt to log in
using a custom application, we get an error message stating "[Microsoft][ODBC
SQL Server Driver][SQL Server]Cound not find stored procedure
'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in the
Stored Procedures subfolder in the restored database. I ran the restore from
Enterprise Manager just using the wizzard and had no issues when restoring.
Any ideas?What user are you connecting as? What is that user's default database? Are
you sure you're executing in the right database? (A common problem is that
a connection string without a database specified would default to a user's
default database, but if the database is dropped/re-created/renamed...) Who
is the owner of the stored procedure? If it is dbo, did you try EXEC
dbo.drs_AuthenticateCurrentUser instead of EXEC drs_AuthenticateCurrentUser?
It is always a good idea to prefix object names both when creating and when
executing.
--
Aaron Bertrand
SQL Server MVP
"Jason Carter" <JasonCarter@.discussions.microsoft.com> wrote in message
news:0A96F5C3-EB54-46B7-A7B2-3EBAD3E9842B@.microsoft.com...
> We are setting up a new server to replace a currently running SQL 2000
> server. After restoring the database from a backup, when we attempt to log
> in
> using a custom application, we get an error message stating
> "[Microsoft][ODBC
> SQL Server Driver][SQL Server]Cound not find stored procedure
> 'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in
> the
> Stored Procedures subfolder in the restored database. I ran the restore
> from
> Enterprise Manager just using the wizzard and had no issues when
> restoring.
> Any ideas?|||Thanks for the reply Aaron....
I have tried a number of users including sa. sa's default database is
'master', but the other users I have tried have their default databases set
to MTCCashRecon which is where the stored procedure is located. I have also
verified the permissions for each of those users in that database is the same
as the ones on the old server. The owner of the stored procedure is dbo. I
have not tried to manually execute that procedure; I have only lanched this
from the application. I am nowhere near a SQL DBA and kind of got forced into
this one. I do not have access to the code and do not know if the stored proc
is being called as dbo.drs_authenticateCurrentUser or if it is being called
simply by the name; although I can say that the database was simply restored
from a .BAK file and I would have hoped that it would have just worked.
I have considered using the Data Transformation Sets to get the stored
procedures from the old server, but am not sure how that affects the old
server since it is still in use; does it lock the server or move any stored
procedures vs. just copying them.

corrupted tables

Does anyone make a program that will detect and attempt to
correct corrupted tables in a sql database?
DBCC CHECKDB ?
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||Hi,
Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
status of your database.
If you found any errors on any table, you could use
DBCC CHECKTABLE with repair options to clear the errors. Please verify books
online for
command usage.
Thanks
Hari
MCDBA
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||You should always try to restore from your backups rather than run repair -
as repair does not preserve the constraints and business logic inherent in
your data.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
> status of your database.
> If you found any errors on any table, you could use
> DBCC CHECKTABLE with repair options to clear the errors. Please verify
books
> online for
> command usage.
> Thanks
> Hari
> MCDBA
>
> "dave" <davidn@.carlsonpaving.com> wrote in message
> news:986601c478b1$ca55c660$a301280a@.phx.gbl...
>
|||Hi Paul,
MS documentation states that DBCC with REPAIR_REBUILD option will never ends
in a data loss. One instance I had solved an issue
in my production server with out any data loss using the REPAIR_REBUILD
option. That is the reason I recommended Repair_option.
I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
data loss.
So will you please recommend the usage of Repair option?
Correct me if my logic is wrong.
Thanks
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> You should always try to restore from your backups rather than run
repair -
> as repair does not preserve the constraints and business logic inherent in
> your data.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> books
>
|||You are correct that REPAIR_REBUILD will not result in data loss - however,
with all due respect, you didn't say that in your reply - you just said 'If
you found any errors on any table, you could use DBCC CHECKTABLE with repair
options to clear the errors.'
Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration of
the effects. This is spelled out load and clear in the BOL for SQL Server
2005.
Some other things to consider:
1) using a repair option requires the database to be in single_user mode,
i.e. the whole thing is offline, not just the index being rebuilt
2) when any problems are found, root cause analysis should be done rather
than blindly fixing them using repair, even for relatively innocuous
problems involving non-clustered indexes.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hi Paul,
> MS documentation states that DBCC with REPAIR_REBUILD option will never
ends[vbcol=seagreen]
> in a data loss. One instance I had solved an issue
> in my production server with out any data loss using the REPAIR_REBUILD
> option. That is the reason I recommended Repair_option.
> I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
> data loss.
> So will you please recommend the usage of Repair option?
> Correct me if my logic is wrong.
> Thanks
> Hari
> MCDBA
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> repair -
in
> rights.
>
|||Than
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||THan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||Thanks Paul Randal for the detailed information.
Regards
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>