Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

sql

Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

Mirroring + remoting

Hi,

I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:

1) Client .NET app on client systems with remoting connection to...

2) Host .NET app with a DAL connecting to ...

3) SQL Server 2005 mirrored

Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.

My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?

There are two setups I have in mind:

1) Host app + SQL on same box

2) Host app on its own server + SQL on its server (+mirror)

With these scenarios:

Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.

Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.

Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.

This is new to me, so if anyone can advise, or direct me to the right info.

Hi,

I assume your solution employs the Presentation Layer <-> Business Layer <-> Data Layer architecture. Since you want to maximise transparency to the client application (pres layer), in my opinion, it's better to go with option 2 and put the host app on a separate server from the database to decrease the probability of multiple failures (if the machine with host + sql goes down - both will be inaccessible).

If you do this and code the host app to use ADO.NET to connect to the database, you could take advantage of the ability of the SqlConnection object to automatically re-connect to the failover partner in a mirrored configuration. This MSDN article explains how this works - http://msdn2.microsoft.com/en-us/library/5h52hef8.aspx.

In addition, if you want complete transparency to the client application, you will still have to make it handle the case where the host fails.

-- Kaloian.

|||

Thanks Kaloian,

That leaves me with the problem of having the clinet apps fail over to another server when the host app's server fails. I guess I'm asking is there a remoting option to fail over, similar to the Failover Partner attribute in the sql connection string? Or am I going to have to trap the failure and code the switch?

|||

As far as I know there's no such mechanism, but you may want to post this question to the .NET Remoting newsgroup - http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=41&SiteID=1

- Kaloian.

Mirroring + remoting

Hi,

I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:

1) Client .NET app on client systems with remoting connection to...

2) Host .NET app with a DAL connecting to ...

3) SQL Server 2005 mirrored

Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.

My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?

There are two setups I have in mind:

1) Host app + SQL on same box

2) Host app on its own server + SQL on its server (+mirror)

With these scenarios:

Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.

Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.

Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.

This is new to me, so if anyone can advise, or direct me to the right info.

Hi,

I assume your solution employs the Presentation Layer <-> Business Layer <-> Data Layer architecture. Since you want to maximise transparency to the client application (pres layer), in my opinion, it's better to go with option 2 and put the host app on a separate server from the database to decrease the probability of multiple failures (if the machine with host + sql goes down - both will be inaccessible).

If you do this and code the host app to use ADO.NET to connect to the database, you could take advantage of the ability of the SqlConnection object to automatically re-connect to the failover partner in a mirrored configuration. This MSDN article explains how this works - http://msdn2.microsoft.com/en-us/library/5h52hef8.aspx.

In addition, if you want complete transparency to the client application, you will still have to make it handle the case where the host fails.

-- Kaloian.

|||

Thanks Kaloian,

That leaves me with the problem of having the clinet apps fail over to another server when the host app's server fails. I guess I'm asking is there a remoting option to fail over, similar to the Failover Partner attribute in the sql connection string? Or am I going to have to trap the failure and code the switch?

|||

As far as I know there's no such mechanism, but you may want to post this question to the .NET Remoting newsgroup - http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=41&SiteID=1

- Kaloian.

mirroring

hello

i used following query to create endpoint

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_A_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

i got the following error msg:

Msg 1498, Level 16, State 2, Line 11

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online.

any help plzz

thanks in advance

Hello

Support for Database mirroring in SQL Server 2005 as a feature was officially availble in SQL Server 2005 Service Pack 1. Before Service Pack 1 in RTM build you could still use this feature but after Enabling Trace Flag 1400 DBCC TRACEON(1400,-1). I would suggest upgrading to SQL Server 2005 SP1 or SP2.

Hope this helps

Vishal Gandhi

|||

thanks vishal

i downloaded sp2 i check its created the endpoints properly, let me check whether mirroring will work proper or not.

sql

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

sql

minvalue and maxvalue in sequence

Hello everyone:

I try to create a sequence with following requirements:

create a sequence that will generate integers starting with the value 9. Each value should be three less than the previous value generated. The lowest possible balue the sequence should be allowed to generate is -1, and it should not be allowed to cycle.

I think values generated by this sequence are 9, 6, 3, 0

my sql statement goes like this:

SQL> create sequence MY_FIRST_SEQUENCE
2 increment by -3
3 start with 9
4 minvalue -1
5 nocycle;
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04004: MINVALUE must be less than MAXVALUE

I don't know what's the maxvalue in this squence. Should it be 9 if that's the case. why do I get a error message?

Plase give me some suggestio on how can I make this sequence work. Thanks!

sjgrad03
12-03Even without knowing anything about sequences, if this is a DESCENDING sequence and it STARTS WITH 9, then it is logical that its MAXIMUM VALUE is 9.

Your statement will be executed properly if you add MAXVALUE n, where n >= START_WITH_value. If it is not clear enough, here's an example:

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 9
MINVALUE -1
INCREMENT BY -3;

or

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 1000
MINVALUE -1
INCREMENT BY -3;

NOCYCLE is the default and it is not necessary to specify it.

Read more about creating Oracle sequences here (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm).

Minor Table Insert help

i have the following code, it all works how i want it to bar the first time it runs, when i run the program and insert the data the first time, it inserts the data twice, all other times only once or the update.

$dbh=mysql_connect ("localhost", "twqwwsoy_user", "iiyama") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("twqwwsoy_resources");

$count="SELECT COUNT(message) FROM Diary";
$result = mysql_query($count);
$co = mysql_result($result,$x);

if ($co == 0) {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {

$count="SELECT COUNT(username) FROM Diary WHERE username = '$username' AND day_id = '$day'";
$result = mysql_query($count);
$co1 = mysql_result($result,$x);
echo "$co1";
}

if ($co1 == 1) {
$SQL= "UPDATE Diary SET message = '$message' WHERE username = '$username' AND day_id= '$day'";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}I don't speak MySQL, but it seems like this: when the table 'diary' is empty, you are running the script for the first time:co = 0 and co1 = 0

IF co = 0 (yes, it is) THEN
INSERT INTO diary ... -> this is executed
ELSE
SELECT COUNT ... -> this is not executed
END IF

IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this statement performs second insertRunning the script second (and every other) time:IF co = 0 (no, it isn't) THEN
INSERT INTO diary ...
ELSE
SELECT COUNT ... -> it is executed and co1 = 2
END IF

IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this is executed
END IFIf I'm not wrong, this is what happens. But, you didn't say what you wanted to happen ... Anyway, I guess you'll have to adjust logic a little bit.

Minor incompatibility between 2000 and 2005

Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)Hello Sergey Lipnevich sergey.at.optimaltec.com,

> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features. :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).

> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!

> These are both known features. No, really. Features. :)
Known... Yes. Features... I don't know :-).
Sergey.|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.

Minor incompatibility between 2000 and 2005

Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)
Hello Sergey Lipnevich sergey.at.optimaltec.com,

> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).

> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!

> These are both known features. No, really. Features.
Known... Yes. Features... I don't know :-).
Sergey.
|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.
|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.

Minimum Setup Requirements for SQL 2005 Replication.

What are the minimum setup requirements to support the following.
2 Remote Laptops running SQL Server 2005 Express databases with the ability
to synchronize with each other. Also they should be able to synch with a
server hosting a SQL Database whci is accessible from 2 other Desktop PCs.
What are the SQL CAL Licensing issues that would apply to this scenario.
Regards
Unfortunately SQL Server Express can only be a subscriber to all types of
replication. All subscriptions must be created through RMO or replication
stored procedures.
Regarding your desktop pc's - If they are running workgroup edition they can
have up to 25 merge subscribers, but 5 transactional subscribers. In regards
to licensing anything which accesses the desktop must have a license, which
can be a CAL or a server per processor license (which will not make a whole
lot of sense).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stuart Lowe" <StuartLowe@.discussions.microsoft.com> wrote in message
news:6C1471E6-61F1-4BF2-AAA8-16E67A29A967@.microsoft.com...
> What are the minimum setup requirements to support the following.
> 2 Remote Laptops running SQL Server 2005 Express databases with the
> ability
> to synchronize with each other. Also they should be able to synch with a
> server hosting a SQL Database whci is accessible from 2 other Desktop PCs.
> What are the SQL CAL Licensing issues that would apply to this scenario.
> Regards
sql

minimum price on earliest date

Ok my first posted question :
(This is related to a travel website)

I have the following table layout :

CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;

The package table contains a list (a very large one) for holiday accomodation packages.

What i'm trying to get is the following :

The MINIMUM price using the following for "price" :

CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))

AND

The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.

Any thoughts?

With any luck I will be able to give you an test data insert for this.

This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :

SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code

At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :

SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start

In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.

Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me

i would not use the same alias "p" in more than one place in the query

and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.

What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189

They both match the minimum price and date and thus both records are output. DOH!

As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql

Minimum components required to create 2005 database/tables programmatically

I want to know what are the minimum SQL Server 2005 tools required to perform the following task, and how do I install them:

1. I have a server with SQLS 2005 (and all of its management components) installed. Let's call this Server A. I want to create a database and tables in an instance on this machine.

2. I have another machine (Workstation B) on the same domain from which I want to programmatically create the database on Server A with legacy code that uses SQL-DMO.

What are the minimum components (i.e. SQLDMO for 2005?) I need to install on Workstation B and how/where are they available/packaged? I know that SMO is the replacement for SQLDMO, but we're not ready to upgrade to it yet and we still want to create databases on the 2005 instance.

Thanks in advance,

jd

You just have to install SQLDMO for SQL 2005. It is available as a separate download as part of SQL Server 2005 Feature Pack.

You can get it from here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Regards,

Minimum components required to create 2005 database/tables programmatically

I want to know what are the minimum SQL Server 2005 tools required to perform the following task, and how do I install them:

1. I have a server with SQLS 2005 (and all of its management components) installed. Let's call this Server A. I want to create a database and tables in an instance on this machine.

2. I have another machine (Workstation B) on the same domain from which I want to programmatically create the database on Server A with legacy code that uses SQL-DMO.

What are the minimum components (i.e. SQLDMO for 2005?) I need to install on Workstation B and how/where are they available/packaged? I know that SMO is the replacement for SQLDMO, but we're not ready to upgrade to it yet and we still want to create databases on the 2005 instance.

Thanks in advance,

jd

You just have to install SQLDMO for SQL 2005. It is available as a separate download as part of SQL Server 2005 Feature Pack.

You can get it from here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Regards,

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
Eval
Pleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval
|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId = '1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId => '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

min value for a date

Hi,
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147-- Legacy falcon
and R.room_type_id = 72-- Double room
and R.board_type = 6-- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
@.year
and r.min_nights = 1-- Standard type
and a.date >= getDate()-- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
100268231002008-02-08 00:00:00.000
100268241502008-02-08 00:00:00.000
100268241502008-02-09 00:00:00.000
100268231002008-02-10 00:00:00.000
100268231002008-02-11 00:00:00.000
100268231002008-02-13 00:00:00.000
100268231002008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?
Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
|||Hi
it seems that rate_id is not needed in the group by
selectmin(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
fromhotel_acc_rates R
joinhotel_acc_rate_avail A on R.rate_id = A.rate_id
wherer.hotel_id = 1147-- Legacy falcon
and R.room_type_id = 72-- Double room
and R.board_type = 6-- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
@.year
and r.min_nights = 1-- Standard type
and a.date >= getDate()-- we dont go back in time
group byA.date
order byA.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
selectmin(r.rate) MinRate, A.date
fromhotel_acc_rates R
joinhotel_acc_rate_avail A on R.rate_id = A.rate_id
wherer.hotel_id = 1147-- Legacy falcon
and R.room_type_id = 72-- Double room
and R.board_type = 6-- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
@.year
and r.min_nights = 1-- Standard type
and a.date >= getDate()-- we dont go back in time
group byA.date
) b ON b.date = c.date AND b.minrate = s.rate
order byb.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:

> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147-- Legacy falcon
> and R.room_type_id = 72-- Double room
> and R.board_type = 6-- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=
> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=
> @.year
> and r.min_nights = 1-- Standard type
> and a.date >= getDate()-- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 100268231002008-02-08 00:00:00.000
> 100268241502008-02-08 00:00:00.000
> 100268241502008-02-09 00:00:00.000
> 100268231002008-02-10 00:00:00.000
> 100268231002008-02-11 00:00:00.000
> 100268231002008-02-13 00:00:00.000
> 100268231002008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>
|||On Feb 11, 2:41Xpm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15Xpm, Rippo <i...@.rippo.co.uk> wrote:
>
>
>
>
>
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> X X X X r.Rate_id, min(r.rate) MinRate, A.date
> from
> X X X X hotel_acc_rates R
> inner join
> X X X X hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> X X X X r.hotel_id = 1147 X X X X X X X X X X X -- Legacy falcon
> X X X X and R.room_type_id = 72 X X X X -- Double room
> X X X X and R.board_type = 6 X X X X X X-- Bed and breakfast
> X X X X and month(R.valid_from_date) <= @.month and
> year(R.valid_from_date) <=
> @.year
> X X X X and month(R.valid_to_date) >= @.month and year(R.valid_to_date)>=
> @.year
> X X X X and r.min_nights = 1 X X X X X X-- Standard type
> X X X X and a.date >= getDate() X X X X X X X-- wedont go back in
> time
> group by
> X X X X r.Rate_id, A.date
> having min(r.rate) =
> (select min(s.rate)
> from
> X X X X hotel_acc_rates S
> inner join
> X X X X hotel_acc_rate_avail B on S.rate_id = B.rate_id
> where
> X X X X r.hotel_id = 1147 X X X X X X X X X X X -- Legacy falcon
> X X X X and S.room_type_id = 72 X X X X -- Double room
> X X X X and S.board_type = 6 X X X X X X-- Bed and breakfast
> X X X X and month(S.valid_from_date) <= @.month and
> year(S.valid_from_date) <=
> @.year
> X X X X and month(S.valid_to_date) >= @.month and year(S.valid_to_date)>=
> @.year
> X X X X and s.min_nights = 1 X X X X X X-- Standard type
> X X X X and b.date >= getDate() X X X X X X X-- wedont go back in
> time
> X X X X and R.Rate_id = S.Rate_id
> X X X X and A.date = B.date
> group by
> X X X X s.Rate_id, B.date
> )
> order by
> X X X X A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
...
and A.date = B.date
group by
B.date
)
HTH