Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Unanswered: Transactional Replication syntax error or access violation

    Hello,

    I am attempting to get Transactional Replication configured on 2 SQL Server 2000 boxes. They are both standalone, not in a domain. I am able to push the inital snapshot, but transactional updates always fail with the following error:

    "Syntax error or access violation (Source: ODBC SQL Server Driver (ODBC); Error number: 37000)"

    I haven't found anything useful in the MS KB or Books Online with respect to this error.

    At first, the servers were configured to use the LocalSystem account for running the SQLServerAgent service. Changing them to a user that is a member of the local Administrators group did not change the error. I
    have now tried to use the SA account, and I still get the same error when trying to perform an update.

    I am able to log on to the remote server with Query Analyzer and see the tables, whether I use SA or the Administrator account (the accounts use the same admin user password on each computer to sort of emulate a
    domain). Network connectivity between the two machines is not an issue, as they are on the same subnet (for testing purposes, but the production machines will be two different computers located on remote networks).

    Any ideas? Has anyone successfully pulled off transactional replication without a domain?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    Paranon,

    Where do you see the error message? Which agent has error in replication monitor?

    It should use OLE DB between SQL servers, not ODBC. OK?

    What version is SQL?
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  3. #3
    Join Date
    Feb 2003
    Posts
    4
    >Where do you see the error message? Which agent has error in
    > replication monitor?

    Yes, I see the error in the Replication Monitor, or under the Distribution Agent.

    > It should use OLE DB between SQL servers, not ODBC. OK?

    I haven't changed the default settings, and I don't know if it uses ODBC or OLE DB by default. I followed a standard walkthrough to enable transactional replication, but so far haven't gotten it to work.

    > What version is SQL?

    SQL Server 2000 SP3 on both machines.

    Thank you!

    P

  4. #4
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    Be aware that the Snapshot Agent must have full control access to the replication folder (by default: ...\Program Files\Microsoft Sql Server\ReplData), and the Distribution Agents and the Merge Agents must have read access. This is valid for all types of replication.
    So, ensure the access of the subscriber to your replication folder. (share the folder)


    Originally posted by paranon
    >Where do you see the error message? Which agent has error in
    > replication monitor?

    Yes, I see the error in the Replication Monitor, or under the Distribution Agent.

    > It should use OLE DB between SQL servers, not ODBC. OK?

    I haven't changed the default settings, and I don't know if it uses ODBC or OLE DB by default. I followed a standard walkthrough to enable transactional replication, but so far haven't gotten it to work.

    > What version is SQL?

    SQL Server 2000 SP3 on both machines.

    Thank you!

    P
    Steve

  5. #5
    Join Date
    Feb 2003
    Posts
    4
    Originally posted by dbadelphes
    Be aware that the Snapshot Agent must have full control access to the replication folder (by default: ...\Program Files\Microsoft Sql Server\ReplData), and the Distribution Agents and the Merge Agents must have read access. This is valid for all types of replication.
    So, ensure the access of the subscriber to your replication folder. (share the folder)
    Unfortunately, in my test environment, both servers are running all services as Administrator. I do not have a domain, but used the same username (Administrator) and password on both machines.

    Thanks

    -P

  6. #6
    Join Date
    Feb 2003
    Posts
    4

    Angry

    Originally posted by paranon
    Unfortunately, in my test environment, both servers are running all services as Administrator. I do not have a domain, but used the same username (Administrator) and password on both machines.

    Thanks

    -P
    Just for kicks, I just promoted one of my servers to be a Domain Controller, set the services on both machines to run as DOMAIN\Administrator, re-ran the Distribution Agent on the Publisher/Distributor, and received the exact same error.

    Any further thoughts?

    -P

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •