.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

HOW TO LIMIT EXECUTION OF STORED PROCEDURE ON SPECIFIC DAYS

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
 
How do you limit the execution of stored procedure / or update of items only to happen on specific days.   i need this only to execute if today is day 3 through day 7 (tue - sat) UPDATE itm SET AVG_WHSL_PRC = AWP.CURR_AWP_AMT ,AWP_EFF_DT = @Pricing_Date ,UPDT_DTS = GETDATE() ,UPDT_OPR_ID = RIGHT(SUSER_SNAME(), 7) FROM dbo.T_ITEM_PRC_CHG itm JOIN #TEMP_AWP awp ON itm.EM_ITEM_NUM = awp.EM_ITEM_NUM WHERE itm.PRC_CURR_CD IN ('C','F') AND @Pricing_Date BETWEEN itm.PRC_EFF_DT AND itm.PRC_END_DT AND itm.HOME_ACQ_COST > 0 AND ISNULL(itm.AVG_WHSL_PRC, 0) <> awp.CURR_AWP_AMT


View Complete Post


More Related Resource Links

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Insert the results of a stored procedure into specific columns

  
Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

Stored Procedure taking time in execution

  

Dear All,

 

I am breaking my head on this issue since long. I have stored procedure in MS SQl and when I try to execute that procedure by providing all the parameters in SQL Query, it takes long time to execute but when I try to directly run the query which is there in SP it executes in no time. This is affecting my application performance also as we are using stored procedures to fetch the data from DB Server.

Please help.

Regards,

Vikram


Vikram

Stored Procedure taking time in execution

  

Dear All,

 

I am breaking my head on this issue since long. I have stored procedure in MS SQl and when I try to execute that procedure by providing all the parameters in SQL Query, it takes long time to execute but when I try to directly run the query which is there in SP it executes in no time. This is affecting my application performance also as we are using stored procedures to fetch the data from DB Server.

Please help.

Regards,

Vikram


Vikram

create stored procedure to remove records older than 90 days.

  

Hi All, i want to create two new stored procedures to remove records older than 90 days from tables DirStaging.Trr.FullEnrollmentImport and DirStaging.Trr.WeeklyMonthlyImport. The same stored procedure will remove all records from Dir.dbo.ErrorTable where there is not a record in Dir.Trr.FullEnrollment and Dir.Trr.WeeklyMonthly.    Both the procedures will accept an input variable called @CleanupDays. This will be passed from the calling system. there is a field called EtlId in DirStaging.Trr.FullEnrollmentImport which i would have to use to get recent load of information. i mean, if i am loading thousand records then they would have same EtlId. so i guess i have to have @EtlId and @CleanupDays as input variables.

I tried something like moving all the data from the DirStaging.Trr.FullEnrollmentImport to temp table, truncate DirStaging.Trr.FullEnrollmentImport and apply condition on temp table to get recent

How to Get the Execution Count of a Stored Procedure?

  
How to Get the Execution Count of user define Stored Procedure?Basically i want to find the unwanted/un-using stored procedure in mssql.im using 2008 sql server.

what is limit on max size of XML datatype which we can pass to stored procedure and what is most el

  
what is limit on max size of XML datatype which we can pass to stored procedure  and what is most elegant way to pass huge XML to Stored procedure

Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions

  

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names

  

Hi

I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.



Sort by gridview SortExpression parameter via Stored Procedure

  

I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                        <Columns>
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                <ItemTemplate>
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
                            <

Stored procedure generator?

  

Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.


Create stored procedure from asp.net

  

Hi,

we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.

 

 


Call SQL Stored Procedure Asyncronously

  

Hello,

I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.


Best regards,

Imran


Unable to select any stored procedure while creating TableAdapters in wizard

  

I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend