Useful System SPs of SQL Server 2005

sp_add_data_file_recover_suspect_db (Transact-SQL)
Adds a data file to a filegroup when recovery cannot complete on a database due to insufficient space on the file group (error 1105). After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD FILE.
Click this link

sp_addextendedproc (Transact-SQL
Registers the name of a new extended stored procedure to Microsoft SQL Server.
Click this link

sp_addextendedproperty (Transact-SQL)
Adds a new extended property to a database object.
Click this link

sp_add_log_file_recover_suspect_db (Transact-SQL)
Adds a log file to a file group when recovery cannot complete on a database due to insufficient log space (error 9002). After the file is added, sp_add_log_file_recover_suspect_db turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD LOG FILE.
Click this link

sp_addmessage (Transact-SQL)
Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages stored by using sp_addmessage can be viewed by using the sys.messages catalog view.
Click this link

sp_addtype (Transact-SQL)
Creates an alias data type.
Click this link

sp_addumpdevice (Transact-SQL)
Adds a backup device to an instance of the Microsoft SQL Server 2005 Database Engine.
Click this link

sp_altermessage (Transact-SQL)
Alters the state of user-defined messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Click this link

sp_attach_db (Transact-SQL)
Attaches a database to a server.
Click this link

sp_attach_single_file_db (Transact-SQL)
Attaches a database that has only one data file to the current server. sp_attach_single_file_db cannot be used with multiple data files.
Click this link

sp_autostats (Transact-SQL)
Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a specified table or indexed view in the current database.
Click this link

sp_batch_params (Transact-SQL)
Returns a rowset that contains information about the parameters included in a Transact-SQL batch. sp_batch_params only parses the batch specified and returns information about embedded parameter values. It does not execute the batch or modify the execution environment.
Click this link

sp_bindefault (Transact-SQL)
Binds a default to a column or to an alias data type.
Click this link

sp_bindrule (Transact-SQL)
Binds a rule to a column or to an alias data type.
Click this link

sp_bindsession (Transact-SQL)
Binds or unbinds a session to other sessions in the same instance of the SQL Server Database Engine. Binding sessions allows two or more sessions to participate in the same transaction and share locks until a ROLLBACK TRANSACTION or COMMIT TRANSACTION is issued.
Click this link

sp_certify_removable (Transact-SQL)
Verifies that a database is correctly configured for distribution on removable media and reports any problems to the user.
Click this link

sp_configure (Transact-SQL)
Displays or changes global configuration settings for the current server.
Click this link

sp_control_plan_guide (Transact-SQL)
Drops, enables, or disables a plan guide.
Click this link

sp_create_plan_guide (Transact-SQL)
Creates a plan guide for associating query hints with queries in a database.
Click this link

sp_create_removable (Transact-SQL)
Creates a removable media database. Creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the database on those files.
Click this link

sp_createstats (Transact-SQL)
Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.
Click this link

sp_datatype_info (Transact-SQL)
Returns information about the data types supported by the current environment.
Click this link

sp_db_vardecimal_storage_format (Transact-SQL)
Returns the current vardecimal storage format state of a database, or enables or disables a database for vardecimal storage format. Requires SQL Server 2005 Service Pack 2 or later versions. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.
Click this link

sp_dbcmptlevel (Transact-SQL)
Sets certain database behaviors to be compatible with the specified version of SQL Server.
Click this link

sp_dbmmonitoraddmonitoring (Transact-SQL)
Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on the server instance.
click this link

sp_dbmmonitorchangealert (Transact-SQL)
Adds or changes warning threshold for a specified mirroring performance metric.
Click this link

sp_dbmmonitorchangemonitoring (Transact-SQL)
Changes the value of a database mirroring monitoring parameter.
Click this link

sp_dbmmonitordropalert (Transact-SQL)
Drops the warning for a specified performance metric, by setting the threshold to NULL.
Click this link

sp_dbmmonitordropmonitoring (Transact-SQL)
Stops and deletes the mirroring monitor job for all the databases on the server instance.
Click this link

sp_dbmmonitorhelpalert (Transact-SQL)
Returns information about warning thresholds on one or all of several key database mirroring monitor performance metrics.
Click this link

sp_dbmmonitorhelpmonitoring (Transact-SQL)
Returns the current update period.
Click this link

sp_dbmmonitorresults (Transact-SQL)
Returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored and allows you to choose whether the procedure obtains the latest status beforehand.
Click this link

sp_dbmmonitorupdate (Transact-SQL)
Updates the database mirroring monitor status table by inserting a new table row for each mirrored database, and truncates rows older than the current retention period. The default retention period is 7 days (168 hours). When updating the table, sp_dbmmonitorupdate evaluates the performance metrics.
Click this link

sp_dboption (Transact-SQL)
Displays or changes database options. Do not use sp_dboption to modify options on either the master database or the tempdb database.
Click this link

sp_dbremove (Transact-SQL)
Removes a database and all files associated with that database.
Click this link

sp_delete_backuphistory (Transact-SQL)
Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date. Additional rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically execute sp_delete_backuphistory.
Click this link

sp_delete_database_backuphistory (Transact-SQL)
Deletes information about the specified database from the backup and restore history tables.
Click this link

sp_depends (Transact-SQL)
Displays information about database object dependencies, such as: the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported.
Click this link

sp_detach_db (Transact-SQL)
Detaches a database that is currently not in use from a server instance and, optionally, runs UPDATE STATISTICS on all tables before detaching.
Click this link

sp_dropdevice (Transact-SQL)
Drops a database device or backup device from an instance of the SQL Server 2005 Database Engine, deleting the entry from master.dbo.sysdevices.
Click this link

sp_dropextendedproc (Transact-SQL)
Drops an extended stored procedure.
Click this link

sp_dropextendedproperty (Transact-SQL)
Drops an existing extended property.
Click this link

sp_dropmessage (Transact-SQL)
Drops a specified user-defined error message from an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Click this link

sp_droptype (Transact-SQL)
Deletes an alias data type from systypes.
Click this link

sp_estimated_rowsize_reduction_for_vardecimal (Transact-SQL)
Estimates the reduction in the average size of rows if enable vardecimal storage format is enabled on a table. Use this number to estimate the overall reduction in the size of the table. Because the statistical sampling is used to compute the average reduction in the rowsize, regard it as an estimate only. In rare cases, rowsize may increase after you enable the vardecimal storage format. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.
Click this link

sp_executesql (Transact-SQL)
Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
Click this link

sp_get_query_template
Returns the parameterized form of a query. The results returned mimic the parameterized form of a query that results from using forced parameterization. sp_get_query_template is used primarily when you create TEMPLATE plan guides.
Click this link

sp_getapplock (Transact-SQL)
Places a lock on an application resource.
Click this link

sp_getbindtoken (Transact-SQL)
Returns a unique identifier for the transaction. This unique identifier is a string used to bind sessions using sp_bindsession.
Click this link

sp_help (Transact-SQL)
Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type supplied by SQL Server 2005.
Click this link

sp_helpconstraint (Transact-SQL)
Returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only).
Click this link

sp_helpdb (Transact-SQL)
Reports information about a specified database or all databases.
Click this link

sp_helpdevice (Transact-SQL)
Reports information about Microsoft® SQL Server™ backup devices.
Click this link

sp_helpextendedproc (Transact-SQL)
Reports the currently defined extended stored procedures and the name of the dynamic-link library (DLL) to which the procedure (function) belongs.
Click this link

sp_helpfile (Transact-SQL)
Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.
Click this link

sp_helpfilegroup (Transact-SQL)
Returns the names and attributes of filegroups associated with the current database.
Click this link

sp_helpgroup (Transact-SQL)
Reports information about a role, or all roles, in the current database.
Click this link

sp_helpindex (Transact-SQL)
Reports information about the indexes on a table or view.
Click this link

sp_helplanguage (Transact-SQL)
Reports information about a particular alternative language or about all languages.
Click this link

sp_helpserver (Transact-SQL)
Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the network name of the server, the replication status of the server, the identification number of the server, and the collation name. Also provides time-out values for connecting to, or queries against, linked servers.
Click this link

sp_helpsort (Transact-SQL)
Displays the sort order and character set for the instance of SQL Server 2005.
Click this link

sp_helpstats (Transact-SQL)
Returns statistics information about columns and indexes on the specified table.
Click this link

sp_helptext (Transact-SQL)
Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.
Click this link

sp_helptrigger (Transact-SQL)
Returns the type or types of DML triggers defined on the specified table for the current database. sp_helptrigger cannot be used with DDL triggers. Query the sys.triggers (Transact-SQL) catalog view instead.
Click this link

sp_indexoption (Transact-SQL)
Sets locking option values for user-defined clustered and nonclustered indexes or tables with no clustered index.
The SQL Server 2005 Database Engine automatically makes choices of page-, row-, or table-level locking. You do not have to set these options manually. sp_indexoption is provided for expert users who know with certainty that a particular type of lock is always appropriate.
Click this link

sp_invalidate_textptr (Transact-SQL)
Invalidates the specified in-row text pointer, or all in-row text pointers, in the transaction. sp_invalidate_textptr can be used only on in-row text pointers. These pointers are from tables that have the text in row option enabled.
Click this link

sp_lock (Transact-SQL)
Reports information about locks.
Click this link

sp_monitor (Transact-SQL)
Displays statistics about Microsoft SQL Server.
Click this link

sp_procoption (Transact-SQL)
Sets stored procedure for autoexecution. A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started.
Click this link

sp_recompile (Transact-SQL)
Causes stored procedures and triggers to be recompiled the next time they are run.
Click this link

sp_refreshsqlmodule (Transact-SQL)
Updates the metadata for the specified nonschema-bound stored procedure, user-defined function, or view. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.
Click this link

sp_refreshview (Transact-SQL)
Updates the metadata for the specified non-schemabound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
Click this link

sp_releaseapplock (Transact-SQL)
Releases a lock on an application resource.
Click this link

sp_rename (Transact-SQL)
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.
Click this link

sp_renamedb (Transact-SQL)
Changes the name of a database.
Click this link

sp_resetstatus (Transact-SQL)
Resets the status of a suspect database.
Click this link

sp_setnetname (Transact-SQL)
Sets the network names in sys.servers to their actual network computer names for remote instances of SQL Server 2005. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing SQL Server identifiers that are not valid.
Click this link

sp_settriggerorder (Transact-SQL)
Specifies the AFTER triggers that are fired first or last. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.
Click this link

sp_spaceused (Transact-SQL)
Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
Click this link

sp_tableoption (Transact-SQL)
Sets option values for user-defined tables. sp_tableoption can be used to control the in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, or image columns.
Click this link


sp_unbindefault (Transact-SQL)
Unbinds, or removes, a default from a column or from an alias data type in the current database.
Click this link

sp_unbindrule (Transact-SQL)
Unbinds a rule from a column or an alias data type in the current database.
Click this link

sp_updateextendedproperty (Transact-SQL)
Updates the value of an existing extended property.
Click this link

sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
Click this link

sp_validname (Transact-SQL)
Checks for valid SQL Server 2005 identifier names. All nonbinary and nonzero data, including Unicode data that can be stored by using the nchar, nvarchar, or ntext data types, are accepted as valid characters for identifier names.
Click this link

sp_who (Transact-SQL)
Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
Click this link

How to insert or add URL links inside the gridview?

There are many ways to insert or add links inside a grid. There are many ways to display all the pages in your site in a gridview.

This is one way,

<asp:HyperLinkField DataTextField="Modules" HeaderText="Name" NavigateUrl='<% Bind("URL") %>' />

This the second way to do this,

                                                                    <asp:TemplateField HeaderText="Name" SortExpression="Modules">

                                                                        <ItemTemplate>

                                                                            <asp:HyperLink ID="hlModules" runat="server" Text='<%# Bind("Modules") %>' />

                                                                        </ItemTemplate>

                                                                    </asp:TemplateField>

Then add this code in gridview RowCreated event,

if (e.Row.RowType == DataControlRowType.DataRow)

{

    HyperLink hl = (HyperLink)e.Row.FindControl("hlModules");

    hl.NavigateUrl = "MyPage.aspx";

}

But if you want to open the page in a new window or pop up. You need to put this code in RowDataBound event of the gridview and do like this,

           

if (e.Row.RowType == DataControlRowType.DataRow)

{

HyperLink hl = (HyperLink)e.Row.FindControl("hlModules");

      switch(hl.Text)

      {

            case "FirstPage":

                  hl.NavigateUrl = "javascript:OpenFirstPage();";

            break;                       

            case "SecondPage":

                  hl.NavigateUrl = "javascript:OpenSecondPage();";

            break;                       

 …

      }

}

Of course you need to create the javascript to open a pop up window like this,

<script type="text/javascript" language="javascript">

var winArray = new Array()

function OpenFirstPage()

{

if (!winArray[0] || winArray[0].closed)

      winArray[0] = window.open("FirstPage.aspx", "FirstPage", "width=800,height=600,toolbar=no, menubar=no, directories=no, resizable=yes, scrollbars=yes");

else

            winArray[0].focus();

}

function OpenSecondPage()

{

if (!winArray[0] || winArray[0].closed)

      winArray[0] = window.open("SecondPage.aspx", "SecondPage", "width=800,height=600,toolbar=no, menubar=no, directories=no, resizable=yes, scrollbars=yes");

else

            winArray[0].focus();

}

…

</script>

You may refer to my other post or blog on how to open popup or close all popup windows.

Here are my references:
Link 0

Link 1
Link 2
Link 3
Link 3
Link 4
Link 5
Link 6

Link 7
Link 8
Link 9
Link 10
Link 11
Link 12
Link 13

How to close all windows upon signout?

This solution might not be the exact solution to close all open windows, but it does close all windows upon clicking sign out.

First, you need to create a sign out button on your master page. Then put this code your click event of the button.

Here is the code:

            Session.Clear();

            Response.Redirect("~/LoginPage.aspx");

Second, You need to create a login page. I do not need to put the code for the login page. You can do that.

Third, You need to create javascript that will open each page.

Here is the javascript:

    <script type="text/javascript" language="javascript">

        var winArray = new Array()

        function OpenFirstPage() {

            if (!winArray[0] || winArray[0].closed)

                winArray[0] = window.open("FirstPage.aspx", "FirstPage", "width=800,height=600,toolbar=no, menubar=no, directories=no, resizable=yes, scrollbars=yes");

            else

                winArray[0].focus();

        }

        function OpenSecondPage() {

            if (!winArray[1] || winArray[1].closed)

                winArray[1] = window.open("SecondPage.aspx", "SecondPage", "width=800,height=600,toolbar=no, menubar=no, directories=no, resizable=yes, scrollbars=yes");

            else

                winArray[1].focus();

        }

        function OpenThirdPage() {

            if (!winArray[2] || winArray[2].closed)

                winArray[2] = window.open("ThirdPage.aspx", "ThirdPage", "width=800,height=600,toolbar=no, menubar=no, directories=no, resizable=yes, scrollbars=yes");

            else

                winArray[2].focus();

        }

        function SignOut()

        {

            if (winArray[0] == null)

                OpenFirstPage();

            winArray[0].close();

            if (winArray[1] == null)

                OpenSecondPage();

            winArray[1].close();

            if (winArray[2] == null)

                OpenThirdPage();

            winArray[2].close();

        }

    </script>

Fourth, You need to add attributes to your sign out and button.

Here is the code:

this.lnkUser.Attributes.Add("onclick", "javascript:OpenFirstPage()");

this.lnkTeams.Attributes.Add("onclick", "javascript: OpenSecondPage()");

this.lnkVolunteers.Attributes.Add("onclick", "javascript: OpenThirdPage()");

this.lnkLogout.Attributes.Add("onclick", "javascript:SignOut()");

References used:

Link 1

Link 2  

Link 3

Link 4

Link 5

Link 6

Link 7

Link 8

How to do multiple popup window using ASP.NET 3.5 and AJAX Control and Javascript?

    

There are three ways to popup a new window. The first way to do popup window,

this.lnkNewWindow.Attributes.Add("onclick", "window.open('NewWindow.aspx', 'NewWindow', 'toolbar=no, menubar=no, directories=no, resizable=yes, scrollbar=yes');return false;");

this the second way to do popup window,

<asp:LinkButton ID="lnkNewWindow" runat="server" Text="New Window" onclick="javascript:openNewWindow();” />

Put this javascript inside script tag,

  function OpenNewWindow()

  {

  window.open('NewWindow.aspx', 'NewWindow', 'toolbar=no, menubar=no, directories=no, resizable=yes, scrollbar=yes');

return false;

  }

This is the third way to do popup window,

this.lnkNewWindow.Attributes.Add("onclick", "javascript:OpenNewWindow();");

To close this window just call window.close() like this,

this.btnClose.Attributes.Add("onclick", "window.close()");

If you are doing popup window that has functionality of exporting to pdf or excel with multiview control and updatepanel. You need to put postback trigger. Here is my link.

Here are my References for popup window:

Reference 1

Reference 2

How to export to pdf or excel in ASP.NET 3.5?

Here are my references for export to pdf or excel:
Reference 1
Reference 2
Reference 3
Reference 4
Reference 5
Reference 6

How to extract data from site?

Web scraping (also called Web harvesting or Web data extraction) is a computer software technique of extracting information from websites.

Here is the code that extract the content of a specific site,

WebClient wc = new WebClient();

string html = string.Empty;

MatchCollection matches;

string url = string.Empty;

int id = 0;

html = wc.DownloadString(urlPath).Replace("<html>", "").Replace("</html>", "").Replace("<!DOCTYPEHTML>", "").Replace("<head>", "").Replace("</head>", "").Replace("<script>", "").Replace("</script>", "");

matches = Regex.Matches(html, "<a.*?href=\"(.*?)\".*?>(.*?)</a>", RegexOptions.IgnoreCase | RegexOptions.Singleline);

if (destinationList == null)

      destinationList = new List<clsDestinations>();

foreach (Match match in matches)

{

      string matchUrl = match.Groups[1].Value;

      //For internal links, build the url mapped to the base address

      if (match.Groups[0].Value.Contains("travel/landing_page_hotels.cfm"))

      {

            url = MapUrl(urlPath, match.Groups[1].Value);

            if (url.Length > 0)

            {

                 destination = new clsDestinations();

                 id += 1;

                 destination.ID = id;

                 destination.Url = url;

                 destination.CityName = match.Groups[2].Value;

                 if (!destinationList.Exists(d => d.CityName == destination.CityName))

                       destinationList.Add(destination);

             }

      }

}

                foreach(clsDestinations cy in destinationList)

                {

                    if (!cityBll.CheckForDuplicateCity(cy, false))

                        result += cityBll.InsertCity(cy); 

                }

http://www.consultsarath.com/contents/articles/KB000017-web-scraping--extract-all-links-from-a-web-page-using-vbnet.aspx

http://stackoverflow.com/questions/5283273/extraction-of-text-from-html-web-pages-using-java

Here are the references that I have used:Once you have the data in your collection. Then you can save them one by one like this,

How to Create a Simple Drop-Down List in MS Excel 2007?

This post is derive from this link. This post is done in microsoft excel 2007.
1.

Click on the cell in the workbook where you want your drop-down list to appear. Cell A3 is active in the image to the left (click image to enlarge it).

2.

Click the Data tab, and on the Data Tools menu, click on Data Validation. The Data Validation dialog box will appear.

3.

On the Settings tab, below Allow, click the down arrow and select List.

4.

For a simple list, type the items in the Source box in the order you want them to appear in the drop down, separating them by a comma as shown is the image. Click the OK button.

5.

Now, at the insertion point, you can click the down arrow to display the options, clicking on one to select it.

References:

http://www.ehow.com/how_4535524_simple-dropdown-list-ms-excel.html#ixzz1R7FQFMg

How to upload and download files thru FTP Server?

I have use several references in creating this window application. I have somehow simplified it and extract codes from the references that I have used. There is only three buttons, they are upload, download and close. The upload button is used to copy file to ftp server. In short You are transferring or copying your local file to ftp server. The download button is used to get file from ftp server to your local folder or machine. The close button is used to close your application.

Here is what the application looks like,

Here is my code

Here are my references that I have used in this blog:
Code Project
C# Corner
Code Guru
MSDN Microsoft
ondotnet