|
While there is no technical benifit I have always followed these rules
Primary keys is the first field (I almost never use complex keys) and is usually an identity or a GUID inder duress
Foreign key fields (you know all this ####ID fields)
Primary description field(s)
All other fields
Modified and ModifiedBy are generally the last 2 fields on most tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even though it may look nicer to have columns sorted in a particular order, sooner or later you will have no influence on the order any more. For as far as I know it is not possible to insert a column inbetween to existing columns without dropping the entire table and recreating it, which in most large live systems is a big no no as it cost way to much time to do.
|
|
|
|
|
Hi,
Scenario:
I have a database which is going to be replicated via a set of web services. The web services will select all records where [AwaitingSync] = true. I have cerated two triggers for each table which sets [AwaitingSync] to true when a record is inserted or updated. The web service then sets [AwaitingSync] to false for all records.
Problem:
Due to a combination of my inexperience and lack of thought there is a slight problem(you'ver probably already noticed what the problem is!). After synching, when the web service sets [AwaitingSync] to false the update trigger fires and sets it back to true! 
My question is this then; what can I do about this? Can I add a conditional statement to the trigger so that it only fires if a particular column is being updated(or not being updated in this case). Or maybe the trigger be temporarily disabled while the [AwaitingSync] column is being updated. Or are there any other solutions to this?
Thanks for your time.
EDIT: Think I've found a solution but I would still appreciate your views if there is a better way of doing this. I have modified the trigger to check the value of [AwaitingSync] before updating.
IF(SELECT [AwaitingSync] FROM DELETED) = 0
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
modified on Monday, October 25, 2010 11:21 AM
|
|
|
|
|
Your update trigger should exclude the column [Awaiting Synch] from the list of columns that the trigger should fire on. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Ah! I didn't realise that you could specify what columns fired the trigger.
This is the current trigger definition:
CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
Which fires when any column is updated.
Are you saying that it should look something like this:
CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(CategoryName)
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
END
Thanks
|
|
|
|
|
Yes that is correct. I'm not familiar with the SQL Server flavour of doing things, but when defining a trigger you can specify a list of columns as opposed to the entire table, that the trigger is fired on. Glad that has helped you out.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Is is my first time i touching SQL server.
I'm using sql server 2008 express.
I created simple table using Management studio - and i connected to the table from C# code using dataGridView.
I have small simple question ...
1. How can i define a new SP that can take parameters from C# code and how can i call it from C# code ??
2. I add some new raw from the dataGridView - and i don't know why they are not save in the database ( i try to call 'AcceptChanges' - but still this is not working ) .
Thanks for the help.
modified on Monday, October 25, 2010 8:43 AM
|
|
|
|
|
Yanshof wrote: dataGridView
Yuck.
If all you're doing is seeing that it can be done, then use a DataAdapter's Fill and Update methods to move data between the Grid and the database. But it only works for very simple data and you will soon find that that technique is not appropriate for real applications.
For one thing, Grids are generally not a proper way to get user input -- can you imagine an eCommerce site where you placed orders by filling in columns in a grid? No, you should define a form for entering and editing data. You can use a Grid with a subset of the data to allow the user to locate the record he wishes to edit, but never edit within a Grid.
For actual applications you will need a Data Access Layer. A layered application makes swapping parts in and out easier. For instance, at some future time you may want the client to access the database via a Web Service -- if your client is too-tightly bound to your database, that will be much more difficult.
"A stitch in time saves nine." Learn to use proper data access techniques now, before you really need them.
Here[^]'s an article I wrote on one of my data access techniques.
|
|
|
|
|
PIEBALDconsult wrote: never edit within a Grid.
And I would say to that: Never say "never". Without disputing the general thrust of your post, there are occassions when it makes perfect sennse to edit directly wihtin a grid, particularly if the underlying dataset has only a small number of "simple" fields (strings of limited length, or enum types from which the user has to choose for example.) One can (and should) still separate out the data access code into its own class, or layer, but as far as the user is cnncerned the editing can take place right there in the grid.
It all depends on the exact situation, of course, but solutions should fit the problem; one shouldn't force a problem to fit a pre-determined solution.
Just my tuppenceworth.
|
|
|
|
|
In a couple of places I have allowed editing with a Grid, but only out of laziness to allow a knowledgable administrator to configure certain things, never for an average user to muck around with.
|
|
|
|
|
NeverHeardOfMe wrote: And I would say to that: Never say "never"
I have 1 very rigid rule, if you need to edit more than 1 field you will NEVER get a grid approved - go back and do the job properly. If you have only 1 field to edit I want a 3 page justification for not popping a dialog.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why is there such an objection to (simple) editing within a grid? Suppose you have a CMS used by the client to set up his shop. On one page you allow him to define "sections" - eg, "Toys", "Clothes", "Tools".. whatever. So what would be so wrong in displaying a grid of said sections and allowing him to edit their names there and then, and even add a new section via the footer row? I don't want a dialog for that, and I don't want to load a new page just to do this simple task, nor load the entire "section page" listing its name, sub-sections and/or items within it just to do it either.
The trouble with dialogs in web-apps is that they come at a price - either you need the overhead of fancy javascript to create a modal one that also updates the calling page when closed (and, in the case of adding a new section will maybe need to redirect elsewhere too), or you run the risk of pop-up blockers getting in the way, or it openeing in a new tab and the client wondering what's happened.
I don't see the necessity to take a strict "never edit in a griod" attitude. I quite accept it should be used only in limited cases, but not "never".
|
|
|
|
|
NeverHeardOfMe wrote: The trouble with dialogs in web-apps
I was pretty sure the OP was about a datagridview, and yes web apps have a different set of rules.
My primary problem is that most junior devs want to do everything in a grid, which is fine when there are VERY limited interactions required. As soon as the interaction goes past the trivial they find the limitations of DGV editing.
I am finding with Silverlight I can treat the web much like a winforms app, pop a dialog whenever I need to let the user edit the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
NeverHeardOfMe wrote: web-apps
Oh, that's your problem -- they're rubbish.
|
|
|
|
|
I have problem inserting datetime values into my SQL Server 2008 R2.
The server is capable of handling ISO 8601 format, but If I send them as parameters, the SQL Server/OLE DB provicer, seems to be less tolerant on the timestamp string format.
I use OLE DB from C++ and would like to make my insert statement parametrized
INSERT INTO BATCH VALUES (?, ?, ?, ?....
When I specify timestamp values on the ISO8600 format, "2010-10-23T19:41:56.002+02:00", I get errors.
When enter the same timestamp format directly in the statement, I don't get errors
INSERT INTO BATCH VALUES ('2010-10-23T19:41:56.002+02:00', ?, ?, ?....
I have tried two different OLE DB providers. "Microsoft SQL Server Native Client 10.0" and "Microsoft OLE DB Provider for SQL Server"
They both behave slightly differently.
When sending the timestamp values as part of the SQL INSET, both providers accepts the following formats
2010-10-23 19:41:58.000
2010-10-23T19:41:57.001
2010-10-23 19:41:56.002+02:00
2010-10-23T19:41:55.003+02:00
When sending timestamp as parameter:
2010-10-21 13:44:59.092 Is accepted by both
2010-10-21T13:44:57.092 Is rejected by Microsoft SQL Server Native Client 10.0 and accepted by the other
2010-10-23 19:41:56.002+02:00 Rejected by both providers
2010-10-23T19:41:55.003+02:00 Rejected by both providers
The both providers also report different errors
Microsoft OLE DB Provider for SQL Server reports "Conversion failed when converting date and/or time from character string" which I may understand.
The "Microsoft SQL Server Native Client 10.0" reports HRESULT=DB_S_ERRORSOCCURRED.
I would be glad if someone could explain these differences, especially why I can send the timestamp stirng litterally in the sql but not as a parameter.
|
|
|
|
|
Try a parameterized query. I do not know how it works with C++, it should be something like:
OleDbCommand cmd = new OleDbCommand("Insert .....");
cmd.Parameters.AddwithValue("ParameterName1", timeStampValue);
Note: you use here the timestamp value as such, you *must not* convert it to a string!
|
|
|
|
|
I am using Visual Studio 2010 Professional.
I have created a new SQL Server 2008 Database Project, locally applied and deployed a simple scheme and stored procedure. Everything looks as it should until I try to create the unit test by right clicking the stored procedure, is this functionality only supported in Premium and Ultimate?
thanks.
dotman1
|
|
|
|
|
hi all,
i develope a project using c# and sql server 2005, the problem is i want to make a column modifiable in sql server database, and another column to unmodifiable.
how i can set column property to modified or not modified in sql server 2005?.
thanks all
|
|
|
|
|
You are attempting to do this in the wrong place. A database is for storing and manipulating data. The state of the column is a UI issue not a database issue.
Change the properties of your list control to make it read only.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
i know that, but i the column properties in the database to be unmodified,
in another database the property is exist, but in sql server i can't reach the property.
thanks
|
|
|
|
|
zead wrote: i know that
And yet you insist that SQL server supply this UI functionality!
zead wrote: in another database the property is exist
 What is the other database?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
like pervasive database
|
|
|
|
|
Sorry I had not heard of pervasive. If they supply the functionality you need then use that database, expecting another vendor to match esoteric (and wrong) requirements is not reasonable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys
I detached my database, working for months. after one week I decide to attach it agian. but, I ot the following error:
<br />
<br />
TITLE: Microsoft SQL Server Management Studio<br />
------------------------------<br />
<br />
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)<br />
<br />
For help, click: http:
<br />
------------------------------<br />
ADDITIONAL INFORMATION:<br />
<br />
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)<br />
<br />
------------------------------<br />
<br />
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Hostel.mdf'. (Microsoft SQL Server, Error: 5123)<br />
<br />
For help, click: http:
<br />
------------------------------<br />
BUTTONS:<br />
<br />
OK<br />
------------------------------<br />
<br />
Please help,
I went through many forums on the website but hasn't help me yet.
|
|
|
|
|
I sort out y problem.Righr click and then run as Administrator
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
