Hi,

we are using SQL Server 2005 and replicate Data via "Mergereplication" to SQL Server 2005 Express Edition clients.

Its necessary to have a GUID column in every table to do so. Every column generated in the process of the Replication setup has its default value set to "newsequentialid()". The stored procedure generation template does not recognize this and tries to include the colum in the insert-proc.

The workaround was to change the code in the template to treat newsequentialid() like newid().

This generates

Its not possible to call newsequentialid() inside a stored procedure, i have no idea what the difference is anyway. 

My workaround seems to work fine by now, just wanted to let you know, maybe you even have some better ideas.

Bye,

Joris Bauer 

Hi Joris

I've just had a quick look at the MSDN docs for newsequentialID and it appears to function like the newID() function but it creates new GUIDs that are larger than any previously issued GUIDs so it's almost a kind of auto-increment GUID (which apparently you should avoid if you're concerned about privacy as it may be possible to guess/predict the next value etc).

Anyway, if it's set up as a default for your DB column then I think you need to alter the SPROC to either pass nothing at all to that column or to use the "DEFAULT VALUES" or "DEFAULT" keywords in the Insert proc (as defaults are only used if no value is supplied or the default keyword specified) - as it is the SPROC you've shown above effectively replaces the SQL Default with one that the SPROC creates (hope that makes sense).

I'm sure the ES guys will come back to you with their input as far as the templates/MyGeneration etc goes on this but thought the above might help you make any decisions etc.

Cheers

Martin

Thankyou both.

We´ll go with newid(), works fine by now. I don´t think it would really be a privacy issue using newsequentialid(), as the only use of that column is to merge tables in the replication process. 

I think the best solution would be to not pass this column in the Insert proc at all. It would even be better to not let ES know from that column at all - don´t think thats possible. There´s absolutely no need to change/read that column in the application. Maybe theres a way to recognize "replication-columns" and have an option to exclude them.

Joris 

 edit: just saw that there is an option "RowGuid" in SQL Server 2005. Would be really great to have an option to ignore those column completly.
 

Hi Joris

I think you're correct in suggesting that the field be ignored if possible (maybe it is possible through manually altering your table MetaData - check with the ES guys on that) but I don't think there's any way of automatically recognising replication type fields as although You know that's the use for this particular GUID, as far as any software is concerned (incl MyGeneration) it could just as easily be an actual identifier for that row etc.

Anyway, as I say, hopefully the ES guys will come back to you with options on excluding the field 'post' generation.  Alternatively, is it possible for you to create a copy of the definition for that table, exclude the GUID field, re-gen the 'Generated' (and SPROC if you need it) code just against that table and use the results of that in your project instead of changing the generated code? Just a suggestion.

Cheers

Martin

Hi Martin,

 you`re just too fast :)

See my edit in the previous post. The idea of a copied table is great to tweak the application, but we´re not in a that state yet :)

Joris 

Jobo:

you`re just too fast :)

 

Yup - I know I should probably resist the urge to reply and leave it to the ES team but prior to finding EntitySpaces I was using an open source project and posted regularly on the forums there so old habits are hard to kick I'm afraid!

Anyway, re: your edit, I'm not sure what the RowGUID flag(?) is used for so I'm not sure on this - not sure how easy it would be to optionally exclude fields from tables during generation though as I'm a new user of MyGeneration - again, best leave the difficult answers to the ES guys so I'll 'back off' on this one (for now at least!)

Cheers

Martin

Martin, Your user to user help is always appreciated. I think we have an opening on the support forum night shift, if you want to apply

Joris, I added this to our enhancement request list. MyGeneration could easily handle this using column specific UserMetaData. You would add a special key/value pair on the column that the EntitySpaces templates would recognize. For example, a key of ESIGNORE, with a value of true. The templates would default to false, so it would only need to be added for the ignored columns. UserMetaData is saved to an XML file, so you would only need to do it once.

The tricky part is changing all the templates to recognize it. So, this is a "down the road" feature possibility.

 The newsequentialid tweak is also on the list, and should be easier to implement.

Hi again with another question regarding this columns.

The templates also generates the Update-Procs including those "Rowguid"-columns. This results in an SQL-Error that "Rowguid"-columns cannot be changed within an Update. Makes sense, although in fact nothing would be changed as we don´t do anything with the value of those properties.

My question now is what would i have to change in the template code do get those columns "not-really-set"? I have to keep them in the parameterlist(because the generated classes are expecting that parameter), but i dont need them to be actually set, as this results in the mentioned error. This sounds kinda nasty but i can live for that to some point further down the road

I do wonder if there´s nobody else using ES within an Merge-Replication-Environment? We could make copies of those tables for generation of the procedures and the classes(thanks martin), but that would be really annoying at this point as we still frequently change things.

We already have 2 Developer-Licenses in the company and we will do more projects with Entityspaces if that helps

Thanks in advance,

Joris


 

Hi

I've just taken a quick look at the SPROC generation templates and, although I don't think you're allowed to post code from the purchased templates (might be wrong on that but I'll err on the safety-side and just include the appropriate snippet), I'd suggest the following - but please do bear in mind the fact that I'm new to MyGeneration templates etc so this may not be the preferred way.

 1) Don't make changes to the actual SPROC generation template, instead, make a copy and just alter  (and use) one instead.

 2) About half-way through the template (using notepad to edit it) you should see something like:

For Each objColumn In objTable.Columns

If Not objColumn.IsInPrimaryKey And Not objColumn.IsAutoKey And Not objColumn.IsComputed Then


If Not blnFirst Then 

output.write "," & vbCrLf

End if

blnFirst = False

output.write " [" & objColumn.Name & "] = @" & TrimSpaces(objColumn.Name)

End If 

Next

The check at the start of this foreach basically means that it will only generate the 'fieldname = @fieldname' code in the SPROC so if you also add a check here for a GUID field (something like: And Not objColumn.DataTypeNameComplete = "uniqueidentifier") then it should exclude your GUID cols.  NB: This exclusion will mean that any GUID field in your db will not be included in the 'update' sprocs for that table.

Anyway, hope that helps - or at least gets you started.

Cheers

Martin

CREATE TABLE [dbo].[ComponentGroups](
	[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[ParentRef] [int] NULL,
	[Name] [nvarchar](50) NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [MSmerge_df_rowguid_46DC6C84BF4345CFA96DD8CD143986FE]  DEFAULT (newsequentialid()),
 CONSTRAINT [PK_ComponentGroups] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Here´s a simple CREATE.

 The key to the problem should be ROWGUIDCOL, which i think could be - at least optional - totally ignored.

Would it be possible to use something like "objColumn.RowGuidCol = true" in the template? Don´t know if there´s is documentation for that to be honest.
 

Thanks for the fast answers again, never encountered such a helpful support forum anywhere before  

Hi

I'm not at a development machine at the moment (and won't be for a few hours) but if my suggested change to the SPROC template above won't get you where you need to be then I'd open up MyGeneration and use the Meta explorer to see what info it is picking up on those columns and see if there is anything that uniquely identifies them - if there is then I imagine it would be very straight forward to add that type of functionality/testing in the templates.

Cheers

Martin

Hi

Just another thought, following up from David's post earlier in the thread about using your own Meta data (that you would either add via the MyGen interface or in code - up to you).  If you take a look at the IProperty Interface entry in the MyGeneration MyMeta API help then it looks like it should be fairly trivial for you to add a "DontGenerateForThisField = TRUE" key/value pair for the fields in question (you'd only do this once I believe) then check for this value in the SPROC generation template (in the same place as I pointed you to before if you just want to change the 'Update' SPROCs) then this should also get you to where you need to be.

Hope that helps

Martin

Hi Martin,

 thanks for your input, i will take a look into that. I never looked deeper into MyGeneration Template stuff than changing one line so it handles newsequentialid() like newid(). Would your idea also apply to the generated classes? That would be another change i guess, and i don´t know if that´s possible or if its esPlugin-Stuff. The problem is ES "thinks" the parameter exists in the sproc which leads to the mentioned error when updating.

Another thought here: i tried to make 2 guid-columns, one with ROWGUIDCOL and newsequentialid(), the other without:

CREATE TABLE [dbo].[TestGuidTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_TestGuidTable_rowguid]  DEFAULT (newsequentialid()),
	[testguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TestGuidTable_testguid]  DEFAULT (newid())
) ON [PRIMARY]

 The only thing different in the MetaData as seen in MyGeneration is the default value...

That means we can´t know in MyGeneration if ROWGUIDCOL is set, right? Diggin deeper....

 
Bye,

Joris