Maintain Scroll Position after Asynch Postback

  
    var goTop = parseInt($.cookie("posName"));
    if (goTop) {
        $(document).scrollTop(goTop);
        $.cookie("posName", "");
    }
 
    var prm = Sys.WebForms.PageRequestManager.getInstance();
    try {
        prm.add_initializeRequest(InitializeRequest);
        prm.add_endRequest(EndRequest);
    }
    catch (err) { }
    
 
    
    function InitializeRequest(sender, args) {
        try {
            //debugger;
            var elem = document.getElementById('Container');
            var scrollTop = $(document).scrollTop();
            debugger;
            $.cookie("posName", scrollTop);
            
            showHideLoading(true, true);
 
        } catch (err) { }
 
    }

JQueryUI DatePicker doesnt work in UpdatePanel after partial post back?

Heres the solution:
Im showing an alert if no data available, after alert the datepicker stopped working due to partial post, adding EndRequestHandlerfixed the issue.

 <script type="text/javascript">
     $(document).ready(function () {
         Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
 
         function EndRequestHandler(sender, args) {
             
             $('.datepicker').datepicker({ dateFormat: 'dd-mm-yy' });
         }
 
     });
    </script>  

SQL Server Variable Scope inside loops, Not what U think if u come from C#/Java...

Guess what prints in this loop? 

Will @i b 1 every time ? Will @outTbl  only have one row?
Actually no, scope of loop variables is to the script, a Gotcha 4 ya!
If u use table variables in loop make sure u delete on each iteration to clear.!


Related:

SQL Server variable scope is per batch or the entire function/procedure/trigger, not per black/nested construct

http://msdn.microsoft.com/en-us/library/ms187953.aspx:

Results here...

set nocount on

declare @tt int
set @tt =10
while @tt>0
begin
-- scope to loop or scope to sript?
declare @i int
Declare @outTbl TABLE(Release_App_schedule_ID int)

insert into @outTbl
select  @tt
set @i = isnull(@i,0) + 1

select '@outTbl', * from @outTbl
select '@i', @i
   
    set @tt=@tt-1

end


IS SAME AS


set nocount on

declare @tt int
-- scope to loop or scope to sript?
declare @i int
Declare @outTbl TABLE(Release_App_schedule_ID int)


set @tt =10
while @tt>0
begin


insert into @outTbl
select  @tt
set @i = isnull(@i,0) + 1

select '@outTbl', * from @outTbl
select '@i', @i
   
    set @tt=@tt-1

end

Search and Print sprocs in SQL Server SUPER fast

Tired of using the SSMS Object Explorer to find sprocs/functions and the delay? 

Tired of the delay to view a sproc when clicking on the treeview in SSMS? Well here is the super duper fast way to view sprocs, If u know the name or part of the name of the sproc U want U can use the below script to BOOM....Get ur sproc definition printed in about...Oh....3 ms. Boom

1. Create the sp_LongPrint sproc (below), this is needed to PRINT large strings, because the default PRINT() function only allows like 8000, so create this first! credit from these guys : SP_LongPrint author

2. Run the below script GetSprocs.sql, enter the full or part name of your sproc u want, or a date it was altered, or search by a key word, Just plain bad ass. When U run the script, the sproc will show up in the Messages panel.

This is the difference between 1second and 1minute to find a sproc, Add that up over a day/week/month and U just saved a shitload of time, Time = $$$...now back to work...

--GetSprocs.sql

use master
go
set nocount on
DECLARE MY_CURSOR Cursor
FOR
SELECT r.Routine_Definition, routine_name
FROM INFORMATION_SCHEMA.Routines r
where
--last_altered > '2013-11-16 20:20:26.193' -- search by date?
routine_name like '%print%' -- search by name?
--ROUTINE_DEFINITION LIKE '%Metal_UID%' -- search by contents in sproc?
order by last_altered desc
DECLARE @sprocR VARCHAR(MAX)
set @sprocR = ''
OPEN MY_CURSOR
    DECLARE @sproc VARCHAR(MAX) , @name varchar(max), @txt varchar(max)
    FETCH NEXT FROM MY_CURSOR INTO @sproc, @name
    WHILE (@@FETCH_STATUS <> -1)
   BEGIN
        IF (@@FETCH_STATUS <> -2)
    select @name = 'dbo.' + @name
    select @txt = '-- *************************START procedure ' + @name
    exec [sp_LongPrint]  @txt
-- get sproc definition
    SELECT @txt= OBJECT_DEFINITION(OBJECT_ID(@name))
        exec [sp_LongPrint] @txt
    select @txt = 'go '
    exec [sp_LongPrint] @txt
    select @txt = '-- *************************END procedure ' + @name
    exec [sp_LongPrint] @txt
           FETCH NEXT FROM MY_CURSOR INTO @sproc, @name
    END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

--[sp_LongPrint]

USE master
GO
/****** Object:  StoredProcedure [dbo].[sp_LongPrint]    Script Date: 12/8/2013 8:53:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec [sp_LongPrint] @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */


set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

           
    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END
 

    PRINT SUBSTRING(@String, 1, @CurrentEnd)
                                               
    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)
           
END /*End While loop*/

Symantec VIP Service crashes , and no one on Internet has a solution

If u ever get this error, if u use VIP service for your VPN security token, take solace I have encountered the below error twice, and NOTHING resolves it, UnInstall - ReInstall doesnt work...I found a work-around.....You gotta clear out the local temp files that this pain in the butt app uses....
I 4 got how I fixed it like 2 weeks ago, Im digging thru old notes now and Ill post again shortly with the exact solution,

Ok heres what I had to do:
1. Check ur log file: to see errors:
C:\Users\[user]\AppData\LocalLow\Symantec\VIPAccessClient

2.  VIP Access -> Araow -> Reset Credential -> Close/Open VIP -> Login again, works

http://[YourWebSecuritySite]VPNSoftToken.aspx

then
http://[YourWebSecuritySite]/vipssp

then
https://ssp.vip.symantec.com/vipssp/home.v?successUrl=http://...
then
                enter sec code
        then

      reboot and it worked

Tools I can't live without, Save time !

These simple tools are a daily life-saver for, They are the difference between 5 min and 2 hours, and that means $$$, The best developers is sometimes not the smartest, he or she might just be the best because they are the most productive and adhere to KISS and SOLID as much as they can.

Utility Apps

TextPad : Fastest File System searching, e.g: Find ALL sql and c# code that has the word Event_ID
KillProcess.exe : Multi Select Processes and Kill em!! Task Manager on steroids
DebugView: See all the output from System.Diagnostics.Debug.WriteLine() and default output
ProcessExplorer (kill those handles that lock files!)
XPathTester : when Im in XML hell....This saves the day!
WinDiff : Old school from VS 6.0 and still awesome!
Winrar : boom extract!

Zoho: Cloud based notebook and remote assistance is what I use!
notepad.cc : awesome, simple notepad in cloud
GoogleDrive: Duh!
JSFiddle
SQLFiddle/
SQLTidy All 3 are super utilities

In SSMS:

SqlTreeo: Create Folders in SSMS Object Explorer, The way SSMS SHOULD work but doesn't!
sp_who3 : Sproc that shows who, what, when and where for SPIDS! Who is killing my DB? This answers
vw_DBAObjects : a view to search ALL SQL Objects that contain a keyword, Views, udfs, Tables and Sproc are union-ed, This view saves MASSIVE TIME! Stop using SSMS to find dependencies.
SplitEx : udf to split a string!

VS.Net

JS Parser AddIn : Oh man! Best ever most awesome shows JS functios in Outline, handles JQuery to! If ur doing web dev and are NOT a JS "expert" U gotta try this!!!!
Code Snippets: Custom stuff, e.g: try catch in one click! ect...These are my settings for vs.net, macros, and snippets
Power Command: Adds all the menus that MS forgot to add...Like Collapse Solution, Copy Reference

If U like to do more in less time to, let me know what Ur fav tools are to!

VS Themes and Settings and a wierd name 'son of obsidian

Workin' a hardcore 12 hours stint of ssms and vs.net got me wanting a diff color to focus my eyes on, the default settings create a strain afta' 9 hours....Found these gems and played with a few but ended up going back to default color scheme, When u got a hard deadline changing VS settings is a risk...Slowing down by a mere minute can cost 1000's ...Anyway check the color...And I even found a few SSMS setting files to use here
http://www.jimmcleod.net/blog/index.php/2012/06/26/beautify-your-management-studio-2012/
and vs.net here
http://studiostyl.es/schemes/son-of-obsidian

C# code28

#region Studio Style
class Program : IThemeable
{
    static int _I = 1;
    delegate void DoSomething();

    /// <summary>
    /// The quick brown fox jumps over the lazy dog
    /// THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG
    /// </summary>
    static void Main(string[] args)
    {
        string normalStr = "The time now is approximately " + DateTime.Now;
        Uri Illegal1Uri = new Uri("http://packmyboxwith/jugs.html?q=five-dozen&t=liquor");
        Regex OperatorRegex = new Regex(@"\S#$", RegexOptions.IgnorePatternWhitespace);

        for (int O = 0; O < 123456789; O++)
        {
            _I += (O % 3) * ((O / 1) ^ 2) - 5;
            if (!OperatorRegex.IsMatch(Illegal1Uri.ToString()))
            {
                // no idea what this does!?
                Console.WriteLine(Illegal1Uri + normalStr);
            }
        }
    }
}
#endregion

Advanced highlighting

JS Date, Add Month and Format with DatePicker

$('[id*=fvReleaseGroup_release_id] option:selected').text()

"March-2010"

$.datepicker.formatDate("mm/dd/yy", new Date(this))

"NaN/NaN/NaN"

$.datepicker.formatDate("mm/dd/yy", new Date("10/11/2009"))

"10/11/2009"

$.datepicker.formatDate("mm/dd/yy", new Date("March 1, 2009"))

"03/01/2009"

$.datepicker.formatDate("mm/dd/yy", new Date("March 1 2009"))

"03/01/2009"

$('[id*=fvReleaseGroup_release_id] option:selected').text().split("-").length

2

$('[id*=fvReleaseGroup_release_id] option:selected').text().split("-")[0]

"March"

$('[id*=fvReleaseGroup_release_id] option:selected').text().split("-")[1]

"2010"

$.datepicker.formatDate("mm/dd/yy", new Date("March-2010"))

"03/01/2010"

$.datepicker.formatDate("mm/dd/yy", new Date("March 2010"))

"03/01/2010"

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text())

undefined

var newdt = dt.setMonth(d.getMonth() + 2);

ReferenceError: d is not defined

var newdt = dt.setMonth(dt.getMonth() + 2);

undefined

$.datepicker.formatDate("mm/dd/yy", newdt)

TypeError: undefined is not a function

$.datepicker.formatDate("mm/dd/yy", new Date("March 2010"))

"03/01/2010"

$.datepicker.formatDate("mm/dd/yy", new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()))

"03/01/2010"

$.datepicker.formatDate("mm/dd/yy", new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()).addMonths(2))

TypeError: undefined is not a function

$.datepicker.formatDate("mm/dd/yy", new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()))

"03/01/2010"

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()); dt.toString();

"Mon Mar 01 2010 00:00:00 GMT-0500 (Eastern Standard Time)"

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()); dt.setMonth(dt.getMonth() + 2).toString();

"1272686400000"

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()); var newdt = dt.setMonth(dt.getMonth() + 2).toString();

undefined

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()); var newdt = dt.setMonth(dt.getMonth() + 2); newdt.toString();

"1272686400000"

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()); var newdt = dt.setMonth(dt.getMonth() + 2); newdt = new Date(newdt); $.datepicker.formatDate("mm/dd/yy",newdt);

"05/01/2010"

var dt = new Date($('[id*=fvReleaseGroup_release_id] option:selected').text()); var newdt = dt.setMonth(dt.getMonth() + 2); newdt = new Date(newdt); $.datepicker.formatDate("mm/dd/yy",newdt);

Custom SSIS Logging Example

 

SSIS Logging Example : Logging Detailed Information and Errors

20140610

Created By : JRD

References

Example based on:

http://www.sqlsafety.com/?p=272

Misc References:

Multiple Errors (advanced)

http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/

SSIS Utilities on CodePlex for ssis lots of cool tools:

https://ssisctc.codeplex.com/

SSIS Logging Example Project

Pre-Requisites: Step 1-3

AdventureWorks2012, and Adventureworks2012 DB
Create the table and sproc in the zip qry_LogEvent and ServiceEventLog, word doc in zip has screen shots

See [local], has these db’s

Login as : Sql Authentication

logginguser

pass@word1

            package password: Jas0n

1. The Logging Stored Procedure: (DONE) adventureworks2012.dbo.qry_LogEvent

-- log an INFO message

execute adventureworks2012.dbo.qry_LogEvent @ServiceID=10, @Message='Start from GetSAPMasterData()', @EventID=100

-- log an ERROR message

execute adventureworks2012.dbo.qry_LogEvent @ServiceID=10, @Message='Start from GetSAPMasterData()', @EventID=100

execute adventureworks2012.dbo.qry_LogEvent 'error happened!', 9, 103, 'ERROR', 'SqlException'

1.  
1. The logging Table (DONE) adventureworks2012..[ServiceEventLog]

This is where messages are logged:

a. select * from adventureworks2012..[ServiceEventLog] where Serviceid = 10

2. Your SSIS pck’s uses Service ID = 10 this is the ID used in Logging below:

SSIS: Step 3-6

Open the project , test the connection, all the below steps are already done in the example project but are documented for assistance.

3. *** Configure SSIS Package for Logging! Lets do the first task
1. Add Pre/Post Event Handler if u so desire

2. Add OnError Event Handler for the Task:

                                          i.    Pass the ErrorDescription into the SQL task as follows,

1.    Open the “Error Custom Logging” SQL task here to see:

                                        ii.     

4. Execute
6. Check Results, see how your Machine Name is listed, schema of table is modeled after the “Windows Event Log” schema, If your machine name is not listed then an error occurred logging to table.

select * from adventureworks2012..[ServiceEventLog] where Serviceid = 10

8. Test error Logging!
1. Create an ERROR to test:

SELECT FirstName,

             MiddleName,

cast(1/0 as nvarchar)             LastName

FROM Person.Person

2. Execute package
3. Check errors, take note we got the Full Error message!!!

4. Set sql back to way it was

SELECT FirstName,

             MiddleName,

LastName

FROM Person.Person

5.  

9. If you don’t want error to interrupt flow, : (same as try catch{} with no throw), this would allow ur package to continue execution.
1.  

Source Project https://drive.google.com/file/d/0B_g_1Tnrt6oFc2JaaTBUeFVHSzA/edit?usp=sharing

Delete Audit Trigger in SQL Server

Had a need to find out who/what is deleting rows from a table, this trigger will fire on any delete and save the info to a custom table that is based on the good old sp_who3 results,
Here we just want to know about the SPID of the trigger connection, we then get the current sql via DBCC INPUTBUFFER (if any) and then get connection info and save it to a table!

Credit goes to Shailendra Chauhan
End result is something like this:

create TRIGGER trgAuditDelete ON dbo.Employee_Demo
FOR DELETE
AS
/*

drop trigger trgAuditDelete
disable trigger trgAuditDelete ON dbo.Employee_Demo
enable trigger trgAuditDelete ON dbo.Employee_Demo

use master
go

CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)

-- Now Insert records
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
select * from Employee_Demo

Unit Test
begin tran
--Output will be
 --Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where emp_id = 5
--Output will be
select * from sp_Who3Results
rollback tran

begin tran
--Output will be
 --Now try to delete data in Employee_Demo table
DELETE Employee_Demo
--Output will be
select * from sp_Who3Results
rollback tran

begin tran
--Output will be
 --Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where 5= 6
--Output will be
select * from sp_Who3Results
rollback tran


 --Now try to delete data in Employee_Demo table
truncate table Employee_Demo
--Output will be
select * from sp_Who3Results

*/
begin

DECLARE @tBuff nvarchar(2000), @sql nvarchar(100)
SET @tBuff = ''

PRINT 'trgAfterDelete() INPUTBUFFER, spid ->' + cast(@@Spid as varchar(50))
begin try
Set @sql = 'DBCC INPUTBUFFER (' + cast(@@Spid as varchar(50)) + ')'
CREATE TABLE #tempBuf (   EventType nvarchar(2000), Parameters nvarchar(2000), EventInfo nvarchar(2000))
INSERT INTO #tempBuf EXECUTE (@sql)

SELECT TOP 1 @tBuff = left(EventInfo, 2000) FROM #tempBuf
PRINT  'trgAfterDelete() @tBuff -> ' + @tBuff
DROP TABLE #tempBuf
end try begin catch end catch
PRINT 'trgAfterDelete() INPUTBUFFER DONE, spid ->' + cast(@@Spid as varchar(50))


PRINT 'trgAfterDelete() insert into sp_Who3Results, spid ->' + cast(@@Spid as varchar(50))
begin try
insert into sp_Who3Results
SELECT sp.spid,
    'TRIGGER' status,
    sp.loginame,
    nullif(rtrim(ltrim(sp.hostname)), '') hostname,
    sp.blocked,
    sd.name,
    sp.cmd,
    sp.cpu,
    sp.physical_io,
    sp.memusage,
    sp.open_tran,
    sp.last_batch,
    sp.login_time,
    nullif(rtrim(ltrim(sp.program_name)), '') Programname,
@tBuff eventInfo,
getdate() DateTimeCreated
--into sp_Who3ResultsTrigger
    FROM master..sysprocesses sp
    LEFT OUTER JOIN master..sysdatabases sd ON sd.dbid = sp.dbid
Where sp.spid = @@SPID
PRINT 'trgAfterDelete() insert into sp_Who3Results DONE, spid ->' + cast(@@Spid as varchar(50))

end try begin catch end catch


PRINT 'AFTER DELETE TRIGGER fired.'
end

SQL Server Using WAITFOR with a loop to run batch at a certain time with loop every (n) seconds

BEGIN
declare @sd datetime,@ed datetime

    WAITFOR TIME '04:28';
Set @sd = getdate()
select getdate()
Print 'Startdate ' + convert(varchar(20), getdate(), 120)
WHILE 1 = 1
BEGIN
Declare @msg varchar(500), @cnt int
select @cnt = count(*) from MyTable (nolock)
Set @msg = 'INFO: MyTable Count = ' + cast(@cnt as varchar(50)) + ', at ' + convert(varchar(120), getdate(), 120)
if (@cnt > 0)
execute dbo.qry_LogEvent @msg, 7, 750
else
execute dbo.qry_LogEvent @msg, 7, 751
delete serviceeventlog where eventid in (750, 751) and createddatetime < Dateadd(hour, -12, getdate())
WAITFOR DELAY '00:00:01'


if (datediff(second, @sd, getdate()) > 12)
begin
print 'break'
break
end
else
continue
End
Print 'Done ' + convert(varchar(20), getdate(), 120)
select getdate()
END;

Chrome Multiple Processes is killing my machine!

Why is there multiple Chrome.exe's in the Task Manager? Why is Chrome consuming massive amounts of RAM?
Well Google in all its wisdom decided to make Chrome like IE and start running "background services" even when the browser isnt running!!! By default it is on, this is how to turn it off:
1. Go to settings
2. Show advanced
3. Uncheck this

Surface Pro 2 + Visual Studio + SQL Server, Here we go!

1. Received my Surface Pro 2 via ebay, sweetness! It feels like my Bday!

1. 
   
2. Has base Windows 8.1 (82 GB Free), walked thru the pesky setup, next, next, 
1. I created a Local Account instead of using the "MSN/LiveID" thing
3. Firmware Update (Disable Auto Updates, there are known issues with updates bricking a surface or 2!)
1. Firmware Update
2. Had a little scare, after reboot, it showed a dos black screen with "Certmgr succeeded" and was hung there, I unplugged and rebooted and it was ok
3. Apparently some folks have had there Surface pro brick from stupid Windows Updates!
4. Configure Windows Update to "Not install updated automatically" or just Disable the Windows Update service,
4. Then I Instaled StartMenu, TextPad (I know most of ya'all use Notepad++), Virtual Clone, Chrome,  
5. A word for Chrome users, Chrome SUX in Surface, so I downloaded an extension chromeTouch that is supposed to help with the Surface Pro / Chome interop, The biggest pain I see is that I can not easily select text on a web page (WTF) when Im in chrome, thus its a pain, but everything else is cool. IE in surface is nice! Actually nicer than a normal laptop.....
6. Tuned the OS a bit by disabling windows services not needed per: 
1. Disable pesky windows Services!
   Stop UAC!
7. Move Recovery Partition to USB, Delete Recovery Partition on C:\ this saves 5.2 GB
8. Install SQL Server Express Advanced Svcs: 4.2 GB: DB Engine only + SSMS
9.       after install: (72 GB Free)
10. VS 2012 Premium : 5.45 GB (unchecked Blend and Lightswitch,)
11.       after install: (73 GB Free)    
12. Office 2010: Word, PPT, Excel, tools only : 2.12 GB
13.       after install: (73 GB Free)    

So all done! I got a Surface Pro 2, with VS .net, SQL......Ill update on dev experience shortly...Overall im loving the Surface Pro 2.


Some TODO's that I hope to do include
Battery Life Tuning
Disk Space Tuning (We only git 75 G free!)


And to go over the Surface Tips here:
Surface Pro Hints Tweaks