Hi all,
I found that my testing server is accumulating shapshot folder in repldata. Everytime, we refresh the tables (snapshot publication), a new folder created without the old folders. And I found that there is at most 1 snapshot folder remain in production server. Any parameter to adjust the retention period of the snapshot folder? Thanks in advance
hi all,
Im running a snapshot replication on table.
first, i gonna copy the entire table with a regional filter other than date
second i'm going to replicate a days worth of data by using both the regional filter and date filter (getdate) to capture a days wordth of data.
the publication are working fine.
my question is how am i going to accumulated the result of
the filtered snaphot replication in a single table in my db.
many thanks,
joey
Hi,
I need to restore a sql server 2000 database which does not have any backups apart from a snapshot folder which contains some .bcp, .IDX and .Sch files. (there was a transactional replication setup on this database at some point and the snapshot folder is still there.)
Is this possible? any help will be highly appreciated.
thanks,
Alind
At present our snaopshot folder for merge replication(mainly) and our snapshot replications are on a seperate server. We now need to move the folder to a new server.
Anybody explain how we will do this and what affects it will have
Thanks
One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?
View 5 Replies View RelatedWe are using SQL Server 2005 SP2 to do transactional replication.
We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.
I have been getting an error message when we run the distribution clean up job.
Executed as user: PRODsqladmin. Could not remove directory '\Tes01boxRepldatauncqabox01_DB01_TO_ORACLE20070905104896'. Check the security context of xp_cmdshell
I have dropped the publication and recreated which is what appears to have caused the error.
From
http://technet.microsoft.com/en-us/library/ms151151.aspx
Note:
If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.
The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.
Finally my questions:
Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?
If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
hi all
when my snapshot folder is on a network drive, on which "simple filesharing" is enabled, i can access that folder without typing a user and password and configuring login security settings.
but in fact the network drive on which the snapshot folder will be located will do have a login and password. (when i go to it with windows explorer, i have to enter my login and password).
won't this cause problems for the replication? as i don't see where to enter net network login when i configure the replication via the wizard.
Thanks for help
I've got a table that contains a column of accumulating uptime datathat looks similar to this:239.13239.21239.30239.38239.46239.550.350.440.530.600.680.780.850.93I need to SUM the data up the point where the data gets reset (the nextrow is less than the preceeding row). Then I start the SUM again untildata gets reset.Thanks in advance for help!
View 20 Replies View RelatedI have an issue accumulating a value based on certain conditions:
I need to sum up the days column as you can see table below. The sum is based on the StartNewFlag.
When the flag is 1, I need to start a new sum from zero, else I need to accumulate to the previous rows.
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
[Code] ....
My Results must look like one of the following tables (either one would work, however the second one is preferred)
Result 1:
ItemID StartDate EndDate StartNewFlag Days TotalDays
1 2015-07-01 00:00:00.000 2015-07-05 00:00:00.000 1 4 5
1 2015-07-08 00:00:00.000 2015-07-09 00:00:00.000 0 1 5
1 2015-07-11 00:00:00.000 2015-07-15 00:00:00.000 1 4 11
1 2015-07-18 00:00:00.000 2015-07-21 00:00:00.000 0 3 11
1 2015-07-22 00:00:00.000 2015-07-26 00:00:00.000 0 4 11
OR
Result 2:
ItemID EndDate TotalDays
1 2015-07-09 00:00:00.000 5
1 2015-07-26 00:00:00.000 11
Hi,
I have a table called Daily.
It has 5 columns, "Testers ","Activity ", "GivenHours ","UsedHours " and " Delta ".
This table will be regularly updated every 12 hours.
Now I need to Accumulate the data from GivenHours and UsedHours with respect to the testers and insert the accumulated data Into a new table called "Weekly".
The accumulation should last only for 7 days and then it should be reset again to accumulate new data for the new week.
Weekly table should have 5 columns "Testers ","Activity ", "TotalGivenHours ", "TotalUsedHours " and " Percentage ".
Percentage is ( TotalUsedHours / TotalGivenHours ) *100.
Can anyone tell me how to do this?
I'm trying to create an accumulating field based on a set of records. I need to fill in daily amount balances that accumulates on a daily basis. But I can't seem to figure out how to create a total for the daily dates and have it add on additional amounts if needed.
Here's some sample data:
5 6 20 1 200.00 5/5/20000
5 6 20 1 -149.00 5/8/2000
5 6 20 1 100.00 5/10/2000
Now I already have a table with the dates created via a stored procedure. I have a set of dates from 5/5/2000 to 5/8/2000. So that results set should look like this:
5 6 20 1 200.00 5/5/2000
5 6 20 1 200.00 5/6/20000
5 6 20 1 200.00 5/7/2000
5 6 20 1 51.00 5/8/2000
5 6 20 1 51.00 5/9/2000
5 6 20 1 151.00 5/10/2000
....
I'm trying to creating a rolling sum that accumulates the amount field for each daily record and if a new amount is listed, then roll that amount into the total. If you have any suggestions about how to perform this rolling total via TSQL or SSIS, I would greatly appreciate it.
Thanks
Greg
Hi all,
I'm in a situation where I need to accumulate values from several columns in each of the rows returned in a result set and to then display each accumulation value for each row at the end of each row.
For example, if I had the following...
------------------------------------------------------
name - car - van - bus - total
------------------------------------------------------
Terry - 55 - 34 - 12 - 101
John - 01 - 23 - 05 - 029
etc etc
------------------------------------------------------
Where 'total' column is an accumuliation of the 'car', 'van', and 'bus' columns. The values for the columns 'car', 'van', and 'bus' are all values that have been derived from COUNT function. So what I want is to somehow genarate the values for the 'total' column (car + van + bus).
Now I have tried the following SQL, but to no avail
Code:
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(COUNT(pveh.name1) + SUM(COUNT(pveh.name2))
FROM planvehicles AS pveh
... and I also tried...
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(Car + Van)
FROM planvehicles AS pveh
Thanks
Tryst
I am trying to create a procedure which will calculate the total tuition
This process involves 3 tables.
Contract table has tuition information which is all $100 (set price).
Discount table has discount type and discount percentage (ex. 0.3) on each discount type.
ContractDiscount table have contract number and discount number to connect both tables
I think I need to create a loop since some contract gets more than one discount.
I have to calculate and get result nee to be like this
total_tuition = (tuition - discountPer * tuition) - this has to be a loop condition
Do you have any suggestion ?
Thanks
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message
"cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again."
After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message.
So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
View 2 Replies View Related
Ok,
I have a network folder called A
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
Thanks,
Stephen
Hi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
I have a set of folders with the following structure:
MainFolder
1999
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
2000
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
etc.
Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.
Thanks,
Sam
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this.
declare @inpath varchar(100)
SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath
but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder.
I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
Thanks,
What is a snapshot from replication point of view.
------------------------
I think, therefore I am - Rene Descartes
Dear friends
I am a new fellow to replication.What is the problem I am getting is I am not able to create snapShot.Whenever I start Snapshot Agent it is going for half an hour and it will stuck in the case of one view saying like that 'failed to process bulk copy of data from dbo.syncobj_03x666*'.If I am publishing tables only then also I am getting same error.This view created by system.In my case publiher only working as a distributor.I created a SQL username which have access in subscriber & Publisher.Preferably I will be happy if someone suggest what and all are the primary criterias we have to keep in mind while doing Replication
Thanks in Advance
Filson
I get the following error message in the job history for
synchronization between two SQL 7 machines across the
network.
The process could not read file 'acct.sch' due to OS error
1231.
Is there something to fix this? It has been running fine
for almost 2 years and suddenly quit. There is a firewall
on both sides, if that helps.
TIA,
Cami
The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
Create jobs to copy database and restore database in destination servers
------------
Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------
Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------
Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
Thanks
Robert
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....
Do you have any remedy for that? Thanks
Hi,
I've problem in replicating data thru SNAPSHOT as I've the tables with Foreign Keys at subscriber end. The Truncate table is not working because these tables
were referenced by a FK. Even for recreating table during snapshot is also same problem. Any suggestions?
Thanks
TT
2 questions:
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
Thanks so much for any and all help!
Hi,
I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.
Thanks
Sam
Hi,
I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.
Thanks
Sam
Hi everybody,
We have merge replication between two servers with Sql Server 2000 Service Pack 2, and the Merge agent display the follow message :
The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized.
But i have changes at the suscriber, How can I do to Merge the last changes if the merge agentes is stopped.
I appreciate any help.
thanks
Hi Guys,
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Please help.
Thanks,
Anu
Suppose i want to replicate data from server A to server B
I am using snaphot replication.I did the snapshot replication for the first time
and server B got a snapshot of server A.
Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?
We have a training server and I've had a request that after each training session, we have the ability to quickly roll the training server back to its previous state so that the next group of people can be trained with the same examples.
Doing a restore requires that the maintenance (defrag, warm cache, etc..) also runs after that before the server performs fast enough.
So I was thinking of snapshots as an alternative. When you roll back to a snapshot, does that invalidate what's in the cache or have any adverse effect on query plans, stats, or indexes?