web service registration on server

path for mor info http://www.windows-noob.com/forums/index.php?/topic/3249-how-can-i-install-a-web-service/

click on above link for asp.net web service register on server

sql JOB create and job Schedule

create sql server Job by programming

in this program we fetch data from excel file every 10 seconds in to sql server table

USE [msdb]

GO

/****** Object:  Job [indiandata]    Script Date: 11/30/2012 12:38:02 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/30/2012 12:38:02 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'indiandata',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'indian market data save in to a table product det from excel file',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'SOFTDREA-E08D10\Santrupt Kr. Mallick', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [select data]    Script Date: 11/30/2012 12:38:02 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'select data',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=0,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N' insert into product_det

  select * from OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',

   ''Data Source=C:\ODINNSEBSEMCXNCDEX\Client\Arbitrage.xls;Extended Properties=Excel 8.0'')

   ...Sheet1$ where Exchange is not null',

            @database_name=N'TMP',

            @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'datatime diffrence',

            @enabled=1,

            @freq_type=8,

            @freq_interval=63,

            @freq_subday_type=2,

            @freq_subday_interval=10,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20121130,

            @active_end_date=99991231,

            @active_start_time=100000,

            @active_end_time=235959,

            @schedule_uid=N'791648ae-fc29-4ac7-9ac8-0fab8622dbc8'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Excel data source on a linked server

fetch data from Excel to SQL server Table

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

   'Data Source=C:\ODINNSEBSEMCXNCDEX\Client\Arbitrage.xls;Extended Properties=Excel 8.0')

   ...Sheet1$ --where Exchange is not null

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',

   'Data Source=C:\ODINNSEBSEMCXNCDEX\Client\Arbitrage.xlsx;Extended Properties=Excel 8.0')

   ...Sheet1$ where Exchange is not null

-------------------------------------------------------------------------

Querying an Excel data source on a linked server

You can use SQL Server Management Studio or Enterprise Manager, a system stored procedure, SQL-DMO (Distributed Management Objects), or SMO (SQL Server Management Objects) to configure an Excel data source as a SQL Server linked server. (SMO are only available for Microsoft SQL Server 2005.) In all of these cases, you must always set the following four properties:

• The name that you want to use for the linked server.
• The OLE DB Provider that is to be used for the connection.
• The data source or complete path and file name for the Excel workbook.
• The provider string, which identifies the target as an Excel workbook. By default, the Jet Provider expects an Access database.

The system stored procedure sp_addlinkedserver also expects the @srvproduct property, which can be any string value. 

Note If you are using SQL Server 2005, you must specify a value that is not empty for the Product name property in SQL Server Management Studio or for the @srvproduct property in the stored procedure for an Excel data source.

Using SQL Server Management Studio or Enterprise Manager to configure an Excel data source as a linked server

SQL Server Management Studio (SQL Server 2005)

1. In SQL Server Management Studio, expand Server Objects in Object Explorer.
2. Right-click Linked Servers, and then click New linked server.
3. In the left pane, select the General page, and then follow these steps:
   1. In the first text box, type any name for the linked server.
   2. Select the Other data source option.
   3. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
   4. In the Product name box, type Excel for the name of the OLE DB data source.
   5. In the Data source box, type the full path and file name of the Excel file.
   6. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
   7. Click OK to create the new linked server.

Note In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains.

Enterprise Manager (SQL Server 2000)

1. In Enterprise Manager, click to expand the Security folder.
2. Right-click Linked Servers, and then click New linked server.
3. On the General tab, follow these steps:
   1. In the first text box, type any name for the linked server.
   2. In the Server type box, click Other data source.
   3. In the Provider name list, click Microsoft Jet 4.0 OLE DB Provider.
   4. In the Data source box, type the full path and file name of the Excel file.
   5. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
   6. Click OK to create the new linked server.
4. Click to expand the new linked server name to expand the list of objects that it contains.
5. Under the new linked server name, click Tables. Notice that your worksheets and named ranges appear in the right pane.

Using a stored procedure to configure an Excel data source as a linked server

You can also use the system stored procedure sp_addlinkedserver to configure an Excel data source as a linked server:

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, 
@datasrc, @location, @provstr, @catalog
    

As noted above, this stored procedure requires an additional, arbitrary string value for the @srvproduct argument, which appears as "Product name" in the Enterprise Manager and SQL Server Management Studio configuration. The @location and@catalog arguments are not used.

Using SQL-DMO to configure an Excel data source as a linked server

You can use SQL Distributed Management Objects to configure an Excel data source as a linked server programmatically from Microsoft Visual Basic or another programming language. You must supply the same four arguments that are required in the Enterprise Manager and SQL Server Management Studio configuration.

Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub
    

Using SMO to configure an Excel data source as a linked server

In SQL Server 2005, you can use SQL Server Management Objects (SMO) to configure an Excel data source as a linked server programmatically. To do this, you can use Microsoft Visual Basic .NET or another programming language. You must supply the arguments that are required in the SQL Server Management Studio configuration. The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, you can also use SMO for configuration of SQL Server 2000.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xls"
                .ProviderString = "Excel 8.0"
            End With
            ls.Create()
            MessageBox.Show("New linked Server has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

Querying an Excel data source on a linked server

After you configure an Excel data source as a linked server, you can easily query its data from Query Analyzer or another client application. For example, to retrieve the rows of data that are stored in Sheet1 of your Excel file, the following code uses the linked server that you configured by using SQL-DMO:

SELECT * FROM XLTEST_DMO...Sheet1$
    

You can also use OPENQUERY to query the Excel linked server in a "passthrough" manner, as follows:

SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
    

The first argument that OPENQUERY expects is the linked server name. Delimiters are required for worksheet names, as shown above.

You can also obtain a list of all the tables that are available on the Excel linked server by using the following query:

EXECUTE SP_TABLES_EX 'XLTEST_DMO'
    

Querying an Excel data source by using distributed queries

You can use SQL Server distributed queries and the OPENDATASOURCE or OPENROWSET function to query infrequently accessed Excel data sources on an ad hoc basis.

Note If you are using SQL Server 2005, make sure that you have enabled the Ad Hoc Distributed Queries option by using SQL Server Surface Area Configuration, as in the following example:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
    

Note that OPENROWSET uses an uncommon syntax for the second ("Provider String") argument:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
    

The syntax that an ActiveX Data Objects (ADO) developer may expect to use for the second ("Provider String") argument with OPENROWSET:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
    

This syntax raises the following error from the Jet Provider:

Could not find installable ISAM.

Note This error also occurs if you enter DataSource instead of Data Source. For example, the following argument is incorrect:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.

Pivot and Unpivot in sql

Pivot Data in Standard Query Language (SQL)

Pivoting data is one of the most common techniques in BI to present and manipulate data. While doing this using an ETL or BI tool is usually very simple, doing it in SQL is a more tedious task.

In this article we will see two different ways to pivot data with SQL. The first one is the classical way, while the second one uses the PIVOT clause available in Oracle 11g.

Scenario

The goal is to get the data in the following table as a data source…

Pivoting data is one of the most common techniques in BI used to present and manipulate data. Using an ETL or BI tool is usually a very simple solution; accomplishing it in SQL is more tedious.
In this article we will see two different ways to pivot data with SQL. The first one is the classical way, while the second one uses the PIVOT clause available in Oracle 11g.

Scenario
The goal is to get the data in the following table as a data source…

Figure 1: Source Data

… and pivot it to get the following results showing the amount by sales, persons and product:

Figure 2: Pivoted Data

Before starting, it is very important to be clear on the structure of a pivot table. The following image will help clarify this:

Figure 3: Pivot table structure showing the Rows, Columns and Measures areas

Pivot data in SQL (Classical Query)
The classical way of pivoting data consists of:

• Grouping the source data by the columns to be used, which is located in the rows area of the final pivot table
• Using CASE/DECODE to build the measure values in the measure area for each value in the columns area

So the SQL statement for the previous scenario is:

Pivot data in Oracle 11g SQL
If you are developing in the Oracle 11g Database, you could choose between the classical approach described above, using the new PIVOT clause available in Oracle 11g.
This SQL statement clause is embedded in the query between the FROM and WHERE clauses. Its syntax is the following:
PIVOT ( columns to be aggregated including the aggregation function
FOR columns to be pivoted
IN (values in the columns area and alias)
);
For each value in the IN clause, a new column will be created which contains the aggregated measure specified in the first part of the clause.
The query for the previous scenario would be the following:

Comparison Analysis
In both approaches, the developer needs to know the different values in advance. Any change in the available values may require a change in the SQL statement, if we want the new value to be shown in the columns area.
Using the PIVOT clause available in Oracle 11g, you will only need to add the value and the alias in the PIVOT clause and the alias in the SELECT clause, whereas in the classical approach you will need to rewrite the whole formula.
There is no “best” syntax to pivot data. Practice using both of these approaches and use the one you feel more comfortable with as long as your team is comfortable with that syntax. Take into account that some of your colleagues may be responsible for updating it in the future.

Asp.Net AjaxFileUpload Control With Drag Drop And Progress Bar

Posted by amiT jaiN

This Example explains how to use AjaxFileUpload Control With Drag Drop And Progress BarFunctionality In Asp.Net 2.0 3.5 4.0 C# And VB.NET.

May 2012 release of AjaxControlToolkit includes a new AjaxFileUpload Control which supportsMultiple File Upload, Progress Bar and Drag And Drop functionality.

These new features are supported by Google Chrome version 16+, Firefox 8+ , Safari 5+ and Internet explorer 10 + , IE9 or earlier does not support this feature.

To start with it, download and put latest AjaxControlToolkit.dll in Bin folder of application, PlaceToolkitScriptManager and AjaxFileUpload on the page.

HTML SOURCE

---

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"/>

<asp:AjaxFileUpload ID="AjaxFileUpload1" runat="server" 

                    OnUploadComplete="UploadComplete" 

                    ThrobberID="loader"/>

<asp:Image ID="loader" runat="server" 

           ImageUrl ="~/loading.gif" Style="display:None"/>

ThrobberID is used to display loading image instead of progress bar in unsupported browsers.

Type of files uploaded can be restricted by using AllowedFileTypes property with comma separated list such as "zip,doc,pdf".

Write following code in OnUploadComplete event to save the file.

C#

---

1protected void UploadComplete(object sender, AjaxControlToolkit.AjaxFileUploadEventArgs e)

2    {

3        string path = Server.MapPath("~/Uploads/") + e.FileName;

4        AjaxFileUpload1.SaveAs(path);

5    }

VB.NET

---

1protected void UploadComplete(object sender, AjaxControlToolkit.AjaxFileUploadEventArgs e)

2    {

3        string path = Server.MapPath("~/Uploads/") + e.FileName;

4        AjaxFileUpload1.SaveAs(path);

5    }

Build and run the code.