Thursday, March 8, 2012

"Select *" in Replication Tables

Hi,
I used "select * from replicate_table_name" in my stored procedures and my
application.Now i want to replicate that tables.
Will it affect my application and Replication.
Please Advice me.
Regds
Soura
It has zero effect on replication, since replication is not going to look at
your code.
It will have no effect on your application as long as you are using
transactional replication. If you are using any other method of
replication, an additional column will be added to your tables which can
then be picked up by that select * and returned to your application which
might not be expecting it, thereby causing it to blow up.
It is VERY STRONGLY recommended that you do NOT use select * in any code
that you ever write in an application. It is bad programming practice and
can lead to some very nasty errors after your application is deployed.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:56C4D4EC-2E19-4648-81E0-621E8EF821E7@.microsoft.com...
> Hi,
> I used "select * from replicate_table_name" in my stored procedures and my
> application.Now i want to replicate that tables.
> Will it affect my application and Replication.
>
> Please Advice me.
> Regds
> Soura
>
>
>
|||It depends . The table schema is modified if you have updatable
subscribers or merge replication - an extra column is added. However, in the
case of merge if you already have a guid column with the rowguid attribute
there won't be another column added.
So, in those cases which cause a schema modification, will the extra column
cause an issue? This depends on your code. In TSQL, if you use:
Insert into x
select * from Y
and Y has an extra column added, it'll fail.
If your code simply does
select * from Y
then it depends on how the recordset/dataset is used in the client app.
There may be some code that iterates through the columns collection which'll
fail if it encounters a new column. Actually this is unlikely as most
columns are referenced directly by name, and the new column will simply
exist but never be referred to.
Anyway - hopefully this gives you some ideas of what to look for to see if
these things will be an issue in your code...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

No comments:

Post a Comment