VBA Excel - Stop Macro execution .

Terminating the run in your function can be done by using the keyword - "End"

Sub MySub()

    Dim x
   
    x = MyFunc("Test")
   
    MsgBox x
   
End Sub

Function MyFunc(Arg1) As Long

    If Arg1 = "Test" Then
        End
    Else
        MyFunc = 1
    End If   
End Function

Recommendation :

But using End in this way seems completely wrong. Terminating the run in your function is the wrong way.
You should pass back information via the function(s) and deal with termination in a more appropriate location.

The connection for viewing your linked Microsoft Excel worksheet was lost.Microsoft JET Database Engine

When you use GetObject() function to open an excel file and work on it.

Use  <.Close>  to close the file. Else we generally end up with "The connection for viewing your linked Microsoft Excel worksheet was lost.Microsoft JET Database Engine " error. 

Commenting VBA code using Edit Toolbar

• In VBE Go to View - Toolbars
• Select 'Edit' - this brings up the Edit toolbar
• Highlight the code that you want to comment out
• On the Edit toolbar select "Comment Block"

(There's also an "UnComment Block" to reverse this if needed)

RTRIM does not trim CHAR(160)

Important - LTRIM and RTRIM only remove the space character CHAR(32). They do not remove a CHAR(160) which is a non-breaking space . The strings rtrim('12345' + char(160)) and rtrim('12345' + char(32)) are not the same.


Source - http://technet.microsoft.com

SQL Server - Removing New Line characters

If you suspect that your varchar variable has a new line character , use the following to remove it -

REPLACE(REPLACE( @variable,CHAR(13),''),CHAR(10),'')

Side Effects of VBA IIf

Side Effects of VBA IIf 

Another issue with IIf arises because it is a library function: unlike the C-derived conditional operator, both truepart and the falsepart will be evaluated regardless of which one is actually returned. Consider the following example:

value = 10
result = IIf(value = 10, TrueFunction, FalseFunction)

Although TrueFunction is the function intended to be called, IIf will cause both TrueFunction and FalseFunction to be executed.

Also consider this one:

a = 10
b = 0
result = IIf(b <> 0, a / b, 0)

While the programmer intends to avoid raising an error by performing a division by zero, whenever b is zero the error will actually happen. This is because the code in the snippet is to be read as

a = 10
b = 0
_temp1 = a / b ' Error if b = 0
_temp2 = 0
_temp3 = b <> 0
result = IIf(_temp3, _temp1 , _temp2)

This issue makes the IIf() call less useful than the conditional operator. To solve this issue, Microsoft developers had considered converting IIf to an intrinsic function; had this happened, the compiler would have been able to perform type inference and short-circuiting by replacing the function call with inline code.

References-

support.microsoft.com 

Google DART Generics

Generics

Dart supports reified generics. That is, objects of generic type carry their type arguments with them at run time. Passing type arguments to a constructor of a generic type is a runtime operation. How does this square with the claim that types are optional?

Well, if you don’t want to ever think about types, generics won’t force you to. You can create instances of generic classes without providing type parameters. For example:

new List();

works just fine. Of course, you can write

new List<String>();

if you want.

new List();

is just a shorthand for

new List<Dynamic>();

In constructors, type parameters play a runtime role. They are actually passed at run time, so that you can use them when you do dynamic type tests.

new List<String>() is List<Object>  // true: every string is an object 
new List<Object>() is List<String>  // false: not all objects are strings

Generics in Dart conform to programmer intuition. Here are some more interesting cases:

new List<String>() is List<int>     // false
new List<String>() is List          // true
new List<String>() is List<Dynamic> // same as line above
new List() is List<Dynamic>         // true, these are exactly the same

In contrast, type annotations (for example, types added to the declarations of variables, or as return types of functions and methods) play no runtime role and have no effect on program semantics. One last case worth studying:

new List() is List<String>          // true as well!

You may be writing your program without types, but you will frequently be passing data into typed libraries. To prevent types getting in your way, generic types without type parameters are considered substitutable (subtypes of) for any other version of that generic.

Soure:http://www.dartlang.org

DART-Getting started with DART

Here is a 10 minute screencast showing how to work with Dart Editor,dartc,frog,frog server and Dartium.

Tips to prevent SQL Injection

SQL Injection is the most common threat that a web application usually faces. SQL Injection is a type of attack where in the hacker inserts malicious code into strings, that are passed to an instance of SQL Server for parsing and execution. All procedures that construct SQL statements should be reviewed for injection vulnerabilities, because SQL server executes all scripts that are syntactically accurate.A determined attacker can even manipulate parameterized data.

Developers should follow the below instructions to avoid injection attacks.

1.     Validate all input

2.     Use Type-Safe SQL Parameters

3.     Use parameterized input with stored procedures.

4.     Use the parameters collection with dynamic SQL.

5.     Filtering input may also be helpful in protecting against SQL injection by removing escape characters. 

6.     Note that if you are using a LIKE clause, wildcard characters still must be escaped.

Here is a sample video on Youtube.COM which shows SQL Injection- 

For more information, click here

SQL Server Jobs Monitoring Script

Enterprise databases have many jobs running on them, some of them take care of daily loads like incremental loads, extract generators, database maintenance activities , hourly jobs which are used for delivering reports through e-mail.

These huge databases are maintained by support teams, and they have to keep an eye on the progression or status of a specific jobs , as the loading or extracts generation should complete within a time limit. Below is a useful script to get the list of jobs running on a server at that moment.

BEGIN

DECLARE @jobstatus

TABLE(Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int,Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100),

Running int, Current_Step int, Current_Retry_Attempt int, State int)     

INSERT INTO @jobstatus

EXEC MASTER.dbo.xp_sqlagent_enum_jobs 1,garbage 

                BEGIN

                SELECT DISTINCT CASE         

                   WHEN state=1 THEN 'Job is Executing'

                   WHEN state=2 THEN 'Waiting for thread to complete'

                   WHEN state=3 THEN 'Between retries'

                   WHEN state=4 THEN 'Job is Idle'

                   WHEN state=5 THEN 'Job is suspended'

                   WHEN state=7 THEN 'Performing completion actions'

                END AS State,sj.name,

                CASE WHEN ej.running=1 THEN st.step_id ELSE 0 END AS currentstepid,

                CASE WHEN ej.running=1 THEN st.step_name ELSE 'not executing' END AS currentstepname,

                st.command, ej.request_source_id

               

                FROM @jobstatus ej join msdb..sysjobs sj ON sj.job_id=ej.job_id

                JOIN msdb..sysjobsteps st ON st.job_id=ej.job_id AND (st.step_id=ej.current_step or ej.current_step=0)

                WHERE ej.running+1>1

                END

END

xp_sqlagent_enum_jobs is an undocumented procedure in SQL Server. The documented SP for this is sp_help_job.  It eventually calls the xp_sqlagent_enum_jobs procedure. If you created a table to hold the results from this procedure, you could find plenty of documentation in it.