Friday 11 January 2013

Easy way to Connect to SSMS through Command prompt

DBAs and Developers are normally using below steps to connect to SQL Server Management Studio.

1.   Start         ---- All Programs ---- Microsoft SQL Server 2012 ---- SQL Server Management Studio.
2.   Then Choose Windows Authentication or SQL Server Authentication.
3.   Provide User Name and Password and Click Connect.

It can be optimized by using command line parameters.

Let’s type below command to understand the list of parameters available.

>> ssms /?





























Below command is used to connect to SQL Server Management Studio using Windows Authentication. –E parameter is used to connect to SQL Server through Windows Authentication.

>> ssms –E
                              
Below command is used to connect to SQL Server Management Studio using SQL Server Authentication. It is connecting to [BYGQVS1-TEST] SQL Server Instance.-U indicates Username. –P indicates Password.

>> ssms –S BYGQVS1-TEST –U sa –P welcome@123

Friday 4 January 2013

Rowset Function – OPENXML



OPENXML function is used to provide a relational rowset view of data for given XML document. XML document represents data in the form of Elements\Attributes. OPENXML function converts Elements\Attributes form data to relation table [Rows\Columns] format.

Syntax

          OPENXML (@idoc, <row pattern>, <flag>)
          WITH (<Schema Declaration | Table name>)

@idoc                    -- It is internal representation of XML document which can be created using          sp_XML_preparedocument stored procedure.
Row Pattern         -- It indicates XPath pattern which is used to identify the nodes to be processed as rows in the result set.
Flag             - It indicates the mapping that should be used between XML and relational rowset. And tells how the columns in the relational rowset should be filled.
1)   0                   - Attribute-centric mapping which is default.
2)   1                   - It uses the Attribute-Centric mapping and can be combined with XML_ELEMENTS
3)   2                   - It uses the Element-Centric mapping and can be combined with XML_ATTRIBUTES.
4)   8                   - It can be combined with XML-ELEMENTS OR XML-ATTRIBUTES (LOGICAL-OR Operation).

If you already have the defined Table Name with desired patterns, then you specify the Table name in the WITH Clause. Otherwise specify the Schema declaration in the form of [<Column Name> <Column Type> <Column Pattern>]. Column Pattern is an XPath pattern which defines how XML nodes should be mapped with the columns.

Pre-Configuration Setup

In order to use OPENXML Rowset function, the following pre-configuration has to be set.

1.   Make sure that Ad Hoc Distributed Queries advanced configuration option has been enabled on your SQL Server instance. If not yet enabled, then use the below script to enable it.

SP_CONFIGURE 'show advanced options', 1
RECONFIGURE;
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE;

Example

Below example provides relational rowset format of data from XML data format.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Student StudentID="101" StudentName="James">
   <Department DeptID="1" StudentID="101" JoiningDate="2001-09-05T00:00:00">
      <ScoreReport Subject="Physics" Mark="90"/>
      <ScoreReport Subject="Chemistry" Mark="85"/>
            <ScoreReport Subject="Biology" Mark="92"/>
            <ScoreReport Subject="Hindi" Mark="75"/>
            <ScoreReport Subject="English" Mark="89"/>
   </Department>
</Student>
</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT stmt using OPENXML rowset provider

SELECT *
FROM   OPENXML (@idoc, '/ROOT/Student/Department/ScoreReport',2)
         WITH (
             StudentID  INT                        '../@StudentID',
           DeptID        INT                        '../@DeptID',
          JoiningDate   DATETIME              '../@JoiningDate',
          Subject         VARCHAR(10)         '@Subject',
          Mark               INT                     '@Mark'
                                        )


Also refer the below snapshot for the results.



Wednesday 2 January 2013

Rowset Function – OPENROWSET



OPENROWSET is used to access data from OLEDB data sources. It references OLEDB data sources that are accessed infrequently. It is recommended to use Linked server for the frequent OLEDB data source access. It does not accept any variables for its arguments.

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

Syntax

          OPENROWSET (<Provider Name>, <provider String>, <Query>)

Provider Name       -- It indicates name of the OLEDB Provider or PROGID of OLEDB Provider.

1) SQLNCLI is SQL Server Native Client OLEDB Provider
2) Microsoft.Jet.OLEDB.4.0 is Microsoft OLEDB provider for JET
3)   Microsoft.ACE.OLEDB.12.0 is Microsoft OLEDB provider for ACE
4)   BULK ‘data file’ is the OPENROWSET built-in provider for BULK operations

Provider String    -- It varies based on the type of provider. 
 
It includes {Data source; user ID; Password} for SQLNCLI OLEDB Provider.
It includes {FORMATFILE = ‘Format file path’} for BULK Operations.
 
Also below mentioned arguments are used only in BULK Operations  

    [, CODEPAGE = {‘ACP’ | 'OEM' | 'RAW' | 'code_page’} ] 
         -      It specifies code page of the data in the data file.
         -      It is relevant, only if the data in the data file has CHAR, VARCHAR or TEXT column data in which character values greater than 127 or less than 32.
         -      ACP [It Converts columns of CHAR, VARCHAR, or TEXT data type from ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server Code Page].
         -      OEM [It converts columns of CHAR, VARCHAR, or TEXT data type from OEM code page to the SQL Server Code Page].
         -      RAW [No conversion occurs from one code page to another. This is the fastest option].
        -      Code_page [It indicates the source code page on which the character data in the data file is encoded]

   [, ERRORFILE = <Specifies the file used to collect formatting error rows during conversion> ]
   [, FIRSTROW = <Default is 1 which indicates first row of data file> ] 
   [, LASTROW = <Default is 0 which indicates last row of data file>] 
   [, MAXERRORS = <Default is 10 which specifies number of syntax error rows or non-confirming rows as defined in the format file> ] 
   [, ROWS_PER_BATCH = <Default is UNKNOWN which is total number of rows in the data file. By default all rows in the data file is considered as single Batch> ]  
 [, ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] 
-      It specifies in which order data should be sorted in the data file.

Query           --       It can be in the form of [Catalog]. [Schema]. [Object] or as SQL Query statement.

Pre-Configuration Setup

In order to use OPENROWSET Rowset function, the following pre-configurations have to be set properly.

1.   Make sure that specific OLEDB Driver has been installed on your system.

2.   Make sure that Ad Hoc Distributed Queries advanced configuration option has been enabled on your SQL Server instance. If not yet enabled, then use the below script to enable it.

SP_CONFIGURE 'show advanced options', 1
RECONFIGURE;
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE;

3.   Make sure that DisallowAdhocAccess registry option is explicitly set to 0 for the specified driver. When DisallowAdhocAccess is set to 1, SQL Server does not allow adhoc access through OPENROWSET function against the OLEDB provider. Use the below steps to configure this registry option.
To add the DisallowAdHocAccess value, follow these steps:

a)   Start Registry Editor.
b)   Locate, and then click the following key in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL

c)    On the Edit menu, click Add Value, and then add this registry value:

Value name: DisallowAdHocAccess
Data type:  REG_DWORD
Radix:      Hex
Value data: 0

d)   Quit Registry Editor.

To modify an existing DisallowAdHocAccess value, follow these steps:

a)   Start Registry Editor.
b)   Locate, and then click the DisallowAdHocAccess value under the following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB
On the Edit menu, click DWORD, type 0, and then click OK.
c)    Quit Registry Editor.

For a named instance, the registry key is different:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\MSDASQL


Examples

The following query uses Microsoft ACE OLEDB provider to fetch data from Employee_List spread sheet.

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES; Database=C:\Test\Data for Import\Employee_List.xlsx',
'SELECT * FROM [Status1$]');


The following query uses Microsoft SQL Server Native client to fetch data from SQL Server instance.

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=DBTechno\Instance-1;Trusted_Connection=yes;',
     'SELECT Name, salary, ReportsTo
      FROM EmployeeDB.dbo.Employee
      ORDER BY Name') AS a;


The following query uses OPENROWSET BULK provider to return bulk data from test.txt file.

SELECT a.* FROM OPENROWSET( BULK 'c:\Bulk_Test\test.txt',
   FORMATFILE = 'c:\Bulk_Test\format.fmt') AS a;


Contents of test.txt file

1                            Moses
2                            Suraj
3                            Yoseph
4                            Karthick

Contents of format.fmt file

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                          SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Employee_Name       SQL_Latin1_General_Cp437_BIN

Structure of format file is given below for reference.

[Version Number]
[Number of Columns in the data file]
[Field Order in Source File] [Source File Data Type] [Prefix Length] [Source File Column Length] [Destination Column Order] [Destination Column Name] [Destination Collation Name]

Rowset Function – OPENQUERY


OPENQUERY is used to execute the specified query with the help of mentioned Linked server. Linked Server can reference any OLEDB Data source. OPENQUERY is referenced in the FROM Clause as if it were a table name. INSERT DELETE and UPDATE operations can be executed through OPENQUERY function. Extended stored procedures cannot be executed through OPENQUERY function. But it can be directly executed through Linked Server. It returns only the first record even though query returns multiple result sets.

Syntax

          OPENQUERY (<Linked Server Name>, ‘Query’)

<Linked Server Name>  -- It indicates the name of the Linked server.
<Query>                     -- It specifies the query to be executed through Linked Server. Maximum length of query string is 8KB.

Pre-Configuration Setup

In order to use OPENQUERY Rowset function, the following pre-configurations have to be set.

1.  Make sure that specific Linked server has already been defined on your SQL Server. Linked server can reference any OLEDB Data source.

2. Make sure that Ad Hoc Distributed Queries advanced configuration option has been enabled on your SQL Server instance. If not yet enabled, then use the below script to enable it.

          SP_CONFIGURE 'show advanced options', 1
          RECONFIGURE;
          EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
          RECONFIGURE;

Examples

LServ_Orcl is the defined linked server name which refers OLEDB data source.

Below query, inserts a record into EMPDB.Designation_Master table.

INSERT OPENQUERY (LServ_Orcl, 'SELECT Designation FROM EMPDB.Designation_Master') VALUES ('ARCHITECT');

This query updates a record in EMPDB.Employee_List table for the Emp_ID 9.

UPDATE OPENQUERY (LServ_Orcl, 'SELECT Designation FROM EMPDB.Employee_List WHERE Emp_ID = 9') SET Designation = 'ARCHITECT';

This query deletes a record from EMPDB.Destination_Master table.

DELETE OPENQUERY (LServ_Orcl, 'SELECT Designation FROM EMPDB.Designation_Master WHERE Designation = ''ARCHITECT''');

Tuesday 1 January 2013

Rowset Function – OPENDATASOURCE



OPENDATASOURCE is used to access data from OLEDB data sources. It references OLEDB data sources that are accessed infrequently. It is recommended use Linked server for the frequent OLEDB data source access. It does not accept any variables for its arguments.

Syntax

             OPENDATASOURCE (<provider_name>, <init_string>)

Provider_Name       -- PRODID of the registered OLEDB Provider.
Init_String              -- Below list of Keywords can be defined and separated by semicolon here. [keyword1=’’; keyword2=’’)

1)   Data Source      -- Indicates the name of the data source to connect. For SQL Server native client OLEDB provider, it specifies name of the SQL Server instance. For the JET OLEDB provider, it specifies the name of .mdb or xlsx file.
2)   Location          -- It tells the location of the database to connect to.
3)   Extended Properties  -- It is provider specific connection string.
4)   Connect timeout        -- It specifies timeout values when connection try fails.
5)   User ID                     -- It specifies User Id for the connection.
6)   Password                  -- It specifies password for the connection.
7)   Catalog                   -- It indicates the name of the initial or default catalog when connecting to the data source.
8)   Integrated Security   -- SSPI, to specify Windows Authentication.


 Pre-Configuration Setup

In order to use OPENDATASOURCE Rowset function, the following pre-configurations have to be set properly.

1.   Make sure that specific OLEDB Drive has been installed on your system.

2.   Make sure that Ad Hoc Distributed Queries advanced configuration option has been enabled on your SQL Server instance. If not yet enabled, then use the below script to enable it.

                SP_CONFIGURE 'show advanced options', 1
                RECONFIGURE;
                EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
                RECONFIGURE;

3.   Make sure that DisallowAdhocAccess registry option is explicitly set to 0 for the specified driver. When DisallowAdhocAccess is set to 1, SQL Server does not allow adhoc access through OPENDATASOURCE function against the OLEDB provider. Use the below steps to configure this registry option.
To add the DisallowAdHocAccess value, follow these steps:

a)   Start Registry Editor.
b)   Locate, and then click the following key in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL

c)    On the Edit menu, click Add Value, and then add this registry value:

Value name: DisallowAdHocAccess
Data type:  REG_DWORD
Radix:      Hex
Value data: 0

d)   Quit Registry Editor.

To modify an existing DisallowAdHocAccess value, follow these steps:

a)   Start Registry Editor.
b)   Locate, and then click the DisallowAdHocAccess value under the following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB
On the Edit menu, click DWORD, type 0, and then click OK.
c)    Quit Registry Editor.

For a named instance, the registry key is different:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\MSDASQL


Examples

The following query is fetching data from Employee_Details.xlsx [Sheet Name: Status] spreadsheet using OPENDATASOURCE function. It is using Microsoft ACE OLEDB data provider.

SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Data for Import\Employee_Details.xlsx;Extended Properties=EXCEL 12.0 Xml')...[Status$] ;


The following query is fetching records from [DBTechno\Instance-1] SQL Server instance. Table Name is [Salary Master]. It is using SQL Native Client OLEDB provider.

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=DBTechno\Instance-1;Integrated Security=SSPI')
    .[EMPDB].[dbo].[Salary Master]