I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
Sorry, wrong group...
"Atley" <atley_1@.homtmail.com> wrote in message
news:euhlpsnEEHA.3788@.TK2MSFTNGP10.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
Showing posts with label method. Show all posts
Showing posts with label method. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Change user type from char to vchar
We have a requirement to change a user defined type from char(4) to vchar(4).
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
Manod
ALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.c om...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod
|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
Manod
ALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.c om...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod
|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
Change user type from char to vchar
We have a requirement to change a user defined type from char(4) to vchar(4)
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
Change user type from char to vchar
We have a requirement to change a user defined type from char(4) to vchar(4)
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
Change user type from char to vchar
We have a requirement to change a user defined type from char(4) to vchar(4).
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:
> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
Friday, February 24, 2012
Change SQL Server Authentication Programmatically
Hi,
Does anyone here know whether it is possible to change the
authentication method of a SQL server instance programmatically, rather
than going through enterprise manager.
I am using SQL-DMO (under C#) for some other things, but the
documentation is so bad that I can't find out whether what I want to do
can be done. I have also tried googling but came up empty handed.
I hope that someone can help me.
Cheers
JonoHi
At a guess try SetSecurityMode under the SQLServer/IntegratedSecurity Object
John
"Jono Price" <jonathan_daivd_price@.hotmail.com> wrote in message
news:4298443f$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono|||John Bell wrote:
> Hi
> At a guess try SetSecurityMode under the SQLServer/IntegratedSecurity Object
> John
Thank you so much. I couldn't believe that it wasn't possible, but I
also couldn't find that.
Thanks again.
Jono
Change SQL Server Authentication method programmatically
Hi,
Does anyone here know whether it is possible to change the
authentication method of a SQL server instance programmatically, rather
than going through enterprise manager.
I am using SQL-DMO (under C#) for some other things, but the
documentation is so bad that I can't find out whether what I want to do
can be done. I have also tried googling but came up empty handed.
I hope that someone can help me.
Cheers
JonoThe only thing I know about, though I am not quite familar with SQL-DMO is
to change the regkey:, using the XP xp_regwrite
For registry changing try this regkey:
Default instance:
" HKEY_LOCAL_MACHINE\Software\M_icrosoft\M
SSqlserver\MSSqlServ_er\LoginMode"
to 2 for mixed-mode or 1 for integrated.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jono Price" <jonathan_daivd_price@.hotmail.com> schrieb im Newsbeitrag
news:42984478$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono|||"Jono Price" schrieb:
> Hi,
> Does anyone here know whether it is possible to change the
> authentication method of a SQL server instance programmatically, rather
> than going through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the
> documentation is so bad that I can't find out whether what I want to do
> can be done. I have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono
This is a VB solution (with SQLDMO). You'll have to translate this to C#
yourself.
On Error GoTo ErrSec
Dim srv As SQLDMO.SQLServer2
Set srv = New SQLDMO.SQLServer2
' integrated security login
srv.LoginSecure = True
' or standard security login
srv.LoginSecure = False
srv.Login = "sa"
srv.Password = "MyPassword"
srv.Connect "MyServer"
' switch to integrated
srv.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Integrated
' or switch to mixed mode
srv.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
' restart server: shutdown looses the connection and generates an error.
' we catch that error in the error section and restart the server.
srv.Shutdown
Exit Function
ErrSec:
srv.Disconnect
srv.Start (True) ' true reconnects automatically|||See SQLServer->ServerLoginMode()
_7s6h.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_7s6h.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Jono Price" <jonathan_daivd_price@.hotmail.com> wrote in message
news:42984478$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono
Does anyone here know whether it is possible to change the
authentication method of a SQL server instance programmatically, rather
than going through enterprise manager.
I am using SQL-DMO (under C#) for some other things, but the
documentation is so bad that I can't find out whether what I want to do
can be done. I have also tried googling but came up empty handed.
I hope that someone can help me.
Cheers
JonoThe only thing I know about, though I am not quite familar with SQL-DMO is
to change the regkey:, using the XP xp_regwrite
For registry changing try this regkey:
Default instance:
" HKEY_LOCAL_MACHINE\Software\M_icrosoft\M
SSqlserver\MSSqlServ_er\LoginMode"
to 2 for mixed-mode or 1 for integrated.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jono Price" <jonathan_daivd_price@.hotmail.com> schrieb im Newsbeitrag
news:42984478$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono|||"Jono Price" schrieb:
> Hi,
> Does anyone here know whether it is possible to change the
> authentication method of a SQL server instance programmatically, rather
> than going through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the
> documentation is so bad that I can't find out whether what I want to do
> can be done. I have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono
This is a VB solution (with SQLDMO). You'll have to translate this to C#
yourself.
On Error GoTo ErrSec
Dim srv As SQLDMO.SQLServer2
Set srv = New SQLDMO.SQLServer2
' integrated security login
srv.LoginSecure = True
' or standard security login
srv.LoginSecure = False
srv.Login = "sa"
srv.Password = "MyPassword"
srv.Connect "MyServer"
' switch to integrated
srv.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Integrated
' or switch to mixed mode
srv.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
' restart server: shutdown looses the connection and generates an error.
' we catch that error in the error section and restart the server.
srv.Shutdown
Exit Function
ErrSec:
srv.Disconnect
srv.Start (True) ' true reconnects automatically|||See SQLServer->ServerLoginMode()
_7s6h.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_7s6h.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Jono Price" <jonathan_daivd_price@.hotmail.com> wrote in message
news:42984478$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono
Tuesday, February 14, 2012
Change owner of User Defined Data Types
Please let me know if there is a method for changing the owner of existing User Defined Data Types. I cannot drop and recreate them as the datatypes are in use.
Thanks.Try, sp_changeobjectowner.
Refer to BOL for more information.|||sp_changeobjectowner cannot be used to change the owner of UDTs as it only works with tables, views, user-defined functions and stored procedures.|||Then only way is drop and recreate.|||As I stated in my original post, it is not possible to drop them as they are in use.|||Use specified SP to change owner for that table, which would be applied by default. Apart from this I have no other idea.
Thanks.Try, sp_changeobjectowner.
Refer to BOL for more information.|||sp_changeobjectowner cannot be used to change the owner of UDTs as it only works with tables, views, user-defined functions and stored procedures.|||Then only way is drop and recreate.|||As I stated in my original post, it is not possible to drop them as they are in use.|||Use specified SP to change owner for that table, which would be applied by default. Apart from this I have no other idea.
Subscribe to:
Posts (Atom)