One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
View 6 RepliesIs there any way to call a DTS package from within a stored procedure?
EXEC(dtsrun <myDTSpackage>)
Thank you.
I have a stored procedure in SQL Server 2008 (the stored proc is actually stored there with a name) and I can run it with the 'exec storedproc_name insert_date' command (my stored proc needs a date to run).
The stored proc just creates a temp table with some data (but we can ignore this bit).
I only need a way to run this stored proc remotely (I dont care about getting the data, I just need to run it in the server).
Is there any way of doing this? Preferably via a unix system? I just need a way to run the 'exec' command. Returning data etc. isn't needed.
How does one prevent a long running procedure form crapping out in CLR?
I am trying to do a pull from a distant data source and it works, except I have to break down my stored procedure call into several smaller calls. I would like to do everything in one shot, but I get the thread abort exception when I try to get a lot of data.
Any ideas?
Thanks.
hi,
im getting an error when i run the stored proc with a string parameter in execute sql task object.
this is the only code i have:
exec sp_udt_keymaint 'table1'
I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.
How do i do this right?
cherrie
Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?
Thanks!
For reasons beyond the scope of my question, is there a way to run this command within a Stored Procedure from a low privileged user login? I can grant the entity "db_ddladmin" privilege and the proc runs, but I'd rather not give out that level of permission to what is basically a glorified web access login.
View 5 Replies View RelatedPlease forgive the simplicity of this question - I am not the dba type. When I connect to a server and look at my connection attributes in activity monitor, the user column shows the correct information for my domainusername. When I run a certain stored procedure in that connection, the domainusername changes to another person. We are not using execute as, setuser, or anything special to explicitly change the user. The stored procedure is in a schema that is owned by dbo (principal_id = 1 - I verified by checking sys.database_principals.)
Does anyone have any suggestions?
Thanks in advance,
John Hennesey
Ok,
here's a funky one That I can't find an expanation for. If I go into EM and choose a table from a database and return all rows, I get immediate results and can start browsing records. If I go into query analyzer and do a select * on the same table, it takes up to 20-25 minutes to return the result set. This used to only take like 5 mins. What gives? Anyone seen this before?
All -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
I have a large VB 6.0 application running with Jet 4.0 Access DB. I am considering moving the DB to MSDE. For testing, I installed MSDE on another computer in a peer to peer network running XP sp1, loaded up the DB from Access to MSDE2000A -- all without problem. (I should add the obvious fact I am new to using SQL server.)
What I find is that with a relatively small test DB, running just a single instance of the application, query response from MSDE is taking several seconds (4-5 second lag) longer than response from the Access DB, which runs extremely fast. This is with the Access DB installed on the same network drive, running the same application and the same queries syntax -- only changing configuration of connection for each (SQL vs Jet 4).
Any clues as to what may be going on? The lag time is unacceptable. I am using SQL password instead of NT security. It seems the process is perhaps lagging in the process of authorization.
Thanks for any suggestions or ideas on this.
Hi, when running the following stored procedure: ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]@LeidinggevendeID int,@Start int = 1,@Limit int = 25,@Sofinummer int = NULL,@Achternaam nvarchar(128) = NULL,@Functie nvarchar(64) = NULL
ASWITH Ordered AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY Achternaam) AS RowNumber,Persoon.*FROM PersoonINNER JOIN DienstverbandON Persoon.ID = Dienstverband.PersoonIDINNER JOIN BedrijfsonderdeelON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelIDINNER JOIN LeidinggevendeON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelIDWHERE
Leidinggevende.Begindatum <= getdate()AND (Leidinggevende.Einddatum > getdate()OR Leidinggevende.Einddatum IS NULL
)
AND Leidinggevende.PersoonID = @LeidinggevendeIDAND
(
Sofinummer = @Sofinummer
OR @Sofinummer IS NULL
)
AND
(
Achternaam LIKE @AchternaamOR AchternaamPartner LIKE @AchternaamOR @Achternaam IS NULL
)
)
SELECT *FROM OrderedWHERE RowNumber between @Start and (@Start + @Limit - 1) When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code: [DataObject(true)] public class PersoonFactory { [DataObjectMethod(DataObjectMethodType.Select, false)] public static IList WerknemersBijLeidinggevende(int ldgID, int start, int max) { IList list = new List(); SqlDataReader rdr = null; SqlConnection connection = DatabaseProvider.Connection; SqlCommand command = new SqlCommand("GetWerknemersBijLeidinggevende", connection); command.Parameters.AddWithValue("LeidinggevendeID", ldgID); command.CommandType = CommandType.StoredProcedure; try
{
connection.Open();
rdr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read()) { Persoon pers = new Persoon(); pers.ID = rdr["ID"] as int?; pers.Achternaam = rdr["Achternaam"] as string; pers.AchternaamPartner = rdr["AchternaamPartner"] as string; pers.Achtertitels = rdr["Achtertitels"] as string; pers.DatumOverlijden = rdr["DatumOverlijden"] as DateTime?; pers.Geboortedatum = rdr["Geboortedatum"] as DateTime?; pers.Geslacht = rdr["Geslacht"] as string; pers.Middentitels = rdr["Middentitels"] as string; pers.Naamgebruik = (int)rdr["Naamgebruik"]; pers.Sofinummer = rdr["Sofinummer"] as string; pers.Voorletters = rdr["Voorletters"] as string; pers.Voortitels = rdr["Voortitels"] as string; pers.Voorvoegsel = rdr["Voorvoegsel"] as string; pers.VoorvoegselPartner = rdr["VoorvoegselPartner"] as string; list.Add(pers); } } catch
{
throw; } finally
{
if (rdr != null) rdr.Close(); else connection.Close(); } return list; } I get 0 rows all of a sudden. Any idea why?
Hullo all
I have two machines,
One is a standalone machine and the other is on a domain network. How can I run a stored procedure/job on the standalone machine from the domain machine ?
running the procedure as a Domain user results in a failed job/stored proc.
also creating an sql login and attempting to run it as that user also fails. How can I solve this problem ?
please e-mail me at wayde@sunnygrp.com if you have any thoughts...
I have searched many forums and found some cases of people also reporting slower rendering in RS than in Management Studio / Query Analyzer. However, none of the other solution suggestions seem to make a difference for me.
I'm a VS/VB developer and have got multiple reports built -- all using stored procedures on the backend -- that all take many times longer to run than if executed via Management Studio (SSMS). My simplest proc takes a couple of parameters (no defaults included) and does a simple select against one table with a few joins. Nothing complicated. It runs in 8 secs for 6867 rows via SSMS. Through RS (running locally through Visual Studio 2005 at this point) it takes around 25-28 secs. Yet, when I'm in the report on the DATA tab (not the PREVIEW tab) the run takes the expected 8 secs ?!?!
All reports are behaving this way.
I am not using cursors.
I have no default values on parameters.
I have added the "WITH RECOMPILE" to the proc statement.
I have "SET NOCOUNT ON" as the first line of the proc.
I hate to say this, but I even connected the proc to Crystal Reports to see how it behaved. It ran in the expected 8 secs.
I've seen some mention by someone that perhaps this is a known issue of RS that it reads the proc twice. Any truth to this?
Also a couple posts have traced and demonstrated that the report is generating significantly more data "reads" via RS than through SSMS.
We're a shop that is considering a switch from Crystal to RS, but we do everything through stored procedures. I need to clear up this issue before I can go forward recommending a switch. I'm including a copy of a typical proc below for review... What am I missing? What's the deal here with RS?
IF OBJECT_ID('dbo.rpt_InactiveAccounts') IS NOT NULL
DROP PROCEDURE dbo.rpt_InactiveAccounts
GO
CREATE PROCEDURE dbo.rpt_InactiveAccounts
(@pRunDate datetime
,@pSalesperson varchar(5000)
,@pIncludeOpen char(1)
)
WITH RECOMPILE
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- CREATE/SETUP TEMP TABLE FOR USE IN PARSING MULTI-VALUED STRING INPUTS
DECLARE @NumberPivot TABLE (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=4999 BEGIN
INSERT INTO @NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
-- CREATE TEMP TABLES TO HOLD PARSED VALUES FROM MULTI-VALUE STRING INPUT PARAMETERS
DECLARE @SalespersonTable TABLE
(tmpSalesperson varchar(30))
-- PARSE OUT @pSALESPERSON PARAMETER AND STORE VALUES IN TEMP TABLE
INSERT INTO @SalespersonTable
SELECT SUBSTRING(',' + @pSalesperson + ',', NumberID + 1,
CHARINDEX(',', ',' + @pSalesperson + ',', NumberID + 1) - NumberID -1)
FROM @NumberPivot
WHERE NumberID <= LEN(',' + @pSalesperson + ',') - 1
AND SUBSTRING(',' + @pSalesperson + ',', NumberID, 1) = ','
SELECT DISTINCT
CASE
WHEN s.Name IS NULL THEN '<< OPEN >>'
ELSE s.Name
END As SalespersonName
,c.ClassId
,c.CustId
,c.Name
,c.Addr1
,c.Addr2
,c.Addr3
,c.City
,State
,CASE
WHEN Len(c.Zip) = 9 And CharIndex(' ', c.Zip, 0) = 0 THEN Left(c.Zip, 5) + '-' + Right(c.Zip, 4)
ELSE c.Zip
END As Zip
,ac1.descr As Terms
,ac2.descr As Status
FROM
ACTCustomer c (NOLOCK)
LEFT OUTER JOIN CustSales cs ON c.CustId = cs.CustId
LEFT OUTER JOIN ACTSalesperson s ON cs.SlsId = s.SalesId
INNER JOIN @SalespersonTable st ON s.Name = st.tmpSalesperson OR (s.Name IS NULL AND @pIncludeOpen = 'Y')
INNER JOIN ACTCode ac1 ON ac1.Code = c.Terms And ac1.FieldId = 'CustTerms'
INNER JOIN ACTCode ac2 ON ac2.Code = c.Status And ac2.FieldId = 'Status'
WHERE
c.LastInvcDate <= @pRunDate
And c.ClassId <> 'TR'
ORDER BY
SalespersonName, Name, CustId
DECLARE @returnDay int
DECLARE @query varchar(8000)
--Looking at current date,
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 3
SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c: est.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @query
EXEC master.dbo.xp_sendmail @recipients='test@hotmail.com',
@copy_recipients = 'test@hotmail.com',
@message='Submitting Results for the previous month.',
@subject='BloodBank results for the previous month',@attachments = '\cenc$ est.txt'
SELECT @@ERROR As ErrorNumber
I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current date
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.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies View RelatedHi
I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong.
Any help would be greatly appreciated.
Asp.net code;
Dim oCmd As SqlCommand
oCmd = New SqlCommand("exportData", rtConn)
oCmd.CommandType = CommandType.StoredProcedure
rtConn.Open()
With oCmd
.CommandType = CommandType.StoredProcedure
Response.write("CommandType.StoredProcedure")
End With
Try
oCmd.ExecuteNonQuery()
Response.write("ExecuteNonQuery")
Finally
rtConn.Close()
End Try
StoredProcedure;
CREATE PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /local/DTS_ExamResults'
GO
Thanks
Rachel
Hi
I have a SSIS package stored on the database server under Stored Packages within the Integration Services section.
Does anyone know how to run this package using SQL?
Thanks
I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.
How do i call a DB2 stored procedure?
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedHi Guys & Gals
I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:
ALTER PROC sp_get_allowed_growers
@GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @GrowerList + ') ORDER BY nu_code ASC'
EXEC(@SQL)
END
GO
and the code I'm using to execute the procedure is this:
public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
//Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;
}
The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:
Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Anyone with any ideas would be very helpful...
I need to run stored procedures which are currently run on server A(located in another city),by connecting myself to it through my laptop running on SQL server 7.0(only client components installed)on NT workstaion4.0. Can anyone tell all the steps involved to run those stored procedures everyday.Even I need to monitor Server A from my laptop.Please advice...Urgent!!
View 1 Replies View RelatedI am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-
Public Function CheckDigitCalc()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
On Error GoTo TryAgain
conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn
cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute
MsgBox "Numbers created OK.", vbOKOnly
Exit Function
TryAgain:
MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"
End Function
The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.
hi,
i'm using SQL server 2000. i'm getting the below error when i run a store procedure.
"Specified column precision 500 is greater than the maximum precision of 38."
I have created a temporary table inside the stored procedure inserting the values by selecting the fields from other table. mostly i have given the column type as varchar(50) and some fields are numeric(50).
I have two similar stored procedures which I'm running. One runs andone doesn't. I can run both with no problems in SQL Enterprise (7.0standard) and have checked the permissions and am happy with them.Whilst the statements in each sp are different, I'm calling them inexactly the same way (using Delphi 5 Windows 2000). I don't get anytrappable errors.I ran a trace on what was happening and I get two different set ofresults. This is what I'm trying to get my head around.Client Trace (runs)1 09:09:44 Log started for: Swift Client Import Utility2 09:09:48 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftClient;13 09:09:48 SQL Misc: MSSQL - Set stored procedure on or off4 09:09:48 SQL Data In: MSSQL - Param = 1, Name = Result, Type= fldINT32, Precision = 0, Scale = 0, Data = NULL5 09:09:48 SQL Misc: MSSQL - Set statement type6 09:09:48 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftClient;17 09:09:48 SQL Stmt: MSSQL - Close8 09:09:53 SQL Connect: MSSQL - Disconnect NEW9 09:09:53 SQL Connect: MSSQL - Disconnect NEW10 09:09:53 SQL Connect: MSSQL - Disconnect PASSTHRUEnquiry Trace (Doesn't run)1 09:08:21 Log started for: Swift Client Import Utility2 09:08:24 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftEnquiries;13 09:08:24 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftEnquiries;14 09:08:50 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftEnquiries;15 09:08:50 SQL Misc: MSSQL - Set stored procedure on or off6 09:08:50 SQL Data In: MSSQL - Param = 1, Name = Result, Type= fldINT32, Precision = 0, Scale = 0, Data = NULL7 09:08:50 SQL Misc: MSSQL - Set statement type8 09:08:50 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftEnquiries;19 09:08:50 SQL Vendor: MSSQL - dbrpcinit10 09:08:50 SQL Vendor: MSSQL - dbrpcexec11 09:08:50 SQL Vendor: MSSQL - dbsqlok12 09:08:50 SQL Vendor: MSSQL - dbresults13 09:08:50 SQL Vendor: MSSQL - dbnumcols14 09:08:50 SQL Vendor: MSSQL - dbcount15 09:08:50 SQL Stmt: MSSQL - Close16 09:08:50 SQL Vendor: MSSQL - dbdead17 09:08:50 SQL Vendor: MSSQL - dbcancel18 09:08:56 SQL Connect: MSSQL - Disconnect NEW19 09:08:56 SQL Connect: MSSQL - Disconnect NEW20 09:08:56 SQL Connect: MSSQL - Disconnect PASSTHRU21 09:08:56 SQL Vendor: MSSQL - dbdead22 09:08:56 SQL Vendor: MSSQL - dbfreelogin23 09:08:56 SQL Vendor: MSSQL - dbcloseI would have thought that these would be nearly identical. They callsp's on the same servers in the same way, so the call to do thisshould (in my mind) be the same.I can post the sp's if anyone thinks they are of relevance.I can of course set up these stored procedures to run at a certaintime, but I'd like to try and understand this a little more.Thanks in advance.Ryan
View 1 Replies View RelatedHow can I find out if a stored procedure is currently being executed?
sp_who2 and sys.sysprocesses, Command, Cmd fields just gives me parts of the sql inside the stored procedure.
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 RelatedHi,
I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.
I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)
I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.
Here is my SP
ALTER PROCEDURE uspSelectBarItemID2
(
@BarTabID INT,
@DrinkID INT,
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID
FROM [Bar Items]
WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.
What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.
Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.
Cheers.
HI all, I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks.
View 6 Replies View RelatedI’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…