changing column type

hi,
i just want the change the type of a column in a table within sql server
2000 enterprise manager. however it warns that,
'MyTable' table
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
- Warning: The table was created with ANSI_NULLS 'off' and will be
re-created with ANSI_NULLS 'on'.
i don't want to get those options changed when i change the type of the
column and this is not the only database on the server that we cannot stop
the server to change the configuration options(ANSI_PADDING, ANSI_NULLS) of
the sql server. it seems that we have to use sp_dboption stored procedure to
change those options for the current database. my main intention is not to
deal with query analyzer to execute sql statements, only get the job done
through enterprise manager. isn't it possible through enterprise manager to
change the database options only for the current database? i looked through
the menus may be i missed.
thanksEM goes through a much more complex CREATE/INSERT/DROP table routine =when changing data types. Have you tried changing the column via Query =Analyzer?
I have not tried it...but I am guessing that it might work better via =Query Analyzer
ALTER TABLE x ALTER COLUMN y DesiredDataTypeGoesHere
You can always try to make the change via Enterprise Manager, but make =sure that you script out the changes instead of having it apply them for =you. You should then be able to modify the code as you wish.
-- Keith
"Richard" <rich@.thetop.com> wrote in message =news:eBhBgH4nDHA.2304@.TK2MSFTNGP11.phx.gbl...
> hi,
> i just want the change the type of a column in a table within sql =server
> 2000 enterprise manager. however it warns that,
> > 'MyTable' table
> - Warning: One or more existing columns have ANSI_PADDING 'off' and =will be
> re-created with ANSI_PADDING 'on'.
> - Warning: The table was created with ANSI_NULLS 'off' and will be
> re-created with ANSI_NULLS 'on'.
> > i don't want to get those options changed when i change the type of =the
> column and this is not the only database on the server that we cannot =stop
> the server to change the configuration options(ANSI_PADDING, =ANSI_NULLS) of
> the sql server. it seems that we have to use sp_dboption stored =procedure to
> change those options for the current database. my main intention is =not to
> deal with query analyzer to execute sql statements, only get the job =done
> through enterprise manager. isn't it possible through enterprise =manager to
> change the database options only for the current database? i looked =through
> the menus may be i missed.
> thanks
> >

Changing column size in existing databese?

I must increase column (filed) size in existing datebase but without using
Enterprise manager...(Becouse we use MSDE on our clients PCs)
The Filed is part of primary and foreign key constraints...
And every constraint has diferent index number in each database...
for example (PK_something_9e382hjl8), and I don't know how to pick this
value before "drop constraint" command....

Thank you very much...[posted and mailed, please reply in news]

Poted (dario1975@.post.hnet.hr) writes:
> I must increase column (filed) size in existing datebase but without
> using Enterprise manager...(Becouse we use MSDE on our clients PCs) The
> Filed is part of primary and foreign key constraints... And every
> constraint has diferent index number in each database... for example
> (PK_something_9e382hjl8), and I don't know how to pick this value before
> "drop constraint" command....

Moral: name your constraint explicitly according to some standardized
scheme, so you easily can find the names.

This query gives you the name of the PK for a table:

select name from sysobjects
where xtype = 'PK'
and parent_obj = object_id('tablename')

This query lists all referencing foreign key for a table:

select object_name(constid), object_name(fkeyid), col_name(fkeyid, fkey)
from sysforeignkeys
where rkeyid = object_id('depots')
order by constid, fkey

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?
Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
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/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?
Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
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/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
--
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/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:
>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:
>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
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/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in
a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.
Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening por
t
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out wi
th
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening port
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

Change the Report Manager Interface

Hello,
I was hoping to modify the default interface for the Report Manager to feel
more like our other online reporting pages. I found a few articles about
creating asp.net web applications and all that, but was hoping just to change
the color scheme, place an image somewhere, etc...
is this possible? if so, how easy is it and are there any good resources out
there?
Thanks!
--
Ben Sullins
Our Vacation Store
http://ovstravelfolio.com/Hi Ben,
You can edit the stylesheet that's located
<ReportManagerRoot>/Styles/ReportingServices.css (with a default
installation the path should be "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting
Services\ReportManager\Styles\ReportingServices.css") to change the
color-scheme, fonts, etc...
Editing the physical layout of the portal isn't that easy and not very
practical. You would most likely need to replace the Pages.aspx page and
override the Pages class where you would inject / replace the layout
changes. You can however replace the default images and icons that are
already present in the layout. These are located in
<ReportManagerRoot>/images.
Hope this was of some help.
-Christian
Ben Sullins wrote:
> Hello,
> I was hoping to modify the default interface for the Report Manager to feel
> more like our other online reporting pages. I found a few articles about
> creating asp.net web applications and all that, but was hoping just to change
> the color scheme, place an image somewhere, etc...
> is this possible? if so, how easy is it and are there any good resources out
> there?
> Thanks!
> --
> Ben Sullins
> Our Vacation Store
> http://ovstravelfolio.com/

change the look of Report Manager

I would like to change the look of the Report Manager. I have Reporting
Services on both developement and production servers. I would like to
be able to change the color or font etc. so that I can tell without
having to look in the address box to figure out what server I am on.
Is there a value I can change somewhere that will do this for me?Hi Lou,
you could just edit the style sheet
\Reporting Services\ReportManager\Styles\ReportingServices.css
regards
Matt
"Lou" <lbonnes@.yahoo.com> wrote in message
news:1105847715.370619.296730@.f14g2000cwb.googlegroups.com...
> I would like to change the look of the Report Manager. I have Reporting
> Services on both developement and production servers. I would like to
> be able to change the color or font etc. so that I can tell without
> having to look in the address box to figure out what server I am on.
> Is there a value I can change somewhere that will do this for me?
>

Change the File Name in File connection Manager

Hi,

How can I dynamically change the file name in File connection Manager in SSIS package?

I can do this in SQL 2000 but how to achieve the same thing in SQL 2005. I have to generate 10 different excel file and just need to change the file name in connection manager for excel file

Thanks

Shafiq

You'll need to put an expression on the ConnectionString property of the flat file connection manager. Step 3 in here demos this: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

In fact, that whole post maps pretty well to what you seem to want to do.

-Jamie

|||

Jamie,

Your article is good, but some how the expression tab of For Each loop does not have connectionstring property in the dropdown list.

I am using Excel Connection Manager for my files.

Please Guide

|||

ConnectionString is a property of the Flat File Connection Manager. Not of the ForEach loop.

-Jamie

change startup options in SQL 2005

It seems that the only way to change startup option is to use the
Configuration Manager and that too on the server you wish to change directly
I need to change some startup options by adding a trace flag and do not want
to log on remotely on every box to make the change . The Configuration
Manager does not seem to allow me to connect to a remote computer.
Any way I could do this change without logging on every box ?
Thanks
You can connect to Configuration Manager on remote computers
using Computer Management. Connect to the remote server
using Computer Management, go to Services and Applications
and then go to SQL Server 2005 Configuration Manager.
-Sue
On Fri, 3 Aug 2007 10:41:28 -0700, "Hassan"
<hassan@.hotmail.com> wrote:

>It seems that the only way to change startup option is to use the
>Configuration Manager and that too on the server you wish to change directly
>I need to change some startup options by adding a trace flag and do not want
>to log on remotely on every box to make the change . The Configuration
>Manager does not seem to allow me to connect to a remote computer.
>Any way I could do this change without logging on every box ?
>Thanks
>

change startup options in SQL 2005

It seems that the only way to change startup option is to use the
Configuration Manager and that too on the server you wish to change directly
I need to change some startup options by adding a trace flag and do not want
to log on remotely on every box to make the change . The Configuration
Manager does not seem to allow me to connect to a remote computer.
Any way I could do this change without logging on every box ?
ThanksYou can connect to Configuration Manager on remote computers
using Computer Management. Connect to the remote server
using Computer Management, go to Services and Applications
and then go to SQL Server 2005 Configuration Manager.
-Sue
On Fri, 3 Aug 2007 10:41:28 -0700, "Hassan"
<hassan@.hotmail.com> wrote:
>It seems that the only way to change startup option is to use the
>Configuration Manager and that too on the server you wish to change directly
>I need to change some startup options by adding a trace flag and do not want
>to log on remotely on every box to make the change . The Configuration
>Manager does not seem to allow me to connect to a remote computer.
>Any way I could do this change without logging on every box ?
>Thanks
>

change startup options in SQL 2005

It seems that the only way to change startup option is to use the
Configuration Manager and that too on the server you wish to change directly
I need to change some startup options by adding a trace flag and do not want
to log on remotely on every box to make the change . The Configuration
Manager does not seem to allow me to connect to a remote computer.
Any way I could do this change without logging on every box ?
ThanksYou can connect to Configuration Manager on remote computers
using Computer Management. Connect to the remote server
using Computer Management, go to Services and Applications
and then go to SQL Server 2005 Configuration Manager.
-Sue
On Fri, 3 Aug 2007 10:41:28 -0700, "Hassan"
<hassan@.hotmail.com> wrote:

>It seems that the only way to change startup option is to use the
>Configuration Manager and that too on the server you wish to change directl
y
>I need to change some startup options by adding a trace flag and do not wan
t
>to log on remotely on every box to make the change . The Configuration
>Manager does not seem to allow me to connect to a remote computer.
>Any way I could do this change without logging on every box ?
>Thanks
>

Change SQL Server Connection from Enterprise Manager

How can we change the login userid by SQL Server Enterprise Manager. I
login with my Windows userid but the last time I used a SQL Server account it
still uses that account when start SQL Server Enterprise Manager.
How do I change Enterpise Manager to use my windows userid.
Thanks,
Hi Joe,
In Enterprise Manager, right click on the registered server
that you want to change the login for. Select Edit SQL
Server Registration Properties. You can change the login
information from there.
-Sue
On Wed, 26 Jan 2005 13:03:09 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>How can we change the login userid by SQL Server Enterprise Manager. I
>login with my Windows userid but the last time I used a SQL Server account it
>still uses that account when start SQL Server Enterprise Manager.
>How do I change Enterpise Manager to use my windows userid.
>Thanks,

Change SQL Instance name to default(local) instrance

I found some information, but, it didn't work.
Now, i open the enterprise manager, and My SQL Server name is
DENNIS\DENNISDB(windows NT), but i want to change this name to default
name(local), when i installed sql, i changed the instance name, i want
default name.
I used this method.
EXEC sp_dropserver 'DENNISDB'
go
EXEC sp_addserver 'DENNIS', 'local'
go
and i restart SQL Server Service and,
SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
result : DENNIS\DENNIDB
select @.@.SERVERNAME
result : DENNIS
But, stil Instance name On My Enterprize Manager is DENNIS\DENNISDB, I want
"(local)" instance
Help me plase..Hi
As far as I know you cannot change/switch a default instance with a named
one. Re-install SQL Server only.
"DennisLee" <ddongdog@.haja.or.kr.korea> wrote in message
news:ONofqOsMGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I found some information, but, it didn't work.
> Now, i open the enterprise manager, and My SQL Server name is
> DENNIS\DENNISDB(windows NT), but i want to change this name to default
> name(local), when i installed sql, i changed the instance name, i want
> default name.
> I used this method.
>
> EXEC sp_dropserver 'DENNISDB'
> go
> EXEC sp_addserver 'DENNIS', 'local'
> go
> and i restart SQL Server Service and,
> SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
> result : DENNIS\DENNIDB
> select @.@.SERVERNAME
> result : DENNIS
> But, stil Instance name On My Enterprize Manager is DENNIS\DENNISDB, I
> want
> "(local)" instance
> Help me plase..
>
>

change reporting services location (2000)

hi,
our reporting services DB moved and I need to tell the report manager where to find it
isn't it in a config file somewhere, i can't find it?
do i have to reinstall?RSConfig -c -s Server name -d Reporting Services database name -a Sql|Windows -u User name -p User password

nevermind guys|||hrmm it still wont work, says it cant find the db

ugh...|||ok im talking to myself...

now a different microsoft sites says i cannot specify a named instance with this utility... but it let me install it like that (go figure)

now what, i guess i have to reinstall|||hrmm it still wont work, says it cant find the db

ugh...
See the Report Server database can be installed on only one instance of SQL Server per physical database sever computer. The database need not reside on the local Report Server computer but the server must be a member of the Windows domain or a server trusted by the domain.So somebody has moved it and if your reportings were okay so you can easily understand its on the member or a trusted domain.If your reports are not working its better to install it again.|||the ip address of the server changed, so really i shouldnt have to do anything - BUT, they did not open the port that allows dynamic ports to be found (UDP 1434). so i have to redo it and specify the port number i.e. Server\Instance, Port.

the ms website says rsconfig does not support named instances, but the installation GUI did, so that does not make sense

i do not want to reinstall it, it will be a pain, the rsconfig utility should support named instances otherwise what is the point