Advertisements

Archive

Archive for April, 2011

Select multiple number of OrderIDs and send the criteria to a Stored Procedure parameter – MSDN TSQL forum

April 21, 2011 Leave a comment

–> Question:

I’m trying to select any number of orders and call a stored procedure only one time. So my question is in order to perform a select statement in a stored procedure, is it possible to have n number of parameters, and if it is possible what is the best way to call a stored procedure with any number of orderIds? Or is it better to send in a nvarchar 30000 with xml and parse the xml for each order id and stick it into my stored procedure that way? I’m looking for a better way to perform this operation that is reliable.

example:

select * 
from tblOrders 
where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...

 

–> Answer:

Yes, not only XML, but there are various ways to pass multiple values to a Stored Proc via parameters, like:
1. Passing CSV string
2. Passing an XML or JSON string
3. Using temp-table
4. Using TVPs
… check here for all these options, link.
 

You can go with by creating a temp table:

CREATE TABLE #temptblOrders (OrderID int)

… store all OrderIDs into this.

Use this temp table inside your proc as:

SELECT * 
FROM tblOrders 
WHERE OrderId in (
	SELECT OrderID 
	FROM #temptblOrders
)

 

Ref link.


Advertisements

Error while Querying Excel file – The Microsoft Access database engine could not find the object ‘Sheet1$’ – MSDN TSQL Forum

April 14, 2011 Leave a comment

–> Question:

I have 2 servers, on server A the query:

SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=YES;Database=C:\Book1.xls',
    'SELECT * FROM [Sheet1$]');

Runs perfectly, but on server B I get following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly. If ‘Sheet1$’ is not a local object, check your network connection or contact the server administrator.”.

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

If I go to Proviver -> Microsoft.ACE.OLEDB.12.0 -> Properties:
On the server that works: on the white square bellow i got: Linked servers using this provider: XML
On the server with error i got nothing.
 

–> Answer:

If you are using same query/connection then make sure the excel files are on both the servers at C: drive.

Are both the servers same, i.e. 32 bit or 64? check: select @@version

And same for excel, it seems you have Excel 2007.

Check this blog post for more info.
 

Ref link.


SQL Basics – Working with NOT NULL, DEFAULT and CHECK Constraints (Domain Integrity)

April 11, 2011 Leave a comment

In my previous posts I discussed about [Entity Integrity] constraint which deals with Primary Keys & Unique Keys, and [Referential Integrity] constraint, which deals with Foreign Keys, please check the above links to know about them.
 

Here in this post I’ll discuss about Domain Integrity which validates the entries for a given column in a particular table. The Domain integrity can be enforced by:

1. Applying appropriate DATATYPES
 

2. NOT NULL constraint: enforces a column to NOT accept NULL values. Which means the columns must always contain a value and you cannot insert or update a row without specifying a value for that column.

For example: Employee name is mandatory while inserting an employee’s record, thus you can have [EmployeeName] column with NOT NULL constraint, like: [EmployeeName] nvarchar(100) NOT NULL
 

3. DEFAULT constraint: can be used to Insert a default value into a column, if no other value is provided while Inserting a row. This constraint works only with INSERT statement.

For example: To track the rows insert timestamp you can add this constraint to that column, like: [CreatedOn] datetime DEFAULT (getdate())
 

4. CHECK constraint: enforces a column to have only limited and pre-defined values. Means you cannot insert or update a row with values that are not defined with this constraint.

For example: An Employee gender could only be Male or Female, so you can have [Gender] column with CHECK constraint, like: [Gender] nchar(1) CHECK ([Gender] IN (‘M’, ‘F’))
 

–> Check the video on how to work with these three constraints (NOT NULL, CHECK and DEFAULT):

Constraints NOT NULL, DEFAULT, CHECK

–> SQL Code used in above video:


--// Domain Integrity Constraints:


-- 1. NOT NULL Constraint:

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values (NULL, NULL)

/*
Msg 515, Level 16, State 2, Line 13
Cannot insert the value NULL into column 'EmployeeName', table 'TestManDB.dbo.Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/

select * from [Employee]
GO



-- 2. DEFAULT Constraint:

DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)
	,[isActive]		bit				DEFAULT (1)
	,[CreatedOn]	datetime		DEFAULT (getdate())
	,[CreatedBy]	varchar(100)	DEFAULT (SYSTEM_USER)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'M')

insert into [Employee] ([EmployeeName], [Gender])
values ('Kanchan Pandey', 'F')

insert into [Employee] ([EmployeeName], [Gender], [isActive])
values ('Maria Y', 'F', 0)

insert into [Employee] ([EmployeeName], [Gender], [CreatedOn], [CreatedBy])
values ('Brock H', 'M', '2015-01-01 21:32:07.153', 'scott')

select * from [Employee]
GO



-- 3. CHECK Constraint:

DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)		CONSTRAINT ck_emp_gender CHECK ([Gender] IN ('M', 'F'))
	,[isActive]		bit				CONSTRAINT ck_emp_isActive DEFAULT (1)
	,[CreatedOn]	datetime		CONSTRAINT ck_emp_CreatedOn DEFAULT (getdate())
	,[CreatedBy]	varchar(100)	CONSTRAINT ck_emp_CreatedBy DEFAULT (SYSTEM_USER)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'X')

/*
Msg 547, Level 16, State 0, Line 67
The INSERT statement conflicted with the CHECK constraint "CK__Employee__Gender__4D5F7D71". The conflict occurred in database "TestManDB", table "dbo.Employee", column 'Gender'.
The statement has been terminated.
*/

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'M')

select * from [Employee]
GO




--// Table Generated from SSMS - Object Explorer

USE [TestManDB]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_gender]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_CreatedBy]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_CreatedOn]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_isActive]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 3/30/2016 9:45:17 PM ******/
DROP TABLE [dbo].[Employee]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 3/30/2016 9:45:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [int] IDENTITY(100,1) NOT NULL,
	[EmployeeName] [nvarchar](100) NOT NULL,
	[Gender] [nchar](1) NULL,
	[isActive] [bit] NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_isActive]  DEFAULT ((1)) FOR [isActive]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [ck_emp_gender] CHECK  (([Gender]='F' OR [Gender]='M'))
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [ck_emp_gender]
GO



-- Final Cleanup:

DROP TABLE [dbo].[Employee]
GO

To know about more on Constraints and their types check this blog post.
 


more on – ORDER BY with CASE

April 9, 2011 1 comment

One of my previous post mentions about using CASE construct with ORDER BY clause to tweak the ordering of the result set. That example was a simple one, this post provides more insight on this tip.

Most of us must have experienced this problem at least once when you want to order some of your records in a specific way. Or your boss or client asks this weird request to see some specific records on top. And you are puzzelled, how to do this.

The following example shows how we can get this:

USE [AdventureWorks]
GO

--Simple sort by ORDER BY
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY FirstName

-- ORDER BY with CASE
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY CASE LastName
			WHEN 'Wright'  THEN '0'
			WHEN 'Jenkins' THEN '1'
			WHEN 'Torres'  THEN '2'
			WHEN 'Sanchez' THEN '3'
			ELSE LastName
	END

-- Multiple column sort with ORDER BY with CASE
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY CASE LastName
			WHEN 'Wright'  THEN '0'
			WHEN 'Jenkins' THEN '1'
			WHEN 'Torres'  THEN '2'
			WHEN 'Sanchez' THEN '3'
			ELSE LastName
	END, FirstName
Categories: SQL Tips Tags: ,

Autocommit Transactions with SQL Server – MSDN TSQL forum

April 7, 2011 Leave a comment

–> Question:

I remember for the previous version, “autocommit” is defaulted to be on. How about SQL Server 2008 Management Studio? Where can we change its default?
 

–> My Answer:

Go to SSMS, Menu, Tools, Options, Query Execution, SQL Server, ANSI.

Here check the option on SET IMPLICIT_TRANSACTIONS
 

–> Answer by Tom Cooper:

Autocommit defaults to be on for SQL Server 2008. As per MSBoL it says:

“Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode.”

There is a user option named IMPLICIT_TRANSACTIONS which will set implicit transactions ON when the connection is made by a user that has that option. But it only works if you are using DBLIB which is an old deprecated connection method that has many restrictions and should only be used for legacy applications. That user option is ignored if you are using ODBC or OLEDB connections.
 

–> Answer by Steven Wang:

If you SET IMPLICIT_TRANSACTIONS on then you need manually to rollback and commit.

by default the IMPLICIT_TRANSACTIONS is set to off and commit is automatically done.
 

Ref link.