Posts

Showing posts from October, 2015

SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server

Image
Many time I heard about Powershell , but never go thorogh it. I tried it at basic level to connect to sql server from SQL Native Client. Coding looks similar to web developers  (like some properties as  'ExecuteScalar' ,'ExecuteNonQuery' ). Its very interesting which comes with SSMS (SQL Server Management Studio) . I already have sample database created on my local , which I would like to connect through powershell. /*Create table and keep some records for fetching from SQL Query*/ CREATE TABLE [dbo].[tbl_Employee](  [ID] [int] IDENTITY(1,1) NOT NULL,  [FirstName] [varchar](max) NULL,  [LastName] [varchar](max) NULL,  [Address] [varchar](max) NULL)   "1" indicates record inserted successfully !!    PowerShell very useful any kind of deployment on remote servers  .   Share your comments with addition information . Hope u will try it . You might get few error , but its interesting.

Ways to SELECT XML string using Nodes and OPENXML()

XML reading is most probably way of reading data from file. Only for knowledge we are checking performance by two ways OPENXML() and Nodes(). Check following the queries at your end as part of "Performace Tuning ". OpenXML() -                       OpenXML() is a rowset provider. OpenXML can be used in T-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear. Example : DECLARE @idoc int , @doc varchar ( 1000 ), @XMLString xml SET @doc = ' <ROOT> <Customer CustomerID="1" ContactName="Nihar"> <Order CustomerID="1" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail ProductID="11" Quantity="12"/> <OrderDetail ProductID="42" Quantity="10"/> </Order> </Customer> <Customer CustomerID="2" ContactName="HR"> <Order CustomerID=

Error Message Severity Levels in SQL Server

Sample Code: BEGIN TRY     SELECT 1/0; END TRY BEGIN CATCH     SELECT ERROR_SEVERITY() AS ErrorSeverity; END CATCH; GO Output : 16 ############################################################### Here we didnt understand what exactly severity occurs in SQL Expression Find descriptions here for each severity level. Severity Levels 0 through 19 : Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by the user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user's session is not interrupted. Severity Level 10: Status Information This is an informational message that indicates a problem caused by mistakes in the information the user has entered. Severity level 0 is not visible in SQL Server. Severity Levels 11 through 16 These messages indicate errors that can be corrected by the user. Severity Level 17: Insufficient Reso