Chris Barba

If variations quit working, the it could be the relationship list (hidden list in the root site) could be corrupt.
This could happen when you copy sites/pages from the target site back to the source site.

The easiest way to fix is run the variations editor.
http://www.thesug.org/blogs/lsuslinky/Lists/Posts/Post.aspx?List=ee6ea231%2D5770%2D4c2d%2Da99c%2Dc7c6e5fec1a7&ID=21

You have to run the tool on the front end server (just on one of them if you have multiple, SharePoint will propagate the changes to any other front end servers).  You just download the zip file and extract the files to a folder, it doesn't install anything there is just a .exe (SharePoint Variations Editor.exe) file to run.

You have to enter the url for your site (ex, http://www.blah.com/en/pages/home.aspx), then click the scan button (next the text box you entered the url, looks kind of like a site icon).

It will scan all your sites (progress bar (with % complete) at the bottom) and show your site structure.

The very last 2 nodes will include any warnings or errors.

For each error on a page, select the page, check the check box, then click green button in the toolbar.

For each error on a site, you will need to look up that site's group in the hidden relationship list (http://demo/Relationships%20List/AllItems.aspx).  Look for that site in a different variation and get the group id from that site.  You will have to rescan after making these changes.

After that propagation will continue to work and any page not propagated will be propagated to the target sites.

 

This blog summarizes a couple of other blogs I learned from (I want to give credit where credit is due.)
http://stsadm.blogspot.com/2008/04/fun-with-variations.html

http://www.thesug.org/blogs/lsuslinky/Lists/Posts/Post.aspx?List=ee6ea231%2D5770%2D4c2d%2Da99c%2Dc7c6e5fec1a7&ID=21

I found this script that allows you to search across all fields in all tables of a database for a specific string. It’s helpful when you trying to where data might be stored in a strange/large/new database that you are not intimately familiar with. Being a consultant I have found myself digging though a clients database many times trying to figure how the data is stored and this has saved me from looking in every table manually. I did not come up with this code myself, but I didn’t save the link of the blog where I found it. So unfortunately I can’t give credit to the smart person who came up with it. To use it just set the value of @SearchStr (highlighted in red) to the string you wish to search for.

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
DECLARE @SearchStr nvarchar(100)
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr = 'string to search for' 
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results

Technorati Tags: TSQL,T-SQL,Search

I attended the SharePoint Saturday today and attended a session on SharePoint optimal performance by Eric Shupps.  Here are a few of the tips I learned today.

Have a separate server (not available to users) to host search indexes.

Change the default SQL growth from 1 mb, 10% to 100 mb, 250%.

Isolate the SharePoint DB, do not put any other databases on the same server as the SharePoint DB.

Create custom scopes with unique crawl schedules. If you have a specific site that needs to crawled more often than the other sites

Enable manual compression on IIS 6/7.  You will need to balance between compression and cpu benefits (Compression increases CPU utilization on WFE's).

Only things in the 12 hive can be compressed, things out of the database are not

Windows 2008 does dynamic compression out of the box
Windows 2003 can turn on dynamic compression, but has limited improvement

MOSS does caching right out of the box, wss does not
MOSS caches Pages, Objects, Disk (blob, ie documents)
Caching increases memory utilization

Check the object cache
100 MB is kind of small, maybe increase 250-500 MB

Setup cache profiles.
each site collection can be setup different
each zone can be setup different

BLOB caching does not work right before the infrastructure update

Check the output cache.
You can use the cache profiles you setup, which can be set for anonymous and/or windows auth.

For further reading:
check out SharePoint 2007 performance ad scalability white paper check out TechNet IIS performance tuning

More than once I have searched for something on Google and gotten back too many results that don’t matter.  One of the problems with IT is that everything moves and improves quickly.  It’s such a pain when get results that over 2 years old and don’t matter because the technology has moved on.

Use the advanced search of Google to get results that are relevant.  You can tell Google what date range to use (how recent the page is), for example past week, past month, past year.  That way you will only get pages in the results that are from the your date range.  This helps when you’re working with bleeding edge technologies.

For Firefox being such a good browser, I was surprised to find out to find out that Firefox can run faster.  There are some Firefox settings that can be adjusted to speed up your browsing experience.

Of course type about:config in the address to get to the Firefox settings. 

Change the following settings:
network.http.pipelining from false to true
network.http.pipelining.maxrequests from 4 to 15
network.http.proxy.pipelining from false to true

Then add a new entry nglayout.initialpaint.delay and set it’s value to 0.

This will speed up your Firefox browsing experience.

Occasionally I end working on PC that is missing the show desktop icon.  I always end up looking for how to recreate it on Google. 

In Windows XP, go the run command then type the following command:

regsvr32 /n /i:u shell32.dll

Then press enter.  A dialog will let you know it was successful.
The show desktop won’t be there until you reboot or type the following command:

%appdata%\microsoft\internet explorer\quick launch

This will open a folder.  Then you can drag the show desktop icon to your quick launch bar (it will create a short cut in the folder that just opened up).

In Vista, go to howtogeek.com and download the file labeled “ShowDesktop.zip”.  Open that file and drag the file it contains to the quick launch bar.

Tired of Vista asking you if it’s okay to do anything, that’s the User Account Control (UAC).  You can turn it off or use free third party tools to adjust.

I choose to turn it off because it is too annoying.  Just go the control panel-> user accounts.  Then click Turn User Account Control on or off.  Click continue at the UAC prompt and on the next screen, uncheck the Use User Account Control (UAC) to help protect your computer.

That will turn off UAC and you won’t have to worry about it.

I had to determine how many rows are in a speadsheet with VBA.

Dim rowCount as Integer
rowCount = ActiveSheet.UsedRange.Rows.Count

 

If you need to know how many worksheets in a spreadsheet.

Dim sheetCounter as Integer
sheetCounter = ThisWorkbook.Worksheets.Count

Here is a sql script that will give a count of all records in every table in your database. This comes from my friend David McCollough.

CREATE TABLE #sizes ([name] nvarchar(200), 
-- BOL says (20), but that obviously wrong
             [rowcount] varchar(25))
DECLARE @tablename VARCHAR (128)

DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   order by TABLE_NAME

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
  --print 'Insert into #sizes Select ''' +  @tablename + ''' as [name] ,count(*) from ' + @tablename
  --exec ('Insert into #sizes Select ''' +  @tablename + ''' as [name] ,count(*) from ' + @tablename)
  --faster than select count(*)
  exec ('Insert into #sizes Select ''' +  @tablename + ''' as [name],(SELECT rows FROM sysindexes WHERE id = OBJECT_ID(''' +  @tablename + ''') AND indid < 2)')
FETCH NEXT
   FROM tables
   INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

select * from #sizes Order by [name]
drop table #sizes

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

 

Here is another sql script I got from my friend David McCollough that will allow you to search all tables for a specific field name.

Just set the @ColumnName variable and let ‘er rip.

 

DECLARE @ColumnName       varchar(1000)

SET @ColumnName = 'PlayerId'

    select o.name as TableName,
           c.name as [ColumnName]
      from sysobjects o 
inner join syscolumns c on c.id = o.id
      where o.type = 'u' and c.name = @ColumnName       
   Order by TableName

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

If your web app just can't tolerate a user clicking the back button, you can tell the browser to not cache the page. Most of the time it will listen if you use this code, which covers all the bases:

Response.Expires = 60
Response.ExpiresAbsolute = DateAdd(DateInterval.Day, -1, Now())
Response.AddHeader("pragma", "no-cache")
Response.AddHeader("cache-control", "private")
Response.CacheControl = "no-cache"

Here is some JavaScript that has a function to tell if an object 
is viewable on the screen.

This is quite helpful if you have something on the screen you 
want to make sure the user sees,

like a an error or license agreement, etc.

<HTML>
<HEAD>
<SCRIPT>
function isinView(oObject)
{
    var oParent = oObject.offsetParent;
    var iOffsetTop = oObject.offsetTop;
    var iClientHeight = oParent.clientHeight;
    if (iOffsetTop > iClientHeight) {
        alert("Special Text not in view. Expand Window to put Text in View.");
    }
    else{
         alert("Special Text in View!");
    }
}
</SCRIPT>
</HEAD>
<BODY onload="window.resizeTo(430,250)" onclick="isinView(oID_1)"  SCROLL=NO>

<DIV STYLE="position:absolute;left:20px">Click anywhere in window to see if special text is in
view.</DIV>

<DIV id="oID_1" STYLE="position:absolute; left:50px;top:300px;width:280px;color:lightGreen;
font-size:large;font-weight:bold;background-color:hotPink;font-family:Arial">
            Here's some special text
</DIV>
</BODY>
</HTML>

Search functionality is a feature that comes up with many applications. I have written more than one search stored procedure (and screen), but I usually ended up with writing dynamic sql (building a SQL statement and using the exec command, see Figure 1 below). The main disadvantage to dynamic sql statements is that they are not compiled and performance always matters. I was reading CoDe magazine one day and noticed a part of an article that mentioned dynamic sql without dynamic sql. I read it and was quite pleased with the approach. So the basic approach is to have all the lookup parameters as optional parameters, set null parameters to the % wildcard, use a series of like statements in the where clause. See figure 2 for an example of the sql code. Figure 1

Create Procedure Search
    @ProductName VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @ProductName +')'

Exec ( @SQL)

Figure 2

Create Procedure Search
@ProductName varchar(100) = null
As
Set @ProductName = isnull(@ProductNmae, '%')

Select * from Products
where ProductName like @ProductName

Here is some T-SQL I got from a friend of mine (David Mccoullgh). Just set the @SearchPhrase to search across stored proc, functions, triggers to see where that string is in use. Very helpful if you are changing a field name and want to know where it is in use.

DECLARE @SearchPhrase varchar(1000)

SET @SearchPhrase = 'PlayerBonus'

SELECT DISTINCT sysobjects.name AS [Object Name],
                case
     when sysobjects.xtype = 'P' then 'Stored Proc'
                    when sysobjects.xtype = 'TF' then 'Function'
                    when sysobjects.xtype = 'TR' then 'Trigger'
                end as [Object Type],
                (SELECT ParentTable.[Name]
                   FROM sysobjects ParentTable
                  WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
           FROM sysobjects, syscomments
          WHERE sysobjects.id = syscomments.id
            AND sysobjects.type in ('P','TF','TR')
            AND sysobjects.category = 0
            AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

So in Visual Studio when accidently copy (ctrl-c) instead of paste (ctrl-v) you lose what was on your clipboard.

Quite annoying behavior, but you can fix it. 

Tools-> Options –> check Show All Settings –> expand the Text Editor node –> select All Languages –> under Settings –> clear the Apply cut or copy commands when there is no selection.

This will change the behavior in visual studio to work like word when it comes to copy and paste.

 

 

This is a tip I read about in the Code magazine for Jan/Feb 2009.

I talk about SQL server every chance I get because of some the great features available (SSRS, SSIS, SSAS).  So I looked into SQL 2008 the first chance I got.  More than once I have been asked about the new features of SQL server 2008.  So here is a list I complied (not 100% of the features, just the ones I thought they were easy to sell)

Top new features:

1. T-SQL Intellisense – Intellisense is now available for T-SQL. This allows for easier and quicker development.
2. Date/Time data types – There is a new data type for just the date and there is a new data type for just the time. There is no longer the need to program around a datetime data type.
3. Encrypted Backups – Backups can be encrypted to prevent data disclosure or tampering.
4. Transparent Data Encryption – The entire database can be encrypted by the SQL engine. This encrypts all database data and log files for a database log. All indexes and tables are encrypted. No changes to applications are required.
5. Memory Management improvements in Reporting Services – Running large reports will no longer consume all available memory.

List of some of the new features

Security/Auditing
     - Transparent Data Encryption (encryption while data is 'still' on disk, transparent to applications)
     - External Key Management (Consolidation of key management, integration with external products)
     - Data Auditing (1st-class 'AUDIT' objects; DDL support; audit objects, principals, data, etc.; support for multiple logging targets)

Availability/Reliability
     - Pluggable CPU support
     - Enhanced Database Mirroring (compression of mirror streams, enhanced performance, automatic page-level repair for principal/mirror)

Performance
     - Data compression (easy to enable/disable online, more efficient data storage (this is NOT traditional data compression))
     - Backup stream compression (server level control or backup statement control, all backup types)
     - Performance data collection (single, common framework for data collection, reporting, and storage/warehousing)
     - Improved Plan Guide support (plan freezing, pull plans directly from plan cache, SSMS integration, etc.)
     - Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization amoung groups)

Management
     - Policy-based management framework (manage via policies vs. scripts, enterprise-wide support, automated monitoring/enforcement, etc.)
     - Integrate with Microsoft System Center
     - Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW, unprecidented insight into goings-on)

Development Enhancements
     - Improved datetime datatypes (100th nanosecond precision (7 digits past second), time-zone datetime offset, date only, time only)
     - HierarchyID datatype (hierarchical-aware data type, ORDPath values, built-in functions, methods, etc.)
     - Entity Data Model support (develop 'business entities' vs. tables, model complex relationships, retrieve entities vs. rows/columns)
     - LINQ
     - Sql Server Change Tracking (Change Data Capture, get 'diff' data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.))
     - Table Valued Parameters
     - MERGE statement ('upsert' data, also includes deletion functionality)
     - Large UDT's (no more 8000 byte limit on CLR-based UDTs, no more 8000 byte limit for UDA's)
     - Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes)
     - XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.)
     - Inline initialization and compound assignment

  Service Broker
     - New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools, )
     - Conversation Priority (set message ordering, send/receive impact, 1-10 levels)

Data Storage
     - Data compression (see above)
     - FILESTREAM attribute (get the 'best of both' functionality from BLOBs in the DB vs. BLOBs on filesystem, no more "to blob or not to blob")
     - Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing)
     - Sparse columns (more efficient storage for 'wide' tables with many columns that repeat and don't contain data)
     - New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.)

Data Warehousing/ETL
     - Partitioned Table Parallelism (no more thread limit per partition)
     - Star Join support (no special syntax, optimizer based, full backward syntax support)
     - Data compression (see above)
     - Resource Governor (see above)
     - Persistent Lookups in SSIS (no more re-querying for lookup operators, cache lookups in multiple ways, persist lookups to disk)
     - Improved thread scheduling in SSIS (shared thread pool, pipeline parallelism)
     - Change Data Capture (see above)
     - MERGE statement (see above, great uses with slowly changing dimensions)
     - Scale-out analysis services (read-only storage supports multiple AS servers)
     - Subspace computations
     - New Tools for Cube design
     - Best Practice Design Alerting
     - Backup cubes with better scalability
     - Data-mining add-ins for Excell

Reporting
     - IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer)
     - Rich-text support
     - Enhanced visualiztion (graphing)
     - New Word rendering (render reports to Microsoft Word)