I have Snapshot set up as a schedule task between server A and server B.
If I make changes to the db schema on server A - the changes are not replicated to server B.
Is this normal?
Do I have to restart the Sanapshot agent each time there is a schema change?
Thanks,
Bianca
Hi to all,
I have a merge (pull) replication between SQL Server 2005 and SQL Server Express clients.
Data synchronisation is ok, and I already made some schema changes (like adding new columns) at the publisher database which were applied to the subscriber as they should.
Now this doesn't work anymore. New columns added at the publisher (with ALTER TABLE or Management Studio) are no longer replicated to the subscribers.
I don't get any error messages, and I can't find any hints in the logs why this has stopped working.
The "Replicate_Schema_Changes" is still set to TRUE (in general, none of the publication options had been changed).
I tried sp_enumeratependingschemachanges to find out any "bad changes" put the sp returns nothing.
I remember that the last thing I did was adding a default constraint, which was replicated successfully to the subscribers.
Can anybody help?
Michael
Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
I've got a very simple package that truncates some rows from a table. The packages utilizes configurations as described in a post I made on this forum yesterday http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3062343&SiteID=1.
When the package is deployed and installed on the prod server, it runs successfully from the Execute Package Utility, utilizing the configurations as expected, removing the rows from the target table.
When the package is run from the SQL Agent on prod, it completes successfully without errors, but the rows in the prod table remain. On the "Data Source" tab in the job, the correct connection string shows pointing at the prod database (picked up from a config file on the prod server).
Curiously, the job appears to run the truncate against the test db table - I tested this by putting rows in the target tables on both test and prod. When the package is run from SQL Agent, the test rows are removed. When run from the Execute Package Utility on prod, the prod rows are removed. Huh?
Again, the Data Source tab in the Job on prod shows the prod db connection string and the package removes the rows correctly from the prod table when run from the Execute Package Utility on prod.
Does anyone have any thoughts on this behavior when using the Job Agent to execute a package?
Thanks!
Jamie Pick
I am using SQL 2005. I have created a SSIS package that basically executes another SSIS package (as part of a larger package) . It runs fine in SSBIDS but will not run if I save it and schedule it using SQL Agent. I should mention I am using a domain/admin account with SQL Agent, so I don't think that is the problem.
When I execute the job in SSBIDS, the Execute Package Utility window pops up, at which point I click on the Execute button, the job runs successfully and then I click on the close button.
I suspect it is not running via SQL Agent because of the user intervention required to complete the task (i.e. clicking on execute as described above). Is this correct? If so, is there a way to override the requirement for any user intervention. Or, could it be from something else?
Thanks for any insight.
A strange thing is happening to us: we create a bat file that executes an SSIS package with multiple connection managers and tasks.
When calling the bat file from command prompt €“ the package runs just fine!
When calling the same bat file from SQL Server Agent (that runs under the same NT account as SSIS, and all other SQL services, and belongs to local Adminstrators), the package fails half way through with connection failed issue.
Plus it gives us something like this: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
Has anyone ever experienced this issue, do you have any possible solutions?
Thank you!
Anastasia
I have a problem where I have an SSIS package (SQL Server 2005) that won't run properly from SQL Server Agent, but it runs fine when kicked off manually from Integration Services -> Run Package or when run in debug from Visual Studio.
The first step in the package checks for the existance of a file via a script task. The script looks like this...
Code Block
Public Sub Main()
Dim TaskResult As Integer
Dim ImportFile As String = CStr(Dts.Variables("BaseDirectory").Value) + CStr(Dts.Variables("ImportDirectory").Value) + CStr(Dts.Variables("ImportFile").Value)
If Dir(ImportFile) = "" Then
Dts.TaskResult = Dts.Results.Failure
Else
Dts.TaskResult = Dts.Results.Success
End If
Return
End Sub
This script runs fine and the file is seen as expected when I run the package manually. But as a step in a SQL Server Agent job, it doesn't see the file.
The SQL Server Agent service is set to start up / log on as a Local System Account. I've also tried setting up a credential / proxy (using an account that I know can see and even move / rename the file) to run the job as but that didn't seem to help.
The package is being run from SQL Server (stored in MSDB) and is set to rely on SQL Server for sensitive information, so I don't think that's an issue; other packages are set up like this in terms of sensitive data and run fine.
Any ideas why my script can't "see" the file I'm looking at when it's kicked off by SQL Server agent? I've looked and looked...I can't seem to figure this out. I would really appreciate any help you might be able to offer up.
All:
As the subject suggests I am encountering an error while running a package through an agent. Unfortunately the error does not provide much information for me to diagnose the problem, and hence the post.
I have pasted the error below and appreciate help from anyone.
Thank you,
Message
Executed as user: EPSILONSYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:16:31 AM Error: 2007-08-02 10:16:32.25 Code: 0xC002F304 Source: File System Task File System Task Description: An error occurred with the following error message: "Could not find a part of the path 'P:FinanceItems Sold Below CostItems Sold Below Cost_2007-08-01.csv'.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:16:31 AM Finished: 10:16:32 AM Elapsed: 1.047 seconds. The package execution failed. The step failed.
I have an hourly t-log backup job.
It's scheduled, but each night for some reason the schedule changes from "Enabled" to "Disabled".
I'm pretty sure it's not another person doing this.
Anyone seen this behavior. SQL 7, SP2
(FYI, I've dropped the job and re-created it. I'll let you know if this fixes my problem).
Dano
Hello friends!
I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????
T.I.A
I plan to set up a standby server. It will require the same schedule jobs with the production server.
Is there any way I can write a stored procedure to script schedule jobs to files?
Thank you in advance
Hi everyone,
I have a log table that track my SSIS scheduled jobs. This SQL Agent job runs every 15 minutes for now. The problem is, when I check the status(log) table, I see it fails and succeed at random. I have checked the history and I can't figure out why it is doing this.
Has anyone any idea what is going on? At least for the fact that the package succeeds after it failed shows that nothing is wrong with it. Again when I query my detination table, it is loading data as expected when it runs succeessfully.
Please any idea?
PkgID Pkg_Name Start_Time End_Time Run_Duration Success_Flag
81 Dw_DataImport 35:45.0 37:40.4 0:01:55 Y
82 Dw_DataImport 49:31.0 51:53.5 0:02:22 Y
83 Dw_DataImport 34:26.0 NULL NULL N
84 Dw_DataImport 39:05.0 NULL NULL N
85 Dw_DataImport 45:17.0 48:06.5 0:02:49 Y
Thanks
Omon
Guys,
Can i schedule a SSIS package by not using SQL AGENT?
Do we have any other options to schedule teh package?
I have worked on other etl tools which have inbuilt options.
please let me know.
Thanks
I need to run SISS for example every day at 5pm, I run SQL Agent and created new job for my SISS package which store in SISS Package Store on SQL Server, but when this job run I go error:
Log Job History (Test SISS)
Step ID 1
Server ADMNBK-010S
Job Name Test SISS
Step Name Run SISS Mobius
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: ADMNBK-010SSYSTEM. The package execution failed. The step failed.
if I run SISS package from SISS Package Store "by hands" it works...
I added couple of new columns to the distribution database and those new columns are not being replicated.
View 3 Replies View RelatedUsing SQL 2005 SP2. I have a publication that contains indexed views, and some other objects that query the indexed view using WITH (NOEXPAND). Currently replication fails because the CLUSTERED INDEX on the view is NOT replicated. I've experimented with various schema options but nothing changes. The view is replicated but not the clustered index on that view. I've seen some discussion on replicating indexed views to a table, but I would like to replicate indexed view schema fully. (Including the clustered index on that view). Is there a way to make this work?
View 4 Replies View RelatedI have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out. Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily. Currently this is all on the same machine. I have not moved it beyond where I am developing.
On the command line I am using the following command...
dtexec /F "S:connectionscontacts.dtsConfig" /DE "password"
Below is the output log...
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 6:59:40 PM
Progress: 2006-08-16 18:59:41.29
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.29
Source: Data Flow Task
Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.71
Source: Data Flow Task
Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.73
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.81
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.84
Source: Data Flow Task
Pre-Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.90
Source: Data Flow Task
Pre-Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.90
Source: Data Flow Task
Pre-Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.93
Source: Data Flow Task
Cleanup: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.93
Source: Data Flow Task
Cleanup: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.93
Source: Data Flow Task
Cleanup: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.95
Source: Send Mail Task
The SendMail task is initiated.: 0% complete
End Progress
Progress: 2006-08-16 18:59:42.09
Source: Send Mail Task
The SendMail task is completed.: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 6:59:40 PM
Finished: 6:59:42 PM
Elapsed: 1.984 seconds
When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...
Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig". End Info Warning: 2006-08-16 18:40:03.15 Code: 0x80012012 Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-08-16 18:40:03.15 Code: 0x80012059 Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Info: 2006-08-16 18:40:03.20 ... Process Exit Code 1. The step failed.
Thanks in advance for any help!!!
Hi All,
I have assigned a user permissions to be a db_owner on the msdb database and the user is also part of the following msdb roles:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
But I notice that the user can log into the system, but they cannot change the schedule for a job, they can only view it.
I was wondering if there was a way around this without giving the user Sysadmin rights ?
I look forward to hearing from you.
Thanks
I want to schedule a database backup using SQL Server Agent Jobs how can i do this?
View 1 Replies View RelatedI'm a novice with replication and am in need of direction on how to restore replicated databases. Server A replicates 5 databases to server B using transactional replication. Server A also contains the distribution database. I'm perfoming PUSH replication.
The development area asked me to restore Server A with a copy of production data located on server C. After I restored the data from C to A, I noticed that server A was no longer published. Is this normal?
I re-published the 5 databases, but noticed that the Log Reader Agent has now been running for 2 hours. I'm trying to give myself a crash course in replication, but am coming up empty regarding (1) the prefered method for restoring replicated databases and (2) determining why the Log Reader Agent is taking so long.
Any suggestions?
Thanks, Dave
For SQL 2005 transactional replication I have 1400 articles (tables, views, functions, sp) that are all replicated.
Will SQL 2005 allow me to drop a table from from the publication and drop it from the publication database that is referenced by exising views or sps that are replicated or does it somehow check that?
Linda
I have 1 sql server 2005 database in Server A that is publishing a table to another database in Server B using the transactional method.
It is possible for me to delete all the rows in the database located at the publisher at the same time the subscriber pulls them?
Thanks.
I tried to run this(below) the table is replicated(transactional).
EXECUTE
sp_rename N'dbo.Tablename.Columnname, N'New_Columnname', 'COLUMN'But getting this error message:
Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227
Cannot rename the table because it is published for replication.
I'm trying to run a stored procedure from a SQL Agent job.
I tried "EXEC myProc", but whatever SQL user the agent is running as doesn't have the same default schema I do. So I tried "EXEC mySchema.myProc". This works -- except myProc calls other procedures, which call other procedures and functions, and I don't want to have to edit them all to fully-qualify the proc names.
Questions:
can I just change the current schema of the job from within the script, with something like "SET SCHEMA = 'mySchema'; EXEC myProc"?
would this be happening if all the procs (and tables?) belonged to dbo instead of mySchema?
should I move everything into dbo?
Thanks!
We have a master database (SQL 2014 Std) from which data are imported from XML files (send by en ERP system) using SSIS. Â There is about 12 other servers (SQL 2014 Express) located in remote warehouses. People will uses PocketPC to scan barcode of products in the warehouses and all operations must be forwarded to the master DB to be exported in a XML file for the ERP system.Now, each warehouses are independant. How can I setup the replication so only data belonging to a specific warehouse is replicated to its corresponding DB? I thought about creating views, one for each warehouses, and setup a replication for each warehouse, so there would be 12 merge replications configured. Is it fine?
View 2 Replies View RelatedWe just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
View 4 Replies View RelatedWe had an issue recently where a (transactional) replicated table was replicating data as expected.
Then about 30 or so rows in the source table were not in the destination table, but other rows created after those 30 rows were replicated.
We have pretty much confirmed that users did not delete those rows.
Unfortunately we had to resolve the issue quickly and so blew away & recreated the subscription so a lot of evidence is probably gone from the crime scene.
We cant figure out what could cause 30 rows not to be replicated, yet leave replication operational.
Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....
View 0 Replies View RelatedI'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies View RelatedHello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View RelatedI've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7
epldataunc
eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.
My questions are:
1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.
2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.
3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.
I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.
Regards.
I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.
Hi,
Is there any method that I can use to backup the whole database to another database without using replication? I have to backup it every half year. Can I do something like setting schedule to run to "Database Backup" module in sql server?
Thanks,
Matt