how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which referenced the table and
adjust the proc code for each to your liking. You can get some help in finding which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which refer
enced the table and
adjust the proc code for each to your liking. You can get some help in findi
ng which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...[v
bcol=seagreen]
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>[/vbcol]

How to remove a file [dbcc shrinkfile(filename,empty) is not working]

I tried to remove a file from a filegroup by using the following two comamnd
1) dbcc SHRINKFILE('FileName1', EMPTYFILE)
2) ALTER DATABASE PED_PROD REMOVE FILE FileName1

but I am getting the following error message

Server: Msg 5042, Level 16, State 1, Line 1
The file ''FileName1'' cannot be removed because it is not empty.

Please help in this

Thanks in Advance,
SateeshHowdy

What version of SQL are you using?

Cheers

SG

How to recover Master database table data

Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.

Here is the script:

declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)

-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

-- desligando os vínculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

deallocate table_name_cursor

I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.

Is there anyway to recover the data that I have lost?

Help me understand this.

At the beginning, you say that you don't have backups of the master database.

At the end you say that you tried to restore the master database, but had problems.

You should be able to restore the master database if you have a backup of it, and this would be the best method to use.

Can you clarify, and also let us know what method (T-SQL commands etc.) you used to attempt the restore?

|||Kevin don't have any backup of this database, I tried to rebuild it, not restore it, I used the wrong word for it.|||

There isn't any easy answer to this. If you have already rebuilt master then you should have SQL Server up and running with a clean master. Realize that by rebuilding the master database all your jobs and maintenance plans in MSDB have been lost, unless you have a backup of msdb or scripts to recreate the jobs.

The datafiles for your user databases should still be on the disk, you will need to attach those using sp_attach_db.

Once you have the databases attached you still won't have the logins. You will need to recreate the logins, then remap those new logins using the procedure outlined in http://support.microsoft.com/kb/274188/en-us

|||Ok thanks... one more explanation
In fact my real problem isn't on the databases and user logins, becaus this I doesn't lost. I lost the access to table metadata (via jdbc driver) and the permission to create diagrams. Probably this can be done by reconfiguring the server, but I doesn't know how to do it.

How to rebuild the master database.?

Installed SQLSERVER 2005 EVAL copy in my server and trying to rebuild the master database and it is not working.

I tried the following command.

start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=RAJESH

Any thoughts?

Thanks

First off, can you help me understand why you need to rebuild master instead of restoring from backup? Rebuilding master is typically done when it is too corrupt to allow the instance to start.

If the instance is running, that would probably prevent the procedure above from working. It isn't intended for running instances.

If your instance is down and it still doesn't work, please post the exact error message and we'll see what we can find out.

How to rebuild logshipping after role change

Hi ,
I am working on logshipping DRS trail and have done role successfully.
Now after doing the role change I want to rebuild the logshipping for database which is already existing in secondary server.
I tried adding destination server with existing database. It gives me message as " The database is not in standbymode to do logshipping". Can anyone help me on this.
How should I bring the existing database in warm standby mode.

Thanks
Md.S.HassanYou will have to restore the database on the secondary ensuring that you specify NORECOVERY. Then add the logs (again with NORECOVERY) until you are current.

How to read the error message #Error.

Hi,
My custom code i have written try catch end try block and in the catch
section just i returned the err.description. But locally it was working fine.
Once deployed it was not working fine. just showing #error. Any help would
be appreciated.
Regards,
Sri.On Jun 25, 2:36 pm, Sriman <Sri...@.discussions.microsoft.com> wrote:
> Hi,
> My custom code i have written try catch end try block and in the catch
> section just i returned the err.description. But locally it was working fine.
> Once deployed it was not working fine. just showing #error. Any help would
> be appreciated.
> Regards,
> Sri.
This is a long shot, but you could try checking the Event Viewer:
Application Errors log -or- you could try adjusting the Custom Errors
section of the Reports or ReportServer virtual directories (via: Right-
click -> Properties -> Custom Errors tab) -or- add custom code to the
report (via: Layout view -> Report drop-down -> Report Properties... -
> Code). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant