Extended Stored Procedures

Extended procedures are routines residing in DLLs that function similarly to regular stored procedures. They receive parameters and return results via SQL Server's Open Data Services API and are usually written in C or C++. They must reside in the master database and run within the SQL Server process space.

Although the two are similar, calls to extended procedures work a bit differently than calls to system procedures. Extended procedures aren't automatically located in the master database and they don't assume the context of the current database when executed. To execute an extended procedure from a database other than the master, you have to fully qualify the reference (e.g., EXEC master.dbo.xp_cmdshell 'dir').

In this article I'm going to show you ten extended stored procedures that are available to us through code in Microsoft SQL Server 2000. Extended stored procedures are created and loaded using DLL's (which are created in C/C++), and offer more independent functionality than standard procedures (such as access to the Win32 API, services, direct disk/network access, etc), and because they're compiled externally as dynamic linking libraries (DLL's), they're also quicker.

sp_MSgetversion

This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:

EXEC master..sp_MSgetversion

Note. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:

SELECT @@version

xp_dirtree

This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:\MSSQL7 folder, run:

EXEC master..xp_dirtree 'C:\MSSQL7'

xp_subdirs

This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.

This is the example:

EXEC master..xp_subdirs 'C:\MSSQL7'

xp_enum_oledb_providers

This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:

EXEC master..xp_enum_oledb_providers

xp_enumcodepages

This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:

EXEC master..xp_enumcodepages

xp_enumdsn

This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:

EXEC master..xp_enumdsn

xp_enumerrorlogs

This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:

EXEC master..xp_enumerrorlogs

xp_enumgroups

This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:

EXEC master..xp_enumgroups

xp_fileexist

You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:

EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

For example, to check whether the file boot.ini exists on disk c: or not, run:

EXEC master..xp_fileexist 'c:\boot.ini'

xp_fixeddrives

This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:

EXEC master..xp_fixeddrives

xp_getnetname

This extended stored procedure returns the WINS name of the SQL Server that you're connected to. To view the name, run:

EXEC master..xp_getnetname

xp_readerrorlog

This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:\MSSQL7\Log directory, by default. To see the text of the errorlog file, run:

EXEC master..xp_readerrorlog

xp_regdeletekey

This extended stored procedure will delete an entire key from the registry. You should use it very carefully. The syntax is:

EXECUTE xp_regdeletekey [@rootkey=]'rootkey', [@key=]'key'

For example, to delete the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test'

xp_regdeletevalue

This extended stored procedure will delete a particular value for a key in the registry. You should use it very carefully. The syntax is:

EXECUTE xp_regdeletevalue [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name'

For example, to delete the value 'TestValue' for the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'

xp_regread

This extended stored procedure is used to read from the registry. The syntax is:

EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT]

For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:

DECLARE @test varchar(20)EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @value=@test OUTPUTSELECT @test

xp_regwrite

This extended stored procedure is used to write to the registry. The syntax is:

EXECUTE xp_regwrite [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name', [@type=]'type', [@value=]'value'

For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @type='REG_SZ', @value='Test'

Download Article

Update Script Creator

Instructions:


1 download scripts from following link then open script and connect to the source database

http://sites.google.com/site/akshatsharma80/home/sql-server/update-script-creator
Download Script

2 Change the variable values to change the output options for the script below (@tab, @statementtype etc)
3 execute the script (best to use text output)
4 copy the script output into a script window, and run on the destination

Create Script to Copy Database Schema and All The Objects

Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc.

Step 1 : Start

Step 2 : Welcome Screen

Step 3 : Select One or Multiple Database

If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)

Step 4 : Select database options 

Step 5 : Select output option

Step 6 : Review Summary

Step 7 : Observe script generation process

Step 8 : Database object script generation completed in new query window

Is there a way that I can edit more than " Edit Top 200 Rows " for a table in SQL 2008?

Step 1 Select “Edit Top 200 Rows”

Step 2 Click on Show SQL Pane

Step 3 Change TOP() according to your need and execute query

Now you are able to edit more then 200 rows

Making Connection Strings Rapidly

This is great technique to create complex connection string. so now there is no need to remember complex connection strings.


Steps
1) Open a New notepad and save it with "udl" extension, suppose "New.udl".
2) Now you will see that it's icon is changed.
3) open it, you will find Data Link properties dialog box.
4) For SQL Server connection string select Microsoft OLE DB Provider For SQL Server in Provider Tab.
5) Click button "Next" or select Connection Tab
6) here you can select all connection details and press button
Test Connection. If it is successful close this dialog box.
7) Now open this file using "Notepad", you will find the connection string. Though it is built for OLE DB
type of connection, you can use for SQL Server connection by removing Provider attribute.

Dropdown List validation

Like we keep blank textboxes for user to enter something need to do same for dropdown lists too. For this we add an item i.e. “Select”, “Select an item” this indicates that user not selected any thing from dropdown and also indication he/she need to select from given list, so lets do it first and then we will apply validation on it

In your dropdown set AppendDataBoundItems="True" property to true. This property controls whether the items within an existing list are replaced or appended-to when the control is data bounded.

private void FillServices()

{
ddlServices.Items.Clear();
ddlServices.Items.Add(new ListItem("Select", "0"));
ddlServices.DataSource = FilterServices(((customers)Session[SessionTypes.Customer]).services);
ddlServices.DataTextField = "name";
ddlServices.DataValueField = "id";
ddlServices.DataBind();
}

Now, apply validation on our dropdown list as below

Download Article

Fixing LINQ Error “Sequence contains no elements”

Problem
This exception is raised when there are no records that matches to the criteria specified in the LINQ query. For example the following LINQ query raises the exception if the where criteria does not match with any records in the data source

var objResultObject = (from RfVal in db.t_reference_values where RfVal.id == plRefValId select new { RfVal.description }).Single();


ServiceSummaryType serviceSummary = (ServiceSummaryType)responseItems.ServiceSummary.Where(a => a.Service.Code == objAvailableServicesAndRates.ServiceId_ServiceTypeCode.Split('#')[0]).First();

Solution
To Solve this problem replace the method single() with SingleOrDefault() method and First() with FirstOrDefault(). the methods SingleOrDefault() and FirstOrDefault returns a null value if there are no source records that matches to the filtering criteria.

Pass Values between ASP.NET Web Pages

If your application redirects (navigates) from one ASP.NET Web page to another, you will frequently want to pass information from the source page to the target page. For example, you might have a page where users can select items to purchase. When users submit the page, you want to call another page that can process the information that the user has entered.
You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:
-Use a query string.
-Get HTTP POST information from the source page.
The following options are available only when the source and target pages are in the same ASP.NET Web application.
-Use session state.
-Create public properties in the source page and access the property values in the target page.
-Get control information in the target page from controls in the source page.

Using a Query StringWhen you use a hyperlink or Response.Redirect to navigate from one page to another, you can add information in a query string at the end of the URL.
Note: Never pass sensitive data using a query string, because the information is visible to users and can easily be modified, thus representing a potential security risk.

To use a query string to pass information
1. In the source page when you specify the URL of the target page, include the information that you want to pass in the form of key-value pairs at the end of the URL. The first pair is preceded by a question mark (?) and subsequent pairs are preceded by ampersands (&), as shown in the following example:

http://contoso.com/products.aspx?field1=value1
http://contoso.com/products.aspx?field1=value1&field2=value2

2. In the target page, access query string values by using the QueryString property of the HttpRequest object, as shown in the following example:
String s = Request.QueryString["field1"];

Getting Post Information from the Source PageWhen the source page uses the HTTP POST action to navigate to the target page, you can retrieve posted values from the Form collection in the target page. Note that you can get only the post values; you cannot read the values of arbitrary controls on the page.

To get the values of controls from the source page in another applicationIn the source page, include a form element that contains HTML elements (such as input or textarea) or ASP.NET server controls (such as TextBox or DropDownList controls) that post values when the form is submitted.

In the target page, read the Form collection, which returns a dictionary of name/value pairs, one pair for each posted value.

The following code example displays the ID and value of every posted control in the source page and displays the posted values in a label named Label1.

Post information from an ASP.NET Web pages includes the values of hidden fields, such as __VIEWSTATE, __EVENTTARGET, and __EVENTARGUMENT, which are used for internal processing in the page. The following code example excludes the values of posted fields that are named with a leading double underscore (__).

void Page_Load(object sender, EventArgs e)
{
System.Text.StringBuilder displayValues =
new System.Text.StringBuilder();
System.Collections.Specialized.NameValueCollection
postedValues = Request.Form;
String nextKey;
for(int i = 0; i < postedValues.AllKeys.Length; i++) { nextKey = postedValues.AllKeys[i]; if(nextKey.Substring(0, 2) != "__") { displayValues.Append(" "); displayValues.Append(nextKey); displayValues.Append(" = "); displayValues.Append(postedValues[i]); } } Label1.Text = displayValues.ToString(); } Using Session StateInformation in session state is available to all ASP.NET Web pages in the current application. However, session state takes server memory, and the information is stored until the session expires, which can be more overhead than you want for simply passing information to the next page.

Session ["field1"] = "value1";

Getting Public Property Values from the Source PageIf you are designing the source page specifically for sharing information with target pages, and both pages are ASP.NET Web pages in the same Web application, you can add public properties in the source page that expose information you want to share between pages. You can then read the values of the properties in the target pages.

This strategy works in two situations:

-When the source page cross-posts to the target page.
-When you call the Transfer method to transfer execution from the source to the target page on the server.

To get public property values from the source page1. On the source page, create one or more public properties and save the page.
The following code example shows a property named CurrentCity that exposes the value of a TextBox control named textCity.
public String CurrentCity
{
get
{
return textCity.Text;
}
}

2. On the target page, add an @ PreviousPageType page directive that points to the source page.
The following code example shows a PreviousPageType directive that references a source page named SourcePage.aspx.

The PreviousPageType directive causes the page's PreviousPage property to be typed to the source page class.

3. In target page code, use strongly typed members of the PreviousPage property to read the source code properties.
The following code example reads the value of the CurrentCity property that is defined in the source page.

Label1.Text = PreviousPage.CurrentCity;

Getting Control Information from the Source Page in the Same Application
If the source and target pages are both ASP.NET Web pages and in the same Web application, you can read the values of controls on the source page while in the target page. You might use this strategy if the source page does not expose public properties containing the information you need.

To get the values of controls from the source page in the same applicationOn the target page, get a reference to the source page by using the target page's PreviousPage property, and then call the FindControl method to get a reference to the control you want.

The following code example gets the value of the source page's TextBox1 control and displays it in the control named Label1:

if (PreviousPage != null)
{
TextBox SourceTextBox =
(TextBox) PreviousPage.FindControl("TextBox1");
if (SourceTextBox != null)
{
Label1.Text = SourceTextBox.Text;
}
}

The FindControl method finds controls in the current naming container. If the control you are looking for is inside another control, you must first get a reference to the container, and then search the container to find the control that you want. A typical example of this situation is when the previous page is a master page and the control that you want to find is inside a ContentPlaceHolder control. The following example is similar to the previous one except that it assumes that TextBox1 is located in a ContentPlaceHolder control that is named ContentPlaceHolder1:

if (PreviousPage != null)
{
Control placeHolder =
PreviousPage.Controls[0].FindControl("ContentPlaceHolder1")
TextBox SourceTextBox =
(TextBox)placeHolder.FindControl("TextBox1");
if (SourceTextBox != null)
{
Label1.Text = SourceTextBox.Text;
}
}

Download Article