SQL Server: Detach and Attach a DB

Target Audience: Beginners
Keywords: SQL Server, Attaching Databases
This post is not related to SharePoint directly but yes indirectly it is still related to SharePoint. I do lots of applications for SharePoint. I create custom built solutions in .NET for SharePoint. I don't like the back up utility of SQL Server. It is not reliable as it misses several stored procedures when restoring. Although it does not happen always but no body likes to take chances so i usually back up my database by detaching the db and saving the .MDF file to the back up folder. I also use the same mechanism for replicating the DB. Some times when you get stuck when attaching the detached DB. For example, consider this scenario. I want to replicate a DB. I detach the original DB and make a copy of it and rename it. I attach the original DB without any problem but when i try to attach the renamed DB copy, I get an error. This is not a technical solution to a problem as problem itself does not exist :) This is a very simple tip but many users will find it very useful. Believe me. When you are in a hurry to do a replication and could not find the solution, this tip will really come in very handy. Anyway, here are the steps involved:
1. Right click Databases. All Tasks > Attach Database.
2. Browse to find the renamed DB file (the MDF file).
3. Under "Current File(s) Location", double click the file name and change it to the new name. The box shows you the original MDF file name even though you have renamed it. Rename it and DO NOT HIT THE ENTER BUTTON. Instead, use your mouse and click somewhere in the second row. When you hit the enter button, SQL Server does not accept your change and reverse the file name back to the original file name but when you move your focus away from first row to second row by simply clicking the mouse, this problem does not occur.
4. "Attach As" also will still be showing the original file name. Type in the new name that you want to give to your database.
5. Specify the database owner and you are done.
Very simple! What's the big deal in it? Yes, what's the big deal in it? If you are a senior DBA reading this post and laughing and thinking why i am posting such a simple thing then let me tell you you were not the intended audience for this post. This happens with me very frequently. People criticize me for posting simple solutions but let me tell you that my goal is to write for every one, not just for the senior people. Most of the emails that i daily receive are from the young people are still learning the tricks of the trade. We can not ignore them or move away instead of helping them. Well, it gives me pleasure when a senior person appreciates my work but it equally pleases me when a beginner takes advantage of my simple tips and praises my work and thank me for writing it. So, getting back to the tip, here is one final note. If you get an error while attaching the DB without any apparent reason, see if your SQL query analyzer is open and you are accessing the DB in it. Some times, it happens that you are working in Query Analyzer, you leave the session open and detach the DB, for any purpose, be it taking backup or replication, when you try to re-attach the DB, you get an error telling you that attaching failed!! You don't see any reason why your attaching failed. This happens because of the open sessions in your Query Analyzer. Close the session by closing the Analyzer and try attaching the DB again.
Visit the following link to see how to attach a DB using T-SQL:

http://msdn2.microsoft.com/en-US/library/ms189625(SQL.90).aspx

-SSA

Checking User Permissions in SharePoint Sites

Download application and source code


Many people have been asking me this question as to how they can check user permissions in sites programmatically. This brief tutorial will tell you how you can do this. I have included the complete source code of the application for your convenience.

Figure 1: Application screenshot



Let's take a look at the code:

MsgBox(CheckGroupRights(txtSiteURL.Text, txtSubSite.text, txtUserLogin.Text))

We have called the main function, CheckGroupRights in the msgbox function. CheckGroupRights() returns a string telling us whether the user has rights in the subsite or not. Please note that this tool will look for "Reader" privileges only. For example, if you provide a user named as "domainuser1" then this tool will check whether user1 has reader rights or not. You can modify the code to check for any type of rights.


If you look at the screen shot above, you will notice there are three fields where you would have to enter some text. For example, Site URL will contain the main URL of the site. User Login contains the user's login name, that is, complete login name including the domain, for example, domain1johndoe. Sub Site Name is the name of the site where you want to check the permissions. For example, you have a subsite named as subsite1 under the main site which has the following URL:

http://mainsite/sites/site1

The application will form the following URL from the values provided by you:

http://mainsite/sites/site1/subsite1

Here is the code that checks the rights:

Function CheckGroupRights(ByVal FolderPath As String, ByVal SubSite As String, ByVal UserLogin As String) As String
'Notes:
'Folderpath: is the main url where you want to find the permissions. I know this is cumbersome to provide
'both the url of the main site and the name of the subsite but this is just a sample to show you how things
'work. I may make it more simpler in the next version provided i got enough time to make the modifications.
'Examples: Folderpath: http://mainportalsite/sites/site1
' http://mainportalsite
'SubSite: This should be the name of the subsite, it should not be a URL, e.g,
'abc, 123, site1, site2, site3, etc
'final url that will be formed if your folderpath contained http://mainsite/sites/site1 and subsite contained "abc", will be
'http://mainsite/sites/site1/abc
'userlogin: is the users domain login, e.g, domainusername




Try
Dim strStatus As String = "User " & UserLogin & " does not have Reader permissions in " & FolderPath & "/" & SubSite & "."

If Not FolderPath Is Nothing Or Not FolderPath = "" Then


Dim siteCollection As SPSite
siteCollection = New SPSite(FolderPath)
Dim site As SPWeb = siteCollection.OpenWeb(SubSite)


Dim allUsers As SPUserCollection = site.Users
Dim user As SPUser


For Each user In allUsers


If user.LoginName.ToUpper = UserLogin.ToUpper Then


Dim allGroups As SPRoleCollection = user.Roles
Dim group As SPRole


For Each group In allGroups


Dim right As Integer
right = group.PermissionMask And SPRights.ViewListItems


If right = SPRights.ViewListItems Then


strStatus = "User " & UserLogin & " has Reader permissions in " & FolderPath & "/" & SubSite & "."
Return strStatus
Exit Function


End If
Next


End If
Next


Return strStatus


End If


Catch ex As Exception
MsgBox(ex.Message)
End Try


End Function


Code is pretty simple. Nothing fancy! Please look at these lines again:
…..
right = group.PermissionMask And SPRights.ViewListItems
If right = SPRights.ViewListItems Then
…..

SPRights.ViewListItems checks for the "Reader" privileges only. You can modify these lines to check other privileges. For example:


SPRights.ManageLists: Use "ManageLists" if you want to check whether the user has "Approver" rights in the subsite. User with these rights can add, edit, delete, approve content in the sites.


SPRights.EditListItems: User with these permissions can add, delete, modify site content but can not approve items in the site.


Similarly, you can check for many other types of privileges in the site. For complete list of rights, see SPS SDK.


I hope you will find this small tool useful. It is meant for learning purposes only. If you are a beginner, you can pick up hints from this code and can expand and make some other useful application out of this code. I will posting more small applications soon. Stay tuned!


-SSA

Checking User Permissions in SharePoint Sites

Download application and source code

Many people have been asking me this question as to how they can check user permissions in sites programmatically. This brief tutorial will tell you how you can do this. I have included the complete source code of the application for your convenience.

Let's take a look at the code:

MsgBox(CheckGroupRights(txtSiteURL.Text, txtSubSite.text, txtUserLogin.Text))

We have called the main function, CheckGroupRights in the msgbox function. CheckGroupRights() returns a string telling us whether the user has rights in the subsite or not. Please note that this tool will look for "Reader" privileges only. For example, if you provide a user named as "domainuser1" then this tool will check whether user1 has reader rights or not. You can modify the code to check for any type of rights.

If you look at the screen shot above, you will notice there are three fields where you would have to enter some text. For example, Site URL will contain the main URL of the site. User Login contains the user's login name, that is, complete login name including the domain, for example, domain1johndoe. Sub Site Name is the name of the site where you want to check the permissions. For example, you have a subsite named as subsite1 under the main site which has the following URL:

http://mainsite/sites/site1

The application will form the following URL from the values provided by you:

http://mainsite/sites/site1/subsite1

Here is the code that checks the rights:

Function CheckGroupRights(ByVal FolderPath As String, ByVal SubSite As String, ByVal UserLogin As String) As String 'Notes: 'Folderpath: is the main url where you want to find the permissions. I know this is cumbersome to provide 'both the url of the main site and the name of the subsite but this is just a sample to show you how things 'work. I may make it more simpler in the next version provided i got enough time to make the modifications. 'Examples: Folderpath: http://mainportalsite/sites/site1 ' http://mainportalsite 'SubSite: This should be the name of the subsite, it should not be a URL, e.g, 'abc, 123, site1, site2, site3, etc 'final url that will be formed if your folderpath contained http://mainsite/sites/site1 and subsite contained "abc", will be 'http://mainsite/sites/site1/abc 'userlogin: is the users domain login, e.g, domainusername   Try Dim strStatus As String = "User " & UserLogin & " does not have Reader permissions in " & FolderPath & "/" & SubSite & "." If Not FolderPath Is Nothing Or Not FolderPath = "" Then Dim siteCollection As SPSite siteCollection = New SPSite(FolderPath) Dim site As SPWeb = siteCollection.OpenWeb(SubSite) Dim allUsers As SPUserCollection = site.Users Dim user As SPUser For Each user In allUsers If user.LoginName.ToUpper = UserLogin.ToUpper Then     Dim allGroups As SPRoleCollection = user.Roles     Dim group As SPRole     For Each group In allGroups     Dim right As Integer     right = group.PermissionMask And SPRights.ViewListItems          If right = SPRights.ViewListItems Then            strStatus = "User " & UserLogin & " has Reader permissions in " & FolderPath & "/" & SubSite & "."            Return strStatus            Exit Function          End If     Next End If Next Return strStatus End If Catch ex As Exception           MsgBox(ex.Message) End Try End Function

Code is pretty simple. Nothing fancy! Please look at these lines again:

.....
right = group.PermissionMask And SPRights.ViewListItems
         If right = SPRights.ViewListItems Then

.....

SPRights.ViewListItems checks for the "Reader" privileges only. You can modify these lines to check other privileges. For example:

SPRights.ManageLists: Use "ManageLists" if you want to check whether the user has "Approver" rights in the subsite. User with these rights can add, edit, delete, approve content in the sites.

SPRights.EditListItems: User with these permissions can add, delete, modify site content but can not approve items in the site.

Similarly, you can check for many other types of privileges in the site. For complete list of rights, see SPS SDK.

I hope you will find this small tool useful. It is meant for learning purposes only. If you are a beginner, you can pick up hints from this code and can expand and make some other useful application out of this code. I will posting more small applications soon. Stay tuned!

-SSA

SharePoint, Document Library and SQL Server (SPS 2003)

v:* { behavior: url(#default#VML) } o:* { behavior: url(#default#VML) } .shape { behavior: url(#default#VML) }

SharePoint, Document Library and SQL Server

Download Source Code

It's been years now, SharePoint 2007 is about to appear in the market, SharePoint 2003 seems to be something that is history now (Exaggerating .). Many companies have adopted SharePoint during the last one year. Good thing is that I have seen lots of small businesses acquiring SharePoint during the last one year. SharePoint's popularity is growing day by day and it is considered to be a top level portal product.

Document Libraries are collections of files that you can share with team members on a web based on Windows SharePoint services. Want to know more about Document Libraries?? Read more about Document Libraries on this site:

http://office.microsoft.com/en-au/assistance/ha011412871033.aspx

This link will show you how to create and use a document library. You can extend the functionality of these document libraries by using the "Event Handler Toolkit" that can be downloaded from the Microsoft site:

http://www.microsoft.com/downloads/details.aspx?familyid=4B2C2F1B-D74A-482A-903A-45BB44C5DEC4&displaylang=en

Suppose, whenever a document is added in the document library, you want to store the name of the document and the time it was added, in the database. How can you do that? Why would you do that? You can capture events fired in the document library. When ever a document is added, updated, deleted, etc, you can capture the event and add your own business logic or simply capture document information and store it in the database. Why would you store document information in the database? Well, there can be many reasons. You may want to create reports for the senior management who may want to look at the documents added to the library from time to time. You may want to track the document history or you may want to add a workflow to your document library. All this can be done by using the "Event Handler Toolkit".

Before, we proceed, let me tell you the purpose of writing this. There are many excellent articles on the internet that show you how to capture document library events and do processing but still many people come to me and ask me the same question again and again and the question is related to the capturing of document information and storing it in SQL Server. Seeing the demand, I decided to write a small article that will show you how you can capture the document information in the library and store it in SQL Server. You can do so many things, for example:

1. When a document is added, you can copy or move it to another document library.
2. You can delete a document.
3. You can capture document information and store it in SQL Server.
4. You can update document information by updating the properties associated with the document.
5. You can add workflow mechanism to your document library. For example, when a document is added to the document library, you send an email to the approver and ask him to approve the document. Document, after getting approval from approver 1, goes to approver 2 and so on.

These are only some of the important things that you can do with Document Library EventHandler. I wish I could show you all of the above in my article but unfortunately that is not possible due to shortage of time but I promise I will try my best to update this tutorial from time to time and add more content to it. In this article, we will see how you can capture document information when a document is inserted in the library and store this information in DB.

1. Unzip the downloaded file to the root of your system drive. Zip file contains the complete source code and the installation scripts.
2. You must create a database before you start working with the code. Open your SQL Server and create a blank database and name it as "SPSDocLibHandler".
3. Add a table to this database. Table name is "DocInfo". Create following fields in the table:

* Please note that "id" is an identity column.

Here is the SQL script to create this table:

CREATE TABLE [dbo].[DocInfo] (

            [id] [int] IDENTITY (1, 1) NOT NULL ,

            [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [Modified] [datetime] NULL ,

            [ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [ApproverComments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [ApprovalStatus] [int] NULL

) ON [PRIMARY]

            You will find this script in "db.sql" file that is located in the "DB" folder inside the application folder. "db.sql" file also contains the script to create a stored         procedure that will be used in our code. I would suggest that you just create the database and then run the "db.sql" file to create the required table and stored procedure     but that's up to you. You can follow the steps listed here to do it manually.

4. Create a stored procedure called as "sp_AddDocInfo". Here is the SQL script:

CREATE PROCEDURE dbo.sp_AddDocInfo

(

            @Name varchar(50),

            @Modified datetime,

            @ApprovalStatus int,

            @ModifiedBy varchar(50),

            @ApproverComments varchar(255)

)

as

insert into DocInfo values (@Name,@Modified,@ModifiedBy,@ApproverComments,@ApprovalStatus)

5. There is a file called as "db.config" in the root of the application folder. This one line configuration file contains connection string to your SQL Server. Edit this file in your notepad and add your SQL Server connection details.

Data Source = YourSQLServerName (Replace this with your SQL Server's name)

Initial Catalog = SPSDocLibHandler (This is the database name)

User Id = sa (Replace this with your own user id)

Password = (Add password here, if any)

6. Copy this file "db.config" to the root of your system drive. For example, if your system drive is "C:", copy the file to the root of "C:" drive. If your system drive is "D:" then copy the file to the root of "D:" drive.

7. There is a file named as "Install SPSDocLibHandler.bat" in your application folder. Open this file in notepad and see if you need to make any changes. This file installs your DLL into GAC, especially check the path where gacutil.exe is located on your computer. Run this file to install DLL into GAC. You can install the assembly into GAC manually as well. "SharePoint.DocLib.Handler.dll" can be found in the "bindebug" folder inside your application folder.

8. After installing the DLL into GAC, you need to bind your assembly to the document library where this assembly will be used. Before doing that, you also need to enable "Event Handlers" on the virtual server where your SharePoint resides. If you don't know how to do this then read this article on Microsoft's site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/html/tsptDocLibEvents_SV01034973.asp

For your convenience, I am listing the steps required to enable the "Event Handlers" on your virtual server.

a)      Go to SharePoint Central Administration page.

b)      Click "Configure virtual server settings from the virtual server list page" under "Portal Site and Virtual Server Configuration". This will open the "Virtual Server List" page:

c)      Click on your virtual server from the list shown on this page. Clicking on your virtual server will open the following page:

d)      Click on "Virtual server general settings" link on this page. You will see the following page:

On the bottom of the page, you will find "Event Handlers" section. Select "On" in the Event Handlers section to enable use of event handlers on the virtual server.

Now that you have enabled "Event Handlers" on your virtual server, your next step is to bind Your assembly to the document library. Following are the steps required to accomplish this:

a)      From a view of the document library to which you want to attach the event handler assembly, click Modify settings and columns.

b)      Click "Change advanced settings" on the page that opens for customizing the document library.

c)      On the Document Library Advanced Settings page, type the strong name for the assembly in the Assembly Name box. In our case assembly name box would contain:

SharePoint.DocLib.Handler, Version=1.0.0.0, Culture=neutral, PublicKeyToken=32bb15c33a2f8eca.

You can get the values used in the strong name by right-clicking the DLL of your assembly in the Local_Drive:WINDOWSassembly directory and then clicking Properties on the shortcut menu.

d)      Type the fully qualified, case-sensitive name of the class in the Class Name box, which in this case is SharePoint.DocLib.Handler.LibraryEventSink

e)      Reset IIS by typing iisreset at a command prompt.

9. You are now ready to test the application. Insert a document in your library and then go to your SQL Server, open the table and you will see the details of the newly added document. You can create reports based on the data in this table for the senior management. This is just one example of what you can do with SharePoint Document Libraries and the "Event Handler Toolkit".

Code Discussion

Now, let's spend a few minutes to see the application code:

Following files have been taken from the "Event Handler Toolkit":

a)      BaseEventSink.cs

b)      LoggerEventSink.cs

c)      SPListEventArgs.cs

d)      LibraryEventSink.cs

We will not discuss these files. If you want more information about how to use the "Event Handler Toolkit", read following detailed article on Microsoft's site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/html/tsptDocLibEvents_SV01034973.asp

We added our code in "LibraryEventSink" class. Let's take a look!

private void OnInsert()

{

     try

     {

           spFile = EventWeb.GetFile( EventFileUrl );

               

           if (spFile.Exists)

           {

                spListItem = spFile.Item;

               

                //Retrieve Database string from config file

                string strDB;

                StreamReader sr = new StreamReader(@"C:db.config");

               

                strDB = sr.ReadLine();

                sr.Close();

    

We write our code in "OnInsert()" because we want to capture the document information  at the time when it gets added to the library. If we wanted to capture the document when someone updated it, we would write our code in "OnUpdate()". First of all, we get the URL of the file just added to the library.

           spFile = EventWeb.GetFile( EventFileUrl );

spFile is an instance of class SPFile that represents a file in a SharePoint web site that is an item in a document library.

Next, we open the "db.config" file that contains the connection string to the SQL Server. We read the connection string into the "strDB" variable.

                // Create Instance of Connection and Command Object

                SqlConnection myConnection = new SqlConnection(strDB);

                SqlCommand myCommand = new SqlCommand("sp_AddDocInfo", myConnection);

                // Mark the Command as a SPROC

                myCommand.CommandType = CommandType.StoredProcedure;

Instances of Connection and Command objects are created. Stored procedure expects parameters that will contain the values to be added in the database. In the following piece of code, we define the parameters and assign values to them. We capture following document details:

1.       Document Name

2.       Date of Modification

3.       Approval Status

4.       Approver Comments

5.       Modified By

                // Add Parameters to SPROC

                SqlParameter pName= new SqlParameter("@Name", SqlDbType.VarChar , 50);

                pName.Value = spListItem["Name"];

                myCommand.Parameters.Add(pName);

                          

                SqlParameter pModified= new SqlParameter("@Modified", SqlDbType.DateTime, 8);

                pModified.Value = spListItem["Modified"];

                myCommand.Parameters.Add(pModified);

                SqlParameter pApprovalStatus= new SqlParameter("@ApprovalStatus", SqlDbType.Int , 4);

                pApprovalStatus.Value = spListItem["Approval Status"];

                myCommand.Parameters.Add(pApprovalStatus);

               

                SqlParameter pModifiedBy = new SqlParameter("@ModifiedBy", SqlDbType.VarChar , 50);

                pModifiedBy.Value = spListItem["Modified By"];

                myCommand.Parameters.Add(pModifiedBy);

In the following code, we use "if-else" to send proper value to the database to avoid error. If "Approver Comments" are empty, it will generate an error. It is important to tackle this situation. We check the property for the null value, in case it contains null, we add comments of our own otherwise we leave the original comments in tact. This property will always contain null because "Approver Comments" are always null when a new document is added to the library. Approver can add his/her comments only at the time of approval. I used this property thinking that I would demonstrate it's use in the "update" event.

                if (spListItem["Approver Comments"]!=System.DBNull.Value)

                {

                     SqlParameter pApproverComments = new SqlParameter("@ApproverComments", SqlDbType.VarChar , 255);

                     pApproverComments.Value = "No Comments";

                                                                                myCommand.Parameters.Add(pApproverComments);

          

                }

                else

                {

                     SqlParameter pApproverComments = new SqlParameter("@ApproverComments", SqlDbType.VarChar , 255);

                     pApproverComments.Value = spListItem["Approver Comments"];

                                          myCommand.Parameters.Add(pApproverComments);

                }

    

Finally, open the connection, execute the stored procedure and close the connection.

                // Open the connection and execute the Command

                myConnection.Open();

                myCommand.ExecuteNonQuery();

                myConnection.Close();

          }

                         

}

You can catch exceptions using "try-catch". Exceptions caught are logged in the Windows Event Log.

catch( System.Security.SecurityException ex )

{

          message = "Error: " + ex.Message;

          writeToEventLog(message + ":" + ex.ToString());

}

catch( Exception ex )

{

          Message = "Error: " + ex.Message;

          writeToEventLog(message + ":" + ex.ToString());

}

}

Conclusion

Some people will find this tutorial to be very simple while others may find it very complicated. To be honest, it will be complicated for the beginners but those who have lot of experience in SharePoint will find it to be simple. Well, my objective was to explain the process to the people who form the "beginner" class. You might find some errors including typos in the tutorial because it was written hastily. If you get stuck some where and need my assistance then write to me at share.point@yahoo.com or ss_ahmed1@hotmail.com. Although it is not possible to reply to each and every email but I will try my best to answer as many queries as possible. Even though I have stopped authoring tutorials for the last 2-3 years, still I get 30-40 emails daily from people who read and appreciate my tutorials, who read and criticize my work and from people who ask me to help them on their personal projects.

Please stay connected, the next tutorial in this series will be more advanced and will tell you more about the advantages of capturing documents in SharePoint Document Libraries offer.

Good Luck.

S.S. Ahmed

Share.point@yahoo.com

Q: Can I add links to folders and files on my hard drive? I don'

Q: Can I add links to folders and files on my hard drive? I don't want to upload files to SharePoint.

This question was sent today by a user. Here is the solution:

You can use "Links" list to add links to your files and folders on a local drive. Add a "Links" list web part to your site and start adding links to your files and folders. When you click "Add new link" link, you are asked to enter a URL with "http://" prefix. You can add a link to your shared folder like this: \serverfolder (server is your machine name and folder is your folder name). Save this link. This link will take users to your shared folder. If you want to give a link to a file, use this format: \serverfoldermydoc.doc.

How to use usage analysis tools in SharePoint?

Q: How to use usage analysis tools in SharePoint? You can use Frontpage to see usage analysis of your SharePoint sites. Open your SharePoint site in Frontpage. Select View > Reports > Usage > Usage Summary to see the usage summary. There are number of other options like viewing daily summary, browsers, operating system, etc. To enable logging in SharePoint: 1. Go to SharePoint Central Administration. 2. Inside "Component Configuration" section, click "Configure usage analysis processing". 3. Check "Enable Logging" box. 4. Enter number of log files to create. You can enter a number between 1 and 30. 5. Check "Enable usage analysis processing" box and enter start and end times. 6. Click Ok. You can view analysis reports in SharePoint as well: 1. Click Site Settings inside your SharePoint site. 2. Click "Go to site administration". 3. Click "View site usage data" to view a report. Following page on Microsoft site contains complete details: http://www.microsoft.com/technet/prodtechnol/sppt/reskit/c2961881x.mspx -SSA

Changing default location of IIS Log Files

Q: I have over 5 gigs of log files in C:WINDOWSsystem32LogFilesW3SVC1253141572 folder and my drive is near capacity. How can I route it to a bigger drive? You can change the default location of your log files and you may want to do this especially if you are looking to store log files on a bigger drive. 1. Open IIS 2. Right click your portal site and select "Properties" 3. Select "Website" tab if not already selected. 4. In "Enable logging" section, click "Properties" 5. Change the "Log file directory" by clicking the browse button. You can specify another drive on your computer or any computer on your network. -SSA