Stored procedure working when run manually, not running in SQL Agent Job XML sp_OACreate

down vote
I had this same issue when google geocoding and parsing the XML result. Seems that when run via an SQL job the XML result was being truncated.

Found the solution here:

Had to put:

SET TEXTSIZE 2147483647;
at the start of my stored proc.

The decision: in the procedure beginning to write the instruction

SET TEXTSIZE 2147483647

Or any other size, sufficient what to find room returned http the server for the answer.
The reason of such behavior of the server, I think, it is clear.
The moderator: and for zoom-in – the warning

Detect hashchange and hash existed code run duplicate?

var hash = ”;
$(document).load(function() {

$(window).on(‘hashchange’, function() {
if (hash == window.location.hash) {
// do nothing, hash is the same as it was previously

hash = window.location.hash;

if (hash) {

Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio

Changing the Setting From The Tools Menu
In the Options dialog box of Tools Menu, expand Query Results, expand SQL Server and then select Results to Text as shown in the image below. In the right side panel change the value of Maximum number of characters displayed in each column to 8192. Click OK to save the changes as shown in the image below. The changes will go into effect once you open a new query window.


Turn svg to font icons

IcoMoon is striving to build and provide the best iconography and icon management tool for perfectionists. IcoMoon’s icon library features only the very best icon sets out there. All of our icons are designed on a precise pixel grid. The IcoMoon app lets you build and use your own icon packs in many different formats including SVG, Polymer, PDF, XAML, CSH, icon font or simple PNG/CSS sprites.

Send excel file to user in web

Private Sub ExportFile(ByVal strfilePath As String, ByVal strFilename As String)
Response.ContentType = “application/”
strfilePath = Server.MapPath(strfilePath)
If Not System.IO.File.Exists(strfilePath) Then
strfilePath = Server.MapPath(“~//Data//ExportToExcel//NoRecord.xls”)
Response.AddHeader(“content-disposition”, (“attachment;filename=” + (“NoRecord” + “.xls”)))
Response.AddHeader(“content-disposition”, (“attachment;filename=” _
+ (strFilename + “.xls”)))
End If

Dim sourcefile As FileStream = New FileStream(strfilePath, FileMode.Open)
Dim filesize As Long
filesize = sourcefile.Length
Dim getcontent() As Byte = New Byte((CType(filesize, Integer)) – 1) {}
sourcefile.Read(getcontent, 0, CType(sourcefile.Length, Integer))
Catch ex As Exception
ex = ex
End Try

End Sub

Export data from a DataSet into a real Excel 2007 file Open XML SDK

Ever wanted to add an “Export to Excel” function to your ASP.Net, WinForms or WPF application ?

This free C# and VB.Net library lets you export a DataTable or DataSet of data into a “real” Excel 2007 .xlsx file, using one line of code.

From the Microsoft Open XML SDK library
From the Microsoft .Net Framework library


Public Class CreateExcelFile
Public Shared Function CreateExcelDocument(Of T)(ByVal list As List(Of T), ByVal xlsxFilePath As String) As Boolean
Dim ds As New DataSet()

Return CreateExcelDocument(ds, xlsxFilePath)
End Function

‘ This function is adapated from:
‘ My thanks to Carl Quirion, for making it “nullable-friendly”.
Public Shared Function ListToDataTable(Of T)(ByVal list As List(Of T)) As DataTable

Dim dt As New DataTable
Dim row As DataRow
For Each info As System.Reflection.PropertyInfo In list.GetType().GetProperties()
dt.Columns.Add(New DataColumn(info.Name, GetNullableType(info.PropertyType)))

For Each tValue As T In list

row = dt.NewRow()
For Each info As System.Reflection.PropertyInfo In list.GetType().GetProperties()

If Not IsNullableType(info.PropertyType) Then
row(info.Name) = info.GetValue(tValue, Nothing)
row(info.Name) = info.GetValue(tValue, Nothing)
End If
Return dt
End Function

Public Shared Function GetNullableType(ByVal t As Type) As Type

Dim returnType As Type = t

If (t.IsGenericType Or t.GetGenericTypeDefinition() Is GetType(Nullable(Of ))) Then
returnType = Nullable.GetUnderlyingType(t)
End If

Return returnType

End Function

Public Shared Function IsNullableType(ByVal type As Type) As Boolean

Return (type Is GetType(String) Or
type.IsArray Or
(type.IsGenericType And type.GetGenericTypeDefinition() Is GetType(Nullable(Of ))))
End Function

Public Shared Function CreateExcelDocument(ByVal dt As DataTable, ByVal xlsxFilePath As String) As Boolean

Dim ds As New DataSet

Return CreateExcelDocument(ds, xlsxFilePath)
End Function

Public Shared Function CreateExcelDocument(ByVal ds As DataSet, ByVal excelFilename As String) As Boolean
Using document As SpreadsheetDocument = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook)

Dim workbook As WorkbookPart = document.AddWorkbookPart

‘ document.AddWorkbookPart()
document.WorkbookPart.Workbook = New DocumentFormat.OpenXml.Spreadsheet.Workbook()

‘ My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
document.WorkbookPart.Workbook.Append(New BookViews(New WorkbookView()))

‘ If we don’t add a “WorkbookStylesPart”, OLEDB will refuse to connect to this .xlsx file !
Dim workbookStylesPart As WorkbookStylesPart = document.WorkbookPart.AddNewPart(Of WorkbookStylesPart)(“rIdStyles”)

Dim stylesheet As New Stylesheet
workbookStylesPart.Stylesheet = stylesheet

‘ Dim sp As WorkbookStylesPart = document.WorkbookPart.AddNewPart(Of WorkbookStylesPart)()

CreateParts(ds, document)

End Using
‘Trace.WriteLine(“Successfully created: ” + excelFilename)
Return True

Catch ex As Exception
‘ Trace.WriteLine(“Failed, exception thrown: ” + ex.Message)
Return False
End Try

End Function

Private Shared Sub CreateParts(ByVal ds As DataSet, ByVal spreadsheet As SpreadsheetDocument)

‘ Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
Dim worksheetNumber As UInt64 = 1
For Each dt As DataTable In ds.Tables
‘ For each worksheet you want to create
Dim workSheetID As String = “rId” + worksheetNumber.ToString()
Dim worksheetName As String = dt.TableName

Dim newWorksheetPart As WorksheetPart = spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
newWorksheetPart.Worksheet = New DocumentFormat.OpenXml.Spreadsheet.Worksheet()

‘ If you want to define the Column Widths, you need to do this *before* appending the SheetData

‘ If you want to calculate the column width, it’s not easy. Have a read of this article:

Dim columnWidthSize As Int32 = 20 ‘ Replace the following line with your desired Column Width for column # col
Dim columns As New Columns

For colInx As Integer = 0 To dt.Columns.Count
Dim column As Column = CustomColumnWidth(colInx, columnWidthSize)

‘ create sheet data
newWorksheetPart.Worksheet.AppendChild(New DocumentFormat.OpenXml.Spreadsheet.SheetData())

‘ save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart)

‘ create the worksheet to workbook relation
If (worksheetNumber = 1) Then
spreadsheet.WorkbookPart.Workbook.AppendChild(New DocumentFormat.OpenXml.Spreadsheet.Sheets())
End If

Dim sheet As DocumentFormat.OpenXml.Spreadsheet.Sheet = New DocumentFormat.OpenXml.Spreadsheet.Sheet
sheet.Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart)
sheet.SheetId = worksheetNumber
sheet.Name = dt.TableName
‘ Sheets.Append(sheet)

spreadsheet.WorkbookPart.Workbook.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Sheets).Append(sheet)
‘ AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
End Sub

Private Shared Sub WriteDataTableToExcelWorksheet(ByVal dt As DataTable, ByVal worksheetPart As WorksheetPart)

Dim worksheet As Worksheet = worksheetPart.Worksheet
Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()

Dim cellValue As String = “”

‘ Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.

‘ We’ll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
‘ cells of data, we’ll know if to write Text values or Numeric cell values.
Dim numberOfColumns As Integer = dt.Columns.Count
Dim IsNumericColumn(numberOfColumns) As Boolean

Dim excelColumnNames([numberOfColumns]) As String

For n As Integer = 0 To numberOfColumns
excelColumnNames(numberOfColumns) = GetExcelColumnName(n)
Next n

‘ Create the Header row in our Excel Worksheet

Dim rowIndex As UInt32 = 1

Dim headerRow As Row = New Row
headerRow.RowIndex = rowIndex ‘ add a row at the top of spreadsheet

For colInx As Integer = 0 To numberOfColumns – 1
Dim col As DataColumn = dt.Columns(colInx)
AppendTextCell(excelColumnNames(colInx) + “1”, col.ColumnName, headerRow)
IsNumericColumn(colInx) = (col.DataType.FullName = “System.Decimal”) Or (col.DataType.FullName = “System.Int32”)

‘ Now, step through each row of data in our DataTable…

Dim cellNumericValue As Double = 0

For Each dr As DataRow In dt.Rows
‘ …create a new row, and append a set of this row’s data to it.
rowIndex = rowIndex + 1
Dim newExcelRow As New Row
newExcelRow.RowIndex = rowIndex ‘ add a row at the top of spreadsheet

For colInx As Integer = 0 To numberOfColumns – 1
cellValue = dr.ItemArray(colInx).ToString()

‘ Create cell with data
If (IsNumericColumn(colInx)) Then
‘ For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
‘ If this numeric value is NULL, then don’t write anything to the Excel file.
cellNumericValue = 0
If (Double.TryParse(cellValue, cellNumericValue)) Then
cellValue = cellNumericValue.ToString()
AppendNumericCell(excelColumnNames(colInx) + rowIndex.ToString(), cellValue, newExcelRow)
End If
‘ For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames(colInx) + rowIndex.ToString(), cellValue, newExcelRow)
End If


End Sub

Private Shared Function CustomColumnWidth(ByVal columnIndex As Integer, ByVal columnWidth As Double) As Column
‘ This creates a Column variable for a zero-based column-index (eg 0 = Excel Column A), with a particular column width.
Dim column As New Column
column.Min = columnIndex + 1
column.Max = columnIndex + 1
column.Width = columnWidth
column.CustomWidth = True
Return column
End Function

Public Shared Sub AppendTextCell(ByVal cellReference As String, ByVal cellStringValue As String, ByVal excelRow As Row)
‘/ Add a new Excel Cell to our Row
Dim cell As New Cell
cell.CellReference = cellReference
cell.DataType = CellValues.String

Dim cellValue As New CellValue
cellValue.Text = cellStringValue


End Sub

Public Shared Sub AppendNumericCell(ByVal cellReference As String, ByVal cellStringValue As String, ByVal excelRow As Row)
‘/ Add a new Excel Cell to our Row
Dim cell As New Cell
cell.CellReference = cellReference
cell.DataType = CellValues.Number

Dim cellValue As New CellValue
cellValue.Text = cellStringValue


End Sub

Public Shared Function GetExcelColumnName(ByVal columnIndex As Integer) As String
If (columnIndex < 26) Then
Return Chr(Asc("A") + columnIndex)
End If

Dim firstChar As Char,
secondChar As Char

firstChar = Chr(Asc("A") + (columnIndex \ 26) – 1)
secondChar = Chr(Asc("A") + (columnIndex Mod 26))

Return firstChar + secondChar
End Function

End Class

MicrosoftAjax.js: Cannot read property ‘_notified’ of null

The problem is that the browser detection code in MicrosoftAjax.js does not detect WebKit (gasp!). Just include this javascript in your site and that should take care of it.

Sys.Browser.WebKit = {};
if (navigator.userAgent.indexOf(‘WebKit/’) > -1) {
Sys.Browser.agent = Sys.Browser.WebKit;
Sys.Browser.version = parseFloat(navigator.userAgent.match(/WebKit\/(\d+(\.\d+)?)/)[1]); = ‘WebKit’;

Dynamic Web Service JSON return

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Web.Script.Serialization
Imports System.Runtime.Serialization.Json
Imports System.IO

Public Class WS
Inherits System.Web.Services.WebService
Private strConn As String

Public Function GetSummaryDynamic(ByVal startrowindex As String, ByVal maximumrows As String, ByVal searchstring As String, ByVal sortby As String) As String

Dim dtResults As New DataTable
Dim ms As New MemoryStream()

Using sqlConn As IDbConnection = CreateConnection(ConnStr(0))
Using sqlCmd As IDbCommand = sqlConn.CreateCommand
sqlCmd.CommandText = “SPP_SUMMARY_ENQ”
sqlCmd.CommandType = CommandType.StoredProcedure
clsCONN.addInputParam(sqlCmd, “startrowindex”, startrowindex, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “maximumrows”, maximumrows, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “searchstring”, searchstring, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “sortby”, sortby, clsCONN.dtType.dbString)
Using sqlReader As IDataReader = sqlCmd.ExecuteReader
End Using
End Using
End Using

Dim strJson As String = DataTableToJSONWithJavaScriptSerializer(dtResults)
Return strJson
Catch ex As Exception
Throw (New ExceptionMsg(GenerateErrNumber(), ex))
End Try
End Function

Public Function DataTableToJSONWithJavaScriptSerializer(ByVal table As DataTable) As String
Dim jsSerializer As JavaScriptSerializer = New JavaScriptSerializer
Dim parentRow As List(Of Dictionary(Of String, Object)) = New List(Of Dictionary(Of String, Object))
Dim childRow As Dictionary(Of String, Object)
For Each row As DataRow In table.Rows
childRow = New Dictionary(Of String, Object)
For Each col As DataColumn In table.Columns
childRow.Add(col.ColumnName, row(col))
Return jsSerializer.Serialize(parentRow)
End Function
#End Region

Get huge email list from Active Directory

DirectoryEntry entry = new DirectoryEntry(“Domain”, “UserName”, “Password”, AuthenticationTypes.None);
DirectorySearcher dSearch = new DirectorySearcher(entry);
dSearch.SearchScope = System.DirectoryServices.SearchScope.Subtree;
dSearch.ReferralChasing = ReferralChasingOption.All;
dSearch.PageSize = 5000;
dSearch.Filter = “(&(objectClass=user))”;
foreach (SearchResult sResultSet in dSearch.FindAll())
if (sResultSet.Properties[“mail”].Count > 0)
strClientEmails += sResultSet.Properties[“mail”][0].ToString() + “,”;

Kill All Active Connections To A Database

To kill all the connections to your database you can use the ALTER DATABASE command. Sometimes you want to restore the database from a backup but can’t because people are connected. Here is one way which will kick off all the users immediately


–do you stuff here


The code below will wait 60 seconds


–do you stuff here

The reason this is better than looping over sysprocesses or sys.dm_exec_sessions is that nobody will connect while that looping piece of code runs