Thursday, January 25, 2007
Moving SharePoint Portal Server 2003 Service Pack 2(SPS-SP2) and Windows SharePoint Services Service Pack 2 (WSS-SP2) to a new server and url.
Moving SharePoint Portal Server 2003 Service Pack 2(SPS-SP2) and Windows SharePoint Services Service Pack 2 (WSS-SP2) to a new server and url.
The document describes how to move SharePoint Portal Server 2003 Service Pack 2(SPS-SP2) and Windows SharePoint Services Service Pack 2 (WSS-SP2) to a new server and url.
The assumption is that both servers are already running SharePoint Portal Server 2003 Service Pack 2(SPS-SP2) and Windows SharePoint Services Service Pack 2 (WSS-SP2). The only real difference between the servers is that old server is running SQL 2000 and new server is running SQL 2005. BTW, SQL 2005 requires that Service Pack 2 be used. This is supported by Microsoft, but SQL 2005 and SPS without SP2 is NOT supported!
IMPORTANT: This procedure requires down time where SharePoint will not be accessible to users!
Stop your SharePoint website and App Pool, you don't need it unless procedure fails, and you need to rollback.
Create a new website that uses the same host header as the SharePoint site. Create a index.htm file on that site that has javascript that will let user know the site has moved, they need to update bookmarks, and redirects them to new site. In IIS point all (or at least 404) http errors to this file. Now when users access the SharePoint site no url will be found, but they will be redirected to new server.
Here is an example of what I used:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<HTML><HEAD><TITLE>Please update your bookmarks</TITLE>
<META HTTP-EQUIV="Content-Type" Content="text/html; charset=Windows-1252">
<STYLE type="text/css">
BODY { font: 8pt/12pt verdana }
H1 { font: 13pt/15pt verdana }
H2 { font: 8pt/12pt verdana }
A:link { color: red }
A:visited { color: maroon }
</STYLE>
</HEAD><BODY>
<TABLE width=500 border=0 cellspacing=10><TR><TD>
<h1>Please update your bookmarks</h1>
SharePoint has moved to a new url.
<BR>
The old url will no longer be valid.
<BR>
You will be redirected to the new site. Please update your bookmarks.
</TD></TR></TABLE>
<script language="JavaScript">
var oldUrl = window.location.href;
var newUrl = oldUrl.replace("www.oldsite.com", "www.newsite.com");
alert("SharePoint has moved to a new url. \r\nThe old url will no longer be valid.\r\nYou will be redirected to the new site after you click OK. Please update your bookmarks.");
window.location = newUrl;
</script>
</BODY></HTML>
Stop this web site until you have the new server up, otherwise the users will be redirected to a non-existant site or at least start accessing it before it is verified.
Backup Up Existing installation using Start Menu | All Programs | SharePoint Portal Server | SharePoint Portal Server Data Backup and Restore.
Copy files to new server.
Restore files using Start Menu | All Programs | SharePoint Portal Server | SharePoint Portal Server Data Backup and Restore. Change paths to new url here.
IMPORTANT: Make sure your machine keys are the same on old and new server.
c:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\LAYOUTS\web.config
SharePoint web root\web.config
You may need to create a new web site yourself. Be sure to restrict IP address so that only localhost can access it. Otherwise anyone who has the url can access it thinking it is ready to use, when it is not.
At this point both SPS and WSS should be work fine. There is one thing that doesn't get restored correctly. That is the site directory in SPS. To update that execute the following. See justgeeks.blogspot.com and search for those stored procedure names. Create stored procs in _SERV and _SITE databases.
After you execute the lines below no changes are made. This is really just to show what the changes will be. To actually execute the changes comment uncomment the SearchAndReplaceAllTables lines. These will do a replace all in database.
use ITOPS1_SERV
go
SearchAllTables 'pww-nait.sjo.sc.philips.com'
-- SearchAndReplaceAllTables 'www.oldurl.com', 'www.newurl.com'
go
use ITOPS1_SITE -- real changes
go
SearchAllTables 'pww-nait.sjo.sc.philips.com'
-- SearchAndReplaceAllTables 'www.oldurl.com', 'www.newurl.com'
go
That should be it.
Verify installation.
Remove IP restrictions on new and old websites.
Retire old server, or use it for backup server in case your current one fails. It is configured with SharePoint after all.
Here is how long it took me to do this procedure:
Back Up 2GB SharePoint installation: 8 minutes
Copy to new server: 5 minutes
Restore Database: 10 minutes
Verify installation: 20 minutes
SQL Server search and replace on data
Have you ever wanted to search and replace occurrences of a word in all table in a database. I have. Below is the stored that needs to be the database you want to do the search and replace in.
CREATE PROC SearchAndReplaceAllTables
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100) ) AS BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
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
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
Here is how you use it:
--To replace all occurences of 'America' with 'USA':
EXEC SearchAndReplaceAllTables 'America', 'USA'
GO
Here is the link to the original page that I copied this from:
http://vyaskn.tripod.com/sql_server_search_and_replace.htm
Tuesday, January 23, 2007
Best way to post to blogger.com
I am constantly frustrated by trying to paste code and html examples from web pages or my own code to my blog. I have given up on blogger.com's editor. It totally munges the code and html. It appears that it has recently improved so that C# code now retains its formatting, or maybe that was the new blog this on the Google toolbar (I can't remember). However xml or html still is not escaped and is thus not at all useful. So, what to do when need to blog about code, html, or xml?
I have tried Outlook to send email to my blog and have it automatically posted to my blog. That works pretty well, except Outlook has an annoying issue where it treats my new lines as two new lines or worse 4 new lines. I have seen that even when sending email to other email users. It seems like a bug in Outlook. If anyone knows what is causing this, please let me know. I do know I can get around by doing a soft return (alt or shift enter) in Outlook.
Needless to say, none of these options work all the time it appears. The best I have seen is Hotmail web interface. It keeps the formatting of code, html, and xml from what I can tell. Thunderbird does ok, but doesn't copy/paste color of syntax highlighting (I think Hotmail will).
Sorry, so many I can't remembers in this blog. I just am too tired tonight to care, but don't want to forget my general experience of Hotmail web interface being the best yet.
Wednesday, January 17, 2007
Drag 'N Drop is great, but what about exception handling
So, you think the new Visual Studio 2005 and ASP.Net are pretty nice. You get rapid development from drag 'n drop objects like ObjectDataSources, GridView, etc. Then the dreaded exception occurs in your business object. Where does that appear in all this? How do you get to that exception? Believe it or not, Microsoft did it right. They have an event called ObjectDataSource Selected event that you can tap into just like you would any other event.
To check for an exception you would do something like this:
protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
{
if (e.Exception != null)
{
if (e.Exception.InnerException != null)
{
if (e.Exception.InnerException is IOException)
{
// do something with the exception.
e.ExceptionHandled = true; // so it doesn't keep bubbling up
{
}
}
}
Tuesday, January 16, 2007
Java Web Service Exception Handling
This assumes that you are using JAX-RPC which uses SOAP 1.1 to implement a web service, and that the web service is being consumed by .Net (1.1 or 2.0). If you are using WS 2.0 there are some different options.
While it is possible to just throw an plain old Exception in your java web service, it will show up your .Net client as a exception. The problem is determining what exception you have catch once you get it. In this situation we need a code that can be used in the client to identify the exception without try to rely on the message of the exception since this is typically semi user-friendly text. Exception class doesn’t have the concept of a code to identify the exception. The good news is SOAPFaultException has the concept of a code and it also allows you to attach any other data that may be useful.
Creating a SOAPFaultException can be a little messy, so I recommend created a convenience method to encapsulate this. In case this isn’t clear, this goes in your java web service.
// Creates and returns a SOAPFaultException. Throws one if there is an error creating
// Should be Client or Server
// If it is Client then message should NOT be resent without change
// i.e. Server.Fault or Client.Fault
public SOAPFaultException NewException(String faultType, String errorCode, String errorMessage, String webServiceOperationName) throws SOAPException
{
// The faultcode element provides an algorithmic mechanism for identifying the fault.
//SOAP defines a small set of SOAP fault codes covering basic SOAP faults.
QName faultCode = new QName("http://FrontLineWebService/" + webServiceOperationName, faultType + "." + errorCode);
// The faultstring provides a human-readable description of the SOAP fault and is not intended for algorithmic processing.
String faultString = errorMessage;
// The faultactor element provides information about which SOAP node on the SOAP message path caused the fault to happen.
//It indicates the source of the fault.
String faultActor = "http://FrontLineWebService/" + webServiceOperationName;
// The detail element is intended for carrying application specific error information related to the SOAP Body.
Detail faultDetail = SOAPFactory.newInstance().createDetail();
// Define what the body of the email should be in terms of XML. You can put whatever data you want here
faultDetail.addChildElement("ErrorMessage").addTextNode(errorMessage);
faultDetail.addChildElement("ErrorCode").addTextNode(errorCode);
// for more info on params: see http://www.w3.org/TR/2000/NOTE-SOAP-20000508/#_Toc478383507
throw new SOAPFaultException(faultCode, faultString, faultActor, faultDetail);
}
Here is an example of how you would throw the SOAPFaultException in your web service.
public void testMethod() throws Exception, SOAPFaultException, java.rmi.RemoteException
{
throw NewException("Server.Fault", "TEST_CODE", "This is only a test description in English", "testMethod");
}
Now that you can easily throw a SOAPFaultException (SoapException in .Net), you are all set. Here is an example of catching it in your .Net client.
using System.Web.Services.Protocols;
...
try
{
MyWS ws = new MyWS();
ws.testMethod(new testMethod());
}
catch (SoapException ex)
{
if (ex.Code.Name == "Server.Fault.TEST_CODE")
{
// do stuff here
}
}
Friday, January 12, 2007
SQL Server 2005 Mail Logs
The new email funcationality in SQL Server 2005 is quite nice. Every time you call msdb.dbo.sp_send_dbmail it sends the email in the background. This is important when you are sending a lot of emails on a already busy server. The natural question then is, how can I see what was sent, and if it was sent properly. The answer is in msdb database.
To see all the email sent out and the contents just do the following:
select * from msdb.dbo.sysmail_mailitems
sent_status field: 0 = waiting to process 1 = success 2 = error with the smtp server
To see if there were any errors, you can check the log using:
select * from msdb.dbo.sysmail_log
If you want to check retries for sending, try this:
select * from msdb.dbo.sysmail_send_retries
There are other tables, but they seem to be managed by the Microsoft SQL Server Management Studio or other stored procedures. Feel free to take a look at them though:
msdb.dbo.sysmail_principalprofile
msdb.dbo.sysmail_profile
msdb.dbo.sysmail_profileaccount
msdb.dbo.sysmail_profile
msdb.dbo.sysmail_profileaccount
msdb.dbo.sysmail_server
msdb.dbo.sysmail_servertype
msdbdbo.sysmail_query_transfer
If you need help setting up SQL mail in the first place, follow the link above.
Thursday, January 4, 2007
Carriage Returns and Microsoft SQL Server Management Studio
It appears that Microsoft SQL Server Management Studio (replacement for Enterprise Manager and Query Analyzer) in MS SQL Server 2005 does not support carriage returns (or any variation of it) using the Open Table view. This view allows for easy editing of data. The exception of this is data that has carriage return in it. You can’t paste, shift-enter or anything else using this interface.
The other annoying thing is that when showing the results of a query in the grid, if there is carriage returns in the data it simply ignores them when displaying the data. To see the carriage returns, you will need to change the results view from grid to text.
So, the question is, what is the easiest way to insert or update data that has carriage returns in it. The answer is simple. Use SQL, forget the easy to use GUI.
Below is an example:
insert into MyTable(MyCol) values ('line one
line two
line three');
update MyTable set MyCol = 'line one
line two
line three
line four'
where ID = 1;
Subscribe to:
Posts (Atom)