Group multiple records into one and concatenate the values of the string/text values of a field:
ALTER view [dbo].[vcomments] as
with x as
(
select
cmt_domain,
cmt_indx,
cmt_seq,
concat(cmt_cmmt1,cmt_cmmt2,cmt_cmmt3,cmt_cmmt4,cmt_cmmt5,cmt_cmmt6,cmt_cmmt7,cmt_cmmt8,cmt_cmmt9,cmt_cmmt10,cmt_cmmt11,cmt_cmmt12,cmt_cmmt13,cmt_cmmt14,cmt_cmmt15) as cmt_cmmt,
cmt_lang
from
v_cmt_det_act
)
select
cmt_domain,
cmt_indx,
--FOR XML PATH returns a one record XML datatype in which all records are "unioned".
-- However, special characters are encoded as excape sequences, i.e. "<" becomes "<"
-- To reverse the encoding, get the entire root document/element as a value using the VALUE function.
-- The [1] is required because VALUE expects a singleton, i.e. exactly ONE element
rtrim(substring(
(
select char(10) + '[PAGE ' + cast(x2.cmt_seq as nvarchar(2)) + ']' + char(10) + x2.cmt_cmmt -- a field with no name gets no XML element to enclosed it. In short, the XML tag is removed
from x x2
where x1.cmt_domain = x2.cmt_domain and x1.cmt_indx = x2.cmt_indx
order by x2.cmt_seq
for XML PATH (''), type -- the brackets indicate the XML root element. Specifying an empty string as root element name removes it
).value('(/)[1]','nvarchar(max)')
,2,100000)) as cmt_cmmt
from
(
select distinct cmt_domain, cmt_indx
from x
) x1
Query Active Directory from SQL Server
Get active directory server and LDAP servers in a domain
nltest /dclist:sub.dom.com
results amongst others in:
\\dcserver1.sub.dom.com -> dcserver1 is the AD server
Analyse active directory structure
Use Sysinternals ADExplorer to analyse the structure of active directory. Connect to server: dcserver1
Users in a group from command prompt
net group adtestgroup /domain
Active Directory structure in FROM clause
E.g. FROM "LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com"
LDAP = case-sensitive protocol name, always this value
Before the slash: sub.dom.com, is the domain for which to find the AD structure
After the slash: the part of the tree to search. Specified from right to left. So in the example, from parent to child:
com -> dom -> sub -> Global -> Groups -> ADTestGroup, searches the nodes beneath the lowest level, which is ADTestGroup
The names ("dom", "Groups", etc), type ("OU", "CN", "DC") and depth of the levels are dynamic. So check with ADExplorer how AD is configured in the specific instance.
Users in a group from SQL Server
First, add a linked server to ADSI (fill with correct password for DOMAIN\ACCOUNT):
exec sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
exec sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = 'DOMAIN\ACCOUNT', @rmtpassword = '********'
Select users in group "adtestgroup":
select * from openquery(ADSI,'
SELECT objectCategory, cn, sn, mail, name, department, company
FROM ''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''
WHERE MemberOf=''CN=adtestgroup,OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
ORDER BY cn
')
Unfortunately, dynamic sql is not possible inside a udf, so a procedure is needed:
alter procedure cst_usersingroup(@grp nvarchar(100))
as
begin
declare @sql as nvarchar(max) = '
select samaccountname as accountname, name, mail, department, title, company
from openquery(ADSI,''
SELECT objectCategory, cn, sn, mail, name, department, company, title, samaccountname
FROM ''''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''''
WHERE MemberOf=''''CN=' + @grp + ',OU=Groups,OU=Global,DC=sub,DC=dom,DC=com'''''')
x
order by samaccountname'
exec(@sql)
end
Select all BI_* and EDW_* groups from active directory:
select lower(cn) as grp, whencreated, whenchanged, distinguishedname
from openquery(ADSI,'
SELECT cn, distinguishedname, whencreated, whenchanged
FROM ''LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
where CN=''bi_*'' or CN=''edw_*''
') x
order by cn
Notes:
nltest /dclist:sub.dom.com
results amongst others in:
\\dcserver1.sub.dom.com -> dcserver1 is the AD server
Analyse active directory structure
Use Sysinternals ADExplorer to analyse the structure of active directory. Connect to server: dcserver1
Users in a group from command prompt
net group adtestgroup /domain
Active Directory structure in FROM clause
E.g. FROM "LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com"
LDAP = case-sensitive protocol name, always this value
Before the slash: sub.dom.com, is the domain for which to find the AD structure
After the slash: the part of the tree to search. Specified from right to left. So in the example, from parent to child:
com -> dom -> sub -> Global -> Groups -> ADTestGroup, searches the nodes beneath the lowest level, which is ADTestGroup
The names ("dom", "Groups", etc), type ("OU", "CN", "DC") and depth of the levels are dynamic. So check with ADExplorer how AD is configured in the specific instance.
Users in a group from SQL Server
First, add a linked server to ADSI (fill with correct password for DOMAIN\ACCOUNT):
exec sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
exec sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = 'DOMAIN\ACCOUNT', @rmtpassword = '********'
Select users in group "adtestgroup":
select * from openquery(ADSI,'
SELECT objectCategory, cn, sn, mail, name, department, company
FROM ''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''
WHERE MemberOf=''CN=adtestgroup,OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
ORDER BY cn
')
Unfortunately, dynamic sql is not possible inside a udf, so a procedure is needed:
alter procedure cst_usersingroup(@grp nvarchar(100))
as
begin
declare @sql as nvarchar(max) = '
select samaccountname as accountname, name, mail, department, title, company
from openquery(ADSI,''
SELECT objectCategory, cn, sn, mail, name, department, company, title, samaccountname
FROM ''''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''''
WHERE MemberOf=''''CN=' + @grp + ',OU=Groups,OU=Global,DC=sub,DC=dom,DC=com'''''')
x
order by samaccountname'
exec(@sql)
end
Select all BI_* and EDW_* groups from active directory:
select lower(cn) as grp, whencreated, whenchanged, distinguishedname
from openquery(ADSI,'
SELECT cn, distinguishedname, whencreated, whenchanged
FROM ''LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
where CN=''bi_*'' or CN=''edw_*''
') x
order by cn
Notes:
- In most cases, all domain users can query AD using the basic search method specified before. More advanced AD search methods might be disabled and require special rights
Create schema with separate autorization
Perform the following steps in SQL Server to create a schema called "bi" and give create view rights to user "domain\testuser":
CREATE SCHEMA [bi]
CREATE ROLE [db_bischema]
ALTER AUTHORIZATION ON SCHEMA::bi TO [db_bischema]
GRANT CREATE VIEW TO [db_bischema] -- "create view" includes the power to drop views
ALTER ROLE [db_bischema] ADD MEMBER [domain\testuser]
Advantages:
CREATE SCHEMA [bi]
CREATE ROLE [db_bischema]
ALTER AUTHORIZATION ON SCHEMA::bi TO [db_bischema]
GRANT CREATE VIEW TO [db_bischema] -- "create view" includes the power to drop views
ALTER ROLE [db_bischema] ADD MEMBER [domain\testuser]
Advantages:
- If views are removed from or added to the schema, autorization does not need to be added to each individual view
- Give certain users the rights to (re)create the views inside the schema, without the risk of modification of the rest of the database
Pass table-valued parameter from VB.Net
1) Create table type itemtabletype for a table with one, unique clustered column called "item":
create type itemtabletype
as table (
item nvarchar(30) not null,
primary key clustered (item)
)
3) Give execution rights to user(s) and/or groups that are allowed to execute the procedure and table type:
grant exec on type::itemtabletype to [User]
grant exec on cst_testtabletype to [User]
4) Finally, create the following test routine in VB.Net:
Public Sub Start()
'create data table
Dim Table1 As DataTable
'create a table named tmptbl
Table1 = New DataTable("tmptbl")
Dim row As DataRow
Try
'declare a column named item
Dim item As DataColumn = New DataColumn("item")
'setting the datatype for the column
item.DataType = System.Type.GetType("System.String")
'adding the column to table
Table1.Columns.Add(item)
'declaring a new row
Dim i As Integer
For i = 1 To 1000
row = Table1.NewRow()
row.Item("item") = i
Table1.Rows.Add(row)
Next
Catch
End Try
'establishing connection. you need to provide password for SQL server
Dim myConnection = New SqlConnection()
myConnection.ConnectionString = "Server=mySQLServer;Database=myDB;Trusted_Connection=True;"
Try
myConnection.Open()
Dim myCommand As New SqlCommand("cst_testtabletype", myConnection)
' the table-valued parameter is called @tv for cst_testtabletype
Dim myparam As SqlParameter = myCommand.Parameters.Add("@tv", SqlDbType.Structured)
'create parameter
myCommand.CommandType = CommandType.StoredProcedure
myparam.Value = Table1
Dim dr As SqlDataReader = myCommand.ExecuteReader()
While dr.Read()
Debug.Print(dr.Item(0))
End While
Catch ex As SqlException
MsgBox(ex.Message)
End Try
End Sub
create type itemtabletype
as table (
item nvarchar(30) not null,
primary key clustered (item)
)
2) Create a procedure cst_testtabletype that has itemtable type as a table-valued input parameter:
create procedure cst_testtabletype
( @tv itemtabletype readonly )
as
begin
declare @itemcount int = 0
select @itemcount = count(*) from @tv
select concat('succesfully parsed item ',item) from @tv
end
The procedure returns all items provided in the table-valued parameter prefixed with the words "successfully parsed item ".
grant exec on type::itemtabletype to [User]
grant exec on cst_testtabletype to [User]
4) Finally, create the following test routine in VB.Net:
Public Sub Start()
'create data table
Dim Table1 As DataTable
'create a table named tmptbl
Table1 = New DataTable("tmptbl")
Dim row As DataRow
Try
'declare a column named item
Dim item As DataColumn = New DataColumn("item")
'setting the datatype for the column
item.DataType = System.Type.GetType("System.String")
'adding the column to table
Table1.Columns.Add(item)
'declaring a new row
Dim i As Integer
For i = 1 To 1000
row = Table1.NewRow()
row.Item("item") = i
Table1.Rows.Add(row)
Next
Catch
End Try
'establishing connection. you need to provide password for SQL server
Dim myConnection = New SqlConnection()
myConnection.ConnectionString = "Server=mySQLServer;Database=myDB;Trusted_Connection=True;"
Try
myConnection.Open()
Dim myCommand As New SqlCommand("cst_testtabletype", myConnection)
' the table-valued parameter is called @tv for cst_testtabletype
Dim myparam As SqlParameter = myCommand.Parameters.Add("@tv", SqlDbType.Structured)
'create parameter
myCommand.CommandType = CommandType.StoredProcedure
myparam.Value = Table1
Dim dr As SqlDataReader = myCommand.ExecuteReader()
While dr.Read()
Debug.Print(dr.Item(0))
End While
Catch ex As SqlException
MsgBox(ex.Message)
End Try
End Sub
IsNumeric() stinks
Read the thoroughly insightful and funny post by Mike Teevee as a reaction to the technet documentation with regards to the useless ISNUMERIC() function introduced in SQL Server 2012:
http://technet.microsoft.com/en-us/library/ms186272.aspx
Select "SQL Server 2012" under "Other Versions", otherwise the post is hidden.
http://technet.microsoft.com/en-us/library/ms186272.aspx
Select "SQL Server 2012" under "Other Versions", otherwise the post is hidden.
Silverlight not working because of font issue
If Silverlight is installed correctly, but still not working, check for Windows Fonts with an ampersand in the name and remove them:
http://connect.microsoft.com/VisualStudio/feedback/details/719317/silverlight-5-error-1001-font-filename-issue
http://connect.microsoft.com/VisualStudio/feedback/details/719317/silverlight-5-error-1001-font-filename-issue
Uneasy way to determine proxy server
Open a command prompt and perform the following steps:
- Inspect the output of netstat -an | find "EST" (short from 'ESTABLISHED')
- Go to a fresh site (one that you have not recently visited)
- 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.
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.
...\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:
<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:
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:
- Select an entity
- Click on Settings in the top-right corner
- Set "records per page"
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:
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
For example, these Unicode characters cannot be part of the value in any field in the report:
code: visual representation
26: [SUB]
191: ¿
"Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 1, column 36242."
"Excel found unreadable content in..."
SOLUTION
- Rename the Excel workbook from *.xlsx to *.zip
- Within the zip file, open the folder \xl\worksheets and open file sheet1.xml in a text editor, i.e. Notepad++
- Use Notepad++'s GOTO feature to go to position 36242 and determine the offending character
- 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:
5b) Convert the JPG image to text
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)
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:
"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
- 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.
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:
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:
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
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
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:
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
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
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!
Subscribe to:
Posts (Atom)