Do I need the "dbo" at the end of my stored procedures?

I have some stored procedures with (dbo) after them and some that don't. Do I need this? how do I get rid of it? Do I need to refernence them in my code?

I intend to send my Access 2000 ADP front end to a customer and the back end SQL to them is this naming convention going to cause me issues?

Mitchnormally when one see "dbo" it is at the beginning of a stored proceure name i.e. master.dbo.sp_who.

If you have a database called "db1" and a stored procedure created by dbo called "sp1", you could have your app reference the sp as db1.dbo.sp1 from any db and it should work. From within db1 you could call dbo.sp1 annd that would work too.

If you referrence your sp as "sp1" and user "usr1" has created a stored procedure called "sp1" then when the user, "usr1", executes your sp "sp1" he/she/it will execute db1.usr1.sp1 not db1.dbo.sp1.

IMHO, if you want your users to execute the dbo version of a stored procedures then add "dbo." to the beggining of your sp name.

distribution database having problems??

Something is wrong with my distribtion database. When the distribution clean
up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
I get the following error message:
Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
(1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
SO then I run
DBCC CHECKDB
on the distribution database. The MSrepl_commands is the table with the
problem with repeated messages such as:
Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
parent nodes (1:93624), slot 103 and (1:89299), slot 1.
I run this:
dbcc checktable ('MSrepl_commands')
and get the same type of error message.
I have no idea how to fix this. Any advise?? (If I try and use the repair
options of the dbcc commands than it says I have to have the DB in single
user mode. Not sure if you are even suppose to do that with a distribution
db?)
Thanks so much for any and all help,
Kristy
Believe it or not these problems crop up from time to time on replicated
databases. Stop the sql server agent on the publisher, yank the network
cable (if possible), and put this database in single user mode and do the
repair.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Something is wrong with my distribtion database. When the distribution
> clean
> up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
> I get the following error message:
> Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
> ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
> (1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
> SO then I run
> DBCC CHECKDB
> on the distribution database. The MSrepl_commands is the table with the
> problem with repeated messages such as:
> Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
> parent nodes (1:93624), slot 103 and (1:89299), slot 1.
> I run this:
> dbcc checktable ('MSrepl_commands')
> and get the same type of error message.
> I have no idea how to fix this. Any advise?? (If I try and use the
> repair
> options of the dbcc commands than it says I have to have the DB in single
> user mode. Not sure if you are even suppose to do that with a distribution
> db?)
> Thanks so much for any and all help,
> Kristy
>
>
|||Thanks Hilary. I will check to see if we can yank the network cable. Not
sure if we can though because it is on a remote server hosted by another
company in another state.
If not, is there something else I can do? (Besides redo replication and drop
the distribution DB.)
As always, thanks!
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eODseSmPGHA.5516@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Believe it or not these problems crop up from time to time on replicated
> databases. Stop the sql server agent on the publisher, yank the network
> cable (if possible), and put this database in single user mode and do the
> repair.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
index[vbcol=seagreen]
failed.[vbcol=seagreen]
two[vbcol=seagreen]
single[vbcol=seagreen]
distribution
>

Distribution Cleanup Job Fails

The distribution cleanup job that runs for our transactional replication on
sql server 2005 fails.
The job is:
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
The Error is:
Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory
'\\sprs76\rysrepldata\unc\SPRS76_RYS_RYS_OPERATION ALTOREPLICAHIST\20060713140760\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory.
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent
RYSSprs76_distribution@.rowcount_only parameter must be the value 0,1, or 2.
0=7.0 compatible checksu scheduled for retry. Could not clean up the
distribution transaction tables.
If I remove this directory manually, would it cause the replication to
fail? I have tried this on a Test server and it seems that the replication
fails.
I tried to run this job in the Query window and it return this same error.
Our data file is growing and I cannot shrink it because of this issue.
What can i do to resolve this?
Thanks,
George Gopie
There's an MMC snapin to look at shared folders and their connections and
open files. This snapin could be used to drop the connections where needed.
If you have the folder open yourself locally, this snapin won't pick it up,
so you'd have to use the task manager to see what is likely to be accessing
it.
Another possibility is to drop the folder yourself then recreate it for the
distribution agent to later delete.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No it would not cause replication to fail, only remove this portion
20060713140760
Make sure that xp_cmdshell is enabled and the SQL Serer agent account has
rights to list files and folders and delete files on the root of the
repldata directory and it subdirectories.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"georgeg" <ggg@.hotamil.com> wrote in message
news:DFE68859-01CF-4236-AC2A-F9CA596B9482@.microsoft.com...
> The distribution cleanup job that runs for our transactional replication
> on
> sql server 2005 fails.
> The job is:
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72
> The Error is:
> Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
> Could not remove directory
> '\\sprs76\rysrepldata\unc\SPRS76_RYS_RYS_OPERATION ALTOREPLICAHIST\20060713140760\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory.
> Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
> compatible checksum. 1=only check rowcou: agent
> RYSSprs76_distribution@.rowcount_only parameter must be the value 0,1, or
> 2.
> 0=7.0 compatible checksu scheduled for retry. Could not clean up the
> distribution transaction tables.
>
> If I remove this directory manually, would it cause the replication to
> fail? I have tried this on a Test server and it seems that the replication
> fails.
> I tried to run this job in the Query window and it return this same error.
> Our data file is growing and I cannot shrink it because of this issue.
> What can i do to resolve this?
> Thanks,
> --
> George Gopie
>

Distributed Transaction takes far too long

Hi all,

I would like to perform an
INSERT INTO LINKEDSVR.dbo.xyz.abc
SELECT ... FROM dbo.dfg

where LINKEDSVR is a linked server on another machine. Both servers are
running SQLServer 2000 and have the DTC running.

When I run this batch from QueryAnalyzer without explicitly using
transactions, it works well (takes about 5 sec) - however, when I
enclose it using
begin [distributed] tran/commit tran
the query runs forever.

I also tried to use the local server as linked server (loopback) but it
did not work either.

Any suggestions?

Thanks,
JoHi Jo

You don't really give much information to work on! I assume that running
forever means that it has not completed? Therefore you may want to profile
the query to see what is exactly happening. You may also want to try
OPENQUERY or OPENROWSET instead.

I would expect the loop back to fail as you can not use them in a
distributed transaction. From BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:

Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.John
"Jo Siffert" <jo.siffert@.gmx.net> wrote in message
news:cpm67p$t4d$1@.zeppelin.rz.uni-potsdam.de...
> Hi all,
> I would like to perform an
> INSERT INTO LINKEDSVR.dbo.xyz.abc
> SELECT ... FROM dbo.dfg
> where LINKEDSVR is a linked server on another machine. Both servers are
> running SQLServer 2000 and have the DTC running.
> When I run this batch from QueryAnalyzer without explicitly using
> transactions, it works well (takes about 5 sec) - however, when I
> enclose it using
> begin [distributed] tran/commit tran
> the query runs forever.
> I also tried to use the local server as linked server (loopback) but it
> did not work either.
> Any suggestions?
> Thanks,
> Jo|||Jo Siffert (jo.siffert@.gmx.net) writes:
> I would like to perform an
> INSERT INTO LINKEDSVR.dbo.xyz.abc
> SELECT ... FROM dbo.dfg
> where LINKEDSVR is a linked server on another machine. Both servers are
> running SQLServer 2000 and have the DTC running.
> When I run this batch from QueryAnalyzer without explicitly using
> transactions, it works well (takes about 5 sec) - however, when I
> enclose it using
> begin [distributed] tran/commit tran
> the query runs forever.

Have you checked for blocking? I have a recollection that distributed
transactions are by default seriliazable, which have more concurrency
problems.

Use sp_who (on both servers) and keep an eye on the Blk column. A non-
zero value in that column means that this spid is blocking the spid
on this row. And if this spid is your distributed transaction, you bave
the reason.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp