Renaming a Table

Is there a way to rename a table in a database without breaking all the
relationships, views etc.?
Is there any procedure which would search the DB for references to the old
table name and change them to the new one?
ThanksTo display recorded dependencies you can use sp_depends. This may not list
everything though. To be sure, you could use EM to generate a script of all
objects in the database and then do a textual search on the script. An an
interim measure, you could rename the table then create a view with the old
tablename as its name which does a select * from the new tablename, while
you point the other objects towards the new table.
HTH,
Paul Ibison

Renaming a Table

Is there a way to rename a table in a database without breaking all the
relationships, views etc.?
Is there any procedure which would search the DB for references to the old
table name and change them to the new one?
Thanks
To display recorded dependencies you can use sp_depends. This may not list
everything though. To be sure, you could use EM to generate a script of all
objects in the database and then do a textual search on the script. An an
interim measure, you could rename the table then create a view with the old
tablename as its name which does a select * from the new tablename, while
you point the other objects towards the new table.
HTH,
Paul Ibison
sql

Renaming a Table

Is there a way to rename a table in a database without breaking all the
relationships, views etc.?
Is there any procedure which would search the DB for references to the old
table name and change them to the new one?
ThanksTo display recorded dependencies you can use sp_depends. This may not list
everything though. To be sure, you could use EM to generate a script of all
objects in the database and then do a textual search on the script. An an
interim measure, you could rename the table then create a view with the old
tablename as its name which does a select * from the new tablename, while
you point the other objects towards the new table.
HTH,
Paul Ibison|||check out www.dbghost.com
>--Original Message--
>Is there a way to rename a table in a database without
breaking all the
>relationships, views etc.?
>Is there any procedure which would search the DB for
references to the old
>table name and change them to the new one?
>Thanks
>
>.
>

Rename File Name Attachment

I Use Extended Store Procedure to Send Email.
I want to Rename Attachment File Name in result of Query.
What shold I do?Hi Arief,
Why don't you rename the file attachment using DOS command, before attaching
it using xp_sendmail?
- - - - - - - - -
Thanks
Yogishsql

Rename File Name Attachment

I Use Extended Store Procedure to Send Email.
I want to Rename Attachment File Name in result of Query.
What shold I do?
Hi Arief,
Why don't you rename the file attachment using DOS command, before attaching
it using xp_sendmail?
- - - - - - - - -
Thanks
Yogish

Rename File Name Attachment

I Use Extended Store Procedure to Send Email.
I want to Rename Attachment File Name in result of Query.
What shold I do?Hi Arief,
Why don't you rename the file attachment using DOS command, before attaching
it using xp_sendmail?
--
- - - - - - - - -
Thanks
Yogish

rename columns in a temp table

hello
I have a stored procedure that calculates the values of column data based
on an input variable. I would like to rename the columns to the input
variables so that i can directly graph the results of the table. Is this
possible?
for example, a column in the table #summary is called year1 but i need it to
be renamed to '@.inputYear - 5'. So if @.inputYear = 2000, i need the column
to be renamed to 1995.
thanks for any and all help!
benBen wrote:
> hello
> I have a stored procedure that calculates the values of column data
> based on an input variable. I would like to rename the columns to
> the input variables so that i can directly graph the results of the
> table. Is this possible?
> for example, a column in the table #summary is called year1 but i
> need it to be renamed to '@.inputYear - 5'. So if @.inputYear = 2000,
> i need the column to be renamed to 1995.
> thanks for any and all help!
> ben
You can use an alias for the column, but will have to use dynamic sql.
Or you can return two result sets. The first is the name of the columns,
the second is the data to graph.
EXEC ('Select year1 as "' + CAST(@.inputYear - 5 as VARCHAR(4)) + '"
From...')
David Gugick
Quest Software
www.imceda.com
www.quest.com

Rename Cluster Instance

Hi,
I would like to rename a cluster name {network name}
from INTSQL1 to lets say INTSQLVS
whats the procedure ?
With SQL there is not a supported way. Migrate to the new name or uninstall
and start over.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"John Smith" <bukusu@.gmail.com> wrote in message
news:%23xgTTBAOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I would like to rename a cluster name {network name}
> from INTSQL1 to lets say INTSQLVS
> whats the procedure ?
>

Rename and Stored procedure

Hi All,
I have been working on the following problem for more than two ws
but without any luck.
We have ftp server on windows 2003 Exchange server with IIS 6.0. There
is a DSN to connect to SQL Server 2000 databases from the FTP site.
When a file is ftped, it populates the database with file name and the
group name. Stored procedures run every minute to check whether the
database has been populated with the file information. Then it tries to
rename the file with timestamp.
When I use the rename command in the query analyzer, it can rename the
file but from the stored procedure the same code is not successful in
renaming the file.
Here is the rename portion of the code in the stored procedure.
select @.renamestring = 'rename \\W2K3\data\' + rtrim(@.oldfilename) + '
'+ rtrim(@.newfilename)
declare @.result int
declare @.querystring char(200)
EXEC @.result = master.dbo.xp_cmdshell @.renamestring
here is the same in the QA to test , which renames really fine.
master..xp_cmdshell 'rename \\w2k3\data\ftp\test.txt test.sql'
I know it is hard to understand the problem looking at the portion of
the problem. But if you need I can follow-up with the complete code.
I will highly appreciate if you could help me. I have been working on
this for the last two ws.
I thank you in advance million times for your help.
Best regards,
mamunMay be there are files where the name include spaces, in these cases you hav
e
to enclose the path between double quote.
Example:
exec master..xp_cmdshell 'ren "\temp\test 1.txt" test1.txt'
go
AMB
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> I have been working on the following problem for more than two ws
> but without any luck.
> We have ftp server on windows 2003 Exchange server with IIS 6.0. There
> is a DSN to connect to SQL Server 2000 databases from the FTP site.
> When a file is ftped, it populates the database with file name and the
> group name. Stored procedures run every minute to check whether the
> database has been populated with the file information. Then it tries to
> rename the file with timestamp.
> When I use the rename command in the query analyzer, it can rename the
> file but from the stored procedure the same code is not successful in
> renaming the file.
> Here is the rename portion of the code in the stored procedure.
> select @.renamestring = 'rename \\W2K3\data' + rtrim(@.oldfilename) + '
> '+ rtrim(@.newfilename)
> declare @.result int
> declare @.querystring char(200)
> EXEC @.result = master.dbo.xp_cmdshell @.renamestring
>
> here is the same in the QA to test , which renames really fine.
> master..xp_cmdshell 'rename \\w2k3\data\ftp\test.txt test.sql'
>
> I know it is hard to understand the problem looking at the portion of
> the problem. But if you need I can follow-up with the complete code.
> I will highly appreciate if you could help me. I have been working on
> this for the last two ws.
> I thank you in advance million times for your help.
> Best regards,
> mamun
>|||HI AMB,
Here is my complete codes of the two sps. I am still without any luck.
Could anyone see any errors in the following code?
As always I am thankful for your help.
best regards,
mamun
SP1:
CREATE Procedure sp_FTPNotify_New (@.logId int)
As
declare @.recips varchar(255)
declare @.msg varchar(250)
declare @.sub varchar(75)
DECLARE @.cmd varchar(56)
Declare @.txtPtr varbinary(16)
declare @.new int, @.old int
declare @.txt varchar(255)
declare @.email varchar(50)
declare @.newfilename varchar(255)
declare @.oldfilename varchar(255)
declare @.renamestring varchar(255)
declare @.fieldposition int
declare @.NewTarget varchar(255)
declare @.newprefix varchar(20)
declare @.FailedFlag varchar(255)
declare @.LogTime datetime
Select @.NewTarget=Target , @.newprefix =
rtrim(rtrim(convert(char,logtime,12))+co
nvert(char,LogID)),
@.FailedFlag = Operation,@.LogTime = LogTime
from FTPLogs l , FTPNotify n
where LogID = @.logId and
lower(l.username) = lower(n.username)
select @.fieldposition=0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition = @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) = ']'
break
END
if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
='created'
begin
select @.fieldposition=0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition = @.fieldposition+1
if substring(@.NewTarget,@.fieldposition,1) = '.'
break
END
Select
@.NewTarget=rTrim(substring(@.NewTarget,1,
@.fieldposition-1))+rtrim(@.newprefix+
rTrim(substring(@.NewTarget,@.fieldpositio
n,255)))
Select @.msg = 'This is an automatically generated FTP notification
message:'
delete from texttab
INSERT into texttab
select 'This message was generated on ' +
convert(varchar(25),getdate()) + char(13) + replicate ('_', 45) +
char(13)
select
@.recips = ' + rtrim(notify) + ',
@.txt =
'The file from ' + rtrim(description) + ' has arrived. This file is
located on the server W2K3-S1 ' +
'under the DATA' + rtrim(l.username) + ' directory.' + char(13) +
char(13) +
'The file name: ' + rtrim(@.NewTarget) + char(13) +
'Date Received: ' + convert(varchar(25),LogTime) + char(13) +
'File Size: ' + convert(char(20), BytesRecvd) ,
@.sub = 'FTPLog Notification from " + rtrim(description) + ',
@.newfilename = rtrim(@.NewTarget),
@.oldfilename = rtrim(l.username)+''+ rtrim(Target)
from FTPLogs l , FTPNotify n
where LogID = @.logId and
lower(l.username) = lower(n.username)
select @.txtptr = textptr(c1) from texttab
UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
SELECT @.cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
@.recipients = ' + @.recips + ',
@.message = ' + @.msg + ',
@.query = '+ @.cmd + ',
@.subject = ' + @.sub + ',
@.no_header = 'TRUE', @.width = 2500
delete from texttab
select @.renamestring = 'rename \\W2k3-S1\data' + rtrim(@.oldfilename) +
' '+ rtrim(@.newfilename)
print @.renamestring
declare @.result int
declare @.querystring char(200)
EXEC @.result = master.dbo.xp_cmdshell @.renamestring
if (@.result = 1)
begin
select @.querystring ='SELECT logid,substring(username,1,20)
username,logtime,bytesrecvd,substring(ta
rget,1,50) filename FROM
ftplogs.dbo.ftplogs where logid = '+ convert(char,@.logId)
exec master.dbo.xp_sendmail @.recipients = 'mamun@.inc.com',
@.query = '" + @.querystring + " ' ,
@.subject ='Failed Rename',
@.message ='The following file could not be renamed.',
@.attach_results = 'FALSE', @.width = 250
end
end
Else
if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition)) =
'closed'
begin
select @.fieldposition=0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition = @.fieldposition+1
if substring(@.NewTarget,@.fieldposition,1) = '.'
break
END
Select
@.NewTarget=rTrim(substring(@.NewTarget,1,
@.fieldposition-1))+rtrim(@.newprefix+
rTrim(substring(@.NewTarget,@.fieldpositio
n,255)))
Select @.msg = 'This is an automatically generated FTP notification
message:'
delete from texttab
INSERT into texttab
select 'This message was generated on ' +
convert(varchar(25),getdate()) + char(13) +
replicate ('_', 45) + char(13)
select
@.recips = ''' + rtrim(notify) + ''',
@.txt = char(13)+
'THE ATEMPTED FTP FILE TRANSFER TO SERVICES '+ char(13)
+'ON ' +UPPER(convert(varchar(25),LOGTIME)) + ' FROM ' +
upper(rtrim(description)) + char(13)
+'WAS NOT SUCCESSFULLY RECEIVED.'+ char(13)+ char(13)
+'IF NECESSARY PLEASE CONTACT THE APPROPRIATE PARTY' + char(13)
+'TO HAVE THE FILE RESENT.' ,
--@.sub = 'FTPLog Notification ALERT from ' + rtrim(description) + '''
@.sub = 'FTPLog Notification ALERT from ' + rtrim(description) + ''
from FTPLogs l , FTPNotify n
where LogID = @.logId and
lower(l.username) = lower(n.username)
select @.txtptr = textptr(c1) from texttab
UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
SELECT @.cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
@.recipients = " + @.recips + ",
@.message = " + @.msg + ",
@.query = "+ @.cmd + ",
@.subject = " + @.sub + ",
@.no_header = 'TRUE', @.width = 2500
delete from texttab
end
GO
SP2:
CREATE Procedure sp_MailNotify_New
As
Declare @.id int
Declare @.CStatus int
Declare @.fieldposition int
declare @.FailedFlag varchar(255)
Declare C_getLog cursor for
select LogId,Operation from FTPLogs where notified = 0 order by LogID
Open C_getLog
Fetch Next from C_getLog into @.id,@.FailedFlag
select @.CStatus = @.@.FETCH_STATUS
select @.fieldposition=0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition = @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) = ']'
break
END
select @.FailedFlag =
lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
while (@.CStatus <> -1 and (@.FailedFlag = 'closed' or @.FailedFlag =
'created'))
begin
select 'THE ID is ' + convert(char(8),@.id)
execute sp_FTPNotify_new @.id
update FTPLogs set notified = 1 where LogId = @.id
Fetch Next from C_getLog into @.id,@.FailedFlag
select @.CStatus = @.@.FETCH_STATUS
select @.fieldposition=0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition = @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) = ']'
break
END
select @.FailedFlag =
lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
end
update FTPLogs set notified = 1 where LogId <= @.id
Close C_getLog
Deallocate C_getLog
GO
In the scheduled jobs: exec exec sp_MailNotify_New

Removing Windows User from the Security folder

Can't seem to locate the stored procedure for removing a Windows Authenticat
ed User from the Security folder.
sp_droplogin doesn't seem to work. It only works on SQL Logins.
Can't find what appears to be proper procedure in Master database.
What I get is the message "The Login [login name] doesn't exist".
Thanks for any help.
ScottSee sp_revokelogin in Books On Line, sp_droplogin is for SQL logins.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Scott" <scasti1@.cox.net> wrote in message
news:E38AC590-50C0-44B3-BDA2-91FCC0E43CB3@.microsoft.com...
> Can't seem to locate the stored procedure for removing a Windows
Authenticated User from the Security folder.
> sp_droplogin doesn't seem to work. It only works on SQL Logins.
> Can't find what appears to be proper procedure in Master database.
> What I get is the message "The Login [login name] doesn't exist".
> Thanks for any help.
> Scott
>

removing windows group that's a database user

When attempting to remove a windows group using the
stored procedure xp_dropuser obtain the following error:
Server: Msg 15008, Level 16, State 1, Procedure
sp_dropuser, Line 12
User 'D0550000\CSMBalanceInquiry' does not exist in the
current database.
D0550000 is the domain and CSMBalanceInquiry is the
windows group.
Before removing this group I execute sp_helpuser and do a
copy and paste of the name.
If I attempt to remove this group through the enterprise
manager, I do not have a problem. I tried to search in
the Microsoft knowledge base and could not find an
article explaining this problem. Thank you for your help
in advance.You need to specify the value returned from sp_helpuser for the "name" of
the user\group.
You can see the valid names in the database by running select * from
sysusers while in that database.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||is the group a role or a user? Note that sp_helpuser can return information
about roles as well as users...
If you think that might be the case try sp_droprole.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:009e01c3c3fe$8e7b9880$a401280a@.phx.gbl...

quote:

---

> When attempting to remove a windows group using the
> stored procedure xp_dropuser obtain the following error:
> Server: Msg 15008, Level 16, State 1, Procedure
> sp_dropuser, Line 12
> User 'D0550000\CSMBalanceInquiry' does not exist in the
> current database.
> D0550000 is the domain and CSMBalanceInquiry is the
> windows group.
> Before removing this group I execute sp_helpuser and do a
> copy and paste of the name.
> If I attempt to remove this group through the enterprise
> manager, I do not have a problem. I tried to search in
> the Microsoft knowledge base and could not find an
> article explaining this problem. Thank you for your help
> in advance.

---

removing a user from a database problem

I have followed the procedure in http://support.microsoft.com/kb/275312
to change object ownership so that I can delete a user.
This appears to have worked ( subsequent runs tell me that the
original user owns no objects and a query on the sysobjects table
filtered on their uid confirms this)
When I go to delete the user in enterprise manager it tells me that
the user still owns objects ( i have refreshed the view and even
exited EM to no effect)
I am using SQL server 2000 sp4
Any help much appreciated...
TIAHi
Have you tried sp_dropuser stored procedure in QA?
<pryan@.meticulus.com> wrote in message
news:1185880756.933653.204030@.19g2000hsx.googlegroups.com...
>I have followed the procedure in http://support.microsoft.com/kb/275312
> to change object ownership so that I can delete a user.
> This appears to have worked ( subsequent runs tell me that the
> original user owns no objects and a query on the sysobjects table
> filtered on their uid confirms this)
> When I go to delete the user in enterprise manager it tells me that
> the user still owns objects ( i have refreshed the view and even
> exited EM to no effect)
> I am using SQL server 2000 sp4
> Any help much appreciated...
> TIA
>

removing a user from a database problem

I have followed the procedure in http://support.microsoft.com/kb/275312
to change object ownership so that I can delete a user.
This appears to have worked ( subsequent runs tell me that the
original user owns no objects and a query on the sysobjects table
filtered on their uid confirms this)
When I go to delete the user in enterprise manager it tells me that
the user still owns objects ( i have refreshed the view and even
exited EM to no effect)
I am using SQL server 2000 sp4
Any help much appreciated...
TIAHi
Have you tried sp_dropuser stored procedure in QA?
<pryan@.meticulus.com> wrote in message
news:1185880756.933653.204030@.19g2000hsx.googlegroups.com...
>I have followed the procedure in http://support.microsoft.com/kb/275312
> to change object ownership so that I can delete a user.
> This appears to have worked ( subsequent runs tell me that the
> original user owns no objects and a query on the sysobjects table
> filtered on their uid confirms this)
> When I go to delete the user in enterprise manager it tells me that
> the user still owns objects ( i have refreshed the view and even
> exited EM to no effect)
> I am using SQL server 2000 sp4
> Any help much appreciated...
> TIA
>

removing a user from a database problem

I have followed the procedure in http://support.microsoft.com/kb/275312
to change object ownership so that I can delete a user.
This appears to have worked ( subsequent runs tell me that the
original user owns no objects and a query on the sysobjects table
filtered on their uid confirms this)
When I go to delete the user in enterprise manager it tells me that
the user still owns objects ( i have refreshed the view and even
exited EM to no effect)
I am using SQL server 2000 sp4
Any help much appreciated...
TIA
Hi
Have you tried sp_dropuser stored procedure in QA?
<pryan@.meticulus.com> wrote in message
news:1185880756.933653.204030@.19g2000hsx.googlegro ups.com...
>I have followed the procedure in http://support.microsoft.com/kb/275312
> to change object ownership so that I can delete a user.
> This appears to have worked ( subsequent runs tell me that the
> original user owns no objects and a query on the sysobjects table
> filtered on their uid confirms this)
> When I go to delete the user in enterprise manager it tells me that
> the user still owns objects ( i have refreshed the view and even
> exited EM to no effect)
> I am using SQL server 2000 sp4
> Any help much appreciated...
> TIA
>