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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Can i write stored proc for the two queries, truncating and inserting two different tables?

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
Hi All, I have two queries below. trying to write stored proc so i can combine these queries so i can use only one execute sql task. both the queries are truncating and inserting records in two different tables. QUERY 1 à   truncate table [PlanFinder].[InvalidAwps] go   INSERT INTO [PlanFinder].[InvalidAwps]     (Ndc, AwpUnitCost)     SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost     FROM         PlanFinder.PlanFinder.HpmsFormulary P         LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices                     WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A     ON P.Ndc = A.Ndc      WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL)     AND p.Ndc IS NOT NULL --------------------------------------------------------- Query 2 à   truncate table [PlanFinder].[MissingNdcs] go   INSERT INTO [PlanFinder].[MissingNdcs]     (Ndc)     SELECT DISTINCT Ndc     FROM         PlanFinder.InvalidAwps       WHERE AwpUnitCost IS NULL  Thanks a

View Complete Post

More Related Resource Links

how to write a stored proc to prevent multiple users in uploading the reports at a time? plsss help



I wanted to know how to prevent more than one user from uploading the report at a time .I am new to .net ,plss help with the code that would be great.Details are below:

This is a Windos based app written in c# . When User A  clicks Upload option on  one server  from the menu  to upload the files  and at the same time when User B clicks Upload option on different server , User B should be alerted a message saying "User A's uploading is in progress,pls wait" . How to achieve this, plsss help with the code.. I am thinking this logic should be kept in a stored proc, How do I write that proc? plss help


Thanks in advance!

How do i use these queries in Stored Proc.


Hi Frdz, i have these 3 queries and how do i put these queries in one stored proc with filename as input parameter as you c i am having certain file name in where clause.

 FROM  ssislogs.audit.processcontrol
 WHERE SubjectArea = 'UaqaFacetsImp') > 0
SELECT pc.SrcFileName + ' - '+  CONVERT(VARCHAR(10), pc.[RecordCount] ) AS FacetsImpCount
 FROM  ssislogs.audit.processcontrol pc left outer join
 Uaqa.LettersAndCardsExports lc
on pc.etlloadid = lc.etlloadid
  WHERE pc.subjectarea = 'UaqaFacetsImp'
SELECT 'No input file' AS FacetsImpCount

SELECT pc.subjectarea AS FacetsRjctFile
FROM  ssislogs.audit.processcontrol pc left outer join
Uaqa.LettersAndCardsExports lc
on pc.etlloadid = lc.etlloadid
  WHERE pc.subjectarea = 'UaqaFacetsRjct'

SELECT pc.subjectarea AS FacetsPvsFile
FROM  ssislogs.audit.processcontrol pc left outer join
Uaqa.LettersAndCardsExports lc
on pc.etlloadid = lc.etlloadid
  WHERE pc.subjectarea = 'UaqaFacetsPvs'



Stored proc inserting into a identity field


We have a table with an identity field

Occasionaly data is archived out to a seperate database (not my design but stuck with it for now).  If an archived record is re-instated we need to insert back into the original table specifying the identity field (so it gets the same ID as last time)

the user performing the task is a bog standard user i.e. Not db_owner or sysadmin.

I have created a stored proc to perform the reinstate and tried to grant execute permissions as db_owner using my sysadmin account but get the message

Grantor does not have GRANT permission.

I have granted myself execute permissions and made myself db_owner but still get the same error.


I then tried to specify the GRANT to use my login (sysadmin user) , this went through but I still get the error when executying the stored proc as the standard user

The current user is not the database or object owner of table 'DB.dbo.Table1'. Cannot perform SET operation.


Anyone shed any light or am i going to have to rewrite the system to avoid the identity field??


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:

PersonID (FK)
LocationID (FK)

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

Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

Populating a list box in InfoPath from a sql stored proc


I have an InfoPath web enabled form that gets published to a SharePoint site.  I need to add a list box to the form that gets populated with data returned from an existing call to a SQL Stored Proc.  

As the form works now, the user types in a patient id #, then clicks a button that calls the Stored Proc and returns any patients that match the given patient ID.  This part works.  

My task is to add a list box so that if more than one patient is returned , display the list box with all of these patients populated in it.

I am hoping to do this within the confines of the existing code.  The existing code looks like this:

private bool LoadPatientInformationFromDB(string patientId, string patientFirstName,
      string patientLastName, string patientMiddleInitial, string birthDate, string gender,
        string admitDate, string location)
      bool completed = false;
      //Load from DB
      SqlConnection conn = new SqlConnection(GetOrderSetDBConnectionString()); 
      //Create Form Builder
      formBuilder = new FormBuilder();
      SqlDataAdapter adpt = null;
        if (!String.IsNullOrEmpty(patientId) || !String.IsNullOrEmpty(patientFirstName) ||
          !String.IsNullOrEmpty(patientLastName) || !String.IsNullOrEmpty(patientMiddleInitial)
          || !St

Sending Email by exeucting a stored proc on SQL Server that references CLR

Hi Guys, I want to modify the data type of the @body variable in the stored procedure that sends an email. The stored procedure is as follows:   CREATE PROCEDURE [dbo].[spSendMail4]<br/> @recipients [nvarchar](4000),<br/> @cc [nvarchar](4000),<br/> @subject [nvarchar](4000),<br/> @from [nvarchar](4000), @body [nvarchar](4000), @attachment [nvarchar](4000)<br/> WITH EXECUTE AS CALLER<br/> AS <br/> EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail]<br/> GO   I want to change the @body variable to data type: text. The reason for this, is because I am sending an HTML email whose content (characters) exceed 8000. Basically the new stored procedure should looks like below:   CREATE PROCEDURE [dbo].[spSendMail4] @recipients [nvarchar](4000), @cc [nvarchar](4000), @subject [nvarchar](4000), @from [nvarchar](4000), @body text, @attachment [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail] GO   If attempt to make this change, I get the below error:   CREATE PROCEDURE for "spSendMail" failed because T-SQL and CLR types for parameter "@body" do not match.   Understandably so, because from the article http://msdn.microsoft.com/en-us/library/ms131092%28SQL.100%29.aspx CLR data type (.NET Framework) is not compa

How to pass the file path in EXEC of Stored Proc for getting IIS Logfile data

Hello,                  I need to get IIS Logfile data from Stored Proc. How to do this. I am using Bulk Insert Method in my stored Proc When I execute the stored proc I am getting an error that  Incorrect syntax near 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log'.     Below is the code.                 Create  PROCEDURE  spWebLog @path varchar(300) AS begin exec('BULK INSERT Weblog from''' + @path + ''')  end go Declare   @SQL Varchar(100 ) Set    @SQL= 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log' exec   spWebLog @SQL    

Error in executing a stored proc

Hi Team, I have problem in executing a stroed proc SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec PBT_Broker_Report 'PBTBFDAGT ' Alter PROCEDURE PBT_Broker_Report  -- Add the parameters for the stored procedure here @BrokerID nchar(10)  AS BEGIN SET NOCOUNT ON; declare @sql nvarchar(3000) ;   IF EXISTS (    SELECT *    FROM information_schema.tables    WHERE Table_Name='Test_Broker'    )    BEGIN    DROP TABLE Test_Broker    END     set @sql = (case @BrokerID     when 'All' then    N'    SELECT PO.COMPANY_CODE,PO.POLICY_NUMBER, PO.primary_insured    INTO Test_Broker   from dbo.POLICY po   where PO.AG_AGENT_NUMBER <> ''PBT001''   '     else        N'   SELECT PO.COMPANY_CODE,PO.POLICY_NUMBER, PO.primary_insured,   INTO Test_Broker   from dbo.POLICY po   where    PO.AG_AGENT_NUMBER = @BrokerID   AND AG_AGENT_NUMBER <> ''PBT001''  '   end ) --print @sql execute sp_executesql @sql     Error: Must declare the variable '@BrokerID' Please suggest me some solution

Reading data to a table from a stored proc

This has me brain locked.  1. I've got a stored procedure.  2. It does a select with a couple joins.  I want to take the results of that stored proc and read them into some a table -- temp table would be ideal.  here's my (really tough) constraints... these really complicate things: 1. the stored proc uses dynamic SQL. As a consequence, I don't know the columns it's going to return (it's kind of the whole point of using it).  2. Since the stored proc uses dynamic SQL, it needs to use sp_executesql internally.    What won't work(i don't think?): 1. I can't create a function that returns a table and does a "return select * from....", since you can't (I don't think?) call a stored a stored proc in the return statement of a function 2. I can't just do an "Insert into #xyz exec .." because I can't pre-define the temp table (I think this is needed?). 3. I don't have a linked server. and i'd rather not. All of this runs local in the same instance of SqlServer, and will have to handle loads of volume (each hit will return a few million rows).  So (I think?) OPENQUERY  is out.  4. OPENROWSET is a possibility, I suppose, but again, everything is running locally. I think that goes through OLEDB (?) and I'm not sure that's even an option.    THOUGHTS???  HELP?? is it even possible?? Pl

Testing Foregin Key without error out of stored proc

I have a Customer table. Several dirrect mapping tables link to this record. In my stored procedure I would like to try and delete this Customer record and if any keys are bound to it, then catch that error without breaking the stored proc ( try - catch ) Is this possible in a stored proc? I would like to just use a print statement that the record couldnt be deleted. Thanks, Nick

SqlDataSource : in what order do parameters get fed to stored proc?

Hello, I'm wondering if someone can explain how a SqlDataSource determines the order in which parameters are fed to a stored procedure. I'm not able to find clear documentation.So suppose a proc has: CREATE PROCEDURE  updateOneCar@CarActiveFlagCheck int, @CommentsForCar varchar(1500) = NULL, @manufacturerID int...and then suppose the ASP.NET page has the manufacturerID supplied by a querystring, and has a FormView as follows: <asp:FormView ID="FormView1" runat="server" DataSourceID="SQL01"          DataKeyNames="manufacturerID" EditRowStyle-Wrap="False" RenderOuterTable="False">         <EditItemTemplate>         <div>             manufacturerID: <%# Eval("manufacturerID")%>             <br />             Is my Car on?: <%# Eval("carFlag")%>             <br />             Is my car Active Flag:             <asp:CheckBox ID="CarActiveFlagCheckBox" runat="server"              

Oracle stored proc optionally returning a ref cursor.

Hi, I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents. Now, we have done this with the following code:            OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");             command.Parameters.Add(ParameterFactory.CreateParameter("p_doc_id", RTSId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_rev_id", RevisionId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_c_item_id", ContentItemId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_error_code", OracleType.Number, ParameterDirection.Output));          &nbs

Converting a Stored Proc to a query

I have a stored procedure that I want to convert to a query that I can email to the DBAs to run.  The stored proc has 2 parameters (start and end date) as I need them to run the query in date chunks (i.e. 1/1/2010 - 1/31/2010) due to the size of the table. *** Please allow me to mark threads as answered and I will, Thank you ***

How does Linq to Sql Determine which tables to write to

I was wondering how Linq to Sql determines which tables to write to when given primary/foreign Key relationships.  I can (hopefully) better explain using an example.  I have the following tables: Customer CustomerStatus CustomerType CustomerAddresses Therefore I have a Customer Entity, a CustomerStatus entity, a CustomerType Entity and a List of CustomerAddresses.  In my code, I load up the Customer Entity and the List of CustomerAddresses because that's all I need.  For some reason Linq to Sql is trying to insert a new Customer Status record.  I have no idea why its doing this.  Can anyone please explain to me how linq to sql determines which table(s) will be written to based on the entities above? Thanks

inserting same autonumber to both tables.

Hello,I have an SQL DB which contains 2 tables. The first one 'advs' has some information about an advertisement and it got an ID field. The second one, "photos", contains all the paths of photos, this one also has an ID field. Each advertisement from the 'advs' Table has three photos in the PHOTOS table. now obviously the photos related to the 'ad' must have the same ID. What I'm trying to do is a Form to add the information about each advertisement and its' photos. This form should enter the SAME ID number to both tables AUTOMATICALLY after adding.Any ideas how to achieve that?Thanks,Basil.

can i use stored proc to send email.

Hi how can i use stored proc for sending emails?
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