SQL Server Mentalist

Is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.

Is the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, such as: ‘keyword1=value; keyword2=value‘.

For specific keyword-value pairs supported on the provider, see the Microsoft Data Access SDK. This documentation defines the basic syntax. The following table lists the most frequently used keywords in the init_string argument.

4.OPENQUERY

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax

OPENQUERY ( linked_server ,’query’ )

Where above keywords stands for

Is an identifier representing the name of the linked server.

Is the query string executed in the linked server. The maximum length of the string is 8 KB.

5.OPENROWSET

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead. For more information, see Linking Servers. The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

Syntax

OPENROWSET ( { ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’ | ‘provider_string’ } , { [ catalog. ] [ schema. ] object | ‘query’ } | BULK ‘data_file’ , { FORMATFILE = ‘format_file_path’ [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) <bulk_options> ::= [ , CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ] [ , ERRORFILE = ‘file_name’ ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , MAXERRORS = maximum_errors ] [ , ROWS_PER_BATCH = rows_per_batch ] [ , ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]

Where above keywords stands for

Is a character string that represents the friendly name (or PROGID) of the OLE DB provider as specified in the registry. provider_name has no default value.

Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database or databases.

Is a string constant that is the user name passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider. user_id cannot be a Microsoft Windows login name.

Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password cannot be a Microsoft Windows password.

Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information required to initialize the provider. For a list of keywords that are recognized by the SQL Server Native Client OLE DB provider, see Initialization and Authorization Properties.

Is the name of the catalog or database in which the specified object resides.

Is the name of the schema or object owner for the specified object.

Is the object name that uniquely identifies the object to work with.

Is a string constant sent to and executed by the provider. The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. Pass-through queries are useful when used on providers that do not make available their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces. For more information, see SQL Server Native Client (OLE DB) Reference.

Uses the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

The arguments of the BULK option allow for significant control over where to start and end reading data, how to deal with errors, and how data is interpreted. For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. The default behavior is described in the argument descriptions that follow.

For information about how to use the BULK option, see "Remarks," later in this topic. For information about the permissions that are required by the BULK option, see "Permissions," later in this topic.

6.OPENXML

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

Syntax

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ]

Where above keywords stands for

Is the document handle of the internal representation of an XML document. The internal representation of an XML document is created by calling sp_xml_preparedocument.

Is the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.

Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter, and can be one of the following values.

Todays link are follows:

Link Resource # 14 : August 8 – August 10 « Dactylonomy of Web Resource

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 40 : SQL Server Code Name “Denali” CTP3 Product Guide

· BISQL # 41 : Difference Between Char And Varchar

· BISQL # 42 : How to find count of all the records in all the table

· BISQL # 43 : Introduction to DAC – Data-Tier Application {DAC Part – I}

· BISQL # 44 : DAC-Database Project Overview {DAC Part – II}

Hope you will like this post on SQL Server Function Part II.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

               For More information related to BI World visit my Mentalist Blog

                               Link Resource Blog >> Daily Interesting links

                              SQL Server Mentalist >> SQL Learning Blog

                              Business Intelligence Mentalist >> BI World

                                        Connect With me on

           | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                    Copyright © 2011 – 2012 Vishal Pawar