Help getting mirroring working

Hello guys and gals,

I've having some trouble getting DB mirroring set up in Sql Server 2005. Perhaps someone can help, as I've read everything I can find on the web and Google groups about this but still can't solve it.

Here are the steps I have taken:

1. Installed a fresh Windows 2003 on box A (the primary) and a fresh Windows XP on box B (the mirror).
2. Run Windows update on both.
3. Installed Sql Server Enterprise on box A and box B, and also on my own machine box W (the witness). All good so far, I can connect to all machines, all machines can ping each other, all machines can telnet to each other on port 5020 (the 16 keystroke thing), and I can create databases on all machines. No boxes are on a domain so all are using the sa login.
4. Created my test database on box A - just a single table for now.
5. Backed up the DB (recovery set to FULL) and restored on box B - all working.
6. Ran the Configure Security wizard on box A.
7. Elected to configure a witness, selected defaults for everything (port 5022, encryption on, endpoint names "Mirroring", sa login for all three machines).
8. Left blank the Service Accounts for all three boxes (it says: "Leave the textboxes empty if all instances use the same account [they do], the accounts are non-domain accounts [they are non-domain accounts], or the accounts are in untusted domains [not on a domain, so this should be correct, right?]").
9. Finished the wizard, everything successful.
10. Ensured the server network addresses are correct - TCP://bill-primary.ourdomainname.com:5022 etc.
11. Clicked the Start Mirroring button.
12. Receive the dreaded 1418 error: "The server network address "TCP://bill-primary.ourdomainname.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"
13. AGH!

The latest entries from the primary's error log are shown below (IP 10.152.58.243 is the mirror).
The only thing I haven't tried is setting up manually through SQL statements, but this looks extremely messy and would basically just be trial and error (so what's new, you ask?).

Any help appreciated.

--
Mike

Error log from primary machine (10.152.58.242):
2006-03-16 13:57:00.26 spid53 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 13:57:02.28 spid53 Server is listening on [ 'any' <ipv4> 5022].
2006-03-16 13:57:02.28 spid53 The Database Mirroring protocol transport is now listening for connections.
2006-03-16 13:57:29.14 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:29.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:29.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:30.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:32.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:35.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:37.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:40.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:42.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:45.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:11.25 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:13.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:14.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:15.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:17.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:19.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:22.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:24.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:27.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:29.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:00:51.12 spid21s Error: 9642, Severity: 16, State: 3.
2006-03-16 14:00:51.12 spid21s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 14:09:00.04 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:00.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:00.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:01.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:03.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:06.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:08.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:11.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:13.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:16.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:14:51.70 spid25s Error: 1474, Severity: 16, State: 1.
2006-03-16 15:14:51.70 spid25s Database mirroring connection error 4 'An error occurred while receiving data: '10054(error not found)'.' for 'TCP://bill-secondary.globalgossip.net:5022'.
2006-03-16 15:15:09.42 spid23s Error: 1443, Severity: 16, State: 2.
2006-03-16 15:15:09.42 spid23s Database mirroring has been terminated for database 'failover_test'. This is an informational message only. No user action is required.
2006-03-16 15:27:56.39 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:56.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:57.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:58.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:00.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:02.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:05.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:07.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:10.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:12.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:31:26.46 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:26.46 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:31:37.18 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:37.18 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:31:56.76 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:56.76 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:48:52.42 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:52.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:53.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:54.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:56.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:58.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:01.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:03.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:06.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:08.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:08:41.64 spid56 The Database Mirroring protocol transport has stopped listening for connections.
2006-03-16 16:08:43.64 spid56 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 16:31:05.75 spid51 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 16:31:07.75 spid51 Server is listening on [ 'any' <ipv4> 5022].
2006-03-16 16:31:07.75 spid51 The Database Mirroring protocol transport is now listening for connections.
2006-03-16 16:31:14.14 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:14.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:14.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:15.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:17.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:20.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:22.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:25.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:27.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:30.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:17.00 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:19.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:19.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:20.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:22.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:25.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:27.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:30.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:32.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:35.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
Should have also mentioned:

3.5. Enabled the -T 1400 flag in the Configuration Manager on all three machines.

Cheers,

Mike
|||

The problem is security. If you want to solve the problem quickly, I would add the machines to a domain (assuming you have a domain), and make sure that all the SQL processes are running as the same user which is the SA on all machines everything will be fine.

The problem is that although you run as SA on server A, that user is a nobody on server B. Server A tries to login using the account that it runs as; as far as B is concerned, that user is anonymous.

Additional points:

1. You mention that you telnet to 5020, but it looks like you setup the endpoints to listen in on 5022.

2. You could use certificates (search BOL) to setup mirroring for servers that are not in trusted domains.

3. You may also have to backup and restore the transaction log once.

4. The T-SQL is very easy to do. See the examples in BOL.

Thanks,

Mark

|||Thanks for your reply.

The telnet port was a typo.

My understanding is now that the mirroring security wizard only works when the machines are on the same domain. If they are on different domains then it is necessary to create certificates using T-SQL to enable trusted endpoint comminication. Is this correct?

I have attempted, so far unsuccessfuly to create certificates in this manner - please see my subsequent post. If there really is no solution to this problem then I guess we'll have to go down the route of putting both servers in the same domain. However, we are trying to write a self-contained application and it would be best if we didn't have to worry about introducing a domain controller into the setup.

Any advice from a guru such as yourself would be much appreciated.

Mike
|||

SP1 Community Technology Preview release
In this version it should be working without setting the flag "-t 1400“.
I have been tested the mirroring since the Release was released, but nothing work
Can anybody help me?
After I agree "Start Mirroring" always the same failure is pop up!
Translation from German "for database XXXX is morroring not configured”.

Can anybody reproduce this ?

|||

What do you mean "nothing work"?

Are you saying that you cannot setup database mirroring?

I would recommend looking in BOL in the section on troubleshooting database mirroring setup.

It is more than likely a security issue.

Thanks,

Mark

|||

If you can, set mirroring up using a domain admin account on all machines, If that works then it will soon tell you have permissions problems when running under other accounts.

I found it easier to set mirroring up in T-SQL than the GUI, as it provides all the code for you anyway and you get a bit more idea on what's actually going on

|||

Ensure that windows services account through which sql server is running is added in "access this computer from network" under security policy.

This should solve the problem

Manohar Kulkarni

SQLDBA

Help getting mirroring working

Hello guys and gals,

I've having some trouble getting DB mirroring set up in Sql Server 2005. Perhaps someone can help, as I've read everything I can find on the web and Google groups about this but still can't solve it.

Here are the steps I have taken:

1. Installed a fresh Windows 2003 on box A (the primary) and a fresh Windows XP on box B (the mirror).
2. Run Windows update on both.
3. Installed Sql Server Enterprise on box A and box B, and also on my own machine box W (the witness). All good so far, I can connect to all machines, all machines can ping each other, all machines can telnet to each other on port 5020 (the 16 keystroke thing), and I can create databases on all machines. No boxes are on a domain so all are using the sa login.
4. Created my test database on box A - just a single table for now.
5. Backed up the DB (recovery set to FULL) and restored on box B - all working.
6. Ran the Configure Security wizard on box A.
7. Elected to configure a witness, selected defaults for everything (port 5022, encryption on, endpoint names "Mirroring", sa login for all three machines).
8. Left blank the Service Accounts for all three boxes (it says: "Leave the textboxes empty if all instances use the same account [they do], the accounts are non-domain accounts [they are non-domain accounts], or the accounts are in untusted domains [not on a domain, so this should be correct, right?]").
9. Finished the wizard, everything successful.
10. Ensured the server network addresses are correct - TCP://bill-primary.ourdomainname.com:5022 etc.
11. Clicked the Start Mirroring button.
12. Receive the dreaded 1418 error: "The server network address "TCP://bill-primary.ourdomainname.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"
13. AGH!

The latest entries from the primary's error log are shown below (IP 10.152.58.243 is the mirror).
The only thing I haven't tried is setting up manually through SQL statements, but this looks extremely messy and would basically just be trial and error (so what's new, you ask?).

Any help appreciated.

--
Mike

Error log from primary machine (10.152.58.242):
2006-03-16 13:57:00.26 spid53 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 13:57:02.28 spid53 Server is listening on [ 'any' <ipv4> 5022].
2006-03-16 13:57:02.28 spid53 The Database Mirroring protocol transport is now listening for connections.
2006-03-16 13:57:29.14 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:29.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:29.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:30.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:32.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:35.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:37.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:40.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:42.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:45.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:11.25 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:13.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:14.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:15.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:17.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:19.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:22.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:24.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:27.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:29.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:00:51.12 spid21s Error: 9642, Severity: 16, State: 3.
2006-03-16 14:00:51.12 spid21s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 14:09:00.04 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:00.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:00.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:01.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:03.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:06.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:08.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:11.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:13.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:16.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:14:51.70 spid25s Error: 1474, Severity: 16, State: 1.
2006-03-16 15:14:51.70 spid25s Database mirroring connection error 4 'An error occurred while receiving data: '10054(error not found)'.' for 'TCP://bill-secondary.globalgossip.net:5022'.
2006-03-16 15:15:09.42 spid23s Error: 1443, Severity: 16, State: 2.
2006-03-16 15:15:09.42 spid23s Database mirroring has been terminated for database 'failover_test'. This is an informational message only. No user action is required.
2006-03-16 15:27:56.39 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:56.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:57.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:58.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:00.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:02.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:05.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:07.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:10.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:12.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:31:26.46 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:26.46 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:31:37.18 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:37.18 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:31:56.76 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:56.76 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:48:52.42 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:52.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:53.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:54.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:56.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:58.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:01.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:03.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:06.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:08.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:08:41.64 spid56 The Database Mirroring protocol transport has stopped listening for connections.
2006-03-16 16:08:43.64 spid56 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 16:31:05.75 spid51 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 16:31:07.75 spid51 Server is listening on [ 'any' <ipv4> 5022].
2006-03-16 16:31:07.75 spid51 The Database Mirroring protocol transport is now listening for connections.
2006-03-16 16:31:14.14 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:14.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:14.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:15.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:17.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:20.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:22.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:25.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:27.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:30.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:17.00 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:19.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:19.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:20.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:22.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:25.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:27.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:30.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:32.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:35.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
Should have also mentioned:

3.5. Enabled the -T 1400 flag in the Configuration Manager on all three machines.

Cheers,

Mike
|||

The problem is security. If you want to solve the problem quickly, I would add the machines to a domain (assuming you have a domain), and make sure that all the SQL processes are running as the same user which is the SA on all machines everything will be fine.

The problem is that although you run as SA on server A, that user is a nobody on server B. Server A tries to login using the account that it runs as; as far as B is concerned, that user is anonymous.

Additional points:

1. You mention that you telnet to 5020, but it looks like you setup the endpoints to listen in on 5022.

2. You could use certificates (search BOL) to setup mirroring for servers that are not in trusted domains.

3. You may also have to backup and restore the transaction log once.

4. The T-SQL is very easy to do. See the examples in BOL.

Thanks,

Mark

|||Thanks for your reply.

The telnet port was a typo.

My understanding is now that the mirroring security wizard only works when the machines are on the same domain. If they are on different domains then it is necessary to create certificates using T-SQL to enable trusted endpoint comminication. Is this correct?

I have attempted, so far unsuccessfuly to create certificates in this manner - please see my subsequent post. If there really is no solution to this problem then I guess we'll have to go down the route of putting both servers in the same domain. However, we are trying to write a self-contained application and it would be best if we didn't have to worry about introducing a domain controller into the setup.

Any advice from a guru such as yourself would be much appreciated.

Mike
|||

SP1 Community Technology Preview release
In this version it should be working without setting the flag "-t 1400“.
I have been tested the mirroring since the Release was released, but nothing work
Can anybody help me?
After I agree "Start Mirroring" always the same failure is pop up!
Translation from German "for database XXXX is morroring not configured”.

Can anybody reproduce this ?

|||

What do you mean "nothing work"?

Are you saying that you cannot setup database mirroring?

I would recommend looking in BOL in the section on troubleshooting database mirroring setup.

It is more than likely a security issue.

Thanks,

Mark

|||

If you can, set mirroring up using a domain admin account on all machines, If that works then it will soon tell you have permissions problems when running under other accounts.

I found it easier to set mirroring up in T-SQL than the GUI, as it provides all the code for you anyway and you get a bit more idea on what's actually going on

|||

Ensure that windows services account through which sql server is running is added in "access this computer from network" under security policy.

This should solve the problem

Manohar Kulkarni

SQLDBA

help for shrinking database

Hi guys.

I have a huge database file. When I run sp_spaceused this are the
results:

db_size 1337,31 Mb
unallocated 14,62 Mb
reserved 1088456 Kb
data 258992 Kb
index_size 6224 Kb
unused 823240 Kb

Now, when I run dbcc shrinkfile(database_name, 50) or whatever value of
percent, it says there is not enough free space in db.

Please advice how to shrink the size of that file and why is it so big?

Thanks in advance

Zvonkowhat about initially shrinking the log , and then the data file?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Zvonko" <zvonko_NOSPAM_@.velkat.net> wrote in message
news:449799a2@.ns1.novi-net.net...
> Hi guys.
> I have a huge database file. When I run sp_spaceused this are the
> results:
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
of
> percent, it says there is not enough free space in db.
> Please advice how to shrink the size of that file and why is it so
big?
> Thanks in advance
> Zvonko|||Zvonko (zvonko_NOSPAM_@.velkat.net) writes:
> I have a huge database file. When I run sp_spaceused this are the
> results:
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of percent, it says there is not enough free space in db.
> Please advice how to shrink the size of that file and why is it so big?

Big? 1.3 GB is a small database by today's stanadards, at least for a
production system.

The fact that the amount of "unused" is so much larger than data, indicates
that you have one or more tables that suffers from high level of
fragmentation.

This SELECT:

SELECT object_name(id), reserved FROM sysindexes WHERE indid IN (0,1)
ORDER BY reserved DESC

gives you the tables by size. You can then use DBCC SHOWCONTIG to determine
the level of fragmentation, and DBCC DBREINDEX to defragment the tables.
However, you cannot reindex tables that does not have a clustered index
with DBCC DBREINDEX. You can however, create a clustered index on these
tables, and then drop it if you absolutely do not want it.

"reserved" = space actually allocated to tables.
"unused" = space within the reserved space that is not actually used.

DBCC SHRINKFILE operates only with unallocated space. Once you have
defragmented the tables, you may be able to shrink the file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do not forget to do some backup B4 the operation...
Jack Vamvas wrote:
> what about initially shrinking the log , and then the data file?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Zvonko" <zvonko_NOSPAM_@.velkat.net> wrote in message
> news:449799a2@.ns1.novi-net.net...
> > Hi guys.
> > I have a huge database file. When I run sp_spaceused this are the
> > results:
> > db_size 1337,31 Mb
> > unallocated 14,62 Mb
> > reserved 1088456 Kb
> > data 258992 Kb
> > index_size 6224 Kb
> > unused 823240 Kb
> > Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of
> > percent, it says there is not enough free space in db.
> > Please advice how to shrink the size of that file and why is it so
> big?
> > Thanks in advance
> > Zvonko

Help for Newbie: How to create an autonumber

Hi guys/gals. An intro, I've just pick up some database basic skills and currently doing a simple project.

I really need advise on how do I create a field that can generate autonumbering for eg. scenario

There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.

I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!! :oI'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.

-PatP|||my money's on access

and what do you mean by basic skills?

can you spell dba?

Is this homework?|||Sarcasm, Brett? How unlike you!

NOT!

:)

If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.

Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.

But this is not simple SQL.

An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.

Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.

Help developing this project.

Hi,
I'm about to start working on a new project and I would like to have
some ideas from you guys. I already got an idea how to do it, but I'm
not sure how good it is.
This is the problem:
We have about 20 tables with data. We have Persons, Addresses, Vehicles,
Properties, Phones...
The problem that we have is they want to have everything related (many
to many) For example, we may have a Person related with an address, or a
Vehicle with an address, a property with 2 persons... any kind of
combination that you may think.
How to design that? First I thought in having 20 tables and creating one
link table between every two types. As you can see, that wouldn't be a
good solution, because we would have too many of these intermediate tables.
Then I thought that maybe I could have only 1 table with the data of the
20 types and have an extra field that tell us what type it is (if it is
a Person, an Address, ... )
The table would be like this:
Table A
--
PK (autoincrement)
RecordType (from 1 to 20)
Custom1
Custom2
Custom3
Custom4
...
Once I have that, I would add an extra table that give us the relations
between 2 records of Table A. Something Like this:
Table B
--
FK_1 (Foreign key to a record in Table A)
FK_2 (Foreign key to a record in Table A)
What do you guys think? I know it's not really nice but I cannot find a
better solution for that.
Thanks a lot!No - do not have one table for everything - this is very bad. Might as
well use text files to store your data, because you wouldn't need a
relational database.
The correct solution is your first instinct : linking tables that link
one entity to one other entity.
I suspect that everything doesn't truly relate to everything else in a
many-many relation. (e.g., Addresses-Properties).
Once you determine the actual many-many relations, why would having it
done right result in "too many tables?" [sounds like the emporer in
"Amadeus" telling Mozart his music has "too many notes"]
Star wrote:
> Hi,
> I'm about to start working on a new project and I would like to have
> some ideas from you guys. I already got an idea how to do it, but I'm
> not sure how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many
> to many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate tables
.
> Then I thought that maybe I could have only 1 table with the data of the
> 20 types and have an extra field that tell us what type it is (if it is
> a Person, an Address, ... )
> The table would be like this:
> Table A
> --
> PK (autoincrement)
> RecordType (from 1 to 20)
> Custom1
> Custom2
> Custom3
> Custom4
> ...
> Once I have that, I would add an extra table that give us the relations
> between 2 records of Table A. Something Like this:
> Table B
> --
> FK_1 (Foreign key to a record in Table A)
> FK_2 (Foreign key to a record in Table A)
>
> What do you guys think? I know it's not really nice but I cannot find a
> better solution for that.
> Thanks a lot!
>|||Star wrote:
> Hi,
> I'm about to start working on a new project and I would like to have
> some ideas from you guys. I already got an idea how to do it, but I'm
> not sure how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many
> to many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate tables
.
>
Not too many. Just as many as are needed - no more and no less. I
suspect it isn't as complicated as "they" think Probably the customer
is just being lazy about the specs - which is normal - but it's the
developer / analyst or PM's job to deal with that and to elicit proper
specs from the users or sponsors.
Take a look at:
http://www.intelligententerprise.com/010101/celko.jhtml
David Portas
SQL Server MVP
--|||"Star" <star@.nospam.com> wrote in message
news:%23RPA0n29FHA.2816@.tk2msftngp13.phx.gbl...
> Hi,
> I'm about to start working on a new project and I would like to have some
> ideas from you guys. I already got an idea how to do it, but I'm not sure
> how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many to
> many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate
> tables.
> Then I thought that maybe I could have only 1 table with the data of the
> 20 types and have an extra field that tell us what type it is (if it is a
> Person, an Address, ... )
> The table would be like this:
> Table A
> --
> PK (autoincrement)
> RecordType (from 1 to 20)
> Custom1
> Custom2
> Custom3
> Custom4
> ...
> Once I have that, I would add an extra table that give us the relations
> between 2 records of Table A. Something Like this:
> Table B
> --
> FK_1 (Foreign key to a record in Table A)
> FK_2 (Foreign key to a record in Table A)
>
> What do you guys think? I know it's not really nice but I cannot find a
> better solution for that.
> Thanks a lot!
I agree with Trey on this one.
One thing that you mentionned "or a Vehicle with an address".
It sounds silly to me asking "what's the address of the vehicule".
The vehicle should be linked to a person.
(I'm not sure if the vehicle can be owned or registered by more than one
person.)
Once you have the owner, it's easy to find the address or addresses.|||Hey folks,
Thanks for you answers.

> It sounds silly to me asking "what's the address of the vehicule".
Unfortunately not on this system. This systems tracks all kind of
information. Let's say that for example there was a robbery and the
vehicle was found at a certain location. That's why we may have all kind
of weird relationships.
I agree with you guys in having a relationship table for each pair that
we may think that are going to be related, but as I said our users may
have at any moment the need of adding a relation between two very
different kind of data.
If we have one relationship table for each pair, we would have 380
(19*20) relationship tables, and I don't think that would be a good idea.
I will try to convince my supervisors of only having the relationships
that we really know that are going to exist (that's what I have always
wanted), but that's not going to be easy.
By the way, I thought about another solution. Not very good either, but
I would like to have your opinion.
Here it is:
We do have the 20 tables. Persons, Addresses, ...
Each one of these tables look like this:
Table X
--
PK (autoincrement)
[Rest of the fields]
We assign an ID to each one of these tables. For example, for Persons is
1, for Addresses is 2 and so on
Now we have only 1 relationship table and it looks like this:
Table Relations
--
SourceID
FK_Source
DestinationID
FK_Dest
[other fields]
The PK of this table is (SourceID,FK_Source,DestinationID,FK_Des
t)
SourceID and DestinationID are going to have the ID of one of the 20
tables respectively. FK_Source and FK_Dest are the PKs of one of the 20
tables respectively
So if we have in that Relations table something like (1,32123,2,543543)
We know that we have a relation between a person and a address. The PK
of this person is 32123 and the PK of the address is 543543.
The relation between 2 tables changes depending on the type of data.
Not really a relational database...
What do you guys think?
Thanks a lot!!

Raymond D'Anjou wrote:
> "Star" <star@.nospam.com> wrote in message
> news:%23RPA0n29FHA.2816@.tk2msftngp13.phx.gbl...
>
>
> I agree with Trey on this one.
> One thing that you mentionned "or a Vehicle with an address".
> It sounds silly to me asking "what's the address of the vehicule".
> The vehicle should be linked to a person.
> (I'm not sure if the vehicle can be owned or registered by more than one
> person.)
> Once you have the owner, it's easy to find the address or addresses.
>|||"Star" <star@.nospam.com> wrote in message
news:eVN1el39FHA.2184@.TK2MSFTNGP10.phx.gbl...

> If we have one relationship table for each pair, we would have 380 (19*20)
> relationship tables, and I don't think that would be a good idea.
I've developed databases with more tables than that. The limit in SQL Server
is more than 2 billion objects.
An alternative may be the classic "type/supertype" model. See the following
example. Notice that the keys ensure each entity is of exactly one type.
This can get much more sophisticated by adding the entity_types to the
joining table (entity_references) so that you can enforce rules allowing
only certain combinations of references. Also you may want to add a column
to determine what kind of reference is defined in each case.
CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL REFERENCES
entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES entities
(entity_code)) ;
CREATE TABLE addresses (entity_code INTEGER NOT NULL, entity_type CHAR(2)
NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* address
attributes . . . */);
CREATE TABLE vehicles (entity_code INTEGER NOT NULL, entity_type CHAR(2) NOT
NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* vehicle
attributes . . . */);
CREATE TABLE persons (entity_code INTEGER NOT NULL, entity_type CHAR(2) NOT
NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* person
attributes . . . */);
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OPUHrE49FHA.1028@.TK2MSFTNGP11.phx.gbl...
Oops. I missed out a key:
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL REFERENCES
entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES entities
(entity_code), PRIMARY KEY (entity_code_1, entity_code_2)) ;
David Portas
SQL Server MVP
--|||... in fact I missed out nearly all the keys! For completeness, here it is
again:
CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL PRIMARY KEY
REFERENCES entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES
entities (entity_code)) ;
CREATE TABLE addresses (entity_code INTEGER NOT NULL PRIMARY KEY,
entity_type CHAR(2) NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'),
FOREIGN KEY (entity_code, entity_type) REFERENCES entities (entity_code,
entity_type), /* address attributes . . . */);
CREATE TABLE vehicles (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY
(entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
/* vehicle attributes . . . */);
CREATE TABLE persons (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY
(entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
/* person attributes . . . */);
Hope this helps.
David Portas
SQL Server MVP
--|||A few things:
* Nothing wrong with 380 tables, if you need them all.
* Anything that doesn't strongly define and relate entities is going to
lead to problems.
* However your users think things should be related will drive design,
of course, but their definition of "related" could be different than a
rdbms definition. There will almost certainly be more attributes to
these n-n relations than simply x.id(n)--y.id(n) [ simple example:
person-phone may have phone number type (home,work,mobile,fax,etc.)
which truly belongs with this relationship and not the phone number
itself ]. From your one example, Address-Vehicle still seems to not be a
true n-n relationship - it seems more like "address found" is an
attribute of the relationship of "vehicles involved in a case" (which
itself is an n-n relation between cases-vehicles) - after all, what use
is "address where vehicle found" if it doesn't relate to a case?
(assuming there is such entity).
Only thorough business analysis will help you determine this.
Star wrote:
> Hey folks,
> Thanks for you answers.
>
> Unfortunately not on this system. This systems tracks all kind of
> information. Let's say that for example there was a robbery and the
> vehicle was found at a certain location. That's why we may have all kind
> of weird relationships.
> I agree with you guys in having a relationship table for each pair that
> we may think that are going to be related, but as I said our users may
> have at any moment the need of adding a relation between two very
> different kind of data.
> If we have one relationship table for each pair, we would have 380
> (19*20) relationship tables, and I don't think that would be a good idea.
> I will try to convince my supervisors of only having the relationships
> that we really know that are going to exist (that's what I have always
> wanted), but that's not going to be easy.
> By the way, I thought about another solution. Not very good either, but
> I would like to have your opinion.
> Here it is:
> We do have the 20 tables. Persons, Addresses, ...
> Each one of these tables look like this:
> Table X
> --
> PK (autoincrement)
> [Rest of the fields]
> We assign an ID to each one of these tables. For example, for Persons is
> 1, for Addresses is 2 and so on
> Now we have only 1 relationship table and it looks like this:
> Table Relations
> --
> SourceID
> FK_Source
> DestinationID
> FK_Dest
> [other fields]
> The PK of this table is (SourceID,FK_Source,DestinationID,FK_Des
t)
> SourceID and DestinationID are going to have the ID of one of the 20
> tables respectively. FK_Source and FK_Dest are the PKs of one of the 20
> tables respectively
> So if we have in that Relations table something like (1,32123,2,543543)
> We know that we have a relation between a person and a address. The PK
> of this person is 32123 and the PK of the address is 543543.
> The relation between 2 tables changes depending on the type of data.
> Not really a relational database...
> What do you guys think?
> Thanks a lot!!
>
>
>
>
>
>
>
> Raymond D'Anjou wrote:
>|||Thanks a lot, David!
I haven't tried it yet, I will be doing some testing through the
wend. My first impression is your idea is something similar to my
second solution, but will all the relations defined correctly. Please
correct me if I'm wrong.
I'll get back to you in a couple of days, when I do some other testing.
Thanks again.
David Portas wrote:
> ... in fact I missed out nearly all the keys! For completeness, here it is
> again:
> CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_ty
pe
> CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
> Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
> CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL PRIMARY KEY
> REFERENCES entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENC
ES
> entities (entity_code)) ;
> CREATE TABLE addresses (entity_code INTEGER NOT NULL PRIMARY KEY,
> entity_type CHAR(2) NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'),
> FOREIGN KEY (entity_code, entity_type) REFERENCES entities (entity_code,
> entity_type), /* address attributes . . . */);
> CREATE TABLE vehicles (entity_code INTEGER NOT NULL PRIMARY KEY, entity_ty
pe
> CHAR(2) NOT NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY
> (entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
> /* vehicle attributes . . . */);
> CREATE TABLE persons (entity_code INTEGER NOT NULL PRIMARY KEY, entity_typ
e
> CHAR(2) NOT NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY
> (entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
> /* person attributes . . . */);
> Hope this helps.
>sql

help connecting via vbscript to sql 2005 express edition

Hi guys,
I need to connect to a newly created sql 2005 express edition database.
I tried to recycle the connection string used with sql 2000 server but
it doesn't work anymore:
dbConn.Connectionstring="DRIVER=SQL
Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
sSQLServer
database, ssqldb and ssqlserver are variables that correspond to
database names, table names, ecc.
IP and NamedPipes are enabled, so are remote connections (I'm actually
trying this from the same computer).
Any help ?Try specifying:
DRIVER={SQL Server};
Notice the parenthesis.
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81
<zerbie45@.gmail.com> wrote in message
news:1140536817.286087.242470@.g47g2000cwa.googlegroups.com...
> Hi guys,
> I need to connect to a newly created sql 2005 express edition database.
> I tried to recycle the connection string used with sql 2000 server but
> it doesn't work anymore:
> dbConn.Connectionstring="DRIVER=SQL
> Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
> sSQLServer
> database, ssqldb and ssqlserver are variables that correspond to
> database names, table names, ecc.
> IP and NamedPipes are enabled, so are remote connections (I'm actually
> trying this from the same computer).
> Any help ?
>|||Where are you trying to connect from? Are you sure the app code is running
as a windows authenticated user that has been granted access to the
database?
Also, I would use (more out of habit than anything):
"Provider=SQLOLEDB.1; Data Source=" & sSQLServer & "; Initial Catalog=" &
sSQLDB & "; Trusted_Connection=Yes;"
And I would try using SQL Authentication first, because you would have had
to do a lot more on purpose in order to get a trusted connection working.
<zerbie45@.gmail.com> wrote in message
news:1140536817.286087.242470@.g47g2000cwa.googlegroups.com...
> Hi guys,
> I need to connect to a newly created sql 2005 express edition database.
> I tried to recycle the connection string used with sql 2000 server but
> it doesn't work anymore:
> dbConn.Connectionstring="DRIVER=SQL
> Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
> sSQLServer
> database, ssqldb and ssqlserver are variables that correspond to
> database names, table names, ecc.
> IP and NamedPipes are enabled, so are remote connections (I'm actually
> trying this from the same computer).
> Any help ?
>|||Thanks for your answers. I'll give it a try tomorrow at work.
I'm actually trying to connect from the same machine where sql server
2005 express ed is installed. Sql server is using windows auth (I tried
configuring it to use both windows auth and sql auth). The user is
domain administrator, so it should not be a permission issue.
Thanks again for your attention.

Help ! Dangerous user with SA permissions

Hi guys,

I have been told that only users with SA priviledges could check the
status of a job. For this reason, I had to give SA priviledges to this
user so he could check the results of a job (succesful or not) and do
his work. Now, he makes changes on the database without telling me and
last time we nearly lost one day of work as he changed the db option
to truncate on checkpoint. I don't want to be the one to log on and
verify the results of the job everyday so is there any way that I can
give him limited permissions so he could only check the job and leave
the database alone ? I am pregnant so I am afraid that next time he
does something like that I may hit him on the head with the keyboard
(hormones... :-)Any suggestions would help,
Thanks !!!1) Change the sa password immeditaely.

2) Change the job so that it emails you and the target user every time it
completes and/or fails.|||And give him a written warning if you can't sack him immediately.

dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20040301150956.18347.00000503@.mb-m06.aol.com>...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||2) Or grant him permissions only to execute sp_help_jobhistory.

"DCM Fan" <dcmfan@.aol.comSPNOAM> wrote in message
news:20040301150956.18347.00000503@.mb-m06.aol.com...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||pramos00@.bellsouth.net (Patricia) wrote in message news:<b50e122b.0403011202.463c7c0e@.posting.google.com>...
> Hi guys,
> I have been told that only users with SA priviledges could check the
> status of a job. For this reason, I had to give SA priviledges to this
> user so he could check the results of a job (succesful or not) and do
> his work. Now, he makes changes on the database without telling me and
> last time we nearly lost one day of work as he changed the db option
> to truncate on checkpoint. I don't want to be the one to log on and
> verify the results of the job everyday so is there any way that I can
> give him limited permissions so he could only check the job and leave
> the database alone ? I am pregnant so I am afraid that next time he
> does something like that I may hit him on the head with the keyboard
> (hormones... :-)Any suggestions would help,
> Thanks !!!

Create a view that points to the job tables in msdb and give the user
SELECT permissions to this view. If the view is create with DBO the
user should be able to access the information without having SA
priviledges.

Something like:

IF OBJECT_ID('vcheckjobstatus') IS NOT NULL
DROP VIEW vcheckjobstatus
GO

CREATE VIEW vcheckjobstatus
AS
SELECT sj.name, sh.run_status, MAX(run_date) as MAXDATE, MAX(run_time)
as MAXTIME
FROM MSDB.DBO.sysjobs sj
INNER JOIN MSDB.DBO.sysjobhistory sh
ON sj.job_id = sh.job_id
GROUP BY sj.name, sh.run_status
GO

GRANT SELECT ON vcheckjobstatus TO user

Randy
www.Database-Security.Info

Help - deleting existing auto-generated primary key

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

help

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

Help

Hi Guys can you take a look at this SP. When I start with a fresh clean table it seems to work for a little while, But after awhile it gets stuck messed up and dosen't update correctly.

Is there a better way to write this? Thank You

Update SalesPersonelUPCNumber
SET
MoneyOwed = SalesPersonelNewCustomerSalesCount.Level1MoneyEarned +Level2MoneyEarned +Level3MoneyEarned
FROM
SalesPersonelNewCustomerSalesCount
WHERE
SalesPersonelUPCNumber.UserName IN (@.UserName)

Can you tell us what you mean by "it gets stuck messed up and dosen't update correctly"?

Are these columns SalesPersonelNewCustomerSalesCount.Level1MoneyEarned ,Level2MoneyEarned ,Level3MoneyEarned in table SalesPersonelUPCNumber?

Also modify your WHERE clause to

WHERE SalesPersonelUPCNumber.UserName = (@.UserName)

for efficient query plan.

|||

Can you tell us what you mean by "it gets stuck messed up and dosen't update correctly"? --> When I click on the Update Button It ddin't Update values stayed the same.

Are these columns SalesPersonelNewCustomerSalesCount.Level1MoneyEarned ,Level2MoneyEarned ,Level3MoneyEarned in table SalesPersonelUPCNumber? -->Yes These are Columns containing different total amounts

Want all 3 Column Values and Update at (MoneyEarned Column) at Correct UserName SalesPersonelNewCustomerSalesCount.Level1MoneyEarned +Level2MoneyEarned +Level3MoneyEarned in table SalesPersonelUPCNumber

|||

Not knowing exactly how it's table is getting messed up I would first try to rewrite the procedure to something like:

Declare @.MoneyOwed int

Select @.MoneyOwed = Level1MoneyEarned +Level2MoneyEarned +Level3MoneyEarned
FROM SalesPersonelNewCustomerSalesCount
WHERE SalesPersonelUPCNumber.UserName = @.UserName

Update SalesPersonelUPCNumber
SET MoneyOwed = @.MoneyOwned

This would make it more readable (obviously just a style choice) butmay help with the updates as well. Also, are all the Level* columns the same data type? Is it possible that any of the values in the Level columns could be NULL?

|||

Just to add to Andrew's post above, what is the db you are dealing with? SQL Server or Access?

|||

Hi Guys,

Thanks Andrew, & Dinakar for you Advice Help... Here's what seems to have have worked so far. I only had to modify it just a little bit.and add aWHERE statement. It seems to be working though. I will test it some more just to be sure.

Dinakar I'm using SQL Dbase. ThankYou Again Guys!!! Cheers!!!

Select @.MoneyOwed = Level1MoneyEarned + Level2MoneyEarned + Level3MoneyEarned
FROM SalesPersonelNewCustomerSalesCount
WHERE SalesPersonelNewCustomerSalesCount.UserName = @.UserName

Update SalesPersonelUPCNumber
SET MoneyOwed = @.MoneyOwed
WHERE
SalesPersonelUPCNumber.UserName IN (@.UserName)

|||

Use the "=" instead of IN. "=" will perform better, it will use an index seek. Also, just to be on safe side you might want to check for NULLs.

Select@.MoneyOwed=Coalesce(Level1MoneyEarned,0)+Coalesce(Level2MoneyEarned,0)+Coalesce(Level3MoneyEarned,0)

FROM SalesPersonelNewCustomerSalesCount

WHERE SalesPersonelNewCustomerSalesCount.UserName=@.UserName

Update SalesPersonelUPCNumber

SET MoneyOwed=@.MoneyOwed

WHERE SalesPersonelUPCNumber.UserName=@.UserName

|||

Andrew.Hanson:

Not knowing exactly how it's table is getting messed up I would first try to rewrite the procedure to something like:

Declare @.MoneyOwed int

Select @.MoneyOwed = Level1MoneyEarned +Level2MoneyEarned +Level3MoneyEarned
FROM SalesPersonelNewCustomerSalesCount
WHERE SalesPersonelUPCNumber.UserName = @.UserName

Update SalesPersonelUPCNumber
SET MoneyOwed = @.MoneyOwned

This would make it more readable (obviously just a style choice) butmay help with the updates as well. Also, are all the Level* columns the same data type? Is it possible that any of the values in the Level columns could be NULL?

Doing the calculation in two steps (select into holding variable and updating afterwards) is bad form. It is possible, in a multi-user system, for a transaction to be overwritten and therefore lost.

Please list the two tables and the column names in each. Please specify which columns are mandatory and which are optional (i.e., can contain a null value).

Without this information, we are all just guessing.

|||

Thanks Guys,

Seems to be working fine now, If I notice any problems with it I Post the 2 tables but for now it seems to be working. Thanks again Guys!!! Cheers