Recently the Agent on our production server has exhibited a condition where the agent history is missing on around 80% of our jobs. The jobs still run but no history, we have cycled the services, rebooted etc but nothing rememdies the issue.
Has anyone else ever ecountered this with SS and if so do you have a solution?
Hi Everyone,
I don't know why I cannot see any job hisotry after schedule job complete. any idea to fix it? thx
I have changed the following setting in SQL Server 2000:Right click SQL Server Agent and go to properties/Job System anddeselected limit size of job history log. I want to keep all jobhistories in msdb indefinitely.Is there a way to leave this setting on, but configure how much historyshows when you right click an individual job and go to its job history?In other words, I want to keep all of the job history in msdb, butonly want maybe the last 10 or so to be displayed under right clickjob/job history.Thanks.
View 1 Replies View RelatedI have been using SSIS for about two months now and one of the main difficulties I have had is migrating Sql Agent SSIS jobs around environments.
Usually, the SSIS fails because of permissions to file systems, data bases, etc, but until now, I have had to discover these almost by guessing because the job history never game me any useful information. Also, from what I see, the SSIS logs have been unreliable, at best.
What I have done lately is creating Operating System (CmdExec) Steps as part of the job (not SSIS steps) and inserting the output of dtexec.exe into the history.
This seems to give me information that has been really hard to get, otherwise.
My question is (I am no DBA or ETL expert), is this the best way of getting the output of the package when debugging migration issues? Why doesn't the SSIS Steps have a similar feature of outputting useful info to the job history?
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
I have a SQL 2000 sp3 installation on a Active - Active
cluster. I wanted to create a performance alert, but when
I tried, the Sql Server Performance Condition Alert choice
is missing from the drop down menu on the new alert setup screen. Does anyone know why? Any help will be greatly
appreciated.:confused:
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
My backup jobs have been failing and when I go in to services there is no SQLSERVERAGENT listed.
Anyone have any ideas on this one?
Hi,
I'm learning SQL 2005 and installed the express ADV edition to my pc. But I miss the Agent service.
Is this included in express? Has it another name after SQL server 2000?
thanks
majid
Hi all, This is my first message in this forum and I am a new bie to SQL server 2005.. I am trying to schedule the backup job for a database in 2005 and I am not able to figure out where it is. I browsed through few topics in the forums and found that I can do using sql server agent in the SQL server management studio. But I do not see the sql server agent in the management studio. Any help is appreciated.
Thanks all!
I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?
I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?
I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?
Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).
OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.
Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?
I guess that will be me. Is this what happens for being loyal to MS products?
Please - give back the SQL Agent and Replication publication.
Joe
My Snapshot Agent for SQL Server 2005 sometimes does not fully complete. I am expecting around 2073 files in the snapshot folder but only get 492 (this number does change). I have immediate_sync set to true, so I should get a full snapshot every time. It seems to quit after all data has been BCP'ed out and before the schema generation starts. I do get the "The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. " message in the MSSnapshot_history table.
I am guessing that there is some sort of blocking that is preventing the snapshot agent from continuing and then it times out and dies. But that is a total guess as I have not been able to observe the behavior, only the outcome.
Any suggestions to what the cause is or how I can fix it?
Thanks, Amy
I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.
I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?
what conditions a job step my be showing in the job history as both running AND completed successfully?
Hi All,
I have a requirement of logging the failure and the error of a job executing a SSIS package to text file.
However, when I go to job step -> Advanced, in the section "SQL Server
Integration Services Package" where that info to setup logging is, it
is blank and just says:
"There are no options for this command type"
Any ideas?
I thought that this might not be supported in Standard Edition as I am running standard edition. Then I checked on two other servers, where I have Enterprise edition with SP2. On one of the installations, I am able to give the log file, the other one still shows,
"There are no options for this command type"
What is the problem? Both of them I am using the sysadmin role account. I have proxy account created on both the SQL instances. I don't think this is the problem, but still I checked this.
Can anybody help me?
Regards,
Virendra
I have an application which is providing printing facility using a custom hardware interface device.
Whenever a print is required user punches in his ID number into the hardware device, and hence an underlying application gets the event and it calls a managed DLL, which inturn calls the Reporting services webservice on the same server. Problem starts after some days (typically after 2 -3 days), when the server response becomes slow and the aspnet_wp.exe restarts every now and then and after this sequence of happening, finally it crashes with some event in application in event log which reads:
The server was unable to allocate from the system nonpaged
pool because the pool was empty.
Also many times the applications log file reads :
There is an error in XML document (1, 948).
I think this error starts when the aspnet_wp.exe is restarting, but I am not sure.
The server operating system is Windows 2000 SP-4, MS SQL Server 2000 Standard Edition SP-4, MS Reporting Services 2000 Standard SP-2 with .Net Framework v1.1
The Server is running many applications apart from the ones listed above and there is a moderate usage of TCP/IP sockets for communicating with certain hardware devices.
Please help me out!
I have only intermittent success with debugging CLR stored procs. Sometimes it works, and sometimes it does not.
When debugging does not start, the output ends at the line:
Auto-attach to process '[516] sqlservr.exe' on machine 'dcnr-ait669sell' succeeded.
The visual studio still shows running, but it never gets to a breakpoint and never shows another line on the output. Processor usage is zero.
To start debugging, I have to restart SQL Server, restart the Visual Studio, and (probably) restart the machine. Essentially, I just mess with things until it starts. I have seen it start after a long while when I just let it sit, but I have let it sit for several hours without starting.
The Visual Studio will not respond to the Debug > Stop Debugging menu item. It eventually shows a €œStop Now€? box that says that it will disappear when debugging stops, but it never stops.
I am using SQL Server on my local machine for debugging.
When it works, everything is OK.
Any suggestions will be appreciated.
Hi SSIS Guru's,
I am facing a very strange problem.
We have 2 Physical Servers (A and B) on which we have installed the SQL Server, one primary (A) and other as secondary (B). And there is a cluster (C) available to acces the running server. I have created some SSIS packages which we installed on the Server A (Primary), and created the job on the cluster server which initiates the SSIS packages, whcih are installed in the File System.
The problem i am facing is the some thing related to Connection time out. And interestingly i am not getting this error Always. Approxiamtely For Every 5 Times once it;s Failing. I am copying the errors Which i encountered in the different runs.
The thing i am confused is why i am not geting the error all the time? And Why am i getting this error all the time in a different data flow task. My SSIS Package structure is I have created one master package and 6 Child packages. I am getting the connection string for the Data base from the Configuration file which is defined in the XML File.
The connection string that i am using is
Data Source=<<server name>>;User ID=DOMAINuser;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Integrated Security=SSPI;
*************************************************************************************************************************************
RUN 1 - Error
Executed as user: AMRsys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights
reserved. Started: 2:00:07 PM Error: 2007-09-15 14:02:35.92 Code: 0xC0202009 Source: ssis_emp Connection manager
"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An
OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete
login process due to delay in opening server connection". End Error Error: 2007-09-15 14:02:35.92 Code: 0xC020801C
Source: infr_char Get the Records from emp 1 [72] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on
why the AcquireConnection method... The package execution fa... The step failed.
*************************************************************************************************************************************
*************************************************************************************************************************************
RUN 2 - Error
Message
Executed as user: AMRsys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights
reserved. Started: 9:15:01 AM Error: 2007-09-15 09:17:01.64 Code: 0xC0202009 Source: ssis_emp Connection manager
"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An
OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete
login process due to delay in opening server connection". End Error Error: 2007-09-15 09:17:01.64 Code: 0xC020801C
Source: Data Flow Task Get the Records from emp [473] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on
why the AcquireConnection me... The package execution fa... The step failed.
*************************************************************************************************************************************
*************************************************************************************************************************************
Run -3 Error
Message
Executed as user: AMRsys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights
reserved. Started: 11:30:01 PM Error: 2007-09-14 23:32:21.28 Code: 0xC0202009 Source: ssis_dept Connection
manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable
to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:32:21.28 Code:
0xC020801C Source: Data Flow Task Get the Records from dept [632] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on
why the AcquireConnection method ... The package execution fa... The step failed.
*************************************************************************************************************************************
*************************************************************************************************************************************
Run - 4 Error
Message
Executed as user: AMRsys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights
reserved. Started: 11:00:02 PM Error: 2007-09-14 23:02:21.46 Code: 0xC0202009 Source: ssis_emp Connection
manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable
to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:02:21.46 Code:
0xC020801C Source: infr_itm_char_val Get the Records from emp_master [1] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on
why the AcquireCon... The package execution fa... The step failed.
*************************************************************************************************************************************
*************************************************************************************************************************************
Run -5 Error
Message
Executed as user: AMRsys_calyp. ...Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 9:10:59 PM Error: 2007-09-14 21:12:23.25 Code: 0xC0202009 Source:
ssis_salgrade Connection manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2007-09-14
21:12:23.25 Code: 0xC020801C Source: Data Flow Task - ssis_salgrade get salgrade [3227] Description: SSIS Error
Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on
why the AcquireConnection method c. The step failed.
*************************************************************************************************************************************
Hello all,
I'm running into a curious situation here and I was wondering if anyone had some ideas on directions to investigate.
To set up our situation, we are running SQL Server 2005 Standard edition on Windows 2003 Enterprise Edition for 64bit processor. In Dev, winver says V5.2 R2 and we're at SP1. I am told, but cannot verify that the rest of our environments (test, load, stage, prod) are patched to the same level. I do know they are all 64 bit processors and running the 64bit OS. The database servers are all at
Product version 9.00.3027.00
Product level SP2
edition Standard Edition (64-bit)
Our SSIS packages are deployed and stored in SQL server using dtsinstall. I do not know if the DBAs check the "Validate packages after installation." but I will assume they do not. Packages are invoked using dtexec in a manner like "D:Program FilesMicrosoft SQL Server (x86)90DTSBinndtexec.exe" /sq MyPackage /ser MyServer
All of this has been working fine but we've now had two issues where our packages fail to validate. The first was a deployment to production. The same build had been applied to identical environments and processed fine there. However, one package of the 12 deployed to production failed to validate as the built-in Audit dataflow component decided the length for the audit name is not valid for the specified lineage item. We attributed that to a fluke run as we could not reproduce the issue.
Yesterday a different package ran in our test environment. It failed out due to data issues but the important thing is the package actually passed validation and failed at run-time due to bad data. The testers corrected the data issues and this morning when the package (no code changes made to it) was reran it failed to validate with
'The metadata for "[dbo].[MY_TABLE]" cannot be retrieved. Make sure the object name is correct and the object exists.'
* The person invoking the package is a DBA so permissions shouldn't be an issue.
* The code has not changed between yesterday and today.
* The package's configurations in SQL Server have not changed
* The table does exist
To summarize, it validated yesterday and today it fails to validate with no changes to the code. I've asked the DBAs to reprocess the job and see whether the package blows up but in the meanwhile, any thoughts on things to look into? I'm less familar with using SQL Server as the package repository as my last place we used the file system exclusively so that and the possibility of 64bit quirkiness are my current guesses but they are just guesses.
Thank you in advance for your thoughts.
I have developed a stored procedure that filters a view that is a union of several different tables. This provides status information for items across our warehouse management system. This system seems to work very well and normally processes results very quickly (< 3 seconds). However, occasionally (every few days) we begin to see timeouts on the query after 3 minutes of processing. I can watch this process in SQL Profiler and see that the query is timing out after 180 seconds, which is the timeout we have for the query within the DAL. When I copy the line from the SQL Profiler and execute it directly in SSMS, the query executes in less than 2 seconds. I first thought that somehow this had to do with execution plans, but when I try to reload the page again, which executes the query, it still times out. I did add a OPTION(KEEPFIXED PLAN) to the sproc, and that seemed to speed things up for the time, but I am not sure if this is even the problem and what the optimal solution would be. Any thoughts spring to mind?
Thanks, Steve
Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:
Descriptions table:
ID | Title
Doc1 | Document 1
Doc2 | Document 2
Documents table:
ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189
View:
ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)
So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.
In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.
It's running on SQL Server 2005 with 2GB of RAM.
Any suggestions on tracking down the reason for the slowness would be great.
Thanks in advance!!!
-Dan
SQL Server is cutting off client connections intermittently and I'm having a hard time figuring out why. Restarting the SQL server service fixes the issue for a few days but then it happens again. It's SQL Server 2005 on Windows SBS 2003 with XP and Win2000 clients connecting. Clients connect via a DSN file--they run an Access database. I run daily backups and all maintenance tasks on the databases and these happen without any errors.
Here are some of the error messages I get from the SQL Log:
Login failed for user 'NT AUTHORITYNETWORK SERVICE'. [CLIENT: 192.168.1.16]
Error: 18056, Severity: 20, State: 23.
The client was unable to reuse a session with SPID 72, which had been reset for conection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x862. Administrator should deregister this SPN manually to avoid client authentication errors.
And a recurring error in the SQL Agent Log:
[165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
any clues into how i can further troubleshoot would be appreciated. I can't reproduce the error on command but it is bound to happen "randomly" during business hours and i really need it to stop. any insight appreciated.
thanks,
k
I have a basic load proc (loading a staging area.) that truncates a target table just before inserting fresh data in said table, all wrapped in a distributed transaction. What happens (only with certain tables and very inconsistently.) is the truncate will fail (no error) and duplicate data ends up in the target table since no wipe actually occurs. This proc only started having this issue when we went from SQL 2008 to SQL 2014.
View 9 Replies View RelatedHi All,
We have written a multithreaded application in which we are maintaining separate Database connections for each thread. these connections are opened and closed at the start and exit of the thread.
We have observed that even if there is only one thread running ( application thread), execution of simple Select queries some times throws Native exception(Error - 0xc0000005 ).
This error comes intermittently. Execution of same query doesn't throw exception.
Further investigation by breaking the debug at the time of native exception and then looking into the stack trace shows that native exception has occured at one of the Native API (CompileQueryPlan() ) which is being called implicitly by the SQL Mobile.
Each time when we see native exception stack trace shows failure at CompileQueryPlan() call.
Let me know if this is a known defect in SQL Mobile 2005 or there is something else that is causing native exception.
Thanks,
Nikhil
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",   otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",   otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAMEÂ CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
Hi All,
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
Any ideas would be gratefully received.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
Any ideas???
Also this domain account is a member of
SQLServer2005MSSQLUsers$ServerName$MSSQLSERVER
SQLServer2005MSAgentUser$ServerName$MSSQLSERVER
We 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 RelatedHi
I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.
I also need to adjust rows, say mth and nth that meet condition , say y.
My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)
This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.
Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:
ID
Int1
Type1
Ideal Rank by Type
1
1
1
1
2
1
1
2
3
2
1
3
4
3
1
4
5
5
1
5
6
8
2
1
7
13
1
6
8
21
1
7
9
34
1
8
10
55
2
2
11
89
1
9
12
144
1
10
13
233
1
11
14
377
1
12
15
610
1
13
16
987
2
3
17
1597
1
14
18
2584
1
15
19
4181
1
16
20
6765
1
17
The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...
I hope this posting is clear, albeit long.
Thanks in advance
Greg
PS The code:
USE
master
GO
CREATE DATABASE CertainRowsToChange
GO
USE CertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE PROCEDURE Initialise
AS
BEGIN
INSERT INTO InitialisedValues (Int1 )
SELECT 2
INSERT INTO InitialisedValues (Int1 )
SELECT 4
INSERT INTO InitialisedValues (Int1 )
SELECT 8
INSERT INTO InitialisedValues (Int1 )
SELECT 16
END
GO
EXEC Initialise
/*=======================================================*/
CREATE TABLE AllRows
(
AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE TABLE RowsToChange
(
RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE PROCEDURE InitialiseRowsToChange
AS
BEGIN
INSERT INTO RowsToChange (Int1 )
SELECT 2
INSERT INTO RowsToChange (Int1 )
SELECT 4
INSERT INTO RowsToChange (Int1 )
SELECT 8
INSERT INTO RowsToChange (Int1 )
SELECT 16
END
GO
EXEC InitialiseRowsToChange
GO
CREATE PROCEDURE PopulateAllRows
AS
BEGIN
INSERT INTO AllRows (Int1 )
SELECT 1
INSERT INTO AllRows (Int1 )
SELECT 1
INSERT INTO AllRows (Int1 )
SELECT 2
INSERT INTO AllRows (Int1 )
SELECT 3
INSERT INTO AllRows (Int1 )
SELECT 5
INSERT INTO AllRows (Int1 )
SELECT 8
INSERT INTO AllRows (Int1 )
SELECT 13
INSERT INTO AllRows (Int1 )
SELECT 21
INSERT INTO AllRows (Int1 )
SELECT 34
INSERT INTO AllRows (Int1 )
SELECT 55
INSERT INTO AllRows (Int1 )
SELECT 89
INSERT INTO AllRows (Int1 )
SELECT 144
INSERT INTO AllRows (Int1 )
SELECT 233
INSERT INTO AllRows (Int1 )
SELECT 377
INSERT INTO AllRows (Int1 )
SELECT 610
INSERT INTO AllRows (Int1 )
SELECT 987
INSERT INTO AllRows (Int1 )
SELECT 1597
INSERT INTO AllRows (Int1 )
SELECT 2584
INSERT INTO AllRows (Int1 )
SELECT 4181
INSERT INTO AllRows (Int1 )
SELECT 6765
END
GO
EXEC PopulateAllRows
GO
SELECT * FROM AllRows
GO
WITH Temp(OrigID)
AS
(
SELECT OrigID FROM
(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable
INNER JOIN
RowsToChange AS ToTable
ON FromTable.RowScore = ToTable.Int1
)
UPDATE AllRows
SET Int1=1000
FROM
Temp as InTable
JOIN Allrows as OutTable
ON Intable.OrigID = OutTable.AllRowsID
GO
SELECT * FROM AllRows
GO
USE
master
GO
CREATE DATABASE ComplexCertainRowsToChange
GO
USE ComplexCertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE PROCEDURE Initialise
AS
BEGIN
INSERT INTO InitialisedValues (Int1 )
SELECT 2
INSERT INTO InitialisedValues (Int1 )
SELECT 4
INSERT INTO InitialisedValues (Int1 )
SELECT 8
INSERT INTO InitialisedValues (Int1 )
SELECT 16
END
GO
EXEC Initialise
/*=======================================================*/
CREATE TABLE AllRows
(
AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL,
Type1 int NOT NULL
)
GO
CREATE TABLE RowsToChange
(
RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL,
Type1 int NOT NULL
)
GO
CREATE PROCEDURE InitialiseRowsToChange
AS
BEGIN
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 2, 1
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 4, 1
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 8, 1
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 16, 1
END
GO
EXEC InitialiseRowsToChange
GO
CREATE PROCEDURE PopulateAllRows
AS
BEGIN
INSERT INTO AllRows (Int1, Type1 )
SELECT 1, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 1, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 2, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 3, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 5, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 8, 2
INSERT INTO AllRows (Int1, Type1 )
SELECT 13, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 21, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 34, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 55, 2
INSERT INTO AllRows (Int1, Type1 )
SELECT 89, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 144, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 233, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 377, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 610, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 987, 2
INSERT INTO AllRows (Int1, Type1 )
SELECT 1597, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 2584, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 4181, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 6765, 1
END
GO
EXEC PopulateAllRows
GO
SELECT * FROM AllRows
GO
WITH Temp(OrigID)
AS
(
SELECT OrigID FROM
(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable
INNER JOIN
RowsToChange AS ToTable
ON FromTable.RowScore = ToTable.Int1
)
UPDATE AllRows
SET Int1=1000
FROM
Temp as InTable
JOIN Allrows as OutTable
ON Intable.OrigID = OutTable.AllRowsID
GO
SELECT * FROM AllRows
GO
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Is any has the same problem ?