[Articles] The IT Employee Benchmark

---

The IT Employee Benchmark Posted: 09 Apr 2013 11:00 PM PDT What's important in an IT employee? Steve Jones talks about the skills and measurements CIOs want today and tomorrow. Today's editorial was originally released on July 22, 2008. It is being re-published as Steve is at SQL Intersection.

You are subscribed to email updates from SQLServerCentral.com Articles tagged Editorial 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 Data Warehousing] T-SQL SCD2

Here is the query i am using in case if it helps.

To give you little more scenario  on my request  : Our Data warehouse is very traditional approach of ETL , We have update statments that does lookup and update the Foriegn keys.

My first update statments goes and finds an employee based on a logic of Data Entry

Update dbo.SFWP 
Set EmployeeID  = we2.EmployeeSK
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
and  Src_EntryDate >= we2.EffectiveBeginDTM and 
Src_EntryDate <= we2.EffectiveEndDTM

Apparently for some reason some employees cannot be matched because  our employee table has this condition not met because of missing records

and  Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM

So, my new query i am trying to take a previous or Next record and find employee ID and update it.

wITH CustCTE as
(
select  
We2.SK,
We2.NK,
We2.EffectiveBeginDTM,
We2.EffectiveEndDTM,
ROW_NUMBER() OVER (PARTITION BY We2.NK ORDER BY We2.EffectiveBeginDTM) RowNum
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
Where 
 Src_EntryDate < we2.EffectiveBeginDTM or 
Src_EntryDate > we2.EffectiveEndDTM
and a.EmployeeID is NULL
)
,
CustCTE1 as (
Select 
a.SK
,a.NK
,a.EffectiveBeginDTM
,a.EffectiveEndDTM
,a.RowNum
    
From CustCTE a
Union All 
Select 
Curr.SK
,Curr.NK
,Curr.EffectiveBeginDTM
,Curr.EffectiveEndDTM
,Curr.RowNum
    
From CustCTE Curr
Inner Join      CustCTE1 Prev
on Curr.NK = Prev.NK
and Curr.RowNum = Prev.RowNum-1
)
select * From CustCTE1

But i am not getting the same thing as i expect..

Any help in this matter will be highly appreciated.

thank you in advance

.

social.technet.microsoft.com/Forums

[SQL 2012] Connecting to Listener takes several seconds

---

• Connecting to Listener takes several seconds
• SSMS 2012 error when connect to sql server with windows authentication after VS instalation
• SQL 2012 management (query) tools only

Connecting to Listener takes several seconds Posted: 10 Apr 2013 02:25 AM PDT Hi All,I have a client that is connecting slow to the listener of our AlwaysOn availability group. The listener has 2 IPs in different subnets. He is specifying MultiSubnetFailover=True but it doesn't appear that it is checking both IPs in parallel. What appears to be happening is it is checking one, waiting for the timeout and then checking the other IP address. We have several others that are not experiencing this issue.He is running .NET Framework v4.0.30319 so that doesn't appear to be the problem. Any other ideas of what to check?

SSMS 2012 error when connect to sql server with windows authentication after VS instalation Posted: 09 Apr 2013 07:33 PM PDT Hi.After install VS 2012 in my pc i can't connect with SSMS 2012 to my SQL Server 2012 server (i can connect to other server -only SQL Server 2000 server-). I receive the next error message:"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.) (Microsoft SQL Server, Error: 258)".I already execute 'netsh winsock reset' command but the result is the same. I don't know what more doing.Somebody could help me?Thanks.

SQL 2012 management (query) tools only Posted: 09 Apr 2013 12:18 PM PDT Hi I am a software developer and have access to SQL server of different version - 2005, 2008 and so onI have a new laptop and I need good solid query tools with intilesense and preferably version control. I don't want to commit to install a full version of SQL on my laptop unless I know for sure I will be able to install different version side-by-side. I prefer to install only the management studio and not the database engine.What are my options and does the express version management tools of 2012 offer some kind of version control?Thank you in advanceMyalo

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] Need advice on this string manipulate scalar UDF

---

• Need advice on this string manipulate scalar UDF
• CTE and Spool operators
• Initial size of the database file using T-SQL
• The query processor could not start the necessary thread resources for parallel query execution.
• T-SQL Help
• Broadcast calender generator
• Column name or number of supplied values does not match table definition
• Exclude weekends from DateDiff
• TSQL help
• small case but stuck here now

Need advice on this string manipulate scalar UDF Posted: 09 Apr 2013 11:44 PM PDT Dear all,[b]Can you please comment on this UDF, is there any better way to do it?For example CLF scalar UDF or inline table UDF?[/b]There is around 50 Million rows in TableABC and it will be around 1GB in size if it is exported as plain text .we want to use this function in this way:[code="sql"]SELECT 'D' AS RECORDTYPECODE, a.[Id],a.[Col1],a.[Col2],a.[Col3],%DBNAME%.ReplaceSplVarcharsBICD(a.[Col4]),%DBNAME%.ReplaceSplVarcharsBICD(a.[Col5]),%DBNAME%.ReplaceSplVarcharsBICD(a.[Col6]),a.[genID] FROM %DBNAME%.TableABC a[/code]The function is shown below:[code="sql"]USE [BPSUAT_BICD]GO/****** Object: UserDefinedFunction [dbo].[ReplaceSplVarcharsBICD] Script Date: 04/10/2013 16:08:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE function [dbo].[ReplaceSplVarcharsBICD](@value varchar(8000))RETURNS varchar(8000)asbegindeclare @result varchar(8000);-- This function is used to Replace some Special characters with space for varchar and char datatypes.-- Replacing for Enter, newlineselect @result = REPLACE(REPLACE(REPLACE(@value, CHAR(10) + CHAR(13), ' '),CHAR(10),' '), CHAR(13),' ');--Replacing other non printable characters from Hex 00 to Hex'1F' and Hex'7F'select @result = REPLACE(@result, CHAR(0),' ');select @result = REPLACE(@result, CHAR(1),' ');select @result = REPLACE(@result, CHAR(2),' ');select @result = REPLACE(@result, CHAR(3),' ');select @result = REPLACE(@result, CHAR(4),' ');select @result = REPLACE(@result, CHAR(5),' ');select @result = REPLACE(@result, CHAR(6),' ');select @result = REPLACE(@result, CHAR(7),' ');select @result = REPLACE(@result, CHAR(8),' ');select @result = REPLACE(@result, CHAR(9),' ');--select @result = REPLACE(@result, CHAR(10),' ');select @result = REPLACE(@result, CHAR(11),' ');select @result = REPLACE(@result, CHAR(12),' ');--select @result = REPLACE(@result, CHAR(13),' ');select @result = REPLACE(@result, CHAR(14),' ');select @result = REPLACE(@result, CHAR(15),' ');select @result = REPLACE(@result, CHAR(16),' ');select @result = REPLACE(@result, CHAR(17),' ');select @result = REPLACE(@result, CHAR(18),' ');select @result = REPLACE(@result, CHAR(19),' ');select @result = REPLACE(@result, CHAR(20),' ');select @result = REPLACE(@result, CHAR(21),' ');select @result = REPLACE(@result, CHAR(22),' ');select @result = REPLACE(@result, CHAR(23),' ');select @result = REPLACE(@result, CHAR(24),' ');select @result = REPLACE(@result, CHAR(25),' ');select @result = REPLACE(@result, CHAR(26),' ');select @result = REPLACE(@result, CHAR(27),' ');select @result = REPLACE(@result, CHAR(28),' ');select @result = REPLACE(@result, CHAR(29),' ');select @result = REPLACE(@result, CHAR(30),' ');select @result = REPLACE(@result, CHAR(31),' ');select @result = REPLACE(@result, CHAR(127),' ');--Replacing the pipe symbol select @result = REPLACE(@result, CHAR(124),' ');--Repalcing the NULsselect @result = Nullif(@result,'');--Removing the Right Spaces select @result = RTRIM(@result);--Removing the Left Spacesselect @result = LTRIM(@result);return @result;end[/code]or should we use CLF scalar UDF like this[code="VB"] using System; using System.Data.Sql; using System.Data.SqlTypes; using System.Collections.Generic; using Microsoft.SqlServer.Server; using System.Text; using System.Text.RegularExpressions; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString ReplaceSplVarcharsBICD(SqlString s){ if (s.IsNull) return String.Empty; //Removing the Right Spaces and Left Spaces string s1 = s.ToString().Trim(); if (s1.Length == 0) return String.Empty; StringBuilder tmpS = new StringBuilder(s1.Length); //striping out the "control characters" //Control characters are non-printing and formatting characters, such as ACK, BEL, CR, FF, LF, and VT. The Unicode standard assigns the following code points to control //characters: from \U0000 to \U001F, \U007F, and from \U0080 to \U009F. According to the Unicode standard, these values are to be interpreted as control characters unless //their use is otherwise defined by an application. Valid control characters are members of the UnicodeCategory.Control category. //Replacing for Enter, newline 0D0A or \x000D (13) or \x000A (10) //Replacing other non printable characters from Hex 00 to Hex'1F' and Hex'7F' subset of control characters //Replacing the pipe symbol \x007C (124) //Repalcing the NULs 00 for (int i = 0; i <= s1.Length; i++) { if (!Char.IsControl(s1[i]) & !s1[i] =='\x000D' & !s1[i] =='\x000A' & !s1[i] =='\x007C') { tmpS.Append(s1[i]); } else { tmpS.Append(' '); } } string result = tmpS.ToString().Trim(); return result; } }[/code]

CTE and Spool operators Posted: 09 Apr 2013 08:28 AM PDT I commonly find CTE using spool operators in the execution plan. According to Microsoft the spool operator uses the temp table."The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input."So this leads to believe its possible that CTE's are using tempdb? so are the results stored in memory or tempdb? or does is DEPEND?

Initial size of the database file using T-SQL Posted: 09 Apr 2013 04:19 PM PDT Hi,Please let me know how to get the initial size of the database file using T-SQL. sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.Thanks,Karthik R

The query processor could not start the necessary thread resources for parallel query execution. Posted: 09 Apr 2013 04:50 PM PDT I'm getting below error while running this query. [code="sql"]The query processor could not start the necessary thread resources for parallel query execution.[/code] As per my understanding, # of worker threads is automatically decided by SQL Engine. then how can I avoiud this error. does changing the Max worker threads help in this case? Please suggest. [code="sql"]DECLARE @OMSSubscriptionIdentifier AS NVARCHAR(50), @CTPSubscriptionIdentifier AS NVARCHAR(50), @PromotionCodeIdentifier AS NVARCHAR(50), @EDWSourceSystemID AS INT = 2780; SELECT @OMSSubscriptionIdentifier = dbo.fnIDFIdentifierDefault(), @CTPSubscriptionIdentifier = dbo.fnIDFIdentifierDefault(), @PromotionCodeIdentifier = dbo.fnIDFIdentifierDefault(); SELECT __$Operation, OnlineServiceSubscriptionIdentifier, PromotionCodeIdentifier as OnlineServicePromotionCode, EDWSourceSystemID, EDWIsDeletedFlag, LNKSID FROM (SELECT NULL as __$Operation, CASE WHEN SUB.OMSSubscriptionIdentifier <> @OMSSubscriptionIdentifier THEN CAST(SUB.OMSSubscriptionIdentifier AS NVARCHAR(50)) ELSE CAST(SUB.CTPSubscriptionIdentifier AS NVARCHAR(50)) END AS OnlineServiceSubscriptionIdentifier, prom.PromotionCodeIdentifier, @EDWSourceSystemID AS EDWSourceSystemID, CAST(0 AS bit) AS EDWIsDeletedFlag, NULL AS LNKSID, CASE WHEN SUB.OMSSubscriptionIdentifier <> @OMSSubscriptionIdentifier THEN SUB.OMSSubscriptionIdentifier_OrderBy ELSE SUB.CTPSubscriptionIdentifier_OrderBy END AS OnlineServiceSubscriptionIdentifier_OrderBy, prom.PromotionCodeIdentifier_OrderBy FROM stg.IDFOnlineServiceSubscriptionPromotion Prom JOIN stg.IDFOnlineServiceSubscription Sub ON Prom.SubscriptionKey = Sub.OnlineServiceSubscriptionSourceKey WHERE CASE WHEN SUB.OMSSubscriptionIdentifier <> @OMSSubscriptionIdentifier THEN 2770 ELSE 2780 END = @EDWSourceSystemID ) A WHERE A.PromotionCodeIdentifier <> @PromotionCodeIdentifierORDER BY UPPER(OnlineServiceSubscriptionIdentifier_OrderBy) , UPPER(PromotionCodeIdentifier_OrderBy) ][/code]

T-SQL Help Posted: 09 Apr 2013 06:54 AM PDT This seems like a very simple question but for some reason the answer is eluding me.Given the following table data (Customers) how do I select all the records excluding the duplicates with an earlier Actual Date?[code][u]Next[/u] [u]CustId[/u] [u]Customer[/u] [u]Scheduled Date[/u] [u]Actual Date[/u] [u]Last[/u]2013 101 Cust1 2011-04-08 2011-04-08 20112013 202 Cust2 2011-03-31 2011-03-31 20112013 202 Cust2 2010-12-02 2010-12-02 20112013 303 Cust3 2011-04-13 2011-04-13 20112013 404 Cust4 2010-12-29 2010-12-29 20122013 505 Cust5 2011-06-20 2011-06-20 20112013 505 Cust5 2011-01-25 2011-01-25 20112013 606 Cust6 2010-10-07 2010-10-07 20112013 707 Cust7 2010-07-20 2010-07-20 2011[/code]Would like to get:[code][u]Next[/u] [u]CustId[/u] [u]Customer[/u] [u]Scheduled Date[/u] [u]Actual Date[/u] [u]Last[/u]2013 101 Cust1 2011-04-08 2011-04-08 20112013 202 Cust2 2011-03-31 2011-03-31 20112013 303 Cust3 2011-04-13 2011-04-13 20112013 404 Cust4 2010-12-29 2010-12-29 20122013 505 Cust5 2011-06-20 2011-06-20 20112013 606 Cust6 2010-10-07 2010-10-07 20112013 707 Cust7 2010-07-20 2010-07-20 2011[/code]Thanks,Dave

Broadcast calender generator Posted: 21 Mar 2013 03:10 AM PDT Hi guys,Did anyone ever have to create a function/stored procedure that would take a date as an input and generate the values for the broadcast calender like:- Media_Week_Start_Date- Media_Week- Media_Month- Media_MonthName- Media_Quarter- Media_YearThanks in advance!

Column name or number of supplied values does not match table definition Posted: 09 Apr 2013 09:36 AM PDT Greetings,I have a vendor provided procedure that returns two result sets. I am trying to insert the first result set into a table. I believe this should work since I can disregard the second result set.--This worksCREATE TABLE #Test(a float,b float,c float,d float,e float,f int,g float,h DECIMAL(22,2),i DECIMAL(22,2),j DECIMAL(22,0),k DECIMAL(22,2),l float,m float)INSERT INTO #TestSELECT 3.58118449079261E+98, 9.6404720198456E+97, 8.59568543065516E+98, 2.36898164801007E+61, 1.58071941607988E+67, 103, 1.30623880297128E+97, 171334797.19, 131638.64, 404716, 566951665.25, 5504385.09951457, 4.09327353277521E+98SELECT * FROM #TestDROP TABLE #TestGO --I then create some procsCREATE PROCEDURE TestProc1ASSELECT 3.58118449079261E+98, 9.6404720198456E+97, 8.59568543065516E+98, 2.36898164801007E+61, 1.58071941607988E+67, 103, 1.30623880297128E+97, 171334797.19, 131638.64, 404716, 566951665.25, 5504385.09951457, 4.09327353277521E+98GO CREATE PROCEDURE TestProc2 --two return setsASSELECT 3.58118449079261E+98, 9.6404720198456E+97, 8.59568543065516E+98, 2.36898164801007E+61, 1.58071941607988E+67, 103, 1.30623880297128E+97, 171334797.19, 131638.64, 404716, 566951665.25, 5504385.09951457, 4.09327353277521E+98 SELECT 233, '2012-12-31 00:00:00', 'ABC', 2625859.36, 1.40291573314828E+77, 2773929.43, 2625859.36, NULL, 3.89158923999005E+83, 1.67548882141181E+197, 0.00507868627580116GO--This worksCREATE TABLE #Test(a float,b float,c float,d float,e float,f int,g float,h DECIMAL(22,2),i DECIMAL(22,2),j DECIMAL(22,0),k DECIMAL(22,2),l float,m float)INSERT INTO #TestEXEC TestProc1SELECT * FROM #TestDROP TABLE #Test--but this doesn't and I thought it wouldCREATE TABLE #Test(a float,b float,c float,d float,e float,f int,g float,h DECIMAL(22,2),i DECIMAL(22,2),j DECIMAL(22,0),k DECIMAL(22,2),l float,m float)INSERT INTO #TestEXEC TestProc2SELECT * FROM #TestDROP TABLE #TestYour comments are welcome.

Exclude weekends from DateDiff Posted: 09 Apr 2013 03:16 AM PDT Dear Friends,I need an urgent help-"How to Exclude Weekends from DateDiff function?"Kind RegardsDhananjay

TSQL help Posted: 09 Apr 2013 04:55 AM PDT Hi , I have a table from where I want to filter out some records as given below.CREATE TABLE #MissingTransactions (Code VARCHAR(10), Location VARCHAR(10), PrevDate DATE, PrevTime INT, NextDate DATE, NextTime INT, PrevTrans INT, NextTrans INT,NextSite VARCHAR(20),NextCompany VARCHAR(20))INSERT INTO #MissingTransactionsSELECT 'Code1','01','1/17/2013',1711,'1/18/2013',1859,74174,74209,'Site1','Comp1'SELECT 'Code1','26','1/14/2013',1124,'1/14/2013',1432,1,10,'Site2','Comp1'SELECT 'Code1','26','1/14/2013',1128,'1/14/2013',1533,2,20,'Site2','Comp1'SELECT 'Code1','26','1/14/2013',1358,'1/15/2013',722,9,22,'Site2','Comp1'SELECT 'Code1','26','1/16/2013',1611,'1/19/2013',819,70,133,'Site2','Comp1'SELECT 'Code1','26','2/24/2013',2055,'2/25/2013',1130,975,1000,'Site2','Comp1'SELECT 'Code1','26','2/25/2013',1034,'2/26/2013',717,999,1006,'Site2','Comp1'While doing a a Final Select in my report from #missingTransactionsI do not want to display PrevTrans IN (1,2,9) since Prev Trans(2) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTarns = 2 is equal to NextDate(1/14/2013) where PrevTrans = 1AND Prev Trans(9) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 1 OR Prev Trans(9) is in between Prev Trans (2) and Next Trans (20) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 2 Also I want to omit PrevTrans 975 and 999 with the same conditions like above.The only ones I want to display is PrevTrans 74174 and 70.Please help .

small case but stuck here now Posted: 09 Apr 2013 01:53 AM PDT I've never noticed or had the issue before:I have a sp with some datetime parameters:[code="sql"]ALTER PROCEDURE [dbo].[spUpdateDocInfoBySysID](@SysID int, @DocID int, @SelectedDocTitle varchar(255), @NewDocType int, @DocStatus varchar(10), @NewDateFirstIssued smalldatetime, @NewPreviousReviewDate smalldatetime, @NewNextReviewDate smalldatetime, @Note varchar(1000),@Contact varchar(50),@Phone varchar(20),@RiskRating int)[/code]exec [dbo].[spUpdateDocInfoBySysID] 2730, 1578, 'asdfasdfasdfasdf', 2, 'Active', CONVERT(datetime, '2011-10-30',103),CONVERT(datetime, '2013-01-22',103),CONVERT(datetime, '2222-02-22', 103)This threw me:[code="other"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'CONVERT'.[/code]exec [dbo].[spUpdateDocInfoBySysID] 2730, 1578, 'asdfasdfasdfasdf', 2, 'Active', '2011-10-30','2013-01-22','2222-02-22'This threw me:[code="other"]Msg 8114, Level 16, State 5, Procedure spUpdateDocInfoBySysID, Line 0Error converting data type varchar to smalldatetime.[/code]Can anyone point to me what's wrong with it? it has been working fine for quite a long time

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 Data Warehousing] Need some clarifications on Sql Server 2012 QA & Prod environment setup

I think the original question requested information on Licensing as well, which none of the answers contain. I agree on the HW configuration part - they majorly depend on application size including data volume.

With SQL Server 2012 we can use Standard (Core / Server-CAL), BI (Server-CAL) and Enterprise (Core) editions, - among them which edition to choose will depend on what features the solution needs. On high level, if the solution needs canned reporting on moderate sized data, Standard edition will do. If it requires ad-hoc / self-service BI then we need BI or Enterprise edition. If the data volume is high requiring columnstore indexing, we need Enterprise edition.

So far so good, all these are options for the Prod environment.

But for Dev and QA, can we use Developer edition license:

1. Will that license be able to access data from some other SQL Server database (which might be copy of Prod data for Dev and QA)

2. Will that license enable us to move SSIS/RS/AS/PowerView codes to Prod environment without any issues? 

Can some expert from Microsoft answer this question - which I think would answer a good part of the original question as well.

---

Souvik Banerjee. MSBI Lead, Zensar Technologies

.

social.technet.microsoft.com/Forums

[SQL server issues] Commented Issue: AdventureWorksDW2008R2 DimDate table has missing dates [13383]

Hello,
Is this been taken care? If so, please share the latest data for DimDate. I'm working on some POC which requires dates in between 2008 and 2010 as well. If it is not already done, can someone let me know is there is any way I can generate the data for those missing dates in that table?

.

sqlserversamples.codeplex.com

[SQL Server 2008 issues] Use of Threads in Data Flow task of SSIS

---

• Use of Threads in Data Flow task of SSIS
• connect sql server using windows authentication in another pc
• connecting sql server in different domain using Windows authentication
• Get data from 3 or more tables
• How to Get the Object Created by a Specific user in SQL Server
• tablediff Utility on a table with 1.3 billion records?
• Ignoring timeout in stored procedure
• uniqueidentifier scope?
• Linked Server Setup Always Creates a Loopback
• can't connect to sql 2000 from ssms 2008
• Need help on Powershell for SQL 2008 R2
• Another Conversion Question of Text to Date
• Get week ending date given the week number
• Linked server error
• Estimate storage for Images
• More than 1 Foreach Loop Container?
• SSIS list file names in directory
• Storing files in a varbinary
• Multiple Data and Log Files
• Weird Performance problem. Fast on standalone laptop, slow on super-duper server.
• How to get the next ID
• Using joins
• Vaccine Dose & Date Sequence Problems
• Conversion to Date
• Optimize for adhoc workloads
• Help with tuning stored proc
• Query Timeout help
• Using AOG spanning FCI.

Use of Threads in Data Flow task of SSIS Posted: 05 Apr 2013 01:07 AM PDT How are the threads used by data flow engine?Is it like it assigns same no of threads per data source and equivalent worker threads?If I have set a max row size or 1000 and have 1M rows in source. There is one transformation and finally one destination.How the data flow engine manages the data in the above scenario?

connect sql server using windows authentication in another pc Posted: 09 Apr 2013 06:44 PM PDT I am having sql server 2008 r2 express installed in a PC. I have some other PC which are connected to this pc in a local network.Now those PC connect to my pc's sql server using sql authentication. But now i want them to able to connect sql server in my pc using windows authentication. How other pc can connect to sql server installed in another pc using windows authentication?Please give me step by steps method of doing it.

connecting sql server in different domain using Windows authentication Posted: 09 Apr 2013 06:08 PM PDT hi,We have some client PCs connected to a server PC.Users in client PC access sql server 2008 r2 installed in server PC by using Sql authentication.Domains of server PC and client PC are different. Is there any way to use Windows Authentication in client PCs to connect to sql server provided we have same user account in server PC and client PC ? Thank you

Get data from 3 or more tables Posted: 09 Apr 2013 04:19 PM PDT I have tables with following definition'[code="sql"]CREATE TABLE [dbo].[items]( [srno] [int] IDENTITY(1,1) NOT NULL, [itemname] [nvarchar](255) NULL, [unitsymbol] [nvarchar](50) NULL, [itemtype] [nvarchar](50) NULL, [purchaseledger] [int] NULL, [salesledger] [int] NULL, [pvatclass] [int] NULL, [padtaxclass] [int] NULL, [svatclass] [int] NULL, [sadtaxclass] [int] NULL )[/code][code="sql"]CREATE TABLE [dbo].[voucher1]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix] [nvarchar](50) NULL, [voucherno] [int] NULL, [item] [int] NULL, [itemdesc] [nvarchar](50) NULL, [batchno] [nvarchar](50) NULL, [quantity] [float] NULL, [unit] [nvarchar](50) NULL, [rate] [float] NULL, [itemamt] [float] NULL, [disc] [float] NULL, [finalamt] [float] NULL, [vatclass] [nvarchar](50) NULL, [vat] [float] NULL, [adtaxclass] [nvarchar](50) NULL, [adtax] [float] NULL, [total] [float] NULL, [invoicetype] [nvarchar](50) NULL )[/code][code="sql"]CREATE TABLE [dbo].[voucher]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix] [nvarchar](50) NULL, [voucherno] [int] NULL, [voucherrefno] [nvarchar](50) NULL, [dt] [date] NULL, [details] [nvarchar](255) NULL, [invoicetype] [nvarchar](50) NULL )[/code]Items table contains data about items begin purchased and soldVoucher1 table contains data about which item purchase or sold at which rate and tax and etcVoucher table contains data about voucher no and data and etcI want to display each and every item from Items table whether its in voucher1 table or not.How can I get the data in following table format within a specified two date periods?I want data of opening qty(before first date),incoming qty(between two dates),outgoing qty(between two dates)ItemNo VoucherNo OpeningQTY IncomingQTY OutgoingQTY ClosingQTY

How to Get the Object Created by a Specific user in SQL Server Posted: 09 Apr 2013 03:38 PM PDT Hi Team,I am Looking to understand / create a query which will help to Identify the Objects which were created by a Windows User.Twist to this is that the user does not have Direct login, the Login for the user / access is provided as part of the LDAP DL

tablediff Utility on a table with 1.3 billion records? Posted: 09 Apr 2013 03:26 PM PDT Hi,I have loaded data from a table with 1.3 Billion records into a new table which I have partitioned indexes to improve performance in our data warehouse.However as I had to run the load over a few weeks I now need to update any records in Table 2 that have changed since the 1st load was done.I could use an UPDATE with a Join but as both tables have 1.3 Billion rows this could be problematic.I wonder if I could use the tablediff utility on tables of this size - would anyone know the possible issues during the running of the utility?Thanks for your help!JK

Ignoring timeout in stored procedure Posted: 09 Apr 2013 11:54 AM PDT I know that this might cause a lot of suggestions about query optimization and such, but I have a somewhat unique situation. I work for a fishing company that manages 6 vessels. Each of these vessels produce several seafood products on board. When enough customers request a new product (i.e., new case size or packaging size), our office sends out these new products as specifications to the linked server SQL databases on each vessel.If the desired product already exists on a vessel, a call to the linked server remote procedure updates the product with any particulars. If it doesn't exist, the same procedure inserts the new product. We have a product master table in the home database such that when the remote procedures are successful on every vessel, a process flag gets flipped from 0 to 1.The problem is, depending upon weather conditions or whether or not the vessel is making a turn, the satellite connection to the linked server and database might drop or become extremely slow. I want to execute a stored procedure that calls the remote procedure on each linked server, but continues to execute even if one vessel's linked server experiences a timeout (dropped connection). Example:[code="sql"]CREATE PROC dbo.MyProcedureASdeclare @token1 int ,@token2 int ,@token3 int ,@token4 int ,@token5 int ,@token6 int BEGIN EXEC Linkedserver1.DB.dbo.SPROC @Product = 'NewProduct', @Result = @token1 EXEC Linkedserver2.DB.dbo.SPROC @Product = 'NewProduct', @Result = @token2 EXEC Linkedserver3.DB.dbo.SPROC @Product = 'NewProduct', @Result = @token3 EXEC Linkedserver4.DB.dbo.SPROC @Product = 'NewProduct', @Result = @token4 EXEC Linkedserver5.DB.dbo.SPROC @Product = 'NewProduct', @Result = @token5 EXEC Linkedserver6.DB.dbo.SPROC @Product = 'NewProduct', @Result = @token6-- ... additional processing and checking of @token variables....END[/code]If any of the linked servers drop their connection, is there any way I can prevent it from stopping execution?Thanks in advance,Kurt

uniqueidentifier scope? Posted: 09 Apr 2013 09:46 AM PDT Hello - When a column is defined as a uniqueidentifier does that mean that the value is unique compared to all other uniqueidentifiers in the database or only for that coluumn? Can a database eventually reach a limit where it runs out of uniqueidentifiers, for example after trillions of records?

Linked Server Setup Always Creates a Loopback Posted: 09 Apr 2013 03:38 AM PDT I want to create a linked server to a remote server (not a loopback to the one I'm on). But for some reason, on the server I am trying to create this on, it ALWAYS creates a loopback, meaning the catalogs it shows after the creation are those that on the server I'm on - not the remote server. Odd thing is, I can created this linked server correctly on a different server, just not this one. Is there a server setting of some kind that causes this? Thanks!

can't connect to sql 2000 from ssms 2008 Posted: 16 Mar 2011 03:26 AM PDT i installed ssms 2008 on a windows 7 pc, connection to sql 2008 instance is fine, but can't connect to any sql 2000 instances. also, the BID comes with SQL 2008 CD has problem connect to any sql 2000 instances. Do i have to install any OLEDB\ODBC driver? any idea?

Need help on Powershell for SQL 2008 R2 Posted: 09 Apr 2013 06:25 AM PDT Folks ,I created powershell script to be used with SQL 2008 which uses Invoke-sqlcmd . The script runs fine on SQL 2008 environment , however when i try to run the same on other server with SQL 2005 installed on it , i get an error . On troubleshooting further it was found that "Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2" needs to be installed on the server . Can someone guide if the above installation for Powershell extensions has any prerequisite . The environment on this this needs to be installed is production and hence every positive and negative points needs to be taken into consideration .Kindly help !!!

Another Conversion Question of Text to Date Posted: 09 Apr 2013 03:07 AM PDT I've been searching for something similar, but haven't quite found it. My dilemma is that this table was created with a TransDate of right(CONVERT(VARCHAR(10), TheDate, 103),7), which is stored as 'MM/YYYY'. Why this is I'm still not sure; however, to change it now would seem to be more work than I'd expected and having to reload many rows of data.So, is there a way to convert this to 'mm/dd/yyyy'? I've tried using this and other methods, but still get conversion errors because of the mm/yyyy varchar format: cast(right(CONVERT(VARCHAR(10), TransDate, 103),7) as datetime)Thanks in advance!

Get week ending date given the week number Posted: 09 Apr 2013 06:29 AM PDT How can I get Saturday's date given the week number?This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.

Linked server error Posted: 09 Apr 2013 05:57 AM PDT I created a linked server in "ORDERALL" to remote server "ORDER".Exec one store procedure from "ORDERALL" to insert record to a table in "ORDER" got an error below.How to fix it?OLE DB provider "SQLNCLI10" for linked server "ORDER" returned message "The transaction manager has disabled its support for remote/network transactions.".

Estimate storage for Images Posted: 09 Apr 2013 02:27 AM PDT Hi,I'm hoping that some of you have worked before with databases where Images are stored?I'm referring to the situation where we would store a photo of a person so that, when an end user opened up a record for the person, they would see the photo along with the information.Can anyone give me an estimate on what a good size would be to allow for these images?It's for an estimate of storage and growth requirements for a new database (so I don't have any available images to use as a baseline).I appreciate any thoughts that you can provide,Steve

More than 1 Foreach Loop Container? Posted: 09 Apr 2013 04:39 AM PDT Hi,Is it possible to have more than 1 foreach loop container in SSIS 2008? My question is how can I loop through both different text files at same time (i.e., square files that have square patterns and circle files that have circle patterns)? Can I have more than one filespec property? If so, how do I accomplish this? I'm also trying to figure out how to get both different text files that will be looped through using a merge join transformation, so that the output will go into an excel file. Is it also possible to have more than one merge join transformation? Has anyone else done this before?

SSIS list file names in directory Posted: 09 Apr 2013 04:12 AM PDT Hello All,I am trying to create an SSIS package that will look at a directory, get a list of all the file names in the directory, and enter them into a table. Any advice to accomplish this?Thanks!Robert

Storing files in a varbinary Posted: 09 Apr 2013 03:28 AM PDT Hello,We have an app that copies files to a new location via unc...\\Server\Share. This however is slow over vpn and despite being wide open still challenges for credentials. Another option would be storing the files (they are small usually around 10kb) in the SQL 2008R2 db. I had tried this previously and had trouble. It seemed that storing the file into the db was not a problem but writing it out the file was unrecognizeable. IE it was an excel file and would go in as such but come out and not open. So I am looking for advice and or pointers for how to approach this. As of yet I do not have any code to critique as I'm trying to establish a good understanding to build code on.So any reading / direction would be appreciatedJBNo worries Google got me some good readingThx

Multiple Data and Log Files Posted: 08 Apr 2013 10:59 PM PDT Hi Guys,I wanted to know if it's possible to go back to a single data and log files from multiple files. Here's the scenario,a disk and data/log file becomes full. I decided to add another file on different disk to create another space. After some time, as data are removed from the file, the first file have some space again. Is it possible to delete the second file I created?Thank you

Weird Performance problem. Fast on standalone laptop, slow on super-duper server. Posted: 09 Apr 2013 01:16 AM PDT Hi All,We have a 3rd party application which collects data and shovels it via a web server into a SQL 2008 R2 database (O/S is Windows Server 2008 R2 - 64bit).It runs on a high spec HP physical box 32Gb RAM, 4 Quad Core CPU and SAN attached disk.The performance is dreadful loading the data and also running reports against the data.Even just switching between different screens on the web based application takes forever.Database is currently about 100Gb and continually growing.All the stored procs are encrypted so I can't actually see what's going on.DMVs would indicate that possibly the 3rd party code is inefficient or could benefit from better indexing BUT.... When we gave the 3rd party a copy of our database they got it flying.... On a laptop....I thought maybe SAN latency could be an issue so I ran SQLIO and baselined the SAN disks and got the 3rd party to do the same with their laptop. The SAN and laptop disk latency was very similar but the SAN throughput was about 10 times faster so it doesn't look like disk I/O is the issue.If the vendor can get the app to fly when they restore our database onto a laptop but it's slow on our far higher spec server it then I feel it must be something external to SQL to do with our Production environment.The network guys claim there are no issues (but then again they always do) and i've checked we don't run anti-virus s/w against database files, etc...The 3rd party aren't coming up with any solutions and i'm running out of ideas.Anyone got any suggestions where else to check?

How to get the next ID Posted: 09 Apr 2013 01:42 AM PDT Hello,How would i find out the next ID from the current one i have selected, as at the moment i pass in a value which returns a row of information, inside that information i need to return the ID example123 --- Im currently select this one, also select 4 as the next ID45?

Using joins Posted: 08 Apr 2013 11:59 PM PDT Hi,I Need to remove subqueries and need to use Joins following query..SELECT DISTINCT set_name, a.seq_no AS set_id FROM defulat_sets a WHERE set_type = @set_type AND ISNULL(qualifier1,'') = @qualifier1 AND ISNULL(qualifier2,'') = @qualifier2 AND ISNULL(qualifier3,'') = @qualifier3 AND ISNULL(qualifier4,'') = @qualifier4 --check against specialties AND ((specialties LIKE '%'+@dept+'%' OR specialties LIKE '%all%') AND specialties NOT LIKE '%not '+@dept+'%') --check against providers and cast(a.seq_no as varchar(36)) in (select distinct set_id from default_provs where set_id=cast(a.seq_no as varchar(36)) and ( --ALL is in the list and there is not a corresponding NOT (last_name='ALL' and set_id not in (select distinct set_id from default_provs where set_id=cast(a.seq_no as varchar(36)) and provider_id=@provider_id and not_ind=1)) OR --There is an ID and not a corresponding NOT id (provider_id=@provider_id and set_id not in (select distinct set_id from default_provs where set_id=cast(a.seq_no as varchar(36)) and provider_id=@provider_id and not_ind=1)) ) ) order by set_name Please give your input.

Vaccine Dose & Date Sequence Problems Posted: 09 Apr 2013 01:45 AM PDT Someone at one schools has reversed the dates associated with vaccine doses for various students. For example, a particular vaccine requires 5 doses with doses 1 having the earliest date and subsequent doses having newer dates. In a lot of cases, someone has completely reversed the date with the dose, e.g. Doses 5 is the oldest date and doses 1 has the newest date.Using Stu_ID 5846 as an example, dose 5 should have dose 1 date, dose 4 should have dose 2 date and dose 3 is correct.Does someone have a SQL example that would update the dose 1 with the earliest date and subsequent doses with appropriate dates? DATA:STU_ID_ Dose_1 Dose_2 Dose_3 Dose_4 Dose_55846 5/6/2003 8/25/1999 11/16/1998 9/11/1998 7/17/19984111 12/16/2003 3/26/2003 4/15/1998 2/13/1998 12/8/19972548 4/24/2003 8/12/1998 6/23/1998 4/13/1998 25118 4/26/1999 10/5/1998 7/1/1998 4/30/1998 25119 7/29/2002 4/26/1999 10/5/1998 7/1/1998 4/30/19985454 8/5/2003 5/22/2000 11/3/1998 1/29/1998 31936 9/17/2003 1/28/2001 1/28/2000 5/11/1999 12/11/199825572 3/20/2002 9/11/2000 12/15/1998 9/24/1998 6/10/19986004 4/2/2004 10/9/2000 1/10/2000 11/8/1999 9/8/19996377 3/25/2004 11/17/2000 2/16/2000 12/17/1999 10/18/19995327 9/24/1999 9/28/1998 7/27/1998 5/28/1998 9966 11/10/2005 8/15/2003 12/29/1998 11/2/1998 8/31/19989948 12/2/2005 10/30/2003 1/14/1998 1/20/1997 12/20/19964979 1/28/2003 6/22/1999 9/8/1998 6/30/1998 4/30/19984644 4/1/2003 10/13/1998 4/8/1998 2/10/1998 11/15/19977749 6/2/2004 3/15/1999 5/18/1999 7/19/1999 4/18/20014980 8/13/1999 6/10/1999 8/26/1998 7/15/1998 4/13/19985472 8/13/2003 4/27/2002 6/17/1999 4/8/1999 7437 6/2/2004 5/5/2000 8/16/1999 5/21/1999 3/7/19998150 4/28/2004 2/1/2000 6/17/1999 2/13/1999 12/19/19988151 4/28/2004 2/1/2000 6/17/1999 2/13/1999 12/19/199824370 7/14/2004 11/16/2000 2/4/2000 12/6/1999 10/5/19994981 3/7/2003 7/28/1999 12/4/1998 9/15/1998 7/16/199868666 3/21/2003 2/15/2000 4/15/1999 2/17/1999 12/7/19985451 8/12/2003 10/17/2000 3/26/1999 2/18/1998 12/11/199762333 2/24/2004 6/19/2000 7/27/1999 4/30/1999 2/26/19995460 8/12/2003 7/27/2000 1/4/2000 4/4/1998

Conversion to Date Posted: 09 Apr 2013 12:36 AM PDT Hi everyoneI have a field in one of my tables called FinancialMonth. It's a varchar field and it shows the financial month and year and displays them like 201210 ie January 2013 (month 10 of financial year 2012 = Jan 13). The first month in the new financial year would be 201301. Essentially I'd like to convert this field back to a valid SQL date. The day of the month can be the first day of the month. Thanks in advance. BO

Optimize for adhoc workloads Posted: 08 Apr 2013 09:20 PM PDT Can anyone please tell me where I can find the optimize for adhoc workloads settings in a table / view because I want to programmatically test for the setting?thanks

Help with tuning stored proc Posted: 08 Apr 2013 08:23 PM PDT Hi allI have a proc here where the estimated amount of rows is around 2 trillion where the actual numbers of rows is 300. I have updated statistics on the relevant tables to no avail, there seems to be a hash match at the time when the rows go miles out of synch. Does anyone have nay suggestions? Currently there are no indexes on the temp tables perhaps that would help I am unsure?I have attached the plan and the code is below.Thanks for any input[code="sql"]DECLARE@paramCompany varchar(3),@paramStartDate datetime,@paramEndDate datetime,@paramSalesOffice varchar(2000),@paramSalesResponsible varchar(max),@paramQwerty varchar(2)SET @paramCompany = @CompanySET @paramStartDate = @StartDateSET @paramEndDate = @EndDateSET @paramSalesOffice = @SalesOfficeSET @paramSalesResponsible = @SalesResponsibleSET @paramQwerty = @QwertySELECT svcitem.DESPATCHPOINT, svcitem.DATAAREAID, svcitem.ITEMIDINTO #d1FROM [BIDATAWSQL].DynamicsV5Realtime.dbo.MARDESPATCHSERVICEITEMS svcitemINNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.marreportenums enumON svcitem.MARSALESCHARGEITEMTYPE = enum.ENUMVALUEINTWHERE enum.ENUMNAME = 'MARSalesChargeItemType' and enum.ENUMVALUESTR = 'Excess Haulage' and svcitem.DATAAREAID = @paramCompany-- to get Original Sales Orders that have had an RTW order create within the date parametersSELECT DISTINCT st.MARIMSSALESID, st.DATAAREAIDINTO #d2FROM [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESTABLE st --- RTW OrderWHERE st.DATAAREAID = @paramCompany and st.SALESTYPE = 4 --Returned Order and st.CUSTACCOUNT not like('K' + '%') and st.CUSTACCOUNT not like('E' + '%') and (CAST(FLOOR(CAST(st.CREATEDDATETIME AS FLOAT))AS DATETIME) between @paramStartDate and @paramEndDate)SELECT st.MARIMSSALESID as 'OriginalOrder', st2.MARTRANSPORTGROUP as 'DespatchPoint', st2.MARVEHICLETYPE as 'OriginalOrderVehicleType', origwt.OriginalOrderWeight, isnull(servchrg.OriginalServiceCharges,0) as 'OriginalServiceCharges', CASE WHEN origwt.OriginalOrderWeight = ABS(SUM((sl.QTYORDERED * it.NETWEIGHT) / 1000)) THEN isnull(servchrg.OriginalServiceCharges,0) ELSE 0 END as 'ReturnableServiceCharges', st.SALESID as 'RTWOrder', st.MARSALESOFFICE as 'RTWSalesOffice', st.MARVEHICLETYPE as 'RTWOrderVehicleType', pm.name as 'RTWSalesResponsible', st.CREATEDDATETIME as 'RTWOrderCreated', st.DELIVERYCITY + ' - ' + st.DELIVERYZIPCODE as 'SiteTown', dbo.udf_get_PricingArea(st.DELIVERYZIPCODE,#d1.ITEMID) as 'PricingArea', st.MARTRANSPORTGROUP as 'Returnto', ABS(SUM((sl.QTYORDERED * it.NETWEIGHT) / 1000)) as 'RTWWeight', -- in tons ho.SALESID as 'HaulageOrder', sl1.ITEMID as 'Item', sl1.SALESQTY as 'Qty', sl1.LINEAMOUNT as 'HaulageCharged', CASE WHEN st.MARVEHICLETYPE = '0' or st2.MARVEHICLETYPE = '0' THEN 1 ELSE 2 END as 'haulcalcfactor', coalesce(dbo.udf_get_YardPrice(st.DELIVERYZIPCODE,#d1.ITEMID), dbo.udf_get_SitePrice(st.DELIVERYZIPCODE,#d1.ITEMID)) as 'ListPrice', coalesce(dbo.udf_get_YardPrice(st.DELIVERYZIPCODE,#d1.ITEMID), dbo.udf_get_SitePrice(st.DELIVERYZIPCODE,#d1.ITEMID)) * ABS(SUM((sl.QTYORDERED * it.NETWEIGHT) / 1000)) * (CASE WHEN st.MARVEHICLETYPE = '0' or st2.MARVEHICLETYPE = '0' THEN 1 ELSE 2 END) as 'ExpectedHaulage', --ListPrice * RTWWeight * 2 or 1 now - see CASE statement #d1.ITEMID as 'ExcessHaulageItem', pm.emplid as 'EmplId', IsNull(pm.email,'gill.wilton.@marshalls.co.uk') as 'SalesResponsibleEmail'INTO #d3FROM [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESTABLE st --- RTW OrderLEFT OUTER JOIN udf_R000_EmployeeList() pmON st.SALESRESPONSIBLE = pm.emplidINNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESLINE slON sl.SALESID = st.SALESID and sl.DATAAREAID = st.DATAAREAID INNER JOIN #d2ON #d2.MARIMSSALESID = st.MARIMSSALESID and #d2.DATAAREAID = st.DATAAREAIDINNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.INVENTTABLE itON it.ITEMID = sl.ITEMID and it.DATAAREAID = sl.DATAAREAID LEFT OUTER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.INVENTDIM idON id.INVENTDIMID = sl.INVENTDIMID and id.DATAAREAID = sl.DATAAREAID INNER JOIN (SELECT st.SALESID, st.MARIMSSALESID, st.DATAAREAID, st.CREATEDDATETIME FROM [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESTABLE st INNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESLINE sl ON sl.SALESID = st.SALESID and sl.DATAAREAID = st.DATAAREAID WHERE st.DATAAREAID = @paramCompany and st.CUSTACCOUNT not like('K' + '%') and st.CUSTACCOUNT not like('E' + '%') and sl.ITEMID = 'JZEX111RTW0' ) ho -- Haulage OrderON ho.MARIMSSALESID = st.MARIMSSALESID and ho.DATAAREAID = st.DATAAREAID and ho.SALESID <> st.SALESIDINNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESLINE sl1ON sl1.SALESID = ho.SALESID and sl1.DATAAREAID = ho.DATAAREAID and sl1.ITEMID = 'JZEX111RTW0'INNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESTABLE st1ON st1.SALESID = sl1.SALESID and st1.DATAAREAID = sl1.DATAAREAIDLEFT OUTER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESTABLE st2ON st2.SALESID = st.MARIMSSALESID and st2.DATAAREAID = st.DATAAREAID-- to get total value of original order service chargesLEFT OUTER JOIN (SELECT sl.SALESID, sl.DATAAREAID, SUM(sl.SALESQTY * sl.SALESPRICE) as 'OriginalServiceCharges' FROM [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESLINE sl WHERE sl.ITEMID like ('JZEX' + '%') GROUP BY sl.SALESID, sl.DATAAREAID ) servchrg ON servchrg.SALESID = st2.SALESID and servchrg.DATAAREAID = st2.DATAAREAID-- to get net weight of original order items sentLEFT OUTER JOIN (SELECT sl.SALESID, sl.DATAAREAID, ABS(SUM(sl.SALESQTY * it.NETWEIGHT / 1000)) as 'OriginalOrderWeight' FROM [BIDATAWSQL].DynamicsV5Realtime.dbo.SALESLINE sl INNER JOIN [BIDATAWSQL].DynamicsV5Realtime.dbo.INVENTTABLE it ON it.ITEMID = sl.ITEMID and it.DATAAREAID = sl.DATAAREAID WHERE sl.ITEMID not like ('JZEX' + '%') GROUP BY sl.SALESID, sl.DATAAREAID ) origwt ON origwt.SALESID = st2.SALESID and origwt.DATAAREAID = st2.DATAAREAID LEFT OUTER JOIN #d1ON #d1.DESPATCHPOINT = st2.MARTRANSPORTGROUP and #d1.DATAAREAID = st2.DATAAREAIDWHERE st.DATAAREAID = @paramCompany and st.SALESTYPE = 4 --Returned Order and st.CUSTACCOUNT not like('K' + '%') and st.CUSTACCOUNT not like('E' + '%') and st1.MARVEHICLETYPE = '100' --DUMMY T.M.G. (ADMINISTRATION) --and st1.MARFOEORDERTYPE = 0 -- commented out for Gill to test and advise ??? and (st.MARSALESOFFICE in(select * from udf_MultiValueParameterHandlingString(@paramSalesOffice)) or @paramSalesOffice = 'All') and (pm.emplid IN (select * from udf_MultiValueParameterHandlingString(@paramSalesResponsible)) or @paramSalesResponsible = 'All')GROUP BY st.SALESID, st.MARIMSSALESID, st2.MARTRANSPORTGROUP, st.DELIVERYCITY + ' - ' + st.DELIVERYZIPCODE, st.MARTRANSPORTGROUP, ho.SALESID, sl1.ITEMID, sl1.SALESQTY, sl1.LINEAMOUNT, st.DELIVERYZIPCODE, #d1.ITEMID, st.CREATEDDATETIME, servchrg.OriginalServiceCharges, origwt.OriginalOrderWeight, st.MARVEHICLETYPE , st2.MARVEHICLETYPE, pm.name, st.MARSALESOFFICE, pm.emplid, IsNull(pm.email,'gill.wilton.@marshalls.co.uk') SELECT #d3.OriginalOrder, #d3.DespatchPoint, #d3.OriginalOrderVehicleType, #d3.OriginalOrderWeight, #d3.OriginalServiceCharges, #d3.ReturnableServiceCharges, #d3.RTWOrder, #d3.RTWSalesOffice, #d3.RTWOrderVehicleType, #d3.RTWSalesResponsible, #d3.RTWOrderCreated, #d3.SiteTown, #d3.PricingArea, #d3.Returnto, #d3.RTWWeight, #d3.HaulageOrder, #d3.Item, #d3.Qty, #d3.HaulageCharged, #d3.ListPrice, #d3.ExpectedHaulage, #d3.ExcessHaulageItem, #d3.HaulageCharged - #d3.ExpectedHaulage as 'OldValDiff', (#d3.HaulageCharged - #d3.ExpectedHaulage) / (#d3.ExpectedHaulage) * 100 as 'OldPercentDiff', #d3.HaulageCharged - #d3.ExpectedHaulage - #d3.ReturnableServiceCharges as 'ValDiff', (#d3.HaulageCharged - #d3.ExpectedHaulage - #d3.ReturnableServiceCharges) / (#d3.ExpectedHaulage + #d3.ReturnableServiceCharges) * 100 as 'PercentDiff', #d3.haulcalcfactor, #d3.EmplId, #d3.SalesResponsibleEmail INTO #d4 FROM #d3WHERE (#d3.HaulageCharged - #d3.ExpectedHaulage - #d3.ReturnableServiceCharges) / (#d3.ExpectedHaulage + #d3.ReturnableServiceCharges) * 100 <= -1 --< -0.05IF @paramQwerty = 'D' SELECT * FROM #d4IF @paramQwerty = 'S' SELECT DISTINCT #d4.EmplId, #d4.SalesResponsibleEmail FROM #d4ORDER BY 1DROP TABLE #d1DROP TABLE #d2DROP TABLE #d3DROP TABLE #d4GO[/code]

Query Timeout help Posted: 08 Apr 2013 08:19 PM PDT Hi,Could anyone tell me why the following query is timing out please?[code="other"]SELECT PLAN_HANDLE FROM SYS.DM_EXEC_PROCEDURE_STATS PS WHERE PS.PLAN_HANDLE = 0x05000A00ED4FE245402231CC000000000000000000000000[/code]If there is a better way to check if a specific plan exists in cache, please could you let me know.Thanks

Using AOG spanning FCI. Posted: 03 Apr 2013 12:06 AM PDT I have read that all disk sizes and drive letters need to be the same when using AOG.Is this just for the Drives which hold the MDF and LDF files for dbs in the group.Not much on the net about this but I remember someone stating this last year some time.

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