Performance Of Procedures Executed By SQLAgent Jobs
Dec 15, 1999One particular SQL stored procedure executes 25 times more slowly when invoked by an SQlAgent job than when executed directly. Any suggestions?
View 4 Replies
ADVERTISEMENT
Transferring SQLAgent Jobs
Jun 5, 2001If you are transferring to a new server is there a good way to transfer SQLAgent jobs in bulk. Scripting out and in is one way, is there a better way
Jim
SQLAgent Jobs Email Notification
Jun 24, 2004This sends a success or fail status and length of execution time can it be extended to send error details when it fails
View 1 Replies View RelatedSQLAgent - TSQL Jobs Hangs (SQL Server 2005)
Aug 15, 2007
I have two tables
t_DTM_DATA_STAGING around 2 million records
t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR]
,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME]
,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM])
SELECT
t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
Unable To View Jobs Even After Adding Users To MSDB Roles SQLAgent...
May 20, 2008Developers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
Jobs Executed In The Past
May 16, 2008How to find the jobs executed in the past for a perticular period.
e.g. jobs ran between 2-3pm in the past.
I am using sysjobhistory but not really able to use column run_time
------------------------
I think, therefore I am - Rene Descartes
How Stored Procedures Are Executed
Apr 20, 2007I was wondering the way in which stored procedures are executed.
For example if I had a stored procedure (A) that executed a sub stored procedure (B).
And two different users where to execute the Stored procedure at the same time what would happen.
Would the second user have to wait while the first user had finished (A and B), dose each user get a copy of the stored procedure or can one user be running stored procedure (B) whilst the other is running stored procedure (A).
Performance Difference Between Query Executed Through ASP.NET And SSMS
Sep 18, 2007I have also posted this in microsoft.public.sqlserver.programming.
I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.
The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.
However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.
Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.
I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.
The query is of the form:
WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @minRow AND Row < @maxRow
Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).
This is running against SQL Server Developer Edition.
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.
Jun 7, 2006Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?
Thanks you!
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.
May 7, 2008Hi mister, I have this script sql but I get this error:
Mens. 557, Nivel 16, Estado 2, Línea 1
Only functions and extended stored procedures can be executed from within a function.
DROP FUNCTION ObtenerTablaPorNombre2
GO
CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]
FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla
SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla
--EXEC (@cmd)
SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'
SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '
EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla
RETURN
END
GO
SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function
Sp_fetch Executed A Lot Of Times, Decreasing Performance - ASP + SQL 2005
May 23, 2008Hey,
In a ASP application, which used Access and is now being migrated to SQL 2005, I have a medium-sized query with many inner joins. I optimized the tables, with indexes, etc, and if I run the query on the SSMS it returns me like 55 lines in less then a second. On the other hand, when the ASP page passes the query to the SQL 2005, it takes 10 sec. to get the data. Using the Profiler I found that the sp_fetch is being executed a lot of times. It looks like the query is decomposed in smaller pieces and the rows as selected one by one. I'm using OleDb.
How to make the ADO (the culprit in my opinion) to execute that query at once?
Thank you.
Transact SQL :: Only Functions And Some Extended Stored Procedures Can Be Executed From Within A Function
Sep 11, 2013I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
Creating CSV Files Using BCP && Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@
Jan 24, 2008Hi all,
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
=========================================================================================
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
===================================================================
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
Thanks in advance,
Scott Chang
Jobs,DTS & Stored Procedures
Sep 26, 2000I have a quick query for all you experts!
I have set up a lot of .bat & dts & stored procedures which have to be run in a sequence.I wish to Automate this "Sequence" but am a little lost on how to do it. Would anyone recommend how to encapsulate all the events into a DTS or A stored procedure or simply have a "Job" to run this?????
All suggestions are welcome!
Jobs - Unexpected Slow Performance
Mar 7, 2003DECLARE @num int
SET @num = 0
DECLARE @tableVariable table(ColA int, ColB decimal(18,4))
WHILE @num < 1000
BEGIN
INSERT INTO @tableVariable VALUES (2, 10.56)
SET @num = @num +1
ENDWhen this code is run in SQL Server 2000 Query Analyzer it commits in less than 1 second.
The same code run as a SQLServerAgent job takes 16 seconds.
Similar behaviour appears if INSERT statement is substituted with UPDATE.
SELECT statement runs equally nice on both alternatives.
Has anybody got an idea what might be the reason for slow execution of INSERT and UPDATE in a job?
Thanks,
teetjott
Jobs Running Stored Procedures Stall In Randon Places
Nov 24, 1999Developers have complained that some of their jobs are taking too long to
run. I used Profiler to trace one really bad performer and could see that
the code was making one pass through its loop in about 7 seconds. I found
one select statement that was using the majority of the time, plugged it into
query analyzer and looked at the execution plan. It was using an index but
not very efficiently. I then ran it through index analysis and it
recommended a new index. This was cool! The new index helped.
But, the job continued to run very, very slowly - over a week before it died!
I ran another trace, and it was hanging for 10+ seconds in random locations.
Sometimes it would be on a database call, sometimes it would be on a simple
SET @variable = value statement. There was no pattern. According to the
sys admin, the server itself did not look taxed. It does not appear to be a
locking problem because of the random location of the stall. The application
and server are only lightly used at this point, so I would hate to see how
bad it could be if it was busy.
I am still fairly new to SQL, though I have years of DBA experience. I would
appreciate any ideas - especially the obvious things that I am probably not
considering. I have logged a call with Microsoft but they haven't gotten
back with an answer. I sent them a trace file and a perfmon file, neither
of which showed them anything.
Thanks!
Same DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer
Mar 13, 2002I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
May 26, 2008Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
May 15, 2008i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
Help With Stored Procedures (low Performance)
May 27, 2004Hi, im developing an action site and im having proplems with stored procedures
that are causing extreme load on the server.
PLEASE LOOK AT THE CODE AND TELL ME IF U SEE ANYTHING THAT CAUSES LOW PERFORMANCE OR SOME SUGGESTIONS ON HOW TO INCREASE THE PERFORMANCE.
SHOULD I F.E. TAKE THIS LOGIC FROM THE DB AND TRY DOING IT IN c#?
The action-site uses an auto-bid-feature that is causing a load.
Basically when a user makes a bid, i have to loop through all other users that have a bid in
the product and automatically increase a bid untill it matches the highest bid of a user.
F.e. a user bids auto-bid $2000 in a product where another user has a $2100 bid in the product and the current bid is $1200. So the SP loops through the users increases the bid-history untill it mathces the highest bid of a user.
- $1250
- $1300
- $1350
- etc...
This is the SP that loops through the users and calls another function each time to increase the bid in the bid-history.
------------------------------------------
CREATE PROC HaekkaBodIUppbodi
@uID INT
AS
DECLARE @curUpphaed DECIMAL
SELECT @curUpphaed = MAX(upphaed) FROM bodsaga WHERE uppbod_ID = @uID
IF(@curUpphaed IS null)
BEGIN
SET @curUpphaed = 0
END
DECLARE @curCount INT
SELECT @curCount = COUNT(kaupandi_netfang) FROM bod WHERE upphaed >
@curUpphaed AND uppbod_ID = @uID
WHILE(@curCount>0)
BEGIN
-- skilgreinum notanda breytu
DECLARE @notandi NVARCHAR(255)
IF(@curCount<2)
BEGIN
SELECT @notandi = kaupandi_netfang FROM bod WHERE upphaed >
@curUpphaed AND uppbod_ID = @uID
EXEC HaekkaBodAVoru @uID, @notandi
BREAK;
END
-- búum til cursor fyrir alla notendur
-- sem eru með hærri boð en núverandi boð
DECLARE crs CURSOR FAST_FORWARD FOR
SELECT kaupandi_netfang FROM bod WHERE upphaed > @curUpphaed
AND uppbod_ID = @uID
ORDER BY dags
-- opnum cursorinn
OPEN crs
-- sækjum fyrsta notandann inn í @notandi
FETCH NEXT FROM crs
INTO @notandi
DECLARE @returnValue INT
SET @returnValue = 0
DECLARE @tempValue INT
SET @tempValue = 0
-- lúppum í gegnum notendurna
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC HaekkaBodAVoru @uID, @notandi
FETCH NEXT FROM crs
INTO @notandi
END
CLOSE crs
DEALLOCATE crs
-- sækjum hæstu upphæð í boðsögu
SELECT @curUpphaed = MAX(upphaed) FROM bodsaga WHERE uppbod_ID = @uID
SELECT @curCount = COUNT(kaupandi_netfang) FROM bod WHERE upphaed >
@curUpphaed AND uppbod_ID = @uID
END
GO
-------------------------------------------------------------
And here is the SP that increases the bid...
CREATE PROC HaekkaBodAVoru
@uID INT,
@notandi NVARCHAR(255)
AS
-- Hækkun á uppboði
DECLARE @haekkun DECIMAL
SELECT @haekkun = haekkun_upphaed
FROM uppbod WHERE id = @uID
-- Hæsta boð í vöru
DECLARE @haesta_bod DECIMAL
SELECT @haesta_bod = MAX(upphaed) FROM bodsaga
WHERE uppbod_id = @uID
-- Hæsta boð sem notandi vill gera
DECLARE @haesta_bod_notanda DECIMAL
SELECT @haesta_bod_notanda = upphaed FROM
BOD WHERE kaupandi_netfang = @notandi
-- Seinasti notandi sem gerði boð í uppboðinu
DECLARE @seinasti_notandi NVARCHAR(255)
SELECT @seinasti_notandi = notandi FROM
BODSAGA WHERE uppbod_id = @uID
DECLARE @upphaed DECIMAL
SET @upphaed = @haekkun
-- Ef búið að bjóða í vöruna
IF(@haesta_bod IS NOT null)
BEGIN
IF(@haesta_bod = @haesta_bod_notanda)
BEGIN
SET @upphaed = @haesta_bod
END
ELSE
BEGIN
SET @upphaed = @haesta_bod + @haekkun
END
END
-- Ef hæsta boð notanda er sama eða lægra en núverandi verð
-- og notandi var ekki sá síðasta til þess að setja inn boð
IF(@upphaed <= @haesta_bod_notanda)
BEGIN
-- Ef notandi hefur EKKI sett inn þessa upphæð áður...
IF((SELECT COUNT(*) FROM bodsaga WHERE notandi = @notandi AND upphaed = @upphaed)=0
-- Og notandi á EKKI núverandi hæsta boð
AND (SELECT COUNT(*) FROM bodsaga WHERE notandi = @notandi AND upphaed = @haesta_bod)=0)
BEGIN
INSERT INTO bodsaga
VALUES(@uID, @notandi, @upphaed, GETDATE())
RETURN 1
END
END
RETURN 0
GO
PERFORMANCE OF STORED PROCEDURES
Jul 31, 1998I`ve written a pretty complex powerbuilder application and my performance
is fair use stored procedures. Is there any way to force sql server 6.5
to reevaluate the current query plan and if it is bad create a new
query plan.
Stored Procedures With Some IFs, Performance Issues
Jul 20, 2005Hello folks,I usually use this way to store more procedures inside a single SP:CREATE PROCEDURE usp_MyProc(@usp_mode int)ASIF @usp_mode = 1BEGINENDIF @usp_mode = 2BEGINENDand so on...My question is about performance: I don't know deeply how SP executionruns, is there a performance fall following this way? Do you haveother methods or suggestion instead of this way to avoid having dozensof procedures inside my database (obviously I use this method becauseI have many procedures and I prefer a more compact view andorganization)?TIA, tK
View 3 Replies View RelatedPerformance Problem With Views And Stored Procedures..
Nov 8, 2000Hello Everybody,
I posted this same question couple of times in the news groups but no answers. I have a 2 tables and i am doing a union query using a view. each has 250 rows. The query takes 20 seconds to return the results. no joins or anything. the create view simply looks like this:
create view myview as
select id, name from table1
union
select id,name from table2
Where as if i write a stored procedure like below, it returns the rows in 4 seconds.
create table #mytable
( id int, name varchar(30))
insert into #mytable (id, name) select id, name from table1
insert into #mytable (id, name) select id, name from table2
select id,name from #mytable.
I prefer doing in the view since both returns the same result. I tried running dbcc, update statistics. but no luck. Can anyone please help me in this issue.
Thanks
Ramesh
Performance Of Extended Stored Procedures In SQL Server 2000
Jul 23, 2005What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
View 1 Replies View RelatedPerformance Comparision - Stored Procedures VS SQL (Inside SSIS)
Mar 23, 2007I am working on a technical design of data integration ETL package which will be moving data from SQL Server Source to DB2 destination. I currently have two options, when moving data to DB2(IBM AS400). I can call a AS400 Stored Procedure, and pass in the data to the stored procedure, and perform the insert processing within the AS400 environment or I could do inserts from SSIS in a DFT and write individually to AS400 tables. My question is from a performance and good practice perspective, which method should I move forward with. I need a possible list of pros- and cons when using AS400 Sproc vs using SQL within SSIS? I would really appreciate response from individuals who have done something similar in the past. Thanks a lot and I am really looking forward to responses.
SQLAgent
Feb 11, 2001Hi:
I hope somebody can give me a clue for this problem. Our SQL Server was installed with default option. The SQL Server account is .SQL-Cmdexec. I tried to create two backup jobs to save daily backup files into another server. The SQL Server could not detect that server drive during the database backup set up. I knew that was because the SQL Agent account could not access to that server. I had the following questions:
1. How to grant write right to current SQL Agent account to another backup server?
2. It looks like .SQL-cmdexec is created by SQL Server default installation. If I try to change this account to another account, the Enterprise Manager is the only place to be changed or there is another place keeps SQL Agent account info.
3. If I try to give SQL Agent account access to the other servers, what is the relationship between NT User manager and SQL Agent accounts.
Thanks.
Joan
Sqlagent.out
Dec 19, 2005Hello,I have the following error message in the sqlagent.out file, loopingeach minute.2005-12-19 10:58:54 - ! [298] SQLServer Error: 14262, The specified@job_id ('254D5C3B-CB1F-4B02-AD79-FF5AFE343E3B') does not exist.[SQLSTATE 42000] (ConnExecuteCachableOp)I restarted the sqlagent and ms-sqlserver service, but did not fix theproblem.ThanksDiane Lavoie
View 5 Replies View RelatedSQLAgent Can't Up
Jul 20, 2005I have two questions:(1)After I stopped SQLAgent, I couldn't start it up, even restart theserver.Why?(2) I may need to reinstall the server, if I do, do I need touninstall? or just override the current one?Thanks!Saiyou
View 1 Replies View RelatedSQLAgent Login
Aug 17, 2000SQL Server name: BOProd
Domain: BODomain
I have an NT User 'SQLAgent' with domain Admin previleges.
SQL server has 'SQLAgent' user with NT authentication and system admin previledges.
I am in process of setting up mail services for sqlmail and sql agent mail.
As a part of this process, when I log on to the server computer as SQLAgent, it doesn't let me access Enterprise manager saying
'Login could not be established to BOProd-Login failed for user 'BODomainSQLAgent'. It does allow me to connect to query analyser by providing 'user name/password'.
Any idea why connection to enterprise manager fails?
SQLAgent Not Starting
Feb 12, 2002The SQLAgent service was modified to login as a domain account. SQLAgent would not start. I made sure that the account was in the Admin group on the server, I setup the account as a System Administrator in SQL. I made sure that the network protocols in Client Network Utiliity and Server Network Utility where the same as other servers using the domain account. The message I'm getting is...
SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).
I modified the properties for SQLAgent back to login as local account and I still get the same problem.
Any suggestions would be appreciated.
Sidney Ives
Database Administrator
Sentara Healthcare.
SQLAgent Crash
Jul 20, 2005Hi all, I have some trouble with SQLAgent...when I try to schedule a jobthat invokes a script (vbs, java, cmdshell,...), SQLAgent crashes at themoment of scheduled execution. If I try to execute it manually, all worksfine. This installation runs on a cluster Win2003 and the node initiate afailover if I don't delete the job and then restart Agent (I can't start theservice before deleting that job!!!). Event viewer logs something like:"LOG] Exception 5 caught at line 191 of file ..src
efreshr.c.SQLServerAgent initiating self-termination".I've already tried to debug the service but my dump did not help me.Any suggestion?Thankscamau
SQLAGent Error
Nov 12, 2007Hi,
Can someone explain me this error I encountered, is this an application error or script error? How can i resolve
this one?
"The description for Event ID ( 208 ) in Source ( SQLAgent$TSESMSDBS ) 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: [0230] GET MA_SMSCODE, 0x523D92CBAACD304E88CF720580B18357, Failed, 2007-11-10 02:30:00, The job failed. The Job was invoked by Schedule 2 (MA_SMSCODE - Every 2.30AM). The last step to run was step 5 (NSCSI - MODEL SMS SHORT CODE).."
Hope to hear from anyone as soon as possible.
Thanks.
Russell
Assistance With SQLAgent
May 9, 2008All,
I have a SQLAgent job created using isqlw to run the query and output the results to a file on the C drive. However, the job never completes, which is strange since the database is fairly new and when I run the query manually in a isqlw window, it completes and outputs the file in seconds.
Any ideas where I'm failing here?
Thanks in advance,
JB