.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

ETL Stored Proc trouble!

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

The loading table are in a star schema, and for every entry in the si_work_order (header) tables there should be at least one entry into the detail table . . . 

I am trying to perform ETL, and I am having trouble figuring out how to do the following:

·         Locate all new records in tbLocal_WMS_OrderStatus (tbLocal_WMS_OrderStatus STATUS=”N”) in MES_TRANS_ID order.  For each new record in tbLocal_WMS_OrderStatus, generate a record with STATUS=”N” in tbLocal_WMS_OrderStatusTransLog for each JDE work center associated with the MES execution path from tbLocal_WMS_OrderStatus identified by PATH_ID in the order the work centers are confi

View Complete Post

More Related Resource Links

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.

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!

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

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

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

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 ***

can i use stored proc to send email.

Hi how can i use stored proc for sending emails?

How to assign datatype at run time to a declared var in a stored proc?

Hi, I want to declare a local var. in sp. such that it would have the same datatype as a column in a specific table Example (forgive the syntax!) Decalre @Address  CustomerTable.Address The above would translate to the following at execution time Declare @Address VARCHAR(200) This feature was in Oracle, is it in SQL Server as well? Thanks. EK

have sql 64 bit with sql express and having issues with oledb running from stored proc in sql expres

Hello, I have added sqlexpress with my sql 64 bit in windows xp so i could use oledb to access excel files.  I can not use ace as the excel is 2003 and not all 64 bit.  It works perfectly until i try and run exec stored proc (has my oledb procedure) to insert the results into a table variable. declare   @MyTable table ( FirstName varchar(50), LastName varchar(50) ) INSERT   INTO @MyTable exec   [MyDevServer\sqlexpress].MySQLexpressDatabase.dbo.exceltest   the excel test procedure works fine by itself but as soon as i use insert it crashes.  any ideas??    

Rounding problem for money output parameter from stored proc

I have a GridView sourced by a SQL Server stored procedure with several databound fields, including a money column for item values. My stored proc returns the data through a SELECT and everything looks good. The stored proc also has a single output parameter that sums the total value of the displayed data, and this value is displayed in a label above the GridView. The output parameter (defined as money on the SQL Server side) is passed back to the app in good shape, but is rounded when displayed in the DataSource_Selected event handler. So a total of $30,155.22 in SQL Server shows up as $30155 on my aspx.In debugging, I have confirmed that the value leaving SQL Server is correct (30155.2200), and I can see that the value coming IN to the ASP-side event handler is already rounded. What do I need to do here to coerce/force it to show as money/currency?Relevant snippets:Stored Proc:... @onHandValue money OUT...  CREATE TABLE #tempValueOnHand(... valueOnHand money )... set @onHandValue = (select round(sum(valueOnHand), 2) from #tempValueOnHand) set @onHandValue = (select sum(valueOnHand) from #tempValueOnHand)And again, the output parameter @onHandValue is coming back, just already rounded.On the ASP side:    Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourc

Deadlocking in stored proc calls (sql 2008)


The deadlock revolves around 3 tables (A, B, and C), shown simplified below.


Table “A”:


     [A_ID] [bigint] NOT NULL,

     [A_DeleteID] [bigint] NULL,


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