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: