We have several 2005 boxes that have been renamed. RS works fine from a
browser and I can deplopy reports to it and see it with the config manager.
What I can't do is reference it from Management Studio. On the one box that
I restarted after sp_drop and sp_add, a message box pops up referring to the
old name. on the other box, niether the old name no the new name are found.
Where does the Studio get these names?For anyone who cares, I had to chnge the rsreportserver.config file. It was
referencing the old server names in the ulrroot section. This also impacted
the SAC tool's ability to manage the rs instance.
"Jeff Ericson" wrote:
> We have several 2005 boxes that have been renamed. RS works fine from a
> browser and I can deplopy reports to it and see it with the config manager.
> What I can't do is reference it from Management Studio. On the one box that
> I restarted after sp_drop and sp_add, a message box pops up referring to the
> old name. on the other box, niether the old name no the new name are found.
> Where does the Studio get these names?
Showing posts with label manager. Show all posts
Showing posts with label manager. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Renamed RS Server; unable to access site
Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
Please provide any information that will resolve this problem. Thanks in advance,
KevinIn your Report Manager directory, look for a RSWebApplication.config file. Open that baby up and look for something like:
<ReportServerUrl>http://Youroldservername/ReportServer</ReportServerUrl>
You might spot your old server name there. I've been playing with that file today, which sparked the idea. Not a clue if it works or not!
Hope it helps!
- T
"Kevin" wrote:
> Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
> Please provide any information that will resolve this problem. Thanks in advance,
> Kevin
>|||You need to update the server URL in the file rswebapplication.config
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:09D00359-881C-41F5-9D16-BE40D5A4023A@.microsoft.com...
> Good evening. I just renamed the server that hosts both SQL Server and
Reporting Services. I am now unable to access the report manager website
(http://localhost/reports). I receive an error stating "The underlying
connection was closed: The remote name could not be resolved." As suggested
by previous posts, I ran the rsconfig.exe utility specifying the new server
name along with the security information. I am now able to access the
report server (http://localhost/reportserver) and I can successfully run
reports. I am still receiving the same error when attempting to access the
report manager website, however. I have searched the registry and replaced
the only instance of the old server name in a key pertaining to Reporting
Services and rebooted, but I still have the same problem.
> Please provide any information that will resolve this problem. Thanks in
advance,
> Kevin
>|||That did the trick. Thanks for all of your help!
Kevin
"Brian Hartman [MSFT]" wrote:
> You need to update the server URL in the file rswebapplication.config
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:09D00359-881C-41F5-9D16-BE40D5A4023A@.microsoft.com...
> > Good evening. I just renamed the server that hosts both SQL Server and
> Reporting Services. I am now unable to access the report manager website
> (http://localhost/reports). I receive an error stating "The underlying
> connection was closed: The remote name could not be resolved." As suggested
> by previous posts, I ran the rsconfig.exe utility specifying the new server
> name along with the security information. I am now able to access the
> report server (http://localhost/reportserver) and I can successfully run
> reports. I am still receiving the same error when attempting to access the
> report manager website, however. I have searched the registry and replaced
> the only instance of the old server name in a key pertaining to Reporting
> Services and rebooted, but I still have the same problem.
> >
> > Please provide any information that will resolve this problem. Thanks in
> advance,
> > Kevin
> >
>
>|||We completed an installation of SQL Server 2000 Reporting Services and
upon trying to visit the server to publish or view the normal menu we
get only the following error:
-2147467259 (0x80004005)
The local user on the IIS server has access to the Report Database , I
can create a UDL and get a Connection Successful from the IIS box. But
I can't seem to get my normal startup page from any PC.
Thanks,
Teros wrote On 7/27/2004 4:25 PM:
> In your Report Manager directory, look for a RSWebApplication.config file. Open that baby up and look for something like:
> <ReportServerUrl>http://Youroldservername/ReportServer</ReportServerUrl>
> You might spot your old server name there. I've been playing with that file today, which sparked the idea. Not a clue if it works or not!
> Hope it helps!
> - T
>
> "Kevin" wrote:
>
>>Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
>>Please provide any information that will resolve this problem. Thanks in advance,
>>Kevin
Please provide any information that will resolve this problem. Thanks in advance,
KevinIn your Report Manager directory, look for a RSWebApplication.config file. Open that baby up and look for something like:
<ReportServerUrl>http://Youroldservername/ReportServer</ReportServerUrl>
You might spot your old server name there. I've been playing with that file today, which sparked the idea. Not a clue if it works or not!
Hope it helps!
- T
"Kevin" wrote:
> Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
> Please provide any information that will resolve this problem. Thanks in advance,
> Kevin
>|||You need to update the server URL in the file rswebapplication.config
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:09D00359-881C-41F5-9D16-BE40D5A4023A@.microsoft.com...
> Good evening. I just renamed the server that hosts both SQL Server and
Reporting Services. I am now unable to access the report manager website
(http://localhost/reports). I receive an error stating "The underlying
connection was closed: The remote name could not be resolved." As suggested
by previous posts, I ran the rsconfig.exe utility specifying the new server
name along with the security information. I am now able to access the
report server (http://localhost/reportserver) and I can successfully run
reports. I am still receiving the same error when attempting to access the
report manager website, however. I have searched the registry and replaced
the only instance of the old server name in a key pertaining to Reporting
Services and rebooted, but I still have the same problem.
> Please provide any information that will resolve this problem. Thanks in
advance,
> Kevin
>|||That did the trick. Thanks for all of your help!
Kevin
"Brian Hartman [MSFT]" wrote:
> You need to update the server URL in the file rswebapplication.config
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:09D00359-881C-41F5-9D16-BE40D5A4023A@.microsoft.com...
> > Good evening. I just renamed the server that hosts both SQL Server and
> Reporting Services. I am now unable to access the report manager website
> (http://localhost/reports). I receive an error stating "The underlying
> connection was closed: The remote name could not be resolved." As suggested
> by previous posts, I ran the rsconfig.exe utility specifying the new server
> name along with the security information. I am now able to access the
> report server (http://localhost/reportserver) and I can successfully run
> reports. I am still receiving the same error when attempting to access the
> report manager website, however. I have searched the registry and replaced
> the only instance of the old server name in a key pertaining to Reporting
> Services and rebooted, but I still have the same problem.
> >
> > Please provide any information that will resolve this problem. Thanks in
> advance,
> > Kevin
> >
>
>|||We completed an installation of SQL Server 2000 Reporting Services and
upon trying to visit the server to publish or view the normal menu we
get only the following error:
-2147467259 (0x80004005)
The local user on the IIS server has access to the Report Database , I
can create a UDL and get a Connection Successful from the IIS box. But
I can't seem to get my normal startup page from any PC.
Thanks,
Teros wrote On 7/27/2004 4:25 PM:
> In your Report Manager directory, look for a RSWebApplication.config file. Open that baby up and look for something like:
> <ReportServerUrl>http://Youroldservername/ReportServer</ReportServerUrl>
> You might spot your old server name there. I've been playing with that file today, which sparked the idea. Not a clue if it works or not!
> Hope it helps!
> - T
>
> "Kevin" wrote:
>
>>Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
>>Please provide any information that will resolve this problem. Thanks in advance,
>>Kevin
Monday, March 26, 2012
Rename server names in Enterprise Manager?
Hi there.
Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")
Can someone give me a hint, please?
Thanx.Not mentioned the version of SQL server used>
If its SQL 2K then you can use SP_DROPSERVER & SP_ADDSERVER which will take affect at the restart of SQL services.
If its version 7 then you've to run the setup again to take new changes affected.
Refer to books online about Renaming Servers topic.|||Originally posted by LuckyLights
Hi there.
Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")
Can someone give me a hint, please?
Thanx. It sounds to me like you want to register a server, but have a completely different name show up on the screen in the Server collection. IF I understood what you wanted correctly, according to How to register a server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_7jua.asp), that can't be done.
-PatP
Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")
Can someone give me a hint, please?
Thanx.Not mentioned the version of SQL server used>
If its SQL 2K then you can use SP_DROPSERVER & SP_ADDSERVER which will take affect at the restart of SQL services.
If its version 7 then you've to run the setup again to take new changes affected.
Refer to books online about Renaming Servers topic.|||Originally posted by LuckyLights
Hi there.
Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")
Can someone give me a hint, please?
Thanx. It sounds to me like you want to register a server, but have a completely different name show up on the screen in the Server collection. IF I understood what you wanted correctly, according to How to register a server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_7jua.asp), that can't be done.
-PatP
Friday, March 23, 2012
rename login (Possible OT)
I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
Thanks
Don't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables[vbcol=seagreen]
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the
>
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
Thanks
Don't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables[vbcol=seagreen]
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the
>
rename login (Possible OT)
I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> > I need to rename a login that owns some tables. I cannot do this via
the
> > enterprise manager? Can I do it via SQL scripting?
> >
> > Please advise (also if there is a better NG to post this to)
> >
> > Thanks
> >
> >
>
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> > I need to rename a login that owns some tables. I cannot do this via
the
> > enterprise manager? Can I do it via SQL scripting?
> >
> > Please advise (also if there is a better NG to post this to)
> >
> > Thanks
> >
> >
>
rename login (Possible OT)
I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>
Rename database
Hi,
Is there any easy way to rename a database?
It can be done using SQL script or just
Enterprise Manager.
I'm using SQLServer 2000
Regards
Miroomust use T-SQL script, two means:
1.sp_renamedb dbold, dbnew
2.ALTER DATABASE dbold MODIFY NAME=dbnew
"Miroo_news" <miroo@.USUNTO.poczta.fm> wrote in message
news:bm09qr$78l$1@.atlantis.news.tpi.pl...
> Hi,
> Is there any easy way to rename a database?
> It can be done using SQL script or just
> Enterprise Manager.
> I'm using SQLServer 2000
> Regards
> Miroo|||U¿ytkownik "Henry" <qiuliang@.163.net> napisa³ w wiadomo¶ci
news:OyD5KTWjDHA.684@.TK2MSFTNGP09.phx.gbl...
> must use T-SQL script, two means:
> 2.ALTER DATABASE dbold MODIFY NAME=dbnew
Thank you, it works OK.
Regards,
Miroo
Is there any easy way to rename a database?
It can be done using SQL script or just
Enterprise Manager.
I'm using SQLServer 2000
Regards
Miroomust use T-SQL script, two means:
1.sp_renamedb dbold, dbnew
2.ALTER DATABASE dbold MODIFY NAME=dbnew
"Miroo_news" <miroo@.USUNTO.poczta.fm> wrote in message
news:bm09qr$78l$1@.atlantis.news.tpi.pl...
> Hi,
> Is there any easy way to rename a database?
> It can be done using SQL script or just
> Enterprise Manager.
> I'm using SQLServer 2000
> Regards
> Miroo|||U¿ytkownik "Henry" <qiuliang@.163.net> napisa³ w wiadomo¶ci
news:OyD5KTWjDHA.684@.TK2MSFTNGP09.phx.gbl...
> must use T-SQL script, two means:
> 2.ALTER DATABASE dbold MODIFY NAME=dbnew
Thank you, it works OK.
Regards,
Miroo
Wednesday, March 21, 2012
Rename an AS Database?
Is there a way to rename an Analysis Services database? Can I do it through
the Analysis Manager interface?
Thanks!
Unfortunately, No. You need to do a copy and then a paste. When the paste
runs, it notices that you already have a database with that name and will
prompt for a new name. You enter a new name; then reprocess the database (an
option on the right-click menu). Then you can go back and delete the old
database.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:45D8C536-72F1-47BE-8BFA-9915DE6EE107@.microsoft.com...
> Is there a way to rename an Analysis Services database? Can I do it
through
> the Analysis Manager interface?
> Thanks!
sql
the Analysis Manager interface?
Thanks!
Unfortunately, No. You need to do a copy and then a paste. When the paste
runs, it notices that you already have a database with that name and will
prompt for a new name. You enter a new name; then reprocess the database (an
option on the right-click menu). Then you can go back and delete the old
database.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:45D8C536-72F1-47BE-8BFA-9915DE6EE107@.microsoft.com...
> Is there a way to rename an Analysis Services database? Can I do it
through
> the Analysis Manager interface?
> Thanks!
sql
Rename an AS Database?
Is there a way to rename an Analysis Services database? Can I do it through
the Analysis Manager interface?
Thanks!Unfortunately, No. You need to do a copy and then a paste. When the paste
runs, it notices that you already have a database with that name and will
prompt for a new name. You enter a new name; then reprocess the database (an
option on the right-click menu). Then you can go back and delete the old
database.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:45D8C536-72F1-47BE-8BFA-9915DE6EE107@.microsoft.com...
> Is there a way to rename an Analysis Services database? Can I do it
through
> the Analysis Manager interface?
> Thanks!
the Analysis Manager interface?
Thanks!Unfortunately, No. You need to do a copy and then a paste. When the paste
runs, it notices that you already have a database with that name and will
prompt for a new name. You enter a new name; then reprocess the database (an
option on the right-click menu). Then you can go back and delete the old
database.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:45D8C536-72F1-47BE-8BFA-9915DE6EE107@.microsoft.com...
> Is there a way to rename an Analysis Services database? Can I do it
through
> the Analysis Manager interface?
> Thanks!
Monday, March 12, 2012
Removing SQL Express instances
Using SQL Express, UNDER COMPUTER management, SQL Server Configuraqtion Manager, SQL Server 2005 Network configuration Ihave the following listed instances
CSOLUTION - not listed under services
MSSQLSERVER - Disabled
SQLEXPRESS - Diaabled
SQLTEST - Disabled
SQLEXPRESSDATA - used by VB05 program
Questions
a) how do I delete all the instancces I do not use. In MSDE, each instance could be removed via the add/remove programs, but I don't see how to remove it under express
b) since SQLEXPRESS is the default for an SQL Express installation, I would like for the VBB05 program to use that instance; how do I do that?
Any help would be appreciated.
Bob
a) When you go to Add/Remove Programs, select the SQL 2005 entry and click on Remove. This will pop up a box showing the instances you have on your machine. You can select one from here and continue with the uninstall to remove it.
b) When you create your DB connection in your VB app, you'll need to supply a DB name to connect to. Here, use <MACHINENAME>/SQLEXPRESS and it will connect to that instance. You'd do the same thing to connect to any other named instance.
Thanks,
Sam Lester (MSFT)
Removing SQL Express instances
Using SQL Express, UNDER COMPUTER management, SQL Server Configuraqtion Manager, SQL Server 2005 Network configuration Ihave the following listed instances
CSOLUTION - not listed under services
MSSQLSERVER - Disabled
SQLEXPRESS - Diaabled
SQLTEST - Disabled
SQLEXPRESSDATA - used by VB05 program
Questions
a) how do I delete all the instancces I do not use. In MSDE, each instance could be removed via the add/remove programs, but I don't see how to remove it under express
b) since SQLEXPRESS is the default for an SQL Express installation, I would like for the VBB05 program to use that instance; how do I do that?
Any help would be appreciated.
Bob
a) When you go to Add/Remove Programs, select the SQL 2005 entry and click on Remove. This will pop up a box showing the instances you have on your machine. You can select one from here and continue with the uninstall to remove it.
b) When you create your DB connection in your VB app, you'll need to supply a DB name to connect to. Here, use <MACHINENAME>/SQLEXPRESS and it will connect to that instance. You'd do the same thing to connect to any other named instance.
Thanks,
Sam Lester (MSFT)
removing replication table
Hi all,
Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.
Any idea?
Thanks!
Hi Alan,
In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.
This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||
Hello Mahesh,
Background:
I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.
Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.
Before opening up a case do you have any recommendations? (or anyone else for that matter)
Thank you in advance.
|||Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.
update sysdatabases
set category = 0
where name = <your database name>
|||A better way is to try:
If using Merge replication:
exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'
If Tran replication:
exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'
See if that serves the purpose.
|||Mahesh,
I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin
|||Sachin,
Thank you. What I did was the following:
1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.
2. I attempted to run your script above but I did not have ad hoc updates on.
3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.
4. Then I created a drop database query and ran that.
5. That dropped the table from the system just fine!
Thank you.
|||Don,
I am glad I could help. :)
|||Sachin,
Me too. Step 5 should have been "dropped the database" instead of table.
Best Regards~
Don
removing replication table
Hi all,
Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.
Any idea?
Thanks!
Hi Alan,
In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.
This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||
Hello Mahesh,
Background:
I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.
Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.
Before opening up a case do you have any recommendations? (or anyone else for that matter)
Thank you in advance.
|||Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.
update sysdatabases
set category = 0
where name = <your database name>
|||A better way is to try:
If using Merge replication:
exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'
If Tran replication:
exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'
See if that serves the purpose.
|||Mahesh,
I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin
|||Sachin,
Thank you. What I did was the following:
1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.
2. I attempted to run your script above but I did not have ad hoc updates on.
3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.
4. Then I created a drop database query and ran that.
5. That dropped the table from the system just fine!
Thank you.
|||Don,
I am glad I could help. :)
|||Sachin,
Me too. Step 5 should have been "dropped the database" instead of table.
Best Regards~
Don
Wednesday, March 7, 2012
Removing Identity property SQL2K5
What is the fastest way to remove identity property of a column.?
I traced Enterprise Manager way of doing, it creates a replicate of the
table and copies all rows and constratints to it and deletes old table and
renames the new table with the original column.This is taking long time
Please suggest.
thxThat's probably pretty much it. i doubt SQL Server can do it any other way.
Anything else would involve the same thing, but in different ways e.g.
DTSing the data into a duplicate table, dropping the old table and renaming
the other one.
"skg" <skg@.yahoo.com> wrote in message
news:%23RcwXTSRGHA.424@.TK2MSFTNGP12.phx.gbl...
> What is the fastest way to remove identity property of a column.?
> I traced Enterprise Manager way of doing, it creates a replicate of the
> table and copies all rows and constratints to it and deletes old table and
> renames the new table with the original column.This is taking long time
> Please suggest.
> thx
>|||I guess you could add another column, copy the data over using an UPDATE sta
tement, drop the old
column and rename the column. But the columns would no be in the same order
as they were originally.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ian Boyd" <admin@.SWIFTPA.NET> wrote in message news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gb
l...
> That's probably pretty much it. i doubt SQL Server can do it any other way
.
> Anything else would involve the same thing, but in different ways e.g.
> DTSing the data into a duplicate table, dropping the old table and renamin
g the other one.
> "skg" <skg@.yahoo.com> wrote in message news:%23RcwXTSRGHA.424@.TK2MSFTNGP12
.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTn$x4fRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I guess you could add another column, copy the data over using an UPDATE
>statement, drop the old column and rename the column. But the columns would
>no be in the same order as they were originally.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ian Boyd" <admin@.SWIFTPA.NET> wrote in message
> news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gbl...
>
I traced Enterprise Manager way of doing, it creates a replicate of the
table and copies all rows and constratints to it and deletes old table and
renames the new table with the original column.This is taking long time
Please suggest.
thxThat's probably pretty much it. i doubt SQL Server can do it any other way.
Anything else would involve the same thing, but in different ways e.g.
DTSing the data into a duplicate table, dropping the old table and renaming
the other one.
"skg" <skg@.yahoo.com> wrote in message
news:%23RcwXTSRGHA.424@.TK2MSFTNGP12.phx.gbl...
> What is the fastest way to remove identity property of a column.?
> I traced Enterprise Manager way of doing, it creates a replicate of the
> table and copies all rows and constratints to it and deletes old table and
> renames the new table with the original column.This is taking long time
> Please suggest.
> thx
>|||I guess you could add another column, copy the data over using an UPDATE sta
tement, drop the old
column and rename the column. But the columns would no be in the same order
as they were originally.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ian Boyd" <admin@.SWIFTPA.NET> wrote in message news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gb
l...
> That's probably pretty much it. i doubt SQL Server can do it any other way
.
> Anything else would involve the same thing, but in different ways e.g.
> DTSing the data into a duplicate table, dropping the old table and renamin
g the other one.
> "skg" <skg@.yahoo.com> wrote in message news:%23RcwXTSRGHA.424@.TK2MSFTNGP12
.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTn$x4fRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I guess you could add another column, copy the data over using an UPDATE
>statement, drop the old column and rename the column. But the columns would
>no be in the same order as they were originally.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ian Boyd" <admin@.SWIFTPA.NET> wrote in message
> news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gbl...
>
Removing identity column
We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?
The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?
Open up the Northwind DB an select identity NO for the OrderID
The script that Enterprise Manager creates is this (Click on the 3rd Icon from the left, left side of the scissors)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM dbo.Orders TABLOCKX')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
GRANT REFERENCES ON dbo.Orders TO public AS dbo
GRANT SELECT ON dbo.Orders TO public AS dbo
GRANT UPDATE ON dbo.Orders TO public AS dbo
GRANT INSERT ON dbo.Orders TO public AS dbo
GRANT DELETE ON dbo.Orders TO public AS dbo
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
so as you can see there is a lot that has to be done behind the scenes
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Monday, February 20, 2012
Removing a SQL server backup
In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.
r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.
r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka
Removing a SQL server backup
In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka
Removing a SQL server backup
In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka
Subscribe to:
Posts (Atom)