"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)

"Phantom" Stored Procedures?

I have run into a problem where I cannot add a new stored
procedure to the master database because it supposedly
already exists, but I cannot see it in Enterprise
Manager, I cannot see it if I query the sysobjects table
directly for it, and I cannot drop it (I get a message
that nothing by that name exists in the catalog). I'd
like to know what is going on here with this catch-22
thing. It seems to be related to the fact that I moved
the master database over to this server from another one
using BACKUP/RESTORE. The originating server and the
destination server were named differently, and the master
database was moved from the C drive on the original
server to the D drive on the destination server when it
was restored.
I would be most grateful if someone could help me avoid a
rebuild of the master database, or worse, a re-install of
SQL Server altogether to solve this problem.
Mark> I have run into a problem where I cannot add a new stored
> procedure to the master database because it supposedly
> already exists, but I cannot see it in Enterprise
> Manager, I cannot see it if I query the sysobjects table
> directly for it, and I cannot drop it (I get a message
> that nothing by that name exists in the catalog).
Could you tell us the name of the stored procedure you are trying to add,
and why you are trying to add it to the master database?|||1) Are you using the sp_addextendedproc to add it?
2) Have you tried removing it first using sp_dropextendedproc?
3) and you're sure you are adding to master?
"Mark Schmidt" <Me@.spamthis.com> wrote in message
news:006d01c35127$e3ffc370$a101280a@.phx.gbl...
> >--Original Message--
> >> I have run into a problem where I cannot add a new
> stored
> >> procedure to the master database because it supposedly
> >> already exists, but I cannot see it in Enterprise
> >> Manager, I cannot see it if I query the sysobjects
> table
> >> directly for it, and I cannot drop it (I get a message
> >> that nothing by that name exists in the catalog).
> >
> >Could you tell us the name of the stored procedure you
> are trying to add,
> >and why you are trying to add it to the master database?
> >
> >
> >.
> >
> Well, this time it's actually an extended stored
> procedure that a consulting company gave me to effect
> some functionality in one of our applications that uses
> SQL Server. However, I had the same problem upgrading
> another of our servers to SP3a. That time it was system
> stored procedures that couldn't be dropped and re-added
> via the upgrade script because they were already there.
> Both scenarios involve the movement of the master
> database from an older server (which went out the door
> due to its lease expiration) to a newer one. I have
> noticed that seems to be the common denominator.

"Hidden" Replication Stored Procedures?

In looking thru the code for the replication related stored procedure
sp_addarticle, I noticed that there were 2 stored procedures called from
sp_addarticle that I can't seem to find anywhere on my SQL Server:
sys.sp_MSrepl_getpublisherinfo & sys.sp_MSrepl_addarticle. These are stored
procedures that appear to be undocumented and I have read comments on
Microsoft sites stating things like: “Undocumented stored procedures are only
for the use of internal replication components and should not be used to
administer replication.”. I just want to know more about these stored procs
(and others like these) as far as where they live, what they do, how
sp_addarticle calls these Stored Procs without the SP blowing up....as I
have tried to call sys.sp_MSrepl_getpublisherinfo in a 10 line script I made
and all I got back was that the sys.sp_MSrepl_getpublisherinfo couldn't be
found no matter what DB I tried to execute against. I don't see anything
helpful about these SPs when I Google (and I am a Google master) or anywhere
else. If anyone has anything relevant & helpful, I would appreciate the info.
Thanks.
Chris Jones
Application Development Consultant
they are in mssqlsystemresource.mdf. Open it in a text editor to read what
this proc does.
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
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:420DD987-9E55-46AB-8445-82FD01740996@.microsoft.com...
> In looking thru the code for the replication related stored procedure
> sp_addarticle, I noticed that there were 2 stored procedures called from
> sp_addarticle that I can't seem to find anywhere on my SQL Server:
> sys.sp_MSrepl_getpublisherinfo & sys.sp_MSrepl_addarticle. These are
> stored
> procedures that appear to be undocumented and I have read comments on
> Microsoft sites stating things like: "Undocumented stored procedures are
> only
> for the use of internal replication components and should not be used to
> administer replication.". I just want to know more about these stored
> procs
> (and others like these) as far as where they live, what they do, how
> sp_addarticle calls these Stored Procs without the SP blowing up....as I
> have tried to call sys.sp_MSrepl_getpublisherinfo in a 10 line script I
> made
> and all I got back was that the sys.sp_MSrepl_getpublisherinfo couldn't be
> found no matter what DB I tried to execute against. I don't see anything
> helpful about these SPs when I Google (and I am a Google master) or
> anywhere
> else. If anyone has anything relevant & helpful, I would appreciate the
> info.
> --
> Thanks.
> Chris Jones
> Application Development Consultant
|||Great, that was exactly what I was looking for and when opening the
aforementioned MDF file in Notepad and doing a quick search, I found the SPs
I had mentioned in my earlier post. Thanks for the good info!
I have one follow up question based on your answer:
Is there a way to have the Resource Database appear in the list of Databases
in SQL Server Management Studio for easy browsing of it's contents, or is the
only browse option to open the MDF file with a text editor such as notepad?
Thanks.
Chris Jones
Application Development Consultant
"Hilary Cotter" wrote:

> they are in mssqlsystemresource.mdf. Open it in a text editor to read what
> this proc does.
> --
> 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
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:420DD987-9E55-46AB-8445-82FD01740996@.microsoft.com...
>
>
|||Just make a copy of the database files, attach it with a different name
and... 'voila'!
:-)
Chris wrote:[vbcol=seagreen]
> Great, that was exactly what I was looking for and when opening the
> aforementioned MDF file in Notepad and doing a quick search, I found the SPs
> I had mentioned in my earlier post. Thanks for the good info!
> I have one follow up question based on your answer:
> Is there a way to have the Resource Database appear in the list of Databases
> in SQL Server Management Studio for easy browsing of it's contents, or is the
> only browse option to open the MDF file with a text editor such as notepad?
> --
> Thanks.
> Chris Jones
> Application Development Consultant
>
> "Hilary Cotter" wrote:
|||Yip...you are absolutely correct...I just discovered that and was coming
back here to post the instructions. Here's what I found...
First, giving credit where credit is due, I found the following info I am
about to post at the following location:
http://www.aspfaq.com/sql2005/show.asp?id=28
Since the engine has hooks that hide the mssqlsystemresource database from
users, you don't have direct access to it through the GUI.
However, there is a way around this:
1.)Determine where the system databse files live, and keep this path handy:
USE master
GO
SELECT REPLACE(filename, 'master.mdf', '')
FROM sys.sysfiles
WHERE fileid = 1
2.)Stop the SQL Server service;
3.)Copy the files mssqlsystemresource.*df -> resource_copy.*df.
NOTE: do *not* rename or remove the mssqlsystemresource files!
4.)Start the SQL Server service;
5.)Run the following code in a new query window:
EXEC sp_attach_db
'Resource_Copy',
'<path from above>resource_copy.mdf',
'<path from above>resource_copy.ldf'
6.)Now, the system will no longer identify this database as a "special"
database
7.)You can easily scan through the names of system objects that might
otherwise be unknown (and browse any code associated with these objects) from
a GUI (like SQL Server Management Studio).
Thanks.
Chris Jones
Application Development Consultant
"zerg2k@.yahoo.com" wrote:

> Just make a copy of the database files, attach it with a different name
> and... 'voila'!
> :-)
> Chris wrote:
>
|||i just read ur post and i liked it beacuse i didn't even knew that SQL Server
uses such databases other then the known ones. but i didn't find the
mssqlsystemresource.mdf file can i please specify the address.
"Hilary Cotter" wrote:

> they are in mssqlsystemresource.mdf. Open it in a text editor to read what
> this proc does.
> --
> 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
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:420DD987-9E55-46AB-8445-82FD01740996@.microsoft.com...
>
>