Execution In Scheduled Job Vs Direct Execution
Nov 29, 2004
Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
Any idea?
View 3 Replies
ADVERTISEMENT
Mar 20, 2001
In SQL 7.0 jobs that have been scheduled start correctly, but jobs will not start when requested manually. All services are running.
The only way to fix this problem has been to reboot the server.
Does anybody have any ideas what might be causing this situation.
TIA,
Philip
View 5 Replies
View Related
Dec 18, 2000
Hi,
I want to know if it's possible to retrieve by programmation the time that it took to run a scheduled job.
Thank
Martin
View 2 Replies
View Related
Apr 24, 2008
Hi,
I am new to service broker but I heard that maybe can be useful for my needs.
I have to install my DB to many different machines with SQL Server 2005 express edition and I need a scheduled execution of a procedure (each night ay 4 AM). Due to the "express" I don't have the server agent and due to the kind of the application I can't use task scheduler in order to execute a command by sqlcmd.
So, service broker (included inside the DB instance, correct?!?) can helps me?
Thank you..
Luca
View 5 Replies
View Related
Feb 6, 2004
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Help!!!
View 3 Replies
View Related
Jul 23, 2005
Hi NG!I wrote a stored procedure which at first deletes 100.000 rows and theninserts 100.000 new rows.There is a huge difference between executing this SP in thequery-analyzer (runtime approx. 2 minutes), and scheduling this SP as anJob (runtime > 30 minutes!!!).Why? Whats the issue?And how can i improve the "job"-runtime?The INSERT-Statement gets the data from a joined and grouped SELECT -here is the code (exemplary):INSERT INTO [Table1](Field1, Field2,... )SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1))FROM Table2 T2INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID]INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID]INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID]GROUP BY field1, field2...I have noticed something strange: After executing the SP thequeryanalyzer outputs "1 Row(s) affected." line by line - 100.000times... Obviously every line is inserted one by one.Any idea?Would it help, to create a temporary-table with SELECT INTO, and thenjust INSERT this data (w/o aggregation) into the target table?Any help is greatly appreciated, tia and best regards---Heiko
View 10 Replies
View Related
Aug 23, 2007
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies
View Related
Dec 7, 2005
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies
View Related
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
View 4 Replies
View Related
Jul 7, 2006
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Jan 29, 2007
As per coding below. Who's can show me how to execute this coding to my sql data source? I already setting my data source, but i can't call my data source to my table. I wanna show the data in my data grid when i type ID Number in textbox but when i click the command button, no record appear. Is it got something error in my coding or no connection between my sql data source? Plssss....help me.....I'm still new in asp.net and no experience with that.
Dim strSQL As String = "SELECT * FROM anggota"
Dim strWhere As String = String.Empty
Dim strCriteria As String() = txtIC.Text.Split(" ")
For Each str As String In strCriteria
If Not String.IsNullOrEmpty(str) Then
If Not String.IsNullOrEmpty(strWhere) Then strWhere &= "AND"
strWhere &= String.Format(" (Column LIKE ' %" &{0}& " % ') ", str)
' %" & myvar & " % '
End If
Next
If Not String.IsNullOrEmpty(strWhere) Then
strSQL &= String.Concat(" WHERE", strWhere)
End If
MsgBox(strSQL)
View 8 Replies
View Related
Dec 7, 2001
Hi!
I need to calculate how many times stored procedure executes and keep the information for several months. What is the best way to do it if I cannot use global variables.
Thank you,
Elena.
View 1 Replies
View Related
Jul 23, 2004
When a SP errors with a deadlock, does the SP stop executing or does it fail the @@error and continue through the next steps in the stored proc?
View 3 Replies
View Related
Feb 17, 2006
Hello,
I need to execute a package from a ASP page.
SQL server and Webserver are on different machines.
It is possible?
If yes How?
Thank you
View 2 Replies
View Related
Aug 26, 2004
Hai..
I have two systems. SQL server 2000 is installed in one system (SERVER ) and the client tools are installed in a seperate system (Client)
My question is from which system should i have to execute the bcp command line utility, from the server or from the client. ?
Thanks in advance...
with regards
Sudar
View 4 Replies
View Related
Dec 13, 2005
Is there a way to tell if a stored proc has ever been executed? I've taken over another system where the developers backed up there procs by creating them with a different name. Of course there's no pattern to the naming convention either. :mad:
View 11 Replies
View Related
Mar 29, 2007
I'm having trouble with dts. I used the dtswizard to create a dts package, yet when I use dtsrun with the package name, I get a message saying that the package cannot be found. I've even copied the .dtsx file into the same folder as dtsrun and still gotten the same message. Can someone shed some light on what I might be messing up. Btw, I'd love to run the dtsrunui, but apparently that file wasn't part of my distribution.
View 3 Replies
View Related
Jan 15, 2004
Can i write a trigger to execute a SP When one row is inerted into the table.If yes,can anyone let me know the syntax.
Thanks.
View 1 Replies
View Related
May 24, 2007
Greetings.
We generate table exports on a SQL Server 2005 instance at irregular intervals. Often when exports are required we have a number of them that need to be run. We've found that the exports job run sequentially. Is there a way of simultaneously executing the jobs ?
Thanks.
alan
View 3 Replies
View Related
Nov 20, 2007
HI all
How can i execute UDF's in MS SQL SERVER 2005 can u any body plz explain me
Regards
subu
View 1 Replies
View Related
Jan 11, 2006
Hi ,
For how long is the value of Execution ID valid? Is it valid after the session also?
Can i store the value of Execution ID in some place and use it after some time/day to get the last excution done on the report?
Thanks
View 5 Replies
View Related
Apr 24, 2008
Hi,
I just installed SQL Server 2005 RS Samples to be able to create and use the Execution Log as it says on
http://technet.microsoft.com/en-us/library/aa964131.aspx#rsmnrptexpf04
but when it comes to the point that I just go and open the Solution named "Execution Log" I don't find it! I don't have it at all and neither the reports which are shown in the solution in the above link such us "Todays Reports.rdl", "Report Size.rdl" or "Longest Running Reports.rdl".
WHy is that? Is it a bug in the Samples file?
Please help me!
If someone has the reports or the entire solution I would appreciate if I could get it!
Thanks
Enrico
View 7 Replies
View Related
Sep 12, 2007
I've got a scheduled job that runs a package every couple of minutes, and I also have a method in an application that calls a stored proc that can also execute the same scheduled job. My question is what will happen if I call the stored proc to execute the job at the same time that the job is executing on its regular schedule. Is it possible that the an instance of the package will execute in parallel?
View 3 Replies
View Related
Jan 10, 2008
Hello Anybody !
I want to get the execution time of a query, I mean I will run the one sql statement like this " SELECT * FROM tblname WHERE field1 = '009' and then I want to get from my program execution time of this query. I think I just keep the sys time before run it and compare with sys time when finished it. But I don't like this one, So, can I get the execution time from sql server by running their sys s-procedure or something like.
Thanks.
View 4 Replies
View Related
Nov 16, 2001
I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:
rebuild the indexes
dbcc indexdefrag
update statistics
I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.
Any ideas would be greatly appreciated.
Thanks.
-Scott
View 1 Replies
View Related
Jan 18, 2002
Is it possible that a stored procedure runs slower when called by an application,and runs faster when executed as 'exec xxxxx' on query analyzer?
It's actually happening to us.Any clue??
thanks.
Di.
View 1 Replies
View Related
May 15, 2001
I have already created package which loads a text file to database.I wanted to execute that package based on the availability of the file using Visual Basic,Perl or VBSCRIPT whichever is easier.Please advise.Thanks
View 4 Replies
View Related
Jun 5, 2001
I want a task to run last after all of the other task have run. How do I ensure that this task will run last?
View 3 Replies
View Related
Dec 21, 2000
Hey guys,
I've got a problem here. I gave execute permission to an
user on a particular stored proc which has simple select queries.
(The tables have no select permissions for that user.) Now, the user was able to run it fine and got the results. When hdynamically creates sql queries inside the proc (Based on the input parameters) sqlserver is checking for the permissions on the tables and got permission denied message.
Is there any solution to avoid this problem?..please let me know
and i'd appreciate that..
thank you very much.
View 3 Replies
View Related
Oct 30, 2000
I wrote a stored procedure that calls a DTS package that imports a file into a SQL Server table, then executes some commands on that table. How can I tell that the DTS package has completed, so that I can continue with the next command? Here is my code:
-- Run the DTS package
EXEC @Result = msdb..sp_start_job @job_name = 'MyJob'
WAITFOR DELAY '00:00:02'
-- Call the table that the package created
SELECT * FROM <imported_table> WHERE ...
I had to insert a WAITFOR statement to make this work. Any other suggestions as to how to know when to continue?
Thank you.
View 4 Replies
View Related
Oct 16, 2000
Execution of a scheduled package fails however, direct execution of the package is successful. The package owner and job owner are the local admin account under which SQLAgent and SQLService are starting.
Ideas?
View 8 Replies
View Related
Oct 2, 2002
Hello,
How do I get quiry result in variable? exp. -
DECLARE @varVariable varchar(50),
@varFld varcahr(50),
@varSel varchar(1000)
SELECT @varVariable = (SELECT Field1 FROM Table1 WHERE Field2 = 1234)
This will give value of Field1 in @varVariable
But how I will get this value in Dynamic Execution?
It will work? -
SET varFld = 'Field1'
SELECT @varVariable = (SELECT @varSel = 'SELECT '+@varFld+' FROM Table1 WHERE Field2 = 1234')
EXEC (@varSel)
PRINT @varVariable
************************************
This will work? what is the alternative?
help please.
ANB
View 4 Replies
View Related
