I am a beginner with .net. I faced issue with the following error
"The transaction operation cannot be performed because there are pending requests working on this transaction.".
i read somewhere in the blog .i appended my connection string with enlist=true and the issue was resolved.
Note: i am upgrading my DB from sql server 2005 to sql server 2008R2.
Please help to understand the importance of using enlist.
I have a reporting requirement from the following tables. I created a new database with these tables and imported data from the live database for reporting purpose.
The report parameter is a date range. I read the following and found that DATE_CORRELATION_OPTIMIZATION can be used to make the query work faster by utilizing seek instead of scan. I made the required settings – still the query is using same old plan and same execution time. What additional changes need to be made to make the query utilize the date correlation?
Note: I am using SQL Server 2005
REFERENCES
SQL
--Database change made for date correlation
ALTER DATABASE BISourcingTest
SET DATE_CORRELATION_OPTIMIZATION ON;
GO
--Settings made
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
--Test Setting
IF ( (sessionproperty('ANSI_NULLS') = 1) AND
(sessionproperty('ANSI_PADDING') = 1) AND
(sessionproperty('ANSI_WARNINGS') = 1) AND
(sessionproperty('ARITHABORT') = 1) AND
(sessionproperty('CONCAT_NULL_YIELDS_NULL') = 1) AND
(sessionproperty('QUOTED_IDENTIFIER') = 1) AND
(sessionproperty('NUMERIC_ROUNDABORT') = 0)
)
PRINT 'Everything is set'
ELSE
PRINT 'Different Setting'
--Query
SELECT C.ContainerID, C.CreatedOnDate,OLIC.OrderID
FROM ContainersTest C
INNER JOIN OrderLineItemContainers OLIC
ON OLIC.ContainerID = C.ContainerID
WHERE C.CreatedOnDate > '1/1/2015'
AND C.CreatedOnDate < '2/01/2015'
TABLES
CREATE TABLE [dbo].[ContainersTest](
[ContainerID] [varchar](20) NOT NULL,
[Weight] [decimal](9, 2) NOT NULL DEFAULT ((0)),
[CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [XPKContainersTest] PRIMARY KEY CLUSTERED
(
[CreatedOnDate] ASC,
[ContainerID] 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
CREATE TABLE [dbo].[OrderLineItemContainers](
[OrderID] [int] NOT NULL,
[LineItemID] [int] NOT NULL,
[ContainerID] [varchar](20) NOT NULL,
[CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_POLineItemContainers] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[LineItemID] ASC,
[ContainerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_OrderLineItemContainers] UNIQUE NONCLUSTERED
(
[ContainerID] 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].[OrderLineItemContainers] WITH CHECK ADD CONSTRAINT [FK_POLineItemContainers_Containers] FOREIGN KEY([ContainerID])
REFERENCES [dbo].[Containers] ([ContainerID])
GO
ALTER TABLE [dbo].[OrderLineItemContainers] CHECK CONSTRAINT [FK_POLineItemContainers_Containers]
Plan
--
Table : item
+---------+------------+
| item_id | item_sc |
+---------+------------+
| 63247 | 313150069 |
| 47558 | 2S27500EVW |
+---------+------------+
Table: incident
+-------------+-------------+---------+
| incident_id | date_logged | Item_id |
+-------------+-------------+---------+
| 10074329 | 1-Nov-2015 | 63247 |
| 10074869 | 31-Jan-2016 | 63247 |
| 10074399 | 20-Jan-2016 | 63247 |
| 10075274 | 7-Feb-2016 | 63247 |
| 10035727 | 2-Aug-2013 | 47558 |
| 10050239 | 8-Jul-2014 | 47558 |
| 10076340 | 25-Feb-2016 | 47558 |
| | | |
+-------------+-------------+---------+
Table: item_maint
+---------------+---------+---------------+-------------+-------------+
| item_maint_id | item_id | maint_crct_id | start_date | end_date |
+---------------+---------+---------------+-------------+-------------+
| 71748 | 63247 | 2132 | 11-Nov-2015 | 10-Nov-2016 |
| 62281 | 63247 | 1819 | 11-Nov-2014 | 10-Nov-2015 |
| 40786 | 47558 | 921 | 1-May-2013 | 8-Sep-2016 |
+---------------+---------+---------------+-------------+-------------+
Table: maint_crtc
+---------------+----------------+----------------+-------------+
| maint_crct_id | maint_crct_sc | effective_date | expiry_date |
+---------------+----------------+----------------+-------------+
| 1819 | ACSS-2015-0011 | 11-Nov-2014 | 10-Nov-2015 |
| 2132 | ACSS-2015-0091 | 11-Nov-2015 | 10-Nov-2016 |
| 921 | ACSS-2013-0066 | 1-May-2013 | 8-Sep-2016 |
+---------------+----------------+----------------+-------------+
Expected Result
+-------------+-------------+-----------+----------------+
| incident_id | date_logged | item_sc | maint_crct_sc |
+-------------+-------------+-----------+----------------+
| 10074869 | 31-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10074399 | 20-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10075274 | 7-Feb-2016 | 313150069 | ACSS-2015-0091 |
+-------------+-------------+-----------+----------------+
My Query
select DISTINCT incident.incident_id,incident.date_logged,ITEM.item_id, item.item_sc,maint_crct.maint_crct_id, maint_crct.maint_crct_sc
FROM incident INNER JOIN
dbo.item ON dbo.incident.item_id = dbo.item.item_id INNER JOIN
dbo.item_maint INNER JOIN dbo.maint_crct ON dbo.item_maint.maint_crct_id = dbo.maint_crct.maint_crct_id
ON dbo.incident.item_id = dbo.item_maint.item_id
WHERE maint_crct.maint_crct_n='ACSS-2015-0091'
As per my above query I am getting result like below,
+-------------+-------------+-----------+----------------+
| incident_id | date_logged | item_sc | maint_crct_sc |
+-------------+-------------+-----------+----------------+
| 10074869 | 31-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10074399 | 20-Jan-2016 | 313150069 | ACSS-2015-0091 |
| 10075274 | 7-Feb-2016 | 313150069 | ACSS-2015-0091 |
| 10074329 | 1-Nov-2015 | 313150069 | ACSS-2015-0091 |
+-------------+-------------+-----------+----------------+
How can I get the result without 10074329.
I'm using SQL SERVER 2005 with FULL recovery model. I accidentally executed the UPDATE statement without specify the condition using WHERE clause.
UPDATE TABLE SET Column1='All'
Now all Column1 data are set to 'All'. And I need to undo the column1 to have previous data.
Any idea? Thanks...
I have some SQL CLR and stored procedures which I created on a SQL Server 2005 database using Visual Studio 2012 but I need to have a SQL Server 2005 database associated rather than the default SQL Server 2012.
How can I create a 2005 database from VS 2012? I tried changing the target DB version to SQL Server 2005 - but it does not work.
I have a job that I want to run that passes a variable to an ssis package. The variable is a filename but the filename changes daily. I have an access front end that the user enters the filename into. The access program runs a stored procedure which writes the filename to a temp table and then runs the job. I would like the job to query that table for the filename and pass it along to my package variable.
I can get the job to work using a static filename. On the set values tab I used the property path \Package.Variables[User::FileName] and the value \myserver......\filename.txt. But I don't know how to replace that filename with the results of the query
Thanks in advance.
Scott
How to pivot tables with out aggregate functions and with boolean fields from three tables
CREATE TABLE [dbo].[TmpData](
[TmpDataID] [int] IDENTITY(1,1) NOT NULL,
[TmpDataName] [nvarchar](255) NOT NULL,
[StatusID] [int] NOT NULL,
[SignOffDate] [datetime] NULL,
[SignOffUserID] [int] NULL,
[SignOffComments] [nvarchar](max) NULL,
[IsClosed] [bit] NOT NULL,
[ClosedDate] [datetime] NULL,
CONSTRAINT [PK_dbo.TmpData] PRIMARY KEY CLUSTERED
(
[TmpDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
here is the sample data TmpDataID | TmpDataName |StatusID |SignOffDate |SignOffUserID |SignOffComments |IsClosed |ClosedDate 1 |TmpData |1 |00:00.0 |25 |xthda |1 |00:00.0
2 |TmpDtata2 |1 |00:00.0 |22 |eeeee |1 |00:00.0
CREATE TABLE [dbo].[TmpSectionData](
[TmpSectionDataID] [int] IDENTITY(1,1) NOT NULL,
[TmpDataID] [int] NOT NULL,--foreign key
[TmpSection] [nvarchar](255) NULL,
[TmpSectionOverall] [bit] NOT NULL,
[TmpSectionCusID] [int] NULL,
[TmpSectionOutcome] [bit] NULL,
[TmpSectionHeading] [bit] NOT NULL,
[TmpSectionActive] [bit] NOT NULL,
[TmpSectionHasOutcome] [bit] NOT NULL,
CONSTRAINT [PK_dbo.TmpSectionData] PRIMARY KEY CLUSTERED
(
[TmpSectionDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
here is the sample data
TmpSectionDataID |TmpDataID |TmpSection |TmpSectionOverall |TmpSectionCusID |TmpSectionOutcome |TmpSectionHeading |TmpSectionActive |TmpSectionHasOutcome| 1 |1 |TmpSection1 |1 |1 |1 |1 |0 |0 2 |1 |TmpSection2 |1 |5 |1 |0 |1 |1 3 |1 |TmpSection3 |0 |6 |0 |0 |1 |1 CREATE TABLE [dbo].[TmpSampleData]( [TmpSampleDataID] [int] IDENTITY(1,1) NOT NULL, [TmpSectionDataID] [int] NOT NULL, --foreighn key [TmpSampleText] nvarchar NULL, [TmpSampleValidation] [bit] NOT NULL, [TmpSampleCusConID] [int] NULL, [TmpSampleQPRID] [int] NOT NULL, [TmpSampleHasOutcome] [bit] NOT NULL, CONSTRAINT [PK_dbo.TmpSampleData] PRIMARY KEY CLUSTERED ( [TmpSampleDataID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And the data needed in the format:
TmpDataID TmpDataName StatusID SignOffDate SignOffUserID SignOffComments IsClosed ClosedDate TmpSection1+TmpSectionOverall TmpSection1+TmpSectionCusID TmpSection1+TmpSectionOutcome TmpSection1+TmpSectionHeading TmpSection1+TmpSectionActive TmpSection1+TmpSectionHasOutcome TmpSection2+TmpSectionOverall TmpSection2+TmpSectionCusID TmpSection2+TmpSectionOutcome TmpSection2+TmpSectionHeading TmpSection2+TmpSectionActive TmpSection2+TmpSectionHasOutcome TmpSection3+TmpSectionOverall TmpSection3+TmpSectionCusID TmpSection3+TmpSectionOutcome TmpSection3+TmpSectionHeading TmpSection3+TmpSectionActive TmpSection3+TmpSectionHasOutcome
1 TmpData 1 00:00.0 25 xthda 1 00:00.0 1 1 1 1 0 0 1 5 1 0 1 1 0 6 0 0 1 1
All the column data from the first table along with the data from second table TmpSectionData based on TmpSection which should come as header and the related data one after another in a single row. If i use joins i will get multiple columns and i have seen some of the question with PIVOT where they use aggregate function. here the columns i wanted to pivot is bool fields.
I have gone through some of the answers by few of stackoverflow collegues with pivot single column and there all use a amount field and made use of aggregate funstions.those didnt give me a clear idea on how i can work with more tables and more columns with one column as base for all the pivot. I tried to implement http://ift.tt/1QkZfsV . There also i am not getting the requeired format. Hope some one can help me with it.I have seen some of bluefeet answers and realized i may be able to achive the same with pivot in sql.I could manage to get one transpose one column