Multiple Distribution Agents Running
Jun 23, 2007
hi !
I am using sql server 2005 sp1 standard edition.
I have a transactional replication set up from one server to another.
From last couple of days i am noticing two distribution agents running for the same subscription in the replication monitor. Before this only one distribution agent was running. Is there any problem having two distribution agents running in parallel for the same subscription on a server?
Any help would be appreciated.
Thanks
View 1 Replies
ADVERTISEMENT
Dec 5, 2006
Hi all replication experts...
I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.
There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?
Grateful for answers
Best,
/M
View 3 Replies
View Related
Jul 23, 2002
Hi
By default, the Log Reader Agent and Distribution Agent use the account the starts SQL Server Agent to logon into the Publisher SQL Server when performing SQL Server Transactional replication. Is there a way of configuring them so they use a different account.
Thanks
Tariq
View 1 Replies
View Related
Jun 23, 2008
We have Lots of databases wich typically have three Trasactional Publications and two subscribers. Some DB's use a shared distribution Agent for all three Pubs whilst some DB's use Independent Agents. The reason for this set up seems to be purely by chance.
BOL says "Sharing the same Distribution Agent reduces the resource demands and increases performance at the Distributor". Whilst some resources on the net say "IF you use independant Agents, you can get performance improvements while replicating your data". This seems to be conflicting information.
I know I should go with BOL but having seperate distribution streams does make more sense (although I now have three jobs per subsciber instead of one using this method). Also more importantly, if one publications fails with dodgy data then this does not affect the other Publications. What do other people think?
Thanks in advance
View 1 Replies
View Related
Sep 18, 2006
Ideally I am looking for a procedure that I can call in T-SQL to verify that the Log Reader, Snapshot, and Sql Agents are all up and running on a given server. I found the undocumented sp_MSrepl_distributionagentstatussummary proc and got it to work for the distribution agent. I also found sp_MSrepl_agentstatusummary, but I've yet to get this to work. Overall, I'd really like to have a better way to do this.
This absolutely must be something callable from the sqlcmd interface. It can not be through a UI nor anthing more complicated than standard T-SQL.
Any suggestions?
View 6 Replies
View Related
Feb 24, 2003
Can I have multiple SQL Server Agents running concurrently?
I would like to have 10 instances of database running on my local SQL Server - each of these are Merge Replication to a unique database located offsite.
Do they all run through one SQL Server Agent?
If they do would this be a slow process as, I think, the SQL Server Agent queues the replications pending.
Thanks,
Bianca
View 1 Replies
View Related
Aug 21, 2007
Hi all
I have SQL 2000 SP4 running on a W2K server SP4.
The server acts as a subcriber and publisher and acts as its own distributor.
Each publication uses its own distribution agent and is set to loop.
There are 4 queued updating subscriptions on this box.
When running 3rd party tools to determine open active ports, the replication executables (distrib and logreader) are connecting to local server multiple times. Easily 15+ established connections plus many more waits.
Everything connects to 1433 then opens a new port.
Is this because of the queued updating?
Replication is functioning as it should without any problems.
I even checked our 2 main distribution servers which only act as distributors and there is not one connection from replication to itself.
Any insight would be appreciated
Thx
John
View 3 Replies
View Related
Mar 10, 2007
I have a very strange error (included at the end of message) and it seems to indicate that I have not installed the SQL Server correctly - perhaps the wrong version. I have re-run the configuration a couple of times with no effect. I have not removed the server and re-installed yet. I have Here is my version info:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not start the wizard.
------------------------------
ADDITIONAL INFORMATION:
Could not load file or assembly 'ConfigureDistWizard, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (mscorlib)
View 7 Replies
View Related
Jul 6, 2007
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
Any suggestion as to what the problem could be?
Thanks,
Amy
View 3 Replies
View Related
Sep 9, 2005
I have many, many T-SQL scripts that create stored procedures and gives proper permissions. (uploading to production)I've been looking around for a way to run them all at once with a single script to pull them into the sql analyzer.Not copying and pasting them all, there are too many, but reading and executing.Anyone know a link with a sample to do this?Thanks,Zath
View 3 Replies
View Related
Jun 20, 2002
We have a DTS package that is submitted every minute by a SQL Agent job (2000). We see instances where before the first job is completed, the second one starts running.
Has anyone encountered this type of behavior before?
View 3 Replies
View Related
Mar 2, 2005
Hi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuart
View 5 Replies
View Related
Feb 18, 2004
I've got the stored procedure which first creates and then starts a job.
This stored procedure can be invoked from a number of triggers (after insert, after update)
The problem is:
when a sequence of statements is being executed one by one (like an insert immediately followed by several updates) and the stored procedure is invoked from the propper insert or update trigger once for each statement, the jobs are created in the right order (first for insert statement, then for updates in the order of the initial statements), but are executed in the wrong order.
Is there any explanation to this? And any solution?
The order of such jobs execution is vital for my application.
Thanx a lot in advance.
Please see the enclosed screenshot for an example list of jobs. Name of the job contains time of it's creation and another column shows the time the job was executed.
View 7 Replies
View Related
Feb 29, 2008
Hi,
I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.
Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.
I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.
I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?
I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.
Thanks very much,
mike123
View 2 Replies
View Related
Jan 8, 2008
I have several SQL files that make several different table updates and data changes.
How would I create something (maybe an SSIS package?) or even write a T-SQL script that runs through these files and executes the queries contained in them?
(I put this thread in SQL General also because I wasnt sure if this could be implemented in T-SQL or not.)
Right now what I have is a ForEachLoop Container with an ExecuteSQL task component inside of it. I'm thinking that the contents of each of the sql files would be copied over to a "Temp.sql" file and then executed in the loop. So I've created a Temp.sql file for this purpose and the properties of the ExecuteSQLTask component are setup as so:
(I've created my connection managers appropriately with an OLEDB source and a File Connection pointing to Temp.SQL)
ConnectionType OLEDB
Connection servertest
SQLSourceType File connection
FileConnection Temp.sql
And for my ForEachLoop container, my properties have been set as:
Collection
--> Enumerator Foreach File Enumerator
--- Enumerator Configurations ---
Folder: C:lahblahblah
Files: *.sql
with retrieve file name set as Fully Qualified
Have I set this up properly? (obviously not, because it's not working )
View 7 Replies
View Related
Oct 9, 2007
Hi Techs,
I have created several DTS Packages those are working fine, tested and verified.
I need to run them by a single click, either by batchfile or some sort of command.
Thanks
Iqbal Ansari
View 4 Replies
View Related
Jun 25, 2007
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…
View 7 Replies
View Related
Jan 26, 2006
I have several sequence containers in one package that fire off execute package tasks. I would like each of the sequence containers to start at the same time when the job starts running. However when I set them up to do that, i get an error that the variable cannot be read because it is locked. I have the variables setup as readonly so not sure why they are being locked. When I run the package and have each sequence container fire off after the previous one ends it runs fine.
Any ideas on how to get around this?
Thanks!
View 1 Replies
View Related
Jun 12, 2015
I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
SELECT
--DISTINCT
p.SPID,
p.LogiName,
p.HostName,
[Code] ....
View 8 Replies
View Related
Feb 13, 2006
In my application code I am trying to invoke multiple threads in which each thread is loading an instance of the same SSIS package and would initialize the package variables with different values and execute the different instances in parallel. In each thread - after the package execution has completed successfully - I read that instance's SSIS package variables to get result information from that Instance run.
When I load the same package in different thread using LoadFromSqlServer() method
- does the code create multiple instances of the SSIS package and load the distinct instances in each of the thread
- Will the Package Execution ID be different for the different instances?
- Are the package level variables instance safe?
View 2 Replies
View Related
Jul 12, 2007
During customer engagement, I was told that there are Microsoft add-on to be installed on SQL 2005 and the SQL 2005 can be simulated as SQL 2000, SQL 2000 SP4, SQL 7.0 in different SQL instances.
Is this true? Are there URLs about this topic?
Thanks a lot.
Po-Shan.
View 7 Replies
View Related
Sep 14, 1999
I am using MS SQL 6.5 SP5, NT4.0. running Tivoli Service Desk. Two of the four agents on the Applications server were not running so I recycled the server and now NONE of the agents are coming up. Generally the agents show up on the application server control panel but they have not started. The status of the server is 'initializing' and the Watchdog has started but only the 'dispatcher'started. I have hard booted the aps server and recycled the aps and db servers both. I know that there is a way to suspend the agents but that has not been done and the 'resume' button is not available to use. I tried restarting the dispatcher but it didn't do anything. I have also gone into 'config' on the control panel and it shows that '4' agents are set to start up.
We run a Web version also so I have my help desk analysts using it but they cannot refer back to tickets that they have just opened, etc.
ANY SUGGESTIONS - I have been waiting on Tivoli to call me back for almost an hour and a half.
Thanks - Mary
812-485-7876
mkeown@dc-is.org
View 1 Replies
View Related
Oct 8, 2007
I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your help
View 2 Replies
View Related
Sep 14, 2006
Which replication agents are available in Express edition?
abdul
View 1 Replies
View Related
May 22, 2006
Is it possible to obtain the status, running or not running, of the distribution agents that appear under Replication MonitorAgentsDistribution Agents in Enterprise Manager using WMI? If yes, can you please tell me the WMI namespace and objects that can be used?
Thanks
View 1 Replies
View Related
Feb 21, 2008
I have a form that contains data that some of which needs to be inserted into one table and the rest updated to another table. I want this to happen on a single button click. Is this possible?Here's what I have so far: String id = parm_tb_tktid.Text; String update = parm_tb_desc.Text; String status = parm_cb_status.Text; SqlCommand cmd = new SqlCommand("Insert into History (tkt_id, hist_desc) values(@parm_tb_tktid, @parm_tb_desc)", HelpdeskConnectionString); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@parm_tb_tktid", id); cmd.Parameters.AddWithValue("@parm_tb_desc", update); SqlCommand cmd1 = new SqlCommand("Update Ticket (tkt_status) values(@parm_cb_status) where tkt_id = @parm_tb_tktid)", HelpdeskConnectionString); cmd1.CommandType = CommandType.Text; cmd1.Parameters.AddWithValue("@parm_cb_status", status); HelpdeskConnectionString.Open(); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); HelpdeskConnectionString.Close(); parm_tb_tktid.Text = String.Empty; parm_tb_desc.Text = String.Empty; parm_cb_status.Text = String.Empty;I know it isn't right, but I can't find any info on why it's wrong and how to code it properly.
View 9 Replies
View Related
Aug 23, 2005
Hi, I have looked and loked for the answer to this question and made no progress. I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas). Is it alright to create another database and have 2 running simultaneous on the same data engine? Is there any type of limitations that I sould know about - such as number of allowed accesses at one time? Any help is greatly appreicated.
View 1 Replies
View Related
May 14, 2014
I have the following:
(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.
(b) Three SQL Indexed View queries that take 0 ms when run together.
When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.
What is causing the extra 363 ms of latency.
View 9 Replies
View Related
May 9, 2006
I have a website running under Windows Server 2003 which I am trying to deploy Reportin Services to using a custom security extension using Forms authentication. There is also another website which is just a Report Server running on this IIS 6.
On my development machine, XP with IIS 5.1, the Website with Reporting Services using forms authentication works fine, shares cookies for authentication and all that jazz. but trying to configure a second instance of Reporting Services on Server 2003 is not behaving.
Is there some trickery or details involved with running two instances of Reporting Services?
Also in the Configure Report Server tool it is failing and so not initializing on the last step of the Database Setup
Setting Connection Info for Reporting Server
ReportServicesConfigUI.WMIProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)
--- End of inner exception stack trace ---
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
View 1 Replies
View Related
Sep 19, 2007
I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).
The problem is that somehow these two applications are sharing something either with the connection or commands.
For instance if the two instances are in the following function at the same time then they both thow an SEH exception:
Code Snippet
inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {
struct _Recordset * _result = 0;
HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _RecordsetPtr(_result, false);
}
The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."
And afterwords when they go to release the command an error occurs on
First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.
Code Snippet
inline void _bstr_t::Data_t::_Free() throw()
{
if (m_wstr != NULL) {
::SysFreeString(m_wstr);
}
if (m_str != NULL) {
delete [] m_str;
}
}
This is being called from.
Code Snippet
inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {
HRESULT _hr = putref_ActiveConnection(ppvObject);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
}
The exception that occurs: First-chance exception at ...: 0xC0000005: Access violation reading location 0xcccccccc.
Similarly when one instance releases a command using
Code Snippet
inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {
HRESULT _hr = putref_ActiveConnection(ppvObject);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
}
The second instance of the application fails when running the command at the exact same time.
Code Snippet
inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {
struct _Recordset * _result = 0;
HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _RecordsetPtr(_result, false);
}
The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.
View 2 Replies
View Related
Mar 26, 2008
Is it possible to run through a buffer multiple times in an asynchronous script?
Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?
Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".
I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.
I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)
View 5 Replies
View Related
May 13, 2008
Hello all -
Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.
We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...
thanks for all the fantastic support on this site
View 6 Replies
View Related
