Today when I came into work, I noticed all of my jobs failed on a particular server. I tried to manually kick off the packages, but to no avail. Both the job history and the error message from the packages state a timeout error.
Any idea on this? Also, I have no problem running a query from query analyzer...even on a remote machine.
Any help would be appreciated
Trevor
I can not get SQLServerAgent to Send Mail. Help!
SQLMail started successfully.
In the server agent properties I use the Test button and I receive the
response.
"Successfully started(and stopped) a mail session with this profile."
Indicating that mail should work.
If I send a email notification from a scheduled job it gives me the error:
NOTE: Failed to notify 'operations' via email
In the SQL Server Agent Error log I get.
"[264] An attempt was made to send an email when no email session has been
established"
xp_sendmail works fine.
Any ideas would be greatly appreciated.
TIA...
The SQL Server Agent process is running. When I use EM to start a database integrity
check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base
and research this problem.
In the mean time if someone can help, it is much appreciated.
Thanks to all.
All my DTS scheduled Jobs fail after I installed SQL SP3. But if I run the DTS package from Enterprise Manager it executes successfully.
Here is one of the errors:
The job failed. The Job was invoked by Schedule 7 (dtsImportCustomers_0001). The last step to run was step 1 (dtsImportCustomers_0001).
and
Executed as user: SQLADM. DTSRun: Loading... Process Exit Code 1. The step failed.
Thanks for your help!
Hello,
It is our yearly procedure to change the sa password.
This time we do it for the first time in ss2005.
After changing the sa password, all jobs fail with the following error:
MessageLogin failed for user 'sa'. [CLIENT: <local machine>]
MessageError: 18456, Severity: 14, State: 8.
When i check the properties of the job i can see (in the general tab) that all jobs are running through Windows authentication.
Any newly created jobs run successfull.
What do i do wrong?
Any help is appreciated.
Best regards,
Harry
Please refer me to a good article for creating alerts for sql jobs failure. Thanks a lot.
View 1 Replies View RelatedRunning MSSQL 2000 on Windows2000 in an NT domain. We have several jobs created by the web assistant wizard. They run along just fine for days or weeks, then all of a sudden SOME of them (?!) begin to fail with the message "Unable to perform a SETUSER to the requested username 'dbo' because the username is invalid for database 'xxx'" where xxx is of course the db where the proc resides. SQLAgent service is running as a domain admin and has not changed; job is owned by sa and has not changed; the domain admin in question is in sa role and has not changed. Something sure has changed, but what? Sometimes when this happens we can clear it up by deleting the web job and simply re-creating it. Today we have three culprits and none of our usual tricks are working. Why does this happen? How can we fix it? How can we PREVENT it! Thanks for any words of wisdom out there.
View 1 Replies View RelatedSQL 2012 Standard on Windows Server 2012 Standard
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
After upgrading a server from SQL 2000 to SQL 2005, I can't get any SQL Agent jobs to run, giving logon failed messages. MSSQLSERVER and SQLSERVERAGENT services are set up to run as the same domain account which has administrative rights to the server and is a SQL login with the sysadmin role. For basic testing purposes, I created a test job that all it does is run a T-SQL command 'SELECT @@VERSION'. The job is also set to run as the same account that the Agent is running under. Below are the logs (with trace messages on) from the point of starting the Agent to my attempt to run the job (user and server names have been changed to protect the innocent):
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168
2007-10-22 15:31:15 - ? [101] SQL Server TESTSERVER version 9.00.3042 (0 connection limit)
2007-10-22 15:31:15 - ? [102] SQL Server ODBC driver version 9.00.3042
2007-10-22 15:31:15 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2007-10-22 15:31:15 - ? [310] 4 processor(s) and 3328 MB RAM detected
2007-10-22 15:31:15 - ? [339] Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2
2007-10-22 15:31:15 - ? [431] Populating subsystems cache...
2007-10-22 15:31:15 - ? [432] There are 11 subsystems in the subsystems cache
2007-10-22 15:31:15 - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 80)
2007-10-22 15:31:16 - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40)
2007-10-22 15:31:16 - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40)
2007-10-22 15:31:16 - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 100)
2007-10-22 15:31:16 - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'SSIS' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2007-10-22 15:31:16 - + [260] Unable to start mail session (reason: No mail profile defined)
2007-10-22 15:31:16 - ? [174] Job scheduler engine started (maximum worker threads: 400)
2007-10-22 15:31:16 - ? [193] Alert engine started (using Eventlog Events)
2007-10-22 15:31:16 - ? [146] Request servicer engine started
2007-10-22 15:31:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2007-10-22 15:31:16 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient...
2007-10-22 15:31:16 - ? [133] Support engine started
2007-10-22 15:31:16 - ? [167] Populating job cache...
2007-10-22 15:31:16 - ? [297] SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000]
2007-10-22 15:31:16 - ? [168] There are 2 job(s) [0 disabled] in the job cache
2007-10-22 15:31:16 - ? [170] Populating alert cache...
2007-10-22 15:31:16 - ? [171] There are 0 alert(s) in the alert cache
2007-10-22 15:32:59 - ? [177] Job Test1 has been requested to run by User DOMAINmyaccount
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
2007-10-22 15:32:59 - ? [184] Job completion for Test1 is being logged to sysjobhistory
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
I have 2 DTS packages that import data from an Access database that have recently started failing. They run fine manually in DTS, but not manually as a job. They get an "unspecified error." These were running fine, until we installed Outlook and started to add Outlook mail to SQL.
Originally, Administrator was the owner and that is how the jobs were run. We changed to SQLAdmin for the SQLAgent to start under, and I changed the owner of the jobs to SQLAdmin. This works for all jobs but these 2. I thought maybe SQLAdmin could not get to the Access database, but it can. I spell out the path for the database, I don't use any mappings. I recreated the jobs logged in as SQLAdmin, and they still do not work as jobs.
Any ideas are much appreciated!! Thanks in advance!!
Karen
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 a parent package and three child packages.
In all packages they make connections to a SQL 2005 db which sits on the same instance as which the packages are being deployed.
In the Child packages these connections have the connection string set as a package property variable in Package configs and in the Parent package this variable exists correctly.
This variable in the Parent is itself defined in a PackageConfig which uses SQL Server as the holder for the configuration.
When I deploy the packages the connections to in the child packages fail, one connection by timing out, another with an acquire connection failure.
I have set the DelayValidation to false on all connections but it made no difference.
I have also increased the timeout to 180s but again still a failure.
When I deploy the parent package on its own it is successful, however when the child packages are deployed on their own these connections again fail to validate due to a timeout and connection failure, yet they are using the same connections defined in the parent.
The child packages are no larger than other single packags that I have deployed successfully with the same connection to the same server.
Is there anything special that I should be aware of regarding deployment of parent and child packages.
Perhaps an ignorant question, but is there a way to back up the jobs and packages in SQL server 7..????
View 2 Replies View RelatedHi,
I have diferent jobs scheduled in the system but I can´t find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)
DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28
I would like to know the Package name related to.
Any help would be very appreciated.
Regards
Hi boys and girls, I am a total newbie to ms sql. So please be gentle.
I have a ms sql database on one server but I need a duplicate of the ms sql database on another server with all packages and jobs duplicated on the new server.
I was able to export the database over to the new database on the new server no problems there.
The problem is under DTS I have "local packages" that all need to be moved to the new server. I am able to export them into my computer. But on the new server there is no import for the local packages. How do I get these packages onto the new server into my server's ms sql???
Also under the management and sql agent manager, there are jobs that are on the old db, that I need to go to the new server in ms sql.
Any help at all would be appreciated!!!
Thanks!!!
Hello,
I want to move a db from a SQL server 7 to SQL Server 2K. I'll do a backup and restore function to move the database, but how do I handle the objects mentioned in the header ?
Bothe servers (old and new) will have the same name, as well as the DB
All help is appreciated !
Thanks
I'm having an issue in SQL Server 2005 with jobs that execute SSIS packages. The jobs run fine for a week or so, then I'll come to find that four or five (of the ten or so jobs) are hung in "executing" status. They seem to hang indefinitely (as some have been "executing" for hours with no end. The schedules of the hung jobs are all different, varying from every 10 minutes to nightly. The packages perform completely diffent tasks, as well. I can't seem to find any common thread with the jobs that get hung, other than they are all executing SSIS packages.
I've tried manually stopping the jobs and restarting the agent and SQL Server but the jobs hang again on there next scheduled run. The only thing that fixes the issue is rebooting the box, and then the jobs hang again in a week or so. Could some sort of memory leak be consuming resources throughout the week and be causing the jobs to eventually hang? I just rebooted the box and the sqlagent90.exe process is currently using about 7mb of memory. I'll keep an eye on it. Any other suggestions?
I've thought of creating another job that stops jobs that are hung, but what's to say that this job won't get hung as well? Plus this seems like a band-aid fix...
I don't recall having these problems until installing SQL Server 2005 SP2. Could this be related? I've searched like crazy and still can't find a resolution to this. It's becoming a big PITA...
Anyway, any suggestions would be very much appreciated!
Hi,
any1 tried using a button in aspx page to trigger SQL DTS packages or SQL schedule jobs??
(*using SQL2000)
I need to generate a list of databases, jobs, DTS packages, Linked servers, and users.
Is there a simple script or procedure for this?
Thanks
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
I need for a database to give my users and indication that a renewal has been complete. Basically what happens is every year once a month a report is generated from sql of how many employees need their gaming license renewed the filter is based off of a field called final suit. I need to find a way to let them know through the database that an employee has been renewed. anyone got any ideas??
View 9 Replies View RelatedHi all
I am fishing for ideas on the following scenario and hope someone can point me in the best direction.
I create a new table and insert data from relevant other tables but i want to set the data in the new table colums to set widths that have leading zeros where applicable.
I.E the new table column is varchar (10) the data going in to the column is coming from another table where the size was varchar (8) but the data was only 2 characters in size so i want to pad it out to the full new varchar (10) with leading zeros if that makes sence.
I am really just trying to get some ideas on the best possible way to do this thanks.
I have a table with 18,000 records with beg_eff_date since the year 2005. I need to separate the entries based on their daily activity. For example if Beg_eff_date 01/0/2005 then day is "1" if Beg_eff_date is 01/27/2005 then the day is "27".
Repeating the same process until I reach "NOW" present time. Any ideas of how can I do this?
Thank You for all your previous help and the current one!!
Hi:
I created a Temp Table that hold a collection of records by date.
I need to do create calculation that give me the Total fuel used per day:
c.rcpt_nom_vol -c.rcpt_fuel- c.rcpt_act_vol = Total Fuel used per day.
Then pull the result of this calculation and assign the result to the specific day of the month. For example is the Calculation Result is 4.25 on Feb 12, 2007. Then the record is insert into a Temp Table as 4.25 day #12.
Does anyone has an idea of how to do this? Thanks and Let me know!!!!
Create Table #TP_Daily_Imb(
contract_nbr char (8),
contract_sub_type char (3),
contract_type_code char (3),
current_expirtn_date datetime,
nom_id char(12),
nom_rev_nbr char(2),
beg_eff_date datetime,
rcpt_dlvry_ind float,
rcpt_nom_vol float,
rcpt_fuel float,
rcpt_act_vol float,
end_eff_date_DGC datetime)
SELECT Distinct a.contract_nbr, a.contract_sub_type, a.contract_type_code,a.current_expirtn_date,
b.nom_id, b.nom_rev_nbr,
c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel,c.rcpt_act_vol
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
Hi everyone
We've got currenlty around 500 dts 2000 in production.
In order to know in what ETL processes we have Oracle connections, or FTP tasks or whatever, we did a VB6 app using dtspkg.dll which load all the properties for each DTS into Sql server tables. So that, then you could see from a specific DTS how many connections, Sql Tasks it had and so on..
How to accomplish the same with SSIS? I know, doing the same but using .Net, of course, but is there any else approximation? I am little bit concerned when we will have hundreds of them up.
Maybe 2005 is offering this feature automatically, I don't know.
Thanks in advance for your time/advices/ideas,
Am working on an SSIS project and I am not sure how to handle this situation. I have four tables in two completely separate networks {AB} {CD} and I want to populate one of the tables D based on the information from tables A and C with the following scenario I get data from from table A make a lookup transformation to check what did has changed if the data has changed or if there is a new entry in table A get the identity key from table C {they both share a common field} and add the identity key as part of my data inserted in table D
A ----lookup <
data that has changed --- get the identity key from C ---- insert data from table A + identity key into D
I have a text file that I am importing in a data flow task. Not all of the rows are the same, there are "header" rows that contain a company code.
What I want to do is keep that company code and append that to the row of text that I am writing to a CSV file.
Since you cannot change variables until the post execute, what are my options?
Hope that's clear
Thanks!
BobP
I have some performance issues which I can not explain; The database it is running on on a dedicated SQL server box with 8 cpu€™s 4 GB memory. Users are connecting with a fat client.
There is hardly on users using it but they are getting long delays. So I kicked of a profiler trace and sp_blocker. I noticed some queries taking over 30 sec to complete in profiler but when I run them in query analyzer they run in under a second.
I check the output from the blocker and there was no blocking over that time period and it was the only processes actually running.
How can it be so slow I have run out of ideas please any ideas would be welcome
Many thanks
I'm looking for Ideas on how to handle a Pldege Reminder process. For example; a pledge is made to pay $2400 over the next two years. They will pay $100 per month and each month a reminder will be sent. No real mistery if there is a balance you send a reminder. My problem is how to handle things like what if they want to pay quarterly or annually and how to determine if a payment is really due based on when they paid last, etc... You most likely see what I mean.
If anyone has done this in the past and/or has any ideas and is willing to share I would greatly appreciate any help.
Some stuff that may help you help me better:
tblClient (ClientID)
tblPledge (PledgeID, ClientID, PledegedAmt, PledgeDate,Frequency,NumberYears)
tblPledgePayments (PmtID, PledgeID,PmtAmt,PmtDate)
I'm new to asp.net. Please help me with some ideas about the design of databases and interface of the web site.
We are a public school district, I want to create a website for families to go on line to update their family and student's demographic information. Basically I put the students' infomation pulled from our student information system software on the web, the parents can log in on line to update it at the beginning of school year. After that, I will create some report and let secretary manually do the update to our student information system.
The demographic infor includes 3 parts,
1. family street address, city, state, zip2 guardian1 primary phones,second phone, emails. primary phones,second phone3, student date birth, gender. may have multiple students in one family
But how can I track which field parents changed, shall I do it in programming in the web form, or later when I create some kind of reports. For I only want to pull students with the fields that updated and give them to secretary to update manully, and I don't want to generate a full report and let them compare which field is changed and then update, it will take them too much time.
Thanks much in advance
I've read thru all the threads here which say basically the same thing, use a drop table & then a create table to clear the spreadsheet. My problem is the process works the first time to create the table in a new Excel file but fails to clear excel after that. The processes drop, create, & pump data all work without fail but the spreadsheet data is appended to. I've used the wizard as well, saved the package and ran it again without success. Anyone ran into this one?
View 1 Replies View Related