Stored Pro Run Time Erorr -2147217871(80040e31)
Oct 2, 2007
Hey guys, I need your generous heart to share something with what I am experiencing. I found the error from Sql Profiler. Here is the result of my trace:
/*w/o runtime*/
declare @P1 int
set @P1=0
exec CP_glmJournalEntry_Save 1, '110802010200', 'To record PPC-Visa transaction, $4,967.39@Php46.22
select @P1
/*w runtime*/
declare @P1 int
set @P1=NULL
exec CP_glmJournalEntry_Save 1, '210204020202', 'Sample
select @P1
May I know if the runtime came from the stored proc or on the application itself.
Moreover I want to know a lot with stored procedure. I am confusing now because there are times that runtime didn't occur. While some other times runtime occured.
I hope you'll goin to help me,, thanks in advance!
View 2 Replies
ADVERTISEMENT
Sep 27, 2007
Hi guys,
I'm having a problem wiht this: Run-time error -2147217871 (80040e31)". There are times that this error does not occur. But there are times that this error occurs. I am using a third party software. And we don't have enough support at this moment. May I know what should I do. Moreover. We are using VB 6.0 for front end. Then SQL server 2000 for back end.
Thanks
View 4 Replies
View Related
Sep 17, 2007
Hi guys,
When I thought everything is okay with this script, I got a new problem...
I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000.
in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results.
The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR:
run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors.
Thanks in advance,
Aldo.
Below the code:
Code Snippet
Function QuerySalesAging()
'--------------------------------------------------------------
'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library
'--------------------------------------------------------------
Dim ConnString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
'Setting Connection String
Driver = "{SQL Server}"
ServerName = "SERVER"
DB_Name = CompanyName
ConnString = "Driver=" & Driver & ";" & "Server=" & ServerName & ";" _
& "Database=" & DB_Name & ";" & "Uid=" & SQLLoginName & ";" & "Pwd=" & SQLPassword & ";"
'Report Criterias
Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange
' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay.
' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table
TemporaryTableName = "CTE" ' The table is a regular one
CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..."
CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName
CmdLine03 = CmdLine03 & " FROM ..."
CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")"
CmdLine03 = CmdLine03 & " ORDER BY ..."
CmdLine04 = CmdLine04 & " ALTER TABLE " & TemporaryTableName ...
CmdLine05 = CmdLine05 & " UPDATE " & TemporaryTableName ...
CmdLine06 = CmdLine06 & " SELECT ..."
CmdLine06 = CmdLine06 & " FROM ..."
ConnString.Open
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02
RecordSet.Open CmdLine01, ConnString
RecordSet.Open CmdLine02, ConnString
RecordSet.Open CmdLine03, ConnString
RecordSet.Open CmdLine04, ConnString
RecordSet.Open CmdLine05, ConnString
RecordSet.Open CmdLine06, ConnString
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02 ' The debbuger stops here:" if object_id('CTE') is not null exec('DROP TABLE CTE') "
ConnString.Execute CmdLine03
ConnString.Execute CmdLine04
ConnString.Execute CmdLine05
ConnString.Execute CmdLine06
ConnString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Cleanup & Close ADO objects
ConnString.Execute "USE master"
ConnString.Close
Set RecordSet = Nothing
Set ConnString = Nothing
End Function
View 1 Replies
View Related
Mar 6, 2008
Hi folks,
My SQL 2005 Server is acting up.
For a long while, at least a week, a particular stored procedure was working great. It was returning records just fine. Then my applicatino stated giving me the error message (after a long time of waiting) in the subject of this post.
I went to the server and opened a new query window in SQL Management Studio. From there I executed the stored procedure and it timed out. While executing the SQL server's CPU usage jumped to 100%. It bogged down the whole server!
Thinking the problem was a corrupt database or something like that, I created a copy (using the detatch/re-attach method) of that database.
I ran the same stored procedure with the same parameters on the copy and it completed execution in 0 seconds.
I then ran the stored procedure again on the original database (that was hogging 100% cpu) and it completed execution in 0 seconds. The act of coyping the database seemed to fix the problem?!?!
This is the second time this has happened. The first time I ignored it and moved on after I was able to get it to work again (not sure what I did). However as this is the second time in two weeks I've seen this behavior (I call "gremlins"), I thought I would ask the advice of my peers.
Do any of you know what might cause this behavior?
This query will recreate the table in question (note a Full Text catalog is created and the [Title] and [Description] fields are indexed.)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RSSHarvestedHeadlines](
[HeadlineID] [int] IDENTITY(1,1) NOT NULL,
[SourceID] [int] NOT NULL,
[Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Link] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PubDate] [datetime] NULL,
[GMTDateAdded] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTDateAdded] DEFAULT (getutcdate()),
[GMTLastHarvested] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTLastHarvested] DEFAULT (getutcdate()),
[HideFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_HideFlag] DEFAULT ((0)),
[PublishedFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_PublishedFlag] DEFAULT ((0)),
[EditStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_RSSHarvestedHeadlines] PRIMARY KEY CLUSTERED
(
[HeadlineID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
This is what I used to enable full text:
if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
begin
EXEC sp_fulltext_database 'enable'
end
GO
IF NOT EXISTS (select * from sysfulltextcatalogs where name = N'FTCatalog')
BEGIN
exec sp_fulltext_catalog N'FTCatalog', 'create'
END
GO
exec sp_fulltext_table N'[dbo].[RSSHarvestedHeadlines]', 'create', N'FTCatalog', PK_RSSHarvestedHeadlines
GO
exec sp_fulltext_column N'[dbo].[RSSHarvestedHeadlines]', N'Title', 'add', 1033
GO
exec sp_fulltext_column N'[dbo].[RSSHarvestedHeadlines]', N'Description', 'add', 1033
GO
exec sp_fulltext_table N'[dbo].[RSSHarvestedHeadlines]', 'activate'
GO
exec sp_fulltext_catalog N'FTCatalog', 'start_full'
GO
exec sp_fulltext_table RSSHarvestedHeadlines, 'Start_change_tracking'
GO
exec sp_fulltext_table RSSHarvestedHeadlines, 'start_background_updateindex'
GO
And last but not least, this is my stored procedure:
ALTER Procedure [dbo].[sp_RSSHarvestedHeadlines_FullTextSearch]
(
@ORKeywords varchar(4000) = 'xxxx',
@ANDKeywords varchar(4000) = 'xxxx',
@NOTKeywords varchar(4000) = 'xxxx',
@SourceID int = -1,
@IsHidden bit = null
)
As
set nocount on
SELECT HHL.HeadlineID,
HHL.Title,
HHL.Link,
HHL.[Description],
HHL.PubDate,
HHL.GMTDateAdded,
RSSSources.SourceTitle,
RSSSources.SourceLink
FROM RSSHarvestedHeadlines HHL
INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID
WHERE HHL.PublishedFlag = 0
AND (@IsHidden is null OR HHL.HideFlag = @IsHidden)
AND (@SourceID = -1 OR HHL.SourceID = @SourceID)
AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords)))
AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords)))
AND (
@NOTKeywords = 'xxxx'
OR (
(NOT CONTAINS(HHL.Title, @NOTKeywords)
AND
NOT CONTAINS(HHL.Description, @NOTKeywords))
)
)
ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC
This is the query that I execute in a query window in order to test. This caused the server to go to 100% for a good 14 seconds to get results (but after the copy it now returns results instantly)
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_RSSHarvestedHeadlines_FullTextSearch]
@ORKeywords = N'test',
@ANDKeywords = N'xxxx',
@NOTKeywords = N'xxxx',
@SourceID = -1,
@IsHidden = 0
SELECT'Return Value' = @return_value
GO
Note: running this qeury returns results instantly... (even when it was hanging)
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_RSSHarvestedHeadlines_FullTextSearch]
@ORKeywords = N'xxxx',
@ANDKeywords = N'xxxx',
@NOTKeywords = N'xxxx',
@SourceID = -1,
@IsHidden = 0
SELECT'Return Value' = @return_value
GO
Any help or advise will be appreciated.
View 1 Replies
View Related
Feb 28, 2008
hi
below is my procedure but when i execute this i m gettin g following error
Syntax error converting the varchar value 'empbankdetailsBACS' to a column of data type int.
what is problem
-- EXEC sp_GetPaymentMethod 0
CREATE PROCEDURE sp_GetPaymentMethod
@PaymentMethodID int = 0
AS
BEGIN
SET NOCOUNT ON
SELECT
PaymentMethodID,
PM.Descn,
'empbankdetails'+ PM.Descn as 'Descn2',
DateCreated,
LastModified,
0 as 'Value'
INTO
#tmpPaymentMethodGlobalSettings
FROM
t_PayrollPaymentMethod PM, t_GlobalSettings GS
WHERE
PaymentMethodID = 0 OR 0 = 0
ORDER BY
Descn
Declare @SqlString1 varchar(1000)
Declare @SqlString2 varchar(250)
Set @SqlString2 = ''
Declare @Payment integer
Declare @Descn varchar(50)
Declare Payment Cursor for
Select PaymentMethodID
from #tmpPaymentMethodGlobalSettings
Open Payment
FETCH NEXT FROM Payment into @Payment
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @Value int
Select @Descn = Descn2 from #tmpPaymentMethodGlobalSettings where
PaymentMethodID = @Payment
--Set @SqlString1 = '
--Declare @Value int
Select @Value = @Descn from t_GlobalSettings
Update #tmpPaymentMethodGlobalSettings
Set Value = @Value where PaymentMethodID = Convert(varchar(10),
@Payment)
-- exec (@SqlString1)
FETCH NEXT FROM Payment into @Payment
END
CLOSE Payment
DEALLOCATE Payment
Select * from #tmpPaymentMethodGlobalSettings
--Drop Table tmpPaymentMethodGlobalSettings
IF @@error <> 0
RETURN(0)
ELSE
RETURN(1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 7 Replies
View Related
Aug 30, 2006
Hi All,
I installed SQL Express Advanced Services, and when i try to create Data Base or any connection, it shows me this message..http://www.unlimitednetwork.net/data/sql_express_error.JPG
I don't know what to do , and what does it mean that SQL Express requier to function properly!!!!!!!!
Any Help.
Thanks.
View 1 Replies
View Related
May 2, 2001
My DTS package fails with the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete. Process Exit Code 9. The step failed.
What does Exit Code 9 means?
View 1 Replies
View Related
Jun 1, 2007
I have the following problem in a Win 2000 Server + SQL Server 2000environment and I hope somewhat can help me to resolve it (after manydays of useless attempts I am desperate).In my database I have two table:- master(id, field1, field2, ...)- detail(id0, id, progr, data, sede, esecutori, brani_autori)in a master-detail relation with "id" as foreign key.The fields of the "detail" table are:- id0: uniqueidentifier, primary key (newid() IsRowGuide=Yes);- id: uniqueidentifier, foreign key;- progr: bigint, Identity=Yes;- data: smalldatetime;- sede: varchar (100);- esecutori, brani_autori: text.In certain situations, in my asp site, I have to make a copy of a recordof "master" with all the linked record of the "detail" table.The code I've written to realize this task has been tested in manysimilar situations and has always worked fine (it is reported on the endof mail).With the two table above I have this strange behavior: when I attempt todo the copy of linked records in the "detail" table (using an "Insert"query), some records are correctly inserted, whereas for few otherrecords the Conn.Execute of the "Insert" query don't go and I receivethe message:Microsoft OLE DB Provider for SQL Server error '80040e31'Timeout expiredAfter many attempts I've reached these conclusions:1.It isn't the situation described in this faq:http://www.aspfaq.com/show.asp?id=2287because also using the IP for the "Data Source" the situation is thesame; on the other hand I have the problem also when there is only onerecord to copy in the "detail" table, so the problem is not the durationof the query.2.It isn't due to the contents of the record (at least not directly): if Isubstitute the contents of one of this records with simple text theerror persists, if I create manually a new record in "detail" and put init the data of the indicted record it is copied normally. So the problemseems to be the record itself and not its contents.3.The insert query work normally if I execute it from the Query Analizer.4.The problem seems to be due to the fact that, when I have a recordsetobject open on the table and pointed to one of this records, SQL Serverblocks the table and don't permit new insertion; in fact if I executethe same Insert query out of the code where the recordset object is openit works.Finally if the problem is the one of the point 4, I don't know thereason of this behaviour and how to resolve it.So, please, help me because it is of great importance for my work!Many, many thanksTonio Tanzi*** Code of the copy procedure ***....old_id_master= 'the id of the master record to copynew_id_master= 'the id of new master record (copy of the above)strsql="Select * From detail where id='" & old_id_master & "'"set rs=Conn.Execute(strsql)do while not rs.Eofstrsql="Insert Into detail (id, data, sede, esecutori, brani_autori)"& " Values ('" & new_id_master & "','" & data & "','" & _sede "','" & esecutori & "','" & brani_autori & "')"Conn.Execute(strsql)rs.movenextlooprs.closeThis code works good for the "good" records, don't words for the "bad"records, but if I force an insert for a "bad" record before or after thedo while-loop (i.e. when the rs is not pointed on a "bad" record) it works.
View 4 Replies
View Related
Mar 10, 2007
after its saying 100% installed its doing the next error:
http://img171.imageshack.us/img171/3786/8847ig0.gif
please help!
btw here's the log file
################################################## #############################
Starting Service ...
Hebrew_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1084)
SQL Server configuration failed.
################################################## #############################
17:04:32 Process Exit Code: (-1)
17:04:40 Setup failed to configure the server. Refer to the server error logs and C:WINDOWSsqlstp.log for more information.
17:04:40 Action CleanUpInstall:
17:04:40 C:WINDOWSTEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service SQLSERVERAGENT
17:04:40 Process Exit Code: (1060) the currect service are not found as installed service.
17:04:40 C:WINDOWSTEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service MSSQLSERVER
17:04:40 Process Exit Code: (0)
17:04:40 StatsGenerate returned: 2
17:04:40 StatsGenerate (0x80000000,0x1,0xf000000,0x200,1033,303,0x0,0x1,0 ,0,0
17:04:40 StatsGenerate -1,Administrator)
17:04:40 Installation Failed.
View 2 Replies
View Related
Oct 30, 2007
Hi All,
we are working on a web application created in ASP & SQL 2000 environment.
In the code , we have a single connectionstring to connect database.
The application was running fine for the past 3 years, but all of a sudden we are getting "timeout expired" error only in someparts of the application but not on all database access.
The code which raises this error is a bit large which need to look into table of 8k recs with more conditions.
The same sql query took 1:01 minutes to execute in query qnqlyser.
I changes the script timeout to 900secs,
I herd we need to upgrade MDAC or we need to change connection timeout or we need to cahnge the code.
we cannot change the query, it is the most possible way we could get that.
Can somebody help me with the possible solution.
Thanks & Regards,
Sai. K.K
View 5 Replies
View Related
Jun 11, 2015
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Jan 30, 2007
I am trying to call a stored procedure from an asp.net application. If I execute the stored procedure in Query Analyzer it takes 1 second to execute, but when I am trying to call it from c# code it times out.
Ideas?
Thanks in advance.
View 4 Replies
View Related
Feb 18, 2000
Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.
Thanks in Advance,
Bye,
Madhuker.
View 1 Replies
View Related
Jan 12, 2000
How can I compile all the stored procedures in the database at one shot?
thanks in advance
Pranav
View 2 Replies
View Related
Aug 24, 2000
I have a sotred procedure using a cursor which sort data and create subsets based on same oid and same decision_date columns, for each subset I am trying to order them and affect values 1, 2,... for each record in a different subset.
The stored procedure seems to work very well and fast against a small tables (during tests). When used against a table with 200,000 records it takes more than 24 hours...
I am looking for a help to make it work faster, thanks guys.
here is the stored procedure:
CREATE PROCEDURE ORDERING_TEST_PROCEDURE AS
DROP TABLE REVIEWS_TEST_TABLE
SELECT OID,DECISION_DATE,DECISION_ID,VOTES_REQUIRED, ORDERING INTO REVIEWS_TEST_TABLE FROM DECISION_FLAGS
WHERE VOTES_REQUIRED IN ('1','2','3') and FINAL_DECISION_CODE NOT IN ('0800','0810','0840') and DECISION_TYPE_CODE < '0100'
ORDER BY OID, DECISION_DATE,VOTES_REQUIRED DESC
CREATE INDEX OID_DECISIONID_DATE_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID, DECISION_DATE, OID)
CREATE INDEX VOTESREQUIRED_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (VOTES_REQUIRED)
CREATE INDEX DECISIONID_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID)
declare @oldoid varchar(12)
declare @olddecision_date varchar(75)
declare @oid varchar(12)
declare @decision_date varchar(75)
declare @Decision_id varchar(12)
declare @ordering varchar(1)
declare @ordering_count int
declare @votes_required varchar(12)
set @oldoid = 'space'
set @olddecision_date = 'space'
set @oid = 'space'
set @decision_date = 'space'
set @votes_required='space'
set @Decision_id = 'space'
set @ordering = '0'
set @ordering_count = 0
declare review_test_cursor cursor for
select oid,decision_date,votes_required,ordering,decision _id from CRIMS.dbo.reviews_test_table
order by oid,decision_date,votes_required asc
open review_test_cursor
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
while (@@fetch_status = 0 )
begin
if @oldoid <> @oid or @olddecision_date <> @decision_date
begin
set @oldoid = @oid
set @olddecision_date = @decision_date
set @ordering_count=0
end
update reviews_test_table
set ordering = CAST ((@ordering_count + 1) as VARCHAR)
where decision_id = @Decision_id
set @ordering_count = @ordering_count + 1
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
end
close review_test_cursor
deallocate review_test_cursor
/*********************************/
UPDATE DECISION_FLAGS
SET ORDERING = '0'
UPDATE DECISION_FLAGS
SET DECISION_FLAGS.ORDERING = TEM.ORDERING
FROM DECISION_FLAGS DEC, REVIEWS_TEST_TABLE TEM
View 4 Replies
View Related
Oct 5, 1999
I'm having problems testing the effectiveness of changes I'm making to a stored_procedure. I need to test the runtime, but each time I run the sp, it gets progressively faster because the data has already been cached. Is there a way for me to clear the data from cache before each run? I can't stop SQL Server.
View 2 Replies
View Related
Jun 24, 2008
I am trying to test the connection pooling of my app and need to artificially increase the execution of a stored procedure to 10-15 seconds. There aren't any big tables in my sample data to select on. What would be the easiest way to make a stored procedure fun for a few seconds?
Thanks.
View 6 Replies
View Related
Mar 7, 2008
Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help?
Thanks,
Anne.
View 19 Replies
View Related
May 3, 2008
help-take a long time run this stored procedure 15 second
Code Snippet
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,@NumDays TINYINT,@myNum TINYINT
set @myNum=4
SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
select @WantedDate
IF @NumDays = 28
BEGIN
SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,
CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis
FROM dbo.v_hasot_all INNER JOIN
dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int
WHERE (NOT (dbo.v_hasot_all.nameday IN (29, 30, 31)))
END
ELSE IF @Numdays = 29
BEGIN
SELECT ...............
WHERE (NOT (dbo.v_hasot_all.nameday IN ( 30, 31)))
END
ELSE IF @Numdays = 30
BEGIN
SELECT ......................
WHERE (NOT (dbo.v_hasot_all.nameday IN (31)))
END
ELSE IF @Numdays = 31
BEGIN
SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,
CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis
FROM dbo.v_hasot_all INNER JOIN
dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int
END
and whan i run my code separately it run fast
i think it is the ELSE IF @Numdays
TNX
View 5 Replies
View Related
Sep 14, 2004
G'day everyone...
I have two tables: Category and Product.
CategoryID is the Foreign Key of Product table.
I want to update those tables at the same time using stored procedure.
It will work like this.
If I update the Display column of Category table to have the value of 0, it will also update any records in the Product table -that has the same CategoryID- to have the value of 0 in its Display column.
I just read something about trigger and bit gives me idea that I might use it for this case, but don't know how to write the SQL.
Can anyone please help me...
Thanks...
View 2 Replies
View Related
Apr 25, 2001
Hi
I have many existing tables within my db with the date format mm/dd/yyyy
Is it possible to run a stored procedure in order to convert the EXISTING records to the date format dd/mm/yyyy?
Thanks
View 2 Replies
View Related
Jul 14, 2000
I have created a DTS package. I want to know how to call/run this package from a stored procedure.
Please let me know how to call this DTS from a Stored Procedure.
Thanks.
-Rajesh
View 1 Replies
View Related
Jul 21, 2000
Hello all,
We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.
So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?
Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!
Kevin Kraus
View 2 Replies
View Related
Aug 2, 1999
I have a stored procedure that normally takes about 5 hours to complete:
DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())
tblX has about 55 million records and has an index on PROC_DT.
I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.
View 4 Replies
View Related
Nov 12, 2004
Hi there,
SQL newbie here, and thanks for any help you may able to provide.
My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday.
I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it.
Thanks again.
View 4 Replies
View Related
Oct 6, 2005
Hi
I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.
What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.
Thanks.
View 1 Replies
View Related
Dec 6, 2007
Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYT
View 14 Replies
View Related
Jul 17, 2015
I have a sp that was taking very little time (about 34 sec). But suddenly is stacked. It is running and running and running but not LOCKED neither SUSPENDED. It is always RUNNABLE. I have made Index and statistics optimization but nothing. I looked into execution plan but everything seems ok. All the time is in 3 indexes that are Index Seek and not Table Scan!!! So why is stacked... I do not know how much time it takes because I have to stop it. (SQL SERVER 2008 R2, the database was migrated from SQL SERVER 2000)
View 6 Replies
View Related
Jul 18, 2007
Hello,
I have a sqlclr stored procedure that works well if started via execute in SSMS. (The stored procedures makes a SSRS web service call.) When I install a trigger on a database table to execute the stored procedure
create trigger NewWdsStatsEntry on dbo.WDSSTATS after insert
as execute UpdateReportExecutionSnapshot N'http://localhost/ReportServer/reportservice2005.asmx', N'/Report1';
go
and add something to the table, I get the following error:
Msg 6522, Level 16, State 1, Procedure UpdateReportExecutionSnapshot, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "UpdateReportExecutionSnapshot":
System.Net.WebException: The request was aborted: The operation has timed out.
System.Net.WebException:
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at UserProcedures.ReportService2005.ReportingService2005.UpdateReportExecutionSnapshot(String Report)
at StoredProcedures.UpdateReportExecutionSnapshot(String url, String reportPath)
.
The statement has been terminated.
The insert times out after 100 seconds and no row has been added to the table, but the web service call actually created a new report! Any idea why the trigger can't execute the stored procedure?
Thanks!
werner
View 3 Replies
View Related
Apr 30, 2008
Hello,
If i want to optimise stored procedure response time.
Which things i need to be considered?
Thanks in advance.
View 3 Replies
View Related
Feb 21, 2002
HI,
I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
Any feedback would be appreciated!
Thanks in advance...
View 2 Replies
View Related
