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 cannot connect to SQL Server 2000

I'm trying to build a simple form using SQL Server 2000 and am having trouble establishing a connection to my database. I was able to build and successfully test my connection in Server Explorer. But when I try to open the connection in my app I keep getting a "Login failed for user xxx" error message. I'm using standard Windows NT security. Would appreciate any help.If you are using Windows security, when you are testing in Server Explorer, you are using your Security Context. The ASP.NET page is useing the ASPNET user's security context, so if you want to use Windows security, you must add the ASPNET Windows user to SQL Server. If you have Enterprise Magager, expand out the database and right click on users, and then New database User. The dialog from there is mostly self explanitory. Let us know if you have any other questions.|||Thanks Douglas that did the trick!

Help - Rebuild System Databases on a clustered instance

I am in a DR senario and I am having trouble rebuilding the system databases on my SQL 2005 cluster.

I run the following string:

start /wait setup.exe /qn VS=Vname INSTANCENAME=instname REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=password SQLACCOUNT=domain\acct SQLPASSWORD=password AGTACCOUNT=domain\acct AGTPASSWORD=password REINSTALLMODE=vomus

The setup fails with the following error:

Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows Server 2003 family, Service Pack 1 (Build 3790)
Time : Wed Dec 13 15:41:44 2006
CLUSTERSVR1 : To install Microsoft SQL Server 2005, COM+ should work.
CLUSTERSVR1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
CLUSTERSVR1 : To install Microsoft SQL Server 2005, COM+ should work.
CLUSTERSVR1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
Machine : CLUSTERSVR1
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Error Number : 5897

SQL 2005 was originally installed in May '06 as RTM. SP1 applied in September '06.

I have the original RTM install disk but obviously SP1 is causing rebuild to fail.

Can anyone advise on this please.

D.

Can you apply SP1 and then retry the procedure?

I mean doing the setup of 2005, applying SP1 and then retrying?

|||

JohDas,

Unfortunately I can't.

Don't forget I do not have any databases - system or user. Therefore SP1 cannot authenticate to SQL Server to process the upgrade.

I have a bad feeling that my only option is to uninstall/reinstall SQL 2005.

But this is a last resort and I want to avaoid it if I can.

D.

Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})
) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime
})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentnumber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}')
And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})
) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}
')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') A
nd
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentnumber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}'
) And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime
})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') A
nd
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCod
e}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}')
And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) An
d
...for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written

Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!
I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>
|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.
|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>
|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentn umber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName
|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time
|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentn umber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>
|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:

> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>
|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
....for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written

Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentnumber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentnumber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
...for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written|||Have you tried running the SQL outside of Crystal just to look and verify
that they look like you want?
I'm not sure if this is SQL passed to the db or in a stored procedure. But,
either way, one way to do this would be to use sql-profiler to profile either
the sp execution or TSQL execution. You can then use the TextData to see the
exact call being made on the SQL server.
You can then run this in query analyzer to view the result set that is being
returned.
From what I know, you can not regroup you data to redisplay on separate
pages of the reports. Since, Crystal would have already walked through your
data to bind to the report using the first grouping.
I definitely am not a Crystal expert. But, I would recommend running the
SQL outside of Crystal so you have a good idea of the Record Set that you are
ultimately binding to your report and to verify that your joins and WHERE
clause is working as you expect.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> 1)DateTime
> 2)there are only 2 now...BeginDateTime and EndDateTime
> 3)it will be a date/time like 1/4/06 14:30
>
> Thank you for all of you help.....I am trying this right now...
> Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
> ....for the line of code that is erroring....I am not getting an
> error...but page 2 of my report where the data is grouped by
> departments is all blank...I don't know if this is supposed to
> happen...This is the first real Crystal Report I have ever written
>|||Thank you so much for your help. You have been very helpful. I
finally got it all to work out with that last line of code...I just
had to check the dates on the db...that is why i was getting a blank
report. Right now I am just writting the formula fields and I will be
done with this report. Couldn't have done it without your help!!!!
Thanks again!!!!|||Cool. Glad I could help.
Best of luck.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> Thank you so much for your help. You have been very helpful. I
> finally got it all to work out with that last line of code...I just
> had to check the dates on the db...that is why i was getting a blank
> report. Right now I am just writting the formula fields and I will be
> done with this report. Couldn't have done it without your help!!!!
> Thanks again!!!!
>