Hi all,
Is there any way so that we can run/ execute multiple jobs or local packeges at once. Can we create any sql script which will execute multiple jobs/packeges at once. please advise
I am getting this error when a user runs this job that was created. It is executing a dos command. Any hints on what to fix or look for? This account seems to be o.k. WHat privilege needs to be set for this account.
Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client). The step failed
Hi,
I have a very peculiar problem. I have few CommandExec jobs on SQL Server 7.0 which has been scheduled to execute at a specific time. The jobs are executing properly but, the problem is they stop after the completion. i.e after finishing the jobs they still flash a message "Executing the job" they don't flash the message "Not Running" this is happening only to commandExec jobs, I have also few TSQL jobs which are working properly. I tried even giving the duration (start and stop time) but no use. Has anyone encountered this problem? If you have a solution PLS pass it on to me. I am in a very desparate situation.
Thanks in advance.
Robert
Hello everyone. I'm having a problem with the sql server agent occasionally not executing its scheduled jobs, and unless the sql server agent is restarted they will not execute according to their schedule. I've read many posts on people having problems with running a package manually and then not being able to run it in a job, although that is not my problem since these are jobs that have all run successfully historically, and only until the agent is restarted do they start working again. It is the strangest thing.....we have many different jobs on this server and all of them will just mysteriously stop being executed. Two other things of note, the agent and server processes both run on AD accounts, and we receive this error:
SQL Server Scheduled Job 'X' - Status: Failed - Invoked on: 2007-10-27 02:00:00 - Message: The job failed. Unable to determine if the owner (ADadacct) of job 'X' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).
Any help would be greatly appreciated.
Just an FYI for those scheduling SSIS packages on 64bit Itaniums.
This came to light when using a 32 bit OLEDB driver on a 64bit Itanium.
When manually executing the package on the server through SQL Management
Studio, we got the expected results. However, when we scheduled the
package as a job, it failed every time. The error was:
"The AcquireConnection method call to the connection manager 'ConnectionName' failed with error code 0xC0202009€?.
We tried everything to fix this, but nothing worked. Finally we found
that when manually running the package, SQL Management Studio was calling the
32bit version of dtexec.exe, while the Agent calls the 64bit version of
dtexec.exe. The fix is to not use the "SQL Server Integration
Services Package" type in the job step, but use "Operating system
(CmdExec)" type. Then in the command box, call the 32bit (x86)
version of dtexec.exe.
Example:
C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /DTS
"File SystemSSIS Packages[PackageName]" /SERVER [SQLSERVERNAME]
/DECRYPT [password] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
/REPORTING V
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!!!
I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
How i can create parameterized sql query .This is my basic idea to implement.
select * from dimemployee
where name = ?
or
exec proc sp_para_employee ?
how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
View 2 Replies View RelatedI want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT
:ERROR
ECHO *** ERROR *** Check Log File
:EXIT
*** JOB COMPLETED ***
Dave
I've got the stored procedure which first creates and then starts a job.
This stored procedure can be invoked from a number of triggers (after insert, after update)
The problem is:
when a sequence of statements is being executed one by one (like an insert immediately followed by several updates) and the stored procedure is invoked from the propper insert or update trigger once for each statement, the jobs are created in the right order (first for insert statement, then for updates in the order of the initial statements), but are executed in the wrong order.
Is there any explanation to this? And any solution?
The order of such jobs execution is vital for my application.
Thanx a lot in advance.
Please see the enclosed screenshot for an example list of jobs. Name of the job contains time of it's creation and another column shows the time the job was executed.
I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?
View 2 Replies View RelatedHey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly.....
any suggestions....
thanks,
jonathan
Hi,
I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -
EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5
Thanks
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Can I use Execute package task for this purpose??
Thanks!!
I am probably missing something simple here but...
I have a CLR Stored Procedure where I am attempting to execute multiple SQL commands. Here is what happens.
Case One
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL Query.
8. Execute Query via ExecuteReader and get SQLReader.
9. Read query result set.
10. Close QueryReader.
11. Set Command Text Property to another SQL Query.
12. Execute Query
This is where I get one of two exceptions.
1. An Exception stating that I cannot have parallel transactions.
or
2. An Exception that I have an open SqlReader that must be closed.
Case Two
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL INSERT command.
8. Execute Insert command.
9. Set Command Text Property to SQL Query command.
10. Execute Query Command via ExecuteReader.
At this point the stored procedure appears to hang.
Could someone please enlighten me if I have missed something obvious while reading the manual, regarding what you can and cannot do?
Thanks in advance.
HI,
We are currently trying to import the Data from one DB to Multiple DBs.And these multiple DBs connection strings must be configurable. This I can do it by reading the connection strings from dtsconfig file and execute it for each DB.Is it possible to execute for all the DBs in parallel(Multithreaded).
Thanks,
Sabari.
hi,
i am making a n application which in between deletes the multiple tables from the sql database.
for that i have written the following code:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;
cmd.ExecuteNonQuery();
in this way, many more tables are to be deleted.
is there any need to create the new SQLCommand object again and agin for each and every query. can iot be done like the given above or can there be some better method?
thanz in advance..
divya
Is there a way to get the Report Manager to allow a single user to
execute more than 1 report at a time? Currently, each user can only
have a single report being executed at a time. It looks like any
other reports are being queued and executed serially.
Hi,
I am executing following script which return more than 1 query/row as there are more than 1 log backup :
select @Query = 'RESTORE LOG ' + @dbName + ' FROM DISK=' + '''' +@path+''''+ ' WITH' + ' NORECOVERY, FILE= ' + convert(varchar(10),Position) from #temp where BackupName = 'Log_Backup' and BackupType = 2 and Position> @Diff_Position EXEC(@QUERY)
[Above script restores database with latest Differential backup file and latest log files. ]
Now I want to execute this, but @Query don't work for more than one row returned . Can you please suggest me how to execute such query?
Thanks,
Dipak.
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Then I did the following:
1. Right click on MSDB
2. Import package
Inside Import package window:
Package location: SQL Server
Server: servername
authentication: windows authen
Package path: \serverC$folder1SSIS Packagesfolder2
Import package as:
Package name: package.dtsx
Below is the error I am getting:
TITLE: Import Package
------------------------------
Cannot find folder "\serverC$folder1SSIS Packagesfolder2".
Can I use Execute package task for this purpose??
Thanks!!
Hello!We have a set of individual .SQL scripts which we would like toexecute against a MS SQL Server 2000. Is there any way to have ISQLutility (or any other means) to execute all of them without having toestablish a separate database connection for each script:isql -Ux -Py -Ss -i script1.sqlisql -Ux -Py -Ss -i script2.sqlisql -Ux -Py -Ss -i script3.sql.....isql -Ux -Py -Ss -i scriptN.sqlAll scripts are in the same location, which could be made visible toby the SQL Server itself (so either a client-based or a server-basedinvokation is ok)TIA
View 6 Replies View RelatedHi All
I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.
I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.
Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.
Thanks
Nadreck
Happy new year everyone!!!
Ok, now for my not so happy new year problem ... here's the scenario:
I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!
But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact)
Actually configuration is:
1. OLE DB Source
2. SQL Command access mode
3. The following command written in the command text box:
SET FMTONLY OFF;
exec sp_SampleSP @param1=?, @param2=?
4. Configured parameter mapping.
The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.
Help anyone? Please ...
I am following website link:
[URL]
I require to gather status details about all the SQL Agent jobs in the environment on multiple SQL Servers.
I tried to edit the script using:
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$sqlServerName = 'localhostdeveloper'
 $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlServerName)
 foreach($job in $sqlServer.JobServer.Jobs)
{
$job | select Name, OwnerLoginName, IsEnabled, LastRunDate, LastRunOutcome, DateCReated, DateLastModified
}
but SQL Agent jobs are not reflecting in the mail output...
Hi,
Can we execute multiple instances of the same SSIS package simultaneously??
If yes, how?
If no, what is the work-around to simulate such a functionality?
Thanks in advance.
Regards,
B@ns
Hello all -
Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.
We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...
thanks for all the fantastic support on this site
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies View RelatedI am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
Hi all
I have SQL 2000 SP4 running on a W2K server SP4.
The server acts as a subcriber and publisher and acts as its own distributor.
Each publication uses its own distribution agent and is set to loop.
There are 4 queued updating subscriptions on this box.
When running 3rd party tools to determine open active ports, the replication executables (distrib and logreader) are connecting to local server multiple times. Easily 15+ established connections plus many more waits.
Everything connects to 1433 then opens a new port.
Is this because of the queued updating?
Replication is functioning as it should without any problems.
I even checked our 2 main distribution servers which only act as distributors and there is not one connection from replication to itself.
Any insight would be appreciated
Thx
John
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedHi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View Related