I have 2 SQL 2000 servers (both have SP4) and are running on Win2003 SP1. We will call them SQL1 and SQL2.
SQL1 is the publisher and distributor for trans replication, SQL2 is the subscriber with immediate updating, and queued updating as failover.
I configured the publisher and subscriber. The snapshot replicates fine to the subscriber and all the agents are working fine. There is only 1 table article configured for replication.
Let's say I am trying to update a single row.
I can make as many updates on this row to the publisher as I want and they all replicate just fine to the subscriber. (Note: an update to publisher row causes a new GUID to be generated in the "msrepl_tran_version" column.) The updated data and the new GUID are successfully replicated to subscriber. I can continue to successfully make as many updates to this row on the publisher just fine.
Now I want to make an update to this row at the subscriber: When I do this (via enterprise manager), I update column 1 with a new value, but a new GUID is NOT created on the subscriber. The column I updated successfully replicates back to the publisher on the first update attempt. This update causes the publisher to create a new GUID for the row, but the new GUID does not replicate back to the subscriber. (at this point the publisher and subscriber do not have matching GUID values).
Further updates to this row on the subscriber cause an error "...rows do not match between publisher and subscriber...", and further updates to the publisher do not cause an error message, but simply does not update back to the subscriber.
Hello, During an intantanée arrest of replication of data base sql server 7 sp2 (nt4), I lose keys on the subscriber, how don't have it? Thank you in advance easter pascal
I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.
I am sure this has been asked multiple times before, but I have a DB (SQL 2K) that is involved in transactional replication. The log periodically grows to a seriously big size. How can I shrink it without damaging replication? I have opened SSIS (SQL 2005 is the subscriber) and tried to shrink the file, but to no avail. It reckons I could shrink it to 0MB, but I feel this would throw out replication.
Here's what I'm trying to do (Maybe there's a better way)
I'm using Transaction Replication and a pull subscription to get all the transactions from the ORDERS table within the last year from Server_A to Server_B. I filter the rows "WHERE (ORDR_DATE > (GETDATE()) - 365)"
That works fine, BUT, the old rows in the subscriber's ORDERS table do not get deleted, so I have all the records for the last year, plus the older records that I don't want any more. Do I need another step some where that does a "Delete WHERE (ORDR_DATE < (GETDATE()) - 365)" But then I have to maintain my date logic in more than 1 place if I ever want to change it.
Any suggestions, thoughts, improvements will be appreciated .... Thanks a lot
I configured the transactional replication using push method. I use the option generate snapshot. Until the Snapshot complete, I didn't any tables in the subscriber db.After that also I can see in some tables it has indexes and some tables it doesn't but it has data in that table .
How does the bulk copy works from snapshot?i.e. is it first create the table with columns then insert data later it will create the indexes...How the sequence works in the subscriber.
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId) WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
I've got a simple transactional replication set up. I have a separate publisher, distributor, and subscriber with 76 articles (tables only) being pushed from the distributor.
I have this exact setup with the same tables and data working in the SQL 2000 environment. I am testing replication on our SQL 2005 test servers before moving to production, however when the distributor attempts to push out the initial snapshot I keep getting this error.
Error messages:
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102) Get help: http://help/102
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102) Get help: http://help/102
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156) Get help: http://help/156
Hi,I am having a problem with a replication over a Modem-Connection,which works fine over LAN. Has anyone experienced this problem before?Settings are:2 SQL Servers 2000, SP3on Windows 2000Publischer Database ist about 3GB, Subscriber DB about 1,5GBPublisher and Subscriber are connected via 56Kbit Dial-Up--> The Replication worked fine now this way for about 6 Months now!overall the publisher has about 20 Subscribers all connected viadial-up and they all work fine.Behavior now is like this:- Dial Up works fine- Synch starts- Synch takes some time (Upload works fine)- Synch fails- Error Message(german):Der Prozess konnte die Zeilenmetadaten auf 'Subscriber' nichtabfragen.{call sp_MSgetmetadatabatch(?,?,?)}Allgemeiner Netzwerkfehler. Weitere Informationen finden Sie in derDokumentation über Netzwerke.Der Prozess wurde erfolgreich beendet.If I connect the Subscriber-Server directly to the publisher servervia LAN it all works fine.My thesis is, that there might be some kind of "timeout" or something,as it cant be the dial-up network, because we tried different modemsand different server locations. And it all works fine on most of theother connedted subscribers. Only 2 other very big subscribersexperience the same problem, therefore i assume it could havesomething to to with slow connection speed, timeouts and an inabilityof the SQL server to handle this.Anyone seen this before? Anyone can help me?Thanks and Best Regards,Gerd
SQL Server 2012 Replication. The command prompt instance of the replication is being run as the same user as the SSMS "version" using the runas option. With SSMS the error is that the client is missing a privilege. There is no error when it runs from the command prompt. All runs well.
We have reinstalled the replication feature.We have gone through several MSDN articles that indicate exactly what privileges (both within the DB and in Windows) that service account should have. None have worked Incidentally, when I create a new publication and indicate that the SQL Server Agent be used , the publication runs.
I've got a popular problem so i get a message that server acces denied! ..
But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...
On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by
RETTO - name of my server
server=RETTO;uid=sa;pwd=password;database=db1; or by Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;
I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!
PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!
I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running
TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED
TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED
TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1025 *:*
UDP 0.0.0.0:1030 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 81.105.102.47:123 *:*
UDP 81.105.102.47:1900 *:*
UDP 81.105.102.47:5353 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1086 *:*
UDP 127.0.0.1:1900 *:*
Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.
The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:
Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
I would expect this as the DNS has not been advised to encrypt the conection.
This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.
This is on a XP machine trying to connect to the remote webhosting company via the internet.
I can ping the server
I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled
I do not have any aliases set up
No I do not force encryption
I wonder if you have any further suggestions to this problem?
I wnat to have a log file with all the updates and inserts made to the DB. I understand I have to do a trans-log,how? in order to make a query a transaction do I only have to declare trans...commit ??
I have set up a simple trans rep from a server in the office to the web. both servers are NT4sp6aSQL 7.0. Tables only.
The publication and distribution db are on the server at the office, and there is a full time connection through the firewall.
The initialization and first replication works perfectly, but after that, there is a message from the snapshot agent that "no subscriptions needed initialization", the logreader says thare are no replicated transactions and the Distribution agent says there are no replicated transactions. What am I missing?
Does anyone know how to move the transaction log(s) of a LIVE database to a new location. I must move the log of a database to a new mirrored drive without any disruption to users. I cannot take the database offline or use the sp_detach_db stored procedure. Your inputs are much appreciated!
I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)
I tried shinkfile (logfile,truncateonly) and no luck.
I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)
I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.
start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.
Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.
Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?
We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.
I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big
What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?
I keep reading various books and BOL, but I guess I don't quite "get it" yet ......
Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.
The error log file for the transaction log backups is...
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'ZCHQ_SQLPRODUCTION' as 'ZCISQLSERVICE' (trusted) Starting maintenance plan 'zeon_live_commerce DB Maintenance Plan1' on 6/12/2002 8:15:03 AM Backup can not be performed on database 'zeon_live_commerce'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'zeon_live_commerce DB Maintenance Plan1' on 6/12/2002 8:15:03 AM SQLMAINT.EXE Process Exit Code: 1 (Failed)
If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.
Hi, I was just wondering, if I have a DB for log shipping, and change the recovery model to bulk-insert before I do a dbreindex, does the log still grow as big as the full recovery model? as in when the DB in bulk-insert model, will dbreindex still writes to the log? What i'm trying to do is try to make the log files smaller for log ship when i'm doing the db reindexing job. Thanks.
I’m really scratching my head with this Transact-SQL, say you wanted to cycle through a set of rows, then perform an operation on each row, so in VB/DAO it might look like this: Dim rsTables As Recordset, rsIndex As Recordset Set rsTables = dbSource.OpenRecordset("SELECT * FROM INFORMATION_SCHEMA.tables") Do While rsTables.EOF Set rsIndex = dbSource.OpenRecordset("Select * From SysIndexes Where Name = '" & rsTables!Table_Name & "'") DoSomethingToIndex rsIndex!Name rsTables.MoveNext Loop
I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS( sections work however usualy they do, in this case it does not andt I can't seem to find out why.
Does anyone see the error?
Thanks
--========================================= --Create a local temporary table that hold --all the data from the source table --=========================================
SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC
--========================================= --Remove table entries that are no longer --needed or that have to be updated --=========================================
DELETE FROM LTTSTOCK
WHERE NOT EXISTS( SELECT * FROM #TEMP
WHERE LTTSTOCK.WarehouseNo = LTWHLO
AND LTTSTOCK.Location = LTWHSL
AND LTTSTOCK.ItemNo = LTITNO
AND LTTSTOCK.NumberAvail = LTAVAL
)
--=========================================
--Insert data that is missing or that
--needed to be updated and was previously
--deleted
--=========================================
INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)
Hi all, Just wondering if the virtual filesize of the transaction log can be changed to suite us. run dbcc loginfo(sagent_dev) display the followings:
FileId FileSize StartOffset FSeqNo Status Parity 213041664 8192 2110128 213041664 130498561960128 213041664 26091520197064 2524288039133184201064 2524288065404928204064 2524288070647808203064 25242880102105088210064 25242880107347968000 25242880112590848214264 25242880117833728213064 25242880123076608212064 Note that the virtual filesize can comprise of different configuration. In other database I can specify this logical size to whatever I like.
We would like to replicate from a SQL 7 DB some data onto a SQL 2000 Server running SQL 2000. We plan to take some data off the SQL 7 DB's and create a Data Warehouse on the SQL 2000 Box.
Question: Will the data replicate successfully from the SQL 7 box onto the SQL 2000 box?
I have a trans log backup that runs every 15 minutes on san, works fine until I do a backup after a large load. I get the below error message. Anyone had this before?
Executed as user: DPSCSDOMsqlexec. The file on device 'n:sqllogsmdentallgdmp' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step fail
We're running Lawson software on our SQL 2000 box. We're using Veritas Backup Exec to back up the databases to tape. I'm also using a Maint Plan for an extra backup (kinda redundant, but I need the practice at all this). I just added transaction logs to the maint plan (so I thought) but I only see a log for 1 of the 3 databases.
Also, my trans log file is 11 gig. I thought backing up the trans logs would get the trans to delete afterwards (databases are recovery model=full). Can anyone point a newbie in the right direction. Fortunately it's not in production yet, and we haven't had any disasters (yet).
No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec @p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20), @p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20), @p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14 varchar(20), @p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19 varchar(20), @p20 varchar(20) AS IF @p20 = 'P' update patient set date_insurance_updated = getdate() where patient_id = @p1 and practice_id = @p13
Is there anyway in SQL to backup the Database and Transaction log without truncte the Transaction log? :o. I would like to keep all or as much of the Transaction log info as possable. I know the Trans log will get large but HD space is not a problem. I use the transaction log to report field changes usung Log Explorer.
Should the transaction log of the system databases be backed up and if so, how often should this be done, generally?
Currently, we are backing up the system databases daily. The transaction log of the system databases, however, is not backed up.
Recently, I was wondering what exactly could happen, which would imply that we need to back up the transaction log of the system databases.
As far as I understand, 'msdb' contains things like jobs, dts. As such, if no jobs/dts are added in the middle of the day, there is no need to back up the trans log of this db - there could even be no reason, in such case, to back this db up at all!
'master' db would change if we add/modify objects/properties and so on ... so unless many changes are made, no need for backing up its trans log.
Having said that, could anyone enlighten me as to whether it is necessary to back up the transaction log of the system dbs and/or what it depends on and/or when it is suggested to do so?
Writing SQL scripts with insert trans. If one of the inserts failed rollback all previous inserts. Here what I come up with but I don't think it will work.
SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY Insert 1 END TRY BEGIN CATCH IF @@TRANCOUNT > 0
