|
- Enable the TCP/IP protocol (see configuration).
- Open port 1433 on your firewall.
- Setup port forwarding on your router.
..and start praying; a SQL Server directly connected to the internet is a bad idea. Expect quite some people poking the server to check its security.
The "best" way to expose your database would be by using webservices 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
This is my first database project and I am having difficulties forming the correct relationship between two tables. The database is storing inventory records for approximately 20 buildings, and the room numbers in many of the building are numbered the same.
One table has columns like Asset Number, PO Number, Equipment Type, Manufacturer, Model Number, Serial Number. The PK is the Asset Number.
The second table has Building Name, Room Number, Room Type, Staff Name. The key for this table is a combination of Building Name and Room Number. I need both to uniquely identify the location.
My question is, how do I go about creating the relationship between the tables. The relationship would be described as each Asset having a unique Building/Room Number, but each Building/Room Number can have many Assets.
When I try to create the one-to-many relationship it tells me "Both sides of a relationship must have the same number of columns"
|
|
|
|
|
All of the primary key columns from the table on the "one" side need to be present in the table on the "many" side. In this case, you would add both building name and room number to your assets table, and specify both columns in the FOREIGN KEY relationship.
I'd be inclined to move the buildings out to a separate table with a surrogate key, since the value is likely to be repeated across many rows in the rooms table. Similarly, room type and staff name might be better in their own lookup tables, depending on your data.
Buildings
---------
BuildingId (PK)
BuildingName
Rooms
-----
BuildingId (PK, FK->Buildings)
RoomNumber (PK)
...
Assets
------
AssetNumber (PK)
BuildingId (FK->Rooms)
RoomNumber (FK->Rooms)
...
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank You Richard, I will take your suggestion and do a bit of experimentation. It's clearly the case that a few hours of video at an online training site has not fully educated me. LOL... But it may be enough to get me through this project. I will try and find ( and study ) some documentation on database theory when I get a chance. But until then... Thanks
|
|
|
|
|
As a STRONG suggestion you should devote a couple of days to a basic database design book. The consequences of getting your data structure wrong will cost you many multiples of that investment down the track.
Depending on the criticality of the project I would even consider getting a professional to do the basic DB design. There is also a site with sample schemas (I have lost the link) that will give you some excellent pointers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To somewhat piggy-back on the previous suggestions, I would recommend that you consider using an Identity column for your Primary Key and then using the PK to accomplish the links beween tables.
For Example:
Table: Buildings
BuildingID [int] Primary Key Identity (1-1)
BuildingName [VarChar] (25)
...
Table: BuildingRooms
BuildingRoomID [int] Primary Key Identity (1-1)
BuildingID INT
RoomName [Varchar] (10)
...
Table: Assets
AssetID [int] Primary Key Identity (1-1)
AssetNumber [varchar] (25)
AssetName [VarChar] (50)
BuildingRoomID [int]
...
By using using the Identity columns for the PK's of the tables, you can make your joins much more easily. You may also want to add some Unique Indexes on, for instance, the BuildingName, RoomName, and AssetNumber columns just to make sure someone doesn't accidentally add those items multiple times. 
Check out the following link regarding what is termed "normalization" . . . which is what we have suggested with regard to your tables.
http://www.dbnormalization.com/rules-of-normalization-i
|
|
|
|
|
I've spent some time looking into normalization and currently my tables should be in 3rd Normal Form. I also have my application talking to the remote SQL server via the Entity Framework, so I'm getting there. Thanks all for your suggestions, they were most helpful.
Rick...
|
|
|
|
|
I using three textbox(txtName,txtmobile,txtremark)and two skinid for this textboxs(snTextBox,snTextBoxNumeric)If cursor focused in txtname or txtremark mean defaultbutton is working,while its in txtmobile mean defaultbutton is not working because skinid for that textbox is different.
|
|
|
|
|
This is the database-forum. What does your question have to do with databases?
FWIW, you'd also need to explain which type of GUI you're building; ASP.NET, WinForms, WPF, Gtk# - otherwise you'll be getting answers that assume the wrong technology.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: What does your question have to do with databases?
Access 
I'm betting it is a miss post but some people call Access a database, much to my disgust!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you define a database as something that stores data, then by all means Access is indeed a database. It even stores data in a relational format and therefore qualifies to be called a Relational database as well.
|
|
|
|
|
I don't consider "stores data" as a definition of a database! Word, excel, xml, CSV, bin with screwed up paper in it all store data!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can I obtain total number of years, months and days between 2 dates in a single sql query.
eg :
date1 = 2012-07-02
date2 = 2013-09-04
difference between date1 and date2 should show as "1 year, 2 months and 2 days"
Datediff function returns difference only by years or months or days etc...how can i do that
|
|
|
|
|
maxRazar wrote: difference between date1 and date2 should show as "1 year, 2 months and 2 days"
That's usually not done at the SQL-level, but in code. The SQL-statement would return the difference (just subtract one from the other). It's a bad idea to format data in Sql.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm with Eddy on this one do your formatting in the UI.
However did it occur to you to do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
That won't work. Consider:
declare @start date = '20121231';
declare @end date = '20130101';
select
cast(datediff(year, @start, @end) as varchar(5)) + ' year, '
+ cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
+ cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;
The output will be 1 year, 1 month, 1 day, which is obviously wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
As stated, the query you showed will provide the wrong results, however, that doesn't mean datediff 3 times can't be used. There is just more to it.
Tim
|
|
|
|
|
You are right, a simple datediff 3 times will not do it, however with the application of a little imagination and some work with SSMS I know you can do this, I have seen it done in an example somewhere but dammed if I am willing to chase it down.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As other's have said, formatting shouldn't be done in SQL. However, if that is the requirment of the application, other replies do provide some insight.
Post what you have and then it can be reviewed and commented on.
Tim
|
|
|
|
|
I am in the middle of development on a WPF/SQL 2008 project.
As I make changes to the application I need to push changes to my client for testing. So I can create a ClickOnce for the binaries, but what's the right way to push DB changes?
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
We use Red-Gates SQL Compare to deploy to UAT (production deployment is a more stringent process). Data compare to synch the master tables. I think it can be automated but we do a manual deployment as reports are built against UAT and not the dev database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm experimenting with encrypting some values in SQL Server (2012). This requires that I open the appropriate key(s) for the user/role. I haven't seen any information on doing this automatically.
I have written a procedure that will open keys by querying a table that associates users with keys. When I open the database in ADO.net via C#, I can simply check for the existence of the procedure and execute it before proceding.
[I see that procedures can be executed when the server starts, but that's not what I want.]
But it seems like there should be a standard/conventional/"best practice" way to do this at the database level so that the keys get opened even when I connect from SSMS or SSIS.
Can keys be opened automatically depending on the user?
Can a procedure be executed automatically when a user connects?
|
|
|
|
|
More of a poll than a real question, I guess, but here goes:
I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship.
Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing.
What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
|
|
|
|
|
If the system performance is currently acceptable and is unlikely to change based on data growth, they why change the system?
However, if you view this as an opportunity to improve the system design and it will not adversely affect other work or performance, then change it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
