Uneasy way to determine proxy server

Open a command prompt and perform the following steps:

1. Inspect the output of netstat -an | find "EST" (short from 'ESTABLISHED')
2. Go to a fresh site (one that you have not recently visited)
3. Run the netstat command again, looking for the new connection. It might look like:
   TCP 192.168.1.1:1989 192.168.1.88:8080 ESTABLISHED

(credits go to Royce Williams)

Alternatively, install TCPView and look at the remote address and remote port columns for the browser connections.

Configure Reporting Services proxy for Bing maps

Add the following XML element to the web.config of both folders:
...\MSSQL\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\
...\MSSQL\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\

Add at the end between the runtime close tag and configuration close tag:

</runtime>
<system.net>
     <settings>
          <ipv6 enabled="true" />
      </settings>
      <defaultProxy enabled="true" useDefaultCredentials="true">
           <proxy bypassonlocal="True" proxyaddress="http://:"/>
       </defaultProxy>
</system.net>
</configuration>

It might be needed to restart the Microsoft Reporting Services service.

Change records per page in Master Data Services

To change the number of records/rows per page in the Explorer view of Microsoft Master Data Services (MDS), do the following:

• Select an entity
• Click on Settings in the top-right corner
• Set "records per page"

Note:
This has to be done per entity and each time the entity is edited. It is not possible to set the records per page one time for all entities.

To change the number of entries in a dropdown list for all entities:

• Edit table master_data_services.dbo.tblSystemSetting
• Update the SettingValue for SettingName "DBAListRowLimit" to the desired value

Cannot map network drive to Sharepoint library

Example error: "Your client does not support opening this list with Windows Explorer"

Steps to check beforehand:

• Add Sharepoint web address to trusted sites within IE
• Login to Sharepoint with "remember this login" turned on

Solutions:

• Use IE 32-bit instead of the 64-bit version, if on a 64-bit Windows:
  c:\program files (x86)\internet explorer\iexplore.exe
• Install Desktop Experience http://technet.microsoft.com/en-us/library/cc754314.aspx
  It is a feature that can be found in the server manager
• Uninstall IE10 - Windows automatically reverts back to IE 9 - or apply hotfix:
  http://support.microsoft.com/kb/2846960

Unable to read exported Excel workbook because of illegal character

After exporting from reporting services, Excel opens the workbook with one of the following errors:

"Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 1, column 36242."

"Excel found unreadable content in..."

SOLUTION

1. Rename the Excel workbook from *.xlsx to *.zip
2. Within the zip file, open the folder \xl\worksheets and open file sheet1.xml in a text editor, i.e. Notepad++
3. Use Notepad++'s GOTO feature to go to position 36242 and determine the offending character
4. Replace this character in the report's source, i.e. by using the SQL REPLACE function if the source is a plain SQL query
   Note: this is a reactive measure. Each time the error occurs, the character needs to be determined and replaced

For example, these Unicode characters cannot be part of the value in any field in the report:

code: visual representation
26: [SUB]
191: ¿

OCR a (region of a) PDF using C# and "freeware"

Perform the following steps:

1) Install Ghostscript to make conversion of PDF possible:
https://code.google.com/p/ghostscript/downloads/list

2) Install Imagemagick to convert more easily from PDF to JPG:
http://www.imagemagick.org/script/binary-releases.php#windows

3) Install MODI using Sharepoint Designer 2007, if the Office version on the system is more recent then 2007:
Download Sharepoint Designer 2007
Start setup, custom, disable all
Then select all options under Microsoft Office Document Imaging

By default (on English Windows) only four English-alike languages can be recognized, so install other language packs, e.g. for Japanese or Chinese if needed. In Windows 7 Enterprise or Ultimate this can be done using the "optional updates" from the Windows Update tool In other versions additional languages are not available.

4) From C#, add COM reference to Microsoft Office Document Imaging

5a) Convert a PDF page to JPG from code by executing the following command line syntax:

Whole page:
convert -type grayscale -density 300 jp.pdf[0] jp.jpg

Region of the page:

convert -type grayscale -density 300 jp.pdf[0] -crop 600x600+50+50 jp_crop.jpg

5b) Convert the JPG image to text

Example C# code:

MODI.Document d = new MODI.Document();
d.Create(@"c:\tmp\image\jp_crop.jpg");
d.OCR(MODI.MiLANGUAGES.miLANG_ENGLISH, false, false);
MODI.Image i = d.Images[0];
Debug.WriteLine(i.Layout.Text.ToString());

Note:

• If the OCR statement returns a "bad language" error, then install the requested language pack
• If the OCR statement returns a "document not ready" error, then uninstall and reinstall MODI by using remove programs and rerunning the Sharepoint Designer setup
• The language of the text that is parsed needs to be known and set in the OCR function

Analysis Services fails with "attribute key cannot be found" on Unicode field

ERROR
The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'city', Value: '　'. The attribute is 'City'.

OR

The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'customername', Value: 'ﾊﾟﾅｿﾆｯｸ（株）'. The attribute is 'Customername'.

CAUSE
The problem is caused by the Unicode nature of the SPACE character in the text field in the database. In the field are for example Kanji characters and unicode has about 18 characters for SPACE:

http://www.fileformat.info/info/unicode/category/Zs/list.htm

There is no way to tell Analysis Services or SQL Server the difference between these spaces. In short: collation, case sensitivity, accent sensitivity, width sensitivity or Kana sensitivity have no influence.

WORKAROUND
Convert all the kind of SPACE characters to one SPACE character, e.g:
replace(ad_.ad_name,' ',' ') as ad_name

This appears to do nothing, but the first empty space finds ALL space characters, e.g. ASCII 32 and Unicode 12288 and the second parameter converts it to ONE space (Unicode 32)

Openrowset fails with "network access was interruped"

A SQL Server openrowset query using ODBC to retrieve data from an Excel spreadsheet fails with the error:

"Your network access was interrupted. To continue close the database and then open it again."

Example Openrowset query (Excel 2010+):

SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=C:\temp\test.xlsx;Uid=;pwd=;','SELECT * FROM [memberlist$]')

Solution:

• Disable all network connections and try again. Only works if the frontend (e.g. Management Studio) and the backend (SQL Server database) are on the same machine
• Change SQL Server service user to an NT account

Remarks:

• In some cases, activating the network connections again will prevent the error from occurring
• In some cases, changing the NT account to the "local service" account will prevent the error from occurring
• The MSDASQL keyword indicates that openrowset will use the ODBC driver in stead of an OLE DB driver

Packing intervals

Solution to the "packing intervals" problem by Itzik Ben-Gan:
http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx

Scroll to code listing 4 for the SQL code.

Registration is required to copy and paste the code. It can only be copy and pasted from the PDF that can be downloaded after registration.

Execute query under cursor

There is no way in SQL Server Management Studio (SSMS) to execute the query under the cursor. There is the option to execute the entire script with F5 or CTRL+E, but the script might contain multiple SQL statements.

The quickest solution is to install a freeware add-in:

• Download dbForge SQL Complete:
  Note: registration is required
• Install the add-in

The option for auto-capitalization and intellisense is enabled by default. This can be disabled, so only the "execute query under the cursor" (CTRL+SHIFT+E) feature remains:

• Start SMSS
• SQL Complete -> Options -> General -> uncheck "Enable SQL Complete"

Count of weekend days in date range

Use the formula in "calc_name" or "calc_df" to calculate the number of inclusive weekend dates in a date range. Inclusive means that if e.g. the start date is a Saturday, it is counted as 1. The code is pure SQL Server T-SQL, with no need for helper tables or cursors.

Note:

• "Set datefirst" is not needed, but can be used to check the correct outcome of the "calc_df" calculation
• The "calc_df" function is more robust, since it does not rely on localized day names. But it is a bit more verbose and obfuse then "calc_name"
• Column "correct" contains the correct number of weekend dates that can be used as a reference for the calculation
• Subquery X contains some random test values for start- and enddates

SET DATEFIRST 7

SELECT
x.*,
(DATEDIFF(wk, sd, ed) * 2)
  +(CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END)
  +(CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END) as calc_name,
DATEDIFF(wk, sd, ed) * 2
  +CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END
  +CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as calc_df,
@@DATEFIRST as datefirst,
(DATEDIFF(wk, sd, ed) * 2) as weeks,
CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END as sun_start,
CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END as sun_start_df,
CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END as sat_end,
CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as sat_end_df,
DATEPART(dw, sd)+@@datefirst as sun_df,
DATEPART(dw, ed)+@@datefirst as sat_df
from
(
select 1 as correct, '2013/3/17' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/17' as ed union
select 3 as correct, '2013/3/16' as sd, '2013/3/23' as ed union
select 3 as correct, '2013/3/10' as sd, '2013/3/22' as ed
) x

Quipu JDBC connection to MS Access

There are several ways to connect to a MS Access database from Java, like the low-level MS Access library called Jackcess: http://jackcess.sourceforge.net/

The downside is that most Java applications that support heterogeneous connections have a default built-in procedure to connect and open the database. Most of the time, the "getSchemanames" function is called and this one doesn't function as expected with MS Access. Quipu uses this method also.

Therefore, a connection can be made in the following way, but results in an error when retrieving the schema names during reverse engineering with Quipu from MS Access

1) JDBC to ODBC bridge

Create a connection to the MS Access database d:\test.mdb using a native and DSN-less JDBC to ODBC bridge:

Manage connection types:
Add a new connection type:

name= ms access jdbc
type= jdbc
database or file delimiter=
database or file required= false
jdbc port delimiter= :
jdbc default port=
jdbc driver= sun.jdbc.odbc.JdbcOdbcDriver
url type= jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=

Manage connections:
Add a new connection:

name: ms access jdbc
database= test.mdb
hostname= d
port= /

Notes:

• The url type above is for Java x64 and MS Access x64. If Java x32 is used, then the url type= jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=
• No additional jdbc driver (jar file) needs to be installed, because the driver is embedded in the Java JRE runtime

There is an alternative method:

2) UCanAccess

It is a open source database driver written by Marco Amadei that behaves identical to the JDBC to ODBC bridge, but
- doesn't use ODBC
- handles the getschemanames call, using a default "PUBLIC" schema

Download it here: http://sourceforge.net/projects/ucanaccess

To use it with Quipu:
- Extract the jar files from the \lib folder in the zip file to Quipu's \webapps\qp\WEB-INF\lib folder
- Sort files by name and make sure no duplicates exist, e.g. hsqldb and hsqldb-1.8.0 is not allowed.
- Remove the duplicate with the lowest versionnumber. In the case of hsqldb, which has no versionnumber: keep the one from the UCanAccess zipfile

Manage connection types:

Add a new connection type:
name= ucanaccess
type= jdbc
database or file delimiter=
database or file required= false
jdbc port delimiter= :
jdbc default port=
jdbc driver= net.ucanaccess.jdbc.UcanaccessDriver
url type= jdbc:ucanaccess://

Manage connections:

Add a new connection:
name: ucanaccess
database= test.mdb;showschema=true
hostname= d
port= /

Notes:

- The "showschema=true" property bypasses the getSchemanames error

Uninstall Microsoft Security Essentials

Microsoft Security Essentials can be uninstalled from Windows 7 as follows:

• Download the installer from the Microsoft website (currently: http://windows.microsoft.com/en-US/windows/security-essentials-download)
• Save the resulting file as "c:\mseinstall.exe"
• Win + R, "cmd", enter, type in "c:\mseinstall.exe mssefullinstall-amd64fre-en-us-vista-win7.exe /U"
• To reinstall, just start mseinstall.exe

Flash 11.4 in SRWare IronPortable v22

To get Flash support in the portable version of Iron v22:

• Start IronPortable and type "about:plugins" in the URL bar
• Scroll to the section titled "Flash"
• If there is one enabled item within that section, then Flash should already work. Test it here: http://www.adobe.com/software/flash/about/
• If the section does not exist or there are no items in it, then 
• Create a folder called "iron\plugins" in the IronPortable folder
• Download the file NPSWF32_11_4_402_278.dll from the internet or copy it from the system Flash installation folder and put it in the previously created folder.
  Example system Flash installation folder: C:\Windows\SysWOW64\Macromed\Flash\NPSWF32_11_4_402_287.dll
• If there are more items, then 
• Disable all items but one for Flash v11.4
• Restart iron and type "about:plugins"
• If all Flash items are disabled, then enable one Flash item

Note: putting file gcswf32.dll in the root of the IronPortable folder does no longer work!

Stop Outlook from reformatting telephone numbers

A telephone number gets reformatted when adding it to a contact in Outlook, e.g. "+31.." becomes "31".

This behaviour can be stopped as follows:

• Start, Search: "phone" and hit enter. If this doesn't work, then start the Telephony service:
• Start, Search: "services" and hit enter - Double click Telephony (TapiSvr)
• Startup type: automatic
• Click Start
• In the Phone and Modem screen, select a location or, if required, make a new location and use anything for the area code
• Click Edit..
• Country/region: Internation Freephone Service
• (Stop and) start Outlook

Note: it doesn't stop reformatting completely.

E.g. "+31-12345678" still gets reformatted to "+31 12345678", but at least the starting "+" sign remains untouched. And "+31612345678" gets reformatted to "+31612345678 "

Epson AL-C900 with empty color toner

The Epson AL-C900 Aculaser has a default setting that makes it impossible to continue printing if one of the color toners/consumables is empty. Even in black and white.

This can be fixed as follows:

• Connect the printer directly, i.e. via USB to the PC or laptop and turn the printer on
• Install "Status Monitor 3 1.1b" from the Epson website:
  http://esupport.epson-europe.com/FileDetails.aspx?lng=en-GB&id=31761
  The name of the downloaded file is: epson31761eu.exe
  Note:  there is no separate Windows 7 x64 version, so use the Windows Vista x64 version for that platform
• Right-click the "Status monitor 3" icon in the tray and:
• Epson AL-C900 -> Printer Setting...
• Check "Continue printing when consumables are empy"

MS Access DSN-less connection

It is possible to create a DSN less linked table and/or pass-through query from Microsoft Access to SQL Server Express without deleting those objects first, as is often suggested, e.g. here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

This is safer, because the linked objects will be gone if the code fails after deleting the objects, but before re-adding has completed.

One of the downsides of this approach is that MS Access has to be re-started for the new connections to be effected. In more detail: Access caches ODBC connections. For example, when a working ODBC connection of a linked table is replaced with one that has a wrong username, it will still work, because the previous one is retrieved from cache. It is not possible to clear this cache programmatically or manually, unless Access is restarted. Furthermore, Access only adds new connections to the cache when the driver, server or database property changes. Adding a new and correct ODBC connection to a linked table that only as a different login than the old one has no effect! The old connection from the cache will be used.

Another downside is that the database grows significantly after each execution of the code. A compact/repair resolves this. Set the "compact on close" property on the database.

Use this code to loop over all tables and queries to set the connection string:

Dim db As Database
Dim td As TableDef
Dim qd As QueryDef
Dim strODBC As String

  Set db = DBEngine(0)(0)
  strODBC = "ODBC;DRIVER=SQL Server;SERVER=REMOTESERVER\SQLEXPRESS;DATABASE=TestDB;UID=sa;PWD=sa;Trusted_Connection=No;"

  ' reconnect pass-through queries
  For Each qd In db.QueryDefs
    With qd
      If Left$(.Connect, 4) = "ODBC" Then
        ' note: for a passthrough query the uid and pwd are always stored
        .Connect = strODBC 
        .Close
      End If
    End With
Next qd

  ' reconnect linked tables
  For Each td In db.TableDefs
    With td
      If Left$(.Connect, 4) = "ODBC" Then
        ' note: for a linked table the uid and pwd are only stored when this property is set!
        .Attributes = dbAttachSavePWD
        .Connect = strODBC
        .RefreshLink
      End If
    End With
  Next td

  db.Close
  Set db = Nothing

Notes:

• Use DBEngine(0)(0) in stead of CurrentDB(); the implementation is 1000x faster
• Use Left$ in stead of Left; the implementation is faster
• This particular code assumes that DAO is used, not ADO
• The connection uses a SQL Server Authentication instead of Windows Authentication
• The connection string value is assigned to variable strODBC
• The connection string has to start with the text "ODBC;" both for the linked table as the pass-through query
• If the connection is to a default instance of SQL Server, the string "\SQLEXPRESS" can be removed
• By default, the username and password are stored for a pass-through query, but not for the linked table. This appears to be a bug, it either should be set for both or for neither. Setting the uid/pwd parameters for a linked table does, however, not result in an error
• ... but if the code td.Attributes = dbAttachSavePWD is used, then the uid/pwd are also stored for a linked table. At least for MS Access 2010 on Windows 7 x64, this is enough. In some cases two registry settings need to be set for this to work:
  REGEDIT4
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network]
  "DisablePwdCaching"=dword:00000000
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security]
  "DisablePwdCaching"=dword:00000000
• After the code has run, the connection string has been set for all the objects, but is not yet effective! For this, MS Access needs to be closed and reopened
• I found that the reconnecting loop using DRIVER=SQL Server in stead of
  DRIVER={SQL Server}or
  PROVIDER={SQL Server Native Client 10.0 ODBC Driver}
  was much faster
• Close and destroy the reference to the database object to prevent memory leaks

Related errors might include:

"login failed. The login is from untrusted domain and cannot be used with windows authentication"

PALO backend is unavailable

You might get an "backend is unavailable" error when connecting to the PALO server from an internet page.

Check the %SYSTEMDRIVE%\Program Files (x86)\Jedox\Palo Suite\log\core.log file for a probable cause. In my case the fonts folder did not exist:

[2012/05/02 10:22:28] ERROR [core] palo web core server version boost::filesystem::basic_directory_iterator constructor: The system cannot find the path specified: "C:\WINDOWS\Fonts"

The reason for this error is that Windows and the program files folder are installed on my G drive and PALO apparently hardcodedly searches the C drive for these fonts. The service PaloWebCoreServerService cannot start because of this error.

Resolution:

• Create a folder called c:\windows\fonts
• Copy at least the following fonts from the g:\windows\fonts folder:
  andalemo.ttf
  arial.ttf
  arialbd.ttf
  comic.ttf
  courier.ttf
  georgie.ttf
  impact.ttf
  times.ttf
  trebuc.ttf
  verdana.ttf
  webdings.ttf
  If any of these fonts is not available in the folder, then download them from Microsoft:
  http://sourceforge.net/projects/corefonts/files/the%20fonts/final/
  
• Start the service PaloWebCoreServerService

Bamboo Pen & Touch supported driver not found

If you try to start the Wacom Bamboo Preferences application, but get an error message displaying "a supported tablet could not be found", then check if the service "Wacom Consumer Touch Service" is enabled and running.

Oracle OLE DB source in SSIS

An OLE DB source to Oracle can be added to a SSIS package.

Example data

Server: locksnlatches.mydomain.com
Oracle instance/SID: dwh
Username/schema: scott
Password: tiger

An OLE DB source to Oracle can be added as follows:

• From the menubar, select SSIS -> New connection... -> OLEDB -> Add -> New
  Provider: Oracle Provider for OLE DB
  Server or filename: locksnlatches/dwh
  User name: scott
  Password: tiger

Notes: 

• The "Oracle Provider for OLE DB" provider always exists in SSIS. It is a wrapper around the Oracle Client driver that is installed when installing Integration Services. However, the Oracle Client has to be installed separately. It can be downloaded from the Oracle website: instantclient-basiclite-nt-11.2.0.2.0. Install the 32 bit version of the driver to be able to use it in the design environment. The Visual Studio / SSIS IDE does not support 64 bit components
• It is possible to use the 64 bit Oracle Client driver during execution of the package, e.g. via dtexec, but it needs to be installed additionally. The SSIS package has to be configured to use this different version during runtime.
• If the server cannot be found, then use the fully qualified name. One reason might be that the Oracle server is located in a different (Windows) domain as the SSIS server.