There have been several threads about changing a database's collation but none have come up with an easy answer before.
The suggestion before was to create an empty database with the correct collation and then copy the data across.
However this is hard work as you have to populate tables in a specific order in order not to violate foreign keys etc. You can't just dts the whole data.
There follows scripts we have written to do the job. If people use them, please could you add to this thread whether they worked successfully or not.
Firstly we change the default collation, then change all the types in the database to match the new collation.
===================
--script to change database collation - James Agnini
--
--Replace <DATABASE> with the database name
--Replace <COLLATION> with the collation, eg SQL_Latin1_General_CP1_CI_AS
--
--After running this script, run the script to rebuild all indexes
ALTER DATABASE <DATABASE> COLLATE <COLLATION>
exec sp_configure 'allow updates',1
go
reconfigure with override
go
update syscolumns
set collationid = (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
where collationid <> (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
go
exec sp_configure 'allow updates',0
go
reconfigure with override
go
===================
As we have directly edited system tables, we need to run a script to rebuild all the indexes. Otherwise you will get strange results like comparing strings in different table not working.
The indexes have to actually be dropped and recreated in separate statements.
You can't use DBCC DBREINDEX or create index with the DROP_EXISTING option as they won't do anything(thanks to SQL Server "optimization").
This script loops through the tables and then loops through the indexes and unique constraints in separate sections. It gets the index information and drops and re-creates it.
(The script could probably be tidied up with the duplicate code put into a stored procedure).
====================
--Script to rebuild all table indexes, Version 0.1, May 2004 - James Agnini
--
--Database backups should be made before running any set of scripts that update databases.
--All users should be out of the database before running this script
print 'Rebuilding indexes for all tables:'
go
DECLARE @.Table_Name varchar(128)
declare @.Index_Name varchar(128)
declare @.IndexId int
declare @.IndexKey int
DECLARE Table_Cursor CURSOR FOR
select TABLE_NAME from INFORMATION_SCHEMA.tables where table_type != 'VIEW'
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @.Table_Name
--loop through tables
WHILE @.@.FETCH_STATUS = 0
BEGIN
print ''
print @.Table_Name
DECLARE Index_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@.Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
not exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @.Table_Name AND (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'UNIQUE') and
CONSTRAINT_NAME = name)
order by indid
OPEN Index_Cursor
FETCH NEXT FROM Index_Cursor
INTO @.IndexId, @.Index_Name
--loop through indexes
WHILE @.@.FETCH_STATUS = 0
begin
declare @.SQL_String varchar(256)
set @.SQL_String = 'drop index '
set @.SQL_String = @.SQL_String + @.Table_Name + '.' + @.Index_Name
set @.SQL_String = @.SQL_String + ';create '
if( (select INDEXPROPERTY ( OBJECT_ID(@.Table_Name) , @.Index_Name , 'IsUnique')) =1)
set @.SQL_String = @.SQL_String + 'unique '
if( (select INDEXPROPERTY ( OBJECT_ID(@.Table_Name) , @.Index_Name , 'IsClustered')) =1)
set @.SQL_String = @.SQL_String + 'clustered '
set @.SQL_String = @.SQL_String + 'index '
set @.SQL_String = @.SQL_String + @.Index_Name
set @.SQL_String = @.SQL_String + ' on '
set @.SQL_String = @.SQL_String + @.Table_Name
set @.SQL_String = @.SQL_String + '('
--form column list
SET @.IndexKey = 1
-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @.IndexKey <= 16 and INDEX_COL(@.Table_Name, @.IndexId, @.IndexKey)
IS NOT NULL
BEGIN
IF @.IndexKey != 1
set @.SQL_String = @.SQL_String + ','
set @.SQL_String = @.SQL_String + index_col(@.Table_Name, @.IndexId, @.IndexKey)
SET @.IndexKey = @.IndexKey + 1
END
set @.SQL_String = @.SQL_String + ')'
print @.SQL_String
EXEC (@.SQL_String)
FETCH NEXT FROM Index_Cursor
INTO @.IndexId, @.Index_Name
end
CLOSE Index_Cursor
DEALLOCATE Index_Cursor
--loop through unique constraints
DECLARE Contraint_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@.Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
exists( Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @.Table_Name AND CONSTRAINT_TYPE = 'UNIQUE' and CONSTRAINT_NAME = name)
order by indid
OPEN Contraint_Cursor
FETCH NEXT FROM Contraint_Cursor
INTO @.IndexId, @.Index_Name
--loop through indexes
WHILE @.@.FETCH_STATUS = 0
begin
set @.SQL_String = 'alter table '
set @.SQL_String = @.SQL_String + @.Table_Name
set @.SQL_String = @.SQL_String + ' drop constraint '
set @.SQL_String = @.SQL_String + @.Index_Name
set @.SQL_String = @.SQL_String + '; alter table '
set @.SQL_String = @.SQL_String + @.Table_Name
set @.SQL_String = @.SQL_String + ' WITH NOCHECK add constraint '
set @.SQL_String = @.SQL_String + @.Index_Name
set @.SQL_String = @.SQL_String + ' unique '
if( (select INDEXPROPERTY ( OBJECT_ID(@.Table_Name) , @.Index_Name , 'IsClustered')) =1)
set @.SQL_String = @.SQL_String + 'clustered '
set @.SQL_String = @.SQL_String + '('
--form column list
SET @.IndexKey = 1
-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @.IndexKey <= 16 and INDEX_COL(@.Table_Name, @.IndexId, @.IndexKey)
IS NOT NULL
BEGIN
IF @.IndexKey != 1
set @.SQL_String = @.SQL_String + ','
set @.SQL_String = @.SQL_String + index_col(@.Table_Name, @.IndexId, @.IndexKey)
SET @.IndexKey = @.IndexKey + 1
END
set @.SQL_String = @.SQL_String + ')'
print @.SQL_String
EXEC (@.SQL_String)
FETCH NEXT FROM Contraint_Cursor
INTO @.IndexId, @.Index_Name
end
CLOSE Contraint_Cursor
DEALLOCATE Contraint_Cursor
FETCH NEXT FROM Table_Cursor
INTO @.Table_Name
end
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
print ''
print 'Finished, Please check output for errors.'
====================
Any comments are very welcome.The error "Cannot resolve collation conflict for equal to operation" may be caused by you doing a query between 2 databases with different collations.
It can also happen if your database has a different collation to the server and you are using system stored procedures or tempory tables.
See above for how to change your database's collation.
Showing posts with label changing. Show all posts
Showing posts with label changing. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
Change Data Type
Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
Paul
Paul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>
|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT@.String = 'Hello'
SELECTDATALENGTH(@.String)
SELECTDATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
Paul
Paul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>
|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT@.String = 'Hello'
SELECTDATALENGTH(@.String)
SELECTDATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)
Change Data Type
Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage
e.g
DECLARE @.string VARCHAR(10
SELECT @.String = 'Hello
SELECT DATALENGTH(@.String
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10))
Regard
Juli
http://www.sqlporn.co.uk :o)
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage
e.g
DECLARE @.string VARCHAR(10
SELECT @.String = 'Hello
SELECT DATALENGTH(@.String
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10))
Regard
Juli
http://www.sqlporn.co.uk :o)
Change Data Type
Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requireme
nts. If you have long string lengths you need to remember that nvarchar will
double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT @.String = 'Hello'
SELECT DATALENGTH(@.String)
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requireme
nts. If you have long string lengths you need to remember that nvarchar will
double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT @.String = 'Hello'
SELECT DATALENGTH(@.String)
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)
Sunday, March 11, 2012
Change config file
Do I have to reboot after changing RSReportServer.config?
I am working with the alias and host names. I have the alias set to
true, but it only puts in john_g.
I need it to add '@.domain.com' Can that be done in the config using the
DefaultHostName? If so, just put domain.com in?
Thanks.You don't have to reboot, but you should restart the ReportServer service
(in Control Panel) after changing config files.
You might also want to run iisreset.exe, depending on the changes you are
making -- but that's more for when you have GUI code changes or are tweaking
custom extensions.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>|||Yes, you want to use the defaultHostName element. You do not need to reboot
after changing the config file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>
I am working with the alias and host names. I have the alias set to
true, but it only puts in john_g.
I need it to add '@.domain.com' Can that be done in the config using the
DefaultHostName? If so, just put domain.com in?
Thanks.You don't have to reboot, but you should restart the ReportServer service
(in Control Panel) after changing config files.
You might also want to run iisreset.exe, depending on the changes you are
making -- but that's more for when you have GUI code changes or are tweaking
custom extensions.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>|||Yes, you want to use the defaultHostName element. You do not need to reboot
after changing the config file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>
Change computer name while running SQL Server
I need to change the computer name on my Windows Server 2003. I am currently
running SQl Server, and I am trying to find out if changing the name will
affect the SQL server or not. Is there a process for doing this, or if I
change the name and reboot, will the name automatically change in the SQL
Server Services Manager?
Thanks,
JOE
"Joe Michl" <JoeMichl@.discussions.microsoft.com> wrote in message
news:094A1C97-34EE-49F2-B374-1BC7DF62A712@.microsoft.com...
> I need to change the computer name on my Windows Server 2003. I am
currently
> running SQl Server, and I am trying to find out if changing the name will
> affect the SQL server or not. Is there a process for doing this, or if I
> change the name and reboot, will the name automatically change in the SQL
> Server Services Manager?
As long as there are no applications coded for your specific server name you
should be OK. After changing the server name and rebooting, use the commands
in the following article:
http://support.microsoft.com/default...&Product=sql2k
Steve
running SQl Server, and I am trying to find out if changing the name will
affect the SQL server or not. Is there a process for doing this, or if I
change the name and reboot, will the name automatically change in the SQL
Server Services Manager?
Thanks,
JOE
"Joe Michl" <JoeMichl@.discussions.microsoft.com> wrote in message
news:094A1C97-34EE-49F2-B374-1BC7DF62A712@.microsoft.com...
> I need to change the computer name on my Windows Server 2003. I am
currently
> running SQl Server, and I am trying to find out if changing the name will
> affect the SQL server or not. Is there a process for doing this, or if I
> change the name and reboot, will the name automatically change in the SQL
> Server Services Manager?
As long as there are no applications coded for your specific server name you
should be OK. After changing the server name and rebooting, use the commands
in the following article:
http://support.microsoft.com/default...&Product=sql2k
Steve
change column width
Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar
80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NUL
L
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar
80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NUL
L
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
change column width
Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, Calin
Tester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, Calin
Tester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
change column width
Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
Change Column Order
How does one go about changing the column order of the data moving thru an SSIS package? It'd be nice to able to do this at any point in the package for readability, but where I really need it is in the Flat File Output. I need to deliver the columns in a specific order, and I can't figure out how to do that.
This seems to be such a basic question, but I can't find it in Search anywhere. My apologies if it's already been answered.The column order of a flat file is defined within the connection. Changing column order within the pipeline itself would be a bad ideas as this require moving data around, which costs. A way to do this as a view for readability may well be nice, but may end up being confusing as well.
The default behaviour of the Flat File Destination means you create a new connection within the UI, and that connection will happily define the columns according to the buffer, which is just perfect 99% of the time. For your case you have two workarounds-
1 - Add your Flat File Connection, set file and delimiters. Select the Advanced tab, and add your columns as you want in the correct order. This connection can then be selected, and you map the columns. You could do this within the Flat File Destination, as this will create all columns to start with, but cannot change order, which is a shame, so you will have to remove and insert columns to get the correct order overall.
2 - A simple method is to create a flat file that represents what you want to produce from SSIS. Then you can add a new Flat File Connection, and select the sample file, and allow the columns to be generated from this file. You may still wish to fine tune this through the Advanced tab, but it should do the bulk of the work.
Why not log some Feedback on MSDN for the ability to change column order within the Flat File connection as that is really what you want I belive.|||I had the exact same question...and I too couldn't believe that it wasn't simple to re-order the outputs being written to a flat file.
I am pulling data from a database using a SQL Query then adding columns with the Derived Column transform. I want the derived columns to go at the beginning of the output file, not the end.
I like your idea of reverse engineering a sample file...I will give that a shot.
I am currently evaluating ETL tools for converting data from various database types and structures to complex (multiple record types) flat files. Overall, I am pretty impressed with SSIS although I have noticed that some tasks are not as straightforward as they are in Sagent and Informatica.
|||If you are not already aware there are a couple of whitepapers that may help your evaluation-
Microsoft SQL Server: Forrester Report: Microsoft Addresses Enterprise ETL
(http://www.microsoft.com/sql/technologies/integration/foresterreport.mspx)
Well that's one. The other was a review of SSIS compared to Informatica, by Conchango. I can't find it now in the wake of the site updates for RTM. Hopefully it will reappear or someone will post a link. Searching microsoft.com only gives a broken link.
Don't forget you get a free RDBMS, OLAP and Reporting System when you buy SSIS :)|||
Very funny...you never know when that RDBMS may come in handy ; )
Thanks for the Forrester link.
I found the Conchango review....thanks for the lead.
http://download.microsoft.com/download/1/0/3/103fd39e-3ca4-4db7-a087-1263dc6ed0b1/CompIntTools.pdf
Our enterprise product is built pretty much from front to back on Microsoft technology so SSIS will be hard to beat if it can get the job done efficiently.
Thursday, March 8, 2012
change @@SERVERNAME 2005 - Urgent please help !!!
Hi There
I have changed the servername of a sql server 2005 instance. Using sp_dropserver and sp_addserver as per BOL.
However, after changing the servername i still see that :
EWX-JDGSQLSRV1\SQLServer2005SQLAgentUser$EWX-JDGSQLSRV1$MSSQLSERVER
EWX-JDGSQLSRV1\SQLServer2005MSSQLUser$EWX-JDGSQLSRV1$MSSQLSERVER
EWX-JDGSQLSRV1\SQLServer2005MSFTEUser$EWX-JDGSQLSRV1$MSSQLSERVER
built in logins still have the old server name.
My big concern is that these will no longer work?
Also i am changing not only servernames but doamins, i cannot find anythign on chaning domains and all that must be done.
Are the built in sql server logins still ok after i change the servername ? Even though they do not reflect the change?
Wjat else must i do before moving the server to a new domain (i will change the servcie accounts to use new domain accounts in the configuration manager?)
Thanx
Dietz:
Did you include the "local" tag when you called the sp_addserver stored procedure?
|||
Dave
Yes i did, i found the prblem though. Has nothing to do with that when i checked the sql log it could not execute sp_startup_agent, becuase agent XP's were not enabled, i could not enable agent XP's because lock pages in memory priviledges were not granted to the new sqlservice account.
So i granted the priviledges and enabled agent XP's and all was well.
Subscribe to:
Posts (Atom)