Wednesday, April 10, 2013

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

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

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.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog