Database

Mycroft Holmes10-Jul-14 14:15

10-Jul-14 14:15

Personally I don't consider that an audit solution, I say solution because audit is a whole lot of objects. We do a field level audit so we can track any changes to the data. Simply the ID for an inserted record, each field that is changed - only getting the from value and each field from a deleted record.

In theory this allows us to reproduce any action done by the users. Also we do not audit every table as the apps we build are based on other systems so we only track the master table changes.

The audit is initiated by a trigger spit, the ONLY valid use for the dammed things. This is our audit table.

SQL

CREATE TABLE [dbo].[AuditLog](
	[AuditID] [INT] IDENTITY(1,1) NOT NULL,
	[Action] [CHAR](1) NULL,
	[TableName] [VARCHAR](128) NULL,
	[PrimaryKeyField] [VARCHAR](1000) NULL,
	[PrimaryKeyValue] [VARCHAR](1000) NULL,
	[FieldName] [VARCHAR](500) NULL,
	[OldValue] [VARCHAR](1000) NULL,
	[ModifiedDate] [DATETIME] NULL,
	[UserName] [VARCHAR](200) NULL
) ON [PRIMARY]

Every table has a modified and modifiedby field. We also have a table of table names that allows us to run a script and apply/remove triggers from the target tables. As I said theres a whole ecosystem for auditing and it all lives in our model database so it is automatically included in every new database created.

In a vast number of years building LOB apps and some of them transactional I have never been hit by the condition of 2 users editing the same record except in 2 cases, contrived conditions to test the effect and in the early 90s using Microsoft Access in a multi user environment. It is a completed furfy IMNSHO.

Never underestimate the power of human stupidity
RAH

GuyThiebaut11-Jul-14 2:09

GuyThiebaut

11-Jul-14 2:09

I will second Mycroft's design which is almost identical to the audit table I created.

CRUD dates alone will be of little help as they will not give any context to the data.
Audit tends to mean that you can rebuild a row's values at any point in time.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Eddy Vluggen11-Jul-14 7:28

11-Jul-14 7:28

Mycroft Holmes wrote:
I have never been hit by the condition of 2 users editing the same record except in 2 cases

That doesn't mean that the concurrency-issue does not exist. Depending on the amount of operations, once in a million could happen next monday.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jschell11-Jul-14 9:07

11-Jul-14 9:07

Eddy Vluggen wrote:
Depending on the amount of operations, once in a million could happen next monday.

No that isn't what that means at all.

If you have a business case where modification of the same record by two users can occur then the number of operations has nothing to do with it. When one has such a business case then one should of course take the necessary precautions, based on business needs (not just implementation hacking) to insure that a the best and most correct result is arrived at.

If there is no business case then the only way that can happen is due to poor design and/or implementation. Or perhaps just flat out wrong design/implementation.

Eddy Vluggen11-Jul-14 9:44

11-Jul-14 9:44

jschell wrote:
based on business needs (not just implementation hacking) to insure that a the
best and most correct result is arrived at.

Two of us get called by a customer and update the same issue in the bugtracker. Whose result is the most "best and correct"?

You better make sure it's my edit, not yours.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes11-Jul-14 14:12

11-Jul-14 14:12

Eddy Vluggen wrote:
Two of us get called by a customer and update the same issue in the bugtracker

I would contend that that is not a reasonable business case, the same bug. Still it MAY be possible and you would have to design around it. However most requirements for this are just not reasonable.

Never underestimate the power of human stupidity
RAH

jschell12-Jul-14 10:33

12-Jul-14 10:33

Eddy Vluggen wrote:
Two of us get called by a customer and update the same issue in the bugtracker

Ok.

First exactly what sort of business scenario is going to lead to two different customer employees to call at the same time to have one modify the very same bug?

Second presumably you are referring to that those two different customer employees are acting independently and both wish to resolve the bug, and not just comment on it, in different ways.

So exactly how, in terms of business usage, if those two same people called at different times would you correctly resolve updating the status two completely different ways? Say one calls on tuesday and says they want to close the bug as no longer applicable and a completely different person calls on wednesday and says they want to increase the priority to critical. Exactly how is your software, and only your software, going to resolve that? And just to make it more fun lets say the guy on tuesday is the CTO of the customers company and the guy on wednesday is a junior developer. (Keeping in mind of course that this is your scenario where two completely different people are interacting with your company at the same time.)

Third, "Whose result is the most 'best and correct'?", is exactly the question. That is business decision not a software decision. You cannot write software that is going to answer that question.

Eddy Vluggen12-Jul-14 22:57

12-Jul-14 22:57

jschell wrote:
First exactly what sort of business scenario is going to lead to two
different customer employees to call at the same time to have one modify the
very same bug?

Try the one I painted.

jschell wrote:
Second presumably you are referring to that those two different customer
employees are acting independently and both wish to resolve the bug, and not
just comment on it, in different ways.

Yeah, easiest way to not have the problem is to not edit, but to add.

jschell wrote:
would you correctly resolve updating the status two completely different ways?

Yes.

But then again, we already noted that adding a record, as opposed to editing one, would solve that.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jschell14-Jul-14 9:05

14-Jul-14 9:05

Eddy Vluggen wrote:
Try the one I painted.

You have a customer with two different employees. Both of them call the service desk at 8:50am on Tuesday June 6th. Both want to do wildly different things to the same thing.

This means the customer has tasked them both with the same task. And that both reached wildly different outcomes with regard to what should happen.

No that doesn't seem like a scenario which is likely to occur and in fact is wildly unlikely to occur. Thus prioritizing this even to the extent of writing the requirement much less implementing it seems very unlikely.

Eddy Vluggen wrote:
But then again, we already noted that adding a record, as opposed to editing one, would solve that.

But again that is not the point. There are valid, probable and needful reasons for adding a record versus update that have nothing to do with the highly likely scenario that you have posited.

jschell11-Jul-14 9:11

11-Jul-14 9:11

Mycroft Holmes wrote:
Personally I don't consider that an audit solution, I say solution because audit is a whole lot of objects.

However that should depend on business needs. If you need to track every change in the system then something like your solution is required.

But there can be a much lower need so less of a solution might be sufficient.

Mycroft Holmes wrote:
In a vast number of years building LOB apps and some of them transactional I have never been hit by the condition of 2 users editing the same record

I have seen a number of developers and QA people come up with that situation.

Took me quite a few years to realize that I should start asking them what their business case was for the scenario.

Mycroft Holmes11-Jul-14 14:19

SQL Server Express Setup Command Line Problem

11-Jul-14 14:19

jschell wrote:
However that should depend on business needs

Always, this is the mantra I repeat at the start of every requirements meeting, I want it tattoo'd on the foreheads of every BA, I want "it is your job to interpret their requirements CORRECTLY" tattoo'd on their buts!

I'm old I've been doing this for a bloody long time, the number of times I have had this ~~argument~~ discussion with BAs and user are innumerable. I just hate it when I have to pound it into the head of a senior dev.

Mind you I'm delighted when they (the BAs and senior devs) come up with some novel concepts especially when they work.

Never underestimate the power of human stupidity
RAH

Amr Muhammed9-Jul-14 11:25

Amr Muhammed

9-Jul-14 11:25

I need to setup SQL Server Express using the command line and also make the installation unattended I tried many command lines however each one of them did not work here is the last one:

Setup.exe /ADDCURRENTUSERASSQLADMIN="True" /AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVIC" /AGTSVCSTARTUPTYPE="Disabled" /ASBACKUPDIR="Backup" /ASCOLLATION="Latin1_General_CI_AS" /ASCONFIGDIR="Config" /ASDATADIR="Data" /ASLOGDIR="Log" /ASPROVIDERMSOLAP="1" /ASSVCSTARTUPTYPE="Automatic" /ASTEMPDIR="Temp" /BROWSERSVCSTARTUPTYPE="Disabled" /ENABLERANU="True" /ENU="True" /ERRORREPORTING="False" /FARMADMINPORT="0" /FEATURES="SQLENGINE,REPLICATION" /FILESTREAMLEVEL="0" /HELP="False" /INDICATEPROGRESS="False" /INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server\" /INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server\" /INSTANCEDIR="C:\Program Files\Microsoft SQL Server\" /INSTANCEID="SQLExpress" /INSTANCENAME="SQLExpress" /ISSVCACCOUNT="NT AUTHORITY\NetworkService" /ISSVCSTARTUPTYPE="Automatic" /NPENABLED="0" /QUIET="True" /QUIETSIMPLE="False" /ROLE="AllFeatures_WithDefaults" /RSINSTALLMODE="FilesOnlyMode" /RSSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /RSSVCSTARTUPTYPE="Automatic" /SECURITYMODE="SQL" /SQLCOLLATION="Arabic_CI_AS" /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /SQLSVCSTARTUPTYPE="Automatic" /SQMREPORTING="False" /TCPENABLED="0" /UIMODE="" /X86="False" /IACCEPTSQLSERVERLICENSETERMS /ACTION="Install"

But also it did not work. Could any one tell the correct syntax to install the SQL Server Express 2008 x64

Re: SQL Server Express Setup Command Line Problem

Bernhard Hiller9-Jul-14 21:11

Bernhard Hiller

9-Jul-14 21:11

Amr Muhammed wrote:
But also it did not work

That's a great statement. What was the result? Did you get any error messages? What does the log file of the installer tell you?

Re: SQL Server Express Setup Command Line Problem

Amr Muhammed11-Jul-14 3:28

Amr Muhammed

11-Jul-14 3:28

The installer every time was telling me that the installation failed and I did not know the reason however finally I got the solution the .exe package I was trying to install seems to be not complete as I download another one from microsoft site and when I tried again it worked fine.

Error converting data type varchar to numeric

Maideen Abdul Kader5-Jul-14 19:42

Maideen Abdul Kader

5-Jul-14 19:42

Hi

i have issue in stored procedure converting data type

Destination table dbo.Z_tbl_CirculationCopy_TEMP has numeric data type
But source table Z_tbl_Import_temp have VarChar Type

If I export data from source, I have error message

Msg 8114, Level 16, State 5, Procedure Z_usp_IMPORT_EXCEL_UNPIVOT_1, Line 10
Error converting data type varchar to numeric.

Pls help to resolve this issue

Thanking you in advance

Maideen

Below is my store procedure

SQL

INSERT INTO dbo.Z_tbl_CirculationCopy_TEMP([MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total])

SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total]

FROM (SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[MONTHNAME],[YEARNO],[REMARKS],
[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20], [BCP_20_Below],
[WP_TOTAL],[SABAH_Total],[SARAWAK_Total],[EM_Total],
[Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
FROM dbo.Z_tbl_Import_temp ) P

UNPIVOT (Copies FOR State IN ([Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],
[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[Sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
))AS unpvt

Re: Error converting data type varchar to numeric

Mycroft Holmes6-Jul-14 13:01

Can not connect to my database from asp .net application

6-Jul-14 13:01

Your excel sheet is probably formatted with comma.

You should split out your unpivot and inspect the results of the query to try and determine the invalid data, look for blanks, character and formatting (commans)

Never underestimate the power of human stupidity
RAH

Stephen Holdorf5-Jul-14 0:15

Re: Can not connect to my database from asp .net application

5-Jul-14 0:15

I have a named instance of 2008 r2 located on my windows 7 professional local machine. I have tried to get my connect string to work but I just cant. I am using machine name "HOLDORF-PC" SQL instance name "SQL_2008_R2" and the rest of the connection string. Here is my connection string:

"Data Source=HOLDORF-PC\\SQL_2008_R2;Initial Catalog=EFDataModel" providerName="System.Data.SqlClient" />

Please help

Steve Holdorf

Eddy Vluggen5-Jul-14 1:23

Re: Can not connect to my database from asp .net application

5-Jul-14 1:23

The CS does not say whether you want to use mixed mode or windows authentication. There's examples here[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Stephen Holdorf5-Jul-14 2:03

Re: Can not connect to my database from asp .net application

5-Jul-14 2:03

I tried this:

"Data Source=HOLDORF-PC\\SQL_2008_R2;Initial Catalog=EFDataModel; Integrated Security=SSPI" providerName="System.Data.SqlClient"

and it still does not work?

Stephen Holdorf5-Jul-14 2:08

Re: Can not connect to my database from asp .net application

5-Jul-14 2:08

Sorry, I tried this:

"Data Source=HOLDORF-PC\\SQL_2008_R2;Initial Catalog=EFDataModel; Integrated Security=True"

and it still doesn't work. One point is that this is the connection string that coexists with my Entity Framework model.

Stephen Holdorf5-Jul-14 2:26

Re: Can not connect to my database from asp .net application

5-Jul-14 2:26

OK. I think you were right on the DB connection string. Now, I'm getting this error:

An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

Here is my Entity Framework connection string:

<add name="EFDataModelEntities2" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=HOLDORF-PC\SQL_2008_R2;initial catalog=EFDataModel;integrated security=True;multipleactiveresultsets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

And here is my Model connections:

<EntityContainer Name="EFDataModelEntities2" annotation:LazyLoadingEnabled="true">

<EntityContainerMapping StorageEntityContainer="EFDataModelModelStoreContainer" CdmEntityContainer="EFDataModelEntities2">

Eddy Vluggen5-Jul-14 22:46