Problems with sending Event Notifications to remote service

Hi!

Just set up my first Service Broker test. Wanted to send an Event Notification from one instance to another. Finally, the message arrives but profiler shows that the message is still being continously resent (and rejected as being duplicate). What am I doing wrong?

With best regards,

Artus

--

/* Local Event Source.sql */

USE master
GO

CREATE DATABASE NotificationDB
GO

ALTER DATABASE NotificationDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5554
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO

USE NotificationDB
GO

CREATE EVENT NOTIFICATION TestEN
ON DATABASE
FOR CREATE_TABLE
TO SERVICE 'ExpressService', 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6'
GO

CREATE ROUTE ExpressRoute
WITH SERVICE_NAME = 'ExpressService' ,
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'TCP://localhost:5555'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

USE master
GO

SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO

/* Remote Service.sql */

USE master
GO

CREATE DATABASE Test
GO

ALTER DATABASE Test SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5555
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO

USE Test
GO

CREATE QUEUE ExpressQueue
WITH STATUS = ON
GO

CREATE SERVICE ExpressService
ON QUEUE ExpressQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

GRANT SEND ON SERVICE::ExpressService
TO [public]
GO

CREATE ROUTE ExpressServiceRoute
WITH SERVICE_NAME = 'ExpressService',
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'LOCAL'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO

SELECT * FROM sys.endpoints
GO

On the remote instance you need a route back to the EN service in the [test] database:

CREATE ROUTE NotificationsServiceRoute

WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',

BROKER_INSTANCE = '<service_broker_guid of NotificationDB>',

ADDRESS = 'tcp://localhost:5554'

GO

W/o this route, acknowledgements for messages cannot reach back to the sender, so the sender keeps retrying the same message again and again.

And remove the [ExpressServiceRoute] route from the target [test] database, is not needed.

HTH,
~ Remus

|||

Thank you very much!

I knew I was missing routing information but I simply did not figure out that the destination of (ack)messages from the ExpressService could be. Wasn't clear to me that there is a service

'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService'

Guess I simply missed that in the Eventnotification sample in BOL?

With best regards,

Artus

Problems with remote SQL 2005 server and excel as an input source

Hello,

I am trying to write my first couple Integration Services packages using SQL 2005. My configuration is a workstation running windows xp professional, and a windows 2003 server that is running the SQL server.

Anytime I run a package that accesses the remote server from my workstation, the job fails with an error code. The workstation cannot seem to run a package to load data to the remote sql server. Why is this? Is there a service pack, or hotfix coming out soon to correct this problem?

Additionally, I also seem to be unable to update a database using excel as the data source from which information should be used. If I import my excel spreadsheet into an access table, I can update the sql database from Access using integration services. Why can't I use an excel spreadsheet as the source? Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?

Thanks!

Jim

I've been able to input an excel spreadsheet in the dataflow and then use an Oracle destination to update an oracle table - what errors are you getting?|||

Jim R wrote:

Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?

It would help if you told us what the problem was before anyone answers that question.

What errors are you getting?

|||What destination transform are you using?

If you are using the SQL Server Destination, then the package must run

on the destination server. The SQL Destination inserts data much

more efficiently than the OLE Destination.

If you are using the OLE DB Destination, then please post the error message you are receiving so that we help you debug.

Larry|||

I am using an SQL destination, but I don't understand why the package must run on the destination server for SQL server? That was never a requirement before with sql 2000's DTS services... Shouldn't I be able to develop, test, and even deploy packages to other servers?

Speaking of deploying. Once I have a package developed, how can I run it on the server machine? I don't intend to install the visual studio, etc. on the server....

Jim

|||

Jim,

the SQL Server desination is a special destination adapter for LOCAL SQL Servers only... If you want to connect to remote servers you have to use the OLE DB destination... This is by design...

|||

Why is the SQL server destination for LOCAL SQL servers only? Isn't there a significant performance increase in using the SQL server type versus the OLEDB one when accessing a SQL server?

I also read some articles that state that the SSIS service does not get installed in the workstation edition of SQL server 2005, unless you use the Developer Edition. Can someone also explain why that is?

Thanks!

|||

Hi,

to be honest: no idea. I only know that they did some "tricks" to make accessing local servers faster...

What do you mean with "workstation edition"? I'm not aware of this edition...

|||

The Service is indeed not part of Workgroup Edition, as noted in the matrix in "Features Supported by the Editions of SQL Server 2005." However unlike the SQL Server or Analysis Services services, for example, the Integration Services service is not crucial to building and running packages, but merely provides some extra services, like monitoring running packages.

-Doug

|||

Even i have taken excel spreadsheet as input in dataflow and use SQL server destination to update data. But iam not able to update data in database.

I have an excel source which has some columns with values(for ex column with values "yes" / "No"). Now i need to update particular column of a table in a sql server database depending on the column value in excel source.

There was no error in the package but i could not get the expected result could any one help me in this regard.

thanks in advance.

|||

You have not told us what unexpected results you obtained, or what errors you encountered.

You almost certainly need to add a Derived Column transformation to convert "yes" or "no" values to the appropriate Boolean values that a SQL database is probably expecting.

For relatively simple import and export scenarios, you'll usually save yourself some grief by using the Import and Export Wizard to create and save the initial package, then revise and enhance it as needed.

-Doug

|||

Jim R wrote:

Why is the SQL server destination for LOCAL SQL servers only?

Because of the way it works. It has a special mechanism for accessing the memory space of the SQL Server instance but of course in order to do that it needs to be on the same machine. It isn't a limitation that they have delierately put in - its just the way it is.

-Jamie

|||

The scenario goes on like this:

I have an excel source with 3 coulumns(sno,sname,status). The values for status column will be either "yes" or "no".

I have a student table in SQL Server datatase which contain some columns(stdsno,stdsname, stdstatus)

i need to update the stdstatus column of student table in sql server database if and only if the sno of excel source matches with stdsno of sqlserver table and also status column value of excel source is "no".

(for example i need to update the stdstatus column values in database only if the status column value of excel source is "no").

I have taken excel as input source and sqlserverdestination as destination. Which transformation should i use to achive the above said

output.

|||

sanj_vam wrote:

The scenario goes on like this:

I have an excel source with 3 coulumns(sno,sname,status). The values for status column will be either "yes" or "no".

I have a student table in SQL Server datatase which contain some columns(stdsno,stdsname, stdstatus)

i need to update the stdstatus column of student table in sql server database if and only if the sno of excel source matches with stdsno of sqlserver table and also status column value of excel source is "no".

(for example i need to update the stdstatus column values in database only if the status column value of excel source is "no").

I have taken excel as input source and sqlserverdestination as destination. Which transformation should i use to achive the above said

output.

If you need to compare source with destination then Lookup is a good option. Merge Join also has cpabilities in this area.

-Jamie

Problems with remote SQL 2005 server and excel as an input source

Hello,

I am trying to write my first couple Integration Services packages using SQL 2005. My configuration is a workstation running windows xp professional, and a windows 2003 server that is running the SQL server.

Anytime I run a package that accesses the remote server from my workstation, the job fails with an error code. The workstation cannot seem to run a package to load data to the remote sql server. Why is this? Is there a service pack, or hotfix coming out soon to correct this problem?

Additionally, I also seem to be unable to update a database using excel as the data source from which information should be used. If I import my excel spreadsheet into an access table, I can update the sql database from Access using integration services. Why can't I use an excel spreadsheet as the source? Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?

Thanks!

Jim

I've been able to input an excel spreadsheet in the dataflow and then use an Oracle destination to update an oracle table - what errors are you getting?|||

Jim R wrote:

Is there a a service pack or hotfix coming out soon for 2005 sql that will correct this problem?

It would help if you told us what the problem was before anyone answers that question.

What errors are you getting?

|||What destination transform are you using?

If you are using the SQL Server Destination, then the package must run

on the destination server. The SQL Destination inserts data much

more efficiently than the OLE Destination.

If you are using the OLE DB Destination, then please post the error message you are receiving so that we help you debug.

Larry|||

I am using an SQL destination, but I don't understand why the package must run on the destination server for SQL server? That was never a requirement before with sql 2000's DTS services... Shouldn't I be able to develop, test, and even deploy packages to other servers?

Speaking of deploying. Once I have a package developed, how can I run it on the server machine? I don't intend to install the visual studio, etc. on the server....

Jim

|||

Jim,

the SQL Server desination is a special destination adapter for LOCAL SQL Servers only... If you want to connect to remote servers you have to use the OLE DB destination... This is by design...

|||

Why is the SQL server destination for LOCAL SQL servers only? Isn't there a significant performance increase in using the SQL server type versus the OLEDB one when accessing a SQL server?

I also read some articles that state that the SSIS service does not get installed in the workstation edition of SQL server 2005, unless you use the Developer Edition. Can someone also explain why that is?

Thanks!

|||

Hi,

to be honest: no idea. I only know that they did some "tricks" to make accessing local servers faster...

What do you mean with "workstation edition"? I'm not aware of this edition...

|||

The Service is indeed not part of Workgroup Edition, as noted in the matrix in "Features Supported by the Editions of SQL Server 2005." However unlike the SQL Server or Analysis Services services, for example, the Integration Services service is not crucial to building and running packages, but merely provides some extra services, like monitoring running packages.

-Doug

|||

Even i have taken excel spreadsheet as input in dataflow and use SQL server destination to update data. But iam not able to update data in database.

I have an excel source which has some columns with values(for ex column with values "yes" / "No"). Now i need to update particular column of a table in a sql server database depending on the column value in excel source.

There was no error in the package but i could not get the expected result could any one help me in this regard.

thanks in advance.

|||

You have not told us what unexpected results you obtained, or what errors you encountered.

You almost certainly need to add a Derived Column transformation to convert "yes" or "no" values to the appropriate Boolean values that a SQL database is probably expecting.

For relatively simple import and export scenarios, you'll usually save yourself some grief by using the Import and Export Wizard to create and save the initial package, then revise and enhance it as needed.

-Doug

|||

Jim R wrote:

Why is the SQL server destination for LOCAL SQL servers only?

Because of the way it works. It has a special mechanism for accessing the memory space of the SQL Server instance but of course in order to do that it needs to be on the same machine. It isn't a limitation that they have delierately put in - its just the way it is.

-Jamie

|||

The scenario goes on like this:

I have an excel source with 3 coulumns(sno,sname,status). The values for status column will be either "yes" or "no".

I have a student table in SQL Server datatase which contain some columns(stdsno,stdsname, stdstatus)

i need to update the stdstatus column of student table in sql server database if and only if the sno of excel source matches with stdsno of sqlserver table and also status column value of excel source is "no".

(for example i need to update the stdstatus column values in database only if the status column value of excel source is "no").

I have taken excel as input source and sqlserverdestination as destination. Which transformation should i use to achive the above said

output.

|||

sanj_vam wrote:

The scenario goes on like this:

I have an excel source with 3 coulumns(sno,sname,status). The values for status column will be either "yes" or "no".

I have a student table in SQL Server datatase which contain some columns(stdsno,stdsname, stdstatus)

i need to update the stdstatus column of student table in sql server database if and only if the sno of excel source matches with stdsno of sqlserver table and also status column value of excel source is "no".

(for example i need to update the stdstatus column values in database only if the status column value of excel source is "no").

I have taken excel as input source and sqlserverdestination as destination. Which transformation should i use to achive the above said

output.

If you need to compare source with destination then Lookup is a good option. Merge Join also has cpabilities in this area.

-Jamie

Problems with remote connections to Express

Hi,

I'm having problems getting a remote connection to sql server express.

The application connects fine on the local PC (using SQL server authentication), however a networked laptop cannot connect to sql.

The PC/Laptop have been networked using the network wizard, and files on the PC are accesible from the laptop.

tcp/ip is enabled on express and the browser service is running. All firewalls have been switched off.

Any ideas?

pse post back the error no and description you gets. Also post back the connection string

Madhu

|||

Hi

Have a look at this post, it may be useful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1576999&SiteID=1

Tailor

|||Thanks both, the problem I have is that it's one of my users having the problem & I'm having trouble replicating it here. The port stuff sounds interesting & I'll have a play with that & report back.

Problems with remote connections to Express

Hi,

I'm having problems getting a remote connection to sql server express.

The application connects fine on the local PC (using SQL server authentication), however a networked laptop cannot connect to sql.

The PC/Laptop have been networked using the network wizard, and files on the PC are accesible from the laptop.

tcp/ip is enabled on express and the browser service is running. All firewalls have been switched off.

Any ideas?

pse post back the error no and description you gets. Also post back the connection string

Madhu

|||

Hi

Have a look at this post, it may be useful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1576999&SiteID=1

Tailor

|||Thanks both, the problem I have is that it's one of my users having the problem & I'm having trouble replicating it here. The port stuff sounds interesting & I'll have a play with that & report back.

Problems with remote conexion

Hi all,
I have installed SQL SERVER 2005 EXPRESS but i cant do a remote login
with SQL Server Management Studio Express. I have deactivated Windows
XP firewall in the SQL SERVER side and I have activated tcp/ip
connections. Which can be the problem?
Thanks all(dagato2@.terra.es) writes:
> I have installed SQL SERVER 2005 EXPRESS but i cant do a remote login
> with SQL Server Management Studio Express. I have deactivated Windows
> XP firewall in the SQL SERVER side and I have activated tcp/ip
> connections. Which can be the problem?

First of all, deactivating the Windows firewall sounds like a bad idea. You
may have to open the ports on which SQL Server listens, or list the
machine you are connecting from as an exception.

Next, I will have to ask you for clarification. On which machine have you
installed SQL Express? On which machine have you installed Mgmt Studio
Express? To which server instance do you try to connect?

Keep in mind that by default SQL Express installs as a named instance, so
if you install SQL Express on a machine named DAGATO2, you should connect
to DAGATO2\SQLEXPRESS.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog ha escrito:

> (dagato2@.terra.es) writes:
> > I have installed SQL SERVER 2005 EXPRESS but i cant do a remote login
> > with SQL Server Management Studio Express. I have deactivated Windows
> > XP firewall in the SQL SERVER side and I have activated tcp/ip
> > connections. Which can be the problem?
> First of all, deactivating the Windows firewall sounds like a bad idea. You
> may have to open the ports on which SQL Server listens, or list the
> machine you are connecting from as an exception.
> Next, I will have to ask you for clarification. On which machine have you
> installed SQL Express? On which machine have you installed Mgmt Studio
> Express? To which server instance do you try to connect?
> Keep in mind that by default SQL Express installs as a named instance, so
> if you install SQL Express on a machine named DAGATO2, you should connect
> to DAGATO2\SQLEXPRESS.

I found the problem, I didnt have enabled sql browser.
Thanks for the answer.|||(dagato2@.terra.es) writes:
> I found the problem, I didnt have enabled sql browser.

Yeah that one as well! Thanks for posting back! Maybe one day I will
be able to remember all things that can go wrong with connection!

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Problems with PULL on remote server

Hi Paul,
I am still having problems with the OS 53 error.
I done what you suggested by mapping a shared drive on the server to
'REPLDATA' and its still causing the same error.
Now I need to clarify a few things before I go any further.
1. The subscriber (SBS2000 Remote Server) has 'sa' as the login for the
Publisher and Distributor in the security tab. Is this right?
2. The distributor link password is the same password as 'sa'.
3. Under the publisher properties, the login name is 'sa'.
4. I created a windows login 'SQLUser' which has all admin rights which is
in the PAL and security,login areas of both servers all with identical
passords.
I'm confused to where you put the 'sa' user and the 'SQLUser' in the
numerous login boxs all over the replication process. This is probably the
reason i'm getting the errors below.
Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script
'\\broadserver\repldata\unc\BROADSERVER_partner_pa rtner\20070110215231\snapshot.pre' could not be propagated to the subscriber.
Category:AGENT
Source: CLIFTSERVER
Number: 0
Message: The process could not read file
'\\broadserver\repldata\unc\BROADSERVER_partner_pa rtner\20070110215231\snapshot.pre' due to OS error 53.
The schema script
'\\broadserver\repldata\unc\BROADSERVER_partner_pa rtner\20070110215231\snapshot.pre' could not be propagated to the subscriber. The step failed.
The job failed. The Job was invoked by Schedule 26 (Replication agent
schedule.). The last step to run was step 1 (Run agent.).
Please help as my boss is getting a bit upset with me coz i'm taking so long
to sort this out.
Many thanks
TIM
The use of sa as the login is for the database access. At the file level,
the access will be via the sql server agent's account on the subscriber. For
this, use a domain user account that has the correct rights on the
publisher. To make life easier, I often use the same account as the
publisher's sql server login.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
If I setup a PUSH from the distributor it works OK and there is NO
problems....
However, if I setup a PULL from the subsciber then it give the OS 53 error.
So what is causing the PULL to fail. Would it be that the login from the
agent (from the subsciber) is not trusted (but I knew this anyway) or is
there anything else I havent thought of?..
Many thanks
TIM
"Paul Ibison" wrote:

> The use of sa as the login is for the database access. At the file level,
> the access will be via the sql server agent's account on the subscriber. For
> this, use a domain user account that has the correct rights on the
> publisher. To make life easier, I often use the same account as the
> publisher's sql server login.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||This is not inconsistant with what I was suggesting. You'll need a
domain-user with rights on the share to run the SQL Server agent. On sql
server 2005 you can use a proxy for this instead. Alternatives on SQL Server
2000 are : FTP and nosync initializations.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OS Error 53 basically suggests that the PULL agent on the subscriber is
unable to get the network path \\broadserver\repldata.
Your PUSH subscriptions work successfully because the PUSH agent run on the
distributor and the snapshot files are also located on the distributor.
Where as in case of PULL your agent runs on the subscriber (which in this
case is the SBS2000 Remote Server) and tries to connect to the network path
\\broadserver\repldata to get to the snapshot files.
So to correct the problem, Make sure that the account under which the PULL
agent is getting kicked off, which will most likely be the SQL Agent's
service startup account, can connect to the network path
\\broadserver\repldata and access any or all the sub-folders under it.
HTH
Emaniel
""confused"" wrote:

> Hi Paul,
> I am still having problems with the OS 53 error.
> I done what you suggested by mapping a shared drive on the server to
> 'REPLDATA' and its still causing the same error.
> Now I need to clarify a few things before I go any further.
> 1. The subscriber (SBS2000 Remote Server) has 'sa' as the login for the
> Publisher and Distributor in the security tab. Is this right?
> 2. The distributor link password is the same password as 'sa'.
> 3. Under the publisher properties, the login name is 'sa'.
> 4. I created a windows login 'SQLUser' which has all admin rights which is
> in the PAL and security,login areas of both servers all with identical
> passords.
> I'm confused to where you put the 'sa' user and the 'SQLUser' in the
> numerous login boxs all over the replication process. This is probably the
> reason i'm getting the errors below.
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147201001
> Message: The schema script
> '\\broadserver\repldata\unc\BROADSERVER_partner_pa rtner\20070110215231\snapshot.pre' could not be propagated to the subscriber.
> Category:AGENT
> Source: CLIFTSERVER
> Number: 0
> Message: The process could not read file
> '\\broadserver\repldata\unc\BROADSERVER_partner_pa rtner\20070110215231\snapshot.pre' due to OS error 53.
> The schema script
> '\\broadserver\repldata\unc\BROADSERVER_partner_pa rtner\20070110215231\snapshot.pre' could not be propagated to the subscriber. The step failed.
> The job failed. The Job was invoked by Schedule 26 (Replication agent
> schedule.). The last step to run was step 1 (Run agent.).
> Please help as my boss is getting a bit upset with me coz i'm taking so long
> to sort this out.
> Many thanks
> TIM
|||You can also copy the snapshot to the subscriber so a lot of the
authentication issues go away. Then just tell the subscription to get
the snapshot from a local folder.
Paul Ibison wrote:
> This is not inconsistant with what I was suggesting. You'll need a
> domain-user with rights on the share to run the SQL Server agent. On sql
> server 2005 you can use a proxy for this instead. Alternatives on SQL Server
> 2000 are : FTP and nosync initializations.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Yes - thanks for pointing this out. This is indeed different to a "nosync
initialization" which is what I mentioned. In fact you have jogged my memory
as I used to move large snapshots around this way. If this route is chosen,
I'd create the snapshot, zip it up, transfer the file, unzip, restore then
use the "alternative snapshot location" setting.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
I want to try the nosync way next but I really need to know if there is
something wrong with the setup i have at the moment.
The problem I've found now is that the subscirber agent simply wont accept
any other login other then 'sa'. I have tried admin, administrator, etc etc.
The reason it fails (assumes) is that 'sa' is not a windows domain account
and therefore doesnt have the rights to the snapshot folder hence the error.
But for the life of me I cannot get the PULL subsciber to login using any
other user login......
If I use any other login name it just gives me 'login user name failed'.
Is it because the windows on my subsciber is CLIFT\[user name] and the
distributor is BROAD\[user name]. Will the distributor detect the login name
has a different domain name and therefore does not accept it.
I dont know as I'm clutching at straws here but i'm sure its something to do
with that. The rights to the snapshot should be ok as I have mapped a drive
to the 'REPLDATA' directory and set it as 'H:' drive. The drive has FULL
admin rights given to Administrator, SQLUser (but not sa for obvious reasons).
Like I said before it works fine if I do a PUSH from the distributor as the
agent logs in locally so its got to be a auth problem regarding the login of
the agent...?
This is really cooking my brain now and all I want to do is sort it.
Any ideas
TIM
"Paul Ibison" wrote:

> Yes - thanks for pointing this out. This is indeed different to a "nosync
> initialization" which is what I mentioned. In fact you have jogged my memory
> as I used to move large snapshots around this way. If this route is chosen,
> I'd create the snapshot, zip it up, transfer the file, unzip, restore then
> use the "alternative snapshot location" setting.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Please check out my previous responses. Access to the snapshot share is via
the sql server agent logon. Try setting that logon to be a domain user which
is an administrator on the publisher machine. This can be refined later on -
let's just get it working for now. This will only work if the domains are
trusted. If not, then have a look at doing a nosync initialization
(http://www.replicationanswers.com/NoSyncInitializations.asp) or FTP.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi,
I would recommend checking the owner of the SQL Agent replication job, as
the distributor_admin login may be owner and may not have access to share.
Phillip Cox
"Emaniel Chekelea" wrote:
[vbcol=seagreen]
> OS Error 53 basically suggests that the PULL agent on the subscriber is
> unable to get the network path \\broadserver\repldata.
> Your PUSH subscriptions work successfully because the PUSH agent run on the
> distributor and the snapshot files are also located on the distributor.
> Where as in case of PULL your agent runs on the subscriber (which in this
> case is the SBS2000 Remote Server) and tries to connect to the network path
> \\broadserver\repldata to get to the snapshot files.
> So to correct the problem, Make sure that the account under which the PULL
> agent is getting kicked off, which will most likely be the SQL Agent's
> service startup account, can connect to the network path
> \\broadserver\repldata and access any or all the sub-folders under it.
> HTH
> Emaniel
> ""confused"" wrote:

Problems with ER in msde.

Hi, I have a database that was created on sql 2000 standard edition. The
system requiered to use remote servers so we decided to use msde.
Checking the information that is in the database we discover that there
is some information that is incorrectly.
For example, we have two tables: table1 and table1details, this has ER
defined between them but there is information that is on the
table1details that doesnt exists on table1.
This means that the ER is not working. We try to delete information in
QA but it wasn't posible.
Do somebody know if there is a problem with msde and ER created on the
database.
or do somebody know if ER is not respected when bulk insert data inserts
data into databases'
Thanks a lot for your help.
*** Sent via Developersdex http://www.examnotes.net ***Hi Maria
If the Foreign Key was created with the NOCHECK option existing data would
not be checked to see that it is valid
From BOL:
"WITH CHECK | WITH NOCHECK
Specifies whether the data in the table is or is not validated against a
newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified,
WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for
re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against
existing data, use WITH NOCHECK. This is not recommended except in rare
cases. The new constraint will be evaluated in all future updates. Any
constraint violations suppressed by WITH NOCHECK when the constraint is adde
d
may cause future updates to fail if they update rows with data that does not
comply with the constraint.
Constraints defined WITH NOCHECK are not considered by the query optimizer.
These constraints are ignored until all such constraints are re-enabled usin
g
ALTER TABLE table CHECK CONSTRAINT ALL."
It is also possible to load data with BULK INSERT/BCP that ignores constrain
ts
From BOL:
"CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the bulk
copy operation. By default, constraints are ignored. Note that the MAX_ERROR
S
option does not apply to constraint checking."
HTH
John
"MariaGuzman" wrote:

> Hi, I have a database that was created on sql 2000 standard edition. The
> system requiered to use remote servers so we decided to use msde.
> Checking the information that is in the database we discover that there
> is some information that is incorrectly.
> For example, we have two tables: table1 and table1details, this has ER
> defined between them but there is information that is on the
> table1details that doesn4t exists on table1.
> This means that the ER is not working. We try to delete information in
> QA but it wasn't posible.
> Do somebody know if there is a problem with msde and ER created on the
> database.
> or do somebody know if ER is not respected when bulk insert data inserts
> data into databases'
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Problems when connect sql2005 locally

Hi All,

When I use asp.net 1.1 to connect sql 2005 enterprise server which just enable local access option instead of remote, I got error message to say database server is not avaiable. I use following connection string

user id=abc;password=abc;server=(local);Integrated Security = SSIP;

user id=abc;password=abc;server=.;Integrated Security = SSIP;

Actually, I used default instance, and ASP.Net and database server are in same server. Everything is fine when I change connection string as

user id=abc;password=abc;server=servername;Integrated Security = SSIP;

Frank,

This is a known issue when SS25 does not have TCP on, the client is using MDAC/Everret(.Net1.1) OLEDB provider. For connection sting like (local) or “.”, the connection will fail.

One of the workaround, if TCP/IP is off, is to use hostname in connection string that will connect you through named pipe provider, which is what you have seen.

Thanks,

|||

One more note is that if you can't change the your applications and they use "(local)" and "." in connection string, you can create aliases for "(local)", and "." using tools like cliconfg.exe or SQL Server Configuration Manager.

Please also not that the the there is a typo in your connection string, it should be "SSPI" instead of "SSIP".