Tuesday, 24 June 2014
SSIS: Error: A rowset based on the SQL command was not returned by the OLE DB provider.
you must put set nocount on and set nocount off around your stored procedure body. SInce the rowcount returned by the stored procedure is returned before the actual resultset, SSIS attempts to read that in as the resultset, which is why it complains about an invalid resultset. set nocount on will supress the rowcount message and only return the resultset.
Tuesday, 10 June 2014
ASP.NET Web form on iPAD
How to make a website full screen on the iPad?
add this tag to your webpage <Header> section, and make a button on home page<meta name="viewport" content="initial-scale = 1.0, maximum-scale = 1.0, user-scalable = no, width = device-width">
Diable Zoom
<meta name="viewport" content="initial-scale = 1.0, maximum-scale = 1.0, user-scalable = no, width = device-width">
Let Page cannot move, more like native app
<script type="text/javascript">
window.attachEvent('load', document.ontouchmove = function(event) { event.preventDefault(); }, false);
</script>
Thursday, 27 March 2014
Tuesday, 18 March 2014
SQL: MERGE statement with Trigger
Trigger Implementation
For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.
If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.
If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.
If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.
Please refer to:
Monday, 10 March 2014
Wednesday, 5 March 2014
SQL - Delete Duplicated Records
WITH CTE AS(
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY Code ORDER BY CreatedDate Desc)
FROM dbo.dw_emp
)
DELETE FROM CTE WHERE RN > 1
Table dw_emp Structure
------------------------------------------------------------------------------------------------
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY Code ORDER BY CreatedDate Desc)
FROM dbo.dw_emp
)
DELETE FROM CTE WHERE RN > 1
Table dw_emp Structure
------------------------------------------------------------------------------------------------
| Code | Name | Address | CreatedDate |
| 10001 | Mark | Tai Seng Street | 1/12/2012 |
| 10002 | Jack | Jurong East St 11 | 12/12/2011 |
| 10001 | Mark | Tai Seng Street | 12/5/2013 |
If need delete the duplicated one, and remain the one whose created date is biggest, you can use the script above.
Tuesday, 4 March 2014
Excel 2007: Highlight Duplicates
Using conditional formatting, you can easily highlight duplicate values in your spreadsheet. This will make it easier to identify the duplicates so that you can remove them. To do this…
1. Select the column that is to be checked for duplicate values.
2. Go to the Ribbon and select the Home tab.
3. In the Styles section, click Conditional Formatting.
4. Select Highlight Cells Rules and click Duplicate Values.
5. In the Duplicate Values dialog box, choose the formatting you wish to use from the dropdown. The default is a light red fill with dark red text.
6. Click OK.
The background color of all values that appear more than once in the selected column will show the selected formatting.
Subscribe to:
Posts (Atom)