.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

Proc Aggregation utility?

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
Hi What  the Proc Aggregation is used for? Thanks The complexity resides in the simplicity

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.

Utility Spotlight: 12 Steps To Faster Web Pages With Visual Round Trip Analyzer


Download Visual Round-trip Analyzer (VRTA) to uncover the root of your Web page loading problems and identify these 12 common ailments.

Jim Pierson

MSDN Magazine November 2008

Inside MSDN: Building the MSDN Aggregation System


Have you visited msdn2. microsoft. com? It's the new online face of the MSDN® Developer Tools and Enterprise Server documentation. The infrastructure behind it includes a system developed by my team at Microsoft for aggregating information related to our content.

John Mollman

MSDN Magazine July 2006

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

Pivot w/o aggregation...query not working

Hi all--Given this query, I need to make this query into a pivot table: select Personnel_id, First Name, Last Name, E-mail, Division, Region, Country, Manager, Question, Answer, Date Completed from #pivot where Question Answer 01. Received File No 02. Read File Yes 03. Signed File Yes ... Needs to look like this: Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ... Test01 Test No Yes Yes I have the following pivot query: SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM (SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM #pivot) topvt PIVOT (MAX([Personnel_id]) FOR Answer in ([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt ORDER BY pvt.[Personnel_id] I get this error: Msg 207, Level 16, State 1, Line 83 Invalid column name 'Personnel_id'. Any ideas? Thanks!

Does SQL Server have to load and init statics every time a SQL/CLR managed proc is invoked?

We are working with SQL/CLR stored procedures, triggers etc and it seems that all assemblies we use are unloaded/loaded every time a proc or trigger or TVF is called. Is this true? Is there any way to retain data between invocations of procs? for example can I create some static struct or object that I can setup once and then just refer to after that, so that unless we terminate SQL server, the static data is always in memory? Thanks PS: This was originally posted here, but that forum seems to be rarely frequented.  

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

2005 proc not working in 2008

following proc works in 2005, and fails in 2008 returning "Msg 207, Level 16, State 1, Invalid column name 'xxx' " message.  Anyone have any ideas? The proc alters an existing proc in another db to return a different value: existing proc: /******************************************************* USE [tstDB] GO CREATE PROCEDURE [dbo].[sp_GetName] AS SELECT "aaa" AS strDescription GO /******************************************************* proc to alter return value: /******************************************************* USE [srcDB] GO Drop Procedure up_chg_name go Create Procedure up_chg_name (@chgName varchar(20),@db varchar(100)) AS Declare @ls_sql nvarchar(255) set @ls_sql='USE '+@db+' EXECUTE ('' ALTER PROCEDURE dbo.sp_GetName AS SELECT "'+@chgName+'" AS strName '')' exec(@ls_sql) GO /******************************************************* execute the altering proc: /******************************************************* USE [srcDB] GO exec up_chg_name @chgName=xxx',@db='tstDB' go /******************************************************* this fails with: "Msg 207, Level 16, State 1, Invalid column name 'xxx' message" - any ideas? Thanks  

Time Intelligence Filter without aggregation

I have a line graph where I am trying to display the drop in a census over time. The report was built using the date level, so If I am showing the current month, or the last 10 days, The graph looks great. The problem is, I want users to be able to sort by Quarter, Semester, and Year. When I do this, I want to display the data by month. That is easy enough. However, when I do that, it adds every day of the month into one value, so instead of displaying a number like, "300," it displays something along the lines of "10k."  What I would like to do is only care about the last day of each month once you get the quarter, semester, and year levels. One solution I tried was setting my time intelligence as Quarter.firstmonth.lastday, Quarter.month.lastday, Quarter.lastmonth.lastday.  This works, if you happen to be in the second month of the quarter. I have also tried passing the second value as Quarter.(firstmonth+1).lastday and Quarter.(lastmonth-1).lastday, but dashboard designer doesn't like that syntax. I have also tried editing the aggregation settings on the measurements in my cube to "no aggregation," and "Last." When I do that and try to deploy the cube, it tells me I can't do that because I am not running the Enterprise version of SQL server 2008. I know the issue just has to be with my formulas, because it looked ok

How to Create SSIS Package to RUN the preweblog utility exe for IIS Logfiles

Hello ,                   I need to create SSIS Package for Microsoft tool  preweblog utility.exe to modify the IIS LogFiles. Kindly provide me some information related to it.

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