Microsoft Technology Consultant, New Jersey. USA

Monday, May 26, 2008

Jobs in Islamabad

Jobs at TMRC Software Engineers Required

Hi Dears,

Our company requires the services of a Software Engineer with at least
one year of prior work experience. The candidate should have excellent
knowledge of working in C#.NET, SQL server and team development
skills. Candidate should be able to work/develop with software
engineering artifacts without any extra guide.

Fresh Candidates can also apply for internship.

Company Description:
TMRC,
a leading firm of Financial & Management Consultants and IT
Solution Providers in Pakistan. We provide quality services in the
areas of web and other IT applications, management consulting,
accounting, corporate management, regulatory accounting, business
consulting, investment consulting, HR management & trainings and
performance evaluation. TMRC has head office in Islamabad and branch at
Lahore.

Post your CVs at adnan@tmrc.com.pk

Regards
--
M Mudassar Adnan
Software Engineer
TMRC
Suite 4, Third Floor,
Yaseen Plaza, Jinnah Avenue,
Blue Area, Islamabad, Pakistan.
Email: send2dani@gmail.com
adnan@tmrc.com.pk
send2dani@iiu.edu.pk
Phone
Mob: +92 333 6802561
Res: +92 514 547170

QA/Testing Jobs in Ultimus Pakistan.

Organization:
Ultimus Pakistan.
Location:
Rawalpindi.
Position
Title:
Quality Assurance Engineer.

Skills Required:
• BS/MS in CS or related discipline.
• Good in development and execution of test plans, test cases, and testing
methodologies.
• Good knowledge of SDLC.
• Working experience of SQL/Oracle databases.
• Must be creative, highly motivated and able to work in Team.
• Must have good communication and analytical skills.
• Experience in .NET architecture is a plus.
• Both fresh and experienced (1-3 years) candidates may apply.

Job Description:
• Responsible for the development and execution of test cases and ensure
thorough functionality coverage.
• Ensure accuracy, thoroughness and reliability of results in the work product.
• Review software documentation for accuracy and completeness.
• Identify means to improve testing techniques that reduces testing time and
enhance/boost end results.

Send your resume in PDF or Microsoft Word format to gshakeel@ultimus.com

About Ultimus:
Ultimus has automated more business processes than any other BPM vendor
worldwide. Enabling companies to accelerate their performance goals at
every stage of corporate growth, Ultimus' SOA-based BPM Suite, featuring broad
integration tools combined with a rapid deployment model, has generated
outstanding results for customers and partners alike.
Ultimus' award-winning technology seamlessly adapts to people, systems and
change. Utilized by more than 1,800 customers worldwide, Ultimus provides
solutions to organizations such as DHL, Microsoft, Daimler, Perdue Farms,
Chevron, Tulane University, Compassion International, Sony, Pfizer, Citigroup,
Charles Schwab, Noor-Islamic-Bank, Frito Lay and AT&T. Ultimus is
headquartered in North America, and has additional offices in Latin America,
Europe, Asia, the Middle East and Australia. For more information, visit www.ultimus.com.

JOBS @ NETSOLACE Inc. STP 1 Islamabad
Technical Report Writers

Qualifications: BS/MCS Computer science or related
descplines.
Experience: 1-3 years of experience, freash candidates
may also apply.
Additional Qualifications: Good communications and
Analytical skills. With charming and grace full
personamlity. Very good presentation skills and can
communicate with higher management. Team player with
extra knolowdge of related technologies and upcomming
updates.

Rreposibilities: Can create flow charts and UML
diagrams. Can write manual that are comprihensive and
user friendly with the essence of audence capturing.

QA/Test engineers:
Skills Required:
• BS/MS in CS or related discipline.
• Good in development and execution of test plans,
test cases, and testing methodologies.
• Good knowledge of SDLC.
• Working experience of SQL/Oracle databases.
• Must be creative, highly motivated and able to work
in Team.
• Must have good communication and analytical skills.
• Experience in .NET architecture is a plus.
• Both fresh and experienced (1-3 years) candidates
may apply.
Knowledge of automated testeing tools is a plus.

Job Description:
• Responsible for the development and execution of
test cases and ensure thorough functionality coverage.

• Ensure accuracy, thoroughness and reliability of
results in the work product.
• Review software documentation for accuracy and
completeness.
• Identify means to improve testing techniques that
reduces testing time and enhance/boost end results.

Salary packages are attractive and good growth
options.

Please send your CVs at Zabbasi@netsolace.com

JOBS @ LMR STP 1 Islamabad
Technical Report Writers

Hi,
TWO really very good resources are required in a multinational company,the requirements are given below and if you don't meet these requirements then please do not forward you CVs.
1. BS or MS Degree Holder( 1 with 2-4 years expreince and one should have 6 months to 1 year experience but fresh graudate who have completed their projetcs can also apply

2. Must have his final project in Java
3. Can work on different envoirments like UNIX,WINDOWS
4. Can work on dektop applications,because we require candidate for dektop application development so its not the post for J2EE. But J2EE would be a plus point
5.Must have some knowledge of shell scripting

6.Must have Good knowledge of Oracle
7.And very important he must have good communication skills because you have to speak often time with other leads/developers from different countries.
if you have all of the above requiremnts then you can forward your CV at this address
abidmehmood@lmkr.com
*Please do not send your CV if you do not have the required skills
Regards,
Abid Mehmood
Application Engineer-Offshore Services
LMK Resources
UAN: 111 101 101 Ext.137
Office: +92 (0)51 2097152 Ext.137
Cell: +92 (0)300-5282465
Fax: +92 (0)51 2879 854-55
VoIP: +1 713 839 4201
E-mail: abidmehmood@lmkr.com
http://www.lmkr.com

Jobs In Islamabad

Friday, May 23, 2008

Rendering Extensions in SQL Server Reporting Services:

Rendering Extensions in SQL Server Reporting Services:
SQL Report Services - report server uses rendering extensions to transform data and layout information from Report Processor into a device-specific format. Reporting Services includes six rendering extensions: HTML, Excel, Text, XML, Image, and PDF. Developers can create additional rendering extensions to generate reports in other formats. The following sections describe the rendering extensions provided with Reporting Services..
HTML Rendering Extension:
When you request a report from the report server, either through a URL or Report Manager, the report server uses the HTML rendering extension to render the report.
Depending on the browser you use, the HTML rendering extension renders reports in either HTML 4.0 or HTML 3.2. Supported HTML 4.0 browsers include:
· Microsoft® Internet Explorer for Windows versions
· Netscape Navigator for Windows version 7.1
HTML 3.2 is used for all other browsers that support HTML, such as earlier versions of the browsers mentioned previously and Internet Explorer for Pocket PC. The HTML rendering extension generates all HTML using UTF-8 encoding.
The HTML rendering extension supports the MIME Encapsulation of Aggregate HTML Documents (MHTML) standard. The rendering extension embeds resources such as images, documents, or other binary files as MIME structures within the report HTML, in a single file. The encoded resources within the report increase the size of the report, but embedding the resources is useful for clients that do not have access to resources stored on the report server or in another location. MHTML reports are also useful for embedding within e-mail messages, because all resources are included with the report.
Excel Rendering Extension:
The Excel rendering extension renders reports that can be viewed and modified in Microsoft Excel 2002 or later. This rendering extension creates files in MHTML, which carry a MIME type of ms-excel and contain HTML meta tags and XML data islands that are specific to Excel. While the Excel rendering extension renders HTML, the rendered report is intended to be viewed in Microsoft Excel, not in a browser.
Resources, such as images, are embedded within the report. For more information about designing reports for the Excel rendering extension, see Designing for Excel Output.
CSV Rendering Extension:
The Comma-Separated Value (CSV) rendering extension renders reports in comma-delimited plain text files, without any formatting. Users can then open these files with a spreadsheet application, such as Microsoft Excel, or any other program that reads text files.
For more information about designing reports for the CSV rendering extension, see Designing for CSV Output.
XML Rendering Extension:
The XML rendering extension renders reports in XML files. These XML files can then be stored or read by other programs.
The XML generated by the XML rendering extension is UTF-8 encoded.
Image Rendering Extension:
The Image rendering extension renders reports to bitmaps or metafiles. The extension can render reports in the following formats: BMP, EMF, GIF, JPEG, PNG, TIFF, and WMF. By default, the image is rendered in TIFF format, which can be displayed with the default image viewer of your operating system (for example, Windows Picture and Fax Viewer). You can send the image to a printer from the viewer.
Using the Image rendering extension to render reports ensures that the report looks the same on every client. (When a user views a report in HTML, the appearance of that report can vary depending on the version of the user's browser, the user's browser settings, and the fonts that are available.) The Image rendering extension renders the report on the server, so all users see the same image. Because the report is rendered on the server, all fonts that are used in the report must be installed on the server.
PDF Rendering Extension:
The PDF rendering extension renders reports in PDF files that can be opened and viewed with Adobe Acrobat 4.0 or later.
Other than these types, you can develop your own format by extending reporting services rendering extension.

Overview of Native XML Web Services for Microsoft SQL Server 2005

Overview of Native XML Web Services for Microsoft SQL Server 2005
Introduction
Microsoft® SQL Server™ 2005 provides a standard mechanism for accessing the database engine using SOAP via HTTP. Using this mechanism, you can send SOAP/HTTP requests to SQL Server to execute:
Transact-SQL batch statements, with or without parameters.
Stored procedures, extended stored procedures, and scalar-valued user-defined functions.
Prior to SQL Server 2005, the only mechanism available to connect to SQL Server was through a custom binary protocol named Tabular Data Stream (TDS). With SOAP/HTTP access, we have provided an open and documented protocol that may be used as an alternative to connect to SQL Server. Providing SOAP/HTTP access enables a broader range of clients to access SQL Server, including "zero foot print" clients, because there is no longer a need to have a Microsoft Data Access Components (MDAC) stack installed on the client device trying to connect to SQL Server. It facilitates interoperability with .NET, SOAP Toolkit, Perl, and more on a variety of platforms. Since the SOAP/HTTP access mechanism is based on well-known technologies such as XML and HTTP, it inherently promotes interoperability and access to SQL Server in a heterogeneous environment. Any device that can parse XML and submit HTTP requests can now access SQL Server.
Many enterprises have heterogeneous environments in which applications that run on UNIX and Linux platforms might require connectivity to SQL Server. Traditionally, the only solution available to such users was to use either JDBC or ODBC drivers. The SOAP/HTTP access now provides another, low-cost alternative. It is extremely useful for scenarios where DBA's have scripts written in Perl that run on UNIX and manage a SQL Server resource. It is also useful in developing client applications that connect to SQL Server using smart integrated development environments (IDEs) that have built-in SOAP/HTTP support, such as Microsoft Visual Studio® .NET or Jbuilder. These IDEs generate proxy code that abstracts the communication with SQL Server and provides objects that the client applications can use. Using SOAP/HTTP also enables anytime, anywhere access to SQL Server, which makes it easier to develop applications for mobile or sporadically connected devices. Once a connection has been established and the server has started processing requests, it can be monitored using existing mechanisms that TDS-based clients such as sqlclient, ODBC, and OLEDB use.
Requirements
SQL Server 2005–native Web services require Microsoft Windows Server™ 2003 as the operating system, because they rely on the kernel mode http driver http.sys that this version provides. Since SQL Server leverages the kernel mode http.sys driver, you do not necessarily need to have IIS installed to expose Web services out of SQL Server; this simplifies administration. Instead, you should base your decision to install IIS on application requirements. For example, certain applications benefit from having an explicit middle tier. In such cases, IIS would be useful.
HTTP Endpoints
Setting up SQL Server as a Web Service that can listen natively for HTTP SOAP requests requires creating an HTTP endpoint and defining the methods that the endpoint exposes. When an HTTP endpoint is created, it must be created with a unique URL that it uses to listen for incoming HTTP requests. For example, if you create an endpoint with the URL "http://servername/sql," SOAP requests that are sent to http://servername/sql will be picked up by http.sys. Http.sys will then route the SOAP requests to the SQL Server instance that hosts the endpoint associated with the URL. From there, the requests will be handed off to the SOAP processing layer within SQL Server.
A SQL Server instance can have multiple endpoints, each of which can expose any number of stored procedures (implemented using either Transact-SQL or CLR) as WebMethods on the endpoint and can be invoked via SOAP remote procedure calls (RPCs). A WebMethod can have a different name than the actual stored procedure that is being exposed. The WebMethod name is what is shown to the user in WSDL as the operation name.
Note It is important to call out that the WebMethod clause in the endpoint is specific to SQL Server 2005 and is unrelated to the ASMX WebMethod attribute.
Users have the ability to execute ad-hoc Transact-SQL statements against the endpoints. This is done by enabling batches on the endpoint using an optional clause in the data definition language (DDL). Enabling batches implicitly results in a WebMethod named "sqlbatch" being exposed to the user. These concepts are illustrated further in the next few sections.
CREATE HTTP ENDPOINT
HTTP Endpoints are created and administered using Transact-SQL DDL. Creating an HTTP Endpoint is the first step in enabling HTTP/SOAP access to SQL Server 2005. Each endpoint has a name and a collection of options that when combined define the behavior of the endpoint.
To illustrate how the CREATE HTTP ENDPOINT is used, let's take a look at a Hello World example for invoking a stored procedure via SQL Server Web Services.
First, create a stored procedure called hello world in the master database, using the following T-SQL. This stored procedure simply displays the string provided in the input parameter.CREATE PROCEDURE hello_world
(@msg nvarchar(256))
AS BEGIN
select @msg as 'message'
END
Next, use the following T-SQL to create the HTTP endpoint which will allow access to this stored procedure as a WebMethod:CREATE HTTP ENDPOINT hello_world_endpoint
AS
AUTHENTICATON = ( INTEGRATED ),
PATH = '/sql/demo',
PORTS = ( CLEAR ),
STATE = STARTED
FOR SOAP (
METHOD
'http://tempuri.org/'.'hello_world'
(NAME = master.dbo.hello_world)
WITH
BATCHES = ENABLED,
WSDL = DEFAULT
)
All endpoints are stored in master, in the metadata view master.sys.http_endpoints. An endpoint doesn't have any SOAP Methods unless you define them. In the above example, we exposed the stored procedure master.dbo.hello_world as WebMethod 'hello_world'; the WebMethod can have any name and, for example, could have been called as 'testproc1' under the 'http://tempuri.org' namespace. Specifying DEFAULT as the value for WSDL clause enables the endpoint to respond to requests for WSDL generating WSDL using the default format. You can suppress WSDL generation by setting WSDL=NONE in the above statement. We discuss the details of WSDL generation in a subsequent section.
Authentication and Security
HTTP Endpoints support the following standard authentication mechanisms: Basic, Digest, Integrated (NTLM, Kerberos), Anonymous - SQL Auth. You first authenticate at the HTTP transport level. Once that is successful, the users SID is used to authenticate with SQL. This is true for all options except ANONYMOUS. Specifying ANONYMOUS at the endpoint only implies that it is anonymous at the HTTP transport level, the client will still need to provide SQL Auth credentials in order to be able to authenticate with the database. When ANONYMOUS is specified an additional HTTP header is required that encodes user name and password. The header must be called "MS-SQLAuth" and the value must be the base64 encoding of a valid username:password. Administrators can also set IP based Restrictions on an endpoint basis, restricting access to endpoints by only allowing specified IP's or ranges of IP's access to HTTP endpoints. Conceptually, an "endpoint" is an "application"—all the methods that implement a single application are mapped to an endpoint, and so security is applied to the endpoint to control access to the application. Endpoints are secure by design; listed below are a few items that help make endpoints secure.
Off by default. No default endpoints or Web methods mapped; must be explicitly created and specified.
Security checks also apply to objects, so a mapped stored procedure is only executable if the user has connect permissions on the endpoint, plus execute permissions on the stored procedure.
No anonymous support for connecting to endpoints. All requests, including requests for WSDL are authenticated. Clients must authenticate, against SQL Server principles, in order to submit any request.
When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:GRANT CONNECT ON HTTP ENDPOINT::hello_world_endpoint TO [DOMAIN\USER]
Clients on non-Microsoft platforms can connect to SQL Server by using either BASIC or SQL Auth. However, using BASIC or SQL Auth requires the channel to be secure, so users can connect only on ports that also have SSL enabled.
WSDL
WSDL is a document written in XML that describes a Web service. It specifies the location of the service and the operations (or methods) the service exposes. WSDL provides the information necessary for a client to interact with a Web service. Tools such as Visual Studio .NET and Jbuilder use the WSDL to generate proxy code that client applications can use to communicate with a Web service. If the endpoint has WSDL enabled, that endpoint will produce WSDL when it receives a request for it. The endpoint created earlier in this article will produce WSDL when an authenticated request is sent to it. A WSDL request is a simple HTTP get request of the form.http://servername/sql/demo?wsdl
The server queries the metadata associated with the endpoint and generates the WSDL dynamically. The WSDL generated provides rich type description of the parameters of the stored procedure. The server has the ability to generate different flavors of WSDL—we call it simple and complex WSDL, depending on whether we use primitive xsd types or complex types to describe the parameters in a request/response message. The default is to use complex types.
SOAP RPC: Method Invocation
In the endpoint created above we have exposed this stored procedure master.dbo.hello_world as a Web method that we can execute via SOAP RPC. The following is an example of the soap message that is sent to the server to invoke this SP via SOAP over HTTP.xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

Hello World!

The result will be a SOAP envelope containing:

Hello World!

Batches: AdHoc Queries
When BATCHES are ENABLED on an endpoint by using the T-SQL command, another SOAP method, called "sqlbatch," is implicitly exposed on the endpoint. The sqlbatch method allows you to execute T-SQL statements via SOAP. This method takes two parameters. The first parameter is named "" and is the batch of T-SQL statements. The second parameter is named "" and is optional; it contains an array of parameter information if the T-SQL statement used any parameters. For example, here is the body of the SOAP request that calls the sqlbatch method and executes a parameterized query.

SELECT EmployeeID, LoginID, Gender
FROM Employee
WHERE EmployeeID=@x
FOR XML AUTO;

xmlns="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlParameter">
1

The response from this SOAP request will contain the following:

Administration and Management
We have seen how simple it is to create an endpoint and submit SOAP requests against the endpoint. Administration is simplified because we now have to administer only one component, namely SQL Server. We don't need to administer an IIS component. The endpoint abstraction provides greater flexibility to administrators, who can enable IP filtering. It also eliminates the need to open up another port because we reuse the port used for http/https Web traffic. You can also provision endpoints for access by only certain individuals by explicitly granting CONNECT privileges only to those users.
Conclusion
With native SOAP access we have provided a protocol based on well-known and documented standards such as SOAP/HTTP to access SQL Server. This promotes interoperability and facilitates outreach by enabling a broader range of clients to connect to SQL Server.

Introducing Replication in Sql server 2000

Microsoft SQL Server 2000 replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency.
Using , you can distribute data to different locations, to remote or mobile users over a local area network, using a dial-up connection, and over the Internet. Replication also allows you to enhance application performance, physically separate data based on how it is used (for example, to separate online transaction processing (OLTP) and decision support systems), or distribute database processing across multiple servers.
Benefits of Replication
Replication offers various benefits depending on the type of replication and the options you choose, but the common benefit of SQL Server 2000 replication is the availability of data when and where it is needed.
Other benefits include:20
Allowing multiple sites to keep copies of the same data. This is useful when multiple sites need to read the same data or need separate servers for reporting applications.
Separating OLTP applications from read-intensive applications such as online analytical processing (OLAP) databases, data marts, or data warehouses.
Allowing greater autonomy. Users can work with copies of data while disconnected and then propagate changes they make to other databases when they are connected.
Scale out of data to be browsed, such as browsing data using Web-based applications.
Increasing aggregate read performance.
Bringing data closer to individuals or groups. This helps to reduce conflicts based on multiple user data modifications and queries because data can be distributed throughout the network, and you can partition data based on the needs of different business units or users.
Using replication as part of a customized standby server strategy. Replication is one choice for standby server strategy. Other choices in SQL Server 2000 include log shipping and failover clustering, which provide copies of data in case of server failure.
When to Use Replication
With organizations supporting diverse hardware and software applications in distributed environments, it becomes necessary to store data redundantly. Moreover, different applications have different needs for autonomy and data consistency.
Replication is a solution for a distributed data environment when you need to:20
Copy and distribute data to one or more sites.
Distribute copies of data on a scheduled basis.
Distribute data changes to other servers.
Allow multiple users and sites to make changes then merge the data modifications together, potentially identifying and resolving conflicts.
Build data applications that need to be used in online and offline environments.
Build Web applications where users can browse large volumes of data.
Optionally make changes at subscribing sites that are transparently under transactional control of the .

Using of stored procedures and ad hoc queries.

Introduction:Well in the previous article you were introduced on how to use dataset. Now we will be seeing about how to work with stored procedures and ad Hoc queries.Stored procedures and ad Hoc queries are the two way of accessing the database and are important while working with DataBase. Usually stored procedure are preferred over the ad hoc queries as it is easy to hack the database information in ad hoc queries where as it is not possible in stored procedure as you use parameter to access the query. Let’s see how to access the data source using stored procedure.Stored procedure is a parameterized method let’s look at an example to see how to write a stored procedure and how it is used in .NET.Store Procedure:Create Procedure SelectAuthorInfo@au_Id Varchar(50)ASSelect * from Authors where au_id =@au_IdGOThis is a simple stored procedure; instead of this Insert, Update or Delete queries can be used in the stored procedure. In the above example the Pubs database is used. This is an in-build data base in MS SQL server.You will now see how to use the stored procedure in .NET application. As the procedure is the select statement it returns some records.VB Code:Dim dsetMydata As New DataSetDim connString As String = "server=.; database=pubs; UID=sa; PWD=;"Dim myConn As New SqlConnection(connString)Dim strQuery As String = "SelectAuthorInfo"Dim myCommand As New SqlCommand(strQuery, myConn)myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"Dim myAdapter As New SqlDataAdapter(myCommand)myAdapter.Fill(dsetMydata)DataGrid1.DataSource = dsetMydata.Tables(0)C# Code:DataSet dsetMydata = new DataSet();string connString = "server=.; database=pubs; UID=sa; PWD=;";string strQuery = "SelectAuthorInfo";SqlConnection myConn = new SqlConnection();SqlCommand myCommand = new SqlCommand(strQuery,myConn);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);myAdapter.Fill(dsetMydata);DataGrid1.DataSource = dsetMydata.Tables(0);The above code gets the records for the au_id = “172-32-1176” this is done by using stored procedure. The Stored procedure has a parameter called @au_id, you are passing the value for the parameter from code behindVB Code:myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"C# Code:myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";You have to note that the command object is used to pass the parameter for the SQL stored procedure. For passing parameter you have to use SqlParameter member as shown in code. The above code executes the stored procedure and returns a record for that au_id
posted by Mitesh V. Mehta at 1:54 AM

How to find Second Maximum value from the table

Sometimes we require to find the Second Maximum values form the table.

Lets takes an examples where we need to find the second max Salary of an employee.

This requires some round work as there is no direct way to get the Second-Max, as in the case of getting the Max value.

Different queries that we can use are as follows:

1)
select max(sal) from table where sal < (select max(sal) from table)

2)
Select min(sal) from table where sal in (select top 2 sal from table order by sal desc)

In the first case, u get the max salary again check out for the max salary less then that value. While in the 2nd case u get the two most paid person and then take the minimum value of the two.

posted by Mitesh V. Mehta at 11:41 PM 4 comments

Monday, April 18, 2005
Using of stored procedures and ad hoc queries.
Introduction:
Well in the previous article you were introduced on how to use dataset. Now we will be seeing about how to work with stored procedures and ad Hoc queries.

Stored procedures and ad Hoc queries are the two way of accessing the database and are important while working with DataBase. Usually stored procedure are preferred over the ad hoc queries as it is easy to hack the database information in ad hoc queries where as it is not possible in stored procedure as you use parameter to access the query. Let’s see how to access the data source using stored procedure.

Stored procedure is a parameterized method let’s look at an example to see how to write a stored procedure and how it is used in .NET.

Store Procedure:
Create Procedure SelectAuthorInfo
@au_Id Varchar(50)
AS
Select * from Authors where au_id =@au_Id
GO

This is a simple stored procedure; instead of this Insert, Update or Delete queries can be used in the stored procedure. In the above example the Pubs database is used. This is an in-build data base in MS SQL server.

You will now see how to use the stored procedure in .NET application. As the procedure is the select statement it returns some records.

VB Code:

Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "SelectAuthorInfo"
Dim myCommand As New SqlCommand(strQuery, myConn)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"
Dim myAdapter As New SqlDataAdapter(myCommand)
myAdapter.Fill(dsetMydata)
DataGrid1.DataSource = dsetMydata.Tables(0)

C# Code:

DataSet dsetMydata = new DataSet();
string connString = "server=.; database=pubs; UID=sa; PWD=;";
string strQuery = "SelectAuthorInfo";
SqlConnection myConn = new SqlConnection();
SqlCommand myCommand = new SqlCommand(strQuery,myConn);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myAdapter.Fill(dsetMydata);
DataGrid1.DataSource = dsetMydata.Tables(0);

The above code gets the records for the au_id = “172-32-1176” this is done by using stored procedure. The Stored procedure has a parameter called @au_id, you are passing the value for the parameter from code behind

VB Code:

myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"

C# Code:

myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";

You have to note that the command object is used to pass the parameter for the SQL stored procedure. For passing parameter you have to use SqlParameter member as shown in code. The above code executes the stored procedure and returns a record for that au_id

posted by Mitesh V. Mehta at 1:54 AM 0 comments

Tuesday, April 05, 2005
How to protect against SQL Injection attacks?
The best way is not to concatenate SQL strings in the first place -- use parameterised queires instead.

You should also use regular expressions to make sure that only 'appropriate' data is entered into fields. For example, a ZIP code should only ever be numbers, so you can have a RegExp like \d{5} to enforce 5 digits (US postal code).

You should also look for things like quotes and hyphens which may be signs of someone trying to inject code, but remember that they are also valid characters sometimes.

posted by Mitesh V. Mehta at 4:59 AM 0 comments

Tuesday, September 21, 2004
SQL Server Express 2005
Summary: Discover the differences in SQL Server Express from other SQL Server versions that make it easier to use and easier to protect, and migrate your existing JET applications to the more secure and more stable SQL Server Express.

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

With Best Regards,
Mitesh Mehta
Email : miteshvmehta@gmail.com
http://cc.1asphost.com/miteshvmehta/

posted by Mitesh V. Mehta at 5:36 AM 1 comments

Wednesday, September 01, 2004
Resetting / Adjusting Identity Columns in SQLServer
Many time a situation comes where we would like to reset identity column values like for testing u inserted few records and now u would like to reset or you have deleted good amount of records and u would like to re-start from the current max value after deletion. Recently I had this requirement, every time we test project we wanted to reset identity values. stupidly we where just running the big whole generated SQL script. The Transact-SQL's DBCC statements gave me an alternative approach and now I see that things can be done programmatically also.

DBCC stands for Database Console Command statements: DBCC CHECKIDENT is used to Checks the current identity value for the specified table and, if needed, corrects the identity value. Simply issue a command like this:

USE TrainingDB
GO
DBCC CHECKIDENT (MyOrders, RESEED, 1)
GO
This SQL code will forcibly reset the seed value to 1 for the specified table.

Oracle has similar thing called for Auto increment called "Sequence" and it has 2 method S1%Nextval and s1%CurrentVal so with this is possible to find out what is the current auto increment value, for some reasons u would like to find out same thing in SQLServer then
This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

USE pubs
GO
DBCC CHECKIDENT (jobs, NORESEED)
GO

Checking identity information: current identity value '7', current column value '7'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The out put is a string so some string parsing is required here. not tried any other stunt so if anyone knows something more do

Sql Server 2005 Videos

An Introduction to SQL Server Reporting Services (Pt. 1)

SQL Performance Optimization

SQL Server 2005 Video Tutorial - 1 - Installing

Microsoft SQL Server Analysis Services

Microsoft SQL Server Analysis Services MS SQL SSASWith SQL Server 2005 Analysis Services provides, for the first time, a unified and integrated view of all your business data as the foundation for all of your traditional reporting, OLAP analysis, Key Performance Indicator (KPI) scorecards and data mining.By introducing a single Unified Dimensional Model (UDM), Analysis Services combines the flexibility and richness of the traditional relational reporting model with the user-friendly powerful analytics and the superb performance of the classic OLAP model.With Proactive Caching and advanced business intelligence capabilities, Analysis Services moves into the realm of real-time analytics while maintaining MOLAP class performance.Major enhancements in enterprise capabilities including scalability, manageability, and productivity, soundly position Analysis Services as the cornerstone of your Business Intelligence (BI) infrastructure and with the deep integration with SQL Server 2005 Reporting Services and with Microsoft Office, Analysis Services 2005 will extend business intelligence to every level of your business.Unified Dimensional ModelBy combining the best aspects of traditional OLAP analysis and relational reporting, Analysis Services 2005 provides a metadata model that covers both sets of needs. A set of cubes and dimensions defined in Analysis Services 2005 is referred to as a Unified Dimensional Model, or UDM. The UDM is a central metadata repository defining business entities, business logic, calculations, and metrics that serves as the source for all reports, spreadsheets, OLAP browsers, KPIs and analytical applications.Using the powerful new Data Source View feature, the UDM is mapped to a host of heterogeneous back-end data sources allowing a complete and integrated picture of the business regardless of the location of the data.With the UDM's friendly descriptions of the business entities, navigation hierarchies, multiple perspectives, and even automatic translations to native languages the end-users will find it easy to explore the corporate business data.Proactive CachingProactive Caching enables the combination of real-time updates with MOLAP class performance. Analysis Services 2005 maintains a highly compressed and optimized data cache that gets automatically maintained as the data in the underlying source databases changes. The cache provides superb query performance and isolates the back-end source systems from the load of the analytical queries.Advanced Business IntelligenceKey Performance Indicator (KPI). The KPI framework provides a rich centralized repository defining key metrics and scorecards. The KPI framework in Analysis Services 2005 allows you to easily build balanced scorecard and other types of business performance management applications.Translations. Translations provide a simple, centrally-managed mechanism for storing and presenting analytic data to users in their preferred languages. * MDX Scripts. MDX Scripts are the new mechanism for defining Calculated Members, Named Sets, and Cell Calculations. * Business Intelligence Wizards. A set of easy to use wizards will help even the most novice user in modeling some of the more complex business intelligence problems. * Semi-Additive Measures. New measure aggregation type for advance data modeling includes last-non-empty, last-child, first-child, average-of-children and even by-account-type.Web ServicesXML for Analysis (XML/A) is the native, standards-based protocol for communicating with the Analysis Services server. New kinds of applications are enabled and easy to develop - applications that integrate analytics with operations in real time that can access the data from any platform and any language.With XML/A as the native protocol, Analysis Services clients can be configured to have a zero footprint, and each server is automatically a web service. * A light-footprint Win32 layer is available for backward compatibility with tools that work with Analysis Services 2000 on OLE DB for OLAP, ADOMD, and ADOMD.Net.
Enterprise CapabilitiesAnalysis Services 2005 sets a new standard for BI servers in enterprise scalability, manageability, and productivity:
Scalability. Analysis Services 2005 scales to the most demanding analytical and reporting applications. With Analysis Services' new dimension architecture, UDM-enabled applications allow users to quickly perform rich and intuitive adhoc analysis using hundreds of dimensions and hierarchies. Hierarchies, whether flat or multilevel, make it easier for the users to navigate and query UDMs with hundreds of millions of members.
The new architecture also removes the limitations of memory resident dimensions and the number of children members per parent. With XML/A-based communication protocol that is paired with greatly optimized role based security; Analysis Services 2005 allows a UDM to handle thousands of users, enabling enterprise scale BI applications using web/middle tier or client/server architectures. Additionally, with parallelized partition and dimension processing Analysis Services can handle more data faster.
Manageability. By integrating the management tools for the relational engine and Analysis Services, the BI administrator benefits from having a single uniform environment for managing Analysis Services along with SQL Server. With SQL Management Studio, administrators can easily script Analysis Services objects and operations/tasks. Administrators can use a rich editor for MDX and DMX queries. Profiler can be used to trace, capture, and replay Analysis Services queries and other commands. Multi-Instancing, enhanced backup and restore, synchronization of databases across servers, and improved fine-grained administrative permissions are some of the new manageability tools available.
Productivity. Integrated with Visual Studio, Analysis Services intuitive wizards and editors provide a true application development environment supporting the full life cycle of the project. Source control, versioning, workstation isolation, embedded debugging, and configuration management are some of the benefit of the new BI Development Studio. Additional Information.
Source: For more information on business intelligence BI software, Microsoft SQL Server Analysis Services, check out http://www.calumo.com/microsoftsqlserveranalysis.html

SQL Server 2005 Reporting Services - Reports Management & Execution Web Services

One of the important and interesting features in SQL Server Reporting Services is the web services support. They can be divided into two parts; one for the reports execution and one for the reports management.
The ReportingService2005 class is responsible for the reports management web service and contains all the required methods & properties for the same. Similarly ReportExecutionService class holds the methods & properties for the execution part. For all the member details and what each of them does, one can refer the MSDN docs; nicely documented so won't reproduce the same stuff again :-) For quick reference (http://msdn.microsoft.com/en-us/library/ms155071.aspx)
The example mentioned below is a small POC that I was working on recently and I thought that someone could benefit from what I learned during experimenting with the web services. The example is not very fancy and there are definitely better ways of doing the stuff...no arguments about that. If anyone feels that this code could be of any use to them, s\he is free to go ahead and do so.
Here is the storyline that is used. The reports server has all the reports published and organized in folders based on the sensitivity of the data that the reports holds. The user would only have access to the reports under a particular folders to which he has been granted access. Mostly, the administrator would allow\restrict access to the reports and\or folders using the management studio for reporting services, but over here we are dealing (and had to mimic) with a third party web access manager who decides the access to the reports\folder based on the current user login. Based on the logged in user it would decide what reports he can see and what not.
In other words the account used to access the reports on the reports server is different than the user login account. It has access to all the reports on the server. The "logged in user's" access is decided by the third party web access software and is "not" dependant on the windows login account.
This is different than the way it would have behaved if the rights access would have been done by the management studio. In that case the windows account and the role to which he belongs is used for deciding the access he has to a particular report.
SSRS Management Web Services
Based on the above mentioned story line, in this example we have two users Tom & Joe. They have access to the "AdventureWorks Reports" folder and "WellsReport" folder respectively. When a user is logged in and runs the app, he is only shown the respective folder\reports to which he has access. This control would be a characteristics of a web access manager and is mimicked by a dummy function (WebAccessManagerBlackBox), which takes a parameter of username. We get the current user by making a call to "System.Security.Principal.WindowsIdentity.GetCurrent().Name" and passing it as a parameter while calling the said function. The trick is that the report's path is set to the folder to which the current user has access and thus only the reports below in the hierarchy to which the user has access are displayed.
The web service method, ListChildren returns the list of the catalogs (report items) which is then passed to populate the tree control.
This would display only the reports to which the user has access and populate the tree view control on the left. Once the user clicks on one of the report, the adjacent reports viewer control would display the selected report. To add some spice to the story line it is assumed that the user might have to open more than one report or even the same report twice so that he can do some comparitative analysis. This feature is made available by the use of the tabbed controls along with multiple reports. When the user clicks on a report in the tree view the current tab control displays the report.
Also, when the user runs the application, he is shown a dummy report which has nothing but a message asking him to click on one of the report in the tree node.
Here is the code...
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using MServices.ReportingService;
namespace MServices{public partial class MServicesMain : Form{private static string reportServer = "/reportserver" ;?=";?">/reportserver" ;?=";?">/reportserver" ;?=";?">/reportserver" ;?>/reportserver";'>http:///reportserver";private static char separator = '/';private static char[] separators = { separator };private static string separatorString = new string(separator, 1);private static string reportPath = "/";
public MServicesMain(){InitializeComponent();}
private void MServicesMain_Load(object sender, EventArgs e){// added this peice to handle the restriction of the reports per user basis.WebAccessManagerBlackBox(System.Security.Principal.WindowsIdentity.GetCurrent().Name);
ReportingService.ReportingService rService = new ReportingService.ReportingService();rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
CatalogItem[] catalogItems;catalogItems = rService.ListChildren(reportPath, true);
PopulateTree(catalogItems);
// added this peice to handle the restriction of the reports per user basis.if (!reportPath.Equals("/")){ReportTreeView.Nodes[0].Text = reportPath.Substring(1);}ReportViewer1.RefreshReport();}
private void WebAccessManagerBlackBox(string username){if (username.Equals("ADV-SQL2\\Tom")){reportPath = "/AdventureWorks Reports";}if (username.Equals("ADV-SQL2\\Joe")){reportPath = "/WellsReport";}}
private void PopulateTree(CatalogItem[] catalogItems){foreach (CatalogItem item in catalogItems){if (item.Type == ItemTypeEnum.Report && item.Name!= "DummyReport"){string path = item.Path.Remove(0, reportPath.Length);string[] tokens = path.Split(separators);AddNodes(tokens, 0, ReportTreeView.Nodes);}}}
private void AddNodes(string[] tokens, int index, TreeNodeCollection nodes){TreeNode node = null;
for (int i = 0; i < text ="="" node =" nodes[i];break;}}" node ="="" node =" new" text =" tokens[index];nodes.Add(node);" 1 ="="" tag =" String.Join(separatorString.ToString()," text =" tokens[tokens.Length"> index){AddNodes(tokens, index, node.Nodes);}}
private void ReportTreeView_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e){string tag = String.Empty;
if (e.Node.Tag != null){tag = e.Node.Tag.ToString();
((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]).ServerReport.ReportServerUrl = new Uri(reportServer);
((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]).ServerReport.ReportPath = reportPath + tag;
((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]).RefreshReport();
tabControl.SelectedTab.Text = tag.Substring(tag.LastIndexOf("/")+1);}}}}
The image shows the form in action...

SSRS Execution Web Services
The sample in the online documentation does an excellent job of getting the concept across. Please refer to the same at the following URL for more details.
http://technet.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx

Exposing a Database as a Web Service

Introduction
When you look back the computer industry, you can clearly identify different different technologies in different time periods. In any given time period, databases have a very high priority in the industry, from small scale to large scale business. You know that Web Services are becoming today's technology and everyone is in the process of moving their applications into the SOA or Web Services world. When doing so, you have a number of advantages, although you have to do a considerable amount of work to do get everything working. Therefore, when considering the advantages (such as accessibility, security, extensibility, and so forth), companies are trying to expose their applications as Web Services, or they are trying to give a Web Service interface to their applications. So, exposing and giving a Web service interface to a database is also becoming a hot topic, and DataServices is very good example of that.
There are a number of approaches that industries have employed when they want to expose their databases as Web Services. The DataService approach can be considered as one of the good approaches, and you can find a number of different DataServices solutions as well. You can consider the WSO2 DataService solution as a good example candidate for a DataService solution that is built on Axis2.

An Approach for Exposing a Database as a Web Service
However, in this article you are not going to examine any of the DataServices approaches; rather, you will learn a very simple approach of exposing a databases as a Web Service using Axis2. You can consider that as exposing a database using Axis2 POJO. To get a better understanding about this approach, having good knowledge about Axis2 will be an added advantage; the Reference section has links to the recommended articles. If you follow then, you are in good shape.
Creating a Database
To expose a database as a Web Service, you first need to have the database around, so create a very simple databases with one table to store personal information. The table will have four fields to store ID, name, address, and age. This sample application is based on MySQL databases, but you can do the exact same thing with any given database.
Run the following database script to create the database table. (First, create a DB schema called "dbsample" and then create the table inside that.)CREATE TABLE PERSON (ID INTEGER NOT NULL,
NAME VARCHAR (100) NOT NULL,
ADDRESS VARCHAR (500),
AGE INTEGER,
PRIMARY KEY (ID));

Inserting Sample Data
By running the following script, you can populate the database with a set of data.INSERT INTO PERSON (ID, NAME, ADDRESS, AGE)
Values (100, "Deepal Jayasinghe", "No 59, Flower Road,
Colombo, Sir Lanka", 29)
INSERT INTO PERSON (ID, NAME, ADDRESS, AGE)
Values (101, "Franck", "San Jose, CA", 30)
INSERT INTO PERSON (ID, NAME, ADDRESS, AGE)
Values (102, "Samisa Abeysinghe", "Colombo, Sri Lanka", 34)
You can add some more data if you want. You can either insert data by running SQL or you can just insert the data by using the MySQL Query Browser.
In the rest of this article, you will learn different ways of exposing the database as a Web service:
List all the people in the DB
List the names of all the people in the DB
List the names and ages of given people in the DB
Insert a person into the DB
Now, it is time to write your POJO class to perform the functionality you want. Before you do this, you need to address a few questions:
Where will you create the database connection?
Where will you store the database connection?
How will you close the database connection?

Service Life Cycle Management and Database Connection Handling
To answer all those questions, Axis2 has something called ServiceLifeCycle management support. Therefore, the correct and best approach would be to get the life cycle management support from Axis2. First, you need to write the life cycle management class and create and store the database connection there. It should be noted here that when Axis2 starts up (at the time of service deployment), the ServiceLifeCycle class will be invoked; also, when the system goes down, the s ServiceLifeCycle class will be invoked again. As you can see, you are going to create the DB connection at the service deployment time and store the database connection inside the ConfigurationContext object.
You can download the source code for the s ServiceLifeCycle implementation class from the Download section (DBSampleServiceLifeCycle.java).
Creating POJO Classes
Now, you have written code to open the DB connection and to store that in ConfigurationContext, so now it's time to write the POJO classes. In this case, you will write a JavaBean object to represent the Person with four fields (id, name, address, and age). When listing all the people in the DB, you just create an array of Person objects and return that (Person.java).
When listing names of all the persons in the DB, you just return the String array. When getting the name and age for a given person, you create new JavaBean to represent those two fields and return that (NameAge.java). In the case of inserting a person object into the DB, you write a method in the POJO class (PersonDBService.java) to get four method parameters.
Your service implementation class, which does all four operations described above, is shown below.package dbsample;
import org.apache.axis2.context.MessageContext;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class PersonDBService {
public Person[] listAllPeople() {
// implementation logic
}
public String[] listPeopleNames() {
// implementation logic
}
public NameAge getNameAge(int id) {
// implementation logc
}
public void insertPerson(int id,
String name,
String address,
int age) {
// implementation logic
}
}

The service description file, or the services.xml for your service, will be a very simple one. You can download that from the Download section. The services.xml file will look like the following:class="dbsample.DBSampleServiceLifeCycle">
Exposing a DB as a Web Service

mep="http://www.w3.org/2004/08/wsdl/in-only"
class="org.apache.axis2.rpc.receivers.
RPCInOnlyMessageReceiver"/>
mep="http://www.w3.org/2004/08/wsdl/in-out"
class="org.apache.axis2.rpc.receivers.
RPCMessageReceiver"/>

dbsample.PersonDBService

Deploying the Service
Before you deploy the service, you need to create a service archive file using your compiled classes and services.xml. You can use any available tools, or you can just create a zip file from the compiled code and services.xml and rename that as dbsample.aar. I have created a service archive file from the compiled code so that you can download that and just try it out.
Because you need to have the DB connection jar, first you have to copy the mysql-connector jar file in the class path or to /webappes/axis2/WEB-INF/lib. Next, you can copy your service archive file into /webappes/axis2/WEB-INF/services directory. Then start Tomcat (or your application server).
Now, type http://localhost:8080/axis2/services/DBSampleService?wsdl in your browser (the port may vary depending on the application server configurations); then, you will be able to see the WSDL file for your sample DB service. This is simply an indicator that your service is up and running. If you do not get the WSDL file, something has gone wrong with your database or database driver.

Invoking the Service
In this sample, you are not going to write a Java client to invoke the service; if you want, you can try that out. However, you will focus more on invoking the service just using the browser or REST manner.

List all the People in the DB
To see all the people in the database, you can invoke the "listAllPeople" method in your service. Just type the following in the browser and see what you are getting. (This is how you invoke the listAllPeople method in the REST manner.)
http://localhost:8080/axis2/services/DBSampleService/listAllPeople
You will get something like the following, which is simply all the people in the DB.

No 59, Flower Road, Colombo, Sir Lanka

29
100
Deepal

San Jose, CA
30
101
Franck

Colombo, Sri Lanka
34
102
Samisa Abeysinghe

List the Names of All the People in the DB
Getting a list of names of the people is almost equal to the method invocation above. To get all the names, just type the following in the browser and see what you get:
http://localhost:8080/axis2/services/DBSampleService/listPeopleNames
Then, you will see something like the following, which is exactly all the names of the people in the DB.
Deepal
Franck
Samisa Abeysinghe

Getting the Name and Age for a Given Person
Now, you will the name and age of a given person, so will be a matter of giving the person ID and the Web Service gives you the name and age of the person represented by the id. Invoking that service is just a matter or typing the following in the browser:
http://localhost:8080/axis2/services/DBSampleService/getNameAge?id=100
Then, you get the following output:

29
Deepal

Inserting a New Person Object
Inserting a person object also can be done using a REST call. You need to pass the id, name, address, and the age to add a new person into the table. You can pass them as URL query parameters, as you can see below.
http://localhost:8080/axis2/services/DBSampleService/insertPerson?id=130&name=Peter&address=No 5, Colombo, Sri Lanka&age=56
This will call the service and insert a new raw to the database. If you want to make sure that the new data in the database just lists all the people again, you can see the new person also in the DB.

Conclusion
You now have a very good understanding about exposing a database as a Web Service using Axis2. The sample you did was a very simple one, but you can write any complex example using this approach. If you have a good understanding about Databases and Axis2, you can do very cool stuff. Trying out the sample and creating your own sample will help you to understand the concept very clearly.
Downloads
The ServiceLifeCycle implementation class
person.java
NameAge.java
PersonDBService.java
services.xml
dbsample.aar
All the files accompanying this article

References
Axis2 deployment architecture: www.developer.com/open/article.php/3557741
Six ways of deployment: www-128.ibm.com/developerworks/library/ ws-axis2soap/index.html
Axis2 POJO: http://www.developer.com/java/other/article.php/ 10936_3726461_3

New HTTP Endpoints Create SQL Server 2005 Web Services

SQL Server 2000 offers some capabilities for returning XML output via HTTP using SQLXML—and, of course, SQLXML supports Web services creation. Although not rocket science, setting up, configuring, and using Web services in SQL Server 2000 does require a little effort (see the SQLXML documentation about Web services in SQL Server 2000).
When .NET came about, people began writing Web services in C# or VB.NET. They simply connected into SQL Server (or any data store, for that matter) to retrieve the data. .NET made creating Web services as simple as 1-2-3:

Create some stored procedures.
Write some WebMethods to return the data from said stored procedures.
Presto! You have a Web service.
But now, the next generation of Web services is hitting the streets. SQL Server 2005 supports native HTTP SOAP via a feature known as HTTP Endpoints. For those who don't know, Simple Object Access Protocol (affectionately known as SOAP) is a lightweight messaging protocol that Webopedia defines as follows:
"Short for Simple Object Access Protocol, a lightweight XML-based messaging protocol used to encode the information in Web service request and response messages before sending them over a network. SOAP messages are independent of any operating system or protocol and may be transported using a variety of Internet protocols, including SMTP, MIME, and HTTP."
This new HTTP Endpoints feature in SQL Server 2005 is the subject of this article.
HTTP Endpoints
So what is an HTTP Endpoint? You may have heard that it is SQL Server 2005's means for creating Web services, but it actually is much more. An HTTP Endpoint also is a means of creating interfaces via HTTP or TCP for SOAP, T?SQL, Service Broker, and even database mirroring. Although these other functions are very intriguing, this discussion concerns only the ability to create Web services in SQL Server—Web services that can return rowset data, scalar values, messages, and even errors, all of which are serialized into XML automatically. And, an HTTP Endpoint does all of this without requiring you to install IIS (it uses the Windows 2003 kernel module http.sys).
Because I like to learn from examples, I am going to teach by example. I demonstrate how to use HTTP Endpoints by walking through an example from start to finish. Before starting, here are a few notes to consider:
This demonstration uses the new demo database called AdventureWorks.
Native HTTP SOAP in SQL Server 2005 is not supported on Windows XP. If you want to try the examples, you need Windows Server 2003.
SQL Server 2005 Express Edition (the new MSDE) does not support HTTP Endpoints, so be sure to install the Developer Edition.
I do not explain how to use SQL Server Management Studio to execute SQL scripts. I trust you know how to do this.
Security
Because HTTP Endpoints are a server-level feature, security of endpoints is also on the server level. Of course, the serveradmin system role can create, alter, and drop endpoints from the SQL Server instance, but how do you allow developers to manage these endpoints without giving them the excessive permissions of this system role?
The answer lies in SQL Server 2005's new ability to assign server-level permissions to logins, as shown here:GRANT ALTER ANY HTTP ENDPOINT TO peter
With this command, you can allow the peter login to manage HTTP Endpoints without giving other unnecessary permissions. The following is a list of permissions that you can modify via GRANT, DENY, or REVOKE:
{GRANTDENYREVOKE} ALTER ANY ENDPOINT TO LoginControls the ability to alter any HTTP Endpoint; also allows permission to transfer ownership and connect to any endpoint
{GRANTDENYREVOKE} ALTER ON ENDPOINT:: EndPointName TO LoginControls whether a login can alter a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint
{GRANTDENYREVOKE} CONTROL ON ENDPOINT:: EndPointName TO LoginControls whether a login can alter or drop a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint
{GRANTDENYREVOKE} CONNECT ON ENDPOINT:: EndPointName TO LoginControls whether or not a login can connect to (execute requests against) an HTTP Endpoint
{GRANTDENYREVOKE} TAKE OWNERSHIP ON ENDPOINT:: EndPointName
TO Login
Controls whether a login can take ownership of the HTTP Endpoint
{GRANTDENYREVOKE} VIEW DEFINITION ON ENDPOINT:: EndPointName
TO Login
Controls the ability for a login to see the metadata (definition) of the HTTP Endpoint
Consider one last example. If I want to deny the peter login the ability to use an HTTP Endpoint named SQLEP_Financial, I would execute the following statement:

Providing a Source for Data
The first order of business is to create the code components that will be the source of information for the endpoint. I create two stored procedures and one user-defined function (UDF), as shown in Listing 1:
Listing 1USE AdventureWorks
GO
CREATE PROCEDURE prProductList
AS
SELECT Product.ProductID, Product.ProductNumber, Product.Name
FROM Production.Product AS Product
INNER JOIN Production.ProductInventory AS Inventory
ON Product.ProductID = Inventory.ProductID
INNER JOIN Production.Location AS Location
ON Inventory.LocationID = Location.LocationID
WHERE Product.ListPrice > 0
AND Location.LocationID = 6 --Products from Misc Storage only
ORDER BY Product.Name
GO
CREATE PROCEDURE prProductStockInfo
@ProductID int
AS
IF (SELECT ListPrice FROM Production.Product
WHERE ProductID = @ProductID) = 0
BEGIN
RAISERROR ('Product not available for retail sale', 11, 1)
END
ELSE
BEGIN
SELECT Product.ProductID, Product.ProductNumber,
Product.Name, Product.Weight,
Product.WeightUnitMeasureCode,
Product.ListPrice, Inventory.Quantity
FROM Production.Product AS Product
INNER JOIN Production.ProductInventory AS Inventory
ON Product.ProductID = Inventory.ProductID
INNER JOIN Production.Location AS Location
ON Inventory.LocationID = Location.LocationID
WHERE Product.ProductID = @ProductID
AND Location.LocationID = 6 --Misc Storage
END
GO
CREATE FUNCTION dbo.fnProductPhoto (@ProductID int)
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE @largePhoto varbinary(max)
SELECT @largePhoto = ProdPhoto.LargePhoto
FROM Production.ProductPhoto AS ProdPhoto
INNER JOIN Production.ProductProductPhoto ProdProdPhoto
ON ProdPhoto.ProductPhotoID = ProdProdPhoto.ProductPhotoID
WHERE ProdProdPhoto.ProductID = @ProductID
RETURN (@largePhoto)
END
The first procedure returns a list of products (product ID, name, and number) from the AdventureWorks database. The second procedure returns more details about a product, including inventory levels. The UDF returns a single varbinary value containing the product photo.
Creating the Endpoint
Once you have your procedures and functions set up, you can create the endpoint (Web service). The following code creates the endpoint on my server:CREATE ENDPOINT SQLEP_AWProducts
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'win2k301'
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
WEBMETHOD 'ProductStockInfo'
(NAME='AdventureWorks.dbo.prProductStockInfo'),
WEBMETHOD 'ProductPhoto'
(NAME='AdventureWorks.dbo.fnProductPhoto'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
Believe it or not, you are now ready to consume this Web service. Before doing that, though, examine the CREATE ENDPOINT statement. Starting from the top, the first thing you will notice is the name of the endpoint, SQLEP_AWProducts, which is how you refer to the Web service within your client code (see Figure 1). PATH is the virtual path on the server for the Web service. So, for this example, you would access the Web service at http://win2k301/AWproducts, but it would be called SQLEP_AWProducts in your .NET application code. Table 1 provides explanations for this and other arguments used in the AS HTTP clause.

Click here for a larger image.
Figure 1: Object Browsing the Web Service in Visual Studio.NET 2005
Table 1: Arguments Used in the AS HTTP Clause
Argument
Description
PATH
The virtual URL path on the server where the Web service will reside
AUTHENTICATION
The method of authentication; can be BASIC, DIGEST, or INTEGRATED
You should try to use INTEGRATED when possible, since it is the most secure. It will try to use Kerberos-based authentication if possible (otherwise, NTLM).
DIGEST is not as secure as INTEGRATED. You should use it only if INTEGRATED authentication is not possible.
BASIC authentication is the least secure. You should use it only if you can't implement either INTEGRATED or DIGEST authentication methods. BASIC requires SSL (see PORTS below).
PORTS
CLEAR (HTTP - port 80 by default) SSL (HTTPS - port 443 by default)
SITE
The name of the server on which the Web service is running
The STATE argument can have one of three values:
STARTED—listening and responding
DISABLED—neither listening nor responding
STOPPED—listening, but returns errors to client requests
You can change the state of an existing Web service using the ALTER ENDPOINT statement. For example, to disable the endpoint, you can execute:ALTER ENDPOINT SQLEP_AWProducts STATE = DISABLED
The FOR SOAP clause determines which methods the Web service will expose, which other features are exposed (dynamic SQL, for example), and other characteristics of the Web service. In this example, three methods are exposed: the two stored procedures and the UDF shown in Listing 1. WEBMETHOD requires the name of the exposed method as its first argument. As you can see in the code, it does not have to match the name of the actual stored procedure or UDF that it is exposing. The next argument of WEBMETHOD is the fully qualified NAME of the object you are exposing.
The BATCHES argument specifies whether or not the Web service supports ad hoc queries. WSDL indicates how the WSDL document generation will occur; DEFAULT indicates that the WSDL document will be created automatically. SQL Server 2005 allows you to implement custom WSDL generators using the CLR-based abilities of SQL Server 2005 (see Books Online for more details). DATABASE is the name of the database that will be accessed from the endpoint (endpoints are defined at the server level). And finally, NAMESPACE is, well, the namespace for the endpoint.
You have now officially created a Web service using SQL Server 2005. Congratulations.

The Client to Consume Your Web Service
Consuming a Web service created in SQL Server 2005 as an HTTP Endpoint is similar to consuming a Web service created in C#. That's the beauty of Web services and SOAP. Begin a new Windows application in Visual Studio.NET and add the following controls to the form (ControlType: name):
Button: btnExecSP
DataGridView: dgvProduct
ListBox: lstProducts
PictureBox: picProduct
You will need to add the Web reference before writing any code. Again, you can browse to http://server/awproducts?wsdl to create the Web reference. Keep in mind that your Web reference object will be named server_AWProducts (my machine is named win2k301 so I browse to http://win2k301/awproducts?wsdl, and my Web reference object is win2k301_AWProducts). You will have to tweak the code to the Web reference name that you create.
Once the Web reference is in place, you can double-click the Button control and add the code in Listing 2.
Listing 2private void BtnExecSP_Click(System.Object sender, System.EventArgs e)
{
win2k301_AWProducts.SQLEP_AWProducts proxy =
new win2k301_AWProducts.SQLEP_AWProducts();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
object[] products = proxy.ProductList();
System.Data.DataSet resultDS;
if (products[0].ToString() == "System.Data.DataSet")
{
resultDS = (System.Data.DataSet)products[0];
DataTable dt = resultDS.Tables[0];
lstProducts.DataSource = dt;
lstProducts.DisplayMember = "Name";
lstProducts.ValueMember = "ProductID";
}
}
The first thing you will notice is that the Web method returns an object array. Because SQL Server can actually send back not only result sets, but also messages, errors, and other types (see Table 2 for more details), you should validate that you received what you expected. Listing 2 does a check by using the ToString method on the first element in the returned object array and comparing it to "System.Data.DataSet". If true, you know you can safely cast the object to a DataSet type and work with it from there.
Table 2: Object Return Types
Item from SQL Server
Corresponding .NET Object
Results of SELECT statement
System.Data.DataSet
Results of a SELECT statement with FOR XML
System.Xml.XmlElement
Raised error
SqlMessage (from WSDL)
Message
SqlMessage (from WSDL)
Output parameter
SqlParameter (from WSDL)
Rows affected
SqlRowCount (from WSDL)
RETURN statement value
System.Int32
Now double-click the ListBox control and add the following code:private void lstProducts_SelectedIndexChanged(object sender,
System.EventArgs e)
{
Int32 i = 0;
try
{i = System.Convert.ToInt32(lstProducts.SelectedValue.ToString());}
catch
{return;} //prevents issue when loading listbox
object e1;
win2k301_AWProducts.SqlMessage errorMessage;
System.Data.DataSet resultDS;
win2k301_AWProducts.SQLEP_AWProducts proxy =
new win2k301_AWProducts.SQLEP_AWProducts();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
object[] products = proxy.ProductStockInfo(i);
e1 = products[0];
switch (e1.ToString())
{
case "System.Data.DataSet":
resultDS = (System.Data.DataSet)products[0];
DataTable dt = resultDS.Tables[0];
dgvProduct.DataSource = dt;
dgvProduct.Refresh();
break;
case "Yukon_Http_v1.win2k301_AWProducts.SqlMessage":
errorMessage = (win2k301_AWProducts.SqlMessage)products[0];
MessageBox.Show("Error fetching product", "'"
+ errorMessage.Message + " occurred at '"
+ errorMessage.Source + "'");
return;
}
Byte[] photo = proxy.ProductPhoto(i);
picProduct.Image = new Bitmap(new MemoryStream(photo));
}
You should pay attention to a couple of details here. First, this code snippet checks for another type of returned object: SqlMessage. Notice the type name (from the ToString method) is Yukon_Http_v1.server_virtualDirectory.SqlMessage. This type is defined in the WSDL document and is unique to each Web service. The following listing shows a snippet from the WSDL of the SqlMessage complexType:

type="sqlmessage:nonNegativeInteger" />

The last tidbit for this discussion is the Byte array that is returned from the UDF. Because this UDF returned varbinary data, its XSD type is xsd:base64Binary. Thus, it is a Byte array in .NET. SQL Server's int data type is the XSD type xsd:int and Int32 in .NET. Books Online has more information about the mapping of types.
Easy Web Service Creation
HTTP Endpoints provide a way to create various interfaces into SQL Server, including features such as Service Broker, database mirroring, and of course, Web services. It makes the creation of a Web service (that exposes SQL Server data) a snap for experienced programmers and database administrators alike. Enjoy!
About the Author
Peter DeBetta is an independent software consultant who trains exclusively for Wintellect. Peter regularly publishes on the topic of SQL Server and recently wrote Introducing SQL Server 2005 for Developers for Microsoft Press. He also speaks at conferences such as VSLive!, WinSummit, WinDev, and Devscovery.