[SQL 2012] "cpu drift" possibly causing 15 second IO messages in log?

---

• "cpu drift" possibly causing 15 second IO messages in log?
• Left join with duplicate records
• Filetable on separate device
• DQS NULL REFERENCE ERROR
• SSMS connection to SSIS with packages on remote MSDB server - is that even remotely possible?
• Why queries are using certain indexes
• Help with IIS and configuring the connection string for a .net app

"cpu drift" possibly causing 15 second IO messages in log? Posted: 22 Aug 2013 02:35 AM PDT We've seen messages like the one below for years, on and off. Usually during index maintenance. Looking for feedback from others who may have found a solution.[b]SQL Server has encountered 878 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [K:\tempdb_data3\tempdev3.mdf[/b]We know our average read/write wait times are generally below 50 milliseconds (tempdb does show 1.2 seconds average write wait, no doubt skewed by updating stats on large tables with 80% sample rate ).Our production sql cluster is currently running on 40 out of 64 cores until we upgrade the licensing model, but we've seen these messages in the log long before we upgraded to sql 2012 from 2005.We also just switched the servers power config to run in performance mode, not energy savings ( no sql restart or reboot ). The 15-second messages still occur once in a while.http://blogs.msdn.com/b/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx

Left join with duplicate records Posted: 22 Aug 2013 01:55 AM PDT Hi all,I am doing a left join betwen two tables, the issue is that the result of this join is bringing more rows that he left table has, from what i saw in the internet the issue resides on the right table which is making the left table adding some duplicates.Now how can i solve this??? :-)Thanks,Daniel

Filetable on separate device Posted: 21 Aug 2013 06:22 AM PDT Hello,I've created a VM (using Windows Virtual PC) running Windows 7, 32-bit, and allocated a virtual hard drive of 126 MB.The virtual hard drive is quite limited, and that's the way I want it.On the VM, I have setup SQL Server 2012, 32-bit.I am about to conduct an experiment using SQL Server 2012 Filetables.Is it possible to setup a Filetable where the data for it is on a separate device (NAS) with a separate IP address?The VM can connect to the NAS through it's fixed IP address. I can not only ping it from the VM, but I can also map a network drive to it.However, I cannot create the Filestream device for the database on it.This works:Create Database RRTestOn Primary ( Name = RRTestData , Filename = 'C:\Data\MSSQL\RRTest.mdf' , Size = 10MB , FileGrowth = 1MB ), FileGroup FSPics Contains FileStream Default ( Name = FSPics , Filename = 'C:\Data\MSSQL\FSPics\' )Log On ( Name = RRTestLog , Filename = 'C:\Data\MSSQL\RRTest.ldf' , Size = 1MB , FileGrowth = 1MB )But this doesn't:Create Database RRTestOn Primary ( Name = RRTestData , Filename = 'C:\Data\MSSQL\RRTest.mdf' , Size = 10MB , FileGrowth = 1MB ), FileGroup FSPics Contains FileStream Default ( Name = FSPics , Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\FSPics\' )Log On ( Name = RRTestLog , Filename = 'C:\Data\MSSQL\RRTest.ldf' , Size = 1MB , FileGrowth = 1MB )I have followed these instructions written in BOL:' filestream_path 'For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. The path up to the last folder must exist, and the last folder must not exist. For example, if you specify the path C:\MyFiles\MyFilestreamData, C:\MyFiles must exist before you run ALTER DATABASE, but the MyFilestreamData folder must not exist.The filegroup and file (<filespec>) must be created in the same statement.The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.I have already created a folder on the NAS, "Data\MSSQL\", but not "Data\MSSQL\FSPics\Can this be done at all?Thanks for any suggestions and pointers.Richard

DQS NULL REFERENCE ERROR Posted: 17 Jul 2013 03:06 AM PDT Experiencing a DQS error whilst running an SSIS package with a DQS component. I have created a DQS knowledge which is accessed by the DQS component. When the package is run from SQL Server agent we get the following error:[size="1"]DFT_ProcessWorkData:Error: Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: The Knowledge Base does not exist [Id : 1030017]. at Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec() at Microsoft.Ssdqs.Proxy.EntryPoint.KnowledgebaseManagementEntryPointClient.DQProjectGetById(Int64 id) at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.PostExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)DFT_ProcessWorkData:Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessChunk(ReadOnlyCollection`1 fieldMappings, ReadOnlyCollection`1 records, CorrectedRecordsStatusStatistics& correctedRecordsTotalStatusStatistics) at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)DFT_ProcessWorkData:Error: An unexpected error occurred in the DQS infrastructure.System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessChunk(ReadOnlyCollection`1 fieldMappings, ReadOnlyCollection`1 records, CorrectedRecordsStatusStatistics& correctedRecordsTotalStatusStatistics)[/size]When the packages were run on our development server they ran fine. The same packages were then deployed to our RTE environment. DQS was also installed on our RTE environment. SQL Jobs were then scheduled to run the packages. The jobs failed giving the above error. I ran the package from visual studio and the package ran successfully. This led me to believe it was a rights issue. I therefore changed the credential and proxy account which ran the sql jobs to ensure that they had the necessary rights. I tried executing the package again from Sql server agent but still got the same error.We uninstalled DQS on our RTE environment and reinstalled it again but the problem still remains. Anybody else experienced this issue or have any idea what may be causing the error.

SSMS connection to SSIS with packages on remote MSDB server - is that even remotely possible? Posted: 01 Mar 2013 09:03 AM PST I've been working in a lab to see what is possible as far as separation of services in SQL Server 2012.I have this setup:Server 1: DB EngineServer 2: SSIS & SSASMy goal is to have the SSIS packages in a database on the main DB server (Server 1) and have the users access Server 2's SSIS via SSMS from their workstation. I'm finding that this is perhaps not possible. [url=http://www.sqlservercentral.com/Forums/Topic1373364-1550-1.aspx#bm1373371]As this forum post states:[/url]Which quotes [url=http://msdn.microsoft.com/en-us/library/aa337083(v=sql.110).aspx]this article from MS[/url]:[quote]Delegation Is Not SupportedSQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.[/quote]So it looks like i finally hit a brick wall. I'm wondering if there is any way to configure such a scenario (mostly for knowledge) or if that is even practical. I'd be curious to hear from experienced DBA's who can perhaps point me in the right direction. Thank you in advance.

Why queries are using certain indexes Posted: 21 Aug 2013 05:05 AM PDT I have the following query written two ways. The first query uses different indexes than the second and I am trying to understand why. Why wouldn't the optimizer figure out to use the same indexes for an or clause versus a union all. [code="sql"][b]QUERY #1[/b]SELECT TOP 400 *FROM AuditLog WITH ( NOLOCK )WHERE CustomerID = 2379 AND PracticeID = 55 AND ( ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) OR ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) )ORDER BY CreatedDate DESC [b]QUERY #2[/b]SELECT TOP 400 *FROM (SELECT TOP 400 *FROM AuditLog WITH ( NOLOCK )WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 4 AND EntityTypeID = 1022638 )ORDER BY CreatedDateUNION ALLSELECT TOP 400 *FROM AuditLog WITH ( NOLOCK )WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 )ORDER BY CreatedDate)subORDER BY sub.CreatedDate[/code][code="xml"]<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3373.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00657216" StatementText="SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) OR ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) ) ORDER BY CreatedDate DESC " StatementType="SELECT" QueryHash="0x9F3AF4664382C044" QueryPlanHash="0x2CA78D552FCE421D" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="368"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="341333" EstimatedPagesCached="512000" EstimatedAvailableDegreeOfParallelism="6" /> <RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657216"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(400)"> <Const ConstValue="(400)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </OuterReferences> <RelOp AvgRowSize="31" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="62088" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_CustomerID_PracticeID_CreatedDate]" IndexKind="NonClustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(2379)"> <Const ConstValue="(2379)" /> </ScalarOperator> <ScalarOperator ScalarString="(55)"> <Const ConstValue="(55)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="4214" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="62082" ActualExecutions="62088" /> </RunTimeInformation> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]"> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditTypeID]=(4) AND [KareoAudit].[dbo].[AuditLog].[EntityTypeId]=(1022638) OR [KareoAudit].[dbo].[AuditLog].[AuditTypeID]=(6) AND [KareoAudit].[dbo].[AuditLog].[SubEntityTypeId]=(1022638)"> <Logical Operation="OR"> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(4)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1022638)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(6)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1022638)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="3" StatementEstRows="2" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0245098" StatementText="SELECT TOP 400 * FROM ( SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 4 AND EntityTypeID = 1022638 ) ORDER BY CreatedDate UNION ALL SELECT TOP 400 * FROM AuditLog WITH ( NOLOCK ) WHERE CustomerID = 2379 AND PracticeID = 55 AND ( AuditTypeID = 6 AND SubEntityTypeID = 1022638 ) ORDER BY CreatedDate)sub ORDER BY sub.CreatedDate " StatementType="SELECT" QueryHash="0x85D7E4BBA213B22A" QueryPlanHash="0x9FBEFBBE742E66A3" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="64" CompileTime="7" CompileCPU="7" CompileMemory="544"> <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="560" RequiredMemory="512" DesiredMemory="560" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="341333" EstimatedPagesCached="512000" EstimatedAvailableDegreeOfParallelism="6" /> <RelOp AvgRowSize="4238" EstimateCPU="0.000107358" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0245098"> <OutputList> <ColumnReference Column="Union1006" /> <ColumnReference Column="Union1007" /> <ColumnReference Column="Union1008" /> <ColumnReference Column="Union1009" /> <ColumnReference Column="Union1010" /> <ColumnReference Column="Union1011" /> <ColumnReference Column="Union1012" /> <ColumnReference Column="Union1013" /> <ColumnReference Column="Union1014" /> <ColumnReference Column="Union1015" /> <ColumnReference Column="Union1016" /> <ColumnReference Column="Union1017" /> <ColumnReference Column="Union1018" /> <ColumnReference Column="Union1019" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <TopSort Distinct="false" Rows="400"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Column="Union1019" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="4238" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.0131412"> <OutputList> <ColumnReference Column="Union1006" /> <ColumnReference Column="Union1007" /> <ColumnReference Column="Union1008" /> <ColumnReference Column="Union1009" /> <ColumnReference Column="Union1010" /> <ColumnReference Column="Union1011" /> <ColumnReference Column="Union1012" /> <ColumnReference Column="Union1013" /> <ColumnReference Column="Union1014" /> <ColumnReference Column="Union1015" /> <ColumnReference Column="Union1016" /> <ColumnReference Column="Union1017" /> <ColumnReference Column="Union1018" /> <ColumnReference Column="Union1019" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Concat> <DefinedValues> <DefinedValue> <ColumnReference Column="Union1006" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1007" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1008" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1009" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1010" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1011" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1012" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1013" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1014" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1015" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1016" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1017" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1018" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1019" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657048"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(400)"> <Const ConstValue="(400)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </OuterReferences> <RelOp AvgRowSize="39" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_EntityTypeID_CustomerID_PracticeID_AuditTypeID]" TableReferenceId="1" IndexKind="NonClustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(1022638)"> <Const ConstValue="(1022638)" /> </ScalarOperator> <ScalarOperator ScalarString="(2379)"> <Const ConstValue="(2379)" /> </ScalarOperator> <ScalarOperator ScalarString="(55)"> <Const ConstValue="(55)" /> </ScalarOperator> <ScalarOperator ScalarString="(4)"> <Const ConstValue="(4)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="4205" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="0" ActualExecutions="5" /> </RunTimeInformation> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]"> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> <RelOp AvgRowSize="4238" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="25" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657048"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(400)"> <Const ConstValue="(400)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="4238" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="26" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </OuterReferences> <RelOp AvgRowSize="39" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="27" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CreatedDate" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[IX_AuditLog_SubEntityTypeID_CustomerID_PracticeID_AuditTypeID]" TableReferenceId="2" IndexKind="NonClustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="CustomerId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="PracticeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditTypeID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(1022638)"> <Const ConstValue="(1022638)" /> </ScalarOperator> <ScalarOperator ScalarString="(2379)"> <Const ConstValue="(2379)" /> </ScalarOperator> <ScalarOperator ScalarString="(55)"> <Const ConstValue="(55)" /> </ScalarOperator> <ScalarOperator ScalarString="(6)"> <Const ConstValue="(6)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="4205" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="29" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="175160000"> <OutputList> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="Application" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditActionTypeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="ChangeSet" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="EntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="SubEntityTypeText" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserId" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="UserName" /> </DefinedValue> </DefinedValues> <Object Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Index="[PK_AuditLog]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[KareoAudit].[dbo].[AuditLog].[AuditLogID]"> <Identifier> <ColumnReference Database="[KareoAudit]" Schema="[dbo]" Table="[AuditLog]" Column="AuditLogID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </Concat> </RelOp> </TopSort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence></ShowPlanXML>[/code][code="xml"][/code]

Help with IIS and configuring the connection string for a .net app Posted: 21 Aug 2013 05:11 AM PDT I am a sql server DBA. I am not completely aware of how IIS recognizes a sql server windows login credentials.This is for our testing only and doesnt reflect any prod or development env's. Right now we have created a new domain TESTApp.xyz.local and added an Active Passive windows and SQL Server Cluster set up to this domain. So my test database server is testdb\apptest. Our test app server is sitting on a different domain which is abc.local. The app server name is appserver1. Now I have restored the databases in testdb\apptest instance for the apps being hosted on appserver1. How do I ensure that the user will be able to login to application?Apps are using .net framework 4 and using IIS. If i create a domain account say appuser.xyz.local and create a login for this in testdb\apptest and give appropriate permissions on the database then how will the app be able to use this user to login to the app? what changes are required here at IIS as well as config file level. Currently the config files are using integrated security.Need your help experts

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Search Value

---

• Search Value
• Using SQL Last in an Aggregate Query
• Argument data type datetime is invalid for argument 1 of substring function
• Fetch numeric values from string using SSIS
• Result that matches all the values from a list
• BCP Help
• Omit Data Length for Numeric Values
• Had to change query to use date field
• Query to get immediate next date row into end_ts column in Tsql/db2 sql
• Executing a stored proc on a linked server
• Grouping data...

Search Value Posted: 21 Aug 2013 08:24 PM PDT Hi,I want to search a word in all stored procedure and create the list of stored procedures where that word exists. I am done with that but one more scenario added to that is that only search those stored procedure where that word is present in an uncommented lines only (exclude those stored procedures where that word present in commented lines).Is this possible?

Using SQL Last in an Aggregate Query Posted: 21 Aug 2013 05:28 AM PDT I have a query that uses the SUM aggregate function to return results for over 200 columns. Based on the @DateFrequency selected it groups the results by ('Daily', 'Weekly', 'Monthly') for a selected start and end date timeframe. The issue is now for several of the columns they want to just have the last record result for that timeframe. So if weekly or monthly is selected it will only display the last record available for that column by the most recent date. I know you can use the (select top 1... order by date desc) to get this result traditionally but the issue I am running into is the query uses all aggregates. So any calculation I make since it is not an aggregate it wants me to add that to the group by which then skews my results that right now group correctly based on the @DateFrequency selected. I tried creating a UDF that uses the TOP 1 logic but it still wants me to include the column name in the group by unless there is something I am doing incorrectly. Is it possible with the given conditions I have described to get a last record result or do I need to rework the query? I have attached a condensed version of my query. Any feedback would be appreciated.

Argument data type datetime is invalid for argument 1 of substring function Posted: 21 Aug 2013 12:26 PM PDT Hi,I have this query that works fine.[code="sql"];WITH cte AS (select * from (SELECT distinct pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'February' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [UltiMonth],rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code],pehIsVoided AS [IS Voided],pehIsVoidingRecord AS [Voiding Record],pehIsOvertime AS [Is Overtime]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t right outer join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]where pehPerControl > '201301011' AND [EARN CODE] = '0002'AND [IS Voided] <> 'Y'AND [Voiding Record] <> 'Y' AND [Is Overtime] = 'Y' AND [org level 2] like '%ZSW'--AND [ULTIMONTH] = 'FEBRUARY'--ORDER BY pehPerControl)SELECT * ,SUM([Current Amount]) OVER (PARTITION BY [Emp No],[UltiMonth]) AS [Monthly Amount]FROM cte[/code]Turns out I used the wrong field (pehPerControl) which was formatted like - 201301111I need to now use 'pehPaydate' (because there can be more than one) which is formatted like - 2013-01-13 00:00:00.000So, If I change the all columns in the above query to use 'pehPaydate' now I get an error:Argument data type datetime is invalid for argument 1 of substring function.Pretty sure I need to use a cast or convert in my substring - but can't get the syntax right.

Fetch numeric values from string using SSIS Posted: 21 Aug 2013 09:25 PM PDT I need to implement below scenario in SSIS 2008I am getting values like below.EN 60320.V.1EN 61000-4-1EN 294EN ISO 3506-1EN 61400-25-6Need output as6032061000294350661400

Result that matches all the values from a list Posted: 21 Aug 2013 05:45 PM PDT I have two tables which are listed below.[code="sql"]TableAStudentID StudentName1 A2 B3 C4 D5 E[/code][code="sql"]TableBStudentID ClassID SectionID1 2 53 2 7[/code]Now I am trying to retrive those students for which all items in a input list matches.For example, If I pass the input list(ClassID & SectionID) as (2, 5), it should return StudentID : 1 If I pass the input list as (2, 5 | 1, 1) it should not return the StudentID : 1[code="sql"]DECLARE @tblData AS TABLE( [ClassID] INT ,[SectionID] INT)INSERT INTO @tblData VALUES (2, 5)INSERT INTO @tblData VALUES (2, 1)SELECT A.[StudentID] ,A.[StudentName] ,B.[ClassID] ,B.[SectionID]FROM [AAAAAA] AS A INNER JOIN [BBBBBB] AS B ON A.[StudentID] = B.[StudentID] INNER JOIN @tblData AS C On B.[ClassID] = C.[ClassID] AND B.[SectionID] = C.[SectionID][/code]But above mentioned query does not returning the expected value.Can you please help me?Regards,P. Paul

BCP Help Posted: 08 Aug 2013 09:17 PM PDT Hi all I am tasked with importing large fixed width text file to SQL, previously I used a library called filehelpers to assist through my code. Now the files are growing I keep getting out of memory errors so decided to try BCP.My command bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmtand the result SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: tt open a connection to SQL Server [2].SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Server Native Client 10.0]A network-related or specific error has occurred while establishing a connection to SQL Server is not found or not accessible. Check if instance name is correct and Server is configured to allow remote connections. For more information server Books Online.SQLState = S1T00, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expiredThis is a localhost server with windows authPlease help me learn why, I have tried with the -t switch and same result

Omit Data Length for Numeric Values Posted: 21 Aug 2013 06:01 AM PDT Hello EveryoneI am merely playing around with an idea today. It is slow today, so I needed to teach myself something new. But as with some things, I have ran into a snag. I could do this manually, but that never any fun.I am executing a select query that returns the column names and the data type for a single table. I am using a system table in the MSDB for sample purpose, since we all have an MSDB database to use.[code="sql"]SELECT c.name AS ColumnName, t.name+'('+CAST(c.max_length AS varchar(3))+')' AS ColumnDataTypeFROM sys.all_columns cJOIN sys.types t ON c.user_type_id = t.user_type_idWHERE object_id = ( SELECT object_id FROM sys.tables WHERE name = 'backupset')ORDER BY c.column_id ASC[/code]That will return this resultset:[quote]ColumnName ColumnDataTypebackup_set_id int(4)backup_set_uuid uniqueidentifier(16)media_set_id int(4)first_family_number tinyint(1)first_media_number smallint(2)last_family_number tinyint(1)last_media_number smallint(2)catalog_family_number tinyint(1)catalog_media_number smallint(2)position int(4)expiration_date datetime(8)software_vendor_id int(4)name nvarchar(256)description nvarchar(510)user_name nvarchar(256)software_major_version tinyint(1)software_minor_version tinyint(1)software_build_version smallint(2)time_zone smallint(2)mtf_minor_version tinyint(1)first_lsn numeric(13)last_lsn numeric(13)checkpoint_lsn numeric(13)database_backup_lsn numeric(13)database_creation_date datetime(8)backup_start_date datetime(8)backup_finish_date datetime(8)[/quote]I have shortened the resultset by a few rows. I would like to be able to not show the max_length of the data type for all non-character data types.So for example:[quote][u]ColumnName[/u] [u]ColumnDataType[/u]backup_set_id intbackup_set_uuid uniqueidentifiermedia_set_id intdatabase_name nvarchar(256)server_name nvarchar(256)machine_name nvarchar(256)last_media_number smallintcatalog_family_number tinyintcatalog_media_number smallintposition intexpiration_date datetimesoftware_vendor_id intname nvarchar(256)description nvarchar(510)user_name nvarchar(256)[/quote]I have tried a CASE statement in the JOIN clause, that only limits the rows returned.Does anyone have an idea as to how to correctly make this work?Thank you in advance for all your assistance, suggestions and commentsAndrew SQLDBA

Had to change query to use date field Posted: 21 Aug 2013 06:52 AM PDT Hi,I needed to change part of my query to use a date field now I can't get it to work:This is the original (works)[code="sql"]select left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) as name from pearhist[/code]This one no work:[code="sql"]select left(substring(cast (pehpaydate as varchar)(pehpaydate,5,len(pehpaydate)),2) as name from pearhist[/code]

Query to get immediate next date row into end_ts column in Tsql/db2 sql Posted: 21 Aug 2013 05:58 AM PDT I copied the query result set to TEST.xls and required result set into result.xls.I have a table now where we have addressline1, city,state, zip, county ,file_no and envelope_date. But for some of the records we have more than one address for particular file_no for different timestamp, you can see in the test.xls for example.what I have to do is, we have a huge table where they have all the information, so I m pulling all the above mentioned fields from this huge table and loading into a process table, then planning to load them in to new dimension table and then get the dimension key and load into FACT Table, but I got into this issue where I m seeing more than one address for one file_no, so what I m planning to do is add a end_ts column to my process table, and then select address between start and enddates. Start date is same as envelope_date and end_ts is nothing but next row in the envelope_date column.Now I m not quite sure how can I pull the next row from same column and update end_ts.Sorry if this explanation is confusingThanks in advance

Executing a stored proc on a linked server Posted: 21 Aug 2013 03:46 AM PDT We have a situation were we want to get the results of a stored procedure from a DB2 database in SQL. I would think we would need a linked server set up pointing to DB2. Does anyone have any link or source materials I could use to determine how to do this?Thanks!

Grouping data... Posted: 21 Aug 2013 01:37 AM PDT Hello, I´m new in this forum. Maybe someone can help me with my problem:My data looks like this:STRBEZ STRNR10. Oktober-Straße 13110. Oktober-Straße 13218. November-Platz 14128. Mai-Platz 1318. Mai-Platz 132Abstimmungsstraße 1411Abstimmungsstraße 1412Ackerweg 711As a result I want to get this:10. Oktober-Straße 131,13218. November-Platz 14128. Mai-Platz 131,132an so on...Is there a "simple" SQL-Statement for this problem? I have resolved such a problemby using a function - but maybe there exists an easier way...Thanks vor helping

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] I want to make my Server 2008 R2 server a dedicated game server.

---

• I want to make my Server 2008 R2 server a dedicated game server.
• Logic to break period of time by month
• problem with installation of Sql Server Management Studio Express 2008
• Any way to check sys configurations from SSMS wizard.
• SQL Agent Job-Retry
• how to pass parameters into an openquery ?.
• What's the best practice to replicate a sql 2008 database to 2005?
• sp_addextendedproperty
• Getting cumulative update version number which is currently installed on sql server
• selecting recent two set of log entries from a table with time
• Index Fragmentation and Page count minimum
• How does reducing the logical read increase performance ?
• connection string issue in app with integrated security
• RAID Caching and SQL Server 2008 R2
• Sql Server Partitioning
• how to return installed services on server 2000
• system table having modify by information for modified dbobjects
• Order by Name not works
• Teradata linked server fails after restarting SQL service...why?
• Find Deadlocks
• Email contents of table

I want to make my Server 2008 R2 server a dedicated game server. Posted: 21 Aug 2013 07:01 PM PDT Helle SQL ServerCentral GURU,You see the subject and I want to make a server to be dedicated for a game. The server at self is noting installed and did not even have a role.Which steps much I do on server configuration to make a dedicated server?greetz, Karim

Logic to break period of time by month Posted: 21 Aug 2013 03:04 PM PDT Hi,Can anybody give me and idea if it's possible, I need to check the following table and do some logic counting period of time but withing each of month, so let say for id=303 with period = 45, I'll get 3 records ,etc:[code="other"] 303 6/27/13 8/11/13 45 || \/ 1 303 6/27/13 6/30/13 32 303 7/01/13 7/31/13 313 303 8/01/13 8/11/13 11 [/code]Is there any date function to do this in TSQL?my sample data[code="other"]with ttt as (select 123 Id, DATEADD(d,-10,getdate()) fromDD, DATEADD(d,-3,getdate()) toDD, datediff(d, DATEADD(d,-10,getdate()), DATEADD(d,-3,getdate())) period ,1000 prodID unionselect 202 Id, DATEADD(d,-25,getdate()) fromDD, DATEADD(d,-17,getdate()) toDD, datediff(d, DATEADD(d,-25,getdate()), DATEADD(d,-17,getdate())) period ,2000 prodID unionselect 303 Id, DATEADD(d,-55,getdate()) fromDD, DATEADD(d,-10,getdate()) toDD, datediff(d, DATEADD(d,-55,getdate()), DATEADD(d,-10,getdate())) period ,3000 prodID )select * from ttt[/code]Thanks Mario

problem with installation of Sql Server Management Studio Express 2008 Posted: 20 Aug 2013 07:39 PM PDT the installation stops because of a Rule Check Failure -"Previous Release of Microsoft Visual Studio failed" It says that i have to " Upgrade Visual Studio 2008 to sp1 before installing Sql server 2008"i already have 'SQL Server Express 2008 r2' and 'Visual Studio 2008 Professional Edition' installed. Please suggest how i can install SSME..

Any way to check sys configurations from SSMS wizard. Posted: 21 Aug 2013 05:59 PM PDT If i am not wrong ,we can change configuration setting through ssms wizard without using sp_configure.Can someone provide me link for the same.

SQL Agent Job-Retry Posted: 21 Aug 2013 04:24 PM PDT Dear all,we have sql agent job,the job is schedule everyday @9.30 A.M and @5.30 P.M.@9.30 scheduled job is failure means need to retry every 10 mins with 3 attempt.I have question retry attempt job should affect current job schedule or not.please explain the detail,if we give the retry timeout for job there is any problem?please help on this.

how to pass parameters into an openquery ?. Posted: 21 Aug 2013 07:50 AM PDT How i can pass parameters into an openquery ?.I have openquery it just works fine with the fixed values but i want to pass the values to START_DATE AND END_DATE from variables and then i want to put the result of that to the "dbo.INFORMATION" table. [quote]declare @STDATE as datetimeset @STDATE = cast (GETDATE()-1 as date )declare @EDATE as datetimeset @EDATE = cast (GETDATE() as date )insert into dbo.INFORMATION SELECT * FROM OPENQUERY ([linkedserver],'set fmtonly off Exec [Database].[dbo].[SP_ALL_DAY] @START_TIME = ''@STDATE'', @END_TIME = ''@EDATE'', @RES = ''day'', @AGG = ''n'', @FIL =''ALLLIST'', @COMPANY = ''%'', @NAME = '''' ') [/quote]

What's the best practice to replicate a sql 2008 database to 2005? Posted: 21 Aug 2013 06:35 AM PDT I have a 2008 DB on my laptop, I need to temporarily set up a server version on 2005, here is the trouble I encountered:1. I backed up and tried to restored, it failed (maybe it works if from 2k5 to 2k8)2. I created new DB on 2k5, export all tables from 2k8 to 2k5, fine, working, however, all Keys/Constraints/Defaults are not there, I can't manually added those things.3. I generated script for all objects, changed some settings and double checked to make sure the Keys/Constraints/Defaults are there, I ran the script on server, tables created with Keys/Constraints/Defaults4. I then import data from 2k8, failed because of "Failure inserting into the read-only column "ID". (SQL Server Import and Export Wizard)"So what should I do with minimum time and effort for this task?Thank you very much.

sp_addextendedproperty Posted: 14 Jul 2013 05:38 PM PDT Hi All,I came across "sp_addextendedproperty" in Adventureworks2008R2 .EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for ContactType records.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ContactTypeID'GO Can someone help me explaining the use of this sp..Where and when do you use it..If possible can you please explain me with someother example..

Getting cumulative update version number which is currently installed on sql server Posted: 21 Aug 2013 01:22 AM PDT Hi,I am trying to find out which cumulative update is currently installed in SQL server 2008 R2.I can get the service pack and build number using this:- select @@version which gives me:-Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) I have searched using 10.50.4000.0 but I can't get the cumulative update.

selecting recent two set of log entries from a table with time Posted: 21 Aug 2013 10:16 AM PDT Hi Experts,We had a application that enters logs into database approximately for every half an hour.Lets say if the apllication started at 1:01 and ended its at job 1:05, the next run of application logging entries will be started at 1:36. Because it calculates 30 mins from 1:05 i.e 30 mins from recent end time.. If the application again started logging some rows starting 1:36 to 1:42. The next logging instance will start at 2:12. The number of rows logged by application might be from 0 to 100(apprx)Here the requirement is i have to send a email with that log entries for the last two loggings, for Ex: If i Set a job to run at 2:00 It should select the logging that occured between 1:01 to 1:05 and 1:35 to 1:42. How can i enable a job, which always selects recent two set of log entries .Please help me in this.Any input is appreciatedThanks

Index Fragmentation and Page count minimum Posted: 01 Aug 2013 08:21 AM PDT I started writing rebuild index script.However, I came across about "SET @page_count_minimum" value .Could you please help me understand what is page_count_minimum and its value to be set etc.Thanks,

How does reducing the logical read increase performance ? Posted: 21 Aug 2013 07:28 AM PDT How does reducing the logical read increase performance ? Your valuable inputs are welcome.Thanks in Advance !

connection string issue in app with integrated security Posted: 21 Aug 2013 05:23 AM PDT I am a sql server DBA. I am not completely aware of how IIS recognizes a sql server windows login credentials.This is for our testing only and doesnt reflect any prod or development env's. Right now we have created a new domain TESTApp.xyz.local and added an Active Passive windows and SQL Server Cluster set up to this domain. So my test database server is testdb\apptest. Our test app server is sitting on a different domain which is abc.local. The app server name is appserver1. Now I have restored the databases in testdb\apptest instance for the apps being hosted on appserver1. How do I ensure that the user will be able to login to application?Apps are using .net framework 4 and using IIS. If i create a domain account say appuser.xyz.local and create a login for this in testdb\apptest and give appropriate permissions on the database then how will the app be able to use this user to login to the app? what changes are required here at IIS as well as config file level. Currently the config files are using integrated security.Need your help experts

RAID Caching and SQL Server 2008 R2 Posted: 21 Aug 2013 05:41 AM PDT Quick background:I have set up three Dell Poweredge 1950's for me to practice setting up an SSRS Scale Out Deployment. In doing so, I set up the RAID controller on each. Everytime the system boots, I get the following error message: "The battery hardware is missing or malfunctioning, or the battery is unplugged. If you continue to boot the system, the battery-backed cache will not function. Please contact technical support for assistance."I think I can solve that error by going into the (<ctrl>+<r>) and checking the option for "Force WB with no battery". But, I would like to understand this a little more before doing it.My SR DBA has asked me a few questions: 1. What is default caching options for SQL Server? 2. What are the two caching options available with the RAID controller and SQL Server.I have been trying my best to research these questions, so that I can intelligently speak about them. I believe the answer to the first question is Write Ahead Logging and I believe the answer to the second question is Read and Write. But both of those can go deeper, for write you can write back or write through. For read, you can have read ahead, adaptive read ahead or No read ahead.Where I am confused is, how does checking "Force WB with no battery" relate to WAL? And how does WAL relate to write back or write through caching? Is one of those a nickname for WAL?I have read a lot of articles about this today and I am still not clear on what it all means. I hoping one of the guru's on this site can make this make sense to me.

Sql Server Partitioning Posted: 21 Aug 2013 02:49 AM PDT Hi Everyone,I'm running Sql Server 2008 R2 and I'm configuring table partitioning for 3 tables.My question is, what is the recommended approach - to create one partition scheme and one partition function on each table, or one scheme and one function that is then used to partition all the tables?The partition key is the same on each table and the table row counts are:Table1 = 60 million recordsTable2 = 20 million recordsTable3 = 100 million recordsThanks in advance.

how to return installed services on server 2000 Posted: 21 Aug 2013 01:54 AM PDT Hi,can anyone have idea on my question please let me know. i have script that works only for 2005 and above versions. i am looking for server 2000( this returns all services like dts,agent,ssas,ssrs....etc)cheers

system table having modify by information for modified dbobjects Posted: 21 Aug 2013 01:04 AM PDT I would like to get info database objects like procedure ,views, table valued functions modified by info across all databases on a server.Is there any sys table or catalog view get to "modify by" information ?I don't like to use DDL trigger on db and DDL on aLL server don't provide such info in its events.

Order by Name not works Posted: 20 Aug 2013 11:54 PM PDT Hi,I tried to sort names in my application. It is strange that W comes in between V :alien:Select * from (Select 'Västerbergslagens Kraft AB' NameunionSelect 'Västerbergslagens Elförsäljning AB' NameunionSelect 'Wäst-Bygg AB' NameunionSelect 'Västbo Galvan AB' NameunionSelect 'Västanvind Vindkraftskooperativ ek för') torder by t.Name[b]Actual output:[/b]NameVästanvind Vindkraftskooperativ ek förVästbo Galvan ABWäst-Bygg ABVästerbergslagens Elförsäljning ABVästerbergslagens Kraft AB[b]Expected: [/b]NameVästanvind Vindkraftskooperativ ek förVästbo Galvan ABVästerbergslagens Elförsäljning ABVästerbergslagens Kraft ABWäst-Bygg ABPlease guide me.

Teradata linked server fails after restarting SQL service...why? Posted: 20 Aug 2013 11:55 PM PDT I have two linked servers setup on my SQL server 2008 install. For some reason, these linked servers work just fine, until I restart the SQL service, or restart the SQL server itself. Before a restart of either, I can test the connection and it is successful. After a restart of either, I test the connection and it fails with the "Cannot create instance of OLE DB provider "TDOLEDB.1" for linked server "teradata_a". (Microsoft SQL Server, Error: 7302)". The teradata client is 13.10.The weird thing is by the next morning it seems to clear itself up on its own, almost like there is some link that gets reset between the teradata server and my SQL server. However, in the meantime I can connect successfully via the Teradata SQL Assistant on the server.Anybody else run across this and/or know of a fix?

Find Deadlocks Posted: 20 Aug 2013 08:22 PM PDT I am asked to check whether their was any deadlock occured 15 days back.. how do i do this :w00t:

Email contents of table Posted: 20 Aug 2013 09:44 PM PDT Hi everyone.Hope someone can offers some guidance.Have a requirement to send the contents of a table via email to a location on a set schedule.What is best way to approach this ?If anyone has any recommendations on the best way to set this up , please let me know.For example should I copy contents to a separate table, or extract from current table and email from there ?thank you.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610