Running Stored Procedure Multiple Times

Jun 25, 2007

I’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…

View 7 Replies


ADVERTISEMENT

Identifying Results Sets When Stored Procedure Called Multiple Times

Oct 18, 2005

I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!

View 3 Replies View Related

Running The Same Subquery Multiple Times

Mar 2, 2005

Hi,

I was wondering if this can be done...

I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

E.g.
I have a table with 2 cols - amount, date.

SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)

Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

Hope someone can help,
Thanks,
Stuart

View 5 Replies View Related

Transact SQL :: Running Query For Multiple Times Through The Day?

Jun 12, 2015

I want  the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .

there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job 
 
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
 SELECT   
--DISTINCT  
p.SPID,
p.LogiName,
p.HostName,   

[Code] ....

View 8 Replies View Related

Asynchronous Script - Running Through Buffer Multiple Times?

Mar 26, 2008



Is it possible to run through a buffer multiple times in an asynchronous script?

Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?

Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".


I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.

I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)

View 5 Replies View Related

Calling Stored Proc. Multiple Times...

Feb 1, 2008

I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.

I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.

Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.

Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.

Any help is very much appreciated.

View 2 Replies View Related

Stored Procedure Times Out

Jun 18, 2008

Hello,
I have been working on this stored procedure, it pulls the results that i want, but it takes a very long time to execute and about half the time it times out, i have tried changing the timeout settings on the sql server to 0 (no timeout) but it seems to have made no difference.
Could anyone help with making the query a bit more effiecient?  SELECT DISTINCT
webStats.id, webStats.ip, webStats.useragent, browsers.browsername, os.osname, webStats.datestamp, webStats.hourstamp, webStats.hostname,
webStats.refurl, webStats.refhost, webStats.entrypage, webStats.visitcount, webStats.country, searchengines.enginename,
IpToCountry.COUNTRY AS CountryName
FROM webStats LEFT OUTER JOIN
IpToCountry ON webStats.country = IpToCountry.CTRY LEFT OUTER JOIN
searchengines ON webStats.useragent LIKE '%' + searchengines.agentstring + '%' LEFT OUTER JOIN
browsers AS browsers ON webStats.useragent LIKE '%' + browsers.agentstring + '%' LEFT OUTER JOIN
os ON webStats.useragent LIKE '%' + os.agentstring + '%'

WHERE (webStats.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
ORDER BY webStats.datestamp DESCThanks for your help.
Bart

View 10 Replies View Related

Executing A Stored Procedure As Times As The View Has Records

Oct 6, 2007

i have a stored procedure with one coming id parameter





Code BlockALTER PROCEDURE [dbo].[sp_1]
@session_id int
...







and a view that holds these @session_id s to be sent to the stored procedure.

how could i execute this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records with different ids.

View 1 Replies View Related

Running Dts From Stored Procedure

Mar 21, 2004

Hi

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

View 4 Replies View Related

Running A DB2 Stored Procedure

Nov 3, 2006

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?

View 1 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 Replies View Related

Function Running Many Times

May 28, 2008

Hello,

I see the following problem regarding a SQL function that returns a value.

We have the following query

SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')

The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.

We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.

We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).

Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.

Thanks in advance,

Lisandro.

View 3 Replies View Related

Problem Running Stored Procedure

Jan 3, 2005

Hi 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...

View 6 Replies View Related

Stored Procedure Only Part Running

Dec 10, 2001

I 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.

View 2 Replies View Related

Error While Running Stored Procedure.

Aug 2, 2007

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).

View 2 Replies View Related

Findout If A Stored Procedure Is Running?

Feb 5, 2008

How 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.

View 5 Replies View Related

Stored Procedure Running Slow In ADO.NET

Jan 9, 2008

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 Related

Running A Stored Procedure In Code

Apr 14, 2008

Hi,

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.

View 11 Replies View Related

Running A Stored Procedure Without Passing Parameters

Feb 14, 2007

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 Related

Timeout Exception When Running Stored Procedure

Feb 4, 2008

Hi,
 I'm running a CLR stored procedure through my web using table adapters as follows:
res = BLL.contractRateAdviceAdapter.AutoGenCRA()    'with BLL being the business logic layer that hooks into the DAL containing the table adapters.
 The AutoGen stored procedure runs fine when executed directly from within Management Studio, but times out after 30 seconds when run from my application. It's quite a complex stored procedure and will often take longer than 30 seconds to complete.
The stored procedure contains a number of queries and updates which all run as a single transaction. The transaction is defined as follows:
----------------------------------------------------------------------------------------------------------------------
options.IsolationLevel = Transactions.IsolationLevel.ReadUncommittedoptions.Timeout = New TimeSpan(1, 0, 0)
Using scope As New TransactionScope(TransactionScopeOption.Required, options)
'Once we've opened this connection, we need to pass it through to just about every
'function so it can be used throughout. Opening and closing the same connection doesn't seem to work
'within a single transactionUsing conn As New SqlConnection("Context Connection=true")
conn.Open()
ProcessEffectedCRAs(dtTableInfo, arDateList, conn)
scope.Complete()
End Using
End Using
----------------------------------------------------------------------------------------------------------------------
As I said, the code encompassed within this transaction performs a number of database table operations, using the one connection. Each of these operations uses it's own instance of SQLCommand. For example:
----------------------------------------------------------------------------------------------------------------------Dim dt As DataTable
Dim strSQL As StringDim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 0Dim rdr As SqlDataReaderstrSQL = "SELECT * FROM " & Table
cmd.CommandText = strSQL
rdr = cmd.ExecuteReader
SqlContext.Pipe.Send(rdr)
rdr.Close()
----------------------------------------------------------------------------------------------------------------------
Each instance of SQLCommand throughout the stored procedure specifies cmd.CommandTimeout = 0, which is supposed to be endless. And the fact that the stored procedure is successful when run directly from Management studio indicates to me that the stored procedure itself is fine. I also know from output messages that there is no issues with the database connection.
I've set the ASP.Net configuration properties in IIS accordingly.
Are there any other settings that I need to change?
Can I set a timeout property when I'm calling the stored procedure in the first place?
Any advice would be appreciated.
 
Thanks

View 2 Replies View Related

Running A MS SQL Stored Procedure On Button Click

Mar 14, 2008

I am new to ASP.NET so please excuse what may seem like a dumb question.
I have a stored procedure that I need to run when the user clicks on our submit button.  I am using Visual Studio 2005 and thought I could use the SqlDataSOurce Control.  IS it possible to us the control or do I need to create a connection and call the stored procedure in the the button_click sub?
Thanks in advance
MF

View 6 Replies View Related

Error When Running A Stored Procedure From My Code

May 9, 2008

 Hi all,      I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code:          // Define data objects        SqlConnection conn;        SqlCommand comm;        // Initialize connection        string connectionString =            ConfigurationManager.ConnectionStrings[            "pay"].ConnectionString;        // Initialize connection        conn = new SqlConnection(connectionString);        // Create command         comm = new SqlCommand("UpdatePaymentDetails", conn);        //comm.CommandType = CommandType.StoredProcedure;        // Add command parameters        comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int);        comm.Parameters["PaymentID"].Value = paymentID;        comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewPayment"].Value = newPayment;        comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewInvoice"].Value = newInvoice;        comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money);        comm.Parameters["NewAmount"].Value = newAmount;        comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewMargin"].Value = newMargin;        comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money);        comm.Parameters["NewProfit"].Value = newProfit;        comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime);        comm.Parameters["NewEditDate"].Value = newEditDate;        comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewQStatus"].Value = newQStatus;        comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewStatus"].Value = newStatus;        // Enclose database code in Try-Catch-Finally        try        {            conn.Open();            comm.ExecuteNonQuery();        } 

View 7 Replies View Related

Stored Procedure To Fill Bridgetable Not Running

Oct 4, 2005

I have a table with information about a mobile account in one table, a table for mobile plans, and a table for planfeatures. Each mobile account is associated with a planid, and may be associated with any combination of the features associated with that plan. The plan features are stored in a bridgetable which contains 'invoicedate' (date stamped on the invoice in question), 'subaccountnumber' (the cell phone number), 'planid', and 'featureid'. I've tested the UpdateMobileFeature sp directly from the SQL Profiler--it works fine on it's own. I use a stored procedure to fill the mobile table (called from aspx page), and since I use three of the four columns written above for both the mobilesub and the mobilefeature tables, I tried just adding the parameter which holds the featureid's to the sp to update the mobilesub table. Then I call the UpdateMobileFeature sp from the updatemobile sp. (The code in mobilesub that is not calling UpdateMobileDetail works well). All seems to work fine--nothing crashes or anything, but nothing is being added to the mobilefeature table. Here is the code:CREATE procedure usp_updatemobile(     @InvoiceDate smalldatetime,    @SubaccountNumber varchar(50),    @PlanId int,   @FeatureList varchar(500) --added for UpdateMobileFeatures. In the form of a comma-seperated list, to imitate an array.  --***irrelevant parameters removed***--)
as
exec dbo.UpdateMobileFeature '@InvoiceDate','@SubaccountNumber','@PlanId','@FeatureList' --the apparently nonfunctional call
if exists (    //select for the mobile row in question )  Begin      //update row 
End
Else  Begin   //insert new row End
GOCREATE PROC dbo.UpdateMobileFeatures(  @InvoiceDate smalldatetime, @SubaccountNumber varchar(50),  @PlanID int, @FeatureList varchar(500))ASBEGIN SET NOCOUNT ON
 CREATE TABLE #TempList (  InvoiceDate smalldatetime,  SubaccountNumber varchar(50),  PlanID int,  FeatureID int     )
 DECLARE @FeatureID varchar(10), @Pos int  SET @FeatureList = LTRIM(RTRIM(@FeatureList))+ ',' SET @Pos = CHARINDEX(',', @FeatureList, 1)
 IF REPLACE(@FeatureList, ',', '') <> '' BEGIN  WHILE @Pos > 0  BEGIN   SET @FeatureID = LTRIM(RTRIM(LEFT(@FeatureList, @Pos - 1)))   IF @FeatureID <> ''   BEGIN    INSERT INTO #TempList (InvoiceDate, SubaccountNumber, PlanID, FeatureID)     VALUES (@InvoiceDate, @SubaccountNumber, @PlanID, CAST(@FeatureID AS int)) --Use Appropriate conversion   END   SET @FeatureList = RIGHT(@FeatureList, LEN(@FeatureList) - @Pos)   SET @Pos = CHARINDEX(',', @FeatureList, 1)
  END END 
 --SELECT o.FeatureID, CustomerID, EmployeeID, FeatureDate --FROM  dbo.Features AS o -- JOIN  -- #TempList t -- ON o.FeatureID = t.FeatureID
 
 Insert Into MobileFeatures Select InvoiceDate, SubaccountNumber, PlanID, FeatureID From #TempList  ENDGOHere is the method I used to call the first sp: (also with irrelevant stuff removed)public void saveCurrentMobile()           {
                      calculateTotals();
                      InvoiceDataSet dataset = InvoiceDataSet.GetInstance();
                      SqlConnection conn = (SqlConnection)Session["connection"];
                      SqlCommand cmdUpdateMobile;
                      cmdUpdateMobile = new SqlCommand("usp_updatemobile", conn);                      cmdUpdateMobile.CommandType = CommandType.StoredProcedure;
                      SqlParameter invoicedate = cmdUpdateMobile.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime);                      invoicedate.Value = DateTime.Parse(Request.Params["InvoiceDate"].ToString());
                      SqlParameter cellnumber = cmdUpdateMobile.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50);                      cellnumber.Value = txtCellNumber.Text.Trim();
                      SqlParameter plan = cmdUpdateMobile.Parameters.Add("@PlanId", SqlDbType.Int);                      plan.Value = planid;
                      SqlParameter featurelist = cmdUpdateMobile.Parameters.Add("@FeatureList", SqlDbType.VarChar, 500);                      featurelist.Value = planform.FeatureList;
                    
                 //Response.Write(isthirdparty.ToString());                 Response.Write(thirdpartycompany);
                      SqlParameter cycle = cmdUpdateMobile.Parameters.Add("@Cycle", SqlDbType.Int);                      cycle.Value = Convert.ToInt32(Request.Params["Cycle"]);
                      int returnvalue = runStoredProcedure(cmdUpdateMobile); //the sp is called within this method.
 
                      if (returnvalue != 0)                      {                          dataset.fillMobileTable();                         Response.Write("Mobile Subaccount Saved!");                      }           }
 

View 3 Replies View Related

Is There Any Way To Bacground A Long Running Stored Procedure?

Feb 21, 2000

I have a stored procedure being called from Visual Cafe 4.0 that takes over 30 minutes to run. Is there any way to backround this so that control returns to the browser that the JFC Applet is running in? The result set is saved to local disk and an email message sent to the user on completion.
Thanks, Dave.

View 2 Replies View Related

Problems Running A Good Stored Procedure As A Job

Dec 13, 1999

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

View 7 Replies View Related

Running A Stored Procedure Using The Execute Sql Task

Mar 13, 2001

Hi

Is it possible in my DTS Package to check if a stored procedure which I'm executing from the Execute sql task icon can be tested for failure?

View 2 Replies View Related

Running A Batch File From A Stored Procedure

Jun 27, 2001

Is there a way to run/call a batch file from a stored procedure?

Or, is there a way to run/call a batch file from a trigger?

View 2 Replies View Related

Running A Stored Procedure Using Output Result.

May 12, 2008

Hey guys!

I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.

I've created a hierarchal listbox form that drills down From

Product - Colour - Year.

based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.

So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.

I want that product number to be used to populate the grid view. Is there away for me to do this?

Thanks in advanced everybody!

View 6 Replies View Related

Running Stored Procedure Using SQL Server Agent...

Jun 3, 2008

HI ALL


I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.

How can I add my stored procedure to the SQL server agent jobs??

Any Idea..

View 1 Replies View Related

Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US

View 1 Replies View Related

Question Regarding Running A Stored Procedure As A Job Step

Apr 16, 2007

I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?

View 3 Replies View Related

Long Running Stored Procedure Status

Mar 24, 2008

I have a stored procedure in SQL 2005 that purges data, and may take a few minutes to run. I'd like to report back to the client with status messages as the sp executes, using PRINT statements or something similar. I imagine something similar to BACKUP DATABASE, where it reports on percentage complete as the backup is executing.

I can't seem to find any information on how to do this. All posts on this subject state that it's not possible; that PRINT data is returned after the procedure executes. However it would seem possible since BACKUP DATABASE, for example, does this.


Is there any way to send status type messages to the client as the sp is executing??

Thanks.

View 6 Replies View Related

Querying Multiple Tables Multiple Times

May 31, 2007

I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me? 

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved