Absurd project: working

Showing posts with label working. Show all posts

Friday, March 30, 2012

Help in crystal reports 8.5

Hi all

I am working on Crystal reports 8.5
In one report we need to display weekdays and weekends in every month for the given date range

I mean

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Days of Service
Weekdays Number of weekdays in the month
Weekends Number of weekends in the month

And I need to calculate total of some items from Friday after 3 pm

2) In another report I am using stored procedure to get fields
As per my knowledge we cannot use other than one stored procedure in crystal reports 8.5
So I am using sub report to get some more fields other than stored procedure fields

But in main report I need to get sub- total of fields in sub report.

Could any one please help me out?You would use the Crystal Reports (http://www.saveonsupport.com) shared variable functionality. In your subreport you would have a formula field something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal := myTotal + {field name}

Add this field to the subreport and suppress it.
Then in the main report you need another formula something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal

The subreport keeps adjusting the total while printing records and the main report simply displays this value.

Hop this helps!|||Thanks for your reply
it works for me

Help if possible

I have very limited skill in Access, but am working on a project for work. I have a database file that includes fields like item number and ticket number. There are currently identical records for both of these fields. I'm trying to create a query that dislplays a specific item only if it has the same ticket number as any other item.

I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql

Wednesday, March 28, 2012

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:

Any help appreciated.

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

Cheers,

Mike
|||

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.

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

Mike
|||

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 format Page Heading

I dont usually use page headers for this reason. I usually put my headings
within the body of the report however it doesnt seem to be working properly
with this matrix so I am going to use a true page header and I am having
trouble because I need to reference fields from the database in the header.
Here is my expression:
=eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
,"Title") & Parameters!MetricCode.Label & vbcrlf &
eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
,"ReportPeriod") & format(CDate(Parameters!StartDate.Value), "MM-dd-yyyy")
& " - " & format(First (Fields!MAXDATE.Value, "GR01_10"),"MM-dd-yyyy")
It is complaining about the MAXDATE value however I need that value and it
comes from the query result. There is probably a way around this ... I just
dont deal with headers so I have never encountered this "rule" of fields
cannot be used in headers or footers.You can't directly put values from a dataset into the page header or footer,
but you can use report paramters to do this.
Create a report parameters for each query field that you want to put in the
header. Setup the parameter to be populated by the appriopriate field.
Then use textboxes that get their value form an expression to put the data
in the header.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:E0813CA6-F8FA-4EBA-80B6-EA86C613DB98@.microsoft.com...
>I dont usually use page headers for this reason. I usually put my headings
> within the body of the report however it doesnt seem to be working
> properly
> with this matrix so I am going to use a true page header and I am having
> trouble because I need to reference fields from the database in the
> header.
> Here is my expression:
> =eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
> ,"Title") & Parameters!MetricCode.Label & vbcrlf &
> eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
> ,"ReportPeriod") & format(CDate(Parameters!StartDate.Value),
> "MM-dd-yyyy")
> & " - " & format(First (Fields!MAXDATE.Value, "GR01_10"),"MM-dd-yyyy")
>
> It is complaining about the MAXDATE value however I need that value and it
> comes from the query result. There is probably a way around this ... I
> just
> dont deal with headers so I have never encountered this "rule" of fields
> cannot be used in headers or footers.sql

Monday, March 26, 2012

Help Fixing Script.

Hello everybody... I have a SQL 2000 Script that is not working on SQL 2005.. Or 2000 for that matter.. I wanted to know if somebody can help me fix this script.

/****** Object: Database NetManage_SQL ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NetManage_SQL')
DROP DATABASE [NetManage_SQL]
GO

CREATE DATABASE [NetManage_SQL] ON (NAME = N'NetManage_SQL_Data', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NetManage_SQL_Data.MDF' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = N'NetManage_SQL_Log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NetManage_SQL_Log.LDF' , SIZE = 10, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'NetManage_SQL', N'autoclose', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'bulkcopy', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'trunc. log', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'torn page detection', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'read only', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'dbo use', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'single', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'autoshrink', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI null default', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'recursive triggers', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI nulls', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'concat null yields null', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'cursor close on commit', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'default to local cursor', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'quoted identifier', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI warnings', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'auto create statistics', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'auto update statistics', N'true'
GO

if( ( (@.@.microsoftversion / power(2, 24) = 8) and (@.@.microsoftversion & 0xffff >= 724) ) or ( (@.@.microsoftversion / power(2, 24) = 7) and (@.@.microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'NetManage_SQL', N'db chaining', N'false'
GO

use NetManage_SQL
GO
exec sp_addlogin 'NetManageAdmin', 'DigitalNetrixdbadmin', 'NetManage_SQL', 'us_english'
GO

/****** Object: Table [dbo].[Device_SwitchPorts] ******/
CREATE TABLE [dbo].[Device_SwitchPorts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_IPAddress_ID] [int] Default 0 ,
[SwitchPortDevice_Type_ID] [int] Default 0 ,
[SwitchPortSlotNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortDeviceID] [int] Default 0 ,
[SwitchPortDeviceName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Device_Type] ******/
CREATE TABLE [dbo].[Device_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_Type_ID] [int] Default 0 ,
[Device_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress] ******/
CREATE TABLE [dbo].[IPAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_ID] [int] Default 0 ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPDecimal] [float] Default 0 ,
[IPAddress_TypeID] [int] Default 0 ,
[Device_Type_ID] [int] Default 0 ,
[IPAddress_Host_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddressLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPComments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress_Type] ******/
CREATE TABLE [dbo].[IPAddress_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[IPAddress_Type_ID] [int] Default 0 ,
[Can_Edit] [int] NULL ,
[IPAddress_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[User_Activity] ******/
CREATE TABLE [dbo].[User_Activity] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login_Date_Time] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_IP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Users] ******/
CREATE TABLE [dbo].[Users] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree_Label] [nvarchar] (50) Default Contact_Name ,
[DisplayStyle] [int] Default 0 ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Full_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDelete] [int] Default 1 ,
[Node_Access] [ntext] Default 0 ,
[Priv] [int] Default 3
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[settings] ******/
CREATE TABLE [dbo].[settings] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LicenseKey] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[subnet] ******/
CREATE TABLE [dbo].[subnet] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parent_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company_Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Node_Location] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VLAN_Info] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created_By] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert into [settings](Company, LicenseKey, comments) values('DEMO COMPANY','|95|49|47|26|253|195|170|232|71|19|151|77|188|231|23|64|87|62|215|53|169|186|27|65|218|111|185|218|238|127|2|115|187|245','Product License Key')
Insert into [subnet](Subnet_Name, Subnet_Mask, Parent_Subnet) values('Network Enterprise','000000000000','0')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(0,'Not Assigned')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(1,'PC')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(2,'Printer')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(3,'Router')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(4,'Switch')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(5,'Hub')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(6,'Web Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(7,'FTP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(8,'Mail Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(9,'DNS Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(10,'DHCP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(11,'Other')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(12,'Virtual Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(13,'Other Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(14,'VoIP Phone')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(15,'SQL Server')

Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(0,0, 'Not Assigned')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(1,0, 'Static')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(2,0, 'DHCP')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(3,0, 'Reserved')

Insert into [Users](UserName, DisplayStyle, [Password],Full_Name, EmailAddress, CanDelete, Priv) values('Administrator',0,'admin','Administrator', 'admin@.company.com',0,1)

I keep getting error:

Msg 128, Level 15, State 1, Line 6

The name "Contact_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any help pleae..

Thanks..

This line in the Create statement for dbo.Users is your problem: [Tree_Label] [nvarchar] (50) Default Contact_Name ,

If it is your attention to have the default value be the text string Contact_Name, then the string must be enclosed in single quotes. Like so:

[Tree_Label] [nvarchar] (50) Default ('Contact_Name') ,

If you want it to default to a value in another column, then you need to use a trigger, not a default.

|||

Robert,

Thanks for the quick reply.. Just because I am new to this sql scripting thing.. how do I use a trigger and not default?

Thanks..

|||

You would probably have to do something like this:

CREATE TRIGGER TRG_SOmeTrigger ON SomeTable
FOR INSERT
AS
UPDATE SomeTable
SET SomeColumnInsteadOfDefault = AnotherValueorColumn
FROM SomeTable T
INNER JOIN INSERTED I
ON T.YOuPrimaryKeyhere = I.YourPrimaryKeyHere
WHERE SomeColumnInsteadOfDefault IS NULL

Although this is not the same logic as the Default value this could be an example for a trigger.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, March 23, 2012

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
w

end. 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 Designing an App. to be Run from a Job

Hello,

I'm working on a project that uses SQL Server 2005 and C# 2.0. I need some ideas of the best way to design/implement an application that will be executed from a SQL Server job.

The general idea is: a SQL Server job will call [something] and pass a couple of parameters to [something], then [something] will query a database and gather other information, then [something] will do some processing of data, and finally [something] will output the results for end user consumption.

The problem is that I don't know what [something] should be. Should it be a C# executable (but it can have no UI)? Should it be a web service? Should it be a console application (but, again, it cannot have a UI)? Should the job call a stored procedure, which in turn calls a C# assembly?

Basically, I'm just trying to get some ideas of the best way to design/implement a solution to my situation.

Thanks.

Hi,

without further information, I would suggest to use a c# console app called from the job.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the input.

What other information could I provide to help choose the best solution?

|||

Best thing is to call a Stored Proc from the Sql job

In the Stored proc gather all the parameters that you need and use BCP out to keep that data in a flat file.

There is no need to use Webservice or other C# program as this would cause an additional overhead.

|||

There are a number of things your description suggests, and others that it leaves open...

1 - from what you say, it looks like the something does not need access to the resources of the server (as a computer) and only needs access to data inside SQL Server. If this is true, then the use of a SQL Server job to invoke a stored procedure is the way to go (and in SQL Server 2005 you can write your stored procedure in C# if you like).

If, on the other hand, you need access to information outside SQL Server (files, I/O, Active Directory, other PCs, ...) you'll need an app running outside SQL Server (yes - you can invoke an app from inside SQL Server, but why jump through hoops when you don't need to?). If this is the road to take, use Windows' scheduler instead of SQL Server's scheduler.

If you're running inside SQL Server, as one of the other posts suggested, you'll have a Stored Proc to gather parameters, that likely will call another (or more) to do the work. If you're doing stuff outside the database server the app will likely invoke a stored proc to do the 'inside' work - so you're just partitioning where you do what work.

When returning the data to the user - there are a number of options that depend on various factors you've not mentioned as to which is better:

> simply write the data to an output table (with a timestamp on each row if you need to hold multiple day's / run's worth of output) - and the use can have a simple reporting app to read from it.

> If the volume of data is small SQL Server can email the recipient(s) - again, this is probably better if the number of recipients is small. You may also need to consider the security of the data as it travels as email.

> If you need to store the data outside the server (in a dated file, for example) you have to get it out, somehow. If your app is already running outside the server that's a no brainer; if everything is running inside the server I think you'll have to trigger some outside app to pull the data (i.e. I don't think there's a way for a SQL Server job to dump data to the outside world). DO NOT TAKE MY WORD for this - DTS may be invokable as a SQL Server job and may server to store data outside the server, or there may be other capabilities of SQL Server 2005 that I've not encountered yet.

> Worst comes to worst - you have a simple outside app that polls a table in the server for a 'ready' flag and then pulls the data. But that's ugly.

HTH

Griffin

sql

Wednesday, March 21, 2012

Help connecting pages which has Database connection.

I am a completely new asp.net / SQL server user.
I've been only working with PHP + MYSQL server before which is very different.

There are databases which come with the SQLserver, such as "master"
and there are databases which i create by my own.

When using "master" database, everything works fine.
here's a screenshot.
http://img528.imageshack.us/img528/57/d1if7.jpg

but when using database which i created by my own, i get an error
whether i use ip or localhost.
http://img513.imageshack.us/img513/4598/d2iz7.jpg

only when i use the debugger, it gives me some port, then i am able to connect but only using localhost... therefore i can't give the adress to anyone else.
http://img528.imageshack.us/img528/3394/d3jn6.jpg

What's the difference between the Built in databases and those i create?
how can i make that i could access pages which has database connection to databases i created by my own??

Sorry for my English, i know it's not really understandable.Anyone? it seems like something which everyone who uses asp.net with sql server for a while should know.
ty.

HELP Cant get connection to my server with SQLDMO

I am trying to learn SQLDMO for working with our SQL Server 2000
servers. I grabbed a demo program from:
http://www.csharphelp.com/archives2/archive342.html
called SQLDMO for C# from Kevin Goss
I built the program successfully in VS 2003
and it shows me a list of servers (mine being on the list)
but when I try and connect to it using the interface I get the
following messages:
Error
--
[Microsoft][SQL Native Client][SQL Server]Login failed for user
'CODE1\smoody'. Reason: Not associated with a trusted SQL Server
connection.
[Microsoft][SQL Native Client]Shared Memory Provider: The system cannot
open the file.
[Microsoft][SQL Native Client]Communication link failure
--
OK
--
The code where this is occurring is: (I didnt want to include the whole
program and I wasnt sure how much was needed for diagnosing the
problem) I can send more of the code if needs be, just tell me how much
you think you need.
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.t
xtPassword.Text);
I have confirmed that the user has access to the database as well as
setting up a DSN entry to access the database. But as of yet I still
can not get past the errors... Please HELP!!!!!
Thanks
-SteveSteve,
Ensure the SQL Server is running in Mixed Mode Authentition instead of
Windows Authentication only or change your connection string to use Windows
credentials.
HTH
Jerry
"SteveM" <steve.moody@.philips.com> wrote in message
news:1129926133.227261.243290@.g47g2000cwa.googlegroups.com...
>I am trying to learn SQLDMO for working with our SQL Server 2000
> servers. I grabbed a demo program from:
> http://www.csharphelp.com/archives2/archive342.html
> called SQLDMO for C# from Kevin Goss
> I built the program successfully in VS 2003
> and it shows me a list of servers (mine being on the list)
> but when I try and connect to it using the interface I get the
> following messages:
> --
> Error
> --
> [Microsoft][SQL Native Client][SQL Server]Login failed for user
> 'CODE1\smoody'. Reason: Not associated with a trusted SQL Server
> connection.
> [Microsoft][SQL Native Client]Shared Memory Provider: The system cannot
> open the file.
>
> [Microsoft][SQL Native Client]Communication link failure
> --
> OK
> --
>
>
> The code where this is occurring is: (I didnt want to include the whole
> program and I wasnt sure how much was needed for diagnosing the
> problem) I can send more of the code if needs be, just tell me how much
> you think you need.
>
> SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
> srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this
.txtPassword.Text);
>
> I have confirmed that the user has access to the database as well as
> setting up a DSN entry to access the database. But as of yet I still
> can not get past the errors... Please HELP!!!!!
> Thanks
> -Steve
>|||I checked and indeed I was NOT running in mixed mode. I changed this and
will check to see if that was the problem. I think I may have found a
better way to do things however. A co-worker of mine showed me a
property called
srv.LoginSecure = true;
This uses my windows credentials without my having to record my userid
and password (even though I know I can encrypt it in a config file in
the project). This seems a better way to go, but I do want to learn to
do things the other way, so you advice will be very helpful. Thanks for
taking the time to respond I appreciate it
-Steve
*** Sent via Developersdex http://www.examnotes.net ***

Monday, March 19, 2012

Help : ASP var at SQL line

Hi,

I try to insert the date parameter on SQL line.
The date parameter is on ASP var declares.

I'm working with the page that have listbox, and gridview bound to msaccess.
when the user select thelistbox with other value, the page will reload.
while the page reload on server, it carry the variable that should insert on sql line,
for displaying the difference gridview records result.
So I have dynamic gridview records base on my listbox.

But, ... the page doesn't load well from the beginning. Doesn't work at all !!

I declare this at the page load.

Dim StartProjectAsString ="1/1/1996"

Sub ProjectListBox_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs EventArgs)Handles PYear.SelectedIndexChanged
StartProject = PYear.SelectedValue.ToString
EndSub

The object "Pyear" is listbox :

<asp:ListBoxID="PYear"runat="server"Rows="12"CssClass="tb01"SelectionMode="Single"AutoPostBack="True">
<asp:ListItemValue="1/1/1991">1991-1995</asp:ListItem>
<asp:ListItemValue="1/1/1996"Selected="True">1996-2000</asp:ListItem>
<asp:ListItemValue="1/1/2001">2001-2005</asp:ListItem>
<asp:ListItemValue="1/1/2006">2006-2010</asp:ListItem>
<asp:ListItemValue="1/1/2011">2011-2015</asp:ListItem>
</asp:ListBox>

And the datasource for bound to gridview is

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/general.mdb"
SelectCommand='SELECT TBL_PROJECT.NAMEPROJECT AS Name, TBL_PROJECT.STARTPROJECT AS Start, TBL_PROJECT.ENDPROJECT AS Finish, TBL_PROJECT.JOBNO AS JobNo, TBL_PROJECT.CLIENT AS Client, TBL_PROJECT.BUILDING AS Building, TBL_PROJECT.CODEPROJECT AS Code, TBL_PROJECT.IDPROJECT AS IDPROJECT FROM TBL_PROJECT WHERE TBL_PROJECT.STARTPROJECT >= DateValue("<%=StartProject%>") And TBL_PROJECT.STARTPROJECT < DateValue("1/1/2001");'>
</asp:AccessDataSource>

It will return the error page regarding the ASP : StartProject.
If I change the variable with the string "1/1/1996" :

'SELECT TBL_PROJECT.NAMEPROJECT AS Name, TBL_PROJECT.STARTPROJECT AS Start, TBL_PROJECT.ENDPROJECT AS Finish, TBL_PROJECT.JOBNO AS JobNo, TBL_PROJECT.CLIENT AS Client, TBL_PROJECT.BUILDING AS Building, TBL_PROJECT.CODEPROJECT AS Code, TBL_PROJECT.IDPROJECT AS IDPROJECT FROM TBL_PROJECT WHERE TBL_PROJECT.STARTPROJECT >= DateValue("1/1/1996") And TBL_PROJECT.STARTPROJECT < DateValue("1/1/2001");

it will work fine, but I will miss my dynamic gridview, right ?

So, please ... help me ...

Thank you ...

The answer :

http://forums.asp.net/t/1110547.aspx

Help ... new to SQL

Hi,

I am working on a school project ...

I have the following schema:
<b>
DIVISION (dvname, manager)
DEPT (dname, parent-dname/parent-dvname, manager, floor#)
EMP (ename, salary, dname/dvname)
ITEM (iname, color, price, type)
SELL (dname, iname)
SUPPLY (sname, iname, dname)
</b>
Each of the first fields is the PKey.

I have to figure out the SQL for the following statement:

<b>List the items supplied by all companies that supply all items of type A.</b>

I have gotten this far, but do not understand division in SQL well enough .. I have a relational algebra solution that works ... but am having a hell of a time with a SQL solution ... Please help,

This is what I have in SQL:
SELECT SE.iname FROM SUPPLY SE WHERE NOT EXISTS
(SELECT I.iname FROM ITEM I WHERE I.type='A' AND NOT EXISTS
(SELECT S.iname FROM SUPPLY S WHERE S.iname=I.iname AND S.dname=SE.dname))

I have not work with SQL much so please help out ... I am using SQL Server if that matters ... Thanks--should give you the supply name where type = 'A'

select s.sname, i.iname, i.color, i.price, i.type
from supply s, item, i
where s.iname = i.iname and i.type = 'A'

if there is more to it let me know, but that should give you the items by supplier, if you want just distinct iname then just put

select distinct i.iname
from supply s, item, i
where s.iname = i.iname and i.type = 'A'

have fun

Wednesday, March 7, 2012

HELP - my virtual directory is not working!

When I follow carefully the instruction inside the book,
I cannot access my virtual directory through url query
(404 page not found error). I am sure that I have allowed
the virtual directory to be queried through url. No
matter where I create the actual path to my virtual
directory, I still got error.
Before setting up the virtual directory, I can access the
files in C:\Inetpub\wwwroot\mydb through url, but after
setting up the virtual directory, what I can get is only
404 error. Could anyone help'What I typed is:
http://myservername/mydb?
sql=select+*+from+tablename+for+xml+uto+root=ROOT
I've got a file named index.htm in
C:\Inetpub\wwwroot\mydb, before settting it as virtual
directory, it works, but after, no luck
>--Original Message--
>When I follow carefully the instruction inside the book,
>I cannot access my virtual directory through url query
>(404 page not found error). I am sure that I have
allowed
>the virtual directory to be queried through url. No
>matter where I create the actual path to my virtual
>directory, I still got error.
>Before setting up the virtual directory, I can access
the
>files in C:\Inetpub\wwwroot\mydb through url, but after
>setting up the virtual directory, what I can get is only
>404 error. Could anyone help'
>.
>

Help - Multi values dimensions ?

Hello all
we developped a software with a data warehouse in sql server, without using
analysis services, and following the star design.
we are working with data about transactions, with caracteristics modeled in
dimensions. in general each transaction has one single value for each
caracteristic, but for one, it may have more than one or no value.
some solution we thought about:
-we use comma separated IDs (string) in the dimension in the fact table, but
it would hurt indexing
-or store the IDs in a separate table, but each query will require a join
my question is what is the best way to implement this feature ?
thank you for your help
its a standard "n-n" relationship.
1 caracteristic, can have 0 to N transacions
1 transaction can have 0 to N caracteristic
So, you'll have 3 tables:
Caracteristics 1 - n CaractToTransac n - 1 Transactions
The CaractToTransac table contain only 2 columns:
CaracteristicID and TransactionID
(maybe you can add a column called "sequence" if the order of your
caracteristics is important for you)
Now you have multi caracteristics for each transaction.
"r_samir" <r_samir@.discussions.microsoft.com> a crit dans le message de
news:8E2216BF-50D7-4FF6-89A3-C1995D83E3B5@.microsoft.com...
> Hello all
> we developped a software with a data warehouse in sql server, without
using
> analysis services, and following the star design.
> we are working with data about transactions, with caracteristics modeled
in
> dimensions. in general each transaction has one single value for each
> caracteristic, but for one, it may have more than one or no value.
> some solution we thought about:
> -we use comma separated IDs (string) in the dimension in the fact table,
but
> it would hurt indexing
> -or store the IDs in a separate table, but each query will require a join
> my question is what is the best way to implement this feature ?
> thank you for your help

Monday, February 27, 2012

Help - Complex Currency Conversion - MDX

Dear all,

I have implemented Conversion conversion in my SSAS cube (One - Many) and is working fine. (Reporting Currency dimension that has currencies to which data can be converted, Fx rates measure group that has FX rates for those currencies from base currency (GBP) and calculation to convert it) The calculation I use is as below

Code Snippet

Scope ( {Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

Scope (Leaves ([Calendar]), Except([Reporting Currency].[Reporting Currency].[Reporting Currency].Members , [Reporting Currency].[Reporting Currency].&[GBP]));

Scope ( {Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

This = [Reporting Currency].[Reporting Currency].[GBP] *

( Measures.[Rate],

[Reporting Currency].[Reporting Currency].CurrentMember

);

End Scope;

Now I have a situation in which the FX rates can be different for each record in the fact table. So I have introducted the FX Rate Set ID dimension (degenerate dimension for the rate measure group) and also have it as a measure (non-additive, visible=false) for the Balance measure group that contain the data to be converted. Now, how do I acheive the actual conversion by modifying the above MDX?

Something similar to the below is what I'm trying to acheive but no idea how to get it right in MDX. Please help in the correct approach and the right MDX.

Code Snippet

This = [Reporting Currency].[Reporting Currency].[GBP] *

( Measures.[Rate],

[Reporting Currency].[Reporting Currency].CurrentMember,

[FX Rate Set].[Set ID].[<<'Value of Measures.[Rate Set ID]'>>]

);

Thanks in advance.

Cheers,

Arun

Pl can anyone help me on this?

|||Could you explain this statement with an example: "I have a situation in which the FX rates can be different for each record in the fact table"? Are you referring to the Balance or the FX Rate fact table - and does this mean that FX rates may vary on the same day?|||

Below is the sample data

Fx Rates : (the pivot currency is GBP)

Date RateSet ToCurrency Rate

03 May 2007 1 AUD 2.54

03 May 2007 2 AUD 2.51

03 May 2007 1 GBP 1

03 May 2007 2 GBP 1

03 May 2007 1 EUR 1.51

03 May 2007 2 EUR 1.6

Fact Table:

ID Date <<Other dimension keys>>> Currency Balance BalanceInGBP FXRateSet

101 03 May 2007 USD 100 195 1

102 03 May 2007 USD 100 194 2

Measure Group:

Balance MG:

Balance (FactTable.Balance)

BalanceReportingCurrency (FactTable.BalanceInGBP)

Rates:

Rate ([Fx Rates].Rate)

Calculation :

In my first post... (the column names may be bit different.)

In the above scenario, the for the same date, I may have to use different "Fx rate Set" whose ID is present in both Fact table and Fx rates table. I have a dimension called Currency that joins with the Currency column in fact table. I also have another dimension called [Reporting Hierarchy] that will hold the ToCurrency in Fx Rates and based on which the conversion will be done dynamically using calculations (The BalanceReportingCurrency measure will be calcualted dynamically using corresponding rates).

|||Forgot to mention - I have the FXRateSetID from the fact table as a measure in the balance MG as it may help in doing the calculation|||One idea is to slightly change the original solution by creating a new dimension like DailyRateSet, whose key attribute is a collection of the 2 fields: Date, RateSetID. This dimension would be used as the intermediate dimension, instead of Calendar. Another approach would be with a separate RateSet dimension, but I haven't been able to work that out yet.|||

Finally got it working. I'm having the Rate Set as another dimension referred by both fact table and the Fx rates table, then whilst doing the calculation, I perform the calc at leaf levels of both Fx rate set and Time - and using the fx rate set dimension for getting the correct rate.

At present, I convert all values to GBP when laoding to my cube. To make the data independent of rate change problems, now I'm trying to work out many-to many currency conversion.

Friday, March 30, 2012

Help in crystal reports 8.5

Help if possible

Wednesday, March 28, 2012

Help getting mirroring working

Help getting mirroring working

HELP format Page Heading

Monday, March 26, 2012

Help Fixing Script.

Friday, March 23, 2012

Help developing this project.

Help Designing an App. to be Run from a Job

Wednesday, March 21, 2012

Help connecting pages which has Database connection.

HELP Cant get connection to my server with SQLDMO

Monday, March 19, 2012

Help : ASP var at SQL line

Help ... new to SQL

Wednesday, March 7, 2012

HELP - my virtual directory is not working!

Help - Multi values dimensions ?

Monday, February 27, 2012

Help - Complex Currency Conversion - MDX

Absurd project

Blog Archive

About Me