Cause INSERT statements without column lists to fail

Hi Hilary, Paul....
I have another real problem I hope you can help with.
Setup replication now with no problems but one!
The database tables fail from the application thats using them because of
the ROWQUID column.
I noticed that JUST before the articles are created it shows this;
SQL Server requires that all merge articles contain a uniqueidentifier
column with a unique index and the ROWGUIDCOL property. SQL Server will add a
uniqueidentifier column to published tables that do not have one when the
first snapshot is generated.
Adding a new column will:
? Cause INSERT statements without column lists to fail
? Increase the size of the table
? Increase the time required to generate the first snapshot
So is there ANY way of getting round this bug because my developer said that
they will not alter their software and the way the tables are accessed.
So, any ideas.
First you have to find out if the code does do something like this
Insert into tablename
select * from anothertablename
This is what will break with merge replication. Ask him specifically about
this. Personally good developers would never do anything like this. They
would also access the database through stored procedures which would allow
you, the dba, to modify something like this.
Another method is to have the application talk to a view which only contains
the non rowguid column. This isn't very scalable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
> Hi Hilary, Paul....
> I have another real problem I hope you can help with.
> Setup replication now with no problems but one!
> The database tables fail from the application thats using them because of
> the ROWQUID column.
> I noticed that JUST before the articles are created it shows this;
> SQL Server requires that all merge articles contain a uniqueidentifier
> column with a unique index and the ROWGUIDCOL property. SQL Server will
> add a
> uniqueidentifier column to published tables that do not have one when the
> first snapshot is generated.
> Adding a new column will:
> Cause INSERT statements without column lists to fail
> Increase the size of the table
> Increase the time required to generate the first snapshot
> So is there ANY way of getting round this bug because my developer said
> that
> they will not alter their software and the way the tables are accessed.
> So, any ideas.
|||Hi,
No it looks like they have hard coded this one particluar SQL statement.
So what acutally changes in the database that affects the table INSERT
commands?.
Is their a way round this. If there isnt then its back to square one for
me...
Thanks
"Hilary Cotter" wrote:

> First you have to find out if the code does do something like this
> Insert into tablename
> select * from anothertablename
> This is what will break with merge replication. Ask him specifically about
> this. Personally good developers would never do anything like this. They
> would also access the database through stored procedures which would allow
> you, the dba, to modify something like this.
> Another method is to have the application talk to a view which only contains
> the non rowguid column. This isn't very scalable.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
>
>
|||You say one particular SQL Statement - is it in a stored procedure? If so,
just modify the stored procedure so it contains all the column name except
the rowguid column.
ie before
insert into mytable
select * from myothertable
after
insert into mytable
select col1,col2, col3, col4,... from myothertable.
If this can't be done change the table the app writes to so it is now a
view. So if the app wrote to Mytable rename mytable as mytable and create a
view called mytable. This view will look like this create view mytable as
select col1, col2, col3, col4,... from mytable1
This doesn't scale well. How many nodes in your system? You might want to
look at bi-directional transactional replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...[vbcol=seagreen]
> Hi,
> No it looks like they have hard coded this one particluar SQL statement.
> So what acutally changes in the database that affects the table INSERT
> commands?.
> Is their a way round this. If there isnt then its back to square one for
> me...
> Thanks
> "Hilary Cotter" wrote:
|||Hi Hilary,
I think I found the problem but I dont know how to get to it.
The dependencies show 3 objects that have a green plus sign next to them.
These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see these
anywhere in the database?.
Any ideas?
TIM
"Hilary Cotter" wrote:

> You say one particular SQL Statement - is it in a stored procedure? If so,
> just modify the stored procedure so it contains all the column name except
> the rowguid column.
> ie before
> insert into mytable
> select * from myothertable
> after
> insert into mytable
> select col1,col2, col3, col4,... from myothertable.
> If this can't be done change the table the app writes to so it is now a
> view. So if the app wrote to Mytable rename mytable as mytable and create a
> view called mytable. This view will look like this create view mytable as
> select col1, col2, col3, col4,... from mytable1
> This doesn't scale well. How many nodes in your system? You might want to
> look at bi-directional transactional replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...
>
>
|||Where are you seeing these green arrows?
Can you do the following for me?
select name from sysobjects where name in (PHONE_ADD, PHONE_DELETE,
PHONE_AMEND) and type='u'
GO
select object_name(id),name from syscolumns where name in (PHONE_ADD,
PHONE_DELETE, PHONE_AMEND)
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:ACD9DC12-A2C2-4E65-83C8-3D4E39EE8109@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think I found the problem but I dont know how to get to it.
> The dependencies show 3 objects that have a green plus sign next to them.
> These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see
> these
> anywhere in the database?.
> Any ideas?
> TIM
> "Hilary Cotter" wrote:

Casing

What is the accepted way of Casing Table,Fields and Stored Procedures?
This is a real problem when mixing and matching.
For example, C uses lower case for everything, but when calling .Net
functions - you have to use Pascal Casing (1st Character of all words
Capitalized). In VB.Net, you use Pascal style for everthing but variables
where you use Camel Casing (1st Character of all ll words capitalized,
except the 1st word).
I tend to use Microsofts guidelines where everything but variables are
Pascal. Variables are Camel Casing. I never use and never liked Hungarian
(prefixed with variable type).
Celko mentioned the problem with Camel Casing being harder to read.
I have seen various ways of casing in Sql Server.
I have also seen various opinions on whether to pluralize table names
(Employees table instead of Employee table). I tend to pluralize myself,
but have seen guidlines that suggest it is better to use the singular form.
I see Sql statements both as SELECT and select, but haven't really seen any
definitive agreement on this.
Much of this just seems to be personal preference - which would be a real
problem in C as case is important.
Just curious.
Thanks,
TomSee my rules in SQL PROGRAMMING STYLE. They are based on ISO-11179,
human factors and readability research. It is a mater of a few decades
of research, not personal taste.
1) For table names, in order of preference
industry standard name - best
collective nouns (Personnel) -better
plural nouns (Employees) - bad
singluar (employee) - not usable, less the set has just one member
Capitalize it, as you would in English, German, etc. for proper names.
2) uppercase reserved words because they are read as a Bouma (single
unit of eye scan). The compiler will take care of typos.
3) lowercase scalars because they are longer and this easier to read
and less likely to be misspelt. Never use "camelCase" because the eye
jumps to the capital letter, then back to the start of the word.
The reason C is a "lowercase" language actually has to do with
teletypes! It is a physical effort to hold down the shift key and when
you are a two-finger typists, you don't want to write anything very
long.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1125333962.715849.259610@.g47g2000cwa.googlegroups.com...
> See my rules in SQL PROGRAMMING STYLE. They are based on ISO-11179,
> human factors and readability research. It is a mater of a few decades
> of research, not personal taste.
> 1) For table names, in order of preference
> industry standard name - best
> collective nouns (Personnel) -better
> plural nouns (Employees) - bad
> singluar (employee) - not usable, less the set has just one member
> Capitalize it, as you would in English, German, etc. for proper names.
I assume you are speaking Pascal casing here.

>
> 2) uppercase reserved words because they are read as a Bouma (single
> unit of eye scan). The compiler will take care of typos.
> 3) lowercase scalars because they are longer and this easier to read
> and less likely to be misspelt. Never use "camelCase" because the eye
> jumps to the capital letter, then back to the start of the word.
What about Pascal style for variables (fields), scalars?
CamelCase is used quite a bit and is the what was used for Hungarian
(although it has been pretty much being phased out of MS notation).

> The reason C is a "lowercase" language actually has to do with
> teletypes! It is a physical effort to hold down the shift key and when
> you are a two-finger typists, you don't want to write anything very
> long.
>
True.
Thanks,
Tom