Do mirrored servers have to be in same domain?

hi,
Can someone tell me if servers participating in SQL 2005 database mirroring
have to be in the same domain or can they just be stand alone servers? I
can't seem to find any documentation about this.
Thanks.No.
In case if the domain account for any of the server is different for example
the domain account for mirror server is different than the domain account of
the principal or the witness server then you can use the following statement
to grant permissions to the endpoint to that domain account: (You will need
to run the below statement on all the three participating servers)
Use Master; Create Login [Domain\Domain_user_account] From Windows; Gran
t
Connect on EndPoint ::Mirroring_Endpoint to [Domain\Domain_user_account]
;
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Steve" wrote:

> hi,
> Can someone tell me if servers participating in SQL 2005 database mirrorin
g
> have to be in the same domain or can they just be stand alone servers? I
> can't seem to find any documentation about this.
> Thanks.
>

Do mirrored servers have to be in same domain?

hi,
Can someone tell me if servers participating in SQL 2005 database mirroring
have to be in the same domain or can they just be stand alone servers? I
can't seem to find any documentation about this.
Thanks.No.
In case if the domain account for any of the server is different for example
the domain account for mirror server is different than the domain account of
the principal or the witness server then you can use the following statement
to grant permissions to the endpoint to that domain account: (You will need
to run the below statement on all the three participating servers)
Use Master; Create Login [Domain\Domain_user_account] From Windows; Grant
Connect on EndPoint ::Mirroring_Endpoint to [Domain\Domain_user_account];
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Steve" wrote:
> hi,
> Can someone tell me if servers participating in SQL 2005 database mirroring
> have to be in the same domain or can they just be stand alone servers? I
> can't seem to find any documentation about this.
> Thanks.
>

Do Linked Servers REQUIRE SA rights

I am trying to set up a linked server, and one of the user is a SQL
user. This user only has permission to select data from several tables.
When I attempt to query the remote server I receive following message:
Access to the remote server is denied because no log in-mapping exists.
However when I grant the user SA rights on the local server that user
is able to query the data.
Any thoughts ?Accessing a linked server doesn't necessarily require
someone to be a member of the sysadmins role. You need to
check the login mapping as the error suggests - look at the
security tab for the linked server.
-Sue
On 8 Jun 2005 12:41:48 -0700, "Rafet Ducic"
<rducic@.gmail.com> wrote:

>I am trying to set up a linked server, and one of the user is a SQL
>user. This user only has permission to select data from several tables.
>
>When I attempt to query the remote server I receive following message:
>Access to the remote server is denied because no log in-mapping exists.
>
>However when I grant the user SA rights on the local server that user
>is able to query the data.
>Any thoughts ?

DNS on Windows 2000 Server & Windows 2003 Server

We are having numerous issues with disconnects after upgrading our PDC to a
Windows 2003 environment. We have several Windows 2000 Servers running SQL
Databases that users connect to. The issue as described in my post found
under client connections is that the network connection is good but we are
getting RPC errors & "The object invoked disconnected message". Our Windows
2003 Server is running DNS. It was suggested by a developer to install the
DNS windows components found under networking on all other servers.
Will this work? Or will we cause DNS issues as a result of it?Hi
Installing DNS on every server will not solve your problem.
DNS Service in Windows 2003 is reliable so there is not need to setup many
of them.
What the the exact error number and error messages you are getting? Are any
errors logged in the SQL Event Log?
Regards
Mike
"Cstewart" wrote:

> We are having numerous issues with disconnects after upgrading our PDC to
a
> Windows 2003 environment. We have several Windows 2000 Servers running SQ
L
> Databases that users connect to. The issue as described in my post found
> under client connections is that the network connection is good but we are
> getting RPC errors & "The object invoked disconnected message". Our Windo
ws
> 2003 Server is running DNS. It was suggested by a developer to install th
e
> DNS windows components found under networking on all other servers.
> Will this work? Or will we cause DNS issues as a result of it?
>|||The errors that are appearing on the client side are as follows:
"The object invoked has disconnected from its clients" and/or "RPC server
unavailable"
Here is more information that I posted previously:
We have recently upgraded our main server from NT4 to Active Directory
(Windows 2003 Server). Our SQL Database is 2000 and is running on a Windows
2000 Server Box. We use com+ components to connect to our database. Since
the change on our PDC to 2003 Server AD, we have had several errors such as
"The object invoked has disconnected from its clients" as well as RPC Server
Unavailable. This occurs even when the user is consistently active. We hav
e
applied latest Windows Service Pack to clients xp sp2 and to the windows 200
0
server box. If you reboot the server and or/stop services (Com+ and Dcom) o
n
the client end, they can get back in. You can browse to the machine no
problem through exporer - right to the database. Issue is only in SQL 2000.
Any assistance would be greatly appreciated. Note: I have also checked
using netstat -a to see if all ports were being used and that is not the
case. This is happening on more than one users connection and I am certain
it is not network based - ping 100%, can browse, two locations within
building etc.
Note: Problems occur regardless if client is at Service Pack 2 or 1.
I have checked the event log on the server and nothing reported. I have
checked the error logs in the SQL directory of Logs and nothing reported.
Any suggestion as to what other logs (if any) to look at?
Thank you for your assistance.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Installing DNS on every server will not solve your problem.
> DNS Service in Windows 2003 is reliable so there is not need to setup many
> of them.
> What the the exact error number and error messages you are getting? Are an
y
> errors logged in the SQL Event Log?
> Regards
> Mike
> "Cstewart" wrote:
>

DNS on Windows 2000 Server & Windows 2003 Server

We are having numerous issues with disconnects after upgrading our PDC to a
Windows 2003 environment. We have several Windows 2000 Servers running SQL
Databases that users connect to. The issue as described in my post found
under client connections is that the network connection is good but we are
getting RPC errors & "The object invoked disconnected message". Our Windows
2003 Server is running DNS. It was suggested by a developer to install the
DNS windows components found under networking on all other servers.
Will this work? Or will we cause DNS issues as a result of it?Hi
Installing DNS on every server will not solve your problem.
DNS Service in Windows 2003 is reliable so there is not need to setup many
of them.
What the the exact error number and error messages you are getting? Are any
errors logged in the SQL Event Log?
Regards
Mike
"Cstewart" wrote:
> We are having numerous issues with disconnects after upgrading our PDC to a
> Windows 2003 environment. We have several Windows 2000 Servers running SQL
> Databases that users connect to. The issue as described in my post found
> under client connections is that the network connection is good but we are
> getting RPC errors & "The object invoked disconnected message". Our Windows
> 2003 Server is running DNS. It was suggested by a developer to install the
> DNS windows components found under networking on all other servers.
> Will this work? Or will we cause DNS issues as a result of it?
>|||The errors that are appearing on the client side are as follows:
"The object invoked has disconnected from its clients" and/or "RPC server
unavailable"
Here is more information that I posted previously:
We have recently upgraded our main server from NT4 to Active Directory
(Windows 2003 Server). Our SQL Database is 2000 and is running on a Windows
2000 Server Box. We use com+ components to connect to our database. Since
the change on our PDC to 2003 Server AD, we have had several errors such as
"The object invoked has disconnected from its clients" as well as RPC Server
Unavailable. This occurs even when the user is consistently active. We have
applied latest Windows Service Pack to clients xp sp2 and to the windows 2000
server box. If you reboot the server and or/stop services (Com+ and Dcom) on
the client end, they can get back in. You can browse to the machine no
problem through exporer - right to the database. Issue is only in SQL 2000.
Any assistance would be greatly appreciated. Note: I have also checked
using netstat -a to see if all ports were being used and that is not the
case. This is happening on more than one users connection and I am certain
it is not network based - ping 100%, can browse, two locations within
building etc.
Note: Problems occur regardless if client is at Service Pack 2 or 1.
I have checked the event log on the server and nothing reported. I have
checked the error logs in the SQL directory of Logs and nothing reported.
Any suggestion as to what other logs (if any) to look at?
Thank you for your assistance.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Installing DNS on every server will not solve your problem.
> DNS Service in Windows 2003 is reliable so there is not need to setup many
> of them.
> What the the exact error number and error messages you are getting? Are any
> errors logged in the SQL Event Log?
> Regards
> Mike
> "Cstewart" wrote:
> > We are having numerous issues with disconnects after upgrading our PDC to a
> > Windows 2003 environment. We have several Windows 2000 Servers running SQL
> > Databases that users connect to. The issue as described in my post found
> > under client connections is that the network connection is good but we are
> > getting RPC errors & "The object invoked disconnected message". Our Windows
> > 2003 Server is running DNS. It was suggested by a developer to install the
> > DNS windows components found under networking on all other servers.
> >
> > Will this work? Or will we cause DNS issues as a result of it?
> >
> >

DNS on Windows 2000 Server & Windows 2003 Server

We are having numerous issues with disconnects after upgrading our PDC to a
Windows 2003 environment. We have several Windows 2000 Servers running SQL
Databases that users connect to. The issue as described in my post found
under client connections is that the network connection is good but we are
getting RPC errors & "The object invoked disconnected message". Our Windows
2003 Server is running DNS. It was suggested by a developer to install the
DNS windows components found under networking on all other servers.
Will this work? Or will we cause DNS issues as a result of it?
Hi
Installing DNS on every server will not solve your problem.
DNS Service in Windows 2003 is reliable so there is not need to setup many
of them.
What the the exact error number and error messages you are getting? Are any
errors logged in the SQL Event Log?
Regards
Mike
"Cstewart" wrote:

> We are having numerous issues with disconnects after upgrading our PDC to a
> Windows 2003 environment. We have several Windows 2000 Servers running SQL
> Databases that users connect to. The issue as described in my post found
> under client connections is that the network connection is good but we are
> getting RPC errors & "The object invoked disconnected message". Our Windows
> 2003 Server is running DNS. It was suggested by a developer to install the
> DNS windows components found under networking on all other servers.
> Will this work? Or will we cause DNS issues as a result of it?
>
|||The errors that are appearing on the client side are as follows:
"The object invoked has disconnected from its clients" and/or "RPC server
unavailable"
Here is more information that I posted previously:
We have recently upgraded our main server from NT4 to Active Directory
(Windows 2003 Server). Our SQL Database is 2000 and is running on a Windows
2000 Server Box. We use com+ components to connect to our database. Since
the change on our PDC to 2003 Server AD, we have had several errors such as
"The object invoked has disconnected from its clients" as well as RPC Server
Unavailable. This occurs even when the user is consistently active. We have
applied latest Windows Service Pack to clients xp sp2 and to the windows 2000
server box. If you reboot the server and or/stop services (Com+ and Dcom) on
the client end, they can get back in. You can browse to the machine no
problem through exporer - right to the database. Issue is only in SQL 2000.
Any assistance would be greatly appreciated. Note: I have also checked
using netstat -a to see if all ports were being used and that is not the
case. This is happening on more than one users connection and I am certain
it is not network based - ping 100%, can browse, two locations within
building etc.
Note: Problems occur regardless if client is at Service Pack 2 or 1.
I have checked the event log on the server and nothing reported. I have
checked the error logs in the SQL directory of Logs and nothing reported.
Any suggestion as to what other logs (if any) to look at?
Thank you for your assistance.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Installing DNS on every server will not solve your problem.
> DNS Service in Windows 2003 is reliable so there is not need to setup many
> of them.
> What the the exact error number and error messages you are getting? Are any
> errors logged in the SQL Event Log?
> Regards
> Mike
> "Cstewart" wrote:

DMO and Windows Authentication

I'd like to use DMO to browse for databases on our SQL Servers using Windows Authneitication, rather than the SQL Server Authentication with login and password. Is this possible. This is the chuck of code that I've borrowed to do it so far. Can you make the Connection without the user and password?

Thanks,

Howie

Set SQLInstance = New SQLDMO.SQLServer
'Connect to the server by name (from global variable)
SQLInstance.Connect t_Server, t_User, t_Password
'Set the instance to that server ID
SQLSrvrID = SQLInstance.ConnectionID
Set SQLInstance = SQL2000.SQLServers.ItemById(SQLSrvrID)

'Display the available databases in a list box
ListDatabases.Clear
x = 1
For Each DB In SQLInstance.Databases
'Do not add system databases to the list
If Not SQLInstance.Databases(x).SystemObject Then
Me.ListDatabases.AddItem (SQLInstance.Databases(x).name)
End If
x = x + 1
Next

Just as an added question, does anyone know how you might go about exploring for data sources in VB, either with ADO or DMO? These would be the ODBC data source that you would create by:

Control Panel

Administrative Tools

Data Sources(ODBC)

Once in the Data Sources application, you pick a name for the data source, and then locate and define the server and associated database.

Thanks gain for any ideas,

Howie

|||I am using VB6 and I can easily get the username using the API

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

|||' login using windows authentication
SQLInstance.LoginSecure = True
SQLInstance.Connect sServer

You don't need the API above.. thanks to Josh on his website for the answer!

Josh Fruits
http://www.programmers-corner.com

josh@.programmerscorner.com

DML by Linked Servers

Hi,

I have two SQL-Server 2000 servers - SERVERDB01 and SERVERDB02.
I made a linked server from SERVERDB01 to SERVERDB02.

This instruction is ok.
SELECT * FROM openquery (SERVERDB02,'select * from dbacdl.aux_data')

I need execute DML commands by Linked Servers.
i.e. -> truncate dbacdl.aux_data or delete dbacdl.aux_data

How can I do?

Thank you by attention.

Bye.how about issuing the truncate or delete using the four part nameing convention?

<Server Name>.<Database Name>.<Owner Name>.<Object Name>|||Paul Young,

thank you by the answer, but It did wrong.

I was in the SERVERDB01
TRUNCATE TABLE SERVERDB02.SICDB.DBACDL.AUX_DATA

This mensage appear:
The object name 'SERVERDB02.SICDB.DBACDL.' contains more than the maximum number of prefixes. The maximum is 2.

I did some mistake, maybe.

Bye.|||No, I answqered your question a little to fast. Truncate won't work but delete will, try again using delete rather than truncate.|||Paul Young ,

It's ok.

thank you very much.

Bye.

Distribution server restore on standby server

Hi,
we have developed an application using transactional replication, with
several publication servers (push) and a distribution server (which is
the only subscriber too). It seems to work fine.
Now we would "protect" the distribution/subscriber server by using a
standby server (note: only the distribution/subscriver server must be
protected, not publication servers) which should replace the working
server in the case it crashes.
Could someone suggest us the best strategy to do this? Thanks in
advance...
Marco
You can manually set this up. Have a look at Strategies for Backing Up and
Restoring Transactional Replication in BOL.
The problem is that this adds to the latency. Transactions remain in your
tlog until the log is dumped. Then they are read from the tlog and written
to the distirbution database.
"Marco69" <marcosindona@.virgilio.it> wrote in message
news:ce7beb14.0403250541.5f374a8f@.posting.google.c om...
> Hi,
> we have developed an application using transactional replication, with
> several publication servers (push) and a distribution server (which is
> the only subscriber too). It seems to work fine.
> Now we would "protect" the distribution/subscriber server by using a
> standby server (note: only the distribution/subscriver server must be
> protected, not publication servers) which should replace the working
> server in the case it crashes.
> Could someone suggest us the best strategy to do this? Thanks in
> advance...
> Marco

Distribution Agent Startup Parameters

Hello,
I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1. One
server is a publisher, the other a subscriber. The replication topology is
Transaction Replication with 1 queued updating subscriber. The distribution
agent resides on the publisher.
Firstly, I am aware of nosync replication, but my environment requires some
more testing before I can implement it, because I can't take the publisher
offline, and I believe some tweaks are needed to ensure that the data will
truly be in sync. This said, one of my publications has a table article with
234 million records to transfer when the article is reinitialized. I am
having problems with the transaction log growing so large on the subscriber,
that it consumes all server space, and effectively kills the distribution
agent.
To mitigate this, I have tried to use the -BcpBatchSize parameter to try to
get the rows per batch down to manageable size so that I can manually shrink
the transaction log intermittantly while the distribution agent is applying
the snapshot. My strategy is not working.
Questions:
1) does -BcpBatchSize directly control the application of the snapshotted
article's transaction commit size, or is this parameter used outside of the
distribution agent's application of a snapshot?
2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I ask
because bulk insert is different from bcp.
3) would -CommitBatchSize be a more appropriate setting to allow me to
actively manage the transaction log on the subscriber while this huge table
is being transferred?
Thanks for any input,
John T
If you are using concurrent snapshot on SQL2000, your only hope will be to
make sure that you hit the minimal logging code path when the snapshot is
delivered to the subscriber as the *entire* snapshot will be applied in an
atomic transaction. At the risk of telling you something that you may know
already, this means that you need to:
1) Make sure that 'select into\bulk copy' is enabled at the subscriber
database
2) The target table is empty with at most one index or not having any
indexes when the bulk copy occurs. This may be tricky to enforce as the
SQL2000 index creation logic in the distribution agent is not very precise.
You may want to modify the .idx scripts to leave only the primary key index
and then manually create other indexes afterwards.
3) Set -BcpBatchSize to be *larger* than the maximum number of rows in your
published tables. 2^31 - delta should work fine. Notice that you may start
seeing "Agent is waiting for database backend.." messages since the
distribution agent will not be able to log any progress messages until the
entire bulk load operation is done. You have also want to increase
the -QueryTimeout of the distribution agent.
-Raymond
"John T" <JohnT@.discussions.microsoft.com> wrote in message
news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
> Hello,
> I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1. One
> server is a publisher, the other a subscriber. The replication topology
> is
> Transaction Replication with 1 queued updating subscriber. The
> distribution
> agent resides on the publisher.
> Firstly, I am aware of nosync replication, but my environment requires
> some
> more testing before I can implement it, because I can't take the publisher
> offline, and I believe some tweaks are needed to ensure that the data will
> truly be in sync. This said, one of my publications has a table article
> with
> 234 million records to transfer when the article is reinitialized. I am
> having problems with the transaction log growing so large on the
> subscriber,
> that it consumes all server space, and effectively kills the distribution
> agent.
> To mitigate this, I have tried to use the -BcpBatchSize parameter to try
> to
> get the rows per batch down to manageable size so that I can manually
> shrink
> the transaction log intermittantly while the distribution agent is
> applying
> the snapshot. My strategy is not working.
> Questions:
> 1) does -BcpBatchSize directly control the application of the snapshotted
> article's transaction commit size, or is this parameter used outside of
> the
> distribution agent's application of a snapshot?
> 2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I ask
> because bulk insert is different from bcp.
> 3) would -CommitBatchSize be a more appropriate setting to allow me to
> actively manage the transaction log on the subscriber while this huge
> table
> is being transferred?
> Thanks for any input,
> John T
|||Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
which means that it controls how many rows will be applied at a time in a
batch. Won't setting it to a large value create a very large transaction log
for large tables?
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:evddMLk8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> If you are using concurrent snapshot on SQL2000, your only hope will be to
> make sure that you hit the minimal logging code path when the snapshot is
> delivered to the subscriber as the *entire* snapshot will be applied in an
> atomic transaction. At the risk of telling you something that you may know
> already, this means that you need to:
> 1) Make sure that 'select into\bulk copy' is enabled at the subscriber
> database
> 2) The target table is empty with at most one index or not having any
> indexes when the bulk copy occurs. This may be tricky to enforce as the
> SQL2000 index creation logic in the distribution agent is not very
> precise. You may want to modify the .idx scripts to leave only the primary
> key index and then manually create other indexes afterwards.
> 3) Set -BcpBatchSize to be *larger* than the maximum number of rows in
> your published tables. 2^31 - delta should work fine. Notice that you may
> start seeing "Agent is waiting for database backend.." messages since the
> distribution agent will not be able to log any progress messages until the
> entire bulk load operation is done. You have also want to increase
> the -QueryTimeout of the distribution agent.
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
>
|||Hilary, what you said is true, but the goal of my reccomendations is not to
have small transactions but to hit the minimal logging (or bulk-logged) code
path in the server which is the only feasible way to have a manageable log
size when a concurrent snapshot is applied in SQL2000. If a small batch size
is specified, the server will see the target table as non-empty after the
first batch is loaded and start going down the fully-logged code path after
that. And since there is an outer transaction bounding the delivery of a
concurrent snapshot in SQL2000, the log simply cannot be truncated even with
a small bcp batch size. I can understand why you think this is
counter-intuitive, but my suggestion had worked on a few occasions before
including the following:
http://groups.google.com/group/micro... e49f1fb79fe5
-Raymond
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uuGAtgk8GHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
> which means that it controls how many rows will be applied at a time in a
> batch. Won't setting it to a large value create a very large transaction
> log for large tables?
> --
> 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
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:evddMLk8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>
|||Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:Oy1jKJl8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> Hilary, what you said is true, but the goal of my reccomendations is not
> to have small transactions but to hit the minimal logging (or bulk-logged)
> code path in the server which is the only feasible way to have a
> manageable log size when a concurrent snapshot is applied in SQL2000. If a
> small batch size is specified, the server will see the target table as
> non-empty after the first batch is loaded and start going down the
> fully-logged code path after that. And since there is an outer transaction
> bounding the delivery of a concurrent snapshot in SQL2000, the log simply
> cannot be truncated even with a small bcp batch size. I can understand why
> you think this is counter-intuitive, but my suggestion had worked on a few
> occasions before including the following:
> http://groups.google.com/group/micro... e49f1fb79fe5
> -Raymond
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uuGAtgk8GHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||Raymond,
Just a couple of points of clarification:
1) If I am using simple recovery mode, I should get the same benefits in
this case as using bulk recovery mode, correct?
2) If I choose the schema option to drop the tables before the snapshot is
applied, this should meet all of the requirements in your second point,
correct?
3) Regarding the -BcpBatchSize setting of 2^31 - delta, what is the delta,
my desired batch commit size?
Thanks
John T
"Raymond Mak [MSFT]" wrote:

> If you are using concurrent snapshot on SQL2000, your only hope will be to
> make sure that you hit the minimal logging code path when the snapshot is
> delivered to the subscriber as the *entire* snapshot will be applied in an
> atomic transaction. At the risk of telling you something that you may know
> already, this means that you need to:
> 1) Make sure that 'select into\bulk copy' is enabled at the subscriber
> database
> 2) The target table is empty with at most one index or not having any
> indexes when the bulk copy occurs. This may be tricky to enforce as the
> SQL2000 index creation logic in the distribution agent is not very precise.
> You may want to modify the .idx scripts to leave only the primary key index
> and then manually create other indexes afterwards.
> 3) Set -BcpBatchSize to be *larger* than the maximum number of rows in your
> published tables. 2^31 - delta should work fine. Notice that you may start
> seeing "Agent is waiting for database backend.." messages since the
> distribution agent will not be able to log any progress messages until the
> entire bulk load operation is done. You have also want to increase
> the -QueryTimeout of the distribution agent.
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
>
>
|||Hi John,
In response to your inquiry:
1) Simple recovery mode is fine.
2) You may still need to make sure that only the primary key index got
created since the index creation logic in SQL2000 distribution agent is not
as optimal as it can be.
3) Delta is just a small number, you only need to make sure that the
resulting bcpbatchsize is bigger than the maximum number of rows in any of
your tables
Hope that helps,
-Raymond
"John T" <JohnT@.discussions.microsoft.com> wrote in message
news:A677C2FE-F672-4473-9B2E-725AFE500039@.microsoft.com...[vbcol=seagreen]
> Raymond,
> Just a couple of points of clarification:
> 1) If I am using simple recovery mode, I should get the same benefits in
> this case as using bulk recovery mode, correct?
> 2) If I choose the schema option to drop the tables before the snapshot is
> applied, this should meet all of the requirements in your second point,
> correct?
> 3) Regarding the -BcpBatchSize setting of 2^31 - delta, what is the delta,
> my desired batch commit size?
> Thanks
> John T
> "Raymond Mak [MSFT]" wrote:
|||Wait a minute, I think you are the one who owe me a beer, and I have proof
for that...
http://groups.google.com/group/micro... c299fb7328e2d
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23yI5FQr8GHA.3820@.TK2MSFTNGP02.phx.gbl...
> Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)
> --
> 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
>
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:Oy1jKJl8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
|||freaking Google.
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:enfPtCt8GHA.1492@.TK2MSFTNGP02.phx.gbl...
> Wait a minute, I think you are the one who owe me a beer, and I have proof
> for that...
> http://groups.google.com/group/micro... c299fb7328e2d
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23yI5FQr8GHA.3820@.TK2MSFTNGP02.phx.gbl...
>
|||This suggestion worked excellently. I went from filling up over 275 gigs of
drive space trying to deploy these large tables, to only using 975 megs of
drive space.
Thank you,
John T
"Raymond Mak [MSFT]" wrote:

> Hi John,
> In response to your inquiry:
> 1) Simple recovery mode is fine.
> 2) You may still need to make sure that only the primary key index got
> created since the index creation logic in SQL2000 distribution agent is not
> as optimal as it can be.
> 3) Delta is just a small number, you only need to make sure that the
> resulting bcpbatchsize is bigger than the maximum number of rows in any of
> your tables
> Hope that helps,
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:A677C2FE-F672-4473-9B2E-725AFE500039@.microsoft.com...
>
>

distributing reports to multiple report servers

Hi,
I am trying to figure out how to package and deploy reports to multiple
report servers. We have 200+ clients, and are trying to switch over to
report server, but I can't find any documentation about deploying reports
outside of an organization.
To clarify a little, new releases come out quarterly, and are installed with
an msi installer, and we would like to be able to distribute reports with
our current process.
any help would be greatly appreciated...
Thanks
CraigCraig,
There is a sample script that shows how to automate the publishing of
the reports. Is this the kind of thing you're looking for?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_sampleapps_v1_1apd.asp
Your msi installer could land the report rdl and collect the variables
necessary for running a script similar to this.
Andy Potter|||That should do just fine =)
Thanks,
Craig
"Potter" <drewpotter@.gmail.com> wrote in message
news:1135889173.574650.189300@.g43g2000cwa.googlegroups.com...
> Craig,
> There is a sample script that shows how to automate the publishing of
> the reports. Is this the kind of thing you're looking for?
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_sampleapps_v1_1apd.asp
> Your msi installer could land the report rdl and collect the variables
> necessary for running a script similar to this.
> Andy Potter
>|||To help automate the script generation have a look at
Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Craig L" <craig.lonsbury@.diamondmunicipal.com> wrote in message
news:eShO$RLDGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am trying to figure out how to package and deploy reports to multiple
> report servers. We have 200+ clients, and are trying to switch over to
> report server, but I can't find any documentation about deploying reports
> outside of an organization.
> To clarify a little, new releases come out quarterly, and are installed
> with an msi installer, and we would like to be able to distribute reports
> with our current process.
> any help would be greatly appreciated...
> Thanks
> Craig
>

Distributed transactions: ITransactionJoin error on two 2003 Servers

I'm hoping someone can help me, I am trying to start a distributed
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UK
I am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.codecomments.com ***

Distributed transactions: ITransactionJoin error on two 2003 Servers

I'm hoping someone can help me, I am trying to start a distributed
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UKI am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.codecomments.com ***

Distributed transactions: ITransactionJoin error on two 2003 Servers

I'm hoping someone can help me, I am trying to start a distributed
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UKI am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.developersdex.com ***

Distributed transactions with multiple instances of Microsoft SQL Server

Hi,

I'm having a problem running a distributed transaction between two
linked servers that both have multiple instances of SQL Server
installed on them. This is the error message that I receive:

"The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."

The query follows the format:

"BEGIN DISTRIBUTED TRAN
UPDATE [LINKEDSERVER1\INSTANCE_NAME].DB.OWNER.TABLENAME
SET fieldname = alias2.fieldname
FROM tablename alias2
JOIN [LINKEDSERVER1\INSTANCE_NAME].DB.OWNER.TABLENAME alias1
on alias2.urn=alias1,urn"

>From what I can gather from various sources the SQL Server must be
named the same as the computer which it is installed on. However, if I
have two instances of SQL Server, they cannot both be named the same as
the computer. Does anyone know of a way around this or whether I'm
barking up the wrong tree completely?

Many thanks.Assuming that you can query the linked server successfully (ie run a
SELECT query), then the issue may be DTC rather than the instance names
- there are a number of possible reasons for the error:

http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701
http://support.microsoft.com/kb/839279

Simon|||Thanks! This one fixed it: http://support.microsoft.com/kb/816701. I
was using Windows 2003 server which has DTC access disabled by default.

Distributed Transactions fail

We are trying to create SPs that are using linked servers. I have been able
to create the linked server and see the views and tables. The issue is that
after creating a SP the uses the 4 part naming convention all atempt to
actually save the querry have failed. In some cases I can run the SQL code
but I just cant save it as a SP
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Now before you say visit the MS knowledge base I have and this did not fix
the problem:
http://support.microsoft.com/defaul...kb;en-us;839279
DTC is turned on and the logon is set as a Network Service.What else could
be the issue. Is it user permission probs. Deprately need help. I cant even
save via QA or .NET designer
JP
.NET Software DevelperJP,
Is the DTC service running on both servers? Might look at the 'data access'
, 'rpc' and 'rpc out' settings...not sure if this has anything to do with
what you're experiencing but LSs can sometimes return some error messages
that may not directly point to the cause of an issue.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:8D2DF49D-0F5F-4470-8F26-C6D34209E6B3@.microsoft.com...
> We are trying to create SPs that are using linked servers. I have been
> able
> to create the linked server and see the views and tables. The issue is
> that
> after creating a SP the uses the 4 part naming convention all atempt to
> actually save the querry have failed. In some cases I can run the SQL code
> but I just cant save it as a SP
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction. [OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Now before you say visit the MS knowledge base I have and this did not fix
> the problem:
> http://support.microsoft.com/defaul...kb;en-us;839279
> DTC is turned on and the logon is set as a Network Service.What else could
> be the issue. Is it user permission probs. Deprately need help. I cant
> even
> save via QA or .NET designer
>
>
> --
> JP
> .NET Software Develper

Distributed Transactions between SQL2005 and SQL2000

Hi there,

We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONE\SQL2000' and 'YELLOWSTONE\SQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONE\SQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONE\SQL2000', I get the following error:

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -

Ok, Figured it out. I needed to define the Server type as 'SQL Server' instead of 'Other' and then picking the SQL Server connector. Ooops!

Distributed transactions bet linked servers

hi anyone,
I checked the DTC on both ends to be on but I am still getting this message
when running an insert
New transaction cannot enlist in the specified transaction coordinator.
Thanks,
--
bicDid you enable Network DTC Access? I did it under 2003 Server but had to do
it again after installing SP1.
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
"bic" wrote:
> hi anyone,
> I checked the DTC on both ends to be on but I am still getting this message
> when running an insert
> New transaction cannot enlist in the specified transaction coordinator.
> Thanks,
> --
> bic

Distributed transactions bet linked servers

hi anyone,
I checked the DTC on both ends to be on but I am still getting this message
when running an insert
New transaction cannot enlist in the specified transaction coordinator.
Thanks,
bic
Did you enable Network DTC Access? I did it under 2003 Server but had to do
it again after installing SP1.
http://support.microsoft.com/default...b;en-us;329332
"bic" wrote:

> hi anyone,
> I checked the DTC on both ends to be on but I am still getting this message
> when running an insert
> New transaction cannot enlist in the specified transaction coordinator.
> Thanks,
> --
> bic

Distributed transactions bet linked servers

hi anyone,
I checked the DTC on both ends to be on but I am still getting this message
when running an insert
New transaction cannot enlist in the specified transaction coordinator.
Thanks,
bicDid you enable Network DTC Access? I did it under 2003 Server but had to do
it again after installing SP1.
http://support.microsoft.com/defaul...kb;en-us;329332
"bic" wrote:

> hi anyone,
> I checked the DTC on both ends to be on but I am still getting this messag
e
> when running an insert
> New transaction cannot enlist in the specified transaction coordinator.
> Thanks,
> --
> bic