April 12, 2012 at 3:15 am
Hi,
I've uploaded data to SQL Server Express 2008 R2. There is a front end where one can manually add the data. It is a poorly designed system which has no referencial integrity. I inherited this system and have to upload the past financial years data. I have done about 90% of this.
The reporting function on the front end is not pulling all the data through. I've compared records which are pulling through with records which are not pulling through. I'm suspecting it has something to do with the Rowversion column in each table. The table design uses GUIDs. There are 2 (in some cases 3) tables which contain personnel info. The Personnel table, Contacts table and Contractors table. I have to allocate these personnel records to other tables, Employment table and the Tasks table. I have reset the Rowversion columns by using getdate(). However still only 137 of the 559 records are pulling through to the report on the front end.
I'm not well versed with Rowversion except that I think it is timestamp which identifies when a record was entered or updated.
Please could you give me some guidance in this regard?
Kind regards,
Jerome
April 12, 2012 at 5:15 am
Hi Jerome,
The Rowversion datatype is a unique binary number, it doesn't record a date/time.
Is the column of this datatype or just named so? (May show as it's badly-named synonym, timestamp).
If you could share some table definitions & sample data, report query and expected results, we may be able to help with troubleshooting the query.
(Obviously, obfuscate the data if it's private)
Regards
Gaz
April 12, 2012 at 5:21 am
If you have it installed somewhere, you could fire up Profiler and check the actual queries that are being sent from the front-end app. Might give you some clues.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2012 at 5:56 am
Hi Gaz,
It is a datetime datatype. It has a getdate() default set on the column in the design of the table.
This the Personnel table:
Column NameData TypeAllow Nulls
pk_PersGUIDuniqueidentifierUnchecked
sIDNumbernvarchar(13)Checked
sFirstNamenvarchar(30)Unchecked
sSurnamenvarchar(30)Unchecked
uiRegionuniqueidentifierUnchecked
uiGenderuniqueidentifierUnchecked
dDOBdatetimeChecked
uiDisabilityuniqueidentifierUnchecked
bIDCheckedbitUnchecked
uiRaceuniqueidentifierUnchecked
bPreEmployMedicalbitUnchecked
uiEmpStatusuniqueidentifierUnchecked
uiPersTypeuniqueidentifierChecked
bIncompleteIDbitChecked
RowVersiondatetimeChecked
iPersonnelIDintChecked
sContactNumvarchar(30)Checked
uiEduGUIDuniqueidentifierChecked
Unfortunately the program is installed and I don't know the query is. I will look into that Profiler program mentioned in another response.
Thank you very much for your response.
Kind regards,
Jerome
April 12, 2012 at 6:05 am
Thank you for you response. I've never used Profiler before. Are you referring to SQL Profiler? The front end program is installed. Would Profiler still be able to view the queries that are being run via the front end?
April 12, 2012 at 8:10 am
Yep, SQL Profiler runs against the server & collects the queries run there.
If you look for the events RPC:Completed (Stored Procedures class) & SQL:BatchCompleted (TSQL class).
The TextData column will contain the Stored Procedure or SQL that is being executed, and the values of any variables used.
April 12, 2012 at 8:39 am
Hi Gaz ... where exactly would I find that?
April 12, 2012 at 8:55 am
There's a good video here about getting started with Profiler:
April 12, 2012 at 12:09 pm
Profiler is not available on the Express edition. If you have a Standard or Enterprise edition anywhere else on the network you can use the profiler provided with that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply