Removing a server: windows

Showing posts with label windows. Show all posts

Friday, March 30, 2012

Renaming Cluster Nodes

Realise it is a common question and have searched through the group:
I have a Windows 2003 R2 cluster (with SQL 2000 SP4) which needs to be
renamed (Nodes, cluster name)
Is there any way to bypass re-installing SQL 2000 after this has been
completed?
(Various non microsoft sites point towards using sp_dropserver and
sp_addserver or updating the syservers table)
eg http://blog.opsan.com/archive/2005/05/10/465.aspx
http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
Thanks.
why you need rename nodes?
you can change virtual server name but microsoft not recommend this.
do NOT update system tables manually.
if you rename sql server (sp_dropserver and sp_addserver), cluster will not
check sql status and servis will be mark as failure.
best solution: install new sql
Aleksandar Grbic
MCDBA, Senior Database Administrator
"RichardT" wrote:

> Realise it is a common question and have searched through the group:
> I have a Windows 2003 R2 cluster (with SQL 2000 SP4) which needs to be
> renamed (Nodes, cluster name)
> Is there any way to bypass re-installing SQL 2000 after this has been
> completed?
> (Various non microsoft sites point towards using sp_dropserver and
> sp_addserver or updating the syservers table)
> eg http://blog.opsan.com/archive/2005/05/10/465.aspx
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> Thanks.
|||"RichardT" <RichardT@.discussions.microsoft.com> wrote in message
news:7DE1DC20-FF62-4B1B-BE0A-6151F11D06F9@.microsoft.com...
> Realise it is a common question and have searched through the group:
> I have a Windows 2003 R2 cluster (with SQL 2000 SP4) which needs to be
> renamed (Nodes, cluster name)
> Is there any way to bypass re-installing SQL 2000 after this has been
> completed?
> (Various non microsoft sites point towards using sp_dropserver and
> sp_addserver or updating the syservers table)
> eg http://blog.opsan.com/archive/2005/05/10/465.aspx
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
Well, I would start with going back to basics and doing a risk assessment
associated with the change and mitigation strategies. Risk, everything
possible going wrong. Mitigating that risk: Don't make the change as it is
completely unnecessary.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
NYC starting Feb 19th
London starting March 6th

Renaming and migration Problems

Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
moved the Database over to this new server, then renamed the server and
assigned it the same IP as the old one. then we did the sp_dropserver
and ap_addserver script to cange the SQL server name. the problems that
we keep getting it that the client machines can't connect to the SQL
Server now. They can see the server and ping to it but can't access the
actual SQL databses. the error message that I get is that its "SQL
Server cannot be found or access denied". any ideas? we have been
trying to get his new server up and running but keep running into this
issue. are we missing any steps?
Thanks
Hello,
Did you restarted the SQL Server and SQL Agent service after issuing
SP_DROPServer and SP_ADDSERVER commands?
As well as did you gave the below command to add server
SP_ADDSERVER 'Newservername','LOCAL'
Try both and keep us posted..
Thanks
Hari
<rncota@.gmail.com> wrote in message
news:1168899609.093776.80630@.l53g2000cwa.googlegro ups.com...
> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
|||Hi
"rncota@.gmail.com" wrote:

> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
If you connect via query analyser on the machine itself and SELECT
@.@.SERVERNAME do you get the new name or the old one? If the old one check
that you are dropping the server correctly as described by
http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
You don't say if you transferred the logins as well as the database, see
http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
will show any orphaned users.
Have you tried connecting with the IP address? Check which protocols are
installed on the server and client and that the clients don't have any
aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
not specified as on when installing.
John
|||Hari Prasad wrote:
> Hello,
> Did you restarted the SQL Server and SQL Agent service after issuing
> SP_DROPServer and SP_ADDSERVER commands?
> As well as did you gave the below command to add server
> SP_ADDSERVER 'Newservername','LOCAL'
> Try both and keep us posted..
> Thanks
> Hari
>
Yes, we used that process to change the internal name of the SQL
Server. We checked the name using Select @.@.SERVERNAME. also we only
have one instance running, the defualt one.
|||> If you connect via query analyser on the machine itself and SELECT
> @.@.SERVERNAME do you get the new name or the old one? If the old one check
> that you are dropping the server correctly as described by
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> You don't say if you transferred the logins as well as the database, see
> http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
> will show any orphaned users.
> Have you tried connecting with the IP address? Check which protocols are
> installed on the server and client and that the clients don't have any
> aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
> not specified as on when installing.
> John
Yes, we checked using Select @.@.SERVERNAME and the new server name comes
up. We also migrated the logins as well as DTS packages and jobs. When
I run exec sp_change_users_login 'Report' nothing comes up as being
orphaned.
I can ping the server using both the Name of the Server and the IP
address of the server through a command prompt from a client machine.
the problem comes up when i try to connect through applications that
access the databases. an example of how I test it was through and ODBC
connection that is required for one of our apps. When went in to test
the connection to it in the ODBC settings, it says that it cannot find
the specified server. I know the login infomation is correct and I know
the logins exisit.
Just to give more information to help figure out this problem, another
example is when we were testing our DTS packages on the SQL server,
anywhere that referenced the name to the SQL Server would come back
with an error telling us that the server could not be found. which is
odd because these packages are sitting inside that server. Very odd. I
feel like there is something missing some where, but we have done
everything by the book from what I can tell.
|||Also i just did a test, since we couldn't get the new SQL server
running properly we went back to the old server. the new server is up
but now running on a different name. When i test a ODBC connection to
the new server using a connection with the name is has now, the
connection works fine. seems to be some issue when we try to pass on
the name and IP of the old server to it.
|||rncota@.gmail.com wrote:
> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
Try this simple test:
1. Choose a login that you are attempting to connect with, one that
fails, i.e. login name "bob"
2. Open a Query Analyzer session, connect to your application database
as "sa"
3. EXEC sp_dropuser 'bob'
4. EXEC sp_adduser 'bob'
5. EXEC sp_addrolemember 'db_datareader', 'bob'
Now test a connection to the application database using the login 'bob'
- does it connect successfully?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||
> Try this simple test:
> 1. Choose a login that you are attempting to connect with, one that
> fails, i.e. login name "bob"
> 2. Open a Query Analyzer session, connect to your application database
> as "sa"
> 3. EXEC sp_dropuser 'bob'
> 4. EXEC sp_adduser 'bob'
> 5. EXEC sp_addrolemember 'db_datareader', 'bob'
> Now test a connection to the application database using the login 'bob'
> - does it connect successfully?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I tried this test, it works fine when I try to connect to the server
with its current name but when We change the server name it stops
working.
|||Hi
"rncota@.gmail.com" wrote:

> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
By being able to connect to the server when the name was changed back means
that your protocols are ok. Did you check for aliases on the client? Have you
tried to connect using the IP? Can you connect using Query Analyser from the
client? When you changed the name of the server (for the second time) did you
drop the server and add it again?
John

Renaming and migration Problems

Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
moved the Database over to this new server, then renamed the server and
assigned it the same IP as the old one. then we did the sp_dropserver
and ap_addserver script to cange the SQL server name. the problems that
we keep getting it that the client machines can't connect to the SQL
Server now. They can see the server and ping to it but can't access the
actual SQL databses. the error message that I get is that its "SQL
Server cannot be found or access denied". any ideas? we have been
trying to get his new server up and running but keep running into this
issue. are we missing any steps?
ThanksHello,
Did you restarted the SQL Server and SQL Agent service after issuing
SP_DROPServer and SP_ADDSERVER commands?
As well as did you gave the below command to add server
SP_ADDSERVER 'Newservername','LOCAL'
Try both and keep us posted..
Thanks
Hari
<rncota@.gmail.com> wrote in message
news:1168899609.093776.80630@.l53g2000cwa.googlegroups.com...
> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>|||Hari Prasad wrote:
> Hello,
> Did you restarted the SQL Server and SQL Agent service after issuing
> SP_DROPServer and SP_ADDSERVER commands?
> As well as did you gave the below command to add server
> SP_ADDSERVER 'Newservername','LOCAL'
> Try both and keep us posted..
> Thanks
> Hari
>
Yes, we used that process to change the internal name of the SQL
Server. We checked the name using Select @.@.SERVERNAME. also we only
have one instance running, the defualt one.|||> If you connect via query analyser on the machine itself and SELECT
> @.@.SERVERNAME do you get the new name or the old one? If the old one check
> that you are dropping the server correctly as described by
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> You don't say if you transferred the logins as well as the database, see
> http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
> will show any orphaned users.
> Have you tried connecting with the IP address? Check which protocols are
> installed on the server and client and that the clients don't have any
> aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
> not specified as on when installing.
> John
Yes, we checked using Select @.@.SERVERNAME and the new server name comes
up. We also migrated the logins as well as DTS packages and jobs. When
I run exec sp_change_users_login 'Report' nothing comes up as being
orphaned.
I can ping the server using both the Name of the Server and the IP
address of the server through a command prompt from a client machine.
the problem comes up when i try to connect through applications that
access the databases. an example of how I test it was through and ODBC
connection that is required for one of our apps. When went in to test
the connection to it in the ODBC settings, it says that it cannot find
the specified server. I know the login infomation is correct and I know
the logins exisit.
Just to give more information to help figure out this problem, another
example is when we were testing our DTS packages on the SQL server,
anywhere that referenced the name to the SQL Server would come back
with an error telling us that the server could not be found. which is
odd because these packages are sitting inside that server. Very odd. I
feel like there is something missing some where, but we have done
everything by the book from what I can tell.|||Also i just did a test, since we couldn't get the new SQL server
running properly we went back to the old server. the new server is up
but now running on a different name. When i test a ODBC connection to
the new server using a connection with the name is has now, the
connection works fine. seems to be some issue when we try to pass on
the name and IP of the old server to it.|||rncota@.gmail.com wrote:
> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
Try this simple test:
1. Choose a login that you are attempting to connect with, one that
fails, i.e. login name "bob"
2. Open a Query Analyzer session, connect to your application database
as "sa"
3. EXEC sp_dropuser 'bob'
4. EXEC sp_adduser 'bob'
5. EXEC sp_addrolemember 'db_datareader', 'bob'
Now test a connection to the application database using the login 'bob'
- does it connect successfully?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Try this simple test:
> 1. Choose a login that you are attempting to connect with, one that
> fails, i.e. login name "bob"
> 2. Open a Query Analyzer session, connect to your application database
> as "sa"
> 3. EXEC sp_dropuser 'bob'
> 4. EXEC sp_adduser 'bob'
> 5. EXEC sp_addrolemember 'db_datareader', 'bob'
> Now test a connection to the application database using the login 'bob'
> - does it connect successfully?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I tried this test, it works fine when I try to connect to the server
with its current name but when We change the server name it stops
working.

Renaming and migration Problems

Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
moved the Database over to this new server, then renamed the server and
assigned it the same IP as the old one. then we did the sp_dropserver
and ap_addserver script to cange the SQL server name. the problems that
we keep getting it that the client machines can't connect to the SQL
Server now. They can see the server and ping to it but can't access the
actual SQL databses. the error message that I get is that its "SQL
Server cannot be found or access denied". any ideas? we have been
trying to get his new server up and running but keep running into this
issue. are we missing any steps?
ThanksHello,
Did you restarted the SQL Server and SQL Agent service after issuing
SP_DROPServer and SP_ADDSERVER commands?
As well as did you gave the below command to add server
SP_ADDSERVER 'Newservername','LOCAL'
Try both and keep us posted..
Thanks
Hari
<rncota@.gmail.com> wrote in message
news:1168899609.093776.80630@.l53g2000cwa.googlegroups.com...
> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>|||Hi
"rncota@.gmail.com" wrote:

> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
If you connect via query analyser on the machine itself and SELECT
@.@.SERVERNAME do you get the new name or the old one? If the old one check
that you are dropping the server correctly as described by
http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
You don't say if you transferred the logins as well as the database, see
http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
will show any orphaned users.
Have you tried connecting with the IP address? Check which protocols are
installed on the server and client and that the clients don't have any
aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
not specified as on when installing.
John|||Hari Prasad wrote:
> Hello,
> Did you restarted the SQL Server and SQL Agent service after issuing
> SP_DROPServer and SP_ADDSERVER commands?
> As well as did you gave the below command to add server
> SP_ADDSERVER 'Newservername','LOCAL'
> Try both and keep us posted..
> Thanks
> Hari
>
Yes, we used that process to change the internal name of the SQL
Server. We checked the name using Select @.@.SERVERNAME. also we only
have one instance running, the defualt one.|||> If you connect via query analyser on the machine itself and SELECT
> @.@.SERVERNAME do you get the new name or the old one? If the old one check
> that you are dropping the server correctly as described by
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> You don't say if you transferred the logins as well as the database, see
> http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
> will show any orphaned users.
> Have you tried connecting with the IP address? Check which protocols are
> installed on the server and client and that the clients don't have any
> aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
> not specified as on when installing.
> John
Yes, we checked using Select @.@.SERVERNAME and the new server name comes
up. We also migrated the logins as well as DTS packages and jobs. When
I run exec sp_change_users_login 'Report' nothing comes up as being
orphaned.
I can ping the server using both the Name of the Server and the IP
address of the server through a command prompt from a client machine.
the problem comes up when i try to connect through applications that
access the databases. an example of how I test it was through and ODBC
connection that is required for one of our apps. When went in to test
the connection to it in the ODBC settings, it says that it cannot find
the specified server. I know the login infomation is correct and I know
the logins exisit.
Just to give more information to help figure out this problem, another
example is when we were testing our DTS packages on the SQL server,
anywhere that referenced the name to the SQL Server would come back
with an error telling us that the server could not be found. which is
odd because these packages are sitting inside that server. Very odd. I
feel like there is something missing some where, but we have done
everything by the book from what I can tell.|||Also i just did a test, since we couldn't get the new SQL server
running properly we went back to the old server. the new server is up
but now running on a different name. When i test a ODBC connection to
the new server using a connection with the name is has now, the
connection works fine. seems to be some issue when we try to pass on
the name and IP of the old server to it.|||rncota@.gmail.com wrote:
> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
Try this simple test:
1. Choose a login that you are attempting to connect with, one that
fails, i.e. login name "bob"
2. Open a Query Analyzer session, connect to your application database
as "sa"
3. EXEC sp_dropuser 'bob'
4. EXEC sp_adduser 'bob'
5. EXEC sp_addrolemember 'db_datareader', 'bob'
Now test a connection to the application database using the login 'bob'
- does it connect successfully?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||
> Try this simple test:
> 1. Choose a login that you are attempting to connect with, one that
> fails, i.e. login name "bob"
> 2. Open a Query Analyzer session, connect to your application database
> as "sa"
> 3. EXEC sp_dropuser 'bob'
> 4. EXEC sp_adduser 'bob'
> 5. EXEC sp_addrolemember 'db_datareader', 'bob'
> Now test a connection to the application database using the login 'bob'
> - does it connect successfully?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I tried this test, it works fine when I try to connect to the server
with its current name but when We change the server name it stops
working.|||Hi
"rncota@.gmail.com" wrote:

> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
By being able to connect to the server when the name was changed back means
that your protocols are ok. Did you check for aliases on the client? Have yo
u
tried to connect using the IP? Can you connect using Query Analyser from the
client? When you changed the name of the server (for the second time) did yo
u
drop the server and add it again?
Johnsql

Renaming a server

Hi all,

I have a Windows 2003 server box running SQL 2000 and BizTalk 2006. I need to change the name of the underlying server, could anyone point me in the right direction of what I would need to change in terms of SQL and BizTalk config to make it all sit happily with this?

Kind regards
Taz

Check out http://msdn2.microsoft.com/en-us/library/ms143799.aspx

Thanks,

Peter Saddow

Monday, March 26, 2012

Rename SQL Server Virtual name in 2003 Cluster

Is it possible to change the current SQL virtual host name in Windows 2003?
and How?
I tried it by right click on the SQL Network name resource -> properties ->
parameters -> change the name. Then search the registry and change it to a
new name and reboot the node. It works but is there an easy way and is what
I did a safe procedure.
Thanks
Ayad
You sure can, see http://support.microsoft.com/?id=307336 for details

Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Ayad" <ashammout1@.hotmail.com> wrote in message
news:%239vieFHsEHA.760@.tk2msftngp13.phx.gbl...
> Is it possible to change the current SQL virtual host name in Windows
> 2003?
> and How?
> I tried it by right click on the SQL Network name resource ->
> properties ->
> parameters -> change the name. Then search the registry and change it to a
> new name and reboot the node. It works but is there an easy way and is
> what
> I did a safe procedure.
> Thanks
> Ayad
>
|||Thanks Rod. But I don't want to un-install and re-install SQL to change the
name.
Ayad
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23X1xRMIsEHA.2732@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You sure can, see http://support.microsoft.com/?id=307336 for details

> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Ayad" <ashammout1@.hotmail.com> wrote in message
> news:%239vieFHsEHA.760@.tk2msftngp13.phx.gbl...
a
>
|||Ok, that's cool. You know you don't have to reinstall. It's right click
rename, then sp_dropserver old_virtualservername
go, sp_addserver new_virtualservername, local go, etc.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Ayad" <ashammout1@.hotmail.com> wrote in message
news:eEPiKeJsEHA.2904@.TK2MSFTNGP15.phx.gbl...
> Thanks Rod. But I don't want to un-install and re-install SQL to change
> the
> name.
> Ayad
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23X1xRMIsEHA.2732@.TK2MSFTNGP09.phx.gbl...
> a
>
|||Hi
Not much choice. Clustering is one of those technologies that you follow the
KB articles exactly otherwise the whole thing comes apart and you end up
re-installing everything.
Sorry.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ayad" <ashammout1@.hotmail.com> wrote in message
news:eEPiKeJsEHA.2904@.TK2MSFTNGP15.phx.gbl...
> Thanks Rod. But I don't want to un-install and re-install SQL to change
the[vbcol=seagreen]
> name.
> Ayad
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23X1xRMIsEHA.2732@.TK2MSFTNGP09.phx.gbl...
to
> a
>
|||I agree with Mike. The ONLY SUPPORTED way to rename a virtual SQL name is to uninstall and reinstall as documented in Microsoft Knowledge Base article
INF: How to Change a Clustered SQL Server Network Name
http://support.microsoft.com/?kbid=307336
This article has the following important NOTE
"Cluster Administrator provides a method to rename cluster resources. To do this, right-click the resource in Cluster Administrator and click Rename. However, if you
rename the SQL Server Network Name resource, a variety of situations could occur, the most critical being that the SQL Server virtual server instance may no longer come
online. "
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Rename Server - SQL 2005 RS

Windows 2003 R2, SQL 2005
I use Virtual Server images for my testing environments and so I need to be
able to take my master copy of an image and rename the server so that I can
put out on the network. When I do this, Reporting Services is unable to
connect to the database, which is no surprise really. I'm having trouble
finding info on how to fix this.
Anyone know what's involved with getting reporting services to work if you
rename the server?
--
thanks,
-JeffHave you tried using the Reporting Services Configuration Utility? This
tool lets' you point to the correct server.
--
Shaun Beane, MCT, MCDST, MCDBA
dbageek.blogspot.com
"Jefffff" <Jefffff@.discussions.microsoft.com> wrote in message
news:6E591CBD-F70B-4F50-B289-5EDF4BDB6D1B@.microsoft.com...
> Windows 2003 R2, SQL 2005
> I use Virtual Server images for my testing environments and so I need to
> be
> able to take my master copy of an image and rename the server so that I
> can
> put out on the network. When I do this, Reporting Services is unable to
> connect to the database, which is no surprise really. I'm having trouble
> finding info on how to fix this.
> Anyone know what's involved with getting reporting services to work if you
> rename the server?
> --
> thanks,
> -Jeff|||Many moons later, I've found some time to test this...
What I find is that after renaming the server, the Configuration Utility
doesn't work. When you launch it, you need to first connect to an instance
of Reporting Services, before you can configure anything, and after renaming
the server, you cannot connect to the Reporting Services instance on that
server, therefore you cannot configure anything.
Unless I'm missing something, it seems renaming your server means
reinstalling SRS.
I'm not sure how the utility connects to an SRS instance. If I had the time
to figure that out, maybe I could figure out a fix.
Anyone else out there dealing with this one?
--
thanks,
-Jeff
"Shaun Beane" wrote:
> Have you tried using the Reporting Services Configuration Utility? This
> tool lets' you point to the correct server.
> --
> Shaun Beane, MCT, MCDST, MCDBA
> dbageek.blogspot.com
> "Jefffff" <Jefffff@.discussions.microsoft.com> wrote in message
> news:6E591CBD-F70B-4F50-B289-5EDF4BDB6D1B@.microsoft.com...
> > Windows 2003 R2, SQL 2005
> >
> > I use Virtual Server images for my testing environments and so I need to
> > be
> > able to take my master copy of an image and rename the server so that I
> > can
> > put out on the network. When I do this, Reporting Services is unable to
> > connect to the database, which is no surprise really. I'm having trouble
> > finding info on how to fix this.
> >
> > Anyone know what's involved with getting reporting services to work if you
> > rename the server?
> >
> > --
> > thanks,
> > -Jeff
>
>

Rename Server

Dan|||http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx
.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to
> change the server name and IP address. Will there be any problem of acces
sing the existing
> database? Thanks.
>|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> http://www.karaszi.com/SQLServer/in...server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>

Rename Server

Dan
|||http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
> change the server name and IP address. Will there be any problem of accessing the existing
> database? Thanks.
>
|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> http://www.karaszi.com/SQLServer/inf...erver_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>

Rename Server

We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to change the server name and IP address. Will there be
any problem of accessing the existing database? Thanks.Diane,
You'll need to update the connection information to the SQL Server in your
code as well to reflect the name and IP change i.e., connection strings,
DSNs, etc...
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks very much for your prompt response. Exactly what and where do I need
to change in SQL code? I know how to change the DNS entry. Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Diane,
> You'll need to update the connection information to the SQL Server in your
> code as well to reflect the name and IP change i.e., connection strings,
> DSNs, etc...
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||DSNs, ADO/ADO.NET connection strings and any other connection sources for
you applications. Touch base with your developers prior to making this
change. Also, if possible test the applications in a testing environment
first before making the changes in the production environment. Consider
having a roll-back strategy in place "just in case" something doesn't go as
expected.
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks very much for your prompt response. Exactly what and where do I
> need to change in SQL code? I know how to change the DNS entry. Thanks.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> Diane,
>> You'll need to update the connection information to the SQL Server in
>> your code as well to reflect the name and IP change i.e., connection
>> strings, DSNs, etc...
>> HTH
>> Jerry
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>
After you rename the computer, open up Query Analyzer so that you can reset
the SQL Server's reference. It won't know who it is anymore. ;-)
--
USE master
GO
-- Drop the old computer name
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
BEGIN
SET NOCOUNT ON
exec sp_dropserver @.server = 'OldName'
END
-- Add the new computer name
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname ='NewName')
BEGIN
SET NOCOUNT ON
exec sp_addserver @.server = 'NewName', @.local = 'local'
END
Client applications that currently point to the old computer name/IP address
will need to be modified to point at the new computer name/ip address.
Rick Sawtell
MCT, MCSD, MCDBA|||http://support.microsoft.com/kb/303774/
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OaW%23LGtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> DSNs, ADO/ADO.NET connection strings and any other connection sources for
> you applications. Touch base with your developers prior to making this
> change. Also, if possible test the applications in a testing environment
> first before making the changes in the production environment. Consider
> having a roll-back strategy in place "just in case" something doesn't go
> as expected.
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
>> Thanks very much for your prompt response. Exactly what and where do I
>> need to change in SQL code? I know how to change the DNS entry. Thanks.
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> Diane,
>> You'll need to update the connection information to the SQL Server in
>> your code as well to reflect the name and IP change i.e., connection
>> strings, DSNs, etc...
>> HTH
>> Jerry
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>>
>>
>|||Thanks very much.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname => 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Thanks very much.
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:O32s4MtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> http://support.microsoft.com/kb/303774/
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
And to add to the other replies, if you have replication enabled on this
server or remote logins then you will find that you cannot use sp_dropserver
until you disable replication.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
I had to go through this myself 2 days ago :)
Dan|||http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
> change the server name and IP address. Will there be any problem of accessing the existing
> database? Thanks.
>|||Thank you very much for your input. I appreciate it very much.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OLUnfy1vFHA.2880@.TK2MSFTNGP12.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
> And to add to the other replies, if you have replication enabled on this
> server or remote logins then you will find that you cannot use
> sp_dropserver until you disable replication.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
> I had to go through this myself 2 days ago :)
> Dan
>|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||Hi Rick,
Your instructions work. I appreciated that you took the time to write me
the detail instructions. I have a question and I don't know if it is
related to renaming the server or not. I got this error message when I
typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'SMART_DATA'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Do you have any suggestions? Thanks.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname => 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Are you in the right database when running the command? Can you check if the object exists in
sysobjects? Also, who is the owner of the object?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Hi Rick,
> Your instructions work. I appreciated that you took the time to write me the detail instructions.
> I have a question and I don't know if it is related to renaming the server or not. I got this
> error message when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
> Do you have any suggestions? Thanks.
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
>> change the server name and IP address. Will there be any problem of accessing the existing
>> database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can reset the SQL Server's
>> reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP address will need to be
>> modified to point at the new computer name/ip address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||Thanks for your prompt response, Tibor.
Yes, I am in the right dabase when running the command. From Query
Analyzer, I selected Query, Change Database and point to Smart_Data dabase
and ran the command.
I see the object exists in sysobjects. I went to Tools, Object Search. I
saw Smart_Data under db_name for sysobjects. The owner of the object is
dbo.
Tibor, I think I made a BIG mistake by not following Rick's instructions.
Rick's instructions said to "USE master". But, I changed "master" to my
database name "smart_data". So, for every master, I changed to smart_data.
Maybe that was the reason that I got the error message when I typed the
command DBCC SHOWCONTIG. Do you know if I still can rerun Rick's
instructions by using the master table? Do you have any other suggestions?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
> Are you in the right database when running the command? Can you check if
> the object exists in sysobjects? Also, who is the owner of the object?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can
>> reset the SQL Server's reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP
>> address will need to be modified to point at the new computer name/ip
>> address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||You don't really have to be in master when executing sp_Dropserver and sp_addserver, so that should
not be the cause of your problem. I've never seen a case where SHOWCONTIG doesn't work if the table
exists and there isn't an object owner problem. I'm out of ideas, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:eoqJQEm0FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks for your prompt response, Tibor.
> Yes, I am in the right dabase when running the command. From Query Analyzer, I selected Query,
> Change Database and point to Smart_Data dabase and ran the command.
> I see the object exists in sysobjects. I went to Tools, Object Search. I saw Smart_Data under
> db_name for sysobjects. The owner of the object is dbo.
> Tibor, I think I made a BIG mistake by not following Rick's instructions. Rick's instructions said
> to "USE master". But, I changed "master" to my database name "smart_data". So, for every master,
> I changed to smart_data. Maybe that was the reason that I got the error message when I typed the
> command DBCC SHOWCONTIG. Do you know if I still can rerun Rick's instructions by using the master
> table? Do you have any other suggestions? Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
>> Are you in the right database when running the command? Can you check if the object exists in
>> sysobjects? Also, who is the owner of the object?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me the detail
>> instructions. I have a question and I don't know if it is related to renaming the server or not.
>> I got this error message when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
>> change the server name and IP address. Will there be any problem of accessing the existing
>> database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can reset the SQL Server's
>> reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP address will need to be
>> modified to point at the new computer name/ip address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Hi Rick,
> Your instructions work. I appreciated that you took the time to write me
> the detail instructions. I have a question and I don't know if it is
> related to renaming the server or not. I got this error message when I
> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Do you have any suggestions? Thanks.
In your other replies to this you have Smart_Data as the table name. Have
you tried
DBCC SHOWCONTIG (Smart_Data)
It's possible that you have your codepage set to a case-sensitive one, in
which case you must use the correct case for any object name.
Dan|||Thanks for your response, Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23USAzov0FHA.904@.tk2msftngp13.phx.gbl...
> You don't really have to be in master when executing sp_Dropserver and
> sp_addserver, so that should not be the cause of your problem. I've never
> seen a case where SHOWCONTIG doesn't work if the table exists and there
> isn't an object owner problem. I'm out of ideas, I'm afraid...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:eoqJQEm0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks for your prompt response, Tibor.
>> Yes, I am in the right dabase when running the command. From Query
>> Analyzer, I selected Query, Change Database and point to Smart_Data
>> dabase and ran the command.
>> I see the object exists in sysobjects. I went to Tools, Object Search.
>> I saw Smart_Data under db_name for sysobjects. The owner of the object
>> is dbo.
>> Tibor, I think I made a BIG mistake by not following Rick's instructions.
>> Rick's instructions said to "USE master". But, I changed "master" to my
>> database name "smart_data". So, for every master, I changed to
>> smart_data. Maybe that was the reason that I got the error message when I
>> typed the command DBCC SHOWCONTIG. Do you know if I still can rerun
>> Rick's instructions by using the master table? Do you have any other
>> suggestions? Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
>> Are you in the right database when running the command? Can you check if
>> the object exists in sysobjects? Also, who is the owner of the object?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database?
>> Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can
>> reset the SQL Server's reference. It won't know who it is anymore.
>> ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP
>> address will need to be modified to point at the new computer name/ip
>> address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>|||Thanks for your suggestions, Dan.
I have tried Smart_Data, SMART_DATA, smart_data. The database name under
Enterprise Manager is SMART_DATA. Do you have any other suggestions?
Thanks.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
> In your other replies to this you have Smart_Data as the table name. Have
> you tried
> DBCC SHOWCONTIG (Smart_Data)
> It's possible that you have your codepage set to a case-sensitive one, in
> which case you must use the correct case for any object name.
> Dan
>|||> The database name under
> Enterprise Manager is SMART_DATA.
Database name? Earlier you said that the name of the table is SMART_DATA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
> Thanks for your suggestions, Dan.
> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
> Thanks.
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name. Have
>> you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one, in
>> which case you must use the correct case for any object name.
>> Dan
>|||Tibor,
You answered my question!!! It was my mistake. I used the database name
instead of the table name. By using the table name with DBCC SHOWCONTIG, I
now have statistics.
Thanks very much for asking the questions. I sincerely apologize for my
errors.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evqdRQz0FHA.2964@.TK2MSFTNGP10.phx.gbl...
>> The database name under Enterprise Manager is SMART_DATA.
> Database name? Earlier you said that the name of the table is SMART_DATA.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
>> Thanks for your suggestions, Dan.
>> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
>> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
>> Thanks.
>> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
>> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it
>> is related to renaming the server or not. I got this error message
>> when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name.
>> Have you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one,
>> in which case you must use the correct case for any object name.
>> Dan
>>|||> Thanks very much for asking the questions. I sincerely apologize for my
> errors.
No need to apologize. I'm glad you sorted it out. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:ueCbj2z0FHA.3524@.tk2msftngp13.phx.gbl...
> Tibor,
> You answered my question!!! It was my mistake. I used the database name
> instead of the table name. By using the table name with DBCC SHOWCONTIG, I
> now have statistics.
> Thanks very much for asking the questions. I sincerely apologize for my
> errors.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:evqdRQz0FHA.2964@.TK2MSFTNGP10.phx.gbl...
>> The database name under Enterprise Manager is SMART_DATA.
>> Database name? Earlier you said that the name of the table is SMART_DATA.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
>> Thanks for your suggestions, Dan.
>> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
>> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
>> Thanks.
>> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
>> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it
>> is related to renaming the server or not. I got this error message
>> when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name.
>> Have you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one,
>> in which case you must use the correct case for any object name.
>> Dan
>>
>

Wednesday, March 21, 2012

Rename and Stored procedure

Hi All,
I have been working on the following problem for more than two w

s
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 w

s.
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 w

s
> 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 w

s.
> 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

Friday, March 30, 2012

Renaming Cluster Nodes

Renaming and migration Problems

Renaming and migration Problems

Renaming and migration Problems

Renaming a server

Monday, March 26, 2012

Rename SQL Server Virtual name in 2003 Cluster

Rename Server - SQL 2005 RS

Rename Server

Rename Server

Rename Server

Wednesday, March 21, 2012

Rename and Stored procedure

Removing a server

Blog Archive

About Me