Hello all.
I have a stored proc which creates a couple of temp tables, then bounces them against a production table an updates a column in the production table.
This stored proc takes about 10 minutes to run and update about 20,000 rows. If I execute each statement seperately in an ISQL window, it all runs in under 2 minutes.
Any ideas on why this is happening are GREATLY appreciated.
we just moved a database from a shared SQL 2000 server to SQLExpress on a VPS server. Every thing seems to work great, really no performance loss at all, except for one stored proc that is giving us problems. It's function is to update a history table, and then update the production table. On the old server, it would take less than a second to run this, now it takes anywhere from 45 seconds to 1 minute or it times out. This database is used on a classic asp web app. ANY help at all on this would be appreciated as I am pulling my hair out trying to figure out what's wrong here. here is the proc.
------------------------------------------ code ----------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
go
ALTER PROCEDURE [dbo].[sp_UpdatePersonalization]
@p_id nvarchar(50),
@cust_num varchar(50),
--@publication varchar(25),
@full_name varchar(500),
@email varchar(200),
@web_address varchar(300),
@include_web bit,
@ReturnAddressYN varchar(1),
@include_email bit,
@job_title varchar(500),
@company_name varchar(500),
@tagline varchar(1000),
@phone1 varchar(30),
@phone2 varchar(30),
@phone3 varchar(30),
@phone4 varchar(30),
@phoneext1 varchar(10),
@phoneext2 varchar(10),
@phoneext3 varchar(10),
@phoneext4 varchar(10),
@phonedesc1 varchar(20),
@phonedesc2 varchar(20),
@phonedesc3 varchar(20),
@phonedesc4 varchar(20),
@company_mail_address varchar(500),
@masthead varchar(250),
@verbiage_page1 varchar(1400),
@verbiage_page4 varchar(2650),
@inc_bbb bit,
@inc_ehl bit,
@inc_eho bit,
@inc_rl bit,
@p_comments varchar(500),
@p_update_flag varchar(10)
--@frequency varchar(50),
--@mail varchar(50),
--@fold varchar(50),
--@contact varchar(50),
-- do not add this on the update! @date_added,
AS
declare @last_updated datetime
select @last_updated=GETDATE()
declare @set_update bit
--if @p_update_flag='False'
--select @set_update = 1
--else
--select @set_update = 0
if @p_update_flag='False'
INSERT INTO pers_main_arch
(
p_id,
cust_num,
publication,
full_name,
email,
web_address,
include_web,
include_email,
job_title,
company_name,
tagline,
phone1,
phone2,
phone3,
phone4,
phoneext1,
phoneext2,
phoneext3,
phoneext4,
phonedesc1,
phonedesc2,
phonedesc3,
phonedesc4,
company_mail_address,
masthead,
verbiage_page1,
verbiage_page4,
inc_bbb,
inc_ehl,
inc_eho,
inc_rl,
p_comments,
frequency,
mail,
fold,
contact,
date_added,
last_updated,
start_month,
ReturnAddressYN
)
SELECT
pm.p_id,
pm.cust_num,
pm.publication,
pm.full_name,
pm.email,
pm.web_address,
pm.include_web,
pm.include_email,
pm.job_title,
pm.company_name,
pm.tagline,
pm.phone1,
pm.phone2,
pm.phone3,
pm.phone4,
pm.phoneext1,
pm.phoneext2,
pm.phoneext3,
pm.phoneext4,
pm.phonedesc1,
pm.phonedesc2,
pm.phonedesc3,
pm.phonedesc4,
pm.company_mail_address,
pm.masthead,
pm.verbiage_page1,
pm.verbiage_page4,
pm.inc_bbb,
pm.inc_ehl,
pm.inc_eho,
pm.inc_rl,
pm.p_comments,
pm.frequency,
pm.mail,
pm.fold,
pm.contact,
pm.date_added,
pm.last_updated,
pm.start_month,
pm.ReturnAddressYN
FROM pers_main pm
WHERE pm.cust_num = @cust_num
if @p_update_flag='True' OR @p_update_flag='False' OR @p_update_flag IS NULL OR @p_update_flag=''
UPDATE pers_main SET
--cust_num=@cust_num,
--publication=@publication,
full_name=@full_name,
email=@email,
web_address=@web_address,
include_web=@include_web,
include_email=@include_email,
job_title=@job_title,
company_name=@company_name,
tagline=@tagline,
phone1=@phone1,
phone2=@phone2,
phone3=@phone3,
phone4=@phone4,
phoneext1=@phoneext1,
phoneext2=@phoneext2,
phoneext3=@phoneext3,
phoneext4=@phoneext4,
phonedesc1=@phonedesc1,
phonedesc2=@phonedesc2,
phonedesc3=@phonedesc3,
phonedesc4=@phonedesc4,
company_mail_address=@company_mail_address,
masthead=@masthead,
verbiage_page1=@verbiage_page1,
verbiage_page4=@verbiage_page4,
inc_bbb=@inc_bbb,
inc_ehl=@inc_ehl,
inc_eho=@inc_eho,
inc_rl=@inc_rl,
p_comments=@p_comments,
--frequency=@frequency,
--mail=@mail,
--fold=@fold,
--contact=@contact,
--date_added,
last_updated=@last_updated,
updated_flag=1,
ReturnAddressYN=@ReturnAddressYN
WHERE cust_num=@cust_num
------------------------------------------ code ----------------------------------------------
Hi,I'm using isql to query data and output the same to a flat file.The isql has the following command options ' -h-1 -w500 -n -b -s"" '.In the SQL_CODE, the first two lines before the select statement areuse dbnameset nocount ongoWhen I run this, an additional blank line is put into the output file.Actually, there are two lines after the last result set in the outputfile. This file is being fed into another system and the blank line iscausing validation issues.How can I supress this blank line?This script is run from windows and the isql is called from a batscript.Batch script ...================================================== ========.....isql -Uuserid -Ppassword -Sserver -i"%SQL_CODE%" -h-1 -w500 -n -b -s""[color=blue][color=green]>>"%OUT_FILE%"[/color][/color]IF ERRORLEVEL 0 SET RC=0IF ERRORLEVEL 1 exit 4================================================== ========SQL code ...================================================== ========use punclaimset NOCOUNT ONGOselect * from XYZ;GO================================================== ========Your help is greatly appreciated.Yash
View 3 Replies View RelatedI am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Hello All,
I have two procedures being run one after the other.
when I run proc1 it runs for about 15 min.
Now the proc2 is dependent on proc1, when I run proc2 it runs for 45 min.
If I run both the proc's simultaneously through .net code it takes more than 1 hour. Can anyone of you tell me where would be the problem.
Thanks in Advance.
We are having issues w/ a stored proc call in our application. When I run the proc through a query window in Mgmt Studio, it comes back in .3 seconds. However, when the application runs the proc (w/ the same parameters), it takes 30 seconds for the proc to complete. When I run a trace with the proc call through Mgmt Studio, it says 4000 read, but through the app, it says 4 million reads. What is happening?? (app uses Hibernate 3.2/ Java 1.5/ JDBC)
Thanks.
We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :
sp executes spA,spB and spC
Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.
In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.
We use SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we get now rows in Crystal.
I enclose an example (with 3 sub- SPs). Help. Martin
CREATE PROCEDURE coral.qryPayEmp8_newtmp @lQryCompanyNumber int,@dtQryPeriodFromDate datetime,@dtQryPeriodEndDate datetime,@dtQryPayrollYearStartDate datetime,@szQryCompanyTaxReference varchar(30) AS
--variables used for cursors
DECLARE
@lCCUniqueID INT,
@lUniqueID INT,
@lEmployeeNumber INT,
@szEmployeeSurname VARCHAR (20),
@szInitials VARCHAR (4),
@szDeptNo VARCHAR (6),
@dtDateLeft DATETIME,
@EdTotalGrossPayThis FLOAT,
@EdTaxableGrossPayThis FLOAT,
@EdTotalGrossPayPrevious FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
@EdTaxPaidThis FLOAT,
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate FLOAT,
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),
--output from qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
@SumOfdEmployeeNIContractedOut FLOAT,
--output from qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT
SET NOCOUNT ON
DECLARE employee_period CURSOR
FOR
SELECT tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,
tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,
tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,
tblEmployees.dTotalGrossPayThis,
tblEmployees.dTaxableGrossPayThis,
tblEmployees.dTotalGrossPayPrevious,
tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,
tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate
FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)
DECLARE employee_ni_categories SCROLL CURSOR
FOR
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,
tblEmployeePeriodDetails.cNICategory
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
OPEN employee_period
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8b @lUniqueID,
@dtQryPayrollYearStartDate,
@SumOfdEmployersNICHoliday OUTPUT
EXEC qryPayEmp8c @lUniqueID,
@dtQryPeriodEndDate,
@PdTotalGrossPayThis OUTPUT,
@PdTaxableGrossPayThis OUTPUT,
@PdTaxPaidThis OUTPUT,
@PdSMPPaid OUTPUT,
@PdSSPPaid OUTPUT
OPEN employee_ni_categories
FETCH FIRST FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
IF (@@FETCH_STATUS <> -1)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8a @lUniqueID,
@szNICategory,
@dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,
@SumOfdEmployersNI OUTPUT,
@SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid,
@szNICategory AS szNICategory,
@SumOfdNIablePay AS SumOfdNIablePay,
@SumOfdEmployersNI AS SumOfdEmployersNI,
@SumOfdEmployeeNI AS SumOfdEmployeeNI,
@SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings,
@SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut
FETCH NEXT FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
END
ELSE
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid
CLOSE employee_ni_categories
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
END
CLOSE employee_period
DEALLOCATE employee_period
DEALLOCATE employee_ni_categories
RETURN
-----------------
CREATE PROCEDURE coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) = 'A',
@dtPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT OUTPUT,
@SumOfdContractedOutEarnings FLOAT OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),
@SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),
@SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),
@SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s),
@SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory = @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory
RETURN
-----------------------------
REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT = 1,
@dtQryPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT
@SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
RETURN
--------------------
CREATE PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT = 1,
@dtQryPeriodEndDate DATETIME = '1900-01-01 00:00:00.000',
@PdTaxableGrossPayThis FLOAT OUTPUT,
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET NOCOUNT ON
SELECT @PdTotalGrossPayThis = tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis = tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis = tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid = tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid = tblEmployeePeriodDetails.dSSPPaid
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))
RETURN
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedI have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedI have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedHello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
When the same code is executed in query analyzer it takes 2s instead of 20s when executing the sp. Any idea?
View 9 Replies View RelatedHi,I have a stored procedure that normalizes data from one database toanother that is included in a SQL Agent job. The job is started by atrigger so that when a table is updated, the job to normalize dataexecutes.This happens once a day in the dead of the night when nothing else ishappening and the stored procedure takes 2.5 hours to finish. Strangely,to run the same stored procedure from Query Analyzer takes only 20minutes to do the same thing. The job is executed as the same user thatlogs in to Query Analyzer.Why does running as a job take so much longer than running it manually.At the time the job is run, there is absolutely nothing else to createwaits or deadlocks. A profiler trace shows that each statement runsquickly but then it sits and twiddles its thumbs between statements.Performance monitor shows little disk activity and only 2% to 5%processor utilization.Any ideas?
View 1 Replies View RelatedWhen you create a Stored procedure, is it automatically stored under the default Filegoup?
How can I see under which Filegroup my Stored Procedures and Triggers are stored?
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
When i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds. I am the only one using the server so I know it is not due to other traffic. I have looked at Profiler and nothing looks out of the ordinary. Another observation is that the slow ones are always near eachother. I will have about 10 fast executions and then 3 slow ones and then back to fast ones. Has anyone seen anything like this before?
View 5 Replies View RelatedDear Reader(s),
Is there anyway to write the following stored procedure without the loop so that it goes much faster? :confused:
----------------------------------------------------------------------------
use MJ_ReportBase
go
if exists(select 1 from sysobjects where type='P' and name='sp_Periode')
begin
drop procedure sp_Periode
end
go
create procedure sp_Periode
@start int
, @stop int
as
declare @x int
set @x = 0
set @x=@start
delete from tbl_periode
while (@x>=@stop)
begin
-- ---
-- ---
-- Create table tbl_inout
if exists(select 1 from sysobjects where type='U' and name='tbl_inout')
begin
drop table tbl_inout
end
select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in
(select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@x),105) group by badgeid)
and badgeid>0
order by personname
-- ---
-- ---
-- Create table tbl_result
if exists(select 1 from sysobjects where type='U' and name='tbl_result')
begin
drop table tbl_result
end
-- ---
-- ---
select
convert(varchar,datetimestamp,105) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
into
tbl_result
from
WinXS..x18
where
convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@x),105)
and
accname in ('Kelder -1','Tnk Entree')
and badgeid>0
group by
convert(varchar,WinXS..x18.datetimestamp,105)
, badgeid
, initials
, personname
order by
initials
, personname asc
, convert(varchar,datetimestamp,105) asc
-- ---
-- ---
-- Rapportage tabel
insert into
tbl_periode
select
tbl_result.datum as DATUM
, ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM
, tbl_result.min as MIN
, tbl_result.max as MAX
, case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT]
, substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS
from
tbl_inout,tbl_result
where
tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas))
= convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid))
order by
tbl_result.naam asc
-- ---
-- ---
--
set @x=@x-1
end
go
print 'Klaar!'
--------------------------------------------------------------------------
What it does is determining the minimum entry time and the maximum exiting time per day of people going true the main entrance of a building.
Many thanks in advance.
:)
I am trying to find the tables and columns that depends on 'table1'sp_fkeys @pktable_name='table1'and this takes about eight seconds, whereas if I run it for finding alltables and columns that 'table1' depends onsp_fkeys @fktable_name='table1'this only takes a second.How can I speed up the first stored procedure execution?
View 2 Replies View RelatedHello,
I have a big problem with slow execution of stored procedure in SQL Server 2005 but I really don't understand the reason. I have a database with large table (about 400 million rows) and simple stored procedure to get data from that table (one select statement to select time and value columns).
Strange thing is that if I call that stored procedure from .net application (native SqlDataProvider) it takes about 6 seconds to execute but if I call the same procedure with the same parameters from within SQL Server Management Studio it takes only 25 milliseconds to execute!
I've noticed that from .net, procedure is called with binary data and in Management Studio sql script is executed so I've copied/pasted the script from Management Studio to .net code and again the same thing happens (6 seconds from .net and 25ms from Management Studio). I traced executions with SQL Profiler and everything seems to be identical for both applications except it takes much longer time for .net application.
Both SQL Server Management Studio and .net application are on the same machine and SQL Server is on another.
This is the query that when executed in Management Studio takes 25ms:
EXEC [dbo].[GetRawData] @pcu = N'DV_ZERJ_HEV1',@tag = N'MJERENO',@from = N'20070629 07:00:00',@to = N'20070629 08:00:00'
This is the same query in .net application code that takes 6 seconds to execute:
sqlCommand = new SqlCommand("EXEC [dbo].[GetRawData] @pcu = N'DV_ZERJ_HEV1',@tag = N'MJERENO',@from = N'20070629 07:00:00',@to = N'20070629 08:00:00'",sqlConnection);
sqlReader = sqlCommand.ExecuteReader();
At first I thought that Management Studio somehow caches results but if I change parameters of stored procedure it always takes less than 30ms to execute.
I really don't understand this. Please, help!
We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks
View 22 Replies View RelatedIm trying to perform an update with a stored procedure thats all working but
an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key
I understand whats going on but how do you update some ones details if its protected this way.
?
I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE) )BEGIN SET @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT @attendanceID = SCOPE_IDENTITY()ENDGO Kindly help with this
View 5 Replies View RelatedHi,
I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT,
I want to create a Geneirc search Proc that can handle a few criteria
I was wondering if it is possible to create a VB like Select case
depending on Information supplied to the stored proc
i.e
@Loc_Thing
@Loc_OtherThing
SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE
Select case @Loc_Thing
Case "Mickey"
LastName = @Loc_OtherThing
Case "Walt"
Company = @Loc_OtherThing
...... etc
is it possible to create a strored proc like this?
I have found a Select case in SQL, but it doesn't work I would like it?
Any Idea's?
Hello,
So I created my first SP today which returned data to populate a datagrid. Worked to perfection. Now I'm trying to do a simple login and I'm having a hard time getting it together. I want the user to enter in a username and pass then if user exists, to return their userid and update their last login date. But I can't get it. Any help would be awesome.
Here's my SP...
CREATE PROCEDURE [dbo].[userLogin] @username varchar(50), @pass varchar(50) AS
declare @x int
declare @userid int
if exists (SELECT userid FROM users WHERE username = @username AND password = @pass)
set @x = 1
else
set @x = 0
if @x = 1
UPDATE users SET lastlogin = getdate() WHERE userid = (SELECT userid AS name FROM users WHERE username = @username AND password = @pass)
else
return '0'
if @x = 1
SET @userid = (SELECT userid FROM users WHERE username = @username AND password = @pass)
return @userid
GO
Any constructive criticism on my SP is welcome. Also, should I be using Print or Return if I want to send back a value to my VB code?
And my VB:
Dim user As String = txtUsername.Text
Dim pass As String = txtPassword.Text
Dim objDataSet As DataSet
Dim objAdapter As SqlDataAdapter
Try
Dim cmd As New SqlCommand
'Enter Param's here
Dim myParam1 As SqlParameter = cmd.Parameters.Add("@username", SqlDbType.VarChar)
Dim myParam2 As SqlParameter = cmd.Parameters.Add("@pass", SqlDbType.VarChar)
myParam1.Value = user
myParam2.Value = pass
objAdapter = New SqlDataAdapter
objAdapter.SelectCommand = cmd
objAdapter.SelectCommand.Connection = SqlConn
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
objAdapter.SelectCommand.CommandText = "userLogin"
SqlConn.Open()
Dim str As String = cmd.Parameters("@userid").Value()
cmd.ExecuteNonQuery()
If str = "0" Then
'Error Login Page
Response.Redirect("login_err.aspx")
Else
Session("userid") = str
Response.Redirect("home.aspx")
End If
SqlConn.Close()
Catch ex As SqlException
SqlConn.Close()
End Try
I have 2 tables, lets say: Table1 and Table2.
Table1 has an ID field that is unique (PK).
Table2 has the same ID field and a date field which are both
unique (PK, FK)
I need to write a stored procedure that will query the ID
field from table1 like
Select distinct(id) from Table1
Would return:
ID
1
2
3
4
5
Now on the first of every month I want a job to run that
would create a new record for EACH ID from the above query. The new record would be created in Table2
with the ID and a date (that I will determine), resulting in:
ID DATE
1 01/01/06
2 01/01/06
3 01/01/06
4 01/01/06
5 01/01/06
The following month, after running it again, the table would
look like:
ID DATE
1 01/01/06
1 02/01/06
2 01/01/06
2 02/01/06
3 01/01/06
3 02/01/06
4 01/01/06
4 02/01/06
5 01/01/06
5 02/01/06
I have no idea how to “loop� through this record set for
each record in Table1 and in turn insert into Table2.
I have a current stored proc that creates records based on
certain criteria. One of the fields I
have is a SmallDateTime field. To
populate this from my stored proc, I have this code (for this one field).
SELECT @myLeaveDate = CAST(STR(MONTH(getdate()))+'/'+STR(01)+'/'+STR(YEAR(getdate())) AS DateTime)
This always creates a record for the 1st of the
current month. This works fine as is. After it runs I can look at the table and it
creates dates that look like the following: “2/1/2006� -Notice it doesn’t have
any minutes, seconds, etc.
Now what I need is to do something similar in another field
which is also SmallDateTime, BUT I want the date to be for the 10th day
of the following month.
I got this working using dateadd, but it also appends the minutes, seconds,
etc.
Can I call a sp from within a sp ?
Is this a fairly normal practice ?
Thanks,
Ivan