How to setup cluster ?

Hi,
I've 3 PC. 2 x Win2K AS and 1 Win2K Pro. Is it possible to cluster the 2
Win2K AS as Primary & Secondary while the Win2K Pro serves as the shared disk
server ?
Do I need to get a Raid card for Win2K Pro PC ?
Advice pls.
You might be able to use iSCSI on the XP box, though I have not tested it.
Other then that, you are asking for instructions that probably don't exist,
and if they did would probably not be supported. If you are in need of a
test environment, may I suggest VS 2005?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:BD968661-3225-4791-A263-2AEC7212A1A4@.microsoft.com...
> Hi,
> I've 3 PC. 2 x Win2K AS and 1 Win2K Pro. Is it possible to cluster the 2
> Win2K AS as Primary & Secondary while the Win2K Pro serves as the shared
> disk
> server ?
> Do I need to get a Raid card for Win2K Pro PC ?
> Advice pls.
|||Forget about using the Win2K Pro as the 'shared disk server' (I took 'shared
disk server' to mean file server). Get an external RAID controller and some
disks to serve as the shared disks, and use the two Win2K AS as the nodes.
Linchi
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:BD968661-3225-4791-A263-2AEC7212A1A4@.microsoft.com...
> Hi,
> I've 3 PC. 2 x Win2K AS and 1 Win2K Pro. Is it possible to cluster the 2
> Win2K AS as Primary & Secondary while the Win2K Pro serves as the shared
> disk
> server ?
> Do I need to get a Raid card for Win2K Pro PC ?
> Advice pls.
|||If this is for production please be sure that you have read the following
KB article and are aware of the limitations of a non-dertified solution:
327518 INF: The Microsoft Support Policy for a SQL Server Failover Cluster
http://support.microsoft.com/?id=327518
To setup a test enviroment you will need at least one external SCSI drive
or ability to setup using iSCSI
Dave Whitney
SQL Support
This posting is provided "AS IS" with no warranties, and confers no rights.

How to setup a backup server for SQL Server2005 for production (in case the primary server fails

I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..

you have two options one is Database mirroring and other is log shipping in sql 2005..........but i advise you to go with db mirroring as the downtime is less in high availability mode as you have a witness server and hence the failover will be automatic and within few seconds............but in log shipping only manual failover is possible........refer,

http://dumthata.com/sqlchn/articles/dbmrr.htm

http://dumthata.com/sqlchn/articles/lship.htm

Going forward pls post in the appropriate forum as it would be easier to read through and answer....

How to set the database to always use next increment number?

Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.

I think its good way if ID is incrementing by this you can come to know which record inserted when and so on ....

A good article on identity

http://msdn2.microsoft.com/en-us/library/ms971502.aspx

|||

it is possible but you have to do it yourself by switching identity insert ON and OFF in your code and taking care about inserted value. It is not recommended way because it slow down insert process and also it can destroy your related record information if you have any, If you would like to have records order information for some purposes you can always do select statement like:

select identity(int,1,1) new_ID, old_id
into #test
from yourtablename
select * from #test
order by new_ID

and next select by new_ID your result.
You can also use ROW_NUMBER:

select ROW_NUMBER() OVER(ORDER BY old_id ASC) [OrderNo] from from yourtablename

|||

Many thanks for all your responses. The reason I was thinking about doing that is because this table is for linking pages. These links will be added and deleted often..and if there is no way to keep the ID in order, I'm afraid the ID may get too big but with less than 40 or 50 actual records in the table.

|||

There is no inbuilt solution for your problem.. you need to use alternative methods..

2 of those methods are discusses here.

http://www.sqlteam.com/item.asp?ItemID=765

|||

Many thanks for the link. It looks like I could use this:

declare @.intCounter int
set @.intCounter = 0
update Yaks
SET @.intCounter = YakSequenceNumber = @.intCounter + 1

The question I have is, do I run the above code everytime when I want to update and insert? Or do I run this code at the begingin where there is no record yet in the table?

|||

I think you need to use this while you are inserting. Is this make sense

|||

Okay, so can you give me a sample code of how I would use this while inserting in C# code behind? I'm still unclear as to how I would use it. By the way, if I'm using this, do I turn of the column Identity?

|||

I will create one example for you can give it to you

|||

This one solution has one big disadvantage that it will slow down your insert so maybe you should run this one time a day(or hour) for your table instead of running it on every insert?

But solution is very nice

How to set primary Key

hey can any one pls reply me how to set primary key in a table.

pls post a query.

thanks

Head over to the Transact-SQL forum for query related help.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

How to set full-text indexing on table which has multi-columns as primary key?

Dear sirs,
How to set full-text indexing on table which has multi-columns as primary
key?
Thanks
ABC,
Unfortunately, it is not directly possible in any SQL Server versions,
including SQL Server 2005 as you must have a unique, single non-nullable
column to FT-enabled a table... That said, the workaround is easy. Alter the
table and add a int column with the identity property, for example:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
go
-- Alter Table: add unique, single non-nullable column
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
exec sp_help EmployeeTerritories
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"ABC" <abc.abc.com> wrote in message
news:%23$mqjDlwFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Dear sirs,
> How to set full-text indexing on table which has multi-columns as primary
> key?
> Thanks
>
|||Thanks, It will be a large great workload to alter all tables (over 200
tables).
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O7mMWulwFHA.1148@.TK2MSFTNGP11.phx.gbl...
> ABC,
> Unfortunately, it is not directly possible in any SQL Server versions,
> including SQL Server 2005 as you must have a unique, single non-nullable
> column to FT-enabled a table... That said, the workaround is easy. Alter
> the table and add a int column with the identity property, for example:
> use Northwind
> go
> exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
> (EmployeeID, TerritoryID)
> go
> -- Alter Table: add unique, single non-nullable column
> ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
> go
> CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
> go
> exec sp_help EmployeeTerritories
>
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "ABC" <abc.abc.com> wrote in message
> news:%23$mqjDlwFHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||You're welcome, ABC,
Unfortunately, this limit is by design and *might* be lifted in the next
version of SQL Server, post-SQL Server 2005.
However, if you're FT-enabling over 200 tables, you should be aware of
another SQL Server 2000 (and not SQL 2005) limit of 256 FT Catalogs per
server (machine). I'd recommend that you create a limited number of related
FT Catalogs and then use these to place more than one Ft-enabled table in
each FT Catalog.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"ABC" <abc.abc.com> wrote in message
news:efXQuMnwFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks, It will be a large great workload to alter all tables (over 200
> tables).
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7mMWulwFHA.1148@.TK2MSFTNGP11.phx.gbl...
>

How to set a primary key constraint in a View table using SQL Server 2005

Hi All,

I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!

now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?

many thanks

You can't apply PRIMARY KEY constriant on views.|||

Hi,

Is there any way in which i can state one of the columns to be a unique identifier? as i want to be able to edit my datagrid which is populating a VIEW table from SQL '05 but i cannot do so as i do not have a unique number to identify the row, even though one of the columns in the table is a PK in its original table.

any ideas?

|||

If you just want a unique column to identify a row,newid() function is good for you, which can be used in your CREATE VIEW statements:

use northwind
go
create view v_test as
select o.OrderID, C.ContactName,newid() as ColID
from Orders o join Customers c
on o.CustomerID=C.CustomerID

How to set a key for a field in a data table

In my SQL Server 2005 database I have created a table with several data fields. One of the fields is designated as a primary key. And another one is just a key.

I can easily set the first field as the primary key, but am not able to set the second field as being just a key field.

I perform these steps to set the primary key.

(1) Right click on the table name and then select 'Open Table Definition'

(2) I right click on the field in my data table and select 'Select Primary Key'

As I mentioned previously, to set another field up as just a key field, I am not able to do this. My choices are the following when I right click on the data field to be designated as the key field:

Set Primary Key, Insert Column, Delete Column,Relationships,Indexes/Keys,Full Text Index,XML Indexes, Check constraints,Properties.

None of these choices will allow me to set the field up as a key field.

Someone please help me out with this problem.

I'm not sure what you're asking for but I can make some guesses. A Primary Key uniquely identifies each record. Therefore there can only be a single primary key for a table. However, a PK can be composed of more than 1 field.

If, however, you have one field which is the key and a second field which contains unique values, then you will want the first field as the key and add an index or constraint for the second field.

HTH.

How to sequentially SORT numbers that also have a letter attached?

Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.