Sysadmin: Replace comma with linebreak character

In Microsoft Word:

1. Select: Edit/Find then go to the Replace tab. On that screen, you may have to press the button that says "More"

2. Click onto the Find What field, then click the "Special" Button at the bottom of the Window. Select "White Space."

3. Click onto the Replace with field, then click "Special" Button again. This time, select "Paragraph Mark."

4. Click the "Replace All" Button. The job is done.

TSQL: JOINS

Joins can be categorized as:

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

=============================

A join is used to combine columns from two or more tables into a single result set. To join data from two tables you write the names of two tables in the FROM clause along with JOIN keyword and an ON phrase that specifies the join condition. The join condition indicates how two tables should be compared. In most cases they are compares on the base on the relationship of primary key of the first table and foreign key of the second table.

I have two tables - Vendor table and Advance table.

Now we are going to apply joins on these tables and see the data results.

Inner Joins
An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate. Actual SQL implementations will normally use other approaches where possible, since computing the Cartesian product is not very efficient. This type of join occurs most commonly in applications, and represents the default join-type.
Example: This is explicit inner join:

e.g. (T-SQL for Inner Join)


SELECT v.VendorId, v.VendorFName, v.VendorLName, a.royality, a.advance
FROM dbo.Vendor as v
INNER JOIN advance as a
ON v.VendorId = a.VendorId
WHERE v.VendorId <= 5
GO
Example: This is implicit inner join:
Use Vendor
GO

SELECT * FROM Vendor, advance
WHERE Vendor.VendorId = advance.VendorId AND Vendor.VendorId <= 5
GO



Cross Join
A cross join, Cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the Cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.

e.g. (T-SQL for Cross Join)

SELECT * FROM Vendor CROSS JOIN advance
GO
Use Vendor
GO
SELECT * FROM Vendor, advance
GO



Outer Joins
An outer join retrieves all rows that satisfy the join condition plus unmatched rows in one or both tables. In most cases you use the equal operator to retrieve rows with matching columns. However you can also use any of the other comparison operators. When row with unmatched columns is retrieved any columns from the other table that are included in the result are given null values.

Note1: The OUTER keyword is optional and typically omitted
Note2: You can also code left outer joins and right outer joins using the implicit syntax.

Three types of outer joins.
1. Left Outer Join
The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

e.g. (T-SQL for Left Outer Join)

SELECT VendorFName, Vendor.VendorId, VendorLName, Advance
FROM Vendor LEFT JOIN advance
ON Vendor.VendorId = advance.VendorId
GO


2. Right Outer Join
A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

e.g. (T-SQL for Right Outer Join)


SELECT VendorFName, advance.VendorId, VendorLName, Advance
FROM Vendor RIGHT JOIN advance
ON Vendor.VendorId = advance.VendorId
GO

3. Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

e.g. (T-SQL for Full Outer Join)

SELECT * FROM Vendor FULL OUTER JOIN advance
ON Vendor.VendorId = advance.VendorId

VS2008: Generate new GUID quickly

Source: http://mundeep.wordpress.com/2008/03/27/quickly-generating-guids-in-visual-studio-2008/

One of the common tasks involved when creating sharepoint solutions is the generation of GUIDs. Visual Studio comes with a tool called GuidGen that lets you create GUIDs however it is annoying having to leave the Visual Studio environment.

I believe Visual Studio 2005 used to have it as an option under the Tools menu however i haven’t found where i can add the same shortcut in Visual Studio Team System 2008 (though i do notice that Visual Studio 2008 Professional DOES have a “Create GUID” shortcut under Tools - i’d still prefer the macro shortcut for ease of use especially when creating a lot of features).

I have however found a nifty alternate solution by Leon Zandman that describes creating a Macro to insert a guid into your current file. I’d just like to clarify some of the steps for those that haven’t dealt with Macros in Visual Studio 2008 before.

1. Load Visual Studio 2008 and goto Tools -> Macros -> Macro Explorer (Alt-F8 for short)
2. Right-click on “Macros” then select New Macro Project
3. Name your project (eg. GUIDGenerator) and choose a location to save it (note no space allowed in Project Name).
4. This should give you a new project with a “Module1″ sitting underneath it. Right-click on “Module1″ and select “Rename” to give it a more meaningful name (eg. GUIDGenModule).
5. Double-click on the newly renamed module and you should be shown the Visual Studio Macro IDE.
6. Enter the following code (the “D” in ToString can be customised see Leon’s article):
   • view plaincopy to clipboardprint?

     1. Sub Create_GUID()
     2. DTE.ActiveDocument.Selection.Text = System.Guid.NewGuid().ToString("D").ToUpper()
     3. End Sub

        Sub Create_GUID()         DTE.ActiveDocument.Selection.Text = System.Guid.NewGuid().ToString("D").ToUpper()     End Sub 

7. Save and close the Macro IDE.
8. Back in the main Visual Studio window goto Tools -> Options
9. Goto the “Keyboard” option under the “Environment” tab.
10. In the “Show Commands Containing” text box type in “Create_GUID”
11. Select the Macro sub that you just created from the list (it should be the only one)
12. Click inside the “Press Shortcut Keys” textbox then press your desired keyboard shortcut for inserting a GUID (eg. Alt+G as Leon suggested makes a lot of sense).
13. Ensure the “Use Shortcut in” option is “Global” and click on “Assign”
14. Close the options window and you should be able to start using your keyboard shortcut to quickly insert GUIDs into text!
15. If you have any other Visual Studio windows open at the time you will need to close them and reload for the macro for the macro to be loaded (or you can goto the Macro Explorer window and manually load your Macro project)

HTML: Mailto with amphersand character

Replace "&" character with %26

WSS3.0: Treeview and SPSiteMapProvider

ARRRGHHH! this caused me much headache!

Finally figured it out.

Problem:

The dynamic treeview displays correctly in SharePoint Designer, why doesn't it expand fully while viewed through the web browser.

Make sure you've set the property "PopulateNodesFromClient" to false.

WSS3.0: Checking multiple files into libraries

WSS being the freebie tool that it is, doesn't provide any easy way for end users to check-in multiple files.

But if you have Sharepoint Designer, you can get around this limitation as it allows you to connect to your site and manage multiple file check-ins.

Excel: CSV schema file

When reading data programmatically from a CSV file via ASP, datatypes for each columns are determined by an initial scan of the first few rows of the file.
This does not give an accurate representation of the data type of each column.
You can define a schema.ini file and position this file in the same folder as the csv.
The schema file is loaded when required and contains custom attributes used when reading the csv file.

e.g.

SCHEMA.INI

[YOURCSVFILE.csv]
ColNameHeader=False
MaxScanRows=0
Format=CSVDelimited

Excel: Text to Number

Use the Text to Columns Command

This method works best if the data is arranged in a single column. The following example assumes that the data is in column A and starts in row 1 ($A$1). To use this example, follow these steps:

1. Select one column of cells that contain the text.
2. On the Data menu, click Text to Columns.
3. Under Original data type, click Delimited, and click Next.
4. Under Delimiters, click to select the Tab check box, and click Next.
5. Under Column data format, click General.
6. Click Advanced and make any appropriate settings for the Decimal separator and Thousands separator. Click OK.
7. Click Finish.