Schedule and Run a SSIS package

• SQL Server 2005 is bit different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, we need to follow some steps for creating job.
• The job creator account should have the roles of Sysadmin, SQLAgentuser, SQLAgentReader, SQLAgentOperator and job needs to be run under proxy account
• The steps are follows as below.
• Creating Account:
  SQL Server Management Studio- >Highlight Security->New Login->Give login user and password and database is your selected database
• Server roles: check sysadmin
  User mapping: your database
  Msdb database:Iinclude SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole -> click on OK.
• Creating Proxy Account:
• In Object Explorer, expand a server.
• Expand SQL Server Agent.
• Right-click Proxies and select New Proxy.
• On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before you create a proxy if one is not already available. For more information about creating a credentialCheck the appropriate subsystem for this proxy.
  On the Principals page, add or remove logins or roles to grant or remove access to the proxy account
• Creating Credentials:
• In Object Explorer, expand Security, right-click Credentials, and then click New Credential.
• In the New Credentials dialog box, in the Credential Name box, type a name for the credential.
• In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account. But the identity can be an account of another type.
• In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.
  Click OK.
• Job Creation,Scheduling,Running:
• In SQL Server Management Studio-> SQL Server Agent -> Start. Select job ->New Job>give name
• Then select steps tab-> New Step->give name
• Type: SQL Server Integration Service Package->Run asmyProxy->Package source: File System.
• Select your file through browse->YourPackage.dtsx->Say OK->then schedule and run it

Choosing a SQL Server Authentication Mode

• Microsoft SQL Server offers administrators two choices of performing user authentication: Windows authentication mode and mixed authentication mode. Making the proper choice affects both the security and maintenance of your organization’s databases
• SQL Server Authentication ModesSQL Server 2008 offers two authentication mode options:
• Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.
• Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server.
• Selecting an Authentication Mode Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight. For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! In conclusion, I suggest that you heed Microsoft’s advice and implement Windows authentication mode for your SQL Server databases whenever possible.

sp_MSforeachtable -Nice feature of sql server

• sp_MSforeachtable is a predefined stored procedure used to perform common action in all table of database at same time.
• For Example:Traditionally if you wanted to run a DBCC CHECKTABLE on every table in a database you'd have to write an elaborate cursor with couple of lines of sql script.
• Using the question mark as a place holder for all table names, the procedure will do the common task in a single line query which actually takes multiple lines in traditional way.
• Example Code : sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"
• You can provide up to 3 commands to the stored procedure using @command1 through @command3
• enjoy the fun programming...

WCF-Windows Communication Foundation

• What is WCF :
• Windows Communication Foundation, or just WCF, is a programming framework used to build applications that inter-communicate. WCF is the part of the .NET Framework dedicated to communications.
• The WCF unifies the various communications programming models supported in .NET 2.0, into a single model. Released in November 2005, .NET 2.0 provided separate APIs for SOAP-based communications for maximum interoperabilllity (Web Services), binary-optimized communications between applications running on Windows machines (.NET Remoting), transactional communications (Distributed Transactions), and asynchronous communications (Message Queues).
• WCF unifies the capabilities from these mechanisms into a single, common, general Service-oriented programming model for communications.
• WCF can use SOAP messages between two processes, thereby making WCF-based applications interoperable with any other process that communicates via SOAP messages. When a WCF process communicates with a non–WCF process, XML-based encoding is used for the SOAP messages but when it communicates with another WCF process, the SOAP messages can be encoded in an optimized binary format. Both encodings conform to the data structure of the SOAP format, called Infoset.
• WCF uses a pluggable encoding system, allowing developers to write their own encoders[1]. With the release of the .NET Framework 3.5 in November 2007, Microsoft released an encoder that added support for the JSON serialization format to WCF[2]. This allows WCF service endpoints to service requests from AJAX-powered web pages.
• WCF is designed in accordance with Service oriented architecture principles to support Distributed computing where services are consumed by consumers. Clients can consume multiple services and services can be consumed by multiple clients. Services typically have a WSDL interface which any WCF client can use to consume the service, irrespective of which platform the service is hosted on. WCF implements many advanced web services (WS) standards such as WS-Addressing, WS-ReliableMessaging and WS-Security. While Microsoft is a board member of WS-I it is not clear how many WS-I profiles they are committing to support fully.
• WCF Services:
• A WCF Service is composed of three parts — a Service class that implements the service to be provided, a host environment to host the service, and one or more endpoints to which clients will connect.
• All communications with the WCF service will happen via the endpoints. The endpoints specify a Contract that defines which methods of the Service class will be accessible via the endpoint; each endpoint may expose a different set of methods. The endpoints also define a binding that specifies how a client will communicate with the service and the address where the endpoint is hosted.
• WCF provides Windows Activation Services which can be used to host the WCF service. Otherwise the WCF service can also be hosted in IIS or in any process by using the ServiceHost class, which is provided by WCF. Services can also be self-hosted, in a console-based application or a Windows-forms application for example.
• Service Definition:
• In programming code, a WCF Service is implemented as a class. The class typically implements a Service Contract - a specially annotated interface whose methods stipulate the operations that the service performs. Typically, the methods on the interface accept inputs and output messages, which conform to Data Contracts, described with specially annotated classes. Think of these as Data Transfer Objects.
• The Data and Service Contracts are defined using annotations in programming code, known formally in .NET as Attributes. Any class that is to be exposed as a WCF service must be either marked with the ServiceContract attribute, or implement an interface marked with it. All methods in the class or interface that a client can invoke using SOAP messages must be marked with OperationContract attribute.
• All classes or structures that define the data to be passed into or out of the operations are marked with the DataContract attribute. The attributes support the automatic generation of WSDL descriptions for the exposed methods, which can then be accessed by clients, or advertised to them.
• A service can expose multiple Service Contracts. This can be done by defining multiple .NET interfaces, each marked as a Service Contract. The service class can then implement all the interfaces.
• The ServiceContract and OperationContract attributes also allow an interface to reference a previously existing contract, thus providing an option for supporting interface versioning.
  All Service Contracts have an associated implicit or explicit Data Contract which defines the data that the service works on.
• If the parameters accepted and returned by the service methods consist of primitive types (integer, double, boolean, etc), then the Data Contract is defined implicitly by WCF. If, on the other hand, the data is of a complex type like an object or a struct, then it must be defined explicitly by the programmer via attributes. Data contracts specify how the data is serialized and de-serialized, allowing for custom representation of objects passing in and out.
• End Points Definition:
• A WCF client connects to a WCF service via an endpoint.
  Each Service exposes its Contract via one or more endpoints. An endpoint has an address, which is a URL specifying where the endpoint can be accessed, and binding properties that specify how the data will be transferred.
• The mnemonic "ABC" can be used to remember Address / Binding / Contract. Binding specifies what communication protocols are used to access the service, whether security mechanisms are to be used, and the like. WCF includes predefined bindings for most common communication protocols such as SOAP over HTTP, SOAP over TCP, and SOAP over Message Queues etc.
• When a client wants to access the service via an endpoint, it not only needs to know the Contract, but it also has to adhere to the binding specified by the endpoint. Thus, both client and server must have compatible endpoints.
• Communication between Service and clients:
• A client can communicate with a WCF service using any of the RPC-based mechanisms in which the service can be invoked as a method call. Using synchronous communications, any call to the service will be blocking - that is, it will halt the execution of the client until the service processes the request.
• The client has to connect to a service using a proxy object, which is connected to the specified endpoint of the service and abstracts the service as an object. All method calls to the proxy object will be routed to the service and the proxy will return the results returned by the service to the caller.
• Tools shipped with the .NET Framework SDK can consume WSDL and create client-side proxy classes for use with WCF. Such classes handle the serialization and data transmission to and from the service, as well as faults and exceptions.
• WCF also supports non-blocking (asynchronous) calls between client and service, via several mechanisms. One option is to use Message Queues as the transport for the delivery and receipt of the messages.
• A second mechanism for supporting asynchronous communications is via multiple threads - there is a simple mechanism to do this in the generated client-side proxies for WCF
• WCF Case Studies : (Real World Examples):
• Below is the link provides good examples of where to use WCF and who has already implemented and getting benfis from the WCF...
• http://blogs.msdn.com/drnick/archive/2007/07/06/wcf-case-studies.aspx

.Net Configurations-Application level,Machine level,Security Level

Application Configuration File:This configuration file is used to set the settings for single application.It is application level configuration file.Any changes made to application configuration file will applicable to only single application.

Machine Configuration : This configuration file is used to set the settings for a resource that is used by multiple applications.The machine.config file is located in C:\\Microsoft.NET\Framework\\config\machine.config.This is a system level configuration file.Any changes made to this file will applicable for all applications that are located in that system
Security Configuration File:
Security configuration files contain information about the code group hierarchy and permission sets associated with a policy level. It is strongly recommended that you use the .NET Framework Configuration tool (Mscorcfg.msc) or Code Access Security Policy tool (Caspol.exe) to modify security policy to ensure that policy changes do not corrupt the security configuration files.

SQL Server Functions

Functions : Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.
Types Of Functions:

1. Row set functions :Can be used like table references in an SQL statement.
2. Aggregate functions :Operate on a collection of values but return a single, summarizing value.
3. Scalar functions :Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. This table categorizes the scalar functions.

SQL Server Function Categories:

1. Configuration Functions:Returns information about the current configuration.
2. Cursor Functions:Returns information about cursors.
3. DateandTimeFunctions:DATEADD ,DATEDIFF,DATENAME,DATEPART,DAY ,GETDATE ,GETUTCDATE ,MONTH,YEAR
   Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.
4. Mathematical Functions:performs a calculation based on input values provided as parameters to the function, and returns a numeric value.
5. Metadata Functions:Returns information about the database and database objects.
6. Security Functions:Returns information about users and roles.
7. String Functions:Performs an operation on a string (char or varchar) input value and returns a string or numeric value.
8. System Functions:Performs operations and returns information about values, objects, and settings in Microsoft SQL Server.
9. System Statistical Functions:Returns statistical information about the system.
10. Text and Image Functions: Performs an operation on a text or image input values or column, and returns information about the value.

Functions Behaviour:UDF's support a wide range of functionality and just because you can’t commit write changes to a database, it doesn't mean that you should turn a blind eye to them. Here's a list of the statements that you can include in the body of a UDF

• Flow control statements such as IF…ELSE, CASE, GOTO labels, etc.
• UPDATE, INSERT and DELETE commands to modify the contents of tables which are local to the UDF and have been created using SQL Server 200's news TABLE variable type.
• The EXECUTE command to execute stored procedures and extended stored procedures.
• The SET and SELECT statements to assign a value to a variable.
• SELECT statements to retrieve field values from tables into local variables.
• CURSOR, OPEN CURSOR, CLOSE CURSOR, DEALLOCATE CURSOR and FETCH NEXT FROM statements as long as the cursor is both created and disposed of within the scope of the UDF. Note that actual rows can't be returned, and they must be explicitly returned into type-matching variables using the FETCH NEXT INTO statement.
• DECLARE statements to create variables local (in terms of scope) to the UDF.
  Internally defined SQL variables which are prepended with "@@", such as @@ERROR and @@FETCH_STATUS.

Function and Stored Procedures Comparision:

• Scope of modification: UDF's aren't allowed to modify the physical state of a database using INSERT, UPDATE or DELETE statements. They can only work with local data.
• Output parameters: UDF's don't have the ability to return output parameters to the calling function. They do however let us return a single scalar value or a locally created table.
• Error handling: In UDF's, if an error occurs during a call to a stored procedure or a trigger, then that statement is terminated and flow control continue through the UDF.
• Calling conventions: One of the major differences between UDF's and stored procedures is that a UDF can be called through a SQL statement without using the EXECUTE statement

Creating Scalar Function:

CREATE FUNCTION dbo.mult(@num1 INT,@num2 INT)

RETURNS INT

AS

BEGIN

RETURN (@num1 * @num2)

END

Creating Table Valued Function :

CREATE FUNCTION getAuthorsByState(@state CHAR(2) )

RETURNS TABLE

AS

RETURN(SELECT au_fname + ' ' + au_lname AS NameFROM authorsWHERE state = @state)