Type of Transferring Data
Transferring data, whether into or out of a database, can be a complex process. SQL Server provides several tools that help you in a variety of situations, including the Bulk Copy Program, the Select statement and other tools.
In this section we will see the two methods of transferring data. 1) Bulk Copy Program(BCP) and 2) Using Store Procedures.
Bulk Copy Program (BCP)
BCP is a command-line utility that can read and write files in both ASCII format and in native format. (Native format uses variable-length records with field type and length descriptors embedded in them.) BCP using native format is the fastest way to transfer data between databases in SQL Server or Sybase SQL Server. If you use BCP with ASCII files, the ASCII files can be generated as fixed-length records or delimited files, and you can choose the delimiters. We can use BCP when we want to transfer large amount of data from or Into an external file.
BCP Parameters
The command line for BCP is complicated. Improper use of the options can cause BCP to fail and create significant headaches. BCP runs in an interactive command-line mode, which lets you specify the format of the table to dump if a format file is not provided for export, but other options default unless they are specified on the command line. Knowing when an option can be left out is as important as knowing what the defaults are for other values. We describe the command line in detail here to try to prevent your frustration when you first attempt to use it. Be aware that the options are case-sensitive. Note also that the login information comes at the end, whereas in most other utilities it is the first thing you supply.
bcp [[database_name.]owner.]table_name {in out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
[[database_name.]owner.]table_name — The table that data will be imported to or exported from. If the user’s ID does not have the desired database as its default, the database name must be specified. If the user ID supplied does not own the table, the owner must be specified and the user must have the appropriate permission. To import data into a table, the user must have Insert permission on the table. To export data from a table, the user must have Select permission on the table and on several of the system tables (sysobjects, sysindexes, and syscolumns). Usually the DBO or SA performs this task, in which case, permissions are not an issue.
{in out} — Specifies the direction of the data transfer, into the database table (in) or out of the database table (out).
datafile — The physical location of file to import or export to. This location must be in the path on the machine executing the BCP or you must specify the file with a complete path.
[/m maxerrors] — The number of errors per batch to ignore before stopping. The default value is 10. If you expect errors, you can raise the maximum number of errors to ignore to let the non-error data load completely.
[/f formatfile] — The physical location of a BCP format file to be used for export or import. You must use a format file if you are not using a native format or default character format (see the /n and /c parameters, below). This parameter must be supplied with a complete path. When you export data, the format file is used to create the required format. When you import a file, the format file specifies the fields in the data file and the columns that the data fields are to be imported into. If you do not specify the file when you’re exporting data, BCP will run interactively and create the format file you describe. If a format file is specified during import and the file does not exist, BCP will generate an error.
[/e errfile] — The physical location of a file to which BCP should write error messages and records with errors. You should specify the complete path. The default path is the directory from which the BCP executable is run.
[/F firstrow] — The row on which the import or export is to start. This parameter is particularly useful if you want to skip headers or break up large files into smaller batches.
[/L lastrow] — The row on which to stop the import or export. This parameter is useful for very large loads that need to be broken up.
[/b batchsize] — The number of records to load between committing transactions. The default value is all the records being inserted in one transaction. If this parameter is not set appropriately for the job, the load can cause transaction logs to become full (see the discussion about fast and slow BCP on page 266). It works with the number of errors to determine when it will abort.
[/n] [/c] — Native format or character format. These two options are mutually exclusive; you can specify a format with the /f parameter instead of using either. They are discussed in more detail on page 265.
[/E] — Turns off the identity insert, allowing files containing rows with identity column information to be inserted with their current identity value. Otherwise, the identity column is set according to the seed and increment defined at the time of creation. If you don’t want to preserve the identity column, it is best to use the format file to leave it out of the data export.
[/t field_term] — The field delimiter indicator; tab (\t) is the default. The most common delimiter is the double quote (“ ”) or the comma (,), but be aware that comma-delimited data will fail if the data records include commas. We recommend that you choose a character that you are sure is not in the data, such as the pipe () or tilde (~), if the source of the data supports it.
[/r row_term] — The row terminator, typically a carriage return/linefeed (\r \n).
[/i inputfile] — A file to replace standard input. You can use the file to provide the information BCP requests interactively.
[/o outputfile] — The physical path to the file to which standard output should be redirected. The file logs information such as the number of rows and other non-error output.
/U login_id — A valid login ID and user ID for the database(s) the tables are being imported to or exported from. The system administrator login and ID are used by default.
[/P password] — The correct password for the above user ID. The BCP utility will prompt for a password if none is provided.
[/S servername] — Server name to connect to. The default is the local server. BCP is faster if you use it locally instead of across the network.
[/v] — Gives the current version information for BCP. This information is important for the format files.
[/a packet_size] — Network packet size to be used in transferring data. Values can be between 512 and 36635. Larger packets can improve performance for larger BCP data transfers. Defaults for SQL on Windows NT are now 4096. This parameter can be set for SQL Server under the Configurations menu and overridden on a case-by-case basis through BCP. BCP reports information concerning the network packet size, number of rows processed, and the rate of processing. Microsoft recommends using a value of between 4096 and 8192 for SQL Server running on Windows NT.
Tuesday, August 19, 2008
ASP.NET XML Web Service Basics
ASP.NET XML Web Service Basics
Since ASP.NET provides the infrastructure for the inner workings of a Web service, developers can focus on implementing the functionality of their specific Web service. Enabling a Web service using ASP.NET entails creating a file with an .asmx file name extension, declaring a Web service in that file and possibly another file, and defining Web service methods. The procedures are listed in Walkthrough: Building a Basic XML Web Service Using ASP.NET and are elaborated upon here.
Declaration of Web Services
When you create a Web service in ASP.NET, you place the required @ WebService directive at the top of a text file with an .asmx file name extension. The presence of the .asmx file and the @ WebService directive correlate the URL address of the Web service with its implementation. You also implement the Web service class that defines the methods and data types visible by Web service clients.
The Web service class you define can be included directly in the .asmx file, or in a separate file. If you use a separate file, it must be compiled into an assembly. Optionally, you can apply a WebService attribute to the class that implements the Web service. The class that implements the Web service can derive from the WebService class.
By applying the optional WebService attribute to a class that implements a Web service, you can set the default XML namespace for the Web service along with a string to describe the Web service. It is highly recommended that this default namespace, which originally is http://tempuri.org, be changed before the Web service is made publicly consumable. This is important because the Web service must be distinguished from other Web services that might inadvertently use the namespace as the default ().
Classes that implement a Web service created using ASP.NET can optionally derive from the WebService class to gain access to the common ASP.NET objects, such as Application, Session, User, and Context. The Application and Session properties provide access to storing and receiving state across the lifetime of the Web application or a particular session. For more information on state management, see How to: Manage State in Web Services Created Using ASP.NET. The User property contains the identity of the caller, if authentication is enabled, for the Web service. With the identity, a Web service can determine whether the request is authorized. For more information on authentication, see Securing XML Web Services. The Context property provides access to all HTTP-specific information about the Web service client's request. For more information on the Context property, see WebService.Context Property.
Definition of Web Service Methods
Methods of a class that implement a Web service do not automatically have the ability to receive Web service requests and send back responses, but with Web services created using ASP.NET, it is very simple to add that capability. Apply a WebMethod attribute to public methods. Methods of a Web service class that can be communicated with over the Web are called Web service methods.
Web service methods are a key part of the messaging infrastructure employed by Web services. That is, a client and a Web service communicate using messages, specifically SOAP messages, by default. Clients send a SOAP request to a Web service and a Web service method typically returns a SOAP response. Web services define the type of messages they accept using operations, as defined by Web Services Description Language (WSDL). These operations correlate to each of the Web service methods within a Web service. Even though each of these Web service methods are defined in ASP.NET using a method of a class, it is important to realize that the data that is eventually communicated over the network must be serialized into XML. As such, it is important to remember that Web services are not a replacement for DCOM, but rather a messaging infrastructure for communicating across platforms using industry standards.
Since ASP.NET provides the infrastructure for the inner workings of a Web service, developers can focus on implementing the functionality of their specific Web service. Enabling a Web service using ASP.NET entails creating a file with an .asmx file name extension, declaring a Web service in that file and possibly another file, and defining Web service methods. The procedures are listed in Walkthrough: Building a Basic XML Web Service Using ASP.NET and are elaborated upon here.
Declaration of Web Services
When you create a Web service in ASP.NET, you place the required @ WebService directive at the top of a text file with an .asmx file name extension. The presence of the .asmx file and the @ WebService directive correlate the URL address of the Web service with its implementation. You also implement the Web service class that defines the methods and data types visible by Web service clients.
The Web service class you define can be included directly in the .asmx file, or in a separate file. If you use a separate file, it must be compiled into an assembly. Optionally, you can apply a WebService attribute to the class that implements the Web service. The class that implements the Web service can derive from the WebService class.
By applying the optional WebService attribute to a class that implements a Web service, you can set the default XML namespace for the Web service along with a string to describe the Web service. It is highly recommended that this default namespace, which originally is http://tempuri.org, be changed before the Web service is made publicly consumable. This is important because the Web service must be distinguished from other Web services that might inadvertently use the namespace as the default (
Classes that implement a Web service created using ASP.NET can optionally derive from the WebService class to gain access to the common ASP.NET objects, such as Application, Session, User, and Context. The Application and Session properties provide access to storing and receiving state across the lifetime of the Web application or a particular session. For more information on state management, see How to: Manage State in Web Services Created Using ASP.NET. The User property contains the identity of the caller, if authentication is enabled, for the Web service. With the identity, a Web service can determine whether the request is authorized. For more information on authentication, see Securing XML Web Services. The Context property provides access to all HTTP-specific information about the Web service client's request. For more information on the Context property, see WebService.Context Property.
Definition of Web Service Methods
Methods of a class that implement a Web service do not automatically have the ability to receive Web service requests and send back responses, but with Web services created using ASP.NET, it is very simple to add that capability. Apply a WebMethod attribute to public methods. Methods of a Web service class that can be communicated with over the Web are called Web service methods.
Web service methods are a key part of the messaging infrastructure employed by Web services. That is, a client and a Web service communicate using messages, specifically SOAP messages, by default. Clients send a SOAP request to a Web service and a Web service method typically returns a SOAP response. Web services define the type of messages they accept using operations, as defined by Web Services Description Language (WSDL). These operations correlate to each of the Web service methods within a Web service. Even though each of these Web service methods are defined in ASP.NET using a method of a class, it is important to realize that the data that is eventually communicated over the network must be serialized into XML. As such, it is important to remember that Web services are not a replacement for DCOM, but rather a messaging infrastructure for communicating across platforms using industry standards.
Source: MSDN Library
Subscribe to:
Comments (Atom)