Hi folks!!
I am new to installation of SQL Server 2005..I wanted to know while selecting Service Account Screen why Avoid running SQL Server Agent as the Local System account.????
T.I.A
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:
A very stupid question but want to see if i have an answer.
Is it possible to run SQL Server Service and SQL Server Agent Service under local system(not Domain account) without granting SA Permissions on the server. is it ever possible to run by just granting some registry permissions and not making BUILTINAdministrators a SA on the server.
Thx
AK
I have an application I developled for a shopping cart function using Wrox ASP.NET book as a guide. I have everything working great on my local system using SQL 2005 Express. When I move the app to an IIS 6.0 Web Server with SQL 2005 Express installed I get the following error.
Line 103: Public Overridable Property Cart() As Wrox.Commerce.ShoppingCartLine 104: GetLine 105: Return CType(Me.GetPropertyValue("Cart"),Wrox.Commerce.ShoppingCart)Line 106: End GetLine 107: Set
It has some comments up top about not being able to establish a connection to the SQL Express Database. I have made the changes to allow TCP/IP and Named-Pipes
The App has to be connecting to the database because it displays grid views during the order process.
The Database is located in the App Data folder
IIS is set to Integrated Authentication and configured as a Web Application.
Can someone please tell me what is wrong.
thanks.....
It is SQL server 7. Scheduled jobs were running fine until the hard drive crasked. Replace the hard drive and restored the dabases. However, all the scheduled jobs cannot run. I created new jobs using database maintenance plan and it still doesn't work. the same error occurs when viewing the Job history.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
However, if I create a job by right click on the database and select backup database. This job runs fine.
What should I do to make jobs created by the database maintenance plan running again?
Thank you for your help.
Hello there,This is a bit of strange one.My local version of SQL Server fails to start when I boot yet if Istart Enterprise Manager its started and working fine. ISQL or QueryAnalyzer is also not working.If I try and register the server by another SQL Server is not there. Methinks that the named pipes might be a bit broken but would appreciateany advise from people.ThanksGinters
View 2 Replies View RelatedIf a job is currently running and I want to disable it so that it does not run as scheduled for the following night, will it disrupt the current process?
View 1 Replies View RelatedI've tried finding an answer to this on this website but I don't see any. I'm running SQL Enterprise Manager v8.0 and have a job in the SQL server agent folder that won't run as scheduled nor will it run manually if I Right-Click and choose Start Job.
It's designed to run a DTS. I can run the DTS manually without errors but the job won't run it for me. I will try to provide as many details in this post as I can in hopes that someone who is more experienced at this can help me.
DTS DETAILS
The DTS is designed to transfer data from a table on one server and transfer it through a firewall to another server into a table on that server. Then delete the entries from the originating table. This works very well if I run it manually. The purpose of deleting entries from the web server is for security. Once the data in on the other side of the firewall it's safe.
The job however will not run either manually or from it's schedule. I've tried creating a new job and setting up a very simple schedule that runs daily every 2 minutes for example. I've played around with different times but no luck. So I will try and provide you with job details below:
JOB DETAILS
Name: TransferData
Enabled, Target = Local Server
Category: Uncategorized Local
Description: Execute package: TransferData
STEPS
ID: 1
Step Name: TransferData
Type: Operating System Command
On Success: Quit with Success
On Failure: Quit with Failure
PROPERTIES OF STEP
Step Name: TransferData
Type: Operating System Command (CmdExec)
Command: ( I have no idea what this translates to as I did not write it) DTSRun/~Z0xF6EE76ED6259A60AC370F465DE7F3095F4B37F7176C05B8E72E396DEBE60135E53ACFEDB00E1F216DD8EC8206782439BA435771E3C1E8A7A5D0DAE3F3AAA9F48BF08A9A9FA23C010A85573546F6B6B900323F76DC8A27EACAFD63904464DD5A9B28137
Nothing special in the advanced tab
SCHEDULES TAB
ID: 20
Name: TransferData
Enabled: No
Description: Occurs every 1 day(s), every 2 minute(s) between 12:00:00 AM and 11:59:59 PM
and is setup as Recurring
Nothing in Notifications Tab
I only have two SQL books to reference. This is a production fire to put out and I really need the help here as I am not a certified SQL guru.
I hope I have provided enough information here for someone to help. I'm happy to provide an email address for easier contact should you request it.
Thanks in advance for any assistance with this.
Hi all,After merged two partitions into one (C: and D: into one C:) byusing Partition Magic, I can't start SQL Server 2000 which waspreviously installed in both C:Program FilesMicrosoft SQL Server80and D:Program FilesMicrosoft SQL ServerMSSQLThe original files in D: (every thing in D: actually) are now copyedin C:Data folder.Other than re-install SQL Server in C:, is it possible to restoredata(files) from D:Data ?Mank thanks to any suggestions/hints.Kind Regards,Bob
View 3 Replies View RelatedHi, all experts here,
Thank you very much for your kind attention.
I am wondering if we could back up the databases to any place outside of the local server system? As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.
Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi,
I ahve attached the bmp doc that will show my screen snapshot.
My SQL server Agent service indicator is not showing green as like other server. I have started the serice and running fine. I don't have any problem running the job.
My curious is why that indicator is not showing up.
Thanks,
Ravi
I get the following error:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'I:MyFolderMyRptsMyApplicationMyDatabase.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
The above path "I:...." is mapped on the server that SQL Server is running on.
This package runs with NO problems in MS Visual Studio.
HI ALL
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
Any Idea..
Hi. I was wondering if anyone has ever had a problem where nothing seems to be wrong with the server, and SQL Server Agent is up and running, but jobs fail. We had a job run at midnight, and it was the last successful run. Every job failed after that, with an error stating "Unable to retrieve steps for job..." There doesn't seem to be any reason for the problem. The jobs were kicked off manually and they all ran.
The only other error messages we could find were in the Application Event Viewer on the server. But the first error happened days ago and said this: "The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Unable to open shim database version registry key - v2.0.50727.00000."
Since then, every few seconds this error occurred: "Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code."
We turned off the performance monitor counters and that error stopped. But we still have no idea if that was the cause of the problem.
This isn't the first time we've had an issue with jobs failing because the steps couldn't be 'found', while SQL Server Agent was up and running.
Has anyone else had this problem?
Thanks!
Hi All,
I was working on some reports on SSRS. Now I cant view those reports coz I can not open report manager.
I noticed that SQLSERVER and SQL Server agent has stopped running in my local machince. But Other services are running properly (SSIS, SSRS, SSAS). When I go to SQL Configuration manager and try to run them, I get the following error massege.
" The request failled or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details. "
So now I can not connect database engine in my local machine through management studio. When I try to do that, I get the following error massage.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"
Also I can not access reportserver data base and reportservertemDB.
If I reinstall SQL Server again, what would happen? Would I still be able to use and view my old reports?
Please can anyone help me to get this worked again correctly?
Thanks
I get 2 errors when I run a job in sql server agent 2005 0xc0202009 and 0x80040E2. I cannot see why I am getting these errors, when I run the job with dtexec it is successful. Also when I run it in the debugger or run the executeable it is successful. But when I try to run it with the agent it fails. Any Ideas would be greatly appriciated.
thank you
Joey Kushinski
Okay, i've got an ETL package which works flawless, we've created a scheduled job for it which also worked.
This ETL accesses a flat file located on a server on a different location in the same domain.
Last week we start a migration to a new network, the location where the flat file is stored has already moved to the new domain but the SQL server agent is still on the old domain.
There is a trust between the 2 domains, the network user from the old domain has been added on the server in the new domain where the flatfile is located.
If i run the etl package manually it works flawless, now if i run the etl using the SQL server agent it won't run anymore.
I get the following error:
Executed as user: OIdDomaindwh. ...n 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:15:00 Error: 2007-10-02 21:15:02.10 Code: 0xC001401E Source: Rolmer_Product Connection manager "Flatfile Product" Description: The file name "\fs-cha-fs01dwhproduit.csv" specified in the connection was not valid.
Don't tell me its a problem related to the user that the SQL Server Agent is set to because its set to OldDomaindwh which is the same user i use to login on the server where i can manually run it without problems. The problem only occures when i run it using the SQL server agent which uses the same user to execute packages.
This is a very difficult problem i doubt anyone here will be able to crack this nut, but if they do i'll be very impressed because it would require a real smart network admin with SQL experiance to figure this one out...
Good luck
Hi, I was wondering if anybody would be able to help me and realise by looking round that is a reasonably common problem, but it€™s starting to drive me mad now!!
I€™ve got an SSIS package which takes data from an excel spreadsheet and using a stored procedure updates a table in a database, before moving the spreadsheet to another folder once it finishes. This works fine when in the Business Intelligence Design Studio, but when I try to set it up to run from the SQL Server Agent I get nothing but problems. I€™ve had a look around various forums and seen that many people have had this problem and I€™ve followed advice that other people have given, but try as I might nothing will work for me.
Currently I€™m trying to go down the package configurations route. I€™ve enabled the use of package configurations in the development studio and specified the configuration to pick up the destinations, usernames and passwords of everything that I have in the Connection Managers. I then go to the SQL agent and as a step tell it to execute the SSIS package using the package configuration xml file that it has created. Both the SSIS package and the package configuration file are saved and been asked to run from the sql server I will be using. I then go to run the package and it thinks about it for a minute or so before falling over. The history then says the below:
Message
Executed as user: UKWACALORsql2k5svc. ....00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:35:31 Progress: 2008-05-06 11:36:32.10 Source: Data Flow Task 1 Validating: 0% complete End Progress Error: 2008-05-06 11:36:32.15 Code: 0xC0202009 Source: WiReCAPSLoad1 Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2008-05-06 11:36:32.15 Code: 0xC020801C Source: Data Flow Task 1 Destination - cstCAPSLoad1 [32] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. ... The package execution fa... The step failed.
The DestinationConnectionOLEDB is pointing at a database on the same server as where I have the SSIS package and configuration file stored. I€™ve gone into the xml file and noticed that the passwords aren€™t saved so I€™ve added the passwords manually into the file after everytime it says username=sa but this hasn€™t made a difference. I€™ve also made sure that the package is saved to dontsaveencrypted.
I€™ve gone through the following article:
http://support.microsoft.com/kb/918760
and tried to use things such as credentials but these haven€™t helped either.
I€™m really not sure why its giving me such hassle, I have the SSIS package on the sql server, I have its package configuration on the same server, I have the databases it wants to access on the same server, I€™m using the sa account wherever I can to make sure I have no access / rights issues, so I really have no idea why this won€™t work. All I can think of is that the sql2k5svc account that the sql agent job then runs under doesn€™t have authority to get to the database or the package configuration file, but I€™ve added that account to have full control on the database that it will accessing, but again no joy. How do I change it so that another account can be used to run this, one that I know has all the authority required to access everything it should?? And why would the login for sa be failing when it€™s the highest level account??
I€™ve read so many different articles on this problem and tried to follow so many different resolutions that I really don€™t know how any of this is meant to work anymore!!
Any help would be gratefully received!!
Thanks
Brad
I am in a situation where I need to get a copy of test database that ison production server running MSSQL 2000 Standard to my local machinerunning MSSQL 2000 personel. I tried to use the copy wizard where itappears I get connected to the source server OK but when I try toindicate the destination server which is my local machine I get errorspoping up about cannot connect to (local) etc.I am NOT a DBA just a programmer trying to get a local test environmentup to be more productive.Lsumnler
View 2 Replies View RelatedI have the Image in FTP Server Folder and i need to insert that image into my local database.
How can i do this I tried with the below Query but i shows the errors as below.
--INSERT INTO AcademyStudents (ImageURL)
--SELECT BULKCOLUMN FROM OPENROWSET(BULK'https://iconic-solutions.net/OTA/test/images(1).jpeg',Single_Blob) AS BLOB
--Where StudentIdentificationNum = 2
--GO
GOt Error
;
Cannot bulk load because the file [URL] could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
Hi
I have setup a step in SQL Server Agent to run a SSIS package that I have created. However the step fails straight away and refers me to the history log, which doesnt seem to show what the problem is.
I've tried running the package manually through dtexec.exe and it runs through fine. Does anyone know what the problem could be?
Thanks
Hello,
I have created a package that runs without problem.
I run the package with the command dtexec /F "package_name.dtsx" > package_name.txt.
Then I run the same package from SQL Server Agent, everything is OK
Then, I tried to edit the command line to have the output file, but I got an error.
The command line is:
dtexec /F "package_name.dtsx" MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E > package_name.txt.
(MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E are created by default)
How can I do?
Thank
When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
My package is connecting to an external data provider using an OLEDB driver . The package runs fine in debug mode.When i tried to run the same from SQL server agent it failed  to aquire the connection. The OLEDB provider does not contain too much of information , ( connection string, initial catalog, blank user name and password).The same package executes successfully if i run using dtexec in BAT file.But if i use the dtexec in sql server job step as operating system command and try to run, the job will fail reporting " can not aquire the connection".
View 2 Replies View RelatedI have about a 447 MB SQL server 2000 database on a desktop PC acting as a QA server. The hardware specs of the QA box are as follows:
CPU: P4 2.4 GHz
Memory: 1GB
Drives: 80 GB IDE
I recently purchased a Dell PowerEdge 2650 server to act as the staging box. The staging box has
CPU: P4 2.4 GHz
Memory: 2GB
Drives: 40GB SCSI, mirrored
I made a backup of the database on the QA box, and restored it on the staging box. Yet when I run something as simple as a select query (select * from <table>), the less powerful QA box is faster.
I figured maybe the statistics are different on the staging box. I ran dbcc showcontig to make sure the statistics were identical. Also ran RedGate's SQL compare and data compare to make sure everything was identical.
I figured maybe the query optimizer needs to be tweaked. I recreated the indexes and updated statistics on the staging box. The queries actually got slower as a result.
I thought maybe SCSI drives are slower. Tried breaking the mirror on the staging box. No luck. Put the mirror back in place, ran a test where I copied a large folder from one directory to another on the staging box. Repeated the same test with the same data on the QA box. The staging box was more than twice as fast than the QA box.
It doesnt appear to be a problem with the query, adjusting memory in SQL server has not effect, both boxes are using SQL server 2000 SP3, why is the bigger machine running queries hundreds of milliseconds slower than the smaller machine? Any help will be appreciated!
I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.
We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).
Here what I have done so far:
I created the database
Created the file and successfully imported data into it using the Import Data feature.
I saved the SSIS package
Scheduled an Agent Job for this package to run at certain time,daily
At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.
Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.
Hello
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!
I am doing some ssis package development in my local machine. I have a requirement to send email the output.
I am developing in my local machine. Is it possible to configure SMTP configuration in my local system to receive the email in Gmail?
if yes, what all are the steps to be followed.
Hi all,
I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.
I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:
Intel Centrino Duo 2.00 GHz
2GB RAM
XP Pro SP2
There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.
Sincerely,
Brian Pulliam
Hi,
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €śdtexec€? command from inside an SQL Server job.
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
We have one €śmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €śdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €ścmdexec€? that runs the €śmain€? SSIS package with the €śdtexec€? command).
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
Additional information:
The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €śMessage€? is empty (€śMessage: €ś).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €śmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
The request:
Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!
Eero Ringmäe
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
What I can do to solve this issue.
Bye!
Daniel
I am trying to upgrade the SQL Server 6.5 on one server to SQLServer2000 on the other machine.At that time, i am getting the following error:"The local SQL Server is running under localsystem priviledges,preventing the upgrade wizard to connect to the export server."I am logged on thru the administrator on both the machines.Pls tell how to to eradicate this error, so that my upgrade issuccessful.Thanks in advance.
View 1 Replies View Related