changing the types of particular columns in particular table in the database

hi,
i have more than 300 tables in the database. Out of that tables some table has column whose type is "char".I want to change only those tables' column to the type "nvarchar".
Is it possible to make the modification all at a once?
since i am changing each and every table , this takes a lots of time.

thanks in advance,
by
singam

Hi,

first of all there is no built in function for this. YOu have to do that one by one in the script, there is sure a chance to do this automagically but I am no fan of a full automatic procedure to modify the schema of a bunch of tables.

You can (as I always do ) let the SQL Server create the script for you and execute this on your own in bacthes for better error handling (as you didn′t point out if error handling is applicable for you like in SQL Server 2005)

SELECT 'ALTER TABLE ' + C.TABLE_NAME +' ALTER COLUMN ' + C.COLUMN_NAME + ' VARCHAR(50)' --New data type and length
FROM INFORMATION_SCHEMA.Columns C
INNER JOIN INFORMATION_SCHEMA.Tables T
ON
T.TABLE_CATALOG = C.TABLE_CATALOG AND
T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE
DATA_TYPE = 'NVARCHAR' AND --Old Type
CHARACTER_MAXIMUM_LENGTH = 50 AND --Old lenght
TABLE_TYPE = 'BASE TABLE'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Changing the Table Type

Is there a to change the Type property on a table? I would like to be able
to change the Type from 'User' to 'System' through code.
Adv-thanks-anceWhat do you need this for?
True system tables have object id values less than 100, and there is no way
you can create a table with such an id.
If you are only talking about what shows up in the 'type' column when you
list the objects in Enterprise Manager, you can run the procedure
exec sp_MS_marksystemobject 'mytable'
However, it would not actually be a system table, even though Enterprise
Manager lists it as such. For example, it would still show 'user table' when
using sp_help, and you would not need to set any special flags to
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:A946CD68-5A88-4E55-B813-4D1ADBF49DA6@.microsoft.com...
> Is there a to change the Type property on a table? I would like to be
> able
> to change the Type from 'User' to 'System' through code.
> Adv-thanks-ance
>

Changing the owner of a user-defined data type

Hi Guys

Wonder if you could help me.

Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.

Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.

This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.

This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.

Any ideas?

Rgds

RobbieHi

I don't think there is a way to do this. If there are dependencies on the
UDT they will all need to be removed, then you can drop and re-create it.

John

"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062351.46e1f3f3@.posting.google.c om...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbiesql

Changing the owner of a user-defined data type

Hi Guys
Wonder if you could help me.
Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.
Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.
This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.
This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.
Any ideas?
Rgds
RobbieI have never changed the owner of a UDT, but if it can be done, then
sp_changeobjectowner would the be sp to do it.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062353.3dba1c6e@.posting.google.com...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbie

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guy
Using ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy
|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:

> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:

> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing security authentication type.

Hi,
in an existing instance of SQL, will changing the security authentication
from SQL & Windows logins, to only windows logins cause any issues?
Also, if a database has been created with hardly any security already, are
there any issues with introducing security further down the line. For
example, a SQL server has been deployed already by my predecessor, with
authentication in SA & windows mode, and allowing pretty much anyone access
to SQL. If i was to create a DBA_Admin group and assign admin writes only to
that group, will it cause any problems in a already functioning database?
TIAInline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi,
> in an existing instance of SQL, will changing the security authentication
> from SQL & Windows logins, to only windows logins cause any issues?
>
It will block (lock out) any attempts to access the database using the sa
account (or any other SQL Login).

> Also, if a database has been created with hardly any security already, are
> there any issues with introducing security further down the line. For
There 'could' be substaintial disruption when you introduce security.
Consider a building where there were no keys necessary to enter the building
and the rooms, and suddenly keys were required to enter the building, and
also to enter rooms. Until everyone got all of the correct keys for their
needs, there would be major disruption. However, if this was a well planned
process, and appropraite keys were distributed before all the locks were
install, the disruption would be minimal if at all.

> example, a SQL server has been deployed already by my predecessor, with
> authentication in SA & windows mode, and allowing pretty much anyone
> access to SQL. If i was to create a DBA_Admin group and assign admin
> writes only to that group, will it cause any problems in a already
> functioning database?
Creating a domain/DBA_Admin group, providing that group login access to the
server, and also placing that group in the sysadmin server role, will not
cause any problems in a functioning database.
The problems will occur as you start locking down and removing permissions
from the sa account. And you 'should' do that. Applications should not be
using the sa account for database access since the sa account can do
'anything' with and to the server.

> TIA
>|||ok, many thanks for the reply.
So in short you're saying to keep the "mixed" authentication, but to secure
the server down, the best path to take is to create an AD DBA group, and
configure key users to be in that group?
furthermore, to leave the sa account's permissions well alone!
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
> Inline...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It will block (lock out) any attempts to access the database using the sa
> account (or any other SQL Login).
>
>
> There 'could' be substaintial disruption when you introduce security.
> Consider a building where there were no keys necessary to enter the
> building and the rooms, and suddenly keys were required to enter the
> building, and also to enter rooms. Until everyone got all of the correct
> keys for their needs, there would be major disruption. However, if this
> was a well planned process, and appropraite keys were distributed before
> all the locks were install, the disruption would be minimal if at all.
>
> Creating a domain/DBA_Admin group, providing that group login access to
> the server, and also placing that group in the sysadmin server role, will
> not cause any problems in a functioning database.
> The problems will occur as you start locking down and removing permissions
> from the sa account. And you 'should' do that. Applications should not be
> using the sa account for database access since the sa account can do
> 'anything' with and to the server.
>
>|||Actually, I recommend that you take a deliberative approach to removing all
usage of the sa account, possibly with the eventual goal of switching to
Windows authentication.
Your first steps, however, are to determine the impact on the applications
of changing the connection strings. For some applications, that will be
relatively easy, for others, a re-deploy may be required and that will take
some time, effort and coordination.
Create one or more logins for the applications. Create one or more roles for
each database. Give those roles access to the appropriate databases. give
those roles permissions to the tables, views, stored procedures and
functions required by the applications. Place the logins in the new roles.
DO NOT put these logins in the sysadmin role.
Change the connections strings for the applications to use the new logins.
You want to eventually remove any application usage of the sa account.
This is a goal, it may take some time to eventually put it into place.
But for now, take no action to change the sa account -until you understand
the implications, and have a plan to addresses any issues.
However, as long as the sa account password is readily known by developers
(and whomever else may know it), you really have no security for your
database. (And if the application is hacked, the hackers will have admin
privileges in the database.)
There may be other regulatory reasons that you need to tighten up the
security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
likely be flagged as a security audit failure.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> ok, many thanks for the reply.
> So in short you're saying to keep the "mixed" authentication, but to
> secure the server down, the best path to take is to create an AD DBA
> group, and configure key users to be in that group?
> furthermore, to leave the sa account's permissions well alone!
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||Many thanks for your advice to date Arnie. Could i just run some more
questions past the group.
After reading your reply, i'm thinking that yes in time the sa account will
be withdrawn from use.
I'm just trying to get my head around a plan of attack on how it will take
place.
The first step i want to take is to create a group for suitable DBA's to
administer the server.
I'm guessing that i'm also going to have to create a DBA Admin user, which
will effectively take the place of the SA account, which will not as you
mention be a member of the sysadmin group / role.
Is the switch over just as easy as selecting the windows authentication
radio button?
if so, if the changeover is made, if things go wrong, will the role back be
to retick mixed mode or would the initial changeover be not possible to
reverse?
also what changes will need to be done to the individual databases, just
changing the connection strings?
once again, thanks for any further advice.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
> Actually, I recommend that you take a deliberative approach to removing
> all usage of the sa account, possibly with the eventual goal of switching
> to Windows authentication.
> Your first steps, however, are to determine the impact on the applications
> of changing the connection strings. For some applications, that will be
> relatively easy, for others, a re-deploy may be required and that will
> take some time, effort and coordination.
> Create one or more logins for the applications. Create one or more roles
> for each database. Give those roles access to the appropriate databases.
> give those roles permissions to the tables, views, stored procedures and
> functions required by the applications. Place the logins in the new roles.
> DO NOT put these logins in the sysadmin role.
> Change the connections strings for the applications to use the new logins.
> You want to eventually remove any application usage of the sa account.
> This is a goal, it may take some time to eventually put it into place.
> But for now, take no action to change the sa account -until you understand
> the implications, and have a plan to addresses any issues.
> However, as long as the sa account password is readily known by developers
> (and whomever else may know it), you really have no security for your
> database. (And if the application is hacked, the hackers will have admin
> privileges in the database.)
> There may be other regulatory reasons that you need to tighten up the
> security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
> likely be flagged as a security audit failure.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||Inline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uZXUeon1GHA.3908@.TK2MSFTNGP05.phx.gbl...
> Many thanks for your advice to date Arnie. Could i just run some more
> questions past the group.
> After reading your reply, i'm thinking that yes in time the sa account
> will be withdrawn from use.
> I'm just trying to get my head around a plan of attack on how it will take
> place.
> The first step i want to take is to create a group for suitable DBA's to
> administer the server.
> I'm guessing that i'm also going to have to create a DBA Admin user, which
> will effectively take the place of the SA account, which will not as you
> mention be a member of the sysadmin group / role.
>
A new DBA Admin User login would only be useful for SQL Login purposes.
You're moving away from SQL Logins.
It may be useful to create a domain group that contains those appointed to
be the SQL Administrators. You can then assign that domain group to the
[sysadmin] role in the server. Anyone that is a SQL Administrator must b
e in
the sysadmin role. (It has the same 'power' as [sa], but the security yo
u
are moving toward is that a user must have their own domain account/password
in order to get into the server, and then if they are in the sysadmin role
they are allowed administrator priviledges. If you cannot create (or have
created) a domain group, then you could add each prospective administrator's
domain/username to the sysadmin role.
Also, if appropriate you can add users to the db_owner role in a database.
That role allows complete control of a database, create tables, etc., BUT
does not allow similar control for other databases on the same server. This
can be good for developers when a database is in development, and then
removed from developers when the database goes into production.

> Is the switch over just as easy as selecting the windows authentication
> radio button?
> if so, if the changeover is made, if things go wrong, will the role back
> be to retick mixed mode or would the initial changeover be not possible to
> reverse?
Yes, you can check the button for Windows Authentication, and if things
break, click on SQL Authentication to return it back to mixed mode. -It will
take a few seconds as the server has to stop/restart.

> also what changes will need to be done to the individual databases, just
> changing the connection strings?
>
Each application will have to have it's connection strings changed to allow
for the new security model. BUT first, you will have created a database role
for the application users, and added the domain groups/accounts to that
role.
It will also be necessary to go through each database and provide
appropriate permissions to that role for Tables, Views, Stored Procedures,
Functions.
Different applications that have their own databases 'should' each have a
distinct database role, so that a user in permissions in one database cannot
inadvertently access data in another database.

> once again, thanks for any further advice.
No problem.
For a good introduction into SQL Server security, see if you can find a copy
of Morris Lewis' book, SQL Server Security Distilled, ISBN 1-9043-47-07-X
http://www.abebooks.com/servlet/Boo...isbn=190434707X

>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>

Changing sa password

Hi All,
I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
data bases on this server & the authentication type is mixed. I would like to
know what are the implications if I change the sa password. Also would like
to know what all the precautions to be taken to do so.
With Regards,
Ovin
Have a look at sp_password?
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
6
> data bases on this server & the authentication type is mixed. I would like
to
> know what are the implications if I change the sa password. Also would
like
> to know what all the precautions to be taken to do so.
> With Regards,
>
|||Hi,
There is no implications in changing the SA password. THe only thing is
check in your application for any hard coded password for sa.
If it is not there then you could change the password for SA.
Note: It is not recommended to use SA password from application. If used
create a new login with DB_owner prev. and access the application.
Thanks
Hari
SQL Server MVP
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
> 6
> data bases on this server & the authentication type is mixed. I would like
> to
> know what are the implications if I change the sa password. Also would
> like
> to know what all the precautions to be taken to do so.
> With Regards,
>
|||In SQL Server 6.5 and earlier, many tasks could only be down as the actual sa
and Windows Network Authenticated system administrators were actually mapped,
aliased, as the sa.
However, since SQL Server 7.0, the creation of the system roles removed this
rectriction. Any login, SQL Server Autenticate or Windows Authenticated, who
is also a member of the system_administrators system role will have the EXACT
same permissions as the sa account itself. The security issue here is that
EVERYONE knows that the sa account exists; thus, this is a huge security hole.
It is not only a good idea to reset this password but to do it often and
make it as complex as possible.
As far as the impact to the system is concerned, it should not as long as no
applications are using them. If you are the application developer, you
should know whether or not you are using this account and switch the
application's login to something else if it is. If you are but a lowly DBA,
you may not know, not have access, or be supporting ill-coded vendor
applications. Regardless, you can use the SQL Server Profiler and audit for
the sa account login. You will have to filter out legitamate system
operations that typically will use the sa account also. How long you run
this audit depends on the nature of the applications support but one work day
to a business week should be enough to identify those systems that may be
using this account.
If all else fails, you can extract out the encrypted password if you do not
already know it, and be prepared to reset the account's password to its
original if issues arise.
Sincerely,
Anthony Thomas
"Ovin Crasta" wrote:

> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
> data bases on this server & the authentication type is mixed. I would like to
> know what are the implications if I change the sa password. Also would like
> to know what all the precautions to be taken to do so.
> With Regards,
>
|||You could run a Profiler trace to catch who logs in using the "sa" login and based on that determine
what you need to do. No-one should need to login as sa, but some (badly written) apps do, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
> data bases on this server & the authentication type is mixed. I would like to
> know what are the implications if I change the sa password. Also would like
> to know what all the precautions to be taken to do so.
> With Regards,
>

Changing sa password

Hi All,
I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
data bases on this server & the authentication type is mixed. I would like to
know what are the implications if I change the sa password. Also would like
to know what all the precautions to be taken to do so.
With Regards,Ovin
Have a look at sp_password?
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
6
> data bases on this server & the authentication type is mixed. I would like
to
> know what are the implications if I change the sa password. Also would
like
> to know what all the precautions to be taken to do so.
> With Regards,
>|||Hi,
There is no implications in changing the SA password. THe only thing is
check in your application for any hard coded password for sa.
If it is not there then you could change the password for SA.
Note: It is not recommended to use SA password from application. If used
create a new login with DB_owner prev. and access the application.
Thanks
Hari
SQL Server MVP
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
> 6
> data bases on this server & the authentication type is mixed. I would like
> to
> know what are the implications if I change the sa password. Also would
> like
> to know what all the precautions to be taken to do so.
> With Regards,
>|||In SQL Server 6.5 and earlier, many tasks could only be down as the actual sa
and Windows Network Authenticated system administrators were actually mapped,
aliased, as the sa.
However, since SQL Server 7.0, the creation of the system roles removed this
rectriction. Any login, SQL Server Autenticate or Windows Authenticated, who
is also a member of the system_administrators system role will have the EXACT
same permissions as the sa account itself. The security issue here is that
EVERYONE knows that the sa account exists; thus, this is a huge security hole.
It is not only a good idea to reset this password but to do it often and
make it as complex as possible.
As far as the impact to the system is concerned, it should not as long as no
applications are using them. If you are the application developer, you
should know whether or not you are using this account and switch the
application's login to something else if it is. If you are but a lowly DBA,
you may not know, not have access, or be supporting ill-coded vendor
applications. Regardless, you can use the SQL Server Profiler and audit for
the sa account login. You will have to filter out legitamate system
operations that typically will use the sa account also. How long you run
this audit depends on the nature of the applications support but one work day
to a business week should be enough to identify those systems that may be
using this account.
If all else fails, you can extract out the encrypted password if you do not
already know it, and be prepared to reset the account's password to its
original if issues arise.
Sincerely,
Anthony Thomas
"Ovin Crasta" wrote:
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
> data bases on this server & the authentication type is mixed. I would like to
> know what are the implications if I change the sa password. Also would like
> to know what all the precautions to be taken to do so.
> With Regards,
>|||You could run a Profiler trace to catch who logs in using the "sa" login and based on that determine
what you need to do. No-one should need to login as sa, but some (badly written) apps do, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
> data bases on this server & the authentication type is mixed. I would like to
> know what are the implications if I change the sa password. Also would like
> to know what all the precautions to be taken to do so.
> With Regards,
>

Changing sa password

Hi All,
I am running SQL server 2000 with sp3a on Windows Server 2003,I am running 6
data bases on this server & the authentication type is mixed. I would like t
o
know what are the implications if I change the sa password. Also would like
to know what all the precautions to be taken to do so.
With Regards,Ovin
Have a look at sp_password?
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
6
> data bases on this server & the authentication type is mixed. I would like
to
> know what are the implications if I change the sa password. Also would
like
> to know what all the precautions to be taken to do so.
> With Regards,
>|||Hi,
There is no implications in changing the SA password. THe only thing is
check in your application for any hard coded password for sa.
If it is not there then you could change the password for SA.
Note: It is not recommended to use SA password from application. If used
create a new login with DB_owner prev. and access the application.
Thanks
Hari
SQL Server MVP
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
> 6
> data bases on this server & the authentication type is mixed. I would like
> to
> know what are the implications if I change the sa password. Also would
> like
> to know what all the precautions to be taken to do so.
> With Regards,
>|||In SQL Server 6.5 and earlier, many tasks could only be down as the actual s
a
and Windows Network Authenticated system administrators were actually mapped
,
aliased, as the sa.
However, since SQL Server 7.0, the creation of the system roles removed this
rectriction. Any login, SQL Server Autenticate or Windows Authenticated, wh
o
is also a member of the system_administrators system role will have the EXAC
T
same permissions as the sa account itself. The security issue here is that
EVERYONE knows that the sa account exists; thus, this is a huge security hol
e.
It is not only a good idea to reset this password but to do it often and
make it as complex as possible.
As far as the impact to the system is concerned, it should not as long as no
applications are using them. If you are the application developer, you
should know whether or not you are using this account and switch the
application's login to something else if it is. If you are but a lowly DBA,
you may not know, not have access, or be supporting ill-coded vendor
applications. Regardless, you can use the SQL Server Profiler and audit for
the sa account login. You will have to filter out legitamate system
operations that typically will use the sa account also. How long you run
this audit depends on the nature of the applications support but one work da
y
to a business week should be enough to identify those systems that may be
using this account.
If all else fails, you can extract out the encrypted password if you do not
already know it, and be prepared to reset the account's password to its
original if issues arise.
Sincerely,
Anthony Thomas
"Ovin Crasta" wrote:

> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
6
> data bases on this server & the authentication type is mixed. I would like
to
> know what are the implications if I change the sa password. Also would lik
e
> to know what all the precautions to be taken to do so.
> With Regards,
>|||You could run a Profiler trace to catch who logs in using the "sa" login and
based on that determine
what you need to do. No-one should need to login as sa, but some (badly writ
ten) apps do, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ovin Crasta" <OvinCrasta@.discussions.microsoft.com> wrote in message
news:C5EBB60D-6421-4C34-943C-46C767389402@.microsoft.com...
> Hi All,
> I am running SQL server 2000 with sp3a on Windows Server 2003,I am running
6
> data bases on this server & the authentication type is mixed. I would like
to
> know what are the implications if I change the sa password. Also would lik
e
> to know what all the precautions to be taken to do so.
> With Regards,
>

Changing result set - Abbreviations

Hello,
I have a table that stores Customer data (e.g. ID, Name,
Type, Address etc)
In the column 'Type' I store either 'B' for Business
or 'P' for private.
When I return a result set I would like to see the
words 'Business' or 'Private', rather than 'B' or 'P'
How would I do this?
Thanks,
BillyThis is a multi-part message in MIME format.
--=_NextPart_000_0206_01C3600A.432E19A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
case
when Type =3D 'B' then 'Business'
when Type =3D 'P' then 'Private'
end
from
MyTable
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Billy" <billyrotorwing@.hotmail.com> wrote in message =news:079b01c3602a$61c354f0$a001280a@.phx.gbl...
Hello,
I have a table that stores Customer data (e.g. ID, Name, Type, Address etc)
In the column 'Type' I store either 'B' for Business or 'P' for private.
When I return a result set I would like to see the words 'Business' or 'Private', rather than 'B' or 'P'
How would I do this?
Thanks,
Billy
--=_NextPart_000_0206_01C3600A.432E19A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=case
= when Type =3D 'B' then 'Business'
= when Type =3D 'P' then 'Private'
=end
from
=MyTable
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Billy" wrote in message news:079b01c3602a$61=c354f0$a001280a@.phx.gbl...Hello,I have a table that stores Customer data (e.g. ID, Name, Type, Address =etc)In the column 'Type' I store either 'B' for Business or ='P' for private. When I return a result set I would like to see the =words 'Business' or 'Private', rather than 'B' or 'P'How would I do this?Thanks,Billy

---

--=_NextPart_000_0206_01C3600A.432E19A0--

changing replication type for hourly to continuously

Hi All ,
Previously i have set up my replication to be on sync per hour but i want
to change it to be continuously
is there a way to do it w/o removing the existing publication & subscriber
and thus save the task of re-creating the snapshot ?
appreciate ur advice
tks & rdgs
--
Message posted via http://www.sqlmonster.comHi All ,
Furthermore , is there any drawbacks in changing it to continuously mode ?
kindly advise
tks & rdgs
maxzsim wrote:
>Hi All ,
> Previously i have set up my replication to be on sync per hour but i want
>to change it to be continuously
> is there a way to do it w/o removing the existing publication & subscriber
>and thus save the task of re-creating the snapshot ?
>appreciate ur advice
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Open the job in SQL Server Agent. Change the schedule to "start when SQL
Server starts" or whatever it says. Remove the schedule that executed it
once per hour.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:59ef504952a00@.uwe...
> Hi All ,
> Previously i have set up my replication to be on sync per hour but i want
> to change it to be continuously
> is there a way to do it w/o removing the existing publication & subscriber
> and thus save the task of re-creating the snapshot ?
> appreciate ur advice
> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||tks Michael , but would you know of any drawbakcs using the continuously mode
? especially when the network is not that fast ? i.e while the previous
changes are still being replicated , there are new changes so will it cause
the system to be cranky or anything like that ?
tks & rdgs
Michael Hotek wrote:
>Open the job in SQL Server Agent. Change the schedule to "start when SQL
>Server starts" or whatever it says. Remove the schedule that executed it
>once per hour.
>> Hi All ,
>[quoted text clipped - 7 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Changing the agent to only start when SQL Server Agent starts will have it
process only a single batch and then the distribution agent will shut down.
It will not restart until you restart SQL Server agent. Try it. Stop your
SQL Server agent, insert a bunch of rows into a replicated table, bounce SQL
Server, observe your distribution agent starting up, processing the batch
and then shutting down.
You also need to edit your agent to and add the -Continuous switch. Right
click on your agent, select agent properties, click on the steps tab, and
double click on run agent. Click in the commands text box and hit the end
key, then press the space bar and type in -Continuous. Restart your
distribution agent. If you distribution agent does fail, it will restart
again in 1 hour if you leave the scheduling option on.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:59f0f9aab1351@.uwe...
> tks Michael , but would you know of any drawbakcs using the continuously
> mode
> ? especially when the network is not that fast ? i.e while the previous
> changes are still being replicated , there are new changes so will it
> cause
> the system to be cranky or anything like that ?
> tks & rdgs
> Michael Hotek wrote:
>>Open the job in SQL Server Agent. Change the schedule to "start when SQL
>>Server starts" or whatever it says. Remove the schedule that executed it
>>once per hour.
>> Hi All ,
>>[quoted text clipped - 7 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||No. It will simply send as much as possible during every synch cycle.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:59f0f9aab1351@.uwe...
> tks Michael , but would you know of any drawbakcs using the continuously
> mode
> ? especially when the network is not that fast ? i.e while the previous
> changes are still being replicated , there are new changes so will it
> cause
> the system to be cranky or anything like that ?
> tks & rdgs
> Michael Hotek wrote:
>>Open the job in SQL Server Agent. Change the schedule to "start when SQL
>>Server starts" or whatever it says. Remove the schedule that executed it
>>once per hour.
>> Hi All ,
>>[quoted text clipped - 7 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

changing replication type for hourly to continuously

Hi All ,
Previously i have set up my replication to be on sync per hour but i want
to change it to be continuously
is there a way to do it w/o removing the existing publication & subscriber
and thus save the task of re-creating the snapshot ?
appreciate ur advice
tks & rdgs
Message posted via http://www.droptable.com
Hi All ,
Furthermore , is there any drawbacks in changing it to continuously mode ?
kindly advise
tks & rdgs
maxzsim wrote:
>Hi All ,
> Previously i have set up my replication to be on sync per hour but i want
>to change it to be continuously
> is there a way to do it w/o removing the existing publication & subscriber
>and thus save the task of re-creating the snapshot ?
>appreciate ur advice
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Open the job in SQL Server Agent. Change the schedule to "start when SQL
Server starts" or whatever it says. Remove the schedule that executed it
once per hour.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59ef504952a00@.uwe...
> Hi All ,
> Previously i have set up my replication to be on sync per hour but i want
> to change it to be continuously
> is there a way to do it w/o removing the existing publication & subscriber
> and thus save the task of re-creating the snapshot ?
> appreciate ur advice
> tks & rdgs
> --
> Message posted via http://www.droptable.com
|||tks Michael , but would you know of any drawbakcs using the continuously mode
? especially when the network is not that fast ? i.e while the previous
changes are still being replicated , there are new changes so will it cause
the system to be cranky or anything like that ?
tks & rdgs
Michael Hotek wrote:[vbcol=seagreen]
>Open the job in SQL Server Agent. Change the schedule to "start when SQL
>Server starts" or whatever it says. Remove the schedule that executed it
>once per hour.
>[quoted text clipped - 7 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Changing the agent to only start when SQL Server Agent starts will have it
process only a single batch and then the distribution agent will shut down.
It will not restart until you restart SQL Server agent. Try it. Stop your
SQL Server agent, insert a bunch of rows into a replicated table, bounce SQL
Server, observe your distribution agent starting up, processing the batch
and then shutting down.
You also need to edit your agent to and add the -Continuous switch. Right
click on your agent, select agent properties, click on the steps tab, and
double click on run agent. Click in the commands text box and hit the end
key, then press the space bar and type in -Continuous. Restart your
distribution agent. If you distribution agent does fail, it will restart
again in 1 hour if you leave the scheduling option on.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f0f9aab1351@.uwe...
> tks Michael , but would you know of any drawbakcs using the continuously
> mode
> ? especially when the network is not that fast ? i.e while the previous
> changes are still being replicated , there are new changes so will it
> cause
> the system to be cranky or anything like that ?
> tks & rdgs
> Michael Hotek wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||No. It will simply send as much as possible during every synch cycle.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f0f9aab1351@.uwe...
> tks Michael , but would you know of any drawbakcs using the continuously
> mode
> ? especially when the network is not that fast ? i.e while the previous
> changes are still being replicated , there are new changes so will it
> cause
> the system to be cranky or anything like that ?
> tks & rdgs
> Michael Hotek wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1

changing replication type for hourly to continuously

Hi All ,
Previously i have set up my replication to be on sync per hour but i want
to change it to be continuously
is there a way to do it w/o removing the existing publication & subscriber
and thus save the task of re-creating the snapshot ?
appreciate ur advice
tks & rdgs
Message posted via http://www.droptable.comHi All ,
Furthermore , is there any drawbacks in changing it to continuously mode ?
kindly advise
tks & rdgs
maxzsim wrote:
>Hi All ,
> Previously i have set up my replication to be on sync per hour but i want
>to change it to be continuously
> is there a way to do it w/o removing the existing publication & subscriber
>and thus save the task of re-creating the snapshot ?
>appreciate ur advice
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Open the job in SQL Server Agent. Change the schedule to "start when SQL
Server starts" or whatever it says. Remove the schedule that executed it
once per hour.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59ef504952a00@.uwe...
> Hi All ,
> Previously i have set up my replication to be on sync per hour but i want
> to change it to be continuously
> is there a way to do it w/o removing the existing publication & subscriber
> and thus save the task of re-creating the snapshot ?
> appreciate ur advice
> tks & rdgs
> --
> Message posted via http://www.droptable.com|||tks Michael , but would you know of any drawbakcs using the continuously mod
e
? especially when the network is not that fast ? i.e while the previous
changes are still being replicated , there are new changes so will it cause
the system to be cranky or anything like that ?
tks & rdgs
Michael Hotek wrote:[vbcol=seagreen]
>Open the job in SQL Server Agent. Change the schedule to "start when SQL
>Server starts" or whatever it says. Remove the schedule that executed it
>once per hour.
>
>[quoted text clipped - 7 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Changing the agent to only start when SQL Server Agent starts will have it
process only a single batch and then the distribution agent will shut down.
It will not restart until you restart SQL Server agent. Try it. Stop your
SQL Server agent, insert a bunch of rows into a replicated table, bounce SQL
Server, observe your distribution agent starting up, processing the batch
and then shutting down.
You also need to edit your agent to and add the -Continuous switch. Right
click on your agent, select agent properties, click on the steps tab, and
double click on run agent. Click in the commands text box and hit the end
key, then press the space bar and type in -Continuous. Restart your
distribution agent. If you distribution agent does fail, it will restart
again in 1 hour if you leave the scheduling option on.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f0f9aab1351@.uwe...
> tks Michael , but would you know of any drawbakcs using the continuously
> mode
> ? especially when the network is not that fast ? i.e while the previous
> changes are still being replicated , there are new changes so will it
> cause
> the system to be cranky or anything like that ?
> tks & rdgs
> Michael Hotek wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||No. It will simply send as much as possible during every synch cycle.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f0f9aab1351@.uwe...
> tks Michael , but would you know of any drawbakcs using the continuously
> mode
> ? especially when the network is not that fast ? i.e while the previous
> changes are still being replicated , there are new changes so will it
> cause
> the system to be cranky or anything like that ?
> tks & rdgs
> Michael Hotek wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1

Changing Recovery Type db_option for all Databases on server

I have about 29 servers that I need to set all db_options for, and they will
be the same on all servers.
Do you know the syntax for changing all database recovery types to "simple"?
Also, I need to change all db_options for AutoShrink to "false". I don't
want to go one by one, so I was hoping someone knows a shortcut.
Thanks,
Michelle-- undocumented, unsupported!
EXEC sp_MSForEachDB 'IF ''?'' != ''TEMPDB'' EXEC(''ALTER DATABASE [?] SET
RECOVERY SIMPLE'')'
http://www.aspfaq.com/
(Reverse address to reply.)
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:6B34E6BF-DF65-40C6-9623-07E3B7D0AA48@.microsoft.com...
> I have about 29 servers that I need to set all db_options for, and they
will
> be the same on all servers.
> Do you know the syntax for changing all database recovery types to
"simple"?
> Also, I need to change all db_options for AutoShrink to "false". I don't
> want to go one by one, so I was hoping someone knows a shortcut.
> Thanks,
> Michelle|||JaneDoe wrote:
> I have about 29 servers that I need to set all db_options for, and
> they will be the same on all servers.
> Do you know the syntax for changing all database recovery types to
> "simple"? Also, I need to change all db_options for AutoShrink to
> "false". I don't want to go one by one, so I was hoping someone
> knows a shortcut.
> Thanks,
> Michelle
Look at ALTER DATABASE in BOL:
SET RECOVERY SIMPLE
SET AUTO_SHRINK OFF
David Gugick
Imceda Software
www.imceda.com|||Deploy a SP on each server (perhaps in master database) that does what you
need. Then have one script that makes a remote call to each server. Also,
perhaps a WSH script that uses DMO to loop through each server, chaning
context and executing commands.
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:6B34E6BF-DF65-40C6-9623-07E3B7D0AA48@.microsoft.com...
> I have about 29 servers that I need to set all db_options for, and they
will
> be the same on all servers.
> Do you know the syntax for changing all database recovery types to
"simple"?
> Also, I need to change all db_options for AutoShrink to "false". I don't
> want to go one by one, so I was hoping someone knows a shortcut.
> Thanks,
> Michelle

Changing field type

I'm using my own data processing extension to provide a dataset to a report -
the dataset is beging generatied by VB code and passed in form of XML to the
report as a parameter. Everything works fine except from that type of all
fields is String (although they are passed as doubles in the code), so I'm
not able to show graphs on the report. I've tried to solve the problem by
adding following line:
<rd:TypeName>System.Double</rd:TypeName>
to each datafield in the RDL-file. But it didn't help.
Is it something else I could try to solve this problem?Right-click on the fields list and click "add...". Give it a name and
specify a calculated field of:
=cint(Fields!fieldname.Value)
Mike G.
"Anna" <Anna@.discussions.microsoft.com> wrote in message
news:75B8EF07-1159-4824-93F2-FDF74863DF20@.microsoft.com...
> I'm using my own data processing extension to provide a dataset to a
> report -
> the dataset is beging generatied by VB code and passed in form of XML to
> the
> report as a parameter. Everything works fine except from that type of all
> fields is String (although they are passed as doubles in the code), so I'm
> not able to show graphs on the report. I've tried to solve the problem by
> adding following line:
> <rd:TypeName>System.Double</rd:TypeName>
> to each datafield in the RDL-file. But it didn't help.
> Is it something else I could try to solve this problem?|||Anna,
Maybe it would work if you create a fuction in the Custom Code section,
which casts the strings to double's
Something like
Function ConvertToDouble(sValue as String)
return Double.Parse(sValue)
end Function
And as your chart data value you sould add
'=Code.ConvertToDouble(Fields!stringValue)' to use the convert values as your
chart input.
Jan Pieter Posthuma
"Anna" wrote:
> I'm using my own data processing extension to provide a dataset to a report -
> the dataset is beging generatied by VB code and passed in form of XML to the
> report as a parameter. Everything works fine except from that type of all
> fields is String (although they are passed as doubles in the code), so I'm
> not able to show graphs on the report. I've tried to solve the problem by
> adding following line:
> <rd:TypeName>System.Double</rd:TypeName>
> to each datafield in the RDL-file. But it didn't help.
> Is it something else I could try to solve this problem?|||Sorry, didn't notice the "double" until I read Jan's post. Of course, that
means using cdbl instead of cint. (or using Jan's suggestion and creating a
function)
Mike G.
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:e6Mh5j0TFHA.584@.TK2MSFTNGP15.phx.gbl...
> Right-click on the fields list and click "add...". Give it a name and
> specify a calculated field of:
> =cint(Fields!fieldname.Value)
> Mike G.
>
> "Anna" <Anna@.discussions.microsoft.com> wrote in message
> news:75B8EF07-1159-4824-93F2-FDF74863DF20@.microsoft.com...
>> I'm using my own data processing extension to provide a dataset to a
>> report -
>> the dataset is beging generatied by VB code and passed in form of XML to
>> the
>> report as a parameter. Everything works fine except from that type of all
>> fields is String (although they are passed as doubles in the code), so
>> I'm
>> not able to show graphs on the report. I've tried to solve the problem by
>> adding following line:
>> <rd:TypeName>System.Double</rd:TypeName>
>> to each datafield in the RDL-file. But it didn't help.
>> Is it something else I could try to solve this problem?
>

Changing Field name of an existing Table

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeepif it is not a primary or a foriegn key you can do something like so...

ALTER TABLE Mytable
ADD INQUIRYID2 BIGINT
GO
UPDATE MyTable SET INQUIRYID2 = INQUIRYID
GO
ALTER TABLE MyTable
DROP COLUMN INQUIRYID

But this a bad idea. What about the existing database object that reference the existing column. Oh well too late. A thousand application buga have just been created. Update your resume. You want biggie size|||if you can connect the db thru EM, just type the new name....|||if you can connect the db thru EM, just type the new name....

The Holy Book says ...

sp_rename

Changes the name of a user-created object (for example, table, column, or user-defineddata type) in the current database.
Syntax

sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
Arguments

[@.objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@.newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@.objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
ValueDescriptionCOLUMNA column to be renamed.DATABASEA user-defined database. This option is required when renaming a database.INDEXA user-defined index.OBJECTAn item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.USERDATATYPEA user-defined data type added by executing sp_addtype.


I don't know why you are providing a round about way of doing the same thing.|||You can try out this one.

EXECUTE sp_rename N'MyTable.MyCurrFName', N'MyNewFName', 'COLUMN'

MyTable has a Field MyCurrFName. It will be renamed to MyNewFName|||You are better off either a). Create a view that has the name you want, or b). unloading, dropping, create, and load

There was a very good article that discusses why do the alter causes sql server to waste a lot of space on the data pages...Now if I can find on Nigel's web site I'll post a link|||Here's the link

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html|||thanks Brett, thats really a good link. never thought of it.

Changing document type definition when using FOR XML AUTO

Hi
When using FOR XML AUTO in sql server is there a way to change the
document typ definition (<?xml version="1.0"?> ). What I wanna do is
add the encoding property so it instead of the above reads <?xml
version="1.0" encoding="ISO-8859-1"?>.
The reason I wanna do this is because I have characters like "" and
"".
Or is this something I can handle in my .asp-page?
Thankfull for all suggestions!
/Christer
ASP code:
<%
Response.Buffer = true
'Response.ContentType = "text/xml"
Dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
Set cn = Server.CreateObject("ADODB.Connection")
cn.open CnString
Set xmldoc = Server.CreateObject("MSXML2.DomDocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "getDiaryXML"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
Set cmd = Nothing
cn.Close
Set cn = Nothing
%>
<%=xmldoc.xml%>
***********************
SQL:
SELECT
YEAR(dtDate) AS strYear,
DATENAME(MONTH,dtDate) AS strMonth,
DAY(dtDate) AS strDay,
diaryID,
txtDiaryHeader,
txtDiaryIngress,
txtDiaryText,
id as photoID
FROM diary ORDER BY dtDate
FOR XML AUTOHi
I think the problem is that the encoding should be UTF-16 this may help
http://groups-beta.google.com/group...c?
hl=en
John
"Christer" wrote:

> Hi
> When using FOR XML AUTO in sql server is there a way to change the
> document typ definition (<?xml version="1.0"?> ). What I wanna do is
> add the encoding property so it instead of the above reads <?xml
> version="1.0" encoding="ISO-8859-1"?>.
> The reason I wanna do this is because I have characters like "?" and
> "?".
> Or is this something I can handle in my .asp-page?
> Thankfull for all suggestions!
> /Christer
> ASP code:
> <%
> Response.Buffer = true
> 'Response.ContentType = "text/xml"
> Dim cn, rs,sQuery, cmd, xmldoc
> Const adExecuteStream = &H00000400
> Const adCmdText = &H0001
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.open CnString
> Set xmldoc = Server.CreateObject("MSXML2.DomDocument")
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = cn
> sQuery = "getDiaryXML"
> cmd.CommandText = sQuery
> cmd.Properties("xml root").Value = "root"
> cmd.Properties("Output Stream") = xmldoc
> cmd.Execute , , adExecuteStream + adCmdText
> Set cmd = Nothing
> cn.Close
> Set cn = Nothing
> %>
> <%=xmldoc.xml%>
> ***********************
> SQL:
> SELECT
> YEAR(dtDate) AS strYear,
> DATENAME(MONTH,dtDate) AS strMonth,
> DAY(dtDate) AS strDay,
> diaryID,
> txtDiaryHeader,
> txtDiaryIngress,
> txtDiaryText,
> id as photoID
> FROM diary ORDER BY dtDate
> FOR XML AUTO
>

Changing data type to the fields of my tables.

Hello.

i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.

I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.

thanks in advance

You can use SQL queries to generate the DDL statements that you need, something like this (use the other columns in the columns schema table to generate other things into the ALTER TABLE statements)

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] int'
+ CASE WHEN upper(IS_NULLABLE) = 'YES' THEN ' NULL' ELSE ' NOT NULL' END
from information_schema.columns
where data_type = 'tinyint'
and table_name in (select table_name from information_schema.tables where table_type = 'BASE TABLE')

Make sure you backup before you start running these scripts and test them all!

|||That looks pretty good. I was curious what this would look like with the new 2005 syntax using the DMVs. Here is what I came up with... Please note: This needs strenuous testing and a backup like the previous post suggested!!

USE tempdb
GO
IF EXISTS(SELECT * FROM sys.types where name = 'SSN')
DROP TYPE SSN
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

GO
IF OBJECT_ID('TestSSN') IS NOT NULL
DROP TABLE TestSSN
GO
CREATE TABLE TestSSN ( MySSn SSN)
GO

-- Show the tables with the UDF fields...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
GO

DECLARE @.ErrMsg varchar(255),
@.ErrNum int,
@.SQL nvarchar(max),
@.TableName sysname,
@.ColumnName sysname,
@.SchemaName sysname

DECLARE @.c CURSOR

-- create a cursor to loop through all the tables
SET @.c = CURSOR FOR
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
FOR READ ONLY

OPEN @.c
FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN -- loop
SET @.SQL = 'ALTER TABLE ' + @.SchemaName + '.' + @.TableName + ' ALTER COLUMN ' + @.ColumnName + ' varchar(32) NOT NULL'
PRINT 'Executing: ' + @.SQL
EXEC sp_executesql @.SQL

SELECT @.ErrNum = @.@.Error--, @.RowCnt = @.@.ROWCOUNT
IF (@.ErrNum <> 0)
BEGIN
RAISERROR('Error updating %s.%s table to have %s a varchar(32). Error Numer: %d', 16, 1, @.SchemaName, @.TableName, @.ColumnName, @.ErrNum)
GOTO ErrorHandler
END

FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
END -- loop
CLOSE @.c
DEALLOCATE @.c

GOTO ExitScript

ErrorHandler:
RAISERROR (@.ErrMsg, 16, 127)
GOTO ExitScript

ExitScript:
GO

-- See if we have any tables with that UDF anymore...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'