Programs & Examples On #Sql server

Microsoft SQL Server is a relational database management system (RDBMS). Use this tag for all SQL Server editions including Compact, Express, Azure, Fast-track, APS (formerly PDW) and Azure SQL DW. Do not use this tag for other types of DBMS (MySQL, PostgreSQL, Oracle etc.). Do not use this tag for issues on software and mobile development, unless it is directly related to the database.

login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

  1. Go to services.msc from run prompt.
  2. Restart the services of SQL server(MSSQLSERVER)
  3. Restart the services of SQL server(SQLEXPRESS)

How to fix a collation conflict in a SQL Server query?

Adding to the accepted answer, you can used DATABASE_DEFAULT as encoding.

This allows database to make choice for you and your code becomes more portable.

    FirstTable a
        INNER JOIN SecondTable b

Check if a temporary table exists and delete if it exists before creating a temporary table

The statement should be of the order

  1. Alter statement for the table
  2. GO
  3. Select statement.

Without 'GO' in between, the whole thing will be considered as one single script and when the select statement looks for the column,it won't be found.

With 'GO' , it will consider the part of the script up to 'GO' as one single batch and will execute before getting into the query after 'GO'.

Execute a large SQL script (with GO commands)

I accomplished this today by loading my SQL from a text file into one string. I then used the string Split function to separate the string into individual commands which were then sent to the server individually. Simples :)

Just realised that you need to split on \nGO just in case the letters GO appear in any of your table names etc. Guess I was lucky there!

SQL Server Script to create a new user

You can use:

CREATE LOGIN <login name> WITH PASSWORD = '<password>' ; GO 

To create the login (See here for more details).

Then you may need to use:

CREATE USER user_name 

To create the user associated with the login for the specific database you want to grant them access too.

(See here for details)

You can also use:

GRANT permission  [ ,...n ] ON SCHEMA :: schema_name

To set up the permissions for the schema's that you assigned the users to.

(See here for details)

Two other commands you might find useful are ALTER USER and ALTER LOGIN.

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

The only way is to drop foreign keys before doing the truncate. And after truncating the data, you must re-create the indexes.

The following script generates the required SQL for dropping all foreign key constraints.


SELECT @drop += N'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

SELECT @drop

Next, the following script generates the required SQL for re-creating foreign keys.


SELECT @create += N'
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + '(' + STUFF((SELECT ',' + QUOTENAME(
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

SELECT @create

Run the generated script to drop all foreign keys, truncate tables, and then run the generated script to re-create all foreign keys.

The queries are taken from here.

SQL Server - Case Statement

I am looking for a way to create a select without repeating the conditional query.

I'm assuming that you don't want to repeat Foo-stuff+bar. You could put your calculation into a derived table:

SELECT CASE WHEN a.TestValue > 2 THEN a.TestValue ELSE 'Fail' END 
FROM (SELECT (Foo-stuff+bar) AS TestValue FROM MyTable) AS a

A common table expression would work just as well:

WITH a AS (SELECT (Foo-stuff+bar) AS TestValue FROM MyTable)
SELECT CASE WHEN a.TestValue > 2 THEN a.TestValue ELSE 'Fail' END    

Also, each part of your switch should return the same datatype, so you may have to cast one or more cases.

Equivalent of LIMIT and OFFSET for SQL Server?

This feature is now made easy in SQL Server 2012. This is working from SQL Server 2012 onwards.

Limit with offset to select 11 to 20 rows in SQL Server:

SELECT email FROM emailTable 
WHERE user_id=3
  • ORDER BY: required
  • OFFSET: optional number of skipped rows
  • NEXT: required number of next rows


Querying a linked sql server

I use open query to perform this task like so:

select top 1 *
from openquery(

The example above uses open query to select data from a database on a linked server into a database of your choosing.

Note: For completeness of reference, you may perform a simple select like so:

select top 1 * from openquery(


There's no need for "else" in this case:

IF EXISTS(SELECT *  FROM  table1  WHERE Name='John' ) return 1
return 0

SQL Server 2008 R2 can't connect to local database in Management Studio

I know, this problem can be faced by so many people and many of them have uninstalled and re-installed the sql server for resolving this issue. In my observation the problem of not connecting the database service locally is just because of your network connection you are using, in most of the cases these problems will come when you are using wi-fi network.

Solution is, if you are using wi-fi then just right click on status of the network and get the ip details and enter the same ip in sql server name, it will work. Regards Vishwajeet

How can foreign key constraints be temporarily disabled using T-SQL?

To disable the constraint you have ALTER the table using NOCHECK


To enable you to have to use double CHECK:

  • Pay attention to the double CHECK CHECK when enabling.
  • ALL means for all constraints in the table.

Once completed, if you need to check the status, use this script to list the constraint status. Will be very helpfull:

        END) AS STATUS,

Insert all values of a table into another table in SQL

The insert statement actually has a syntax for doing just that. It's a lot easier if you specify the column names rather than selecting "*" though:

INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
-- optionally WHERE ...

I'd better clarify this because for some reason this post is getting a few down-votes.

The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. As others have said, the SELECT ... INTO syntax is for when you want to create the new table as part of the command.

You didn't specify whether the new table needs to be created as part of the command, so INSERT INTO ... SELECT FROM should be fine if your destination table already exists.

How to use table variable in a dynamic sql statement?

I don't think that is possible (though refer to the update below); as far as I know a table variable only exists within the scope that declared it. You can, however, use a temp table (use the create table syntax and prefix your table name with the # symbol), and that will be accessible within both the scope that creates it and the scope of your dynamic statement.

UPDATE: Refer to Martin Smith's answer for how to use a table-valued parameter to pass a table variable in to a dynamic SQL statement. Also note the limitation mentioned: table-valued parameters are read-only.

Temporarily disable all foreign key constraints

Use the built-in sp_msforeachtable stored procedure.

To disable all constraints:


To enable all constraints:


To drop all the tables:

EXEC sp_msforeachtable "DROP TABLE ?";

Adding an identity to an existing column

Right click on table name in Object Explorer. You will get some options. Click on 'Design'. A new tab will be opened for this table. You can add Identity constraint here in 'Column Properties'.

Control flow in T-SQL SP using IF..ELSE IF - are there other ways?

IF...ELSE... is pretty much what we've got in T-SQL. There is nothing like structured programming's CASE statement. If you have an extended set of ...ELSE IF...s to deal with, be sure to include BEGIN...END for each block to keep things clear, and always remember, consistent indentation is your friend!

Drop all tables whose names begin with a certain string

In case of temporary tables, you might want to try

SELECT 'DROP TABLE "' + + '"' 
FROM tempdb.sys.tables t
WHERE LIKE '[prefix]%'

Paste MS Excel data to SQL Server

I have developed an Excel VBA Macro for cutting and pasting any selection from Excel into SQL Server, creating a new table. The macro is great for quick and dirty table creations up to a few thousand rows and multiple columns (It can theoretically manage up to 200 columns). The macro attempts to automatically detect header names and assign the most appropriate datatype to each column (it handles varchar columns upto 1000 chars).

Recommended Setup procedure:

  1. Make sure Excel is enabled to run macros. (File->Options->Trust Center->Trust Center Settings->Macro Settings->Enable all macros..)
  2. Copy the VBA code below to the module associated with your personal workbook (So that the Macro will be available for all worksheets)
  3. Assign an appropriate keystroke to the macro ( I have assigned Ctrl Shift X)
  4. Save your personal workbook

Use of Macro

  1. Select the cells in Excel (including column headers if they exist) to be transferred to SQL
  2. Press the assigned keyword combination that you have assigned to run the macro
  3. Follow the prompts. (Default table name is ##Table)
  4. Paste the clipboard contents into a SSMS window and run the generated SQL code. BriFri 238

VBA Code:

Sub TransferToSQL()
' TransferToSQL Macro
' This macro prepares data for pasting into SQL Server and posts it to the clipboard for inserting into SSMS
' It attempts to automatically detect header rows and does a basic analysis of the first 15 rows to determine the most appropriate datatype to use handling text entries upto 1000 chars.
' Max Number of Columns: 200
' Keyboard Shortcut: Ctrl+Shift+X
' ver   Date    Reason
' ===   ====    ======

' 1.6   06/2012 Fixed bug that prevented auto exit if no selection made / auto exit if blank Tablename entered or 'cancel' button pressed
' 1.5   02/2012 made use of function fn_ColLetter to retrieve the Column Letter for a specified column
' 1.4   02/2012 Replaces any Tabs in text data to spaces to prevent Double quotes being output in final results
' 1.3   02/2012 Place the 'drop table if already exists' code into a separate batch to prevent errors when inserting new table with same name but different shape and > 100 rows
' 1.2   01/2012 If null dates encountered code to cast it as Null rather than '00-Jan-1900'
' 1.1   10/2011 Code to drop the table if already exists
' 1.0   03/2011 Created

Dim intLastRow As Long
Dim intlastColumn As Integer
Dim intRow As Long
Dim intDataStartRow As Long
Dim intColumn As Integer
Dim strKeyWord As String
Dim intPos As Integer
Dim strDataTypeLevel(4) As String
Dim strColumnHeader(200) As String
Dim strDataType(200) As String
Dim intRowCheck As Integer
Dim strFormula(20) As String
Dim intHasHeaderRow As Integer
Dim strCellRef As String
Dim intFormulaCount As Integer
Dim strSQLTableName As String
Dim strSQLTableName_Encap As String
Dim intdataTypelevel As Integer
Const strConstHeaderKeyword As String = "ID,URN,name,Title,Job,Company,Contact,Address,Post,Town,Email,Tele,phone,Area,Region,Business,Total,Month,Week,Year,"
Const intConstMaxBatchSize As Integer = 100
    Const intConstNumberRowsToAnalyse As Integer = 100
intHasHeaderRow = 0

strDataTypeLevel(1) = "VARCHAR(1000)"
strDataTypeLevel(2) = "FLOAT"
strDataTypeLevel(3) = "INTEGER"
strDataTypeLevel(4) = "DATETIME"

' Use current selection and paste to new temp worksheet

    Workbooks.Add       ' add temp 'Working' Workbook
    ' Paste "Values Only" back into new temp workbook
    Range("A3").Select  ' Goto 3rd Row
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Copy Format of Selection
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False  ' Copy Values of Selection
    ActiveCell.SpecialCells(xlLastCell).Select  ' Goto last cell
    intLastRow = ActiveCell.Row
    intlastColumn = ActiveCell.Column

' Check to make sure that there are cells which are selected
If intLastRow = 3 And intlastColumn = 1 Then
    Application.DisplayAlerts = False       ' Temporarily switch off Display Alerts
    ActiveWindow.Close                      ' Delete newly created worksheet
    Application.DisplayAlerts = True        ' Switch display alerts back on
    MsgBox "*** Please Make selection before running macro - Terminating ***", vbOKOnly, "Transfer Data to SQL Server"
    Exit Sub
End If

' Prompt user for Name of SQL Server table
strSQLTableName = InputBox("SQL Server Table Name?", "Transfer Excel Data To SQL", "##Table")

' if blank table name entered or 'Cancel' selected then exit
If strSQLTableName = "" Then
    Application.DisplayAlerts = False       ' Temporarily switch off Display Alerts
    ActiveWindow.Close                      ' Delete newly created worksheet
    Application.DisplayAlerts = True        ' Switch display alerts back on
    Exit Sub
End If

' encapsulate tablename with square brackets if user has not already done so
strSQLTableName_Encap = Replace(Replace(Replace("[" & Replace(strSQLTableName, ".", "].[") & "]", "[]", ""), "[[", "["), "]]", "]")

' Try to determine if the First Row is a header row or contains data and if a header load names of Columns
For intColumn = 1 To intlastColumn
    ' first check to see if the first row contains any pure numbers or pure dates
    If IsNumeric(ActiveCell.Value) Or IsDate(ActiveCell.Value) Then
    intHasHeaderRow = vbNo
    intDataStartRow = 3
    Exit For
    strColumnHeader(intColumn) = ActiveCell.Value
    ActiveCell.Offset(1, 0).Range("A1").Select  ' go to the row below
    If IsNumeric(ActiveCell.Value) Or IsDate(ActiveCell.Value) Then
        intHasHeaderRow = vbYes
        intDataStartRow = 4
    End If
    ActiveCell.Offset(-1, 0).Range("A1").Select  ' go back up to the first row
    If intHasHeaderRow = 0 Then     ' if still not determined if header exists: Look for header using keywords
        intPos = 1
        While intPos < Len(strConstHeaderKeyword) And intHasHeaderRow = 0
        strKeyWord = Mid$(strConstHeaderKeyword, intPos, InStr(intPos, strConstHeaderKeyword, ",") - intPos)
        If InStr(1, ActiveCell.Value, strKeyWord) > 0 Then
            intHasHeaderRow = vbYes
            intDataStartRow = 4
        End If
        intPos = InStr(intPos, strConstHeaderKeyword, ",") + 1
    End If
    End If
    ActiveCell.Offset(0, 1).Range("A1").Select  ' Goto next column
Next intColumn

' If auto header row detection has failed ask the user to manually select
If intHasHeaderRow = 0 Then
    intHasHeaderRow = MsgBox("Does current selection have a header row?", vbYesNo, "Auto header row detection failure")
    If intHasHeaderRow = vbYes Then
    intDataStartRow = 4
    intDataStartRow = 3
    End If

End If

' *** Determine the Data Type of each Column ***

' Go thru each Column to find Data types
If intLastRow < intConstNumberRowsToAnalyse  Then             ' Check the first intConstNumberRowsToAnalyse  rows or to end of selection whichever is less
    intRowCheck = intLastRow
    intRowCheck = intConstNumberRowsToAnalyse 
End If

For intColumn = 1 To intlastColumn
    intdataTypelevel = 5

    For intRow = intDataStartRow To intRowCheck
    Application.Goto Reference:="R" & CStr(intRow) & "C" & CStr(intColumn)
    If ActiveCell.Value = "" Then   ' ignore blank (null) values
    ElseIf IsDate(ActiveCell.Value) = True And Len(ActiveCell.Value) >= 8 Then
        If intdataTypelevel > 4 Then intdataTypelevel = 4
    ElseIf IsNumeric(ActiveCell.Value) = True And InStr(1, CStr(ActiveCell.Value), ".") = 0 And (Left(CStr(ActiveCell.Value), 1) <> "0" Or ActiveCell.Value = "0") And Len(ActiveCell.Value) < 10 Then
        If intdataTypelevel > 3 Then intdataTypelevel = 3
    ElseIf IsNumeric(ActiveCell.Value) = True And InStr(1, CStr(ActiveCell.Value), ".") >= 1 Then
        If intdataTypelevel > 2 Then intdataTypelevel = 2
        intdataTypelevel = 1
        Exit For
    End If
    Next intRow
    If intdataTypelevel = 5 Then intdataTypelevel = 1
    strDataType(intColumn) = strDataTypeLevel(intdataTypelevel)
Next intColumn

' *** Build up the SQL
intFormulaCount = 1
If intHasHeaderRow = vbYes Then     ' *** Header Row ***
    Application.Goto Reference:="R4" & "C" & CStr(intlastColumn + 1)    ' Goto next column in first data row of selection
    strFormula(intFormulaCount) = "= ""SELECT "
    For intColumn = 1 To intlastColumn
    If strDataType(intColumn) = "DATETIME" Then         ' Code to take Excel Dates back to text
        strCellRef = "Text(" & fn_ColLetter(intColumn) & "4,""dd-mmm-yyyy hh:mm:ss"")"
    ElseIf strDataType(intColumn) = "VARCHAR(1000)" Then
        strCellRef = "SUBSTITUTE(" & fn_ColLetter(intColumn) & "4,""'"",""''"")" ' Convert any single ' to double ''
        strCellRef = fn_ColLetter(intColumn) & "4"
    End If

    strFormula(intFormulaCount) = strFormula(intFormulaCount) & "CAST('""& " & strCellRef & " & ""' AS " & strDataType(intColumn) & ") AS [" & strColumnHeader(intColumn) & "]"
    If intColumn < intlastColumn Then
        strFormula(intFormulaCount) = strFormula(intFormulaCount) + ", "
        strFormula(intFormulaCount) = strFormula(intFormulaCount) + " UNION ALL """
    End If
    ' since each cell can only hold a maximum no. of chars if Formula string gets too big continue formula in adjacent cell
    If Len(strFormula(intFormulaCount)) > 700 And intColumn < intlastColumn Then
        strFormula(intFormulaCount) = strFormula(intFormulaCount) + """"
        intFormulaCount = intFormulaCount + 1
        strFormula(intFormulaCount) = "= """
    End If
    Next intColumn

 ' Assign the formula to the cell(s) just right of the selection
    For intColumn = 1 To intFormulaCount
    ActiveCell.Value = strFormula(intColumn)
    If intColumn < intFormulaCount Then ActiveCell.Offset(0, 1).Range("A1").Select  ' Goto next column
    Next intColumn

 ' Auto Fill the formula for the full length of the selection
    ActiveCell.Offset(0, -intFormulaCount + 1).Range("A1:" & fn_ColLetter(intFormulaCount) & "1").Select
    If intLastRow > 4 Then Selection.AutoFill Destination:=Range(fn_ColLetter(intlastColumn + 1) & "4:" & fn_ColLetter(intlastColumn + intFormulaCount) & CStr(intLastRow)), Type:=xlFillDefault

 ' Go to start row of data selection to add 'Select into' code
   ActiveCell.Value = "SELECT * INTO " & strSQLTableName_Encap & " FROM (" & ActiveCell.Value

 ' Go to cells above data to insert code for deleting old table with the same name in separate SQL batch
   ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
   ActiveCell.Value = "GO"
   ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
   If Left(strSQLTableName, 1) = "#" Then      ' temp table
       ActiveCell.Value = "IF OBJECT_ID('tempdb.." & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
       ActiveCell.Value = "IF OBJECT_ID('" & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
   End If

' For Big selections (i.e. several 100 or 1000 rows) SQL Server takes a very long time to do a multiple union - Split up the table creation into many inserts
    intRow = intConstMaxBatchSize + 4   ' add 4 to make sure 1st batch = Max Batch Size
    While intRow < intLastRow
    Application.Goto Reference:="R" & CStr(intRow - 1) & "C" & CStr(intlastColumn + intFormulaCount)  ' Goto Row before intRow and the last column in formula selection
    ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a") ' Remove last 'UNION ALL'

    Application.Goto Reference:="R" & CStr(intRow) & "C" & CStr(intlastColumn + 1)    ' Goto intRow and the first column in formula selection
    ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " SELECT * FROM (" & ActiveCell.Value
    intRow = intRow + intConstMaxBatchSize   ' increment intRow by intConstMaxBatchSize

    ' Delete the last 'UNION AlL' replacing it with brackets to mark the end of the last insert
    Application.Goto Reference:="R" & CStr(intLastRow) & "C" & CStr(intlastColumn + intFormulaCount)
    ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")

    ' Select all the formula cells
    ActiveCell.Offset(-intLastRow + 2, 1 - intFormulaCount).Range("A1:" & fn_ColLetter(intFormulaCount + 1) & CStr(intLastRow - 1)).Select
Else    ' *** No Header Row ***
    Application.Goto Reference:="R3" & "C" & CStr(intlastColumn + 1)    ' Goto next column in first data row of selection
    strFormula(intFormulaCount) = "= ""SELECT "

    For intColumn = 1 To intlastColumn
    If strDataType(intColumn) = "DATETIME" Then
        strCellRef = "Text(" & fn_ColLetter(intColumn) & "3,""dd-mmm-yyyy hh:mm:ss"")"   ' Format Excel dates into a text Date format that SQL will pick up
    ElseIf strDataType(intColumn) = "VARCHAR(1000)" Then
        strCellRef = "SUBSTITUTE(" & fn_ColLetter(intColumn) & "3,""'"",""''"")"         ' Change all single ' to double ''
        strCellRef = fn_ColLetter(intColumn) & "3"
    End If

    ' Since no column headers: Name each column "Column001",Column002"..
    strFormula(intFormulaCount) = strFormula(intFormulaCount) & "CAST('""& " & strCellRef & " & ""' AS " & strDataType(intColumn) & ") AS [Column" & CStr(intColumn) & "]"
    If intColumn < intlastColumn Then
        strFormula(intFormulaCount) = strFormula(intFormulaCount) + ", "
        strFormula(intFormulaCount) = strFormula(intFormulaCount) + " UNION ALL """
    End If

    ' since each cell can only hold a maximum no. of chars if Formula string gets too big continue formula in adjacent cell
    If Len(strFormula(intFormulaCount)) > 700 And intColumn < intlastColumn Then
        strFormula(intFormulaCount) = strFormula(intFormulaCount) + """"
        intFormulaCount = intFormulaCount + 1
        strFormula(intFormulaCount) = "= """
    End If
    Next intColumn

    ' Assign the formula to the cell(s) just right of the selection
    For intColumn = 1 To intFormulaCount
    ActiveCell.Value = strFormula(intColumn)
    If intColumn < intFormulaCount Then ActiveCell.Offset(0, 1).Range("A1").Select  ' Goto next column
    Next intColumn

 ' Auto Fill the formula for the full length of the selection
    ActiveCell.Offset(0, -intFormulaCount + 1).Range("A1:" & fn_ColLetter(intFormulaCount) & "1").Select
    If intLastRow > 4 Then Selection.AutoFill Destination:=Range(fn_ColLetter(intlastColumn + 1) & "3:" & fn_ColLetter(intlastColumn + intFormulaCount) & CStr(intLastRow)), Type:=xlFillDefault

 ' Go to start row of data selection to add 'Select into' code
   ActiveCell.Value = "SELECT * INTO " & strSQLTableName_Encap & " FROM (" & ActiveCell.Value

 ' Go to cells above data to insert code for deleting old table with the same name in separate SQL batch
   ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
   ActiveCell.Value = "GO"
   ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
   If Left(strSQLTableName, 1) = "#" Then      ' temp table
       ActiveCell.Value = "IF OBJECT_ID('tempdb.." & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
       ActiveCell.Value = "IF OBJECT_ID('" & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
   End If

  ' For Big selections (i.e. serveral 100 or 1000 rows) SQL Server takes a very long time to do a multiple union - Split up the table creation into many inserts
   intRow = intConstMaxBatchSize + 3        ' add 3 to make sure 1st batch = Max Batch Size
    While intRow < intLastRow
    Application.Goto Reference:="R" & CStr(intRow - 1) & "C" & CStr(intlastColumn + intFormulaCount)  ' Goto Row before intRow and the last column in formula selection
    ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a") ' Remove last 'UNION ALL'

    Application.Goto Reference:="R" & CStr(intRow) & "C" & CStr(intlastColumn + 1)    ' Goto intRow and the first column in formula selection
    ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " SELECT * FROM (" & ActiveCell.Value
    intRow = intRow + intConstMaxBatchSize   ' increment intRow by intConstMaxBatchSize

    ' Delete the last 'UNION AlL'
    Application.Goto Reference:="R" & CStr(intLastRow) & "C" & CStr(intlastColumn + intFormulaCount)
    ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")

    ' Select all the formula cells
    ActiveCell.Offset(-intLastRow + 1, 1 - intFormulaCount).Range("A1:" & fn_ColLetter(intFormulaCount + 1) & CStr(intLastRow)).Select
End If

' Final Selection to clipboard and Cleaning of data
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False              ' Repaste "Values Only" back into cells
Selection.Replace What:="CAST('' AS", Replacement:="CAST(NULL AS", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False  ' convert all blank cells to NULL
Selection.Replace What:="'00-Jan-1900 00:00:00'", Replacement:="NULL", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False  ' convert all blank Date cells to NULL
Selection.Replace What:="'NULL'", Replacement:="NULL", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False  ' convert all 'NULL' cells to NULL
Selection.Replace What:=vbTab, Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False        ' Replace all Tabs in cells to Space to prevent Double Quotes occuring in the final paste text

MsgBox "SQL Code has been added to clipboard - Please Paste into SSMS window", vbOKOnly, "Transfer to SQL"

Application.DisplayAlerts = False       ' Temporarily switch off Display Alerts
ActiveWindow.Close                      ' Delete newly created worksheet
Application.DisplayAlerts = True        ' Switch display alerts back on

End Sub

Function fn_ColLetter(Col As Integer) As String

Dim strColLetter As String

If Col > 26 Then
    ' double letter columns
    strColLetter = Chr(Int((Col - 1) / 26) + 64) & _
        Chr(((Col - 1) Mod 26) + 65)
    ' single letter columns
    strColLetter = Chr(Col + 64)
End If
fn_ColLetter = strColLetter
End Function

SQL Server "cannot perform an aggregate function on an expression containing an aggregate or a subquery", but Sybase can

One option is to put the subquery in a LEFT JOIN:

select sum ( t.graduates ) - t1.summedGraduates 
from table as t
    left join 
        select sum ( graduates ) summedGraduates, id
        from table  
        where group_code not in ('total', 'others' )
        group by id 
    ) t1 on =
where t.group_code = 'total'
group by t1.summedGraduates 

Perhaps a better option would be to use SUM with CASE:

select sum(case when group_code = 'total' then graduates end) -
    sum(case when group_code not in ('total','others') then graduates end)
from yourtable

SQL Fiddle Demo with both

I cannot start SQL Server browser

Make sure that you run the SQL Server Configuration Manager snap-in as Administrator if UAC is enabled. Then right click the service and then click properties, change the start mode to enabled, then start it.

Best way to store time (hh:mm) in a database

If you are using SQL Server 2008+, consider the TIME datatype. SQLTeam article with more usage examples.

SQL "select where not in subquery" returns no results

Please follow the below example to understand the above topic:

Also you can visit the following link to know Anti join

select department_name,department_id from hr.departments dep
where not exists 
    (select 1 from hr.employees emp
    where emp.department_id=dep.department_id
order by dep.department_name;
Benefits    160
Construction    180
Contracting 190

But if we use NOT IN in that case we do not get any data.

select Department_name,department_id from hr.departments dep 
where department_id not in (select department_id from hr.employees );

no data found

This is happening as (select department_id from hr.employees) is returning a null value and the entire query is evaluated as false. We can see it if we change the SQL slightly like below and handle null values with NVL function.

select Department_name,department_id from hr.departments dep 
where department_id not in (select NVL(department_id,0) from hr.employees )

Now we are getting data:

Treasury    120
Corporate Tax   130
Control And Credit  140
Shareholder Services    150
Benefits    160

Again we are getting data as we have handled the null value with NVL function.

How do I execute a stored procedure once for each row returned by query?

Something like this substitutions will be needed for your tables and field names.

Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)
Declare @i Int, @MaxI Int, @UserID nVarchar(50)

Insert into @TableUser
Select User_ID
From Users 
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1

While @i <= @MaxI
Select @UserID = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @UserID

 @i = @i + 1, @UserID = null

Connecting to local SQL Server database using C#

If you're using SQL Server express, change

SqlConnection conn = new SqlConnection("Server=localhost;" 
       + "Database=Database1;");


SqlConnection conn = new SqlConnection("Server=localhost\SQLExpress;" 
       + "Database=Database1;");

That, and hundreds more connection strings can be found at

Using IF ELSE statement based on Count to execute different Insert statements

Not very clear what you mean by

"I cant find any examples to help me understand how I can use this to run 2 different statements:"

. Is it using CASE like a SWITCH you are after?

select case when totalCount >= 0 and totalCount < 11 then '0-10'
            when tatalCount > 10 and totalCount < 101 then '10-100'
            else '>100' end as newColumn
from (
  SELECT [Some Column], COUNT(*) TotalCount
  WHERE [Some Column] = 'Target Data'
  GROUP BY [Some Column]
) A


Add .* to s in your first line.


DELETE s.* FROM spawnlist s
INNER JOIN npc n ON s.npc_templateid = n.idTemplate
WHERE (n.type = "monster");

Can I use CASE statement in a JOIN condition?

Instead, you simply JOIN to both tables, and in your SELECT clause, return data from the one that matches:

I suggest you to go through this link Conditional Joins in SQL Server and T-SQL Case Statement in a JOIN ON Clause


    SELECT  *
FROM    sys.indexes i
        JOIN sys.partitions p
            ON i.index_id = p.index_id 
        JOIN sys.allocation_units a
            ON a.container_id =
               WHEN a.type IN (1, 3)
                   THEN  p.hobt_id 
               WHEN a.type IN (2)
                   THEN p.partition_id

Edit: As per comments.

You can not specify the join condition as you are doing.. Check the query above that have no error. I have take out the common column up and the right column value will be evaluated on condition.

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

A workaround for CONTAINS: If you don't want to create a full text Index on the column, and performance is not one of your priorities you could use the LIKE statement which doesn't need any prior configuration:

Example: find all Products that contains the letter Q:

SELECT ID, ProductName
FROM [ProductsDB].[dbo].[Products]
WHERE [ProductsDB].[dbo].[Products].ProductName LIKE '%Q%'

Insert all data of a datagridview to database at once

If you move your for loop, you won't have to make multiple connections. Just a quick edit to your code block (by no means completely correct):

string StrQuery;
    using (SqlConnection conn = new SqlConnection(ConnString))
        using (SqlCommand comm = new SqlCommand())
            comm.Connection = conn;
            for(int i=0; i< dataGridView1.Rows.Count;i++)
                StrQuery= @"INSERT INTO tableName VALUES (" 
                    + dataGridView1.Rows[i].Cells["ColumnName"].Text+", " 
                    + dataGridView1.Rows[i].Cells["ColumnName"].Text+");";
                comm.CommandText = StrQuery;

As to executing multiple SQL commands at once, please look at this link: Multiple statements in single SqlCommand

How to use group by with union in t-sql

You need to alias the subquery. Thus, your statement should be:

From    (
        Select id, time
        From dbo.tablea
        Union All
        Select id, time
        From dbo.tableb
        ) As Z
Group By

How to detect if a string contains special characters?

Assuming SQL Server:

e.g. if you class special characters as anything NOT alphanumeric:

SET @MyString = 'adgkjb$'

IF (@MyString LIKE '%[^a-zA-Z0-9]%')
    PRINT 'Contains "special" characters'
    PRINT 'Does not contain "special" characters'

Just add to other characters you don't class as special, inside the square brackets

Import MySQL database into a MS SQL Server

Also you can use 'ODBC' + 'SQL Server Import and Export Wizard'. Below link describes it:

enter image description here

Getting the minimum of two values in SQL

The solutions using CASE, IIF, and UDF are adequate, but impractical when extending the problem to the general case using more than 2 comparison values. The generalized solution in SQL Server 2008+ utilizes a strange application of the VALUES clause:

PaidForPast=(SELECT MIN(x) FROM (VALUES (PaidThisMonth),(OwedPast)) AS value(x))

Credit due to this website:

Cannot bulk load. Operating system error code 5 (Access is denied.)

In our case it ended up being a Kerberos issue. I followed the steps in this article to resolve the issue:

It came down to configuring delegation on the machine account of the SQL Server where the BULK INSERT statement is running. The machine account needs to be able to delegate via the "cifs" service to the file server where the files are located. If you are using constrained delegation make sure to specify "Use any authenication protocol".

If DFS is involved you can execute the following Powershell command to get the name of the file server:

Get-DfsnFolderTarget -Path "\\dfsnamespace\share"

SQL-Server: Error - Exclusive access could not be obtained because the database is in use

Use Master
alter database databasename set offline with rollback immediate;

--Do Actual Restore
FROM DISK = 'path of bak file'
WITH MOVE 'datafile_data' TO 'D:\newDATA\data.mdf',
MOVE 'logfile_Log' TO 'D:\newDATA\DATA_log.ldf',replace

alter database databasename set online with rollback immediate;

Row Offset in SQL Server

Depending on your version ou cannot do it directly, but you could do something hacky like

select top 25 *
from ( 
  select top 75 *
  from   table 
  order by field asc
) a 
order by field desc 

where 'field' is the key.

A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)

the following points work for me. Try:

  1. start SSMS as administrator
  2. make sure SQL services are running. Change startup type to 'Automatic'

enter image description here

  1. In SSMS, in service instance property table, enable below:

enter image description here

Add Foreign Key relationship between two Databases

If you need rock solid integrity, have both tables in one database, and use an FK constraint. If your parent table is in another database, nothing prevents anyone from restoring that parent database from an old backup, and then you have orphans.

This is why FK between databases is not supported.

creating triggers for After Insert, After Update and After Delete in SQL

(Update: overlooked a fault in the matter, I have corrected)

(Update2: I wrote from memory the code screwed up, repaired it)

(Update3: check on SQLFiddle)

create table Derived_Values
    BusinessUnit nvarchar(100) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)


ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

create table Derived_Values_Test
    BusinessUnit nvarchar(150)
    ,Questions nvarchar(100)
    ,Answer nvarchar(100)


CREATE TRIGGER trgAfterUpdate ON  [Derived_Values]
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Updated Record -- After Update Trigger.'

    insert into 
        --(BusinessUnit,Questions, Answer) 
        @BusinessUnit + i.BusinessUnit, i.Questions, i.Answer
        inserted i
        inner join deleted d on i.BusinessUnit = d.BusinessUnit


CREATE TRIGGER trgAfterDelete ON  [Derived_Values]
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Deleted Record -- After Delete Trigger.'

    insert into 
        --(BusinessUnit,Questions, Answer) 
        @BusinessUnit + d.BusinessUnit, d.Questions, d.Answer
        deleted d


insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22')

UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11');
UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12');
UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21');
UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22');

delete Derived_Values;

and then:

SELECT * FROM Derived_Values;

select * from Derived_Values_Test;

Record Count: 0;

Updated Record -- After Update Trigger.BU1  Q11 Updated Answers A11
Deleted Record -- After Delete Trigger.BU1  Q11 A11
Updated Record -- After Update Trigger.BU1  Q12 Updated Answers A12
Deleted Record -- After Delete Trigger.BU1  Q12 A12
Updated Record -- After Update Trigger.BU2  Q21 Updated Answers A21
Deleted Record -- After Delete Trigger.BU2  Q21 A21
Updated Record -- After Update Trigger.BU2  Q22 Updated Answers A22
Deleted Record -- After Delete Trigger.BU2  Q22 A22

(Update4: If you want to sync: SQLFiddle)

create table Derived_Values
    BusinessUnit nvarchar(100) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)


ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

create table Derived_Values_Test
    BusinessUnit nvarchar(150) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)


ALTER TABLE Derived_Values_Test ADD CONSTRAINT PK_Derived_Values_Test
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

CREATE TRIGGER trgAfterInsert ON  [Derived_Values]
        i.BusinessUnit, i.Questions, i.Answer
        inserted i


CREATE TRIGGER trgAfterUpdate ON  [Derived_Values]
    declare @BusinessUnit nvarchar(50)
    set @BusinessUnit = 'Updated Record -- After Update Trigger.'

        --BusinessUnit = i.BusinessUnit
        --,Questions = i.Questions
        Answer = i.Answer
        inner join inserted i 
        [Derived_Values].BusinessUnit = i.BusinessUnit
        [Derived_Values].Questions = i.Questions


CREATE TRIGGER trgAfterDelete ON  [Derived_Values]
        inner join deleted d 
        [Derived_Values_Test].BusinessUnit = d.BusinessUnit
        [Derived_Values_Test].Questions = d.Questions


insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q11', 'A11')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU1', 'Q12', 'A12')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q21', 'A21')
insert Derived_Values (BusinessUnit,Questions, Answer) values ('BU2', 'Q22', 'A22')

UPDATE Derived_Values SET Answer='Updated Answers A11' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q11');
UPDATE Derived_Values SET Answer='Updated Answers A12' from Derived_Values WHERE (BusinessUnit = 'BU1') AND (Questions = 'Q12');
UPDATE Derived_Values SET Answer='Updated Answers A21' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q21');
UPDATE Derived_Values SET Answer='Updated Answers A22' from Derived_Values WHERE (BusinessUnit = 'BU2') AND (Questions = 'Q22');

--delete Derived_Values;

And then:

SELECT * FROM Derived_Values;

select * from Derived_Values_Test;

BU1 Q11 Updated Answers A11
BU1 Q12 Updated Answers A12
BU2 Q21 Updated Answers A21
BU2 Q22 Updated Answers A22

BU1 Q11 Updated Answers A11
BU1 Q12 Updated Answers A12
BU2 Q21 Updated Answers A21
BU2 Q22 Updated Answers A22

Why is SQL server throwing this error: Cannot insert the value NULL into column 'id'?

you didn't give a value for id. Try this :

INSERT INTO role (id, name, created) VALUES ('example1','Content Coordinator', GETDATE()), ('example2', 'Content Viewer', GETDATE())

Or you can set the auto increment on id field, if you need the id value added automatically.

How to find difference between two columns data?

select previous, Present, previous-Present as Difference from tablename


select previous, Present, previous-Present as Difference from #TEMP1

SQL Server convert string to datetime

For instance you can use

update tablename set datetimefield='19980223 14:23:05'
update tablename set datetimefield='02/23/1998 14:23:05'
update tablename set datetimefield='1998-12-23 14:23:05'
update tablename set datetimefield='23 February 1998 14:23:05'
update tablename set datetimefield='1998-02-23T14:23:05'

You need to be careful of day/month order since this will be language dependent when the year is not specified first. If you specify the year first then there is no problem; date order will always be year-month-day.

Correct use of transactions in SQL Server

Easy approach:

    C [nvarchar](100) NOT NULL UNIQUE,

SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.
SELECT * FROM T; -- Check before.
SELECT * FROM T; -- Check after.

How to select all rows which have same value in some column

How about

FROM Employees
WHERE PhoneNumber IN (
    SELECT PhoneNumber
    FROM Employees
    GROUP BY PhoneNumber
    HAVING COUNT(Employee_ID) > 1


"No backupset selected to be restored" SQL Server 2012

I had this problem and it turned out I was trying to restore to the wrong version of SQL. If you want more information on what's going on, try restoring the database using the following SQL:

FROM DISK='<the path to your backup file>\<YourDatabase>.bak'

That should give you the error message that you need to debug this.

Convert Datetime column from UTC to local time in select statement

In postgres this works very nicely..Tell the server the time at which the time is saved, 'utc', and then ask it to convert to a specific timezone, in this case 'Brazil/East'

quiz_step_progresses.created_at  at time zone 'utc' at time zone 'Brazil/East'

Get a complete list of timezones with the following select;

select * from pg_timezone_names;

See details here.

Find duplicate records in a table using SQL Server

Select * from dbo.sales group by shoppername having(count(Item) > 1)

Fill DataTable from SQL Server database

If the variable table contains invalid characters (like a space) you should add square brackets around the variable.

public DataTable fillDataTable(string table)
    string query = "SELECT * FROM dstut.dbo.[" + table + "]";

    using(SqlConnection sqlConn = new SqlConnection(conSTR))
    using(SqlCommand cmd = new SqlCommand(query, sqlConn))
        DataTable dt = new DataTable();
        return dt;

By the way, be very careful with this kind of code because is open to Sql Injection. I hope for you that the table name doesn't come from user input

How do I enable MSDTC on SQL Server?

Use this for windows Server 2008 r2 and Windows Server 2012 R2

  1. Click Start, click Run, type dcomcnfg and then click OK to open Component Services.

  2. In the console tree, click to expand Component Services, click to expand Computers, click to expand My Computer, click to expand Distributed Transaction Coordinator and then click Local DTC.

  3. Right click Local DTC and click Properties to display the Local DTC Properties dialog box.

  4. Click the Security tab.

  5. Check mark "Network DTC Access" checkbox.

  6. Finally check mark "Allow Inbound" and "Allow Outbound" checkboxes.

  7. Click Apply, OK.

  8. A message will pop up about restarting the service.

  9. Click OK and That's all.

Reference :

Note: Sometimes the network firewall on the Local Computer or the Server could interrupt your connection so make sure you create rules to "Allow Inbound" and "Allow Outbound" connection for C:\Windows\System32\msdtc.exe

Select SQL Server database size

Worked perfectly for me to calculate SQL database size in SQL Server 2012

exec sp_spaceused

enter image description here

How can I set an SQL Server connection string?

We can simply connect to the database like this:


For example:

string connectionString = @"uid=spacecraftU1;pwd=Appolo11;
SqlConnection con = new SqlConnection(connectionString);

how to get 2 digits after decimal point in tsql?

Try this one -

DECLARE @i FLOAT = 6.677756

      ROUND(@i, 2)
    , FORMAT(@i, 'N2')
    , CAST(@i AS DECIMAL(18,2))
    , FLOOR((@i - FLOOR(@i)) * 100)



Retrieve the maximum length of a VARCHAR column in SQL Server

for mysql its length not len

SELECT MAX(LENGTH(Desc)) FROM table_name

Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?

OK, encouraged by jimhark here is an example of the old single hash table approach: -



    SELECT 1, 'Data1'
    SELECT 2, 'Data2'




    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
        EXEC SP3




    EXEC SP2


--I want some data back from SP3

-- Just run the SP1


--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1




--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1





Getting return value from stored procedure in C#

When we return a value from Stored procedure without select statement. We need to use "ParameterDirection.ReturnValue" and "ExecuteScalar" command to get the value.

    @Email NVARCHAR(20)
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    -- Insert statements for procedure here
    IF EXISTS(SELECT Email FROM Users where Email = @Email)
        RETURN 0 
        RETURN 1

in C#


public int GetOutputParaByCommand(string Command)
            object identity = 0;
                mobj_SqlCommand.CommandText = Command;
                SqlParameter SQP = new SqlParameter("returnVal", SqlDbType.Int);
                SQP.Direction = ParameterDirection.ReturnValue;
                mobj_SqlCommand.Connection = mobj_SqlConnection;
                identity = Convert.ToInt32(SQP.Value);
            catch (Exception ex)

            return Convert.ToInt32(identity);

We get the returned value of SP "IsEmailExists" using above c# function.

Local and global temporary tables in SQL Server

It is worth mentioning that there is also: database scoped global temporary tables(currently supported only by Azure SQL Database).

Global temporary tables for SQL Server (initiated with ## table name) are stored in tempdb and shared among all users’ sessions across the whole SQL Server instance.

Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL Database. User sessions from other databases cannot access global temporary tables.

-- Session A creates a global temp table ##test in Azure SQL Database testdb1
-- and adds 1 row
CREATE TABLE ##test ( a int, b int);
INSERT INTO ##test values (1,1);

-- Session B connects to Azure SQL Database testdb1 
-- and can access table ##test created by session A
SELECT * FROM ##test

-- Session C connects to another database in Azure SQL Database testdb2 
-- and wants to access ##test created in testdb1.
-- This select fails due to the database scope for the global temp tables 
SELECT * FROM ##test
Msg 208, Level 16, State 0, Line 1
Invalid object name '##test'



APPLIES TO: Azure SQL Database (feature is in public preview)

Allows setting the auto-drop functionality for global temporary tables. The default is ON, which means that the global temporary tables are automatically dropped when not in use by any session. When set to OFF, global temporary tables need to be explicitly dropped using a DROP TABLE statement or will be automatically dropped on server restart.

With Azure SQL Database single databases and elastic pools, this option can be set in the individual user databases of the SQL Database server. In SQL Server and Azure SQL Database managed instance, this option is set in TempDB and the setting of the individual user databases has no effect.

SQL Server Configuration Manager not found

This path worked for me. on a 32 bit machine.

C:\Windows\System32\mmc.exe /32 C:\Windows\system32\SQLServerManager10.msc


Why are LEFT/RIGHT and LEFT OUTER/RIGHT OUTER the same? Let's explain why this vocabulary. Understand that LEFT and RIGHT joins are specific cases of the OUTER join, and therefore couldn't be anything else than OUTER LEFT/OUTER RIGHT. The OUTER join is also called FULL OUTER as opposed to LEFT and RIGHT joins that are PARTIAL results of the OUTER join. Indeed:

Table A | Table B     Table A | Table B      Table A | Table B      Table A | Table B
   1    |   5            1    |   1             1    |   1             1    |   1
   2    |   1            2    |   2             2    |   2             2    |   2
   3    |   6            3    |  null           3    |  null           -    |   -
   4    |   2            4    |  null           4    |  null           -    |   -
                        null  |   5             -    |   -            null  |   5
                        null  |   6             -    |   -            null  |   6

                      OUTER JOIN (FULL)     LEFT OUTER (partial)   RIGHT OUTER (partial)

It is now clear why those operations have aliases, as well as it is clear only 3 cases exist: INNER, OUTER, CROSS. With two sub-cases for the OUTER. The vocabulary, the way teachers explain this, as well as some answers above, often make it looks like there are lots of different types of join. But it's actually very simple.

SQL query to select dates between two dates

select Date,TotalAllowance 
from Calculation 
where EmployeeId=1
  and convert(varchar(10),Date,111) between '2011/02/25' and '2011/02/27'

How to deal with SQL column names that look like SQL keywords?

In Apache Drill, use backquotes:

select `from` from table;

What is PAGEIOLATCH_SH wait type in SQL Server?

PAGEIOLATCH_SH wait type usually comes up as the result of fragmented or unoptimized index.

Often reasons for excessive PAGEIOLATCH_SH wait type are:

  • I/O subsystem has a problem or is misconfigured
  • Overloaded I/O subsystem by other processes that are producing the high I/O activity
  • Bad index management
  • Logical or physical drive misconception
  • Network issues/latency
  • Memory pressure
  • Synchronous Mirroring and AlwaysOn AG

In order to try and resolve having high PAGEIOLATCH_SH wait type, you can check:

  • SQL Server, queries and indexes, as very often this could be found as a root cause of the excessive PAGEIOLATCH_SH wait types
  • For memory pressure before jumping into any I/O subsystem troubleshooting

Always keep in mind that in case of high safety Mirroring or synchronous-commit availability in AlwaysOn AG, increased/excessive PAGEIOLATCH_SH can be expected.

You can find more details about this topic in the article Handling excessive SQL Server PAGEIOLATCH_SH wait types

Can't change table design in SQL Server 2008

The answer is on the MSDN site:

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column


Additional useful informations from here:

To change the Prevent saving changes that require the table re-creation option, follow these steps:

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options.
  3. In the navigation pane of the Options window, click Designers.
  4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

Risk of turning off the "Prevent saving changes that require table re-creation" option

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server 2008 to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the "Symptoms" section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

Settings, screen shot

Difference between JOIN and INNER JOIN

As the other answers already state there is no difference in your example.

The relevant bit of grammar is documented here

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]

Showing that all are optional. The page further clarifies that

INNER Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

The grammar does also indicate that there is one time where the INNER is required though. When specifying a join hint.

See the example below


       LOOP JOIN T2
         ON X = Y;

         ON X = Y;

enter image description here

How to drop SQL default constraint without knowing its name?

declare @table_name nvarchar(100)
declare @col_name nvarchar(100)
declare @constraint nvarchar(100)
set @table_name = N'TableName'
set @col_name = N'ColumnName'

IF EXISTS (select       c.*
    from        sys.columns c 
    inner join  sys.tables t on t.object_id = c.object_id
    where = @table_name
    and = @col_name) 

sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where = @table_name
and = @col_name

    IF LEN(ISNULL(@constraint, '')) <> 0
        DECLARE @sqlcmd VARCHAR(MAX)
        SET @sqlcmd = 'ALTER TABLE ' + QUOTENAME(@table_name) + ' DROP CONSTRAINT' + 
        EXEC (@sqlcmd);



The backend version is not supported to design database diagrams or tables

This is commonly reported as an error due to using the wrong version of SSMS(Sql Server Management Studio). Use the version designed for your database version. You can use the command select @@version to check which version of sql server you are actually using. This version is reported in a way that is easier to interpret than that shown in the Help About in SSMS.

Using a newer version of SSMS than your database is generally error-free, i.e. backward compatible.

Altering a column: null to not null

For Oracle 11g, I was able to change the column attribute as follows:

ALTER TABLE tablename MODIFY columnname datatype NOT NULL;

Otherwise abatichev's answer seemed good. You can't repeat the alter - it complains (at least in SQL Developer) that the column is already not null.


You mus use nvarchar text too. that's mean you have to simply had a "N" before your massive string and that's it! no limitation anymore

SET @SQL = N'SomeMassiveString > 4000 chars...';

Drop primary key using script in SQL Server database

You can look up the constraint name in the sys.key_constraints table:

FROM   sys.key_constraints
WHERE  [type] = 'PK'
       AND [parent_object_id] = Object_id('dbo.Student');

If you don't care about the name, but simply want to drop it, you can use a combination of this and dynamic sql:


SELECT @table = N'dbo.Student';

SELECT @sql = 'ALTER TABLE ' + @table 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

EXEC sp_executeSQL @sql;

This code is from Aaron Bertrand (source).

How to set value to variable using 'execute' in t-sql?

You can try like below

DECLARE @sqlCommand NVARCHAR(4000)
SET @ID = 4
SET @sqlCommand = 'SELECT @Name = [Name]
FROM [AdventureWorks2014].[HumanResources].[Department]
WHERE DepartmentID = @ID'
EXEC sp_executesql @sqlCommand, N'@ID INT, @Name NVARCHAR(100) OUTPUT',
@ID = @ID, @Name = @Name OUTPUT
SELECT @Name ReturnedName

Source :

Is there a way to cast float as a decimal without rounding and preserving its precision?

cast (field1 as decimal(53,8)
) field 1

The default is: decimal(18,0)

Why did a network-related or instance-specific error occur while establishing a connection to SQL Server?

While u have tried tons of configuring and clicking and still facing the pop-up error, maybe my answer here will guide u to kill the error.

SELECT query with CASE condition and SUM()

Use an "Or"

Select SUM(CAmount) as PaymentAmount 
from TableOrderPayment 
where (CPaymentType='Check' Or CPaymentType='Cash')
   and CDate <= case CPaymentType When 'Check' Then SYSDATETIME() else CDate End
   and CStatus='" & "Active" & "'"

or an "IN"

Select SUM(CAmount) as PaymentAmount 
from TableOrderPayment 
where CPaymentType IN ('Check', 'Cash')
   and CDate <= case CPaymentType When 'Check' Then SYSDATETIME() else CDate End
   and CStatus='" & "Active" & "'"

SQL Server: use CASE with LIKE

One of the first things you need to learn about SQL (and relational databases) is that you shouldn't store multiple values in a single field.

You should create another table and store one value per row.

This will make your querying easier, and your database structure better.

    case when exists (select countryname from itemcountries where and countryname = @country) then 'national' else 'regional' end
from yourtable

How to get date representing the first day of a month?

The accepted answer works, and may be faster, but SQL 2012 and above have a more easily understood method:

SELECT cast(format(GETDATE(), 'yyyy-MM-01') as Date)

How to get script of SQL Server data?

Check out SSMS Tool Pack. It works in Management Studio 2005 and 2008. There is an option to generate insert statements which I've found helpful moving small amounts of data from one system to another.

With this option you will have to script out the DDL separately.

How to check for Is not Null And Is not Empty string in SQL server?

For some kind of reason my NULL values where of data length 8. That is why none of the abovementioned seemed to work. If you encounter the same problem, use the following code:

--Check the length of your NULL values
FROM your_table

--Filter the length of your NULL values (8 is used as example)

Each GROUP BY expression must contain at least one column that is not an outer reference

Here's a simple query to find company name who has a medicine type of A and makes more than 2.

    WHERE type='A' 

Escape a string in SQL Server so that it is safe to use in LIKE expression

Do you want to look for strings that include an escape character? For instance you want this:

select * from table where myfield like '%10%%'.

Where you want to search for all fields with 10%? If that is the case then you may use the ESCAPE clause to specify an escape character and escape the wildcard character.

select * from table where myfield like '%10!%%' ESCAPE '!'

IDENTITY_INSERT is set to OFF - How to turn it ON?

Shouldn't you be setting identity_Insert ON, inserting the records and then turning it back off?

Like this:


ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 
...insert command...

SQL WHERE.. IN clause multiple columns

If you want for one table then use following query

FROM Student_info S
  INNER JOIN Student_info UT
    ON =
    AND S.studentName = UT.studentName
where in (1,2) and S.studentName in ('a','b')

and table data as follow

1   a   ad  ca
2   b   bd  bd
3   a   ad  ad
4   b   bd  bd
5   c   cd  cd

Then output as follow

1   a   ad  ca
2   b   bd  bd

How do I create a unique constraint that also allows nulls?

It is possible to create a unique constraint on a Clustered Indexed View

You can create the View like this:

SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

and the unique constraint like this:

  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition

You cannot DELETE from multiple tables with a single expression in SQL 2005 - or any other standard SQL for that matter. Access is the exception here.

The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger.

How can I truncate a datetime in SQL Server?

you could just do this (SQL 2008):

declare @SomeDate date = getdate()

select @SomeDate


How do I find a stored procedure containing <text>?

Here is the union version that use Losbear made, but modified so that it also outputs the schema name:

DECLARE @SearchText varchar(1000) = 'searchtext';

SELECT DISTINCT sc.SPECIFIC_SCHEMA as 'Schema', sp.SPName as 'Name'
        WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%' 
        WHERE [text] LIKE '%' + @SearchText + '%' 
        AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    (SELECT OBJECT_NAME(object_id) SPName
        FROM sys.sql_modules
        WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
        AND definition LIKE '%' + @SearchText + '%')
) AS sp



  FROM TableName

Oracle & DB2

     , LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName


     , STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs
  FROM TableName

SQL Server

SQL Server ≥ 2017 & Azure SQL

     , STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName

SQL Server ≤ 2016 (CTE included to encourage the DRY principle)

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA


Ordering requires a CTE or subquery

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName
        ORDER BY FieldA, FieldB)
     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM CTE_TableName

Without ordering

     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM TableName

SSIS Connection Manager Not Storing SQL Password

Check the text contents of the connection manager file itself, the password field might be configured in the Project.params file, in which case entering the password into the connection manager window will cause it to not save.

The transaction log for the database is full

The following will truncate the log.

USE [yourdbname] 

DBCC SHRINKFILE(yourdbname_log, 1)
DBCC SHRINKFILE(yourdbname_log, 1)


SQL Server: Filter output of sp_who2

Yes, by capturing the output of sp_who2 into a table and then selecting from the table, but that would be a bad way of doing it. First, because sp_who2, despite its popularity, its an undocumented procedure and you shouldn't rely on undocumented procedures. Second because all sp_who2 can do, and much more, can be obtained from sys.dm_exec_requests and other DMVs, and show can be filtered, ordered, joined and all the other goodies that come with queriable rowsets.

How to return temporary table from stored procedure

The return type of a procedure is int.

You can also return result sets (as your code currently does) (okay, you can also send messages, which are strings)

Those are the only "returns" you can make. Whilst you can add table-valued parameters to a procedure (see BOL), they're input only.


(Or as another poster mentioned, you could also use a Table Valued Function, rather than a procedure)

Regular expressions inside SQL Server

You can write queries like this in SQL Server:

--each [0-9] matches a single digit, this would match 5xx
SELECT * FROM YourTable WHERE SomeField LIKE '5[0-9][0-9]'

Appropriate datatype for holding percent values?

Assuming two decimal places on your percentages, the data type you use depends on how you plan to store your percentages. If you are going to store their fractional equivalent (e.g. 100.00% stored as 1.0000), I would store the data in a decimal(5,4) data type with a CHECK constraint that ensures that the values never exceed 1.0000 (assuming that is the cap) and never go below 0 (assuming that is the floor). If you are going to store their face value (e.g. 100.00% is stored as 100.00), then you should use decimal(5,2) with an appropriate CHECK constraint. Combined with a good column name, it makes it clear to other developers what the data is and how the data is stored in the column.

Safest way to get last record ID from a table

You can try:

SELECT id FROM your_table WHERE id = (SELECT MAX(id) FROM your_table)

Where id is a primary key of the your_table

How to add days to the current date?

From the SQL Server 2017 official documentation:


If you would like to remove the time part of the GETDATE function, you can do:


Disable Transaction Log

You can't do without transaction logs in SQL Server, under any circumstances. The engine simply won't function.

You CAN set your recovery model to SIMPLE on your dev machines - that will prevent transaction log bloating when tran log backups aren't done.


SQL Server: Examples of PIVOTing String data

Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once.

SELECT Action,
       MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol, 
       MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
 GROUP BY Action

How to write "not in ()" sql query using join

SELECT d1.Short_Code 
FROM domain1 d1
LEFT JOIN domain2 d2
ON d1.Short_Code = d2.Short_Code
WHERE d2.Short_Code IS NULL

XPath to fetch SQL XML value

I always go back to this article SQL Server 2005 XQuery and XML-DML - Part 1 to know how to use the XML features in SQL Server 2005.

For basic XPath know-how, I'd recommend the W3Schools tutorial.

Reset identity seed after deleting records in SQL Server

I use the following script to do this. There's only one scenario in which it will produce an "error", which is if you have deleted all rows from the table, and IDENT_CURRENT is currently set to 1, i.e. there was only one row in the table to begin with.

DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)

    IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
    DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)

How to replace a string in a SQL Server Table Column

select replace(ImagePath, '~/', '../') as NewImagePath from tblMyTable 

where "ImagePath" is my column Name.
"NewImagePath" is temporery column Name insted of "ImagePath"
"~/" is my current string.(old string)
"../" is my requried string.(new string)
"tblMyTable" is my table in database.

Possible reasons for timeout when trying to access EC2 instance

I had the same problem, and the solution ended up being adding my local machine's IP to the list of inbound rules in the active security group. In the inbound dialog below, enter 22 in the port range, your local IP/32 in the source field, and leave 'custom tcp rule' in the dropdown.

enter image description here

SQL Server GROUP BY datetime ignore hour minute and a select with a date and sum value

-- I like this as the data type and the format remains consistent with a date time data type

;with cte as(
        cast(utcdate as date) UtcDay, DATEPART(hour, utcdate) UtcHour, count(*) as Counts
    from dbo.mytable cd 
    where utcdate between '2014-01-14' and '2014-01-15'
    group by
        cast(utcdate as date), DATEPART(hour, utcdate)
select dateadd(hour, utchour, cast(utcday as datetime)) as UTCDateHour, Counts
from cte

Get date from input form within PHP

if (isset($_POST['birthdate'])) {
    $timestamp = strtotime($_POST['birthdate']); 

Chrome Uncaught Syntax Error: Unexpected Token ILLEGAL

There's some sort of bogus character at the end of that source. Try deleting the last line and adding it back.

I can't figure out exactly what's there, yet ...

edit — I think it's a zero-width space, Unicode 200B. Seems pretty weird and I can't be sure of course that it's not a Stackoverflow artifact, but when I copy/paste that last function including the complete last line into the Chrome console, I get your error.

A notorious source of such characters are websites like jsfiddle. I'm not saying that there's anything wrong with them — it's just a side-effect of something, maybe the use of content-editable input widgets.

If you suspect you've got a case of this ailment, and you're on MacOS or Linux/Unix, the od command line tool can show you (albeit in a fairly ugly way) the numeric values in the characters of the source code file. Some IDEs and editors can show "funny" characters as well. Note that such characters aren't always a problem. It's perfectly OK (in most reasonable programming languages, anyway) for there to be embedded Unicode characters in string constants, for example. The problems start happening when the language parser encounters the characters when it doesn't expect them.

wordpress contactform7 textarea cols and rows change in smaller screens

I was able to get this work. I added the following to my custom CSS:

.wpcf7-form textarea{ 
    width: 100% !important;

ORACLE convert number to string

Using the FM format model modifier to get close, as you won't get the trailing zeros after the decimal separator; but you will still get the separator itself, e.g. 50.. You can use rtrim to get rid of that:

select to_char(a, '99D90'),
    to_char(a, '90D90'),
    to_char(a, 'FM90D99'),
    rtrim(to_char(a, 'FM90D99'), to_char(0, 'D'))
from (
    select 50 a from dual
    union all select 50.57 from dual
    union all select 5.57 from dual
    union all select 0.35 from dual
    union all select 0.4 from dual
order by a;

------ ------ ------ ------
   .35   0.35 0.35   0.35
   .40   0.40 0.4    0.4
  5.57   5.57 5.57   5.57
 50.00  50.00 50.    50
 50.57  50.57 50.57  50.57

Note that I'm using to_char(0, 'D') to generate the character to trim, to match the decimal separator - so it looks for the same character, , or ., as the first to_char adds.

The slight downside is that you lose the alignment. If this is being used elsewhere it might not matter, but it does then you can also wrap it in an lpad, which starts to make it look a bit complicated:

lpad(rtrim(to_char(a, 'FM90D99'), to_char(0, 'D')), 6)

------ ------ ------ ------ ------------------------
   .35   0.35 0.35   0.35     0.35
   .40   0.40 0.4    0.4       0.4
  5.57   5.57 5.57   5.57     5.57
 50.00  50.00 50.    50         50
 50.57  50.57 50.57  50.57   50.57

ps command doesn't work in docker container

In case you can't install the procps package (don't have proper permissions) you can use /proc directory.

The first few directories (named as numbers) are PIDs of your processes. Inside directories, you can find additional information useful to decipher which process is connected to each PID. For example, you can use the cat command to view "cmdline" file to check which process is connected to PID.

$ ls /proc
1 10 11 ...

$ ls -1 /proc/22

$ cat /proc/22/cmdline 

Converting std::__cxx11::string to std::string

For me -D_GLIBCXX_USE_CXX11_ABI=0 didn't help.

It works after I linked to C++ libs version instead of gnustl.

How to permanently add a private key with ssh-add on Ubuntu?

I had the same issue on Ubuntu 16.04: some keys were added permanently, for others I had to execute ssh-add on every session. I found out that the keys which were added permanently had both private and public key located in ~/.ssh and the keys which were forgotten on every session had only private keys in ~/.ssh dir. So solution is simple: you should copy both private and public key to ~/.ssh before executing ssh-add.

P.S.: As far as I understand from Gnome wiki my method works thanks to gnome-keyring tool which is part of the Gnome Desktop Environment. Therefore my method should probably work only if you use Gnome or Gnome-based DE.

Free space in a CMD shell

The following script will give you free bytes on the drive:

@setlocal enableextensions enabledelayedexpansion
@echo off
for /f "tokens=3" %%a in ('dir c:\') do (
    set bytesfree=%%a
set bytesfree=%bytesfree:,=%
echo %bytesfree%
endlocal && set bytesfree=%bytesfree%

Note that this depends on the output of your dir command, which needs the last line containing the free space of the format 24 Dir(s) 34,071,691,264 bytes free. Specifically:

  • it must be the last line (or you can modify the for loop to detect the line explicitly rather than relying on setting bytesfree for every line).
  • the free space must be the third "word" (or you can change the tokens= bit to get a different word).
  • thousands separators are the , character (or you can change the substitution from comma to something else).

It doesn't pollute your environment namespace, setting only the bytesfree variable on exit. If your dir output is different (eg, different locale or language settings), you will need to adjust the script.

How to use Visual Studio Code as Default Editor for Git

For what I understand, VSCode is not in AppData anymore.

So Set the default git editor by executing that command in a command prompt window:

git config --global core.editor "'C:\Program Files (x86)\Microsoft VS Code\code.exe' -w"

The parameter -w, --wait is to wait for window to be closed before returning. Visual Studio Code is base on Atom Editor. if you also have atom installed execute the command atom --help. You will see the last argument in the help is wait.

Next time you do a git rebase -i HEAD~3 it will popup Visual Studio Code. Once VSCode is close then Git will take back the lead.

Note: My current version of VSCode is 0.9.2

I hope that help.

A potentially dangerous Request.Form value was detected from the client

You can automatically HTML encode field in custom Model Binder. My solution some different, I put error in ModelState and display error message near the field. It`s easy to modify this code for automatically encode

 public class AppModelBinder : DefaultModelBinder
        protected override object CreateModel(ControllerContext controllerContext, ModelBindingContext bindingContext, Type modelType)
                return base.CreateModel(controllerContext, bindingContext, modelType);
            catch (HttpRequestValidationException e)
                HandleHttpRequestValidationException(bindingContext, e);
                return null; // Encode here
        protected override object GetPropertyValue(ControllerContext controllerContext, ModelBindingContext bindingContext,
            PropertyDescriptor propertyDescriptor, IModelBinder propertyBinder)
                return base.GetPropertyValue(controllerContext, bindingContext, propertyDescriptor, propertyBinder);
            catch (HttpRequestValidationException e)
                HandleHttpRequestValidationException(bindingContext, e);
                return null; // Encode here

        protected void HandleHttpRequestValidationException(ModelBindingContext bindingContext, HttpRequestValidationException ex)
            var valueProviderCollection = bindingContext.ValueProvider as ValueProviderCollection;
            if (valueProviderCollection != null)
                ValueProviderResult valueProviderResult = valueProviderCollection.GetValue(bindingContext.ModelName, skipValidation: true);
                bindingContext.ModelState.SetModelValue(bindingContext.ModelName, valueProviderResult);

            string errorMessage = string.Format(CultureInfo.CurrentCulture, "{0} contains invalid symbols: <, &",

            bindingContext.ModelState.AddModelError(bindingContext.ModelName, errorMessage);

In Application_Start:

ModelBinders.Binders.DefaultBinder = new AppModelBinder();

Note that it works only for form fields. Dangerous value not passed to controller model, but stored in ModelState and can be redisplayed on form with error message.

Dangerous chars in URL may be handled this way:

private void Application_Error(object sender, EventArgs e)
    Exception exception = Server.GetLastError();
    HttpContext httpContext = HttpContext.Current;

    HttpException httpException = exception as HttpException;
    if (httpException != null)
        RouteData routeData = new RouteData();
        routeData.Values.Add("controller", "Error");
        var httpCode = httpException.GetHttpCode();
        switch (httpCode)
            case (int)HttpStatusCode.BadRequest /* 400 */:
                if (httpException.Message.Contains("Request.Path"))
                    RequestContext requestContext = new RequestContext(new HttpContextWrapper(Context), routeData);
                    requestContext.RouteData.Values["action"] ="InvalidUrl";
                    requestContext.RouteData.Values["controller"] ="Error";
                    IControllerFactory factory = ControllerBuilder.Current.GetControllerFactory();
                    IController controller = factory.CreateController(requestContext, "Error");
                    Response.StatusCode = (int)HttpStatusCode.BadRequest /* 400 */;


public class ErrorController : Controller
   public ActionResult InvalidUrl()
      return View();

How can I make all images of different height and width the same via CSS?

Image size is not depend on div height and width,

use img element in css

Here is css code that help you

div img{
         width: 100px;

if you want to set size by div

use this

div {

by this code your image show in original size but show first 100x100px overflow will hide

Call child component method from parent class - Angular

Consider the following example,

    import import { AfterViewInit, ViewChild } from '@angular/core';
    import { Component } from '@angular/core';
    import { CountdownTimerComponent }  from './countdown-timer.component';
        selector: 'app-countdown-parent-vc',
        templateUrl: 'app-countdown-parent-vc.html',
        styleUrl: [app-countdown-parent-vc.css]
    export class CreateCategoryComponent implements OnInit {
         @ViewChild(CountdownTimerComponent, {static: false})
         private timerComponent: CountdownTimerComponent;
         ngAfterViewInit() {


Read more about @ViewChild here.

The server response was: 5.7.0 Must issue a STARTTLS command first. i16sm1806350pag.18 - gsmtp

If you get the error "Unrecognized attribute 'enableSsl'" when following the advice to add that parameter to your web.config. I found that I was able to workaround the error by adding it to my code file instead in this format:

SmtpClient smtp = new SmtpClient();
smtp.EnableSsl = true;

catch (Exception ex)
    MsgBox("Message not emailed: " + ex.ToString());

This is the section of my web.config:

    <smtp from="<from_email>">
      <network host=""
       password="<your_app_password>" />

Input text dialog Android

Sounds like a good opportunity to use an AlertDialog.

As basic as it seems, Android does not have a built-in dialog to do this (as far as I know). Fortunately, it's just a little extra work on top of creating a standard AlertDialog. You simply need to create an EditText for the user to input data, and set it as the view of the AlertDialog. You can customize the type of input allowed using setInputType, if you need.

If you're able to use a member variable, you can simply set the variable to the value of the EditText, and it will persist after the dialog has dismissed. If you can't use a member variable, you may need to use a listener to send the string value to the right place. (I can edit and elaborate more if this is what you need).

Within your class:

private String m_Text = "";

Within the OnClickListener of your button (or in a function called from there):

AlertDialog.Builder builder = new AlertDialog.Builder(this);

// Set up the input
final EditText input = new EditText(this);
// Specify the type of input expected; this, for example, sets the input as a password, and will mask the text

// Set up the buttons
builder.setPositiveButton("OK", new DialogInterface.OnClickListener() { 
    public void onClick(DialogInterface dialog, int which) {
        m_Text = input.getText().toString();
builder.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
    public void onClick(DialogInterface dialog, int which) {

What are good message queue options for nodejs?

Here's a couple of recommendations I can make:

node-amqp: A RabbitMQ client that I have successfully used in combination with Socket.IO to make a real-time multi-player game and chat application amongst other things. Seems reliable enough.

zeromq.node: If you want to go down the non-brokered route this might be worth a look. More work to implement functionality but your more likely to get lower latency and higher throughput.

Skip first line(field) in loop using CSV file?

There are many ways to skip the first line. In addition to those said by Bakuriu, I would add:

with open(filename, 'r') as f:
    for line in f:


with open(filename,'r') as f:
    lines = f.readlines()[1:]

Adding ASP.NET MVC5 Identity Authentication to an existing project

Configuring Identity to your existing project is not hard thing. You must install some NuGet package and do some small configuration.

First install these NuGet packages with Package Manager Console:

PM> Install-Package Microsoft.AspNet.Identity.Owin 
PM> Install-Package Microsoft.AspNet.Identity.EntityFramework
PM> Install-Package Microsoft.Owin.Host.SystemWeb 

Add a user class and with IdentityUser inheritance:

public class AppUser : IdentityUser
    //add your custom properties which have not included in IdentityUser before
    public string MyExtraProperty { get; set; }  

Do same thing for role:

public class AppRole : IdentityRole
    public AppRole() : base() { }
    public AppRole(string name) : base(name) { }
    // extra properties here 

Change your DbContext parent from DbContext to IdentityDbContext<AppUser> like this:

public class MyDbContext : IdentityDbContext<AppUser>
    // Other part of codes still same 
    // You don't need to add AppUser and AppRole 
    // since automatically added by inheriting form IdentityDbContext<AppUser>

If you use the same connection string and enabled migration, EF will create necessary tables for you.

Optionally, you could extend UserManager to add your desired configuration and customization:

public class AppUserManager : UserManager<AppUser>
    public AppUserManager(IUserStore<AppUser> store)
        : base(store)

    // this method is called by Owin therefore this is the best place to configure your User Manager
    public static AppUserManager Create(
        IdentityFactoryOptions<AppUserManager> options, IOwinContext context)
        var manager = new AppUserManager(
            new UserStore<AppUser>(context.Get<MyDbContext>()));

        // optionally configure your manager
        // ...

        return manager;

Since Identity is based on OWIN you need to configure OWIN too:

Add a class to App_Start folder (or anywhere else if you want). This class is used by OWIN. This will be your startup class.

namespace MyAppNamespace
    public class IdentityConfig
        public void Configuration(IAppBuilder app)
            app.CreatePerOwinContext(() => new MyDbContext());
            app.CreatePerOwinContext<RoleManager<AppRole>>((options, context) =>
                new RoleManager<AppRole>(
                    new RoleStore<AppRole>(context.Get<MyDbContext>())));

            app.UseCookieAuthentication(new CookieAuthenticationOptions
                AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
                LoginPath = new PathString("/Home/Login"),

Almost done just add this line of code to your web.config file so OWIN could find your startup class.

    <!-- other setting here -->
    <add key="owin:AppStartup" value="MyAppNamespace.IdentityConfig" />

Now in entire project you could use Identity just like any new project had already installed by VS. Consider login action for example

public ActionResult Login(LoginViewModel login)
    if (ModelState.IsValid)
        var userManager = HttpContext.GetOwinContext().GetUserManager<AppUserManager>();
        var authManager = HttpContext.GetOwinContext().Authentication;

        AppUser user = userManager.Find(login.UserName, login.Password);
        if (user != null)
            var ident = userManager.CreateIdentity(user, 
            //use the instance that has been created. 
                new AuthenticationProperties { IsPersistent = false }, ident);
            return Redirect(login.ReturnUrl ?? Url.Action("Index", "Home"));
    ModelState.AddModelError("", "Invalid username or password");
    return View(login);

You could make roles and add to your users:

public ActionResult CreateRole(string roleName)
    var roleManager=HttpContext.GetOwinContext().GetUserManager<RoleManager<AppRole>>();

    if (!roleManager.RoleExists(roleName))
        roleManager.Create(new AppRole(roleName));
    // rest of code

You could also add a role to a user, like this:

UserManager.AddToRole(UserManager.FindByName("username").Id, "roleName");

By using Authorize you could guard your actions or controllers:

public ActionResult MySecretAction() {}


[Authorize(Roles = "Admin")]]
public ActionResult MySecretAction() {}

You can also install additional packages and configure them to meet your requirement like Microsoft.Owin.Security.Facebook or whichever you want.

Note: Don't forget to add relevant namespaces to your files:

using Microsoft.AspNet.Identity;
using Microsoft.Owin.Security;
using Microsoft.AspNet.Identity.Owin;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.Owin;
using Microsoft.Owin.Security.Cookies;
using Owin;

You could also see my other answers like this and this for advanced use of Identity.

How to remove foreign key constraint in sql server?

alter table <referenced_table_name> drop  primary key;

Foreign key constraint will be removed.

How to set time zone of a java.util.Date?

Here you be able to get date like "2020-03-11T20:16:17" and return "11/Mar/2020 - 20:16"

 private String transformLocalDateTimeBrazillianUTC(String dateJson) throws  ParseException {
    String localDateTimeFormat = "yyyy-MM-dd'T'HH:mm:ss";
    SimpleDateFormat formatInput = new SimpleDateFormat(localDateTimeFormat);

    //Here is will set the time zone

    String brazilianFormat = "dd/MMM/yyyy - HH:mm";
    SimpleDateFormat formatOutput = new SimpleDateFormat(brazilianFormat);
    Date date = formatInput.parse(dateJson);
    return formatOutput.format(date);

How do I print a datetime in the local timezone?

I use this function datetime_to_local_timezone(), which seems overly convoluted but I found no simpler version of a function that converts a datetime instance to the local time zone, as configured in the operating system, with the UTC offset that was in effect at that time:

import time, datetime

def datetime_to_local_timezone(dt):
    epoch = dt.timestamp() # Get POSIX timestamp of the specified datetime.
    st_time = time.localtime(epoch) #  Get struct_time for the timestamp. This will be created using the system's locale and it's time zone information.
    tz = datetime.timezone(datetime.timedelta(seconds = st_time.tm_gmtoff)) # Create a timezone object with the computed offset in the struct_time.

    return dt.astimezone(tz) # Move the datetime instance to the new time zone.

utc = datetime.timezone(datetime.timedelta())
dt1 = datetime.datetime(2009, 7, 10, 18, 44, 59, 193982, utc) # DST was in effect
dt2 = datetime.datetime(2009, 1, 10, 18, 44, 59, 193982, utc) # DST was not in effect



This example prints four dates. For two moments in time, one in January and one in July 2009, each, it prints the timestamp once in UTC and once in the local time zone. Here, where CET (UTC+01:00) is used in the winter and CEST (UTC+02:00) is used in the summer, it prints the following:

2009-07-10 18:44:59.193982+00:00
2009-07-10 20:44:59.193982+02:00

2009-01-10 18:44:59.193982+00:00
2009-01-10 19:44:59.193982+01:00

jQuery addClass onClick

Try to make your css more specific so that the new (green) style is more specific than the previous one, so that it worked for me!

For example, you might use in css:

button:active {/*your style here*/}

Instead of (probably not working):

.active {/*style*/} (.active is not a pseudo-class)

Hope it helps!

Can I add an image to an ASP.NET button?

Assuming a Css class of "image" :

input.image { 
  background: url(/i/bg.png) no-repeat top left; 
  width: /* img-width */; 
  height: /* img-height */ 

If you don't know what the image width and height are, you can set this dynamically with javascript.

HTML: Is it possible to have a FORM tag in each TABLE ROW in a XHTML valid way?

You just have to put the <form ... > tag before the <table> tag and the </form> at the end.

Hopte it helps.

Get multiple elements by Id

Below is the work around to submit Multi values, in case of converting the application from ASP to PHP

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<script language="javascript">
function SetValuesOfSameElements() {
    var Arr_Elements = [];
    Arr_Elements = document.getElementsByClassName("MultiElements");

    for(var i=0; i<Arr_Elements.length; i++) {
        Arr_Elements[i].value = '';
        var Element_Name = Arr_Elements[i].name;
        var Main_Element_Type = Arr_Elements[i].getAttribute("MainElementType");
        var Multi_Elements = [];
        Multi_Elements = document.getElementsByName(Element_Name);
        var Multi_Elements_Values = '';
//alert(Element_Name + " > " + Main_Element_Type + " > " + Multi_Elements_Values);
        if (Main_Element_Type == "CheckBox") {
            for(var j=0; j<Multi_Elements.length; j++) {
                if (Multi_Elements[j].checked == true) {
                    if (Multi_Elements_Values == '') {
                        Multi_Elements_Values = Multi_Elements[j].value;
                    else {
                        Multi_Elements_Values += ', '+ Multi_Elements[j].value;
        if (Main_Element_Type == "Hidden" || Main_Element_Type == "TextBox") {
            for(var j=0; j<Multi_Elements.length; j++) {
                if (Multi_Elements_Values == '') {
                    Multi_Elements_Values = Multi_Elements[j].value;
                else {
                    if (Multi_Elements[j].value != '') {
                        Multi_Elements_Values += ', '+ Multi_Elements[j].value;
        Arr_Elements[i].value = Multi_Elements_Values;

<form name="Training" action="TestCB.php" method="get" onsubmit="SetValuesOfSameElements()"/>

            <td>Check Box</td>
                <input type="CheckBox" name="TestCB" id="TestCB" value="123">123</input>
                <input type="CheckBox" name="TestCB" id="TestCB" value="234">234</input>
                <input type="CheckBox" name="TestCB" id="TestCB" value="345">345</input>
                <input type="hidden" name="SdPart" id="SdPart" value="1231"></input>
                <input type="hidden" name="SdPart" id="SdPart" value="2341"></input>
                <input type="hidden" name="SdPart" id="SdPart" value="3451"></input>

                <input type="textbox" name="Test11" id="Test11" value="345111"></input>

                <!-- Define hidden Elements with Class name 'MultiElements' for all the Form Elements that used the Same Name (Check Boxes, Multi Select, Text Elements with the Same Name, Hidden Elements with the Same Name, etc 
                <input type="hidden" MainElementType="CheckBox" name="TestCB" class="MultiElements" value=""></input>
                <input type="hidden" MainElementType="Hidden" name="SdPart" class="MultiElements" value=""></input>
                <input type="hidden" MainElementType="TextBox" name="Test11" class="MultiElements" value=""></input>
            <td colspan="2">
                <input type="Submit" name="Submit" id="Submit" value="Submit" />



echo $_GET["TestCB"];
echo "<br/>";
echo $_GET["SdPart"];
echo "<br/>";
echo $_GET["Test11"];

How to draw border on just one side of a linear layout?

Easy as pie, allowing a transparent bg:

<?xml version="1.0" encoding="utf-8"?>
<shape xmlns:android=""
        android:centerX="0.01" />

Change the angle to change border location:

  • 0 = left
  • 90 = bottom
  • 180 = right
  • 270 = top

Returning JSON object as response in Spring Boot

You can either return a response as String as suggested by @vagaasen or you can use ResponseEntity Object provided by Spring as below. By this way you can also return Http status code which is more helpful in webservice call.

public class MyRestController

    @GetMapping(path = "/hello", produces=MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<Object> sayHello()
         //Get data from service layer into entityList.

        List<JSONObject> entities = new ArrayList<JSONObject>();
        for (Entity n : entityList) {
            JSONObject entity = new JSONObject();
            entity.put("aa", "bb");
        return new ResponseEntity<Object>(entities, HttpStatus.OK);

git checkout all the files

If you want to checkout all the files 'anywhere'

git checkout -- $(git rev-parse --show-toplevel)

jQuery - find child with a specific class

I'm not sure if I understand your question properly, but it shouldn't matter if this div is a child of some other div. You can simply get text from all divs with class bgHeaderH2 by using following code:


How to make a round button?

For a round button create a shape:

<?xml version="1.0" encoding="utf-8"?>

    android:color="#FFFFFF" />

<solid android:color="#ffee82ee" />

    android:topRightRadius="45dp" />

use it as a background of your button link

Read file from line 2 or skip header row

# Open a connection to the file
with open('world_dev_ind.csv') as file:

    # Skip the column names

    # Initialize an empty dictionary: counts_dict
    counts_dict = {}

    # Process only the first 1000 rows
    for j in range(0, 1000):

        # Split the current line into a list: line
        line = file.readline().split(',')

        # Get the value for the first column: first_col
        first_col = line[0]

        # If the column value is in the dict, increment its value
        if first_col in counts_dict.keys():
            counts_dict[first_col] += 1

        # Else, add to the dict and set value to 1
            counts_dict[first_col] = 1

# Print the resulting dictionary

Sql script to find invalid email addresses

SELECT Email FROM Employee WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

Wildcards in a Windows hosts file

You can use a dynamic DNS client such as Then, with an external DNS server CNAME * to

change figure size and figure format in matplotlib

You can change the size of the plot by adding this before you create the figure.

plt.rcParams["figure.figsize"] = [16,9]

Python Database connection Close

According to pyodbc documentation, connections to the SQL server are not closed by default. Some database drivers do not close connections when close() is called in order to save round-trips to the server.

To close your connection when you call close() you should set pooling to False:

import pyodbc

pyodbc.pooling = False

How to stop creating .DS_Store on Mac?

Its is possible by using mach_inject. Take a look at Death to .DS_Store

I found that overriding HFSPlusPropertyStore::FlushChanges() with a function that simply did nothing, successfully prevented the creation of .DS_Store files on both Snow Leopard and Lion.

DeathToDSStore source code

NOTE: On 10.11 you can not inject code into system apps.

How to add an image to the emulator gallery in android studio?

Although you can have logat on a real device too, if you need to use an emulator try transferring the images through the Android Device Monitor, accessible from the toolbar in Android Studio (it's in eclipse too, of course).

Once you select the device from ADM, you can see the folders tree and copy things inside

How do I get a TextBox to only accept numeric input in WPF?


Private Sub DetailTextBox_PreviewTextInput( _
  ByVal sender As Object, _
  ByVal e As System.Windows.Input.TextCompositionEventArgs) _
  Handles DetailTextBox.PreviewTextInput

    If _IsANumber Then
        If Not Char.IsNumber(e.Text) Then
            e.Handled = True
        End If
    End If
End Sub

What are pipe and tap methods in Angular tutorial?

You are right, the documentation lacks of those methods. However when I dug into rxjs repository, I found nice comments about tap (too long to paste here) and pipe operators:

   * Used to stitch together functional operators into a chain.
   * @method pipe
   * @return {Observable} the Observable result of all of the operators having
   * been called in the order they were passed in.
   * @example
   * import { map, filter, scan } from 'rxjs/operators';
   * Rx.Observable.interval(1000)
   *   .pipe(
   *     filter(x => x % 2 === 0),
   *     map(x => x + x),
   *     scan((acc, x) => acc + x)
   *   )
   *   .subscribe(x => console.log(x))

In brief:

Pipe: Used to stitch together functional operators into a chain. Before we could just do observable.filter().map().scan(), but since every RxJS operator is a standalone function rather than an Observable's method, we need pipe() to make a chain of those operators (see example above).

Tap: Can perform side effects with observed data but does not modify the stream in any way. Formerly called do(). You can think of it as if observable was an array over time, then tap() would be an equivalent to Array.forEach().

mySQL convert varchar to date

As gratitude to the timely help I got from here - a minor update to above.

$query = "UPDATE `db`.`table` SET `fieldname`=  str_to_date(  fieldname, '%d/%m/%Y')";

Iterating over every property of an object in javascript using Prototype?

There's no need for Prototype here: JavaScript has loops. If you're not sure that no one messed with Object.prototype, check hasOwnProperty() as well, ie

for(var prop in obj) {

How do I list all tables in all databases in SQL Server in a single result set?

I realize this is a very old thread, but it was very helpful when I had to put together some system documentation for several different servers that were hosting different versions of Sql Server. I ended up creating 4 stored procedures which I am posting here for the benefit of the community. We use Dynamics NAV so the two stored procedures with NAV in the name split the Nav company out of the table name. Enjoy...

4 of 4 - ListServerDatabaseNavTables - for Dynamics NAV

USE [YourDatabase]


ALTER proc [dbo].[ListServerDatabaseNavTables]
    @SearchDatabases varchar(max) = NULL,  
    @SearchSchema sysname = NULL,
    @SearchCompanies varchar(max) = NULL,
    @SearchTables varchar(max) = NULL,
    @ExcludeSystemDatabases bit = 1,
    @Sql varchar(max) OUTPUT

* Lists all of the database tables for a given server.
*   Parameters
*       SearchDatabases - Comma delimited list of database names for which to search - converted into series of Like statements
*                         Defaults to null  
*       SearchSchema - Schema name for which to search
*                      Defaults to null 
*       SearchCompanies - Comma delimited list of company names for which to search - converted into series of Like statements
*                         Defaults to null  
*       SearchTables - Comma delimited list of table names for which to search - converted into series of Like statements
*                      Defaults to null 
*       ExcludeSystemDatabases - 1 to exclude system databases, otherwise 0
*                          Defaults to 1
*       Sql - Output - the stored proc generated sql
*   Adapted from answer by KM answered May 21 '10 at 13:33
*   From: How do I list all tables in all databases in SQL Server in a single result set?
*   Link:


    DECLARE @l_CompoundLikeStatement varchar(max) = ''
    DECLARE @l_TableName sysname
    DECLARE @l_CompanyName sysname
    DECLARE @l_DatabaseName sysname

    DECLARE @l_Index int

    DECLARE @l_UseAndText bit = 0

    DECLARE @AllTables table (ServerName sysname, DbName sysname, SchemaName sysname, CompanyName sysname, TableName sysname, NavTableName sysname)

    SET @Sql = 
        'select @@ServerName as ''ServerName'', ''?'' as ''DbName'', as ''SchemaName'', ' + char(13) +
        '       case when charindex(''$'', = 0 then '''' else left(, charindex(''$'', - 1) end as ''CompanyName'', ' + char(13) +
        '       case when charindex(''$'', = 0 then else substring(, charindex(''$'', + 1, 1000) end as ''TableName'', ' + char(13) +
        ' as ''NavTableName'' ' + char(13) +
        'from [?].sys.tables t inner join ' + char(13) + 
        '     sys.schemas s on t.schema_id = s.schema_id '

    -- Comma delimited list of database names for which to search
    IF @SearchDatabases IS NOT NULL BEGIN
        SET @l_CompoundLikeStatement = char(13) + 'where (' + char(13)
        WHILE LEN(LTRIM(RTRIM(@SearchDatabases))) > 0 BEGIN
            SET @l_Index = CHARINDEX(',', @SearchDatabases)
            IF @l_Index = 0 BEGIN
                SET @l_DatabaseName = LTRIM(RTRIM(@SearchDatabases))
            END ELSE BEGIN
                SET @l_DatabaseName = LTRIM(RTRIM(LEFT(@SearchDatabases, @l_Index - 1)))

            SET @SearchDatabases = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchDatabases, @l_DatabaseName, ''))), ',', '')))
            SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' ''?'' like ''' + @l_DatabaseName + '%'' COLLATE Latin1_General_CI_AS or '

        -- Trim trailing Or and add closing right parenthesis )
        SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement))
        SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ')'

        SET @Sql = @Sql + char(13) +

        SET @l_UseAndText = 1

    -- Search schema
    IF @SearchSchema IS NOT NULL BEGIN
        SET @Sql = @Sql + char(13)
        SET @Sql = @Sql + CASE WHEN @l_UseAndText = 1 THEN '  and ' ELSE 'where ' END +
            ' LIKE ''' + @SearchSchema + ''' COLLATE Latin1_General_CI_AS'
        SET @l_UseAndText = 1

    -- Comma delimited list of company names for which to search
    IF @SearchCompanies IS NOT NULL BEGIN
        SET @l_CompoundLikeStatement = char(13) + CASE WHEN @l_UseAndText = 1 THEN '  and (' ELSE 'where (' END + char(13) 
        WHILE LEN(LTRIM(RTRIM(@SearchCompanies))) > 0 BEGIN
            SET @l_Index = CHARINDEX(',', @SearchCompanies)
            IF @l_Index = 0 BEGIN
                SET @l_CompanyName = LTRIM(RTRIM(@SearchCompanies))
            END ELSE BEGIN
                SET @l_CompanyName = LTRIM(RTRIM(LEFT(@SearchCompanies, @l_Index - 1)))

            SET @SearchCompanies = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchCompanies, @l_CompanyName, ''))), ',', '')))
            SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' like ''' + @l_CompanyName + '%'' COLLATE Latin1_General_CI_AS or '

        -- Trim trailing Or and add closing right parenthesis )
        SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement))
        SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )'

        SET @Sql = @Sql + char(13) +

        SET @l_UseAndText = 1

    -- Comma delimited list of table names for which to search
    IF @SearchTables IS NOT NULL BEGIN
        SET @l_CompoundLikeStatement = char(13) + CASE WHEN @l_UseAndText = 1 THEN '  and (' ELSE 'where (' END + char(13) 
        WHILE LEN(LTRIM(RTRIM(@SearchTables))) > 0 BEGIN
            SET @l_Index = CHARINDEX(',', @SearchTables)
            IF @l_Index = 0 BEGIN
                SET @l_TableName = LTRIM(RTRIM(@SearchTables))
            END ELSE BEGIN
                SET @l_TableName = LTRIM(RTRIM(LEFT(@SearchTables, @l_Index - 1)))

            SET @SearchTables = LTRIM(RTRIM(REPLACE(LTRIM(RTRIM(REPLACE(@SearchTables, @l_TableName, ''))), ',', '')))
            SET @l_CompoundLikeStatement = @l_CompoundLikeStatement + char(13) + ' like ''$' + @l_TableName + ''' COLLATE Latin1_General_CI_AS or '

        -- Trim trailing Or and add closing right parenthesis )
        SET @l_CompoundLikeStatement = LTRIM(RTRIM(@l_CompoundLikeStatement))
        SET @l_CompoundLikeStatement = LEFT(@l_CompoundLikeStatement, LEN(@l_CompoundLikeStatement) - 2) + ' )'

        SET @Sql = @Sql + char(13) +

        SET @l_UseAndText = 1

    IF @ExcludeSystemDatabases = 1 BEGIN
        SET @Sql = @Sql + char(13)
        SET @Sql = @Sql + case when @l_UseAndText = 1 THEN '  and ' ELSE 'where ' END +
            '''?'' not in (''master'' COLLATE Latin1_General_CI_AS, ''model'' COLLATE Latin1_General_CI_AS, ''msdb'' COLLATE Latin1_General_CI_AS, ''tempdb'' COLLATE Latin1_General_CI_AS)' 

/*  PRINT @Sql  */

    INSERT INTO @AllTables 
    EXEC sp_msforeachdb @Sql

    SELECT * FROM @AllTables ORDER BY DbName COLLATE Latin1_General_CI_AS, CompanyName COLLATE Latin1_General_CI_AS, TableName COLLATE Latin1_General_CI_AS

UUID max character length

Most databases have a native UUID type these days to make working with them easier. If yours doesn't, they're just 128-bit numbers, so you can use BINARY(16), and if you need the text format frequently, e.g. for troubleshooting, then add a calculated column to generate it automatically from the binary column. There is no good reason to store the (much larger) text form.

Ping site and return result in PHP

function urlExists($url=NULL)  
    if($url == NULL) return false;  
    $ch = curl_init($url);  
    curl_setopt($ch, CURLOPT_TIMEOUT, 5);  
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);  
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);  
    $data = curl_exec($ch);  
    $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);  
    if($httpcode>=200 && $httpcode<300){  
        return true;  
    } else {  
        return false;  

This was grabbed from this post on how to check if a URL exists. Because Twitter should provide an error message above 300 when it is in maintenance, or a 404, this should work perfectly.

Python function as a function argument?

Functions in Python are first-class objects. But your function definition is a bit off.

def myfunc(anotherfunc, extraArgs, extraKwArgs):
  return anotherfunc(*extraArgs, **extraKwArgs)

Keyboard shortcut to comment lines in Sublime Text 3

Make sure the file is a recognized type. I had a yaml file open (without the .yaml file extension) and Sublime Text recognized it as Plain Text. Plain Text has no comment method. Switching the file type to YAML made the comment shortcut work.

How to create unique keys for React elements?

Keys helps React identify which items have changed/added/removed and should be given to the elements inside the array to give the elements a stable identity.

With that in mind, there are basically three different strategies as described bellow:

  1. Static Elements (when you don't need to keep html state (focus, cursor position, etc)
  2. Editable and sortable elements
  3. Editable but not sortable elements

As React Documentation explains, we need to give stable identity to the elements and because of that, carefully choose the strategy that best suits your needs:


As we can see also in React Documentation, is not recommended the use of index for keys "if the order of items may change. This can negatively impact performance and may cause issues with component state".

In case of static elements like tables, lists, etc, I recommend using a tool called shortid.

1) Install the package using NPM/YARN:

npm install shortid --save

2) Import in the class file you want to use it:

import shortid from 'shortid';

2) The command to generate a new id is shortid.generate().

3) Example:

  renderDropdownItems = (): React.ReactNode => {
    const { data, isDisabled } = this.props;
    const { selectedValue } = this.state;
    const dropdownItems: Array<React.ReactNode> = [];

    if (data) {
      data.forEach(item => {
          <option value={item.value} key={shortid.generate()}>

    return (

IMPORTANT: As React Virtual DOM relies on the key, with shortid every time the element is re-rendered a new key will be created and the element will loose it's html state like focus or cursor position. Consider this when deciding how the key will be generated as the strategy above can be useful only when you are building elements that won't have their values changed like lists or read only fields.


If the element is sortable and you have a unique ID of the item, combine it with some extra string (in case you need to have the same information twice in a page). This is the most recommended scenario.


  renderDropdownItems = (): React.ReactNode => {
    const elementKey:string = 'ddownitem_'; 
    const { data, isDisabled } = this.props;
    const { selectedValue } = this.state;
    const dropdownItems: Array<React.ReactNode> = [];

    if (data) {
      data.forEach(item => {
          <option value={item.value} key={${elementKey}${}}>

    return (


As a last resort, for editable (but non sortable) fields like input, you can use some the index with some starting text as element key cannot be duplicated.


  renderDropdownItems = (): React.ReactNode => {
    const elementKey:string = 'ddownitem_'; 
    const { data, isDisabled } = this.props;
    const { selectedValue } = this.state;
    const dropdownItems: Array<React.ReactNode> = [];

    if (data) {
      data.forEach((item:any index:number) => {
          <option value={item.value} key={${elementKey}${index}}>

    return (

Hope this helps.

What is the easiest way to remove all packages installed by pip?

First, add all package to requirements.txt

pip freeze > requirements.txt

Then remove all

pip uninstall -y -r requirements.txt 

JavaScript query string

You can extract the key/value pairs from the property, this property has the part of the URL that follows the ? symbol, including the ? symbol.

function getQueryString() {
  var result = {}, queryString =,
      re = /([^&=]+)=([^&]*)/g, m;

  while (m = re.exec(queryString)) {
    result[decodeURIComponent(m[1])] = decodeURIComponent(m[2]);

  return result;

// ...
var myParam = getQueryString()["myParam"];

How to find length of dictionary values

Sure. In this case, you'd just do:

length_key = len(d['key'])  # length of the list stored at `'key'` ...

It's hard to say why you actually want this, but, perhaps it would be useful to create another dict that maps the keys to the length of values:

length_dict = {key: len(value) for key, value in d.items()}
length_key = length_dict['key']  # length of the list stored at `'key'` ...

How to enable CORS in apache tomcat

Just to add a bit of extra info over the right solution. Be aware that you'll need this class org.apache.catalina.filters.CorsFilter. So in order to have it, if your tomcat is not 7.0.41 or higher, download 'tomcat-catalina.7.0.41.jar' or higher ( you can do it from ) and put it in the 'lib' folder inside Tomcat installation folders. I actually used 7.0.42 Hope it helps!

php check if array contains all array values from another array

How about this:

function array_keys_exist($searchForKeys = array(), $searchableArray) {
    $searchableArrayKeys = array_keys($searchableArray);

    return count(array_intersect($searchForKeys, $searchableArrayKeys)) == count($searchForKeys); 

How to append data to a json file?

Append entry to the file contents if file exists, otherwise append the entry to an empty list and write in in the file:

a = []
if not os.path.isfile(fname):
    with open(fname, mode='w') as f:
        f.write(json.dumps(a, indent=2))
    with open(fname) as feedsjson:
        feeds = json.load(feedsjson)

    with open(fname, mode='w') as f:
        f.write(json.dumps(feeds, indent=2))

How can I have Github on my own server?

Yes, there's GitHub Enterprise :)

Only problem is it's a bit pricey :(

Initialize array of strings

This example program illustrates initialization of an array of C strings.

#include <stdio.h>

const char * array[] = {
    "First entry",
    "Second entry",
    "Third entry",

#define n_array (sizeof (array) / sizeof (const char *))

int main ()
    int i;

    for (i = 0; i < n_array; i++) {
        printf ("%d: %s\n", i, array[i]);
    return 0;

It prints out the following:

0: First entry
1: Second entry
2: Third entry

Differences between ConstraintLayout and RelativeLayout

A big difference is that ConstraintLayout respects constraints even if the view is gone. So it won't break the layout if you have a chain and you want to make a view disappear in the middle.

How can I create a dropdown menu from a List in Tkinter?

To create a "drop down menu" you can use OptionMenu in tkinter

Example of a basic OptionMenu:

from Tkinter import *

master = Tk()

variable = StringVar(master)
variable.set("one") # default value

w = OptionMenu(master, variable, "one", "two", "three")


More information (including the script above) can be found here.

Creating an OptionMenu of the months from a list would be as simple as:

from tkinter import *

] #etc

master = Tk()

variable = StringVar(master)
variable.set(OPTIONS[0]) # default value

w = OptionMenu(master, variable, *OPTIONS)


In order to retrieve the value the user has selected you can simply use a .get() on the variable that we assigned to the widget, in the below case this is variable:

from tkinter import *

] #etc

master = Tk()

variable = StringVar(master)
variable.set(OPTIONS[0]) # default value

w = OptionMenu(master, variable, *OPTIONS)

def ok():
    print ("value is:" + variable.get())

button = Button(master, text="OK", command=ok)


I would highly recommend reading through this site for further basic tkinter information as the above examples are modified from that site.

ByRef argument type mismatch in Excel VBA

I don't know why, but it is very important to declare the variables separately if you want to pass variables (as variables) into other procedure or function.

For example there is a procedure which make some manipulation with data: based on ID returns Part Number and Quantity information. ID as constant value, other two arguments are variables.

Public Sub GetPNQty(ByVal ID As String, PartNumber As String, Quantity As Long)

the next main code gives me a "ByRef argument mismatch":

Sub KittingScan()  
Dim BoxPN As String
Dim BoxQty, BoxKitQty As Long

  Call GetPNQty(InputBox("Enter ID:"), BoxPN, BoxQty) 

End sub

and the next one is working as well:

Sub KittingScan()
Dim BoxPN As String
Dim BoxQty As Long
Dim BoxKitQty As Long

  Call GetPNQty(InputBox("Enter ID:"), BoxPN, BoxQty)

End sub

phpMyAdmin allow remote users

My answer is based on getting a 403 error although I had all of the Apache settings mentioned in the other answers correct.

It was a fresh Centos 7 server and it turned out that the issue was not the Apache settings but the fact that the PhpMyAdmin did not serve at all. The solution was to install php and add the php directive to apache.conf:

  • sudo yum install php php-mysql
  • vim /etc/httpd/conf/httpd.conf add something like
  • DirectoryIndex index.php index.phtml index.html index.htm to serve php index files also and then restart apache

Don't forget to restart Apache server to take effect - systemctl restart httpd.service

I hope this helps. I first thought my issue was Apache directives, so I post my solution here.

How can I disable the UITableView selection?

You Can also set the background color to Clear to achieve the same effect as UITableViewCellSelectionStyleNone, in case you don't want to/ can't use UITableViewCellSelectionStyleNone.

You would use code like the following:

UIView *backgroundColorView = [[UIView alloc] init];
backgroundColorView.backgroundColor = [UIColor clearColor];
backgroundColorView.layer.masksToBounds = YES;
[cell setSelectedBackgroundView: backgroundColorView];

This may degrade your performance as your adding an extra colored view to each cell.

no module named urllib.parse (How should I install it?)

With the information you have provided, your best bet will be to use Python 3.x.

Your error suggests that the code may have been written for Python 3 given that it is trying to import urllib.parse. If you've written the software and have control over its source code, you should change the import to:

from urlparse import urlparse

urllib was split into urllib.parse, urllib.request, and urllib.error in Python 3.

I suggest that you take a quick look at software collections in CentOS if you are not able to change the imports for some reason. You can bring in Python 3.3 like this:

  1. yum install centos­-release­-SCL
  2. yum install python33
  3. scl enable python33

Check this page out for more info on SCLs

Blank HTML SELECT without blank item in dropdown list

You can't. They simply do not work that way. A drop down menu must have one of its options selected at all times.

You could (although I don't recommend it) watch for a change event and then use JS to delete the first option if it is blank.

How to filter Pandas dataframe using 'in' and 'not in' like in SQL

I wanted to filter out dfbc rows that had a BUSINESS_ID that was also in the BUSINESS_ID of dfProfilesBusIds

dfbc = dfbc[~dfbc['BUSINESS_ID'].isin(dfProfilesBusIds['BUSINESS_ID'])]

Getting a better understanding of callback functions in JavaScript

Callbacks are about signals and "new" is about creating object instances.

In this case it would be even more appropriate to execute just "callback();" than "return new callback()" because you aren't doing anything with a return value anyway.

(And the arguments.length==3 test is really clunky, fwiw, better to check that callback param exists and is a function.)

What does <? php echo ("<pre>"); ..... echo("</pre>"); ?> mean?

$testArray = [
  "name"   => "Dinesh Madusanka",
  "gender" => "male"
  "name"   => "Tharaka Devinda",
  "gender" => "male"
  "name"   => "Dumidu Ranasinghearachchi",
  "gender" => "male"



  echo "<pre>";

How do I retrieve an HTML element's actual width and height?

It is easy to modify the elements styles but kinda tricky to read the value.

JavaScript can't read any element style property ( coming from css(internal/external) unless you use the built in method call getComputedStyle in javascript.

getComputedStyle(element[, pseudo])

Element: The element to read the value for.
pseudo: A pseudo-element if required, for instance ::before. An empty string or no argument means the element itself.

The result is an object with style properties, like, but now with respect to all css classes.

For instance, here style doesn’t see the margin:

  <style> body { color: red; margin: 5px } </style>

    let computedStyle = getComputedStyle(document.body);

    // now we can read the margin and the color from it

    alert( computedStyle.marginTop ); // 5px
    alert( computedStyle.color ); // rgb(255, 0, 0)


So modified your javaScript code to include the getComputedStyle of the element you wish to get it's width/height or other attribute

window.onload = function() {

    var test = document.getElementById("test");
    test.addEventListener("click", select);

    function select(e) {                                  
        var elementID =;
        var element = document.getElementById(elementID);
        let computedStyle = getComputedStyle(element);
        var width = computedStyle.width;


Computed and resolved values

There are two concepts in CSS:

A computed style value is the value after all CSS rules and CSS inheritance is applied, as the result of the CSS cascade. It can look like height:1em or font-size:125%.

A resolved style value is the one finally applied to the element. Values like 1em or 125% are relative. The browser takes the computed value and makes all units fixed and absolute, for instance: height:20px or font-size:16px. For geometry properties resolved values may have a floating point, like width:50.5px.

A long time ago getComputedStyle was created to get computed values, but it turned out that resolved values are much more convenient, and the standard changed.
So nowadays getComputedStyle actually returns the resolved value of the property.

Please Note:

getComputedStyle requires the full property name

You should always ask for the exact property that you want, like paddingLeft or height or width. Otherwise the correct result is not guaranteed.

For instance, if there are properties paddingLeft/paddingTop, then what should we get for getComputedStyle(elem).padding? Nothing, or maybe a “generated” value from known paddings? There’s no standard rule here.

There are other inconsistencies. As an example, some browsers (Chrome) show 10px in the document below, and some of them (Firefox) – do not:

  body {
    margin: 30px;
    height: 900px;
  let style = getComputedStyle(document.body);
  alert(style.margin); // empty string in Firefox

for more information

String formatting in Python 3

Python 3.6 now supports shorthand literal string interpolation with PEP 498. For your use case, the new syntax is simply:

f"({self.goals} goals, ${self.penalties})"

This is similar to the previous .format standard, but lets one easily do things like:

>>> width = 10
>>> precision = 4
>>> value = decimal.Decimal('12.34567')
>>> f'result: {value:{width}.{precision}}'
'result:      12.35'

Error: [$injector:unpr] Unknown provider: $routeProvider

It looks like you forgot to include the ngRoute module in your dependency for myApp.

In Angular 1.2, they've made ngRoute optional (so you can use third-party route providers, etc.) and you have to explicitly depend on it in modules, along with including the separate file.

'use strict';

angular.module('myApp', ['ngRoute']).
    config(['$routeProvider', function($routeProvider) {
$routeProvider.otherwise({redirectTo: '/home'});

Difference between jar and war in Java

WAR stands for Web application ARchive

JAR stands for Java ARchive

enter image description here

Linux command for extracting war file?

Using unzip

unzip -c whatever.war META-INF/MANIFEST.MF  

It will print the output in terminal.

And for extracting all the files,

 unzip whatever.war

Using jar

jar xvf test.war

Note! The jar command will extract war contents to current directory. Not to a subdirectory (like Tomcat does).

ObservableCollection not noticing when Item in it changes (even with INotifyPropertyChanged)

I've put together what I hope is a pretty robust solution, including some of the techniques in other answers. It is a new class derived from ObservableCollection<>, which I'm calling FullyObservableCollection<>

It has the following features:

  • It adds a new event, ItemPropertyChanged. I've deliberately kept this separate from the existing CollectionChanged:
    • To aid backward compatibility.
    • So more relevant detail can be given in the new ItemPropertyChangedEventArgs that accompanies it: the original PropertyChangedEventArgs and the index within the collection.
  • It replicates all the constructors from ObservableCollection<>.
  • It correctly handles the list being reset (ObservableCollection<>.Clear()), avoiding a possible memory leak.
  • It overrides the base class's OnCollectionChanged(), rather than a more resource-intensive subscription to the CollectionChanged event.


The complete .cs file follows. Note that a few features of C# 6 have been used, but it should be fairly simple to backport it:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.ComponentModel;

namespace Utilities
    public class FullyObservableCollection<T> : ObservableCollection<T>
        where T : INotifyPropertyChanged
        /// <summary>
        /// Occurs when a property is changed within an item.
        /// </summary>
        public event EventHandler<ItemPropertyChangedEventArgs> ItemPropertyChanged;

        public FullyObservableCollection() : base()
        { }

        public FullyObservableCollection(List<T> list) : base(list)

        public FullyObservableCollection(IEnumerable<T> enumerable) : base(enumerable)

        protected override void OnCollectionChanged(NotifyCollectionChangedEventArgs e)
            if (e.Action == NotifyCollectionChangedAction.Remove ||
                e.Action == NotifyCollectionChangedAction.Replace)
                foreach (T item in e.OldItems)
                    item.PropertyChanged -= ChildPropertyChanged;

            if (e.Action == NotifyCollectionChangedAction.Add ||
                e.Action == NotifyCollectionChangedAction.Replace)
                foreach (T item in e.NewItems)
                    item.PropertyChanged += ChildPropertyChanged;


        protected void OnItemPropertyChanged(ItemPropertyChangedEventArgs e)
            ItemPropertyChanged?.Invoke(this, e);

        protected void OnItemPropertyChanged(int index, PropertyChangedEventArgs e)
            OnItemPropertyChanged(new ItemPropertyChangedEventArgs(index, e));

        protected override void ClearItems()
            foreach (T item in Items)
                item.PropertyChanged -= ChildPropertyChanged;


        private void ObserveAll()
            foreach (T item in Items)
                item.PropertyChanged += ChildPropertyChanged;

        private void ChildPropertyChanged(object sender, PropertyChangedEventArgs e)
            T typedSender = (T)sender;
            int i = Items.IndexOf(typedSender);

            if (i < 0)
                throw new ArgumentException("Received property notification from item not in collection");

            OnItemPropertyChanged(i, e);

    /// <summary>
    /// Provides data for the <see cref="FullyObservableCollection{T}.ItemPropertyChanged"/> event.
    /// </summary>
    public class ItemPropertyChangedEventArgs : PropertyChangedEventArgs
        /// <summary>
        /// Gets the index in the collection for which the property change has occurred.
        /// </summary>
        /// <value>
        /// Index in parent collection.
        /// </value>
        public int CollectionIndex { get; }

        /// <summary>
        /// Initializes a new instance of the <see cref="ItemPropertyChangedEventArgs"/> class.
        /// </summary>
        /// <param name="index">The index in the collection of changed item.</param>
        /// <param name="name">The name of the property that changed.</param>
        public ItemPropertyChangedEventArgs(int index, string name) : base(name)
            CollectionIndex = index;

        /// <summary>
        /// Initializes a new instance of the <see cref="ItemPropertyChangedEventArgs"/> class.
        /// </summary>
        /// <param name="index">The index.</param>
        /// <param name="args">The <see cref="PropertyChangedEventArgs"/> instance containing the event data.</param>
        public ItemPropertyChangedEventArgs(int index, PropertyChangedEventArgs args) : this(index, args.PropertyName)
        { }

NUnit Tests

So you can check changes you might make (and see what I tested in the first place!), I've also included my NUnit test class. Obviously, the following code is not necessary just to use FullyObservableCollection<T> in your project.

NB The test class uses BindableBase from PRISM to implement INotifyPropertyChanged. There is no dependency on PRISM from the main code.

using NUnit.Framework;
using Utilities;
using Microsoft.Practices.Prism.Mvvm;
using System.Collections.Specialized;
using System.Collections.Generic;

namespace Test_Utilities
    public class Test_FullyObservableCollection : AssertionHelper
        public class NotifyingTestClass : BindableBase
            public int Id
                get { return _Id; }
                set { SetProperty(ref _Id, value); }
            private int _Id;

            public string Name
                get { return _Name; }
                set { SetProperty(ref _Name, value); }
            private string _Name;


        FullyObservableCollection<NotifyingTestClass> TestCollection;
        NotifyingTestClass Fred;
        NotifyingTestClass Betty;
        List<NotifyCollectionChangedEventArgs> CollectionEventList;
        List<ItemPropertyChangedEventArgs> ItemEventList;

        public void Init()
            Fred = new NotifyingTestClass() { Id = 1, Name = "Fred" };
            Betty = new NotifyingTestClass() { Id = 4, Name = "Betty" };

            TestCollection = new FullyObservableCollection<NotifyingTestClass>()
                    new NotifyingTestClass() {Id = 2, Name = "Barney" },
                    new NotifyingTestClass() {Id = 3, Name = "Wilma" }

            CollectionEventList = new List<NotifyCollectionChangedEventArgs>();
            ItemEventList = new List<ItemPropertyChangedEventArgs>();
            TestCollection.CollectionChanged += (o, e) => CollectionEventList.Add(e);
            TestCollection.ItemPropertyChanged += (o, e) => ItemEventList.Add(e);

        // Change existing member property: just ItemPropertyChanged(IPC) should fire
        public void DetectMemberPropertyChange()
            TestCollection[0].Id = 7;

            Expect(CollectionEventList.Count, Is.EqualTo(0));

            Expect(ItemEventList.Count, Is.EqualTo(1), "IPC count");
            Expect(ItemEventList[0].PropertyName, Is.EqualTo(nameof(Fred.Id)), "Field Name");
            Expect(ItemEventList[0].CollectionIndex, Is.EqualTo(0), "Collection Index");

        // Add new member, change property: CollectionPropertyChanged (CPC) and IPC should fire
        public void DetectNewMemberPropertyChange()

            Expect(TestCollection.Count, Is.EqualTo(4));
            Expect(TestCollection[3].Name, Is.EqualTo("Betty"));

            Expect(ItemEventList.Count, Is.EqualTo(0), "Item Event count");

            Expect(CollectionEventList.Count, Is.EqualTo(1), "Collection Event count");
            Expect(CollectionEventList[0].Action, Is.EqualTo(NotifyCollectionChangedAction.Add), "Action (add)");
            Expect(CollectionEventList[0].OldItems, Is.Null, "OldItems count");
            Expect(CollectionEventList[0].NewItems.Count, Is.EqualTo(1), "NewItems count");
            Expect(CollectionEventList[0].NewItems[0], Is.EqualTo(Betty), "NewItems[0] dereference");

            CollectionEventList.Clear();      // Empty for next operation

            TestCollection[3].Id = 7;
            Expect(CollectionEventList.Count, Is.EqualTo(0), "Collection Event count");

            Expect(ItemEventList.Count, Is.EqualTo(1), "Item Event count");
            Expect(TestCollection[ItemEventList[0].CollectionIndex], Is.EqualTo(Betty), "Collection Index dereference");

        // Remove member, change property: CPC should fire for removel, neither CPC nor IPC should fire for change
        public void CeaseListentingWhenMemberRemoved()

            Expect(TestCollection.Count, Is.EqualTo(2));
            Expect(TestCollection.IndexOf(Fred), Is.Negative);

            Expect(ItemEventList.Count, Is.EqualTo(0), "Item Event count (pre change)");

            Expect(CollectionEventList.Count, Is.EqualTo(1), "Collection Event count (pre change)");
            Expect(CollectionEventList[0].Action, Is.EqualTo(NotifyCollectionChangedAction.Remove), "Action (remove)");
            Expect(CollectionEventList[0].OldItems.Count, Is.EqualTo(1), "OldItems count");
            Expect(CollectionEventList[0].NewItems, Is.Null, "NewItems count");
            Expect(CollectionEventList[0].OldItems[0], Is.EqualTo(Fred), "OldItems[0] dereference");

            CollectionEventList.Clear();      // Empty for next operation

            Fred.Id = 7;
            Expect(CollectionEventList.Count, Is.EqualTo(0), "Collection Event count (post change)");
            Expect(ItemEventList.Count, Is.EqualTo(0), "Item Event count (post change)");

        // Move member in list, change property: CPC should fire for move, IPC should fire for change
        public void MoveMember()
            TestCollection.Move(0, 1);

            Expect(TestCollection.Count, Is.EqualTo(3));
            Expect(TestCollection.IndexOf(Fred), Is.GreaterThan(0));

            Expect(ItemEventList.Count, Is.EqualTo(0), "Item Event count (pre change)");

            Expect(CollectionEventList.Count, Is.EqualTo(1), "Collection Event count (pre change)");
            Expect(CollectionEventList[0].Action, Is.EqualTo(NotifyCollectionChangedAction.Move), "Action (move)");
            Expect(CollectionEventList[0].OldItems.Count, Is.EqualTo(1), "OldItems count");
            Expect(CollectionEventList[0].NewItems.Count, Is.EqualTo(1), "NewItems count");
            Expect(CollectionEventList[0].OldItems[0], Is.EqualTo(Fred), "OldItems[0] dereference");
            Expect(CollectionEventList[0].NewItems[0], Is.EqualTo(Fred), "NewItems[0] dereference");

            CollectionEventList.Clear();      // Empty for next operation

            Fred.Id = 7;
            Expect(CollectionEventList.Count, Is.EqualTo(0), "Collection Event count (post change)");

            Expect(ItemEventList.Count, Is.EqualTo(1), "Item Event count (post change)");
            Expect(TestCollection[ItemEventList[0].CollectionIndex], Is.EqualTo(Fred), "Collection Index dereference");

        // Clear list, chnage property: only CPC should fire for clear and neither for property change
        public void ClearList()

            Expect(TestCollection.Count, Is.EqualTo(0));

            Expect(ItemEventList.Count, Is.EqualTo(0), "Item Event count (pre change)");

            Expect(CollectionEventList.Count, Is.EqualTo(1), "Collection Event count (pre change)");
            Expect(CollectionEventList[0].Action, Is.EqualTo(NotifyCollectionChangedAction.Reset), "Action (reset)");
            Expect(CollectionEventList[0].OldItems, Is.Null, "OldItems count");
            Expect(CollectionEventList[0].NewItems, Is.Null, "NewItems count");

            CollectionEventList.Clear();      // Empty for next operation

            Fred.Id = 7;
            Expect(CollectionEventList.Count, Is.EqualTo(0), "Collection Event count (post change)");
            Expect(ItemEventList.Count, Is.EqualTo(0), "Item Event count (post change)");

ElasticSearch, Sphinx, Lucene, Solr, Xapian. Which fits for which usage?

My sphinx.conf

source post_source 
    type = mysql

    sql_host = localhost
    sql_user = ***
    sql_pass = ***
    sql_db =   ***
    sql_port = 3306

    sql_query_pre = SET NAMES utf8
    # query before fetching rows to index

    sql_query = SELECT *, id AS pid, CRC32(safetag) as safetag_crc32 FROM hb_posts

    sql_attr_uint = pid  
    # pid (as 'sql_attr_uint') is necessary for sphinx
    # this field must be unique

    # that is why I like sphinx
    # you can store custom string fields into indexes (memory) as well
    sql_field_string = title
    sql_field_string = slug
    sql_field_string = content
    sql_field_string = tags

    sql_attr_uint = category
    # integer fields must be defined as sql_attr_uint

    sql_attr_timestamp = date
    # timestamp fields must be defined as sql_attr_timestamp

    sql_query_info_pre = SET NAMES utf8
    # if you need unicode support for sql_field_string, you need to patch the source
    # this param. is not supported natively

    sql_query_info = SELECT * FROM my_posts WHERE id = $id

index posts 
    source = post_source
    # source above

    path = /var/data/posts
    # index location

    charset_type = utf-8

Test script:


    require "sphinxapi.php";

    $safetag = $_GET["my_post_slug"];
//  $safetag = preg_replace("/[^a-z0-9\-_]/i", "", $safetag);

    $conf = getMyConf();

    $cl = New SphinxClient();

    $cl->SetServer($conf["server"], $conf["port"]);

    # set search params

    $cl->setLimits(0, 1, 1); 
    # looking for the post (not searching a keyword)

    $cl->SetFilter("safetag_crc32", array(crc32($safetag)));

    # fetch results
    $post = $cl->Query(null, "post_1");

    echo "<pre>";
    echo "</pre>";

Sample result:

[array] => 
  "id" => 123,
  "title" => "My post title.",
  "content" => "My <p>post</p> content.",
   [ and other fields ]

Sphinx query time:

0.001 sec.

Sphinx query time (1k concurrent):

=> 0.346 sec. (average)
=> 0.340 sec. (average of last 10 query)

MySQL query time:

"SELECT * FROM hb_posts WHERE id = 123;"
=> 0.001 sec.

MySQL query time (1k concurrent):

"SELECT * FROM my_posts WHERE id = 123;" 
=> 1.612 sec. (average)
=> 1.920 sec. (average of last 10 query)

How do I load an HTTP URL with App Transport Security enabled in iOS 9?

I have solved as plist file.

  1. Add a NSAppTransportSecurity : Dictionary.
  2. Add Subkey named " NSAllowsArbitraryLoads " as Boolean : YES

enter image description here

Delete all files in directory (but not directory) - one liner solution

Or to use this in Java 8:

try {
  Files.newDirectoryStream( directory ).forEach( file -> {
    try { Files.delete( file ); }
    catch ( IOException e ) { throw new UncheckedIOException(e); }
  } );
catch ( IOException e ) {

It's a pity the exception handling is so bulky, otherwise it would be a one-liner ...

numpy matrix vector multiplication

Simplest solution

Use or See the documentation here.

>>> a = np.array([[ 5, 1 ,3], 
                  [ 1, 1 ,1], 
                  [ 1, 2 ,1]])
>>> b = np.array([1, 2, 3])
>>> print
array([16, 6, 8])

This occurs because numpy arrays are not matrices, and the standard operations *, +, -, / work element-wise on arrays. Instead, you could try using numpy.matrix, and * will be treated like matrix multiplication.

Other Solutions

Also know there are other options:

  • As noted below, if using python3.5+ the @ operator works as you'd expect:

    >>> print(a @ b)
    array([16, 6, 8])
  • If you want overkill, you can use numpy.einsum. The documentation will give you a flavor for how it works, but honestly, I didn't fully understand how to use it until reading this answer and just playing around with it on my own.

    >>> np.einsum('ji,i->j', a, b)
    array([16, 6, 8])
  • As of mid 2016 (numpy 1.10.1), you can try the experimental numpy.matmul, which works like with two major exceptions: no scalar multiplication but it works with stacks of matrices.

    >>> np.matmul(a, b)
    array([16, 6, 8])
  • numpy.inner functions the same way as for matrix-vector multiplication but behaves differently for matrix-matrix and tensor multiplication (see Wikipedia regarding the differences between the inner product and dot product in general or see this SO answer regarding numpy's implementations).

    >>> np.inner(a, b)
    array([16, 6, 8])
    # Beware using for matrix-matrix multiplication though!
    >>> b = a.T
    >>>, b)
    array([[35,  9, 10],
           [ 9,  3,  4],
           [10,  4,  6]])
    >>> np.inner(a, b) 
    array([[29, 12, 19],
           [ 7,  4,  5],
           [ 8,  5,  6]])

Rarer options for edge cases

  • If you have tensors (arrays of dimension greater than or equal to one), you can use numpy.tensordot with the optional argument axes=1:

    >>> np.tensordot(a, b, axes=1)
    array([16,  6,  8])
  • Don't use numpy.vdot if you have a matrix of complex numbers, as the matrix will be flattened to a 1D array, then it will try to find the complex conjugate dot product between your flattened matrix and vector (which will fail due to a size mismatch n*m vs n).

Can I add jars to maven 2 build classpath without installing them?

After having really long discussion with CloudBees guys about properly maven packaging of such kind of JARs, they made an interesting good proposal for a solution:

Creation of a fake Maven project which attaches a pre-existing JAR as a primary artifact, running into belonged POM install:install-file execution. Here is an example of such kinf of POM:


But in order to implement it, existing project structure should be changed. First, you should have in mind that for each such kind of JAR there should be created different fake Maven project (module). And there should be created a parent Maven project including all sub-modules which are : all JAR wrappers and existing main project. The structure could be :

root project (this contains the parent POM file includes all sub-modules with module XML element) (POM packaging)

JAR 1 wrapper Maven child project (POM packaging)

JAR 2 wrapper Maven child project (POM packaging)

main existing Maven child project (WAR, JAR, EAR .... packaging)

When parent running via mvn:install or mvn:packaging is forced and sub-modules will be executed. That could be concerned as a minus here, since project structure should be changed, but offers a non static solution at the end

Why does overflow:hidden not work in a <td>?

I'm not familiar with the specific issue, but you could stick a div, etc inside the td and set overflow on that.

How do I auto size a UIScrollView to fit its content

You can get height of the content inside UIScrollView by calculate which child "reaches furthers". To calculate this you have to take in consideration origin Y (start) and item height.

float maxHeight = 0;
for (UIView *child in scrollView.subviews) {
    float childHeight = child.frame.origin.y + child.frame.size.height;
    //if child spans more than current maxHeight then make it a new maxHeight
    if (childHeight > maxHeight)
        maxHeight = childHeight;
//set content size
[scrollView setContentSize:(CGSizeMake(320, maxHeight))];

By doing things this way items (subviews) don't have to be stacked directly one under another.

How to draw vertical lines on a given plot in matplotlib

In addition to the plt.axvline and plt.plot((x1, x2), (y1, y2)) OR plt.plot([x1, x2], [y1, y2]) as provided in the answers above, one can also use

plt.vlines(x_pos, ymin=y1, ymax=y2)

to plot a vertical line at x_pos spanning from y1 to y2 where the values y1 and y2 are in absolute data coordinates.

How do you get assembler output from C/C++ source in gcc?

If you're looking for LLVM assembly:

llvm-gcc -emit-llvm -S hello.c with headers

Can I control the HTTP headers sent by (cross browser)?


If not, can I somehow a page that then issues my request with custom headers inside its popped-up window?

  • You can request a URL that triggers a server side program which makes the request with arbitrary headers and then returns the response
  • You can run JavaScript (probably saying goodbye to Progressive Enhancement) that uses XHR to make the request with arbitrary headers (assuming the URL fits within the Same Origin Policy) and then process the result in JS.

I need some cunning hacks...

It might help if you described the problem instead of asking if possible solutions would work.

Function overloading in Python: Missing

As unwind noted, keyword arguments with default values can go a long way.

I'll also state that in my opinion, it goes against the spirit of Python to worry a lot about what types are passed into methods. In Python, I think it's more accepted to use duck typing -- asking what an object can do, rather than what it is.

Thus, if your method may accept a string or a tuple, you might do something like this:

def print_names(names):
    """Takes a space-delimited string or an iterable"""
        for name in names.split(): # string case
            print name
    except AttributeError:
        for name in names:
            print name

Then you could do either of these:

print_names("Ryan Billy")
print_names(("Ryan", "Billy"))

Although an API like that sometimes indicates a design problem.

How to unlock a file from someone else in Team Foundation Server

Based on stackptr answer I've created batch file UnlockOther.bat

@rem from
@rem tf undo {file path} /workspace:{workspace};{username

call "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat" x86
@echo on
tf undo $/MyTfsProject/path/fileName.ext /workspace:CollegeMachine;CollegueName /login:MyLogin 

Docker: Copying files from Docker container to host

I used PowerShell (Admin) with this command.

docker cp {container id}:{container path}/error.html  C:\\error.html


docker cp ff3a6608467d:/var/www/app/error.html  C:\\error.html

Current Subversion revision command

There is also a more convenient (for some) svnversion command.

Output might be a single revision number or something like this (from -h):

  4123:4168     mixed revision working copy
  4168M         modified working copy
  4123S         switched working copy
  4123:4168MS   mixed revision, modified, switched working copy

I use this python code snippet to extract revision information:

import re
import subprocess

p = subprocess.Popen(["svnversion"], stdout = subprocess.PIPE, 
    stderr = subprocess.PIPE)
m = re.match(r'(|\d+M?S?):?(\d+)(M?)S?',
rev = int(
if == 'M':
    rev += 1

Split / Explode a column of dictionaries into separate columns with pandas

To convert the string to an actual dict, you can do df['Pollutant Levels'].map(eval). Afterwards, the solution below can be used to convert the dict to different columns.

Using a small example, you can use .apply(pd.Series):

In [2]: df = pd.DataFrame({'a':[1,2,3], 'b':[{'c':1}, {'d':3}, {'c':5, 'd':6}]})

In [3]: df
   a                   b
0  1           {u'c': 1}
1  2           {u'd': 3}
2  3  {u'c': 5, u'd': 6}

In [4]: df['b'].apply(pd.Series)
     c    d
0  1.0  NaN
1  NaN  3.0
2  5.0  6.0

To combine it with the rest of the dataframe, you can concat the other columns with the above result:

In [7]: pd.concat([df.drop(['b'], axis=1), df['b'].apply(pd.Series)], axis=1)
   a    c    d
0  1  1.0  NaN
1  2  NaN  3.0
2  3  5.0  6.0

Using your code, this also works if I leave out the iloc part:

In [15]: pd.concat([df.drop('b', axis=1), pd.DataFrame(df['b'].tolist())], axis=1)
   a    c    d
0  1  1.0  NaN
1  2  NaN  3.0
2  3  5.0  6.0

Convert UTF-8 with BOM to UTF-8 with no BOM in Python

import codecs
import shutil
import sys

s =
if s != codecs.BOM_UTF8:

shutil.copyfileobj(sys.stdin, sys.stdout)

What is "entropy and information gain"?

As you are reading a book about NLTK it would be interesting you read about MaxEnt Classifier Module

For text mining classification the steps could be: pre-processing (tokenization, steaming, feature selection with Information Gain ...), transformation to numeric (frequency or TF-IDF) (I think that this is the key step to understand when using text as input to a algorithm that only accept numeric) and then classify with MaxEnt, sure this is just an example.

How do I concatenate multiple C++ strings on one line?

Have you tried to avoid the +=? instead use var = var + ... it worked for me.

#include <iostream.h> // for string

string myName = "";
int _age = 30;
myName = myName + "Vincent" + "Thorpe" + 30 + " " + 2019;

$_SERVER["REMOTE_ADDR"] gives server IP rather than visitor IP





Worked for me.

How to use JavaScript source maps (.map files)?

The .map files are for js and css (and now ts too) files that have been minified. They are called SourceMaps. When you minify a file, like the angular.js file, it takes thousands of lines of pretty code and turns it into only a few lines of ugly code. Hopefully, when you are shipping your code to production, you are using the minified code instead of the full, unminified version. When your app is in production, and has an error, the sourcemap will help take your ugly file, and will allow you to see the original version of the code. If you didn't have the sourcemap, then any error would seem cryptic at best.

Same for CSS files. Once you take a SASS or LESS file and compile it to CSS, it looks nothing like its original form. If you enable sourcemaps, then you can see the original state of the file, instead of the modified state.

So, to answer you questions in order:

  • What is it for? To de-reference uglified code
  • How can a developer use it? You use it for debugging a production app. In development mode you can use the full version of Angular. In production, you would use the minified version.
  • Should I care about creating a file? If you care about being able to debug production code easier, then yes, you should do it.
  • How does it get created? It is created at build time. There are build tools that can build your .map file for you as it does other files.

I hope this makes sense.

CSS filter: make color image with transparency white

You can use

filter: brightness(0) invert(1);

html {_x000D_
  background: red;_x000D_
p {_x000D_
  float: left;_x000D_
  max-width: 50%;_x000D_
  text-align: center;_x000D_
img {_x000D_
  display: block;_x000D_
  max-width: 100%;_x000D_
.filter {_x000D_
  -webkit-filter: brightness(0) invert(1);_x000D_
  filter: brightness(0) invert(1);_x000D_
  <img src="" />_x000D_
  <img src="" class="filter" />_x000D_

First, brightness(0) makes all image black, except transparent parts, which remain transparent.

Then, invert(1) makes the black parts white.

Name node is in safe mode. Not able to leave

In order to forcefully let the namenode leave safemode, following command should be executed:

 bin/hadoop dfsadmin -safemode leave

You are getting Unknown command error for your command as -safemode isn't a sub-command for hadoop fs, but it is of hadoop dfsadmin.

Also after the above command, I would suggest you to once run hadoop fsck so that any inconsistencies crept in the hdfs might be sorted out.


Use hdfs command instead of hadoop command for newer distributions. The hadoop command is being deprecated:

hdfs dfsadmin -safemode leave

hadoop dfsadmin has been deprecated and so is hadoop fs command, all hdfs related tasks are being moved to a separate command hdfs.

Hiding elements in responsive layout?

Additional CSS Remove Sidebar from all pages in Mobile view:

@media only screen and (max-width:767px)
#secondary {
display: none;

@viewChild not working - cannot read property nativeElement of undefined

it just simple :import this directory

import {Component, Directive, Input, ViewChild} from '@angular/core';

MySQL Workbench - Connect to a Localhost

If xamp already installed on your computer user these settings

enter image description here

How to disable sort in DataGridView?

If you can extend the DataGridView you can override the Sort method with am empty one. This disables Sort for the DataGridView entirely.

public override void Sort(DataGridViewColumn dataGridViewColumn, ListSortDirection direction)
    //base.Sort(dataGridViewColumn, direction);

Note: You cannot even programmatically sort any column.

Clear form after submission with jQuery

try this in your post methods callback function

 .not(':button, :submit, :reset, :hidden')

for more info read this

Regex for numbers only

Use beginning and end anchors.

 Regex regex = new Regex(@"^\d$");

Use "^\d+$" if you need to match more than one digit.

What's with the dollar sign ($"string")

String Interpolation

is a concept that languages like Perl have had for quite a while, and now we’ll get this ability in C# as well. In String Interpolation, we simply prefix the string with a $ (much like we use the @ for verbatim strings). Then, we simply surround the expressions we want to interpolate with curly braces (i.e. { and }):

It looks a lot like the String.Format() placeholders, but instead of an index, it is the expression itself inside the curly braces. In fact, it shouldn’t be a surprise that it looks like String.Format() because that’s really all it is – syntactical sugar that the compiler treats like String.Format() behind the scenes.

A great part is, the compiler now maintains the placeholders for you so you don’t have to worry about indexing the right argument because you simply place it right there in the string.

C# string interpolation is a method of concatenating,formatting and manipulating strings. This feature was introduced in C# 6.0. Using string interpolation, we can use objects and expressions as a part of the string interpolation operation.

Syntax of string interpolation starts with a ‘$’ symbol and expressions are defined within a bracket {} using the following syntax.



  • interpolatedExpression - The expression that produces a result to be formatted
  • alignment - The constant expression whose value defines the minimum number of characters in the string representation of the result of the interpolated expression. If positive, the string representation is right-aligned; if negative, it's left-aligned.
  • formatString - A format string that is supported by the type of the expression result.

The following code example concatenates a string where an object, author as a part of the string interpolation.

string author = "Mohit";  
string hello = $"Hello {author} !";  
Console.WriteLine(hello);  // Hello Mohit !

Read more on C#/.NET Little Wonders: String Interpolation in C# 6

How do I see the commit differences between branches in git?

You can get a really nice, visual output of how your branches differ with this

git log --graph --pretty=format:'%Cred%h%Creset -%C(yellow)%d%Creset %s %Cgreen(%cr)%Creset' --abbrev-commit --date=relative master..branch-X

Execute stored procedure with an Output parameter?

With this query you can execute any stored procedure(With or Without output parameter):

DECLARE @temp varchar(100)  
EXEC my_sp
    @parameter1 = 1, 
    @parameter2 = 2, 
    @parameter3 = @temp output, 
    @parameter4 = 3, 
    @parameter5 = 4
PRINT @temp

Here datatype of @temp should be same as @parameter3 within SP.

Hope this helps..

counting number of directories in a specific directory

Some useful examples:

count files in current dir

/bin/ls -lA  | egrep -c '^-'

count dirs in current dir

/bin/ls -lA  | egrep -c '^d'

count files and dirs in current dir

/bin/ls -lA  | egrep -c '^-|^d'

count files and dirs in in one subdirectory

/bin/ls -lA  subdir_name/ | egrep -c '^-|^d'

I have noticed a strange thing (at least in my case) :

When I have tried with ls instead /bin/ls the -A parameter do not list implied . and .. NOT WORK as espected. When I use ls that show ./ and ../ So that result wrong count. SOLUTION : /bin/ls instead ls

Secure random token in Node.js

Check out:

var crypto = require('crypto');

Fixed point vs Floating point number

From my understanding, fixed-point arithmetic is done using integers. where the decimal part is stored in a fixed amount of bits, or the number is multiplied by how many digits of decimal precision is needed.

For example, If the number 12.34 needs to be stored and we only need two digits of precision after the decimal point, the number is multiplied by 100 to get 1234. When performing math on this number, we'd use this rule set. Adding 5620 or 56.20 to this number would yield 6854 in data or 68.54.

If we want to calculate the decimal part of a fixed-point number, we use the modulo (%) operand.

12.34 (pseudocode):

v1 = 1234 / 100 // get the whole number
v2 = 1234 % 100 // get the decimal number (100ths of a whole).
print v1 + "." + v2 // "12.34"

Floating point numbers are a completely different story in programming. The current standard for floating point numbers use something like 23 bits for the data of the number, 8 bits for the exponent, and 1 but for sign. See this Wikipedia link for more information on this.

find all unchecked checkbox in jquery

You can do so by extending jQuerys functionality. This will shorten the amount of text you have to write for the selector.

$.extend($.expr[':'], {
        unchecked: function (obj) {
            return ((obj.type == 'checkbox' || obj.type == 'radio') && !$(obj).is(':checked'));

You can then use $("input:unchecked") to get all checkboxes and radio buttons that are checked.

How do I call a function twice or more times consecutively?

I would:

for _ in range(3):

The _ is convention for a variable whose value you don't care about.

You might also see some people write:

[do() for _ in range(3)]

however that is slightly more expensive because it creates a list containing the return values of each invocation of do() (even if it's None), and then throws away the resulting list. I wouldn't suggest using this unless you are using the list of return values.

Saving image to file

If you are drawing on the Graphics of the Control than you should do something draw on the Bitmap everything you are drawing on the canvas, but have in mind that Bitmap needs to be the exact size of the control you are drawing on:

  Bitmap bmp = new Bitmap(myControl.ClientRectangle.Width,myControl.ClientRectangle.Height);
  Graphics gBmp = Graphics.FromImage(bmp);
  gBmp.DrawEverything(); //this is your code for drawing
  bmp.Save("image.png", ImageFormat.Png);

Or you can use a DrawToBitmap method of the Control. Something like this:

Bitmap bmp = new Bitmap(myControl.ClientRectangle.Width, myControl.ClientRectangle.Height);
myControl.DrawToBitmap(bmp,new Rectangle(0,0,bmp.Width,bmp.Height));
bmp.Save("image.png", ImageFormat.Png);

Set JavaScript variable = null, or leave undefined?

The only time you need to set it (or not) is if you need to explicitly check that a variable a is set exactly to null or undefined.

if(a === null) {

} not the same as:

if(a === undefined) {


That said, a == null && a == undefined will return true.


how to hide <li> bullets in navigation menu and footer links BUT show them for listing items

You need to define a class for the bullets you want to hide. For examples

.no-bullets {
    list-style-type: none;

Then apply it to the list you want hidden bullets:

<ul class="no-bullets">

All other lists (without a specific class) will show the bulltets as usual.

To draw an Underline below the TextView in Android

A simple and sustainable solution is to create a layer-list and make it as the background of your TextView:

<layer-list xmlns:android="">
                android:color="@color/colorAccent" />

R * not meaningful for factors ERROR

new[,2] is a factor, not a numeric vector. Transform it first

new$MY_NEW_COLUMN <-as.numeric(as.character(new[,2])) * 5

ActiveMQ or RabbitMQ or ZeroMQ or

There's a comparison between RabbitMQ and ActiveMQ here. Out of the box, ActiveMQ is configured to guarantee message delivery - which can give the impression its slow compared to less reliable messaging systems. You can always change the configuration for performance if you wish and get at least as good performance as any other messaging system. At least you have that option. There's a lot of information on the forums and the ActiveMQ FAQ for configuration for scaling, performance and high availability. Also, ActiveMQ will support AMQP 1.0 when the spec is finalized, together with other wire formats, like STOMP.

Another plus for ActiveMQ is its an Apache project, so there is diversity in the developer community - and its not tied to one company.

How to access site running apache server over lan without internet connection

In your httpd.conf make sure you have:

Listen *:80

And if you are using VirtualHosts then set them as given below:

NameVirtualHost *
<VirtualHost *>

Iterate over model instance field names and values in template

This approach shows how to use a class like django's ModelForm and a template tag like {{ form.as_table }}, but have all the table look like data output, not a form.

The first step was to subclass django's TextInput widget:

from django import forms
from django.utils.safestring import mark_safe
from django.forms.util import flatatt

class PlainText(forms.TextInput):
    def render(self, name, value, attrs=None):
        if value is None:
            value = ''
        final_attrs = self.build_attrs(attrs)
        return mark_safe(u'<p %s>%s</p>' % (flatatt(final_attrs),value))

Then I subclassed django's ModelForm to swap out the default widgets for readonly versions:

from django.forms import ModelForm

class ReadOnlyModelForm(ModelForm):
    def __init__(self,*args,**kwrds):
        for field in self.fields:
            if isinstance(self.fields[field].widget,forms.TextInput) or \
            elif isinstance(self.fields[field].widget,forms.CheckboxInput):

Those were the only widgets I needed. But it should not be difficult to extend this idea to other widgets.

How can I delete all cookies with JavaScript?

This is a function we are using in our application and it is working fine.

delete cookie: No argument method

function clearListCookies()
    var cookies = document.cookie.split(";");
    for (var i = 0; i < cookies.length; i++)
        var spcook =  cookies[i].split("=");
    function deleteCookie(cookiename)
        var d = new Date();
        d.setDate(d.getDate() - 1);
        var expires = ";expires="+d;
        var name=cookiename;
        var value="";
        document.cookie = name + "=" + value + expires + "; path=/acc/html";                    
    window.location = ""; // TO REFRESH THE PAGE

Edit: This will delete the cookie by setting it to yesterday's date.

How to bind event listener for rendered elements in Angular 2?

In order to add an EventListener to an element in angular 2+, we can use the method listen of the Renderer2 service (Renderer is deprecated, so use Renderer2):

listen(target: 'window'|'document'|'body'|any, eventName: string, callback: (event: any) => boolean | void): () => void


export class ListenDemo implements AfterViewInit { 
   private testElement: ElementRef;
   globalInstance: any;       

   constructor(private renderer: Renderer2) {

   ngAfterViewInit() {
       this.globalInstance = this.renderer.listen(this.testElement.nativeElement, 'click', () => {
           this.renderer.setStyle(this.testElement.nativeElement, 'color', 'green');


When you use this method to add an event listener to an element in the dom, you should remove this event listener when the component is destroyed

You can do that this way:

ngOnDestroy() {

The way of use of ElementRef in this method should not expose your angular application to a security risk. for more on this referrer to ElementRef security risk angular 2

How to embed matplotlib in pyqt - for Dummies

Below is an adaptation of previous code for using under PyQt5 and Matplotlib 2.0. There are a number of small changes: structure of PyQt submodules, other submodule from matplotlib, deprecated method has been replaced...

import sys
from PyQt5.QtWidgets import QDialog, QApplication, QPushButton, QVBoxLayout

from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as FigureCanvas
from matplotlib.backends.backend_qt5agg import NavigationToolbar2QT as NavigationToolbar
import matplotlib.pyplot as plt

import random

class Window(QDialog):
    def __init__(self, parent=None):
        super(Window, self).__init__(parent)

        # a figure instance to plot on
        self.figure = plt.figure()

        # this is the Canvas Widget that displays the `figure`
        # it takes the `figure` instance as a parameter to __init__
        self.canvas = FigureCanvas(self.figure)

        # this is the Navigation widget
        # it takes the Canvas widget and a parent
        self.toolbar = NavigationToolbar(self.canvas, self)

        # Just some button connected to `plot` method
        self.button = QPushButton('Plot')

        # set the layout
        layout = QVBoxLayout()

    def plot(self):
        ''' plot some random stuff '''
        # random data
        data = [random.random() for i in range(10)]

        # instead of ax.hold(False)

        # create an axis
        ax = self.figure.add_subplot(111)

        # discards the old graph
        # ax.hold(False) # deprecated, see above

        # plot data
        ax.plot(data, '*-')

        # refresh canvas

if __name__ == '__main__':
    app = QApplication(sys.argv)

    main = Window()


Object variable or With block variable not set (Error 91)

As I wrote in my comment, the solution to your problem is to write the following:

Set hyperLinkText = hprlink.Range

Set is needed because TextRange is a class, so hyperLinkText is an object; as such, if you want to assign it, you need to make it point to the actual object that you need.

How can I include a YAML file inside another?

Includes are not directly supported in YAML as far as I know, you will have to provide a mechanism yourself however, this is generally easy to do.

I have used YAML as a configuration language in my python apps, and in this case often define a convention like this:

>>> main.yml <<<
includes: [ wibble.yml, wobble.yml]

Then in my (python) code I do:

import yaml
cfg = yaml.load(open("main.yml"))
for inc in cfg.get("includes", []):

The only down side is that variables in the includes will always override the variables in main, and there is no way to change that precedence by changing where the "includes: statement appears in the main.yml file.

On a slightly different point, YAML doesn't support includes as its not really designed as as exclusively as a file based mark up. What would an include mean if you got it in a response to an AJAX request?

LEFT JOIN only first row

based on several answers here, i found something that worked for me and i wanted to generalize and explain what's going on.


LEFT JOIN table2 t2 ON (t2.thing = t1.thing)


LEFT JOIN table2 t2 ON (t2.p_key = (SELECT MIN(t2_.p_key) 
    FROM table2 t2_ WHERE (t2_.thing = t1.thing) LIMIT 1))

the condition that connects t1 and t2 is moved from the ON and into the inner query WHERE. the MIN(primary key) or LIMIT 1 makes sure that only 1 row is returned by the inner query.

after selecting one specific row we need to tell the ON which row it is. that's why the ON is comparing the primary key of the joined tabled.

you can play with the inner query (i.e. order+limit) but it must return one primary key of the desired row that will tell the ON the exact row to join.

Update - for MySQL 5.7+

another option relevant to MySQL 5.7+ is to use ANY_VALUE+GROUP BY. it will select an artist name that is not necessarily the first one.

SELECT feeds.*,ANY_VALUE( artist_name
    FROM feeds 
    LEFT JOIN feeds_artists ON = feeds_artists.feed_id 

more info about ANY_VALUE:

SQL Server equivalent to MySQL enum data type?

Found this interesting approach when I wanted to implement enums in SQL Server.

The approach mentioned below in the link is quite compelling, considering all your database enum needs could be satisfied with 2 central tables.

Is there a max size for POST parameter content?

Yes there is 2MB max and it can be increased by configuration change like this. If your POST body is not in form of multipart file then you might need to add the max-http-post configuration for tomcat in the application yml configuration file.

Increase max size of each multipart file to 10MB and total payload size of 100MB max

      multipart:max-file-size: 10MB
      multipart:max-request-size: 100MB

Setting max size of post requests which might just be the formdata in string format to ~10 MB

        max-http-post-size: 100000000 # max-http-form-post-size: 10MB for new version

You might need to add this for the latest sprintboot version ->

server: tomcat: max-http-form-post-size: 10MB

Setting "checked" for a checkbox with jQuery

Here is code for checked and unchecked with a button:

var set=1;
var unset=0;
jQuery( function() {
    $( '.checkAll' ).live('click', function() {
        $( '.cb-element' ).each(function () {
            if(set==1){ $( '.cb-element' ).attr('checked', true) unset=0; }
            if(set==0){ $( '.cb-element' ).attr('checked', false); unset=1; }

Update: Here is the same code block using the newer Jquery 1.6+ prop method, which replaces attr:

var set=1;
var unset=0;
jQuery( function() {
    $( '.checkAll' ).live('click', function() {
        $( '.cb-element' ).each(function () {
            if(set==1){ $( '.cb-element' ).prop('checked', true) unset=0; }
            if(set==0){ $( '.cb-element' ).prop('checked', false); unset=1; }

Send email by using codeigniter library via localhost

$insert = $this->db->insert('email_notification', $data);
                $this->session->set_flashdata("msg", "<div class='alert alert-success'> Cafe has been added Successfully.</div>");

                //require ("plugins/mailer/PHPMailerAutoload.php");
                $mail = new PHPMailer;
                $mail->SMTPOptions = array(
                    'ssl' => array(
                    'verify_peer' => false,
                    'verify_peer_name' => false,
                    'allow_self_signed' => true,

                     Your Account Has beed created successfully by Admin:
                    Username: ".$this->input->post('username')." <br><br>
                    Email: ".$this->input->post('sender_email')." <br><br>
                    <div class='background-color:#666;color:#fff;padding:6px;
                         Bookly Admin.
                $mail->isSMTP(); // Set mailer to use SMTP
                $mail->Host = ''; // Specify main and backup SMTP servers
                $mail->SMTPAuth = true; 
                $subject = "Hello  ".$this->input->post('username');
                $email = $this->input->post('sender_email'); //this email is user email
                $from_label = "Account Creation";
                $mail->Username = 'your email'; // SMTP username
                $mail->Password = 'password'; // SMTP password
                $mail->SMTPSecure = 'ssl'; // Enable TLS encryption, `ssl` also accepted
                $mail->Port = 465;
                $mail->addAddress($email, 'Bookly Admin');
                $mail->Subject = $subject;
                $mail->Body = $message;
                $mail->AltBody = 'This is the body in plain text for non-HTML mail clients';


AngularJS - How to use $routeParams in generating the templateUrl?

This very helpful feature is now available starting at version 1.1.2 of AngularJS. It's considered unstable but I have used it (1.1.3) and it works fine.

Basically you can use a function to generate a templateUrl string. The function is passed the route parameters that you can use to build and return the templateUrl string.

var app = angular.module('app',[]);

    function($routeProvider) {
            when('/', {templateUrl:'/home'}).
                    templateUrl: function(params){ return '/users/view/' + params.user_id; }

Many thanks to for the pull request.

Android - Activity vs FragmentActivity?

FragmentActivity gives you all of the functionality of Activity plus the ability to use Fragments which are very useful in many cases, particularly when working with the ActionBar, which is the best way to use Tabs in Android.

If you are only targeting Honeycomb (v11) or greater devices, then you can use Activity and use the native Fragments introduced in v11 without issue. FragmentActivity was built specifically as part of the Support Library to back port some of those useful features (such as Fragments) back to older devices.

I should also note that you'll probably find the Backward Compatibility - Implementing Tabs training very helpful going forward.

How to insert Records in Database using C# language?

There are many problems in your query.
This is a modified version of your code

string connetionString = null;
string sql = null;

// All the info required to reach your db. See
connetionString = "Data Source=UMAIR;Initial Catalog=Air; Trusted_Connection=True;" ;

// Prepare a proper parameterized query 
sql = "insert into Main ([Firt Name], [Last Name]) values(@first,@last)";

// Create the connection (and be sure to dispose it at the end)
using(SqlConnection cnn = new SqlConnection(connetionString))
       // Open the connection to the database. 
       // This is the first critical step in the process.
       // If we cannot reach the db then we have connectivity problems

       // Prepare the command to be executed on the db
       using(SqlCommand cmd = new SqlCommand(sql, cnn))
           // Create and set the parameters values 
           cmd.Parameters.Add("@first", SqlDbType.NVarChar).Value = textbox2.text;
           cmd.Parameters.Add("@last", SqlDbType.NVarChar).Value = textbox3.text;

           // Let's ask the db to execute the query
           int rowsAdded = cmd.ExecuteNonQuery();
           if(rowsAdded > 0) 
              MessageBox.Show ("Row inserted!!" + );
              // Well this should never really happen
              MessageBox.Show ("No row inserted");

    catch(Exception ex)
        // We should log the error somewhere, 
        // for this example let's just show a message
        MessageBox.Show("ERROR:" + ex.Message);
  • The column names contain spaces (this should be avoided) thus you need square brackets around them
  • You need to use the using statement to be sure that the connection will be closed and resources released
  • You put the controls directly in the string, but this don't work
  • You need to use a parametrized query to avoid quoting problems and sqlinjiection attacks
  • No need to use a DataAdapter for a simple insert query
  • Do not use AddWithValue because it could be a source of bugs (See link below)

Apart from this, there are other potential problems. What if the user doesn't input anything in the textbox controls? Do you have done any checking on this before trying to insert? As I have said the fields names contain spaces and this will cause inconveniences in your code. Try to change those field names.

This code assumes that your database columns are of type NVARCHAR, if not, then use the appropriate SqlDbType enum value.

Please plan to switch to a more recent version of NET Framework as soon as possible. The 1.1 is really obsolete now.

And, about AddWithValue problems, this article explain why we should avoid it. Can we stop using AddWithValue() already?

How to get start and end of day in Javascript?

In MomentJs We can declare it like :

   const start = moment().format('YYYY-MM-DD 00:00:01');
   const end = moment().format('YYYY-MM-DD 23:59:59');

Update value of a nested dictionary of varying depth

This question is old, but I landed here when searching for a "deep merge" solution. The answers above inspired what follows. I ended up writing my own because there were bugs in all the versions I tested. The critical point missed was, at some arbitrary depth of the two input dicts, for some key, k, the decision tree when d[k] or u[k] is not a dict was faulty.

Also, this solution does not require recursion, which is more symmetric with how dict.update() works, and returns None.

import collections
def deep_merge(d, u):
   """Do a deep merge of one dict into another.

   This will update d with values in u, but will not delete keys in d
   not found in u at some arbitrary depth of d. That is, u is deeply
   merged into d.

   Args -
     d, u: dicts

   Note: this is destructive to d, but not u.

   Returns: None
   stack = [(d,u)]
   while stack:
      d,u = stack.pop(0)
      for k,v in u.items():
         if not isinstance(v, collections.Mapping):
            # u[k] is not a dict, nothing to merge, so just set it,
            # regardless if d[k] *was* a dict
            d[k] = v

            # note: u[k] is a dict
            if k not in d:
                # add new key into d
                d[k] = v
            elif not isinstance(d[k], collections.Mapping):
                # d[k] is not a dict, so just set it to u[k],
                # overriding whatever it was
                d[k] = v
                # both d[k] and u[k] are dicts, push them on the stack
                # to merge
                stack.append((d[k], v))

PHP using Gettext inside <<<EOF string

As far as I can see, you just added heredoc by mistake
No need to use ugly heredoc syntax here.
Just remove it and everything will work:

<p><?= _("World"); ?></p>

Why does python use 'else' after for and while loops?

A common construct is to run a loop until something is found and then to break out of the loop. The problem is that if I break out of the loop or the loop ends I need to determine which case happened. One method is to create a flag or store variable that will let me do a second test to see how the loop was exited.

For example assume that I need to search through a list and process each item until a flag item is found and then stop processing. If the flag item is missing then an exception needs to be raised.

Using the Python for...else construct you have

for i in mylist:
    if i == theflag:
    raise ValueError("List argument missing terminal flag.")

Compare this to a method that does not use this syntactic sugar:

flagfound = False
for i in mylist:
    if i == theflag:
        flagfound = True

if not flagfound:
    raise ValueError("List argument missing terminal flag.")

In the first case the raise is bound tightly to the for loop it works with. In the second the binding is not as strong and errors may be introduced during maintenance.

UPDATE multiple tables in MySQL using LEFT JOIN

UPDATE `Table A` a
SET a.`text`=(
        SELECT group_concat(b.`B-num`,' from ',b.`date` SEPARATOR ' / ') 
        FROM `Table B` b WHERE (a.`A-num`=b.`A-num`)

Truncate a string straight JavaScript

var str = "Anything you type in.";
str.substring(0, 5) + "..." //you can type any amount of length you want

How to send authorization header with axios

            'Access-Control-Allow-Headers, Origin,OPTIONS,Accept,Authorization, X-Requested-With, Content-Type, Access-Control-Request-Method, Access-Control-Request-Headers');

Blockquote : you have to add OPTIONS & Authorization to the setHeader()

this change has fixed my problem, just give a try!

How to pass multiple parameters to a get method in ASP.NET Core

enter image description here

NB-I removed FromURI .Still I can pass value from URL and get result.If anyone knows benfifts using fromuri let me know

How to rename files and folder in Amazon S3?

There is no direct method to rename a file in S3. What you have to do is copy the existing file with a new name (just set the target key) and delete the old one.

Why does JavaScript only work after opening developer tools in IE once?

HTML5 Boilerplate has a nice pre-made code for console problems fixing:

// Avoid `console` errors in browsers that lack a console.
(function() {
    var method;
    var noop = function () {};
    var methods = [
        'assert', 'clear', 'count', 'debug', 'dir', 'dirxml', 'error',
        'exception', 'group', 'groupCollapsed', 'groupEnd', 'info', 'log',
        'markTimeline', 'profile', 'profileEnd', 'table', 'time', 'timeEnd',
        'timeStamp', 'trace', 'warn'
    var length = methods.length;
    var console = (window.console = window.console || {});

    while (length--) {
        method = methods[length];

        // Only stub undefined methods.
        if (!console[method]) {
            console[method] = noop;

As @plus- pointed in comments, latest version is available on their GitHub page

How is attr_accessible used in Rails 4?

Rails 4 now uses strong parameters.

Protecting attributes is now done in the controller. This is an example:

class PeopleController < ApplicationController
  def create


  def person_params
    params.require(:person).permit(:name, :age)

No need to set attr_accessible in the model anymore.

Dealing with accepts_nested_attributes_for

In order to use accepts_nested_attribute_for with strong parameters, you will need to specify which nested attributes should be whitelisted.

class Person
  has_many :pets
  accepts_nested_attributes_for :pets

class PeopleController < ApplicationController
  def create

  # ...


  def person_params
    params.require(:person).permit(:name, :age, pets_attributes: [:name, :category])

Keywords are self-explanatory, but just in case, you can find more information about strong parameters in the Rails Action Controller guide.

Note: If you still want to use attr_accessible, you need to add protected_attributes to your Gemfile. Otherwise, you will be faced with a RuntimeError.

How to bind bootstrap popover on dynamic elements

Try this HTML

<a href="#" data-toggle="popover" data-popover-target="#popover-content-1">Do Popover 1</a>
<a href="#" data-toggle="popover" data-popover-target="#popover-content-2">Do Popover</a>

<div id="popover-content-1" style="display: none">Content 1</div>
<div id="popover-content-2" style="display: none">Content 2</div>


$(function() {
  $('[data-toggle="popover"]').each(function(i, obj) {
    var popover_target = $(this).data('popover-target');
        html: true,
        trigger: 'focus',
        placement: 'right',
        content: function(obj) {
            return $(popover_target).html();

How to write multiple line string using Bash with variables?

Below mechanism helps in redirecting multiple lines to file. Keep complete string under " so that we can redirect values of the variable.

echo "line 1, ${kernel}
line 2," > a.txt
echo 'line 2, ${kernel}
line 2,' > b.txt

Content of a.txt is

line 1, 2.6.39
line 2,

Content of b.txt is

line 2, ${kernel}
line 2,

Best way to return a value from a python script

If you want your script to return values, just do return [1,2,3] from a function wrapping your code but then you'd have to import your script from another script to even have any use for that information:

Return values (from a wrapping-function)

(again, this would have to be run by a separate Python script and be imported in order to even do any good):

import ...
def main():
    # calculate stuff
    return [1,2,3]

Exit codes as indicators

(This is generally just good for when you want to indicate to a governor what went wrong or simply the number of bugs/rows counted or w/e. Normally 0 is a good exit and >=1 is a bad exit but you could inter-prate them in any way you want to get data out of it)

import sys
# calculate and stuff

And exit with a specific exit code depending on what you want that to tell your governor. I used exit codes when running script by a scheduling and monitoring environment to indicate what has happened.

(os._exit(100) also works, and is a bit more forceful)

Stdout as your relay

If not you'd have to use stdout to communicate with the outside world (like you've described). But that's generally a bad idea unless it's a parser executing your script and can catch whatever it is you're reporting to.

import sys
# calculate stuff
sys.stdout.write('Bugs: 5|Other: 10\n')

Are you running your script in a controlled scheduling environment then exit codes are the best way to go.

Files as conveyors

There's also the option to simply write information to a file, and store the result there.

# calculate
with open('finish.txt', 'wb') as fh:

And pick up the value/result from there. You could even do it in a CSV format for others to read simplistically.

Sockets as conveyors

If none of the above work, you can also use network sockets locally *(unix sockets is a great way on nix systems). These are a bit more intricate and deserve their own post/answer. But editing to add it here as it's a good option to communicate between processes. Especially if they should run multiple tasks and return values.

SimpleXML - I/O warning : failed to load external entity

simplexml_load_file() interprets an XML file (either a file on your disk or a URL) into an object. What you have in $feed is a string.

You have two options:

  • Use file_get_contents() to get the XML feed as a string, and use e simplexml_load_string():

    $feed = file_get_contents('...');
    $items = simplexml_load_string($feed);
  • Load the XML feed directly using simplexml_load_file():

    $items = simplexml_load_file('...');

What is the use of DesiredCapabilities in Selenium WebDriver?

DesiredCapabilities are options that you can use to customize and configure a browser session.

You can read more about them here!

RelativeLayout center vertical



How to get distinct results in hibernate with joins and row-based limiting (paging)?

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

Simply add this to your criteria:


Now you'll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check as part of the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness after the sql query has been performed.

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

Launch custom android application from android browser

In my case I had to set two categories for the <intent-filter> and then it worked:

<data android:scheme="my.special.scheme" />
<action android:name="android.intent.action.VIEW" />
<category android:name="android.intent.category.DEFAULT"/>
<category android:name="android.intent.category.BROWSABLE"/>

Play an audio file using jQuery when a button is clicked

I here have a nice and versatile solution with a fallback:

<script type="text/javascript">
    var audiotypes={
        "mp3": "audio/mpeg",
        "mp4": "audio/mp4",
        "ogg": "audio/ogg",
        "wav": "audio/wav"

    function ss_soundbits(sound){
        var audio_element = document.createElement('audio')
        if (audio_element.canPlayType){
            for (var i=0; i<arguments.length; i++){
                var source_element = document.createElement('source')
                source_element.setAttribute('src', arguments[i])
                if (arguments[i].match(/\.(\w+)$/i))
                    source_element.setAttribute('type', audiotypes[RegExp.$1])
            return audio_element

After that you can initialize as many audio as you like:

<script type="text/javascript" >
    var clicksound  = ss_soundbits('your/path/to/click.ogg', "your/path/to/click.mp3");
    var plopsound  = ss_soundbits('your/path/to/plopp.ogg', "your/path/to/plopp.mp3");

Now you can reach the initialized audio element whereever you like with simple event calls like



Convert RGBA PNG to RGB with PIL

By using Image.alpha_composite, the solution by Yuji 'Tomita' Tomita become simpler. This code can avoid a tuple index out of range error if png has no alpha channel.

from PIL import Image

png ='RGBA')
background ='RGBA', png.size, (255,255,255))

alpha_composite = Image.alpha_composite(background, png)'foo.jpg', 'JPEG', quality=80)

Error LNK2019 unresolved external symbol _main referenced in function "int __cdecl invoke_main(void)" (?invoke_main@@YAHXZ)

Solution for me is: I clean both the solution and the project. And just rebuild the project. This error happens because I tried to delete the main file (only keep library files) in the previous build so at the current build the old stuff is still kept in the built directory. That's why unresolved things happened. "unresolved external symbol _main referenced in function "int __cdecl invoke_main(void)" (?invoke_main@@YAHXZ) "

How to convert hex to rgb using Java?

Here is a version that handles both RGB and RGBA versions:

 * Converts a hex string to a color. If it can't be converted null is returned.
 * @param hex (i.e. #CCCCCCFF or CCCCCC)
 * @return Color
public static Color HexToColor(String hex) 
    hex = hex.replace("#", "");
    switch (hex.length()) {
        case 6:
            return new Color(
            Integer.valueOf(hex.substring(0, 2), 16),
            Integer.valueOf(hex.substring(2, 4), 16),
            Integer.valueOf(hex.substring(4, 6), 16));
        case 8:
            return new Color(
            Integer.valueOf(hex.substring(0, 2), 16),
            Integer.valueOf(hex.substring(2, 4), 16),
            Integer.valueOf(hex.substring(4, 6), 16),
            Integer.valueOf(hex.substring(6, 8), 16));
    return null;

How to update column value in laravel

You may try this:

Page::where('id', $id)->update(array('image' => 'asdasd'));

There are other ways too but no need to use Page::find($id); in this case. But if you use find() then you may try it like this:

$page = Page::find($id);

// Make sure you've got the Page model
if($page) {
    $page->image = 'imagepath';

Also you may use:

$page = Page::findOrFail($id);

So, it'll throw an exception if the model with that id was not found.

Reading data from DataGridView in C#

If you wish, you can also use the column names instead of column numbers.

For example, if you want to read data from DataGridView on the 4. row and the "Name" column. It provides me a better understanding for which variable I am dealing with.


Hope it helps.

Error message "unreported exception; must be caught or declared to be thrown"

void showfile() throws  <-----

Your showfile() method throws IOException, so whenever you use it you have to either catch that exception or again thorw it. Something like:

try {
catch(IOException e) {

You should learn about exceptions in Java.

Get a list of all the files in a directory (recursive)

Newer versions of Groovy (1.7.2+) offer a JDK extension to more easily traverse over files in a directory, for example:

import static
def dir = new File(".");
def files = [];
dir.traverse(type: FILES, maxDepth: 0) { files.add(it) };

See also [1] for more examples.


Reverting single file in SVN to a particular revision

An alternate option for a single file is to "replace" the current version of the file with the older revision:

svn rm file.ext
svn cp svn://host/path/to/file/on/repo/file.ext@<REV> file.ext
svn ci

This has the added feature that the unwanted changes do not show up in the log for this file (i.e. svn log file.ext).

How can I remove jenkins completely from linux

If your jenkins is running as service instead of process you should stop it first using

sudo service jenkins stop

After stopping it you can follow the normal flow of removing it using commands respective to your linux flavour

For centos it will be

sudo yum remove jenkins

For ubuntu it will

sudo apt-get remove --purge jenkins

I hope this will solve your issue.

Two inline-block, width 50% elements wrap to second line

inline and inline-block elements are affected by whitespace in the HTML.

The simplest way to fix your problem is to remove the whitespace between </div> and <div id="col2">, see:

There are other possible solutions, see: bikeshedding CSS3 property alternative?

How to get input text value from inside td

Ah I think a understand now. Have a look if this really is what you want:

    $(this).closest('tr').find("input").each(function() {

This will give you all input values of a row.

To get the value of not all elements you can use :not():

$(this).closest('tr').find("input:not([name^=desc][name^=phone])").each(function() {

Actually I am not 100% sure whether it works this way, maybe you have to use two nots instead of this one combining both conditions.

T-SQL - function with default parameters

With user defined functions, you have to declare every parameter, even if they have a default value.

The following would execute successfully:

IF dbo.CheckIfSFExists( 23, default ) = 0
    SET @retValue = 'bla bla bla;

SQLiteDatabase.query method

        new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
        TABLE_ROW_ID + "=" + rowID,
        null, null, null, null, null

TABLE_ROW_ID + "=" + rowID, here = is the where clause. To select all values you will have to give all column names:

or you can use a raw query like this 
db.rawQuery("SELECT * FROM permissions_table WHERE name = 'Comics' ", null);

and here is a good tutorial for database.

css background image in a different folder from css

You are using a relative path. You should use the absolute path, url(/assets/css/style.css).

Get the Highlighted/Selected text

This solution works if you're using chrome (can't verify other browsers) and if the text is located in the same DOM Element:


Is it possible to focus on a <div> using JavaScript focus() function?

You can use tabindex

<div tabindex="-1"  id="tries"></div>

The tabindex value can allow for some interesting behaviour.

  • If given a value of "-1", the element can't be tabbed to but focus can be given to the element programmatically (using element.focus()).
  • If given a value of 0, the element can be focused via the keyboard and falls into the tabbing flow of the document. Values greater than 0 create a priority level with 1 being the most important.

How can I auto hide alert box after it showing it?

You can also try Notification API. Here's an example:

function message(msg){
    if (window.webkitNotifications) {
        if (window.webkitNotifications.checkPermission() == 0) {
        notification = window.webkitNotifications.createNotification(
          'picture.png', 'Title', msg);
                    notification.onshow = function() { // when message shows up
                        setTimeout(function() {
                        }, 1000); // close message after one second...
      } else {
        window.webkitNotifications.requestPermission(); // ask for permissions
    else {
        alert(msg);// fallback for people who does not have notification API; show alert box instead

To use this, simply write:


Instead of:


Note: Keep in mind that it's only currently supported in Chrome, Safari, Firefox and some mobile web browsers (jan. 2014)

Find supported browsers here.

Getting result of dynamic SQL into a variable for sql-server

DECLARE @sqlCommand nvarchar(1000)
DECLARE @city varchar(75)
declare @counts int
SET @city = 'New York'
SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75),@cnt int OUTPUT', @city = @city, @cnt=@counts OUTPUT
select @counts as Counts

how to modify an existing check constraint?

No. If such a feature existed it would be listed in this syntax illustration. (Although it's possible there is an undocumented SQL feature, or maybe there is some package that I'm not aware of.)

Tensorflow 2.0 - AttributeError: module 'tensorflow' has no attribute 'Session'

For TF2.x, you can do like this.

import tensorflow as tf
with tf.compat.v1.Session() as sess:
    hello = tf.constant('hello world')

>>> b'hello world

In Visual Basic how do you create a block comment

There is no block comment in VB.NET.

You need to use a ' in front of every line you want to comment out.

In Visual Studio you can use the keyboard shortcuts that will comment/uncomment the selected lines for you:

Ctrl + K, C to comment

Ctrl + K, U to uncomment

Load image with jQuery and append it to the DOM

after you get the image path, try either of following ways

  1. (as you need to set more attr than just the src) build the html and replace to the target region

    $('#target_div').html('<img src="'+ imgPaht +'" width=100 height=100 alt="Hello Image" />');
  2. you may need to add some delay if changing the "SRC" attr

    setTimeout(function(){///this function fire after 1ms delay
    }, 1);

Laravel csrf token mismatch for ajax POST Request

guys in new laravel you just need to do this anywhere. in JS or blade file and you will have csrf token.

var csrf = document.querySelector('meta[name="csrf-token"]').content;

it is vanilla JS. For Ajax you need to do this.

var csrf = document.querySelector('meta[name="csrf-token"]').content;
        url: 'my-own-url',
        type: "POST",
        data: { 'value': value, '_token': csrf },
        success: function (response) {

How to decode Unicode escape sequences like "\u00ed" to proper UTF-8 encoded characters?

$str = '\u0063\u0061\u0074'.'\ud83d\ude38';
$str2 = '\u0063\u0061\u0074'.'\ud83d';

// U+1F638
    "cat\xF0\x9F\x98\xB8" === escape_sequence_decode($str),
    "cat\xEF\xBF\xBD" === escape_sequence_decode($str2)

function escape_sequence_decode($str) {

    // [U+D800 - U+DBFF][U+DC00 - U+DFFF]|[U+0000 - U+FFFF]
    $regex = '/\\\u([dD][89abAB][\da-fA-F]{2})\\\u([dD][c-fC-F][\da-fA-F]{2})

    return preg_replace_callback($regex, function($matches) {

        if (isset($matches[3])) {
            $cp = hexdec($matches[3]);
        } else {
            $lead = hexdec($matches[1]);
            $trail = hexdec($matches[2]);

            $cp = ($lead << 10) + $trail + 0x10000 - (0xD800 << 10) - 0xDC00;

        // Characters between U+D800 and U+DFFF are not allowed in UTF-8
        if ($cp > 0xD7FF && 0xE000 > $cp) {
            $cp = 0xFFFD;

        // php_utf32_utf8(unsigned char *buf, unsigned k)

        if ($cp < 0x80) {
            return chr($cp);
        } else if ($cp < 0xA0) {
            return chr(0xC0 | $cp >> 6).chr(0x80 | $cp & 0x3F);

        return html_entity_decode('&#'.$cp.';');
    }, $str);

Run react-native on android emulator

You can also try use "doctor" command. It will fix most cases.

npx @react-native-community/cli doctor

Support for the experimental syntax 'classProperties' isn't currently enabled

According to this GitHub issue if you using create-react-app you should copy your .babelrc or babel.config.js configurations to webpack.config.js and delete those.because of htis two line of code babelrc: false,configFile: false, your config in babelrc,.. are useless. and your webpack.config.js is in your ./node_madules/react-scripts/config folder I solved my problem like this:

              test: /\.(js|mjs)$/,
              exclude: /@babel(?:\/|\\{1,2})runtime/,
              loader: require.resolve('babel-loader'),
              options: {
                babelrc: false,
                configFile: false,
                compact: false,
                presets: [
                    { helpers: true },

                  '@babel/preset-env', '@babel/preset-react'
                plugins: ['@babel/plugin-proposal-class-properties'],

EC2 instance has no public DNS

This is the tip provided to resolve the issue which does not work:

Tip - If your instance doesn't have a public DNS name, open the VPC console, select the VPC, and check the Summary tab. If either DNS resolution or DNS hostnames is no, click Edit and change the value to yes.

Assuming you have done this and you are still not getting a Public IP then go over to the subnet in question in the VPC admin screen and you will probably discover "Auto-Assign Public IP" is not set to yes. Modify that setting then, and I know you don't want to here this, create a new instance in that subnet. As far as I can tell you cannot modify this on the host, I tried and tried, just terminate it.

Remove carriage return in Unix

If you're using an OS (like OS X) that doesn't have the dos2unix command but does have a Python interpreter (version 2.5+), this command is equivalent to the dos2unix command:

python -c "import sys; import fileinput; sys.stdout.writelines(line.replace('\r', '\n') for line in fileinput.input(mode='rU'))"

This handles both named files on the command line as well as pipes and redirects, just like dos2unix. If you add this line to your ~/.bashrc file (or equivalent profile file for other shells):

alias dos2unix="python -c \"import sys; import fileinput; sys.stdout.writelines(line.replace('\r', '\n') for line in fileinput.input(mode='rU'))\""

... the next time you log in (or run source ~/.bashrc in the current session) you will be able to use the dos2unix name on the command line in the same manner as in the other examples.

Finding smallest value in an array most efficiently

                //smalest number in the array//
    double small = x[0];
    printf("\nThe smallest number is  %0.2lf  \n",small);

What is the difference between typeof and instanceof and when should one be used vs. the other?

var newObj =  new Object;//instance of Object_x000D_
var newProp = "I'm xgqfrms!" //define property_x000D_
var newFunc = function(name){//define function _x000D_
 var hello ="hello, "+ name +"!";_x000D_
 return hello;_x000D_
}_x000D_ = newProp;// add property_x000D_
newObj.func = newFunc;// add function_x000D_
console.log(;// call function_x000D_
// I'm xgqfrms!_x000D_
console.log(newObj.func("ET"));// call function_x000D_
// hello, ET!_x000D_
console.log(newObj instanceof Object);_x000D_

Looking for a good Python Tree data structure

It might be worth writing your own tree wrapper based on an acyclic directed graph using the networkx library.

How to compare datetime with only date in SQL Server

Of-course this is an old thread but to make it complete.

From SQL 2008 you can use DATE datatype so you can simply do:



Select * from [User] U
where  CONVERT(DATE,U.DateCreated) = '2014-02-07' 

Setting max-height for table cell contents

I've solved just using this plugin:

it automatically sets a max height to the target and adds three dots

CSS: Position loading indicator in the center of the screen

  position: fixed;_x000D_
  left: 0px;_x000D_
  top: 0px;_x000D_
  width: 100%;_x000D_
  height: 100%;_x000D_
  z-index: 9999;_x000D_
  background: url('//') _x000D_
              50% 50% no-repeat rgb(249,249,249);_x000D_
<div class="loader"></div>

How can I check if an array contains a specific value in php?

Following is how you can do this:

$rooms = ['kitchen', 'bedroom', 'living_room', 'dining_room']; # this is your array
if(in_array('kitchen', $rooms)){
    echo 'this array contains kitchen';

Make sure that you search for kitchen and not Kitchen. This function is case sensitive. So, the below function simply won't work:

$rooms = ['kitchen', 'bedroom', 'living_room', 'dining_room']; # this is your array
if(in_array('KITCHEN', $rooms)){
    echo 'this array contains kitchen';

If you rather want a quick way to make this search case insensitive, have a look at the proposed solution in this reply:


Custom designing EditText

Use the below code in your rounded_edittext.xml :

<?xml version="1.0" encoding="utf-8" ?>
<shape xmlns:android=""

    <stroke android:width="2dp"

    <corners android:radius="3dp" />

    <gradient   android:startColor="#C8C8C8"


Remote origin already exists on 'git push' to a new repository

If you have mistakenly named the local name as "origin", you may remove it with the following:

git remote rm origin

Creating new table with SELECT INTO in SQL

The syntax for creating a new table is

CREATE TABLE new_table
  FROM old_table

This will create a new table named new_table with whatever columns are in old_table and copy the data over. It will not replicate the constraints on the table, it won't replicate the storage attributes, and it won't replicate any triggers defined on the table.

SELECT INTO is used in PL/SQL when you want to fetch data from a table into a local variable in your PL/SQL block.

MySQL Query GROUP BY day / month / year

Complete and simple solution with similarly performing yet shorter and more flexible alternative currently active:

-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')

MySQL OPTIMIZE all tables?

for all databases:

mysqlcheck -Aos -uuser -p 

For one Database optimization:

mysqlcheck -os -uroot -p dbtest3

How to restart a node.js server

To say "nodemon" would answer the question.

But on how only to kill (all) node demon(s), the following works for me:

pkill -HUP node

How to specify a port number in SQL Server connection string?

Use a comma to specify a port number with SQL Server:,1234

It's not necessary to specify an instance name when specifying the port.

Lots more examples at It's saved me a few times.

Vertical Align text in a Label

This is what I usually do to "vertical align" text inside labels:

label {
   display: block;
   float: left;
   padding-top: 2px; /*This needs to be modified to fit */

It won't scale very nicely, but it works.

How to check if Thread finished execution

For a thread you have the myThread.IsAlive property. It is false if the thread method returned or the thread was aborted.

AngularJS directive does not update on scope variable changes

I needed a solution for this issue as well and I used the answers in this thread to come up with the following:

.directive('tpReport', ['$parse', '$http', '$compile', '$templateCache', function($parse, $http, $compile, $templateCache)
        var getTemplateUrl = function(type)
            var templateUrl = '';

            switch (type)
                case 1: // Table
                    templateUrl = 'modules/tpReport/directives/table-report.tpl.html';
                case 0:
                    templateUrl = 'modules/tpReport/directives/default.tpl.html';
                    templateUrl = '';
                    console.log("Type not defined for tpReport");

            return templateUrl;

        var linker = function (scope, element, attrs)

            scope.$watch('data', function(){
                var templateUrl = getTemplateUrl([0].typeID);
                var data = $templateCache.get(templateUrl);



        return {
            controller: 'tpReportCtrl',
            template: '<div>{{data}}</div>',
            // Remove all existing content of the directive.
            transclude: true,
            restrict: "E",
            scope: {
                data: '='
            link: linker

Include in your html:

<tp-report data='data'></tp-report>

This directive is used for dynamically loading report templates based on the dataset retrieved from the server.

It sets a watch on the property and whenever this gets updated (when the users requests a new dataset from the server) it loads the corresponding directive to show the data.