Monday, 7 December 2009
ASP .NET: How to get parameter in url ( by C# for .net)
Tuesday, 1 December 2009
ASP .NET: Refresh page every x secs
Place inside <~head~> to refresh page after 5 seconds:
<~meta http-equiv="refresh" content="5" /~>
Redirect to http://example.com/ after 5 seconds:
<~meta http-equiv="refresh" content="5;url=http://example.com/" /~>
Redirect to http://example.com/ immediately:
<~meta http-equiv="refresh" content="0;url=http://example.com/" /~>
http://en.wikipedia.org/wiki/Meta_refresh
Friday, 27 November 2009
ASP .NET setting up mail server (SMTP)
For example if using google:
SMTP = smtp.gmail.com
credentialsUN = johnilett@gmail.com
credentialsPW = YOURPASSWORD
returnEmail = noreply@gmail.com - Dummy
using System.Net.Mail;
protected void mail(string email, string name)
{
getCredentials();
MailMessage mail = new MailMessage();
mail.To.Add(email);
mail.From = new MailAddress(credentialsUN);
mail.ReplyTo = new MailAddress(credentialsUN);
mail.Subject = "HR DW Website Password";
mail.IsBodyHtml = true;
string Body = "<~html~><~/body~>Dear " + name + "," + "<~br /~>" + "<~br /~>" + "Your password for the HR DW website is: " + "" + "<~b~>" + password + "<~/b~>";
Body += "<~br /~>" + "<~br /~>" + "Thanks," + "<~br /~>" + "HR DW Support.<~/body~><~/html~>";
mail.Body = Body;
SmtpClient smtp = new SmtpClient();
smtp.Host = credentialsSMTP;
//Dont need these 2 if unsecure SMTP
smtp.Credentials = new System.Net.NetworkCredential(credentialsUN, credentialsPW);
smtp.EnableSsl = true;
smtp.Send(mail);
}
*replace ~ with ""
asp .net: Creating NameSpace
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace YOURNAMESPACENAME
{
///
/// Summary description for of Class
///
public class YOURCLASSNAME
{
//METHODS AND VALUES - PUBLIC
}
}
Step 2: Calls your namespace in aspx.cs class
using YOURNAMESPACENAME;
create new object in Main class for YOURCLASSNAME -
YOURCLASSNAME ClassName = new YOURCLASSNAME ();
Now call your methods and values, etc:
ClassName.MethodName()
Wednesday, 11 November 2009
ASP: gridview delete confirmation box
...
Text="Delete" />
Tuesday, 3 November 2009
Javascript: Confirm logout and save document
if (confirm('Are you sure you would like to logout?')) {
//document.forms['form1'].submit();
window.open('', '_self');
window.close();
};
}
Friday, 30 October 2009
SQL: Try Catch
-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
EG2:CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
GO
BEGIN TRY
EXECUTE usp_ExampleProc;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SQL: Reset column identity
DBCC CHECKIDENT (’tablename’, NORESEED)
--Reset
DBCC CHECKIDENT (’tablename’, RESEED, 1)
Thursday, 29 October 2009
ASP: Creating fading edges in images
There are several ways to create edges on an image which fade away into the background. One way is as follows:
1. Begin with a canvas which is larger than the image to be blurred, around all four edges. One way to do this is to use the Image->Canvas Size dialog.
2. Use the marquee tool to select an area which is a few pixels in from the edge of the image.
3. Use Select->Invert to select the border around the image.
4.
Use Select->Feather to blur the selected area by a few pixels. A value of 4 pixels was used here.
5.
Now, use Filter->Blur-> Gaussian Blur to produce the final effect. A value of 4.1 was used.
6.
Finally, crop any unneeded whitespace around the image and save it.
http://www.mtholyoke.edu/help/creating-pages/imaging/xfade.shtml
Tuesday, 27 October 2009
ADO .NET: Connect to SP
SqlCommand cmd1 = new SqlCommand("[staging].[USP_ETL_INSERT_FILE_ETLQUEUE]");
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add(new SqlParameter("@CartesisID", cartesisID));
cmd1.Parameters.Add(new SqlParameter("@ReportingPeriod",reportingPeriod));
cmd1.Parameters.Add(new SqlParameter("@FilePath", fileNameFull));
cmd1.Parameters.Add(new SqlParameter("@UploadedBy", Request.ServerVariables["AUTH_USER"]));
cmd1.Connection = conn;
cmd1.ExecuteNonQuery();
conn.Close();
//Execute SSIS Package
string packagelocation = System.Configuration.ConfigurationSettings.AppSettings["SSISLocation"].ToString();
string packageConfiglocation = System.Configuration.ConfigurationSettings.AppSettings["SSISConfigLocation"].ToString();
packagelocation = packagelocation + "VerifyFile.dtsx";
Package verifyPackage = app.LoadPackage(packagelocation, null);
verifyPackage.ImportConfigurationFile(packageConfiglocation + "VerifyFileConfig.dtsConfig");
verifyPackage.Variables["FileName"].Value = fileNameFull;
DTSExecResult result = verifyPackage.Execute();
Thursday, 22 October 2009
EXCEL: Convert xls to txt, csv
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ExportToTextFile' This exports a sheet or range to a text file, using a' user-defined separator character.''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Sub DoTheExport() ExportToTextFile FName:="C:\HRDW\200908.txt", Sep:="~", _ SelectionOnly:=False, AppendData:=TrueEnd Sub
Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean, _ AppendData As Boolean)
Dim WholeLine As StringDim FNum As IntegerDim RowNdx As LongDim ColNdx As IntegerDim StartRow As LongDim EndRow As LongDim StartCol As IntegerDim EndCol As IntegerDim CellValue As String
Application.ScreenUpdating = FalseOn Error GoTo EndMacro:FNum = FreeFile
If SelectionOnly = True Then With Selection StartRow = .Cells(1).Row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End WithElse With ActiveSheet.UsedRange StartRow = .Cells(1).Row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End WithEnd If
If AppendData = True Then Open FName For Append Access Write As #FNumElse Open FName For Output Access Write As #FNumEnd If
For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = Cells(RowNdx, ColNdx).Text End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLineNext RowNdx
EndMacro:On Error GoTo 0Application.ScreenUpdating = TrueClose #FNum
End Sub''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' END ExportTextFile''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Wednesday, 21 October 2009
ASP: Increase Session State
The default is 20mins.
<system.web>
<sessionState timeout ="20" mode ="InProc" />
</system.web>
Tuesday, 20 October 2009
ASP: Image mouse over
The main use of function is to prevent us from writing the same code again and again because we can call the same function with a different set of parameters.
In the image roll-over script of the previous session, the code for changing the image was placed inside the event handlers. This is fine if there are one or two images. In case of several images, it is advisable to pack the code in a function that can be called repeatedly by event handlers using different parameters.
There are two important things in an image roll-over code. One is the image name and the other is the image source. Thus, we have to construct a function that accepts these two arguments and changes the image source.
Step 1 (JavaScript):
function roll_over(img_name, img_src)
{
document[img_name].src = img_src;
}
Step 2 (HTML):
<A HREF="some.html" onmouseover="roll_over('but1', 'icon2.gif')"
onmouseout="roll_over('but1', 'icon1.gif')">
<IMG SRC="icon1.gif" WIDTH="100" HEIGHT="50"
NAME="but1" BORDER="0">
</A>
Thursday, 1 October 2009
Wednesday, 23 September 2009
MDX: Performance Guide
MDX Performance Hints
Here are some simple steps you can take to ensure that you get the best performance from your calculations in SQL Server Analysis Services. Some of these exploit new functions or features in SQL Server Analysis Services 2005 while others apply to both this release and SQL Server Analysis Services 2000.
Use your attributes!
In SQL Server Analysis Services 2000, the filter function was a common means of selecting members from a set whose member property satisfied some condition; for example male customers could be expressed as :
Filter(Customer.name.Name.members, Customer.name.currentmember.properties(“Gender”) = “Male”)
Don’t do this in SQL Server Analysis Services 2005. Instead, create an attribute hierarchy Customers.Gender and do this:
(Customers.Gender.Male, Measures.Sales)
The attribute hierarchy can be made not visible if they should not be seen by end-users and still be available in MDX expressions.
The average sales to male customers can be expressed as this:
Avg(Customer.Name.Name.members*Customers.Gender.Male, Measures.Sales)
Why is .properties so much slower? It’s because getting the value of a member forces each member to be retrieved so the property value can be retrieved and queried. Exists is much faster because internal storage structures can be exploited directly
Use Is Comparison Operator
When comparing members, compare the objects using the IS comparison operator. Don’t do this:
Iif( [Currency Code].currentmember.Name = “USA”], …)
Do this:
Iif( [Currency Code].currentmember IS [Currency Code].[USA], …)
Translating the members to strings takes time and doesn’t need to be done.
Use NonEmpty Function
The NonEmpty function (new in SQL Server Analysis Services 2005) is optimized for removing empty tuples. So instead of doing this to get the customers who bought an Xbox,
Filter(Customer.Name.members, not IsEmpty( ([Measures].[Unit Sales], [Product].[Name].[Xbox])
Do this:
NonEmpty (Customer.Name.members, ([Measures].[Unit Sales], [Product].[Name].[Xbox]))
Note that Filter(
Another example. Instead of this:
Count(Filter([year and month].[Month],Not IsEmpty(([Project Count], [Layer].[Layer].[web])) or Not IsEmpty(([Project Count], [Layer].[Layer].[server]))))
Do this:
Count(NonEmpty ([year and month].[Month],{([Project Count], [Layer].[Layer].[web]), ([Project Count], [Layer].[Layer].[server])})
Calculation Non Empty Behavior
Whether or not an expression resolves to null is important for two major reasons. First, most client applications use the “non empty” key word in a query. If you can tell the engine that you know an expression will evaluate to null, then needn’t be computed and can be eliminated from the query results before the expression is evaluated..
Second, the calculation engine can use the knowledge of a calculation’s non empty behavior even when the “non empty” keyword is not used. If a cell’s expression evaluates to null, then it needn’t be computed during query evaluation.
Note that the current distinction between how the engine uses an expression’s non empty behavior is really an artifact of the engine’s design and we’re working to eliminate the distinction in a future release. For now, we make this distinction because one or both optimizations is made depending how the calculation property non_empty_behavior (NEB) is defined - we’ll call the first “Non Empty” and the second “Calculation Engine”
When an expression’s NEB is defined, the author is guaranteeing the result is null when the NEB is null and not null when NEB is not null. This information is used internally by the calculation engine to build the query plan.
What can/should be specified in Non_Empty_Behavior (“NEB”) clauses is first and foremost determined by correctness/semantics aspects, before taking into account any performance goals.
Calculation type NEB applies to
NEB expressions recognized
Calculation Engine SP2 support
Non Empty SP2 support
Example
Calculated Measure
Constant measure reference
Yes
Yes
With Member Measures.DollarSales As Measures.Sales / Measures.ExchangeRate,
NEB = Measures.Sales
Calculated Measure
{List of two or more constant measure references}
No
Yes
With Member Measures.Profit As Measures.Sales – Measures.Cost,
NEB = {Measures.Sales, Measures.Cost}
Any (calculated member, script assignment, calculated cell)
Constant tuple reference
Constant single-measure set
Yes
No
Scope [Measures].[store cost];
This = iif( [Measures].[Exchange Rate]>0, [Measures].[Store Cost]/[Measures].[Exchange Rate], null );
Non_Empty_Behavior(This) = [Measures].[Store Cost];
End Scope;
Here’s a summary of representative, common cases for using NEB. Note that defining NEB is never necessary to achieve the correct calculation results but defining it incorrectly can result in incorrect results. It should only be used if correctness implications are well understood and performance is poor without it.
Measures.M1 + or – Measures.M2
- In general, MUST specify both measures in NEB, for correctness reasons. Using SP2 is required for this.
- In particular, if both measures belong to the same measure group, MAY specify just one of them in NEB (could have better performance).
Measures.M1 * Measures.M2
- In general, one cannot specify any correct NEB for this expression because the result is null when either M1 or M2 is null.
- However, if it is guaranteed that one of the measures is never null (e.g. a currency exchange rate), then the other measure may be used in NEB.
- In particular, if it guaranteed that, for any given cell, either both measures are null, or both are non-null (e.g. they belong to the same measure group), then one may specify both measures in NEB (SP2 required), or specify a single measure.
Measures.M1 / Measures.M2
- The first measure (the numerator, M1) must be used when defining NEB.
Be very careful. This property is generally abused by those that use it. If this property is defined incorrectly, query results can be incorrect.
Don’t use LookupCube
It’s expensive. For any non-trivial usage, create a single cube that has the values you need.
Conditional Calculations
The IIF function can play havoc with existing engine calculation optimizations – the cause of the problem is that the optimizations can’t be brought to bear without knowing the expression that applies to a cell when the cell is being evaluated. We’re looking at the resolving this in a future release but in the meantime there may be some steps you can take.
Conditional Calculations and Scoping
The IIF is sometimes used to scope a calculation on a set of members by relying on the condition; for example:
This =
IIF([account].[Account Type].currentmember IS [Account].[Account Type].[Flow],
)
Don’t do this. This can expressed differently with much better performance:
Scope ([Account].[Account Type].[Flow]);
This =
End Scope;
Scope ([Account].[Account Type].[Account Type].members –
[Account].[Account Type].[Flow]);
This =
End Scope;
Conditional Calculations and NULL
If you can, having one of the arguments as NULL can significantly improve performance:
IIF (
If this can’t be done, oddly enough this expression can be much faster (but only in AS2005 SP2 and later) and is worthy of experimentation:
IIF (
(If
Conditional Calculations and Non Empty Behavior
If the expression has a simple non empty behavior (a subject matter discussed elsewhere in this document), then exploit it. For example, this expression flips the sign of an expression based on a condition:
IIF (
If
Single Branch Conditions
If you must use a condition and the calculation only applies when a condition evaluates to true, do not do something like this to have the cell retain its value:
This = IIF(
CalculationPassValue(measures.currentmember,-1,relative)
);
But do this instead:
IF
Use ValidMeasure to Reduce Calculation Space
In SQL Server Analysis Services 2005, each measure group has the property IgnoreUnrelatedDimensions that defines how dimensions unrelated the measure are treated – either the dimension is ignored, or moving off the default member results ion a null value for the measure.
When defining calculations, don’t rely on this behavior but use the validmeasure function. For example:
scope(leaves([Time]), [Currency].[Currency].members - [Currency].[Currency].[Currency].[USA]
Scope [Measures].[store Sales];
This = iif( isempty(validmeasure([Measures].[Exchange Rate])), null, [Measures].[Store Sales]/validmeasure([Measures].[Exchange Rate]));
End Scope;
Use Sum or Aggregate instead of Additions
Instead of doing this:
Create Member measures.x as
(Sales, Country.USA) + (Sales, Country.Canada) + (Sales, Country.Mexico)…
Do this:
Create Member measures.x as Sum({Country.USA, Country.Canada, Country.Mexico}, Sales)
Clump Hierarchies in Big Crossjoins
If you can, keep hierarchies from the same dimension together. Why? Because internally, the engine does an exists between adjacents sets in a crossjoin. If a hierarchy from a different dimension is inserted between two hierarchies from the same dimension, this exists function happens afterwards and not before. This can greatly expand the space and impact performance.
Caching Calculation Results
The formula engine has an internal cache to reuse calculation results. But to cache something, the result has to be an addressable cell (or tuple) in the cube space.
For example, I have an application that calculates the ratio of a value to the maximum that value is over a set and renders the result as a histogram. The first way I tried to do this was with a calculation something like this:
with
member measures.y as
measures.[unit sales]
/
max(customers.[name].[name].members, measures.[unit sales])
select
measures.y on 0,
customers.[name].[name].members on 1
from sales
This took about 15 seconds on my laptop. The calculation is inefficient in that the maximum value does not change over the set but the expression forces its reevaluation as many times as there are customers (and there are over 10,000 of them). This approach is much better:
with
member measures.x as
max(customers.[name].[name].members, measures.[unit sales])
member measures.y as
measures.[unit sales]
/
(measures.x,[Customers].[Customers].[All Customers] )
select
measures.y on 0,
customers.[name].[name].members on 1
from sales
This takes less than a second! Over an order of magnitude performance improvement! Fun!
Now here’s a quiz – why must the expression for measures.y contain the member [Customers].[Customers].[All Customers] in the denominator? In other words, why can’t I do this:
with
member measures.x as
max(customers.[name].[name].members, measures.[unit sales])
member measures.y as
measures.[unit sales]
/
(measures.x)
select
measures.y on 0,
customers.[name].[name].members on 1
from sales
It is because we want to reference a cached value. Without [Customers].[Customers].[All Customers] overwriting the customer dimension’s attributes, the cell changes with a reference to the customer’s name and the calculation goes back to being reevaluated in each cell.
In the next release, we plan to do this automatically. But for now you have to give the engine a hint that the calculation doesn’t change.
http://www.sqlserveranalysisservices.com/OLAPPapers/MDX%20Performance%20Hintsv1.htm
Tuesday, 22 September 2009
SQL: Row Number Example
FirstName,
LastName,
[Row Number]=ROW_NUMBER() OVER(ORDER BY SalesYTD DESC), SalesYTD
FROM x
Monday, 21 September 2009
SSRS: Error: Report Parameter 'Value1' is read-only and cannot be modified
Thursday, 17 September 2009
SSAS: Attribute relationship
Setting proper attribute relationships for the attributes of a dimension is essential for query performance and usability. As Mosha wrote in his blog, it might be the most important advice for cube designers to set the attribute relationship correctly. The full article can be viewed here and it is very detailed.
I still saw some cases recently where the improper use of attribute relationship settings caused wrong query results (which might even be worse than a low performance), so I decided to post a wrap up of this topic here.
First, you need to know that setting attribute relationship has these benefits:
Better query performance
More efficient methods for the cube to store the data of the hierarchies
Easier way to write MDX queries (as the dependant attribute is correctly positioned and can be referenced e.g. by currentmember), see below for an example
Dependant attribute may be used as metadata (especially helpful when the dependant attribute has it property "Enable attribute hierarchy" set to "false")
Higher ranked attribute may be used as "Order-By"-attribute.
Let me provide an example for the MDX query. Think of a sample cube with a date dimension containing two attributes for month and monthname and think about the following query:
with member MonthMember as [Dim Time].[Monthname].currentmember.NAME
select MonthMember on rows, [Dim Time].[Month].[Month] on columns
from [TestCube]
if there is no relationship between the month and monthname-attribute the monthname-attribute is not changed when selecting the month-attribute and therefor resides its default which is 'All'.
But if there is a relationship, binding the monthname-attribute to the month-attribute, the monthname-attribute is automatically set to the corresponding member:
This shows how query syntax is influenced by the proper use of attribute relationship. In the first example we had to try something with the linkmember() MDX-function to achieve the same result. So designing the attribute relationship in a proper way leads to easier to understand queries with a better performance.
The data was loaded from a very simple date dimension table like shown below:
The attribute relationship can be modeled in BIDS when designing a dimension. After creating the dimension using the wizard, all attributes are associated to the key attribute of the dimension as shown below:
The attribute 'Month' and 'Monthname' have no relationship but are simply tied to the attribute "Dim Time' which is the key attribute for our simple time dimension. If you build a hierarchy, like the YearMonth-hierarchy in the screenshot above, you'll notice a yellow warning sign informing you that there is no relationship between Month and Year. You'll get further information when placing the mouse coursor on the warning sign:
Whenever you see this yellow warning sign we're talking of a non-natural hierarchy.
The relationship can easily be set by dragging the 'Year' attribute to the 'Month' attribute. Now the same dimension looks like this:
Notice that the yellow warning sign in the hierarchy title has disappeared. In this case we're talking of a natural hierarchy.
But be warned: This doesn't mean that your dimension is healthy now. For our simple example we actually made a big mistake. If you check the dimension in the dimension browser, you'll see something like this:
There are no more months for the years 2006 to 2008 anymore! This also means that no fact data will be displayed for those months and that the aggregation will be wrong.
If you model an attribute relationship like Month -> Year you have to be absolutely sure that every month corresponds to exactly one year. You can verify this using a simple SQL query like the one below:
select count(Year) from dbo.DimTime group by Month having count(Year)>1
This query should return no rows at all but in our case it returns
So every month in our source table is mapped to four years which breaks our attribute relationship above.
If you're having BIDS Helper installed on your machine you can also do a dimension health check from the dimension context menu.
In our case the result looks like this:
You can clearly see the rows that break our relationship.
So in this simple example it would have been a bad advice to simply set the attribute relationship as we did above. You should always check the relationship in your source data before designing such a relationship.
And even if the users say that the relationship is unique, you shouldn't rely on that. For example a product might belong to a unique product group. But what happens if the association changes over time and you want to keep track of the changes using a slowly changing dimension of type 2. This can also easily break up your attribute relationship. And as the MDX results might differ after changing the relationship, this could also have an influence on existing reports. So it's a good advise to carefully design your hierarchies before you start creating reports, because it's not difficult turning a non-natural hierarchy into a natural one.
SQL Server 2008:
http://www.sqlskills.com/blogs/stacia/post/SQL-Server-2008-Attribute-Relationship-Designer.aspx
http://oraylis-olap.spaces.live.com/Blog/cns!61F98448A5E17D57!228.entry?wa=wsignin1.0
SSRS: External Images Issue and Configuring the Unattended Execution Account
Firstly, check the image url is valid and can be accessed through an IE page.
If this is the case you must check you have specified a Unattended Execution Account otherwise it will run as anonymous. In order to do this go to the report configuration and connect to the particular report instance. Under Execution account specify an account and password then click apply.
Now try again.
Friday, 11 September 2009
SQL: SQL Server 2008 features and Editions
SQL 2008 Licencing and Pricing Structure:
http://www.microsoft.com/sqlserver/2008/en/us/licensing.aspx
Monday, 10 August 2009
SQL: Generating Unique Identifier Keys
UPDATE [dbo].[DimManagement]
SET [ManagementUniqueKey] = NEWID()
,[CartesisUniqueKey] = NEWID()
Wednesday, 8 July 2009
SSRS: IIS 7 and Reportviewer
Resolution:
Open Internet Information Services (IIS) Manager and select your Web application.
Under IIS area, double-click on Handler Mappings icon.
At the Action pane on your right, click on Add Managed Handler.
At the Add Managed Handler dialog, enter the following:Request path: Reserved.ReportViewerWebControl.axdType: Microsoft.Reporting.WebForms.HttpHandlerName: Reserved-ReportViewerWebControl-axd
Click OK.
Reserved-ReportViewerWebControl-axd handler is now added to your Handler Mappings list. Notice that the following line has also been added to your Web.config file under the system.webserver's handler section:
For more information, see
http://forums.asp.net/p/1360494/2814290.aspx#2814290
Friday, 26 June 2009
SSRS: Performance Tuning
- Open the report config file
- modify RecycleTime parameter from the default 720 to 28800.
Thursday, 25 June 2009
SSIS: VBScript for Excel inserting 0 and get columns
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Diagnostics;
namespace ST_f1f4f90e7d8e4a25b9330e1d946d8c95.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
int error_flag = 0;
Dts.Variables["ErrorFileName"].Value = Dts.Variables["FileName"].Value.ToString().Replace(".xls", "_errors.xls");
//SET NUMERICS IN EXCEL FILE TO ZERO TO AVOID CRAPPY JET DRIVER INCORRECTLY SETTING COLUMN TYPES AT RUNTIME
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + Dts.Variables["FileName"].Value.ToString() + @";
Extended Properties=
""Excel 8.0;HDR=YES;""";
System.Data.OleDb.OleDbConnection ExcelConnection =
new System.Data.OleDb.OleDbConnection
(ConnectionString);
string ExcelQuery;
List
//"[AnnualSalary]",
"[Salaries]",
"[Overtime]",
"[Pay In Lieu Of Holiday]",
"[Severances]",
"[Employers Social Taxes]",
"[SPOT Bonus]",
"[STIP Bonus]",
"[Contractual STIP]",
"[Earnout STIP]",
"[Sales Commision Bonus]",
"[Taxable Value Of PSA Payout]",
"[Taxable Value Of Other Restricted Payout]",
"[Taxable Value Of LEAP Payout]",
"[Taxable Value Of Worldwide Ownership Plan On Exercise Payout]",
"[Taxable Value Of Executive Option Plan On Exercise Payout]",
"[Pensions - Defined Contribution Schemes]",
"[Pensions - Defined Benefit Schemes]",
"[Medical Insurance]",
"[Life Assurance]",
"[PHI/LTD]",
"[Critical Illness]",
"[Car - Lease Or Depreciations]",
"[Car Allowance]",
"[Other Car]",
"[Housing]",
"[School Fees]",
"[COLA]",
"[Home Leave]",
"[Club Membership]",
"[Relocation Expenses]"};
try
{
System.Data.OleDb.OleDbCommand ExcelCommand;
//UPDATE
ExcelConnection.Open();
ExcelCommand = new System.Data.OleDb.OleDbCommand();
ExcelCommand.Connection = ExcelConnection;
int nocols = numericExcelColumns.Count;
//Fill DataTable with columns information
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", ExcelConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
//DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
//DataGrid1.DataBind();
DataTable myDataTable = ExcelConnection.GetSchema("Columns");//, new string[] { "Test", "Tables" });
string columnName;
foreach (string excelColumn in numericExcelColumns)
{
//For every Column in the table
for (int i = 0; i < myDataSet.Tables[0].Columns.Count; i++)
{
//Get column name and type
columnName = myDataSet.Tables[0].Columns[i].ToString();
if ("[" + columnName + "]" == excelColumn)
{
error_flag = error_flag + 1;
break;
}
}
ExcelQuery = string.Format("update [Sheet1$] set {0} = '0' where {0} is null and EmployeeID is not null", excelColumn);
ExcelCommand.CommandText = ExcelQuery;
ExcelCommand.ExecuteNonQuery();
}
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '1' where len([SPOT Bonus]) = 0";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] = 1";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] is null";//works
//ExcelQuery = "update [Sheet1$] set [Overtime] = '0' where [Overtime] is null";//works
ExcelCommand = null;
ExcelConnection.Close();
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
if (error_flag == 30)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}
ASP. NET: Retrieve Excel file columns
string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\exceltest.xls;" + "Extended Properties=Excel 8.0;"; //You must use the $ after the object you reference in the spreadsheet OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Tabelle1$]", strConn);
DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet, "ExcelInfo"); DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView; DataGrid1.DataBind();
}
Monday, 22 June 2009
SSIS: Run packages from Stored Procedure
Name : [USP_SSIS_Run_Packages]
Description : Run SSIS Packages. This package
is called by the website
Created by : John Ilett
Modified Date: 19 June 2009
Ex:
Exec [dbo].[USP_SSIS_Run_Packages] 'C:\HRDW\Data\Uploads\3\200912.xls'
***********************************************************************/
/**********************************************************************
Activate xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
***********************************************************************/
ALTER PROCEDURE [dbo].[USP_SSIS_Run_Packages] (
@FileName nvarchar(200)
)
AS
BEGIN
DECLARE @ServerName VARCHAR(30), @ReturnValue int, @Cmd varchar(1000)
,@FilePath VARCHAR(100)
,@PackageName VARCHAR(50)
,@ParamMappings VARCHAR(MAX)
SET @ReturnValue = -1
SET @ServerName = 'localhost'
SET @FilePath = 'C:\HRDW\SSISPackageConfigs\VerifyFile.dtsx'
SET @PackageName = 'C:\HRDW\SSISPackageConfigs\VerifyFile.dtsx'
SET @Cmd = 'DTExec /F "' + @PackageName + '"' + ' /SET \Package.Variables[FileName].Properties[Value];' + '"' + @FileName + '"'
EXECUTE @ReturnValue = master..xp_cmdshell @Cmd--,NO_OUTPUT
RETURN @ReturnValue
SELECT @ReturnValue [Result]
--SET @Cmd = 'DTEXEC /sq "VerifyFile" /ser ' + @ServerName + '/Set \Package.Variables[User::FileName].Properties[Value];' + @FileName
--SET @Cmd = 'DTEXEC /F "' + @FilePath + '"' + ' /Set \Package.Variables[User::FileName].Properties[Value];' + @FileName
--SET @Cmd = 'DTEXEC /SER ' + @ServerName + ' /SQL ' + @PackageName + ' '
--SET @ParamMappings = '/SET Package.Variables[FileName].Value; ' + '''' + @FileName + ''''
--SET @Cmd = @Cmd + @ParamMappings
----SET @Cmd = 'DTExec /SER ' + @ServerName + ' ' +
----' /SQL ' + --+ '\BuildSCCDW ' --Location of the package stored in the mdb
------' /CONF "\\ConfigFilePath.dtsConfig" '
----' /SET \Package.Variables[ImportUserID].Value; ' +
----' /U "ASPNET" /P "password" '
--PRINT @Cmd
--EXEC sp_xp_cmdshell_proxy_account 'wpp1221\ASPNET_user', 'password';
END
Thursday, 18 June 2009
ASP .NET: Website testing
SQL injection: To test for SQL injection bugs, peruse the application and find places where users can enter text, such as where the text is used to perform a lookup function, according to Breach. Then type a single quote character and some text: If the application shows an error message from your database, then you're likely housing an SQL injection bug.
Cross-site scripting (XSS): Find areas in your application that accept user input, such as a page where users can send in their feedback or reviews of a product, for example. Try submitting this text -- a less-than sign, the word "script," and then the greater-than sign (with no spaces in between): ! script !
If that text displays where you reload the page, then your site has an XSS vulnerability, according to Breach.
Session hijacking: If your application has a session identifier number in the URL -- which Breach does not recommend -- decrease that number by one and reload the page. The app has a session hijacking vulnerability if the app then "sees" you as a different user. And if you don't have a session identifier in the URL, load a plug-in onto your browser that lets you view and modify cookies, according to Breach, which sells Web application firewalls. "Look in the cookie for session identifiers and perform the same test," according to Breach.
ASP. NET: Web.Config File example
!add key="ReportServerURL" value="http://ServerName/ReportServer"/!
!add key="ReportFolder" value="ReportFolder"/! !/appSettings! !connectionStrings! !add name="HRDWConnString" connectionString="Data Source=.;Initial Catalog='HR DW';Integrated Security=True" providerName="System.Data.SqlClient"/! !/connectionStrings!
!trace enabled="false" requestLimit="10" pageOutput="true" traceMode="SortByTime" localOnly="true" /!
!customErrors mode="RemoteOnly" /!
!authentication mode="Windows"/!
!identity impersonate="true"/!
authorization
allow users = "*"/
/authorization
ASP .NET Security considerations
As Session Cookies store on Server side. Cookless ones mean hackers can get around through URL manipulation.
2. Use Server.HtmlEncode() method to encode any user input:
While this vulnerability has been hot-fixed, it is nevertheless important that you employ added precaution. One good method is to use the Server.HtmlEncode() method to encode all user inputs into HTML-encode strings:
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click
lblMessage.Text = Server.HtmlEncode(txtName.Text)
End Sub
3. SQL Injection. If you pass username straight to where clause then hacker can display all users:
They can enter into the password:
xyz' union select @@servername, @@servicename, @@version --
or xyz' or UserId like '%
correct method:
Private Sub btnLogin_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnsecureLogin.Click SqlConnection1.Open() Dim str As String = "SELECT * FROM Users WHERE " & _ "UserID=@userID AND Password=@password" Dim comm As New SqlCommand(str, SqlConnection1) comm.Parameters.Add("@userID", txtName.Text) comm.Parameters.Add("@password", txtPassword.Text)
Dim reader As SqlDataReader = comm.ExecuteReader() If Not reader.HasRows Then _ Response.Write("Login failed. Please try again") While reader.Read() Response.Write("Hello " & reader("UserName")) End WhileEnd Sub
4. Validate user inputs
Always check for length and wildcard characters.
5. Use hashing to store password:
A much better way to store passwords in your database is to use hashing. Hashing is a one-way process of mapping data (plain text) of any length to a unique fixed-length byte sequence. This fixed-length byte sequence is called a hash. Statistically, two different pieces of data would not generate the same hash. And a hash cannot be used to reverse-generate the plain text. In the case of saving passwords in the database, saving the hash value of each password is preferred over the saving the plain password. When a user logs in, the hash value of the password is computed and then compared to the hash value stored in the database. In this case, even if the database server is compromised, the hackers have no way of knowing the users’ real passwords (though he could still alter the hash value of a user’s password to one he generated himself and gain illegal access).
The following function shows how to use the SHA1 hash algorithm implementation in the .NET Framework:
Public Function ComputeHashValue(ByVal data() As Byte) As Byte()
Dim hashAlg As SHA1 = SHA1.Create
Dim hashvalue() As Byte = hashAlg.ComputeHash(data)
Return hashvalue
End Function
You could derive the hash value of a password like this:
Dim hashValue() As Byte
hashValue = ComputeHashValue(Encoding.ASCII.GetBytes(txtPassword.Text))
The hash value could then be stored in place of the user’s password.
Tip 5—Encrypt Sensitive DataASP.NET Web developers know that it is sometimes useful to store information such as database connection strings in the Web.config file rather than hardcode them in the application. Doing so allows the database server to be changed without modifying and recompiling the application. However, storing sensitive information such as the connection string (which may contain user information and password) in plain text format in Web.config file is not a very good idea, as Web.config is an XML document stored as a text file and thus easily accessed.
So, a safer way would be to encrypt the sensitive information and store the ciphertext into the Web.config file. There are two types of encryption algorithms that you can use:
Symmetric
Asymmetric Symmetric algorithms encrypt and decrypt information using a common key. It is a simple and efficient way of encrypting/decrypting information. However the use of a common key makes it less secure if more than one party needs to know the key.
Asymmetric algorithms encrypt and decrypt information using a pair of keys. This pair of keys is made up of a private and a public key. Data encrypted using the public key can only be decrypted using the private key and vice versa. Asymmetric algorithms are much more complex and are computationally expensive. However, it is also much more secure than symmetric algorithms.
Listing 1 shows the use of the Rijndael symmetric encryption algorithm implementation in .NET. Listing 2 shows the RSA asymmetric encryption algorithm implementation in .NET.
The functions shown in Listings 1 and 2 will allow you encrypt the sensitive data in your Web application, especially configuration and XML files. Listing 3 shows the supporting function used by the functions in Listings 1 and 2. The supporting function converts a string to a byte array. For example, it converts a string "1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16" to a byte array of {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}.
For asymmetric encryption, you need to first create the pair of private and public keys:
'===========For Asymmetric use=============
Dim publicKey, privateKey As String
Dim RSA As New RSACryptoServiceProvider()
publicKey = RSA.ToXmlString(False) ' get public key
privateKey = RSA.ToXmlString(True) ' get private key
'===========Asymmetric Encryption=============
Dim cipherText as String = AsymmetricEncryption _
(txtAsyPlainText.Text, publicKey)
'===========Asymmetric Decryption=============
Dim plainText as String = AsymmetricDecryption _
(txtAsyCipherText.Text, privateKey)
For symmetric encryption, you need a 16-byte key and Initialization Vector (IV):
'===========Symmetric=============
Dim Key, IV as String
Key="1234567890123456"
IV ="1234567890123456"
Dim cipherText As String = SymmetricEncryption _
(txtSyPlainText.Text, Key, IV)
'===========Symmetric=============
Dim plainText as String = SymmetricDecryption _
(txtSyCipherText.Text, Key, IV)
Because SOAP messages are sent in plain text, Web services could also benefit from encryption. Instead of using SSL to protect the entire communication path (which is overkill), you could use encryption to protect sensitive information such as credit card numbers from prying eyes.
6. *Store secure information in registry (DB connection string UN and Password):
1. Download the aspnet_setreg.exe utility from http://support.microsoft.com/default.aspx?scid=kb;en-us;Q329290.
!configuration!
!appSettings!
!add key="Distributor" value="workstation id=F16;packet size=4096;integrated security=SSPI;data
source=F16;persist security info=True;initial catalog=Distributor" /!
Dim connStr As String = _
ConfigurationSettings.AppSettings("Distributor")
Dim Conn As New SqlConnection(connStr)
5. Next, use the aspnet_setreg.exe utility to add the username and password of the user account that your ASP.NET application will impersonate, into the registry:
C:\>aspnet_setreg -k:Software\ASPNetApp\Identity -u:ASPNETUSER -p:secret
Please edit your configuration to contain the following:
userName="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,userName"
password="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,password"
The DACL on the registry key grants Full Control to System, Administrators, and Creator Owner.
If you have encrypted credentials for the
!sessionState/! configuration section, ensure that the process identity has Read access to the
registry key. Furthermore, if you have configured IIS to access content on a UNC share, the account used to
access the share will need Read access to the registry key.
Regedt32.exe may be used to view/modify registry key permissions.
You may rename the registry subkey and registry value in order to prevent discovery.
!identity impersonate="true"
userName="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,userName"
password="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,password"
/!
a. Turn trace off:
!trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true" /!
b. Turn debug off:
!compilation defaultLanguage="vb" debug="false" /!
c. Set customErrors to RemoteOnly:
!customErrors mode="RemoteOnly" /!
The mode attribute has three possible values:
>"On" always display custom (friendly) messages
>"Off" always display detailed ASP.NET error information.
>"RemoteOnly" displays custom (friendly) messages only to users not running on the local Web server. This setting is recommended for security purposes, so that you do not display application detail information to remote clients.
d. Remove Solution and Project files from your deployment server.
Reference: http://www.devx.com/security/Article/20898
Wednesday, 17 June 2009
SSRS: Remove or Order Exports
Alternative set :
Visible="false"
to hide the export options.
note: This will apply to ALL reports on the server
Tuesday, 16 June 2009
ASP .NET: Assigning Data to DataSet table
WebConfig
connectionStrings
add name="HRDWConnString" connectionString="Data Source=.;Initial Catalog='HR DW';Integrated Security=True" providerName="System.Data.SqlClient"/
/connectionStrings
Code
//Connect to DB
string connStr = ConfigurationManager.ConnectionStrings["YOURDBCONNECTION IN WEB CONFIG"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlCommand comm = conn.CreateCommand();
comm.CommandText = @"SELECT
--[DisplayName]
[FileName]
,[ParameterName]
,[DefaultValue]
FROM [dbo].[vwLookupReportParameterDefaults]";
try
{
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(comm.CommandText.ToString(),conn);
ad.Fill(dataset, "Parameters");
Cache["dataset"] = ds;
//SqlDataReader dr = comm.ExecuteReader();
//while (dr.Read())
//{
//ReportParameters = new ListItem(dr[2].ToString(), dr[3].ToString());
//}
}
catch (Exception ex)
{
Response.Redirect("LoginError.aspx");
}
finally
{
conn.Dispose();
}
DataSet d = new DataSet();
d = (DataSet)Convert.ChangeType(Cache["dataset"], typeof(DataSet));
dt = d.Tables[0];
for (int i =0; i < dt.Rows.Count; i++) {
if (report == dt.Rows[i]["FileName"].ToString())
{
ReportParameter param = new ReportParameter(dt.Rows[i]["ParameterName"].ToString(), dt.Rows[i]["DefaultValue"].ToString());
ReportViewer1.ServerReport.SetParameters(new ReportParameter[] { param });
}
}
ASP .NET: Caching values and Casting
newDataset = (DataSet)Convert.ChangeType(Cache["dataset"], typeof(DataSet)); //Cast cached to newDataset
Details:
http://aspnet.4guysfromrolla.com/articles/022802-1.aspx
Monday, 15 June 2009
SSAS: Current Month in Cube
Current Month = Tail( Filter( [Time].[Month].Members, Not IsEmpty([Time].CurrentMember)), 1)
Note: If you want to set the time dimension's "default member" to use the "current month" you will need to include .item(0) at the end of the statement to convert the set to an individual member.
The tips provided here have no implied warranty. They are provided as a free service and are informational in nature.
ASP .NET: User Authentication not going through to reports
authentication mode="Windows"/
identity impersonate="true"/
to web.config in System.Web
ASP .net: reportViewer Operation is not valid due to the current state of the object
ASP .NET: Update framework 2.0 to 3.5
Visual Studio 2008 automatically updates the application's Web.config file based on the version being targeted.
Similarly, when you open an existing ASP.NET 2.0 application in Visual Studio 2008, you are prompted as to whether you want to upgrade the website to ASP.NET version 3.5.
Ref: http://www.4guysfromrolla.com/articles/121207-1.aspx
Friday, 13 February 2009
SSAS: Optimisation tips
2. To speed up initial cube load, set default measure of cube to measure not using (Properties at the Measure Group level): Then for that default cube measure scope it-
SCOPE([MEASURES].[COUNT]); This = Null;END SCOPE;
3. Set defaults on Cube by scoping.
4. MDX PERFORMANCE:
a. Use IS When you need to check the value of a member, use IIF [Customer].[Company] IS [Microsoft] and not IIF [Customer].[Company].Name = “Microsoft”. The reason that IS is faster is because the Query Execution Engine does not need to spend extra time translating members into strings.
b. Use the Format String propertyInstead of applying conditional logic to return customized values if the cell is EMPTY or 0, use the Format String property. The Format String property provides a mechanism to format the value of a cell. You can specify a user-defined formatting expression for positive values, negative values, zeros, and nulls. The Format String display property has considerably less overhead than writing a calculation or assignment that must invoke the Query Execution Engine. Keep in mind that your front-end tool must support this property.
c. Avoid late-binding functionsWhen writing MDX calculations against large data sets involving multiple iterations, avoid referencing late binding functions whose metadata cannot be evaluated until run time. Examples of these functions include: LinkMember, StrToSet, StrToMember, StrToValue, and LookupCube. Because they are evaluated at run time, the Query Execution Engine cannot select the most efficient execution path.
D. Eliminate redundancyWhen you use a function that has default arguments such as Time.CurrentMember, you can experience performance benefits if you do not redundantly specify the default argument. For example, use PeriodsToDate([Date].[Calendar].[Calendar Year]) instead of PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].Calendar.CurrentMember). To take advantage of this benefit, you must ensure that you only have one default Time Hierarchy in your application. Otherwise, you must explicitly specify the member in your calculation.
E. Ordering expression argumentsWhen writing calculation expressions like “expr1 * expr2”, make sure the expression sweeping the largest area/volume in the cube space and having the most Empty (Null) values is on the left side. For instance, write “Sales * ExchangeRate” instead of “ExchangeRate * Sales”, and “Sales * 1.15” instead of “1.15 * Sales”. This is because the Query Execution Engine iterates the first expression over the second expression. The smaller the area in the second expression, the fewer iterations the Query Execution Engine needs to perform, and the faster the performance.
4. Use Scope instead of case where possible.
5. IIF is better for performance then Case
SQL: Connecting to other servers
EXEC sp_addlinkedserver
'Server name', 'SQL Server'GO
Wednesday, 4 February 2009
SSRS: Changing the Stylesheet in Reporting Services
This blog applies for both SQL Server Reporting Services 2005 & 2008.
There are 2 steps to modifying the stylesheet -
Step 1: Create the stylesheet
1-Navigate to
2-Modify the stylesheet as appropriate and then save in the style folder
Step 2: Point to the new StyleSheet
1-Navigate to:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
2-Modify the RSReportServer.config file and add the following text:
3-Now navigate to: http://ServerNameHere/ReportServer and select your reports.
Alternatively you can just add the StyleSheet to the URL:
&rc:Stylesheet=MyStyleSheet