Tuesday, March 27, 2012
Changing column from nvarchar to int
from text to a foreign key from another table. This is no problem and I
already have the query batch to do this. I would like to know if it is
possible to change a column type from nvarchar to int.
Thanks,
Drew LaingAs long as all the NVARCHAR values can be converted to an INT, sure.
ALTER TABLE tablename ALTER COLUMN columnname INT
http://www.aspfaq.com/
(Reverse address to reply.)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>|||Drew,
You can do it if the values are numeric or null. If the value can not be
casted, then you will get an error.
Example:
use northwind
go
create table t (
colA varchar(15)
)
go
insert into t values('1')
insert into t values('2')
insert into t values(null)
go
select * from t
go
alter table t
alter column colA int
go
select * from t
go
alter table t
alter column colA varchar(15)
go
insert into t values ('a')
go
-- will fail
alter table t
alter column colA int
go
select * from t
go
drop table t
go
AMB
"Drew" wrote:
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
>|||Thanks to both!
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
>I am upgrading an Access database and need to convert some of the fields
>from text to a foreign key from another table. This is no problem and I
>already have the query batch to do this. I would like to know if it is
>possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
Sunday, March 25, 2012
Changing collation in SQL 2000
Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?
Thank you
GarryHi Garry, I am sorry but this is the wrong forum to ask this question. I hope you can or did ask in http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80 (the SSIS/DTS forum).|||You can change the database collation using the statement ALTER DATABASE ... COLLATE ... - see Books Online for more information.
Make sure tempdb has a compatible collation. Tempdb is used for several operations (e.g. sorting intermediate results) and a discrepency will result in incorrect or failed execution. In that case, you may want to change the server's collation. This is done by rebuilding the master database specifying the new collation in the SQLCOLLATION property of the setup command. Books online contains more information.
Thank you.|||
Thanks Shankar,
I was able to change the collation of the database using the ALTER DATABASE, however, in the the accounting system, for which SQL 2000 serves as the backend, we are getting an error on a report query we are trying to run. The error states: "Collation cannot be resolved". I checked the KB and there is an article stating that it could be a conflict with the collation in the Tempdb and the accounting database, and that is what the software support for the accounting system is telling me. When I check the properties of the accounting database and the tempdb, however, they are the same. That was the purpose of running the ALTER DATABASE command in the first place.
Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?
Thank you,
Garry
Changing the server's collation would be the more reliable way if that is a choice.
Thank you,
Shankar
Program Manager, Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights|||
Hi Shankar,
Can we change collation per query, while keeping a standard collation for the table. I just asking whether is it possible to specify the collation when running a SQL query, without altering collation on table or column.
Thanks,
Roshan.
Changing collation in SQL 2000
Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?
Thank you
GarryHi Garry, I am sorry but this is the wrong forum to ask this question. I hope you can or did ask in http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80 (the SSIS/DTS forum).|||You can change the database collation using the statement ALTER DATABASE ... COLLATE ... - see Books Online for more information.
Make sure tempdb has a compatible collation. Tempdb is used for several operations (e.g. sorting intermediate results) and a discrepency will result in incorrect or failed execution. In that case, you may want to change the server's collation. This is done by rebuilding the master database specifying the new collation in the SQLCOLLATION property of the setup command. Books online contains more information.
Thank you.|||
Thanks Shankar,
I was able to change the collation of the database using the ALTER DATABASE, however, in the the accounting system, for which SQL 2000 serves as the backend, we are getting an error on a report query we are trying to run. The error states: "Collation cannot be resolved". I checked the KB and there is an article stating that it could be a conflict with the collation in the Tempdb and the accounting database, and that is what the software support for the accounting system is telling me. When I check the properties of the accounting database and the tempdb, however, they are the same. That was the purpose of running the ALTER DATABASE command in the first place.
Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?
Thank you,
Garry
Changing the server's collation would be the more reliable way if that is a choice.
Thank you,
Shankar
Program Manager, Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights|||
Hi Shankar,
Can we change collation per query, while keeping a standard collation for the table. I just asking whether is it possible to specify the collation when running a SQL query, without altering collation on table or column.
Thanks,
Roshan.
Sunday, March 11, 2012
Change Unique ID to non unique ID in replicated table
I have a replicated to table. It has the ID column as unique and I want it
to convert it into non unique. How can I do it?
Exactly what do you mean by non-unique. There are three kinds of indexes,
clustered, non clustered or unique.
Unique means an index or actually a constraint that uses an index, which
ensures unique values and allows a null. A non-clustered index is an index
that is maintained on a seperate database page containing pointers which
point back to where in the table a particular value occurs. A clustered
index orders the data in the table according to a criteria.
To change an index you do this
sp_help tablename and obtain the index name
then drop the index
drop index authors.aunmind
then recreate it (if you want to convert the index to a clustered or
non-clustered index).
I assume you are talking about the table on the subscriber.
If you want to replicate a primary key which occurs on a published table on
the publisher to a primary key on the subscriber right click on yoru
publication, select properties, click the articles tab, and click the three
ellipses next to your article you wish to modify the index on. Then in the
snapshot tab, check include DRI. By default primary keys are replicated as
unique indexes on the subscriber.
HTH
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Vivek" <vivek@.nospam.com> wrote in message
news:uMeeotwjEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a replicated to table. It has the ID column as unique and I want
it
> to convert it into non unique. How can I do it?
>
|||Hi Hillary,
Thanks for your reply. I think I should have framed my query differently.
I have a table on publisher with a primary key. What we intend to do is get
rid of the primary key so that we can insert any number and the following
records. Now, how do I can I change the column to be not primary key.
Thanks
Vivek
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Dv3VS1jEHA.1520@.TK2MSFTNGP10.phx.gbl...
> Exactly what do you mean by non-unique. There are three kinds of indexes,
> clustered, non clustered or unique.
> Unique means an index or actually a constraint that uses an index, which
> ensures unique values and allows a null. A non-clustered index is an index
> that is maintained on a seperate database page containing pointers which
> point back to where in the table a particular value occurs. A clustered
> index orders the data in the table according to a criteria.
> To change an index you do this
> sp_help tablename and obtain the index name
> then drop the index
> drop index authors.aunmind
> then recreate it (if you want to convert the index to a clustered or
> non-clustered index).
> I assume you are talking about the table on the subscriber.
> If you want to replicate a primary key which occurs on a published table
on
> the publisher to a primary key on the subscriber right click on yoru
> publication, select properties, click the articles tab, and click the
three
> ellipses next to your article you wish to modify the index on. Then in the
> snapshot tab, check include DRI. By default primary keys are replicated as
> unique indexes on the subscriber.
> HTH
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Vivek" <vivek@.nospam.com> wrote in message
> news:uMeeotwjEHA.2908@.TK2MSFTNGP10.phx.gbl...
> it
>
|||You will have to alter the table to drop the constraint
create table primarykey
(pk int not null identity(1,1) primary key)
GO
sp_help primarykey
GO
--scroll down until you see the name of the primary key - in our case its
called PK__primarykey__52AE4273
alter table primarykey
drop constraint PK__primarykey__52AE4273
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Vivek Sharma" <joebloggs@.news.com> wrote in message
news:OV7xLN9jEHA.2412@.TK2MSFTNGP15.phx.gbl...
> Hi Hillary,
> Thanks for your reply. I think I should have framed my query differently.
> I have a table on publisher with a primary key. What we intend to do is
get[vbcol=seagreen]
> rid of the primary key so that we can insert any number and the following
> records. Now, how do I can I change the column to be not primary key.
> Thanks
> Vivek
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Dv3VS1jEHA.1520@.TK2MSFTNGP10.phx.gbl...
indexes,[vbcol=seagreen]
index[vbcol=seagreen]
> on
> three
the[vbcol=seagreen]
as[vbcol=seagreen]
want
>
|||Thanks a lot Hilary. It was a great help.
Vivek
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:elqZKECkEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> You will have to alter the table to drop the constraint
> create table primarykey
> (pk int not null identity(1,1) primary key)
> GO
> sp_help primarykey
> GO
> --scroll down until you see the name of the primary key - in our case its
> called PK__primarykey__52AE4273
> alter table primarykey
> drop constraint PK__primarykey__52AE4273
> GO
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Vivek Sharma" <joebloggs@.news.com> wrote in message
> news:OV7xLN9jEHA.2412@.TK2MSFTNGP15.phx.gbl...
differently.[vbcol=seagreen]
> get
following[vbcol=seagreen]
> indexes,
which[vbcol=seagreen]
> index
which[vbcol=seagreen]
clustered[vbcol=seagreen]
table[vbcol=seagreen]
> the
replicated
> as
> want
>
Wednesday, March 7, 2012
Change the DATETIME format to DD/MM/YYYY by using CONVERT command.
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
hi Paul,
amripaul@.gmail.com wrote:
> I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
please have a look at CONVERT function in BOL:
SET NOCOUNT ON;
SELECT CONVERT(varchar(10), GETDATE(), 103) AS [my formatted date];
--<--
my formatted date
11/06/2007
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply