"The process could not connect to Distributor"

Hi, i have the following problem:
i have a publisher and a subscriber in the same sql server 2005 machine. All
the process (the Log Reader Agent and the Snapshot Agent) works fine. Even
the snapshot files are being created in the UNC path (\\Desa5el1\Paso), but
when attempt to run synchronization, the Replication Monitor show me an error
in the Subscription. When i see the details of the error, i see the following:
Error messages:
The process could not read file
'\\Desa5el1\Paso\unc\DESA5EL1$CASCABEL_MC_AREASQL_ PUB_MC_AREASQL_PRUEBA\20070719170351\prueba_2.pre'
due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
Get help: http://help/MSSQL_REPL20024
The account under which the agent runs is db_owner (is an sql account, not
an Windows account) and member of the Publication Access List. I think maybe
the problem it is in this:
"Have read permissions on the snapshot share."
Could someone explain me this point? i need to configure some thing extra?
some permissions to the unc path?
I really appreciate any help.
Thanks in advance
Locate your snapshot share. Do that by issue the following on your
distributor
sp_helpdistributor
The name of your snapshot share should be in the directory column.
Also on your publisher in your publication database do this
sp_helppublication 'pubs' -- where pubs is your publication name
Note the value for alt_snapshot_folder column. If this value is null your
snapshot share is the value for the directory column returned by
sp_helpdistributor. If it is not null the snapshot share is in the
alt_snapshot_folder column.
Using file explorer navigate to this folder and right click on it, select
sharing and security and then make sure that the account which the sql
server agent account runs under has read and list files and folders rights
on this folder. The share permissions are by default always read.
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
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:9E69C2AD-9AA6-4B12-8B1A-B118123E9F3D@.microsoft.com...
> Hi, i have the following problem:
> i have a publisher and a subscriber in the same sql server 2005 machine.
> All
> the process (the Log Reader Agent and the Snapshot Agent) works fine. Even
> the snapshot files are being created in the UNC path (\\Desa5el1\Paso),
> but
> when attempt to run synchronization, the Replication Monitor show me an
> error
> in the Subscription. When i see the details of the error, i see the
> following:
> Error messages:
> The process could not read file
> '\\Desa5el1\Paso\unc\DESA5EL1$CASCABEL_MC_AREASQL_ PUB_MC_AREASQL_PRUEBA\20070719170351\prueba_2.pre'
> due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
> Get help: http://help/MSSQL_REPL20024
> The account under which the agent runs is db_owner (is an sql account, not
> an Windows account) and member of the Publication Access List. I think
> maybe
> the problem it is in this:
> "Have read permissions on the snapshot share."
> Could someone explain me this point? i need to configure some thing extra?
> some permissions to the unc path?
> I really appreciate any help.
> --
> Thanks in advance
>
|||Thanks for your help, Hilary.
I executed both sp_helpdistributor and sp_helppublication, and the snapshot
share that appears is correct (\\Desa5el1\Paso).
How can i set sharing and security rights for the account under which the
agent runs? The account exists only in sql server. I need to "register" this
account in somewhere out of sql server? How can i do that?
Thanks again.
"Hilary Cotter" wrote:

> Locate your snapshot share. Do that by issue the following on your
> distributor
> sp_helpdistributor
> The name of your snapshot share should be in the directory column.
> Also on your publisher in your publication database do this
> sp_helppublication 'pubs' -- where pubs is your publication name
> Note the value for alt_snapshot_folder column. If this value is null your
> snapshot share is the value for the directory column returned by
> sp_helpdistributor. If it is not null the snapshot share is in the
> alt_snapshot_folder column.
> Using file explorer navigate to this folder and right click on it, select
> sharing and security and then make sure that the account which the sql
> server agent account runs under has read and list files and folders rights
> on this folder. The share permissions are by default always read.
>
> --
> 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
> "byteman" <byteman@.discussions.microsoft.com> wrote in message
> news:9E69C2AD-9AA6-4B12-8B1A-B118123E9F3D@.microsoft.com...
>
>

"Not enough server storage is available to process this command"

Hi,
We have several customers using replication on similar databases - all with the same schema, published using essentially the same script. One of them has been getting the "Not enough server storage" message when they try to access any network shares on th
e server with the published database on it. This starts happening between 24-36 hours after they first run the snapshot agent.
The application log contains an entry saying "The process could not create file \\[SERVER]\REPLDATA\unc",
and the system log has one saying "The server was unable to allocate from the system paged pool because the pool was empty".
When they turn off the replication everything is fine.
I suggested increasing the IRPStackSize value in the registry, as this was mentioned in the closest article I could find on MSDN/KB (something about Norton AntiVirus), but this didn't make any difference.
This is SQL Server 2000 running on Windows 2000 Server, all with up-to-date service packs.
Any ideas or suggestions would be appreciated.
Mark
from your earlier post i understand that the snapshot creation part gives
out this message.
If the error occurs while the snapshot is being generated, check the
following"
1. Check if the sql server service account has appropriate NTFS and share
level permissions to write to the repldata directory.
2. Check if there is enough storage space on the disk for creating files
in repldata directory.
3. Check if you can run xp_cmdshell "dir
\\distributorname\c$\mssql\repldata"
and dir \\distributor\c$\mssql\repldata from command prompt.
If the error occurs after the snapshot has been completely generated, Refer
-
* KB : 285089 IRPStackSize Parameter in Windows 2000
http://support.microsoft.com/?id=285089
Pls do let me know of the SQL server version (build number). and the event
id's of the event log messages.
Does this message stop replication? are the subscribers and publisher in
sync? - Just to confirm if this message is obstructing replication from
being in sync.

"For each row" Trigger

Hi All,
The trigger in the following code is working on only last record
(sorry row) inserted . I wish to process "EACH ROW" in trigger as in
Oracle but without using cursor.
create table employee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
create table TreeEmployee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
GO
create trigger trgInsertOn on TreeEmployee for insert
as
BEGIN
DECLARE @.EMPID INT
DECLARE @.SUPERVISOR INT
SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
IF @.EMPID IS NOT NULL
BEGIN
INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
@.EMPID
END
END
GO
insert into employee
select 1,'Carl','Hogans',12
union
select 12,'Fred','Smith',NULL
union
select 16,'Sue','Bankers',1
union
select 26,'Frank','Green',12
union
select 55,'Karen','Feeders',NULL
union
select 56,'James','Black',12
union
select 57,'Kirk','Simmons',56
union
select 58,'Cliff','Page', 56
union
select 59,'Jimmy','Plant',56
union
select 60,'Jack','Cale', 59
union
select 61,'Robert','Santana',NULL
union
select 62,'Jack','Russell',1
INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
DROP TRIGGER TRGINSERTON
Drop table TreeEmployee
DROP table employee
With warm regards
Jatinder SinghINSERT INTO TreeEmployee
SELECT * FROM employee WHERE SUPERVISOR IN
(
Select EmpId from Inserted where Empid IS NOT NULL
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"jsfromynr" wrote:

> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||What exactly are you trying to do? Maybe you should first read a book or two
an trees and hierarchies.
When trying to build trees with triggers you need to consider the fact that
SQL prevents execution of recursive triggers, and consider the fact that the
maximum nesting level for recursions in SQL 2000 is 32.
ML|||Hi ML,
Yes , you are right .
I had read the articles written by Joe Celko .
'IF @.EMPID IS NOT NULL ' will stop the insert if empid is null
and we can add if TRIGGER_NESTLEVEL(object_id('YourTrigger
Name')) =32
return . Can't we? I am looking for an alternative approach .
With warm regards
Jatinder Singh|||First it's imperative that you design a good tree model on paper. One of the
most important requirements of any data tree IMHO is to prevent circular
referencing.
In your case there are two entities you need to design appropriate storage
for:
1) employees - selecting a unique key for each employee (a primary key,
maybe) is imperative; and
2) employee_hierarchy - not only do employees supervise other employees and
answer to another employee, relationships may also be contextual (roles,
projects) - in this case hierarchy instances must be supported.
Think about that. Entity #2 is a data tree which references employees, and
is also a self-referenced entity.
Look at this example in my blog:
http://milambda.blogspot.com/2005/0...or-monkeys.html
You can use the function to create your own 'hiearchy-discovery' methods.
ML|||Hi ML,
Really Your blog is excellent!! What I can say about that ? I
read it earlier .
With warm regards
Jatinder Singh|||:)
I'm glad you find it useful.
ML|||I might be missing something here, but here it goes...
When you write a trigger it only fires once per 'triggering' event. So in
your trigger below, only one insert is being run, only one trigger will run.
Where are all my records? Triggers expost an 'inserted' table. Inside this
special table are all of the records that were inserted. There is also a
table called deleted, but for updates triggers use deleted and inserted
(this is a seperate topic).
So onto the meat and potatos, you need to treat the inserted table as a
table! It has multiple rows. So you have two options as I see it.
1) Use a cursor to iterate over each row in the instered table (yuk)
2) Rewrite your trigger to support multiple records.
Here's my stab at #2:
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM inserted
WHERE supervisor IS NOT NULL
END
GO
Now, having done that I have a few remarks... You shouldn't be storing
anything in the supervisor table other than the EmpID and SupID. They
should both be FK's back to the employee table. Also, the trigger needs to
be on the insert of the employee table NOT the TreeEmployee table.
I'm really not sure what your ultimate goal is here. Your TreeEmployee
simply replicates your employee table except where EmpId is null...
That should get you going!
HTH,
Ben
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||Ok I think I see my mistake (maybe...) Here is my corrected trigger... If
this works, I'd tweak it to handle updates and deletes as well...
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
-- start transaction to encapsulate the delete / insert
BEGIN TRANSACTION
-- to prevent dups, clear out inserted supervisors records from the tree
-- this also catches stale records
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
-- add anyone in the employee table to the TreeEmployee table
-- that has a newly inserted EmpId as a supervisor on them
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM employee
WHERE supervisor IN
(
SELECT EmpId
FROM inserted
)
COMMIT TRANSACTION
END
GO
"Ben" <ben@.online.nospam> wrote in message
news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>I might be missing something here, but here it goes...
> When you write a trigger it only fires once per 'triggering' event. So in
> your trigger below, only one insert is being run, only one trigger will
> run.
> Where are all my records? Triggers expost an 'inserted' table. Inside
> this special table are all of the records that were inserted. There is
> also a table called deleted, but for updates triggers use deleted and
> inserted (this is a seperate topic).
> So onto the meat and potatos, you need to treat the inserted table as a
> table! It has multiple rows. So you have two options as I see it.
> 1) Use a cursor to iterate over each row in the instered table (yuk)
> 2) Rewrite your trigger to support multiple records.
> Here's my stab at #2:
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM inserted
> WHERE supervisor IS NOT NULL
> END
> GO
> Now, having done that I have a few remarks... You shouldn't be storing
> anything in the supervisor table other than the EmpID and SupID. They
> should both be FK's back to the employee table. Also, the trigger needs
> to be on the insert of the employee table NOT the TreeEmployee table.
> I'm really not sure what your ultimate goal is here. Your TreeEmployee
> simply replicates your employee table except where EmpId is null...
> That should get you going!
> HTH,
> Ben
>
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
>|||:( where does it end...
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
Needs to be
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT EmpId
FROM inserted
)
"Ben" <ben@.online.nospam> wrote in message
news:9cfb4$42f12d04$d8445835$11906@.FUSE.NET...
> Ok I think I see my mistake (maybe...) Here is my corrected trigger...
> If this works, I'd tweak it to handle updates and deletes as well...
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> -- start transaction to encapsulate the delete / insert
> BEGIN TRANSACTION
> -- to prevent dups, clear out inserted supervisors records from the tree
> -- this also catches stale records
> DELETE FROM TreeEmployee
> WHERE Supervisor IN
> (
> SELECT Supervisor
> FROM inserted
> )
> -- add anyone in the employee table to the TreeEmployee table
> -- that has a newly inserted EmpId as a supervisor on them
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM employee
> WHERE supervisor IN
> (
> SELECT EmpId
> FROM inserted
> )
> COMMIT TRANSACTION
> END
> GO
>
> "Ben" <ben@.online.nospam> wrote in message
> news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>

"Design Storage and Process the Cube" issue

To anyone who can help,
I am reviewing the processes in the Analysis Manager, and I am going
through the sample exercises offered by SQL Server's Analysis Manager Help.
So far, every step has been successful, but now I am running into a
problem. If you have the Analysis Manager installed, you should be able to
follow this directory to see the instructions I am following.
C:\Program Files\Microsoft Analysis
Services\Help80\Tutorial\design_stor_pro
c.htm
When I attempt to process the design storage to set the aggregations, it
is failing. I get the following error for why the process has failed:
[Microsoft][ODBC Driver Manager] Data source name not found and no d
efault
driver specified;IM002; Time:8/26/2004 5:25:00 PM
If you understand why this is happening, I would appreciate any tips. If I
go to redo the process it does show that I already have aggregates for the
cube. Does that mean it actually worked, or am I correct in my assumption
that the error is pointing to a problem that I need to resolve. Thanks for
any information!
CourtneyHi
Did u manage 2 fix the problem?
Udi
"Courtney Fay" wrote:

> To anyone who can help,
> I am reviewing the processes in the Analysis Manager, and I am going
> through the sample exercises offered by SQL Server's Analysis Manager Help
.
> So far, every step has been successful, but now I am running into a
> problem. If you have the Analysis Manager installed, you should be able t
o
> follow this directory to see the instructions I am following.
> C:\Program Files\Microsoft Analysis
> Services\Help80\Tutorial\design_stor_pro
c.htm
> When I attempt to process the design storage to set the aggregations,
it
> is failing. I get the following error for why the process has failed:
> [Microsoft][ODBC Driver Manager] Data source name not found and no
default
> driver specified;IM002; Time:8/26/2004 5:25:00 PM
> If you understand why this is happening, I would appreciate any tips. If
I
> go to redo the process it does show that I already have aggregates for the
> cube. Does that mean it actually worked, or am I correct in my assumption
> that the error is pointing to a problem that I need to resolve. Thanks fo
r
> any information!
> Courtney
>
>

"Design Storage and Process the Cube" issue

To anyone who can help,
I am reviewing the processes in the Analysis Manager, and I am going
through the sample exercises offered by SQL Server's Analysis Manager Help.
So far, every step has been successful, but now I am running into a
problem. If you have the Analysis Manager installed, you should be able to
follow this directory to see the instructions I am following.
C:\Program Files\Microsoft Analysis
Services\Help80\Tutorial\design_stor_proc.htm
When I attempt to process the design storage to set the aggregations, it
is failing. I get the following error for why the process has failed:
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified;IM002; Time:8/26/2004 5:25:00 PM
If you understand why this is happening, I would appreciate any tips. If I
go to redo the process it does show that I already have aggregates for the
cube. Does that mean it actually worked, or am I correct in my assumption
that the error is pointing to a problem that I need to resolve. Thanks for
any information!
Courtney
Hi
Did u manage 2 fix the problem?
Udi
"Courtney Fay" wrote:

> To anyone who can help,
> I am reviewing the processes in the Analysis Manager, and I am going
> through the sample exercises offered by SQL Server's Analysis Manager Help.
> So far, every step has been successful, but now I am running into a
> problem. If you have the Analysis Manager installed, you should be able to
> follow this directory to see the instructions I am following.
> C:\Program Files\Microsoft Analysis
> Services\Help80\Tutorial\design_stor_proc.htm
> When I attempt to process the design storage to set the aggregations, it
> is failing. I get the following error for why the process has failed:
> [Microsoft][ODBC Driver Manager] Data source name not found and no default
> driver specified;IM002; Time:8/26/2004 5:25:00 PM
> If you understand why this is happening, I would appreciate any tips. If I
> go to redo the process it does show that I already have aggregates for the
> cube. Does that mean it actually worked, or am I correct in my assumption
> that the error is pointing to a problem that I need to resolve. Thanks for
> any information!
> Courtney
>
>

"Cannot generate SSPI context" error message in SQL 2005 Beta 2

executing dimension process, the system return an error :
- Cannot generate SSPI context"
I'm not understand where is the problem....all config parameters are
correct...
What could be the reason of this?
my system configuration
Win XP SP2 Pro Ed.
SQL 2005 Beta 2
SQL Server 2005 CTP/Beta questions to
http://communities.microsoft.com/new...r2005&slcid=us
please.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmarchi" <cmarchi@.discussions.microsoft.com> wrote in message
news:7FF32AE8-3632-40C9-BA97-7D2116606D42@.microsoft.com...
> executing dimension process, the system return an error :
> - Cannot generate SSPI context"
> I'm not understand where is the problem....all config parameters are
> correct...
> What could be the reason of this?
> my system configuration
> Win XP SP2 Pro Ed.
> SQL 2005 Beta 2

"Cannot generate SSPI context" error message in SQL 2005 Beta 2

executing dimension process, the system return an error :
- Cannot generate SSPI context"
I'm not understand where is the problem....all config parameters are
correct...
What could be the reason of this?
my system configuration
Win XP SP2 Pro Ed.
SQL 2005 Beta 2SQL Server 2005 CTP/Beta questions to
http://communities.microsoft.com/ne...lcid=us

please.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"cmarchi" <cmarchi@.discussions.microsoft.com> wrote in message
news:7FF32AE8-3632-40C9-BA97-7D2116606D42@.microsoft.com...
> executing dimension process, the system return an error :
> - Cannot generate SSPI context"
> I'm not understand where is the problem....all config parameters are
> correct...
> What could be the reason of this?
> my system configuration
> Win XP SP2 Pro Ed.
> SQL 2005 Beta 2