Showing posts with label sub. Show all posts
Showing posts with label sub. Show all posts

Friday, March 23, 2012

I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go


insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go


drop table submain
go
drop table main
go
drop table combined
go

Monday, March 12, 2012

I too, cannot connect to SQL EXPRESS via different sub-nets

I have been having a problem, each of our departments is on a separate sub net e. 165.10.xx.xx where my server exists on 165.10.10.10 other worksatations upon the same sub net can access the database, where as those on a separate sub-net e.g. 165.10.12.25 cannot.

The particular data base is a an sqlexpress database, which is accessed through an access-front end utilizing ODBC for the connectivity. The user does have access rights via windows security to both the database directory, as well as the front access specific directory. When trying to set up the ODBC driver I cannot browse to the server, I can however type it in e.g. host\sqlexpress i do get through the authentication screen utilizing windows authentication. When I try to change the database from master to the application database I get an error stating database does not exist.

I have confirmed that the sql server is listening (which seemed obvious that same sub-net could get in) I have also assured the problem sub-net workstation is not running any fire wqall blocks or port filters.

--Today I logged on to the work station as myself (system administrator) and I can set up the ODBC driver under my rights, once i typed in the server name, I could see the database I needed. This gave me the idea of adding the user in the problem sub-net to the server administrators group -- which does allow the user access.

I had thought that the secure by design paradigm shift allowed access with minimal rights.

My question is this :

How do I configure the server so that the user is not required to be an administrator on it, so that access to the data can be granted, while keeping a modicum of security on the server?

Any help would be most welcome -- Thanks ;-)

Are you successfully able to connect to the server on subnet 165.10.10.10 from different subnets after making the connecting users admins on the server machine?

It seems very odd that your network connectivity problem would be solved by adding the member to the administrators group. This is not expected and is not required. Are you using IP security in your environment? What is the precise error message that you're receiving when your connection attemp fails?

To get started on how to provision login access for non-administrator users, here's a good page: http://msdn2.microsoft.com/en-us/library/ms189751.aspx. If you look around this part of the documentation you'll find more details on the minimum set of permissions needed for users to successfully login.

Hope this helps,

Vaughn

|||

Once I add the user to the Administrators group on the host machine she can connect to the database and function.

What strikes me as odd though, is that i still have to actually type in the host/sqlexpress refrence as it will not show up in the

Which server do you want to connect to?

Server -- combo box

The authentication step works

then I can see the compulsionasset database in the

Change default database to checkbox -- drop down combo

When the user is not an administrator

I still have to type the server name, authentication still works, but the database table does not show up for selection, and typing it in I get an error that it doesn't exist so that the ODBC administrator wizard will not complete.

There are no other errors.

Adding her to the administrators group did get her in though.

|||

Oh...

I have given her a login for he domain account within SQLExpress on the appropriate table...

|||

Once I add them as administrator they can connect. It does seem crippled though, usually page ione of the ODBC setup wizard lets you choose from a drop down combo box - the server, I must type the full name in as it doesn't see it. then Page 3 usually lets you change the default database -- as an administrator they can see the database -- if not they can only see the default databases master, tempdb and model. Both means appear to connect -- without error messages on page 2 of the wizard.

|||

I'm not sure if the drop down combo box and the login problem have the same root cause but here's what I'd suggest for fixing the drop down:

1) Ensure the SQL Server instance has TCP/IP enabled (it almost certainly does since remote admin connections work)

2) Ensure the SQL Browser service is running on the server machine

3) Make sure there are no firewalls (e.g. windows firewall) blocking connectivity to the sql server instance or sqlbrowser. For SQL Browser you can either add the executable or open UDP port 1433. For the name SQLExpress instance you'll need to add the process since the TCP port is dynamic unless you've manually configured it otherwise.

4) Double check that in SQL Server Configuration Manager there's not a "HideInstance" flag set to true for the SQLExpress instance

Checking all of these *should* make the instance show up in the drop down box.

|||

All of those have been checked. I can see the server from machines within the same subnet, I cannot from machines on a different subnet. Though those users can browse the host via windows networking., there are other instances that do not show up within the drop down as well, though, again they do when on the same subnet.

All firewalls are external to the network none between subnets.

Again, thanks for any and all help.

I too, cannot connect to SQL EXPRESS via different sub-nets

I have been having a problem, each of our departments is on a separate sub net e. 165.10.xx.xx where my server exists on 165.10.10.10 other worksatations upon the same sub net can access the database, where as those on a separate sub-net e.g. 165.10.12.25 cannot.

The particular data base is a an sqlexpress database, which is accessed through an access-front end utilizing ODBC for the connectivity. The user does have access rights via windows security to both the database directory, as well as the front access specific directory. When trying to set up the ODBC driver I cannot browse to the server, I can however type it in e.g. host\sqlexpress i do get through the authentication screen utilizing windows authentication. When I try to change the database from master to the application database I get an error stating database does not exist.

I have confirmed that the sql server is listening (which seemed obvious that same sub-net could get in) I have also assured the problem sub-net workstation is not running any fire wqall blocks or port filters.

--Today I logged on to the work station as myself (system administrator) and I can set up the ODBC driver under my rights, once i typed in the server name, I could see the database I needed. This gave me the idea of adding the user in the problem sub-net to the server administrators group -- which does allow the user access.

I had thought that the secure by design paradigm shift allowed access with minimal rights.

My question is this :

How do I configure the server so that the user is not required to be an administrator on it, so that access to the data can be granted, while keeping a modicum of security on the server?

Any help would be most welcome -- Thanks ;-)

Are you successfully able to connect to the server on subnet 165.10.10.10 from different subnets after making the connecting users admins on the server machine?

It seems very odd that your network connectivity problem would be solved by adding the member to the administrators group. This is not expected and is not required. Are you using IP security in your environment? What is the precise error message that you're receiving when your connection attemp fails?

To get started on how to provision login access for non-administrator users, here's a good page: http://msdn2.microsoft.com/en-us/library/ms189751.aspx. If you look around this part of the documentation you'll find more details on the minimum set of permissions needed for users to successfully login.

Hope this helps,

Vaughn

|||

Once I add the user to the Administrators group on the host machine she can connect to the database and function.

What strikes me as odd though, is that i still have to actually type in the host/sqlexpress refrence as it will not show up in the

Which server do you want to connect to?

Server -- combo box

The authentication step works

then I can see the compulsionasset database in the

Change default database to checkbox -- drop down combo

When the user is not an administrator

I still have to type the server name, authentication still works, but the database table does not show up for selection, and typing it in I get an error that it doesn't exist so that the ODBC administrator wizard will not complete.

There are no other errors.

Adding her to the administrators group did get her in though.

|||

Oh...

I have given her a login for he domain account within SQLExpress on the appropriate table...

|||

Once I add them as administrator they can connect. It does seem crippled though, usually page ione of the ODBC setup wizard lets you choose from a drop down combo box - the server, I must type the full name in as it doesn't see it. then Page 3 usually lets you change the default database -- as an administrator they can see the database -- if not they can only see the default databases master, tempdb and model. Both means appear to connect -- without error messages on page 2 of the wizard.

|||

I'm not sure if the drop down combo box and the login problem have the same root cause but here's what I'd suggest for fixing the drop down:

1) Ensure the SQL Server instance has TCP/IP enabled (it almost certainly does since remote admin connections work)

2) Ensure the SQL Browser service is running on the server machine

3) Make sure there are no firewalls (e.g. windows firewall) blocking connectivity to the sql server instance or sqlbrowser. For SQL Browser you can either add the executable or open UDP port 1433. For the name SQLExpress instance you'll need to add the process since the TCP port is dynamic unless you've manually configured it otherwise.

4) Double check that in SQL Server Configuration Manager there's not a "HideInstance" flag set to true for the SQLExpress instance

Checking all of these *should* make the instance show up in the drop down box.

|||

All of those have been checked. I can see the server from machines within the same subnet, I cannot from machines on a different subnet. Though those users can browse the host via windows networking., there are other instances that do not show up within the drop down as well, though, again they do when on the same subnet.

All firewalls are external to the network none between subnets.

Again, thanks for any and all help.

I really need a debate! Type - Attributes vs Super Type - Sub Types

I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!

Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:

tbAutombbileCategories
CategoryID | Category
----------
1 | Car
2 | Truck
3 | Motorcycle

tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
--------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity

tbAutomobile
VIN | Category | Make | Model
------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150

tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
----------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000

Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?

tbCategories
CategoryID | Category
--------
1 | Cars
2 | Trucks

tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
------------
1 | 1 |Honda | Accord

tbCars
fkVIN | Doors |
------
1 | 2

tbTrucks
fkVIN | Cab | Capacity
--------
2 | 0 | 1000

Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.

Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...

Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.

E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?

tbTrucks
fkVIN | Cab | Capacity | fkCapID
------------
2 | Y | 1000 | 1

tbCaps
CapID | Vendor | Price | et...

Any thoughts at all? I thought this would have been a pretty damn hot topic!

Mike Bhttp://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever|||Originally posted by Brett Kaiser
http://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever
Your last link is invalid.

Anyway, why did you post all the articles on BCNF? Aren't both examples I have illustrated normalized? If they aren't what rules are broken?

I am a newbie, so any explanation would be appreciated!

Also, what method would you use Brett?

Mike B

Wednesday, March 7, 2012

i need to export a report with sub reports to excel

I need to export a report with sub reports to excel that they include all the information of both

i try that but the exported report not include all information and generate and some times present an error in the sub report space

yo necesito exportar un reporte con sub reportes a excel que incluyan toda la informacion de ambos

yo he intentado esto pero cuando exporto el reporte no incluye toda la informacion y algunas veces reporta un error .

Unfortunately this is just not supported with the RS Excel renderer.

Sunday, February 19, 2012

I need help with a sub query

here is a look at the data in the table:

ID Status Date
-- --- ----
1 A 2/10/04
1 I 2/11/04
1 A 2/23/04
2 A 2/11/04
2 I 2/13/04
2 A 2/14/04
...

...you get the idea.

I need to get a result set that has one row for each ID. I need the row that has the greatest date. For example, I need a result set that contains:

ID Status Date
-- --- ----
1 A 2/23/04
2 A 2/14/04

It seem simple but yet so hard. Any help would be appreciated. TIACheck out the GROUP BY clause. Your query will look something like this:

SELECT ID, MAX(StatusDate) FROM myTable GROUP BY ID

Don|||Please refer to my post above as to the result set that is needed.|||It is hard to read the example table. There are 3 columns: ID, Status, Date

I need the result set to contain all three columns.

Wes|||Here ya go:


select A.[ID] as [ID],
(select Status
from #MyTable B
where B.[ID] = A.[ID]
and B.[Date] = max(A.[Date])) as Status,
max(A.[Date]) as [Date]
from #MyTable A
group by [ID]

Enjoy.|||I was so close to that...I did not have the max() in the WHERE clause of the sub query.
THANKS!