InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:41:52 GMT20http://www.sqlservercentral.com/Forums/Topic1453019-3077-1.aspxPerhaps I am confusing this formation with calls to a stored procedure.Isn't it possible to call an insert thus?[code="sql"]insert into TableAColA = @ValueA,ColB = 'some string'[/code]Documentation doesn't seem to support it (should be my first clue) but I have mis-read or been mis-lead by documentation before.Wed, 15 May 2013 05:18:06 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic1456073-3077-1.aspxMy system has a number of history tables that would give the date that a value has changed. I found a very close analogue to this in Adventure works looking up products and seeing what the most recent value was as of a certain date.I can think of two ways to do this:[code="sql"]declare @ReportingDate DATE = '9/7/2004'-- Method 1SELECT Name ,ReportDate = @ReportingDate ,MostRecentOrderDetailId = (SELECT TOP 1 SalesOrderDetailId FROM sales.SalesOrderDetail SalesOrderDetail WHERE SalesOrderDetail.ProductID = Product.ProductID AND SalesOrderDetail.ModifiedDate <= @ReportingDate ORDER BY ModifiedDate DESC)FROM Production.Product Product-- Method 2SELECT Name ,ReportDate = @ReportingDate ,MostRecent.MostRecentOrderDetailIdFROM Production.Product ProductOUTER APPLY (SELECT TOP 1 MostRecentOrderDetailId = SalesOrderDetailId FROM sales.SalesOrderDetail SalesOrderDetail WHERE SalesOrderDetail.ProductID = Product.ProductID AND SalesOrderDetail.ModifiedDate <= @ReportingDate ORDER BY ModifiedDate DESC) MostRecent[/code]Both produce a near identical query plan. Method 2 would be a clear winner of the two if I wanted to retrieve more than a single value from the detail table (say price and quantity).Is there a better way to do this overall without refactoring how the data is stored?Best performance I can think of would be to have a daily reporting table that precalculates the requested values cross applied with a dates table to give the value for every date. Then it would be a simple join rather than a subquery/cross apply. Even using a reporting table (DailySalesOrderDetail), these queries need to be optimized because they'll be needed to recalculate the the reporting whenever the SalesOrderDetail table changes.[code="sql"]IF OBJECT_ID('tempdb..#DailyOrderDetail') IS NOT NULL DROP TABLE #DailyOrderDetailSELECT DateFull, Name, MostRecentOrderDetailIdINTO #DailyOrderDetailFROM edimain.dbo.DateLookupCROSS APPLY (SELECT Name ,MostRecentOrderDetailId = (SELECT TOP 1 SalesOrderDetailId FROM sales.SalesOrderDetail SalesOrderDetail WHERE SalesOrderDetail.ProductID = Product.ProductID AND SalesOrderDetail.ModifiedDate <= DateFull ORDER BY ModifiedDate DESC)FROM Production.Product Product) MostRecentWHERE DateFull BETWEEN '1/1/2002' and '1/1/2005'CREATE NONCLUSTERED INDEX IX_DailyOrderDetail_DateFullON #DailyOrderDetail ([DateFull])INCLUDE ([Name],[MostRecentOrderDetailId])declare @ReportingDate DATE = '9/7/2004'-- Method 3SELECT d.Name ,ReportDate = @ReportingDate ,MostRecentOrderDetailId = d.MostRecentOrderDetailIdFROM Production.Product ProductINNER JOIN #DailyOrderDetail D on D.DateFull = @ReportingDate AND D.Name = Product.Name[/code]Given the reporting table already existing, Method 3 (just a join to a denormalized reporting table) doesn't even register as a single percentage point in comparison to Method 1 and Method 2.So if I could get Method 1 or Method 2 optimized further, that would be an interesting exercise. My application uses it quite a bit an it would be useful for optimizing a reporting table to use Method 3 as well. Is there a term for what sort of query this is?Thu, 23 May 2013 10:00:49 GMTShawnTherrienhttp://www.sqlservercentral.com/Forums/Topic1456387-3077-1.aspxWhat I am trying to do is return a value that is not changed from the row of the table that was changed. I am trying to replace a select @v = col clause. I think this looks like the example in the BOL. Any idea why it reports that it reports f.a can not be bound?[code="sql"]create table foo(a int, b varchar(10), c int)create table bar(z int, y int)insert into foo (a, b, c) select 7, 'Test', 11insert into bar select 3, 21insert into bar select 10, 24insert into bar select 7, 11declare @foo2 table (avalue int)update fooset b = 'this is'OUTPUT f.a into @foo2from foo finner join bar bon f.c = b.yselect avalue from @foo2[/code]Fri, 24 May 2013 05:05:17 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic1454321-3077-1.aspxHere is my case, i have a PRODUCTS table[quote]create table PRODUCTS (ID_PRODUCTS CHAR(10) primary key not null,NAME CHAR(30),PRICE INTEGER)[/quote]Then I fill it with some data,...[quote]insert into PRODUCTS values('B1','Samsung Galaxy Ace 2',250)insert into PRODUCTS values('B2','Samsung Galaxy Tab 3',375)insert into PRODUCTS values('B3','Samsung Galaxy Note 2',700)insert into PRODUCTS values('B4','Apple iPod Touch',200)insert into PRODUCTS values('B5','Apple Macbook Pro',1250)[/quote]Then i wanna create a stored function to search for data based a keyword on NAME column in PRODUCTS table. For example, when i execute that function with a "Samsung" keyword, it will be show a list which contains word "Samsung" inside it. I hope the list will be like this the apperance[quote]========================================ID_PRODUCTS | NAME | PRICE========================================B1 | Samsung Galaxy Ace 2 | 250----------------------------------------B2 | Samsung Galaxy Tab 3 | 375----------------------------------------B3 | Samsung Galaxy Note 2 | 700========================================[/quote]Here is the code, but it show nothing when execute it (select*from dbo.products_fun)[quote]create function product_fun(@name char(30))returns TABLEasreturn (select * from products where name like '%@name%')[/quote]I think it show nothing because the query[quote]select * from products where name like '%@name%'[/quote]It is not a search for keywords inside the variable @name, .. but the search for the keyword "@name",... that's why it show nothing when execute it. Anyone wanna help ???Sun, 19 May 2013 04:56:37 GMTbuyungafriantohttp://www.sqlservercentral.com/Forums/Topic1310096-3077-1.aspxHi Guys,I am planing to do [b]MCSA – SQL server 2012[/b] certification, which has the following exams.a. Exam 70-461:Querying Microsoft SQL Server 2012b. Exam 70-462:Administering Microsoft SQL Server 2012 Databasesc. Exam 70-463:Implementing a Data Warehouse with Microsoft SQL Server 20121. I don't see the MS Press books available. If you are taking the exams, please let me know how are you studying for it?I don't have any previous SQL server certifications. 2. Is there a copy of MS Sql server 2012 I can download to study? My work laptop has a SQL server 2008 which I cannot install. Is there a way to install SQL server 2012 in parrallel without effecting the SQL server 2008 instance?3. Any other advice for a SQL server certification newbie to start?Thanks for your time.. RMSat, 02 Jun 2012 08:45:15 GMTRainmaker097http://www.sqlservercentral.com/Forums/Topic1451213-3077-1.aspxHello,I'm hoping someone can help me with a SQL query I'm trying to write; I'm relatively new to SQL so please bear with me if this is a numpty question. I'm using SQL 2012 Developer Edition.I have a database of address data (for those of you who work in the UK public sector you may be familiar with it as it's from the LLPG) in which there are three fields that can describe the house number or name as follows:House_No_Start: where a property only has one house number it is stored in this field, e.g. the '1' of the address 1 High Street.House_No_End: where a property has more that one house number the secondary value is stored in this field, e.g. the '3' of the address 1 - 3 High Street.House_Name: where a property has a name instead of a house number the value is stored in this field, e.g. 'The manor' of the address The Manor, High Street.The street name, town, post code, etc. are all stored in other fields across other tables. I can successfully use a select statement and joins to pull together all the elements of the address, but I'm stuck when it comes to selecting only the required fields from the three listed above.What I want to do is write some kind of IF (or should that be CASE?) statement as part of my wider select statement that pulls together the full address. I want the IF statement to do the following:Where only the House_No_Start field is populated I want to use the House_No_Start in the address.Where both the House_No_Start and House_No_End fields are populated I want to concatenate the two values with ' - ' between the two values.Where only the House_Name field is populated I want to use the House_Name value in the address.Can this be done? I hope that all makes sense.StuartThu, 09 May 2013 09:46:07 GMTstuart.hemminghttp://www.sqlservercentral.com/Forums/Topic1386328-3077-1.aspxWe have a new server with SQL Server 2012. Database mail has been configured and appears to be working. I am seeing some strange behaviour though and I don't know if it is an issue with SQL Server, Exchange or the infrastructure. This is what I am seeing:1. Create a cursor to send myself 10 emails.[code="sql"]DECLARE @i INT = 1;WHILE @i <= 10BEGIN EXEC msdb..sp_send_dbmail @profile_name = 'my_profile', @recipients = 'me@mydomain.co.za', @subject = 'Test', @body = 'Testing Mail'; SET @i = @i + 1;END;[/code]2. Monitor the msdb mail tables[code="sql"]SELECT 'Unsent', COUNT(*)FROM msdb..sysmail_unsentitemsWHERE sent_date > '2012-11-19 14:15'UNION ALLSELECT 'Failed', COUNT(*)FROM msdb..sysmail_faileditemsWHERE sent_date > '2012-11-19 14:15'UNION ALLSELECT 'Sent', COUNT(*)FROM msdb..sysmail_sentitemsWHERE sent_date > '2012-11-19 14:15'UNION ALLSELECT 'All', COUNT(*)FROM msdb..sysmail_allitemsWHERE sent_date > '2012-11-19 14:15'[/code]For about 1 minute after I have run my code I see NOTHING in these tables.Unsent 0Failed 0Sent 0All 0Then I have 10 entries in the unsent table. They all have a sent_status of retry.Unsent 10Failed 0Sent 0All 10After a few more minutes I see they have all failed.Unsent 0Failed 10Sent 0All 10However, I have 20 emails sitting in my inbox. So somehow SQL Server sends me two copies of each email then reports them as failed.I also have 20 errors in the Database Mail Log, all of which say:The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2012-11-19T14:36:58). Exception Message: Cannot send mails to mail server. (The operation has timed out.).And I also have the following error in the SQL Server Log:An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.InternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.Any ideas?Mon, 19 Nov 2012 05:52:36 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1454433-3077-1.aspxhell everyone , im back i have a problems with partitions of my fact tables everything is ok ( building , deployement ) but when i want process my cube i have a probleme with partitions of my facts tablesany suggestions please ?Mon, 20 May 2013 02:21:15 GMTEl_Mouhttp://www.sqlservercentral.com/Forums/Topic1453034-3077-1.aspxBelow are the IO Statistics for a query before and after optimization.IO Stats are different but actual execution time on production is same for both the queries.Now I want to go with optimized query but my manager want to revert the changes as there is no time gain.[b]What will be your choice in this case!!![/b] [b]QUERY 1 I/O STATISTICS[/b] after Optimization(20998 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'company'. Scan count 1, logical reads 1027, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'person'. Scan count 1, logical reads 646153, physical reads 266, read-ahead reads 430590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'worker'. Scan count 1, logical reads 24215, physical reads 0, read-ahead reads 50, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'buyer_supplier_contract'. Scan count 1, logical reads 140, physical reads 0, read-ahead reads 47, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[b]QUERY 2 I/O STATISTICS before Optimization[/b](20998 row(s) affected)Table 'buyer_supplier_contract'. Scan count 20998, logical reads 44642, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'company'. Scan count 1, logical reads 1026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'person'. Scan count 3, logical reads 1338694, physical reads 399, read-ahead reads 450197, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'worker'. Scan count 1, logical reads 24215, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Wed, 15 May 2013 06:01:57 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1453923-3077-1.aspxDear All, I have a query which was using CROSS JOIN (with relations) earlier and now we modified it with INNER JOIN. As per Prod server report, CROSS JOIN was performing faster but as per my theoretical knowledge, INNER JOIN should perform faster. I have attached Queries, IO Stats and Execution plan for your reference. Any guidance will be highly appreciated.Fri, 17 May 2013 04:33:39 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1453739-3077-1.aspxHas nolock been depricated in SQL Server 2012, or does the optimizer ignore the no lock and still run?Thu, 16 May 2013 12:45:55 GMTgchappellhttp://www.sqlservercentral.com/Forums/Topic1453633-3077-1.aspxHi,I have a table in SQL Server 2012 in which data is imported daily. SQL Server 2012 is connected to QuickBase (online web database application) and what i am trying to achieve is: I want data (which is imported) in SQL server to be updated in Quickbase daily as well. I created a query where data from SQL insert into Quickbase table and then created a job using SQL server Agent to run daily once but its not working for me.Can someone suggest what and how to go about it?Thanks,skmThu, 16 May 2013 09:44:15 GMTm.sakethkumarhttp://www.sqlservercentral.com/Forums/Topic1452529-3077-1.aspxHello!I have one stored proc which returns data by querying multiple tables using union clause without any search criteria.select xyz as Col1 from table1 join table2 ...(total 8 joins)unionselect abc as Col1 from table1 join table3...(total 8 joins)unionselect pqras Col1 from table1 join table4...(total 8 joins)unionselect pqras Col1 from table1 join table5...(total 8 joins)As per database design Table1 represent supertype and Table3,4,5 represent sub types. Table1 has 1146090 rows. Not sure what to do with this. It showed up as leading in average reads at 14 million per execution and runs for 35 seconds. It is going to get a lot worse looking at the tables it's selecting from. It is basically doing a select across lots of tables, some large tables, without a select criteria. It's doing a bunch of in clustered index scans... appreciate your help here.Tue, 14 May 2013 06:14:58 GMTAbhijit Morehttp://www.sqlservercentral.com/Forums/Topic1452688-3077-1.aspxHey Everyone,I have a set of IF THEN logic that create additional fields. Does anyone have a great grasp of how to convert the attached examples from IF THEN to CASE THEN statements. Any help would be appreciated.Example 1:IIF(fmwa.GroupNbr<>'','NO', IIF(dind.DefaultIndicator='Y', 'YES', 'NO')) AS Default_IDExample 2:IIF(tagagt.AgencyName='',(RTrim(tagagt.LastName)) + N', ' + LTrim(RTrim(tagagt.FirstName)), LTrim(RTrim(tagagt.AgencyName))) AS WA_NameTue, 14 May 2013 09:47:27 GMTjjgierhttp://www.sqlservercentral.com/Forums/Topic1452369-3077-1.aspxIs there a way to track a truncate statement without a trace. Can there be a trigger set up or something. We have DDL events being monitored but we have been researching this specific task on truncate tableThanks in advanceMon, 13 May 2013 17:47:35 GMTD-SQLhttp://www.sqlservercentral.com/Forums/Topic1450589-3077-1.aspxHello its my first topic hope i'll get a solution for my troublesi have some stored procedure and every things its okay when i execute my SP except them who contains conversions from varchar data type to datetime i try a lot of things like change CONVERT to CAST , DATETIME to DATETIME2 , i add also a style 9 ( because im working with sql server 2012 )i have 2 errors msg 1 - Conversion failed when converting date and / or time from character string ( this case its a SP with a simple conversion like CONVERT (datetime,column_name ) as column_output )2 -The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.( in this case i create time dimension with a complex conversions but they dont generate data )and sorry for my bad english and my low technical knowledge im a newbie in the world of databaseWed, 08 May 2013 08:09:27 GMTEl_Mouhttp://www.sqlservercentral.com/Forums/Topic1450701-3077-1.aspxOther than breaking a date string into its parts and multiplying them by the appropriate number of seconds is there a, simple, way to display a date as the number of seconds since 01/01/1900? I thought I was on to something but ...[code="sql"]declare @d datetimeset @d = '01/01/1900 00:00:00'select @dselect convert(int, @d)-----------------------1900-01-01 00:00:00.000(1 row(s) affected)-----------0(1 row(s) affected)declare @d datetimeset @d = '01/02/1900 00:00:00'select @dselect convert(int, @d)-----------------------1900-01-02 00:00:00.000(1 row(s) affected)-----------1(1 row(s) affected)[/code] seems like it is thinking in days. Do this and then calculating seems tedious, if not error prone.Wed, 08 May 2013 11:06:22 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic1451089-3077-1.aspxI'm almost finished with the "2012 T-SQL Fundamentals" book and while I understand both derived tables and CTEs, I'm having trouble to understand when to use which query? In fact, I think the most common problem for those starting a new career in SQL is when to use which type of search query? There are millions and each problem could have 20 different types of queries.Thu, 09 May 2013 06:55:28 GMTdob111283http://www.sqlservercentral.com/Forums/Topic1449269-3077-1.aspxHi,I need an urgent help to get the data from previous record. I am able to calculate Cumulative data but data is not getting populated for missing months .Below is the table dataDate, Prj_ID, Amount Jan12, 1, 1000Feb12, 1, 1500Apr12, 1, 1800July12, 1, 1200Jan12, 2, 500Apr12, 2, 1000The output i want isDate, Prj_ID, Amount Jan12, 1, 1000Feb12, 1, 1500Mar12, 1, 1500Apr12, 1 , 1800May12, 1, 1800Jun12 , 1 , 1800July12, 1 , 1200Au12 , 1 , 1200Sep12, 1 , 1200Jan12, 2, 500Feb12, 2, 500Mar12, 2, 500Apr12, 2, 1000May12, 2, 1000Jun12, 2, 1000July12, 2, 1000Au12, 2 , 1000Sep12, 2, 1000ThanksFri, 03 May 2013 09:41:52 GMTMotzhttp://www.sqlservercentral.com/Forums/Topic1448796-3077-1.aspxOut of the two given queries, which will perform faster!!![b]1. Query(1)[/b]SELECT TOP 1000 first_name + ' ' + last_name AS display_name, person_id, company_codeFROM person[b]2. Query(2)[/b]SELECT TOP 1000 CASE WHEN charindex('(', first_name + ' ' + last_name) > 0 THEN (LEFT (first_name + ' ' + last_name, CHARINDEX('(',first_name + ' ' + last_name) -1)) ELSE first_name + ' ' + last_name END AS display_name, person_id, company_codeFROM personThu, 02 May 2013 07:02:55 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1448868-3077-1.aspxGreetings,I have a database in SQL 2000 that I need to migrate to SQL 2012.I detached from the SQL 2000 server using sp_detach_db.I copied the MDF and the LDF files to a SQL 2005 machine and used sp_attached_db MyDatabase,Filename1='E:\path to the file.mdf',Filename2='E:\path to the file.ldf'This worked fine.I detached the database from the SQL 2005 server and attached it to a SQL 2008 server without any problems.I detached the databse from SQL 2008 and copied the files to a SQL 2012 server and tried to attach the database.Use MasterGOCreate Database MyDatabaseON(Filename='E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\Data\MyDatabase.mdf'),(Filename='E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\Data\MyDatabase.ldf')For Attach;I received this errorMsg 5120 Level 16, State 5, Line 1Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\ftrow_CompanyKeyword.ndf". Operating System error 2: "2(the system cannot file the file specified.)". CompanyKeyword is a full text catalog and there are 7 catalogs in this database.So my question is how do I attach this database and include the catalogs or exclude the catalogs.I will need to rebuild the catalogs one the database is attached anyway.Thanks for the help.GaryThu, 02 May 2013 09:44:00 GMTGFhttp://www.sqlservercentral.com/Forums/Topic1448779-3077-1.aspxAnyone have an idea why the varchar data type is limited to be 1 through 8000 or MAX(2GB) but none of the numbers between 8001 and 2GB?Just one of those little gremlins of "Huh" that vex me.Hope you all have a great day. Dodged 5-13 inches of snow myself, so mine already has a good start. :-)Thu, 02 May 2013 06:20:31 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic1448590-3077-1.aspxI just upgraded from SQL Server 2005 to SQL Server 2012 and had a procedure that dynamically builds html and uses xp_smtp_sendmail to send the email. One of the parameters for this procedure is @dumpmsg. This parameter allows you to save the html file to the file system. In SQL Server 2012, sp_send_dbmail does not have a parameter to save the html file to the file system. In 2012, how can I build and html file and save it to the file system? Can this be done in TSQL or do I need to use SSIS?Wed, 01 May 2013 13:29:57 GMTKingfishhttp://www.sqlservercentral.com/Forums/Topic1442750-3077-1.aspxFirst off, my appologies, I am a newbie to programming and SQL.Setup: I have an SSIS package that inserts rows to a database. On occasion I get a constraint error on one of my reference tables. So far it is because of missing data.What I want to do: I want to redirect the row, grab the data and insert it into the reference table then reprocess the row. Questions: A) is this possible? I have looked at doing a lookup task prior to the row being inserted to the database. I have also looked at using a sql task in my error handling, but having issues with the code (see B). B) I need help with the code. How do I pull the information from that single row then insert it into the database? Do I use a variable? Is this a select statement? Any help would be greatly appreciated.Thank youBrianTue, 16 Apr 2013 07:58:25 GMTbrian.geregachhttp://www.sqlservercentral.com/Forums/Topic1448225-3077-1.aspxI’ve experimented with several variations but now I’m ready to call in the troops - I would like to modify this statement:SELECT P.SalesPersonID , P.TerritoryID , S.Name "Sales/Name" , S.ModifiedDate "Sales/ModifiedDate"FROM Sales.Store AS S INNER JOIN Sales.SalesPerson AS P ON S.SalesPersonID = P.SalesPersonIDWHERE (S.SalesPersonID = 275)FOR XML PATH To output like this:<row> <SalesPersonID>275</SalesPersonID> <TerritoryID>2</TerritoryID> <Sales><Sale> <Name>Trusted Catalog Store</Name> <ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate></Sale><Sale> <Name>Catalog Store</Name> <ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate></Sale> </Sales></row>Rather than this:<row> <SalesPersonID>275</SalesPersonID> <TerritoryID>2</TerritoryID> <Sales> <Name>Trusted Catalog Store</Name> <ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate> </Sales></row><row> <SalesPersonID>275</SalesPersonID> <TerritoryID>2</TerritoryID> <Sales> <Name>Catalog Store</Name> <ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate> </Sales></row>Tue, 30 Apr 2013 13:55:05 GMTd_georgehttp://www.sqlservercentral.com/Forums/Topic1448244-3077-1.aspxIt might be my density :-), but looking at examples on the net I can not find a straight forward way to select a column of XML and have it displayed in a formatted manner. Seems like there would be, but then ...Anyone send me in the right direction?Tue, 30 Apr 2013 14:51:00 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic1448036-3077-1.aspxHere's my SQL sequence: [code="sql"]SELECT M.BoMWOID, CAST(SUM(M.BoMEstimatedMaterialCost) AS money) AS BoMTotMaterialEstimate, CAST(SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty) AS money) AS BoMTotLaborHrsEstimate, SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty * DI.BudgetHourlyRate) AS BoMTotLaborCostEstimate, CAST(SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty * DI.BudgetHourlyRate + M.BoMEstimatedMaterialCost) AS money) AS BoMTotCostEstimateFROM dbo.tblBoM AS M INNER JOIN dbo.tblBoMDetail AS D ON M.BoMID = D.BoMID INNER JOIN dbo.tblWorkOrders AS WO ON M.BoMWOID = WO.ID INNER JOIN dbo.tblDiscipline AS DI ON WO.WODiscipline = DI.IDGROUP BY M.BoMWOID[/code]It derives four budget summaries from Bills of Material. Three are no issue, but BoMTotMaterialsEstimate is not working right (my fault...) It should be the sum of M.BoMEstimatedMaterialCost, but it's being multiplied as if it were a detail field. I can't get it straight in my mind how to do this (other than a sub-query.)"WO" is the Work Order Master. There may be zero or more "M" children. "M" is the BoM Master, and it contains a Materials Estimate for the entire BoM. The "D" table is the BoM Detail child of the "M" table. The hours and labor estimates are calculated and summed up from each "D" row. So, while three of the totals are summed over the lowest child domain, one (BoMTotMaterialsEstimate) is summed over the BoM Master ("M") domain. What's the best strategy for this?Tue, 30 Apr 2013 07:47:54 GMTJimS-Indyhttp://www.sqlservercentral.com/Forums/Topic1447575-3077-1.aspxHi all, When i try to add the '+@pDatabase+'.dbo. with the tablename in line 98,99,100 AM getting error like Msg 103,Msg 102,Msg 105 With out that Query running fine PLZ help me out on this. Query in Attachment.Regards,Basee Mon, 29 Apr 2013 08:55:45 GMTbaseehkhanhttp://www.sqlservercentral.com/Forums/Topic1446830-3077-1.aspxHi all,Here are two very small queries in which only difference is the use of DISTINCT. Both the queries are returning same rows and have identical IO Statistics and execution plan.So, technically as there there is no performance gain we can use either of them. But I still want to use 2nd query as it is not having DISTINCT clause.Can you share your experience on this.[b]1.[/b]SELECT top 100000 *FROM account_siteWHERE account_id IN ( SELECT DISTINCT account_id FROM account )[b]2.[/b]SELECT top 100000 *FROM account_siteWHERE account_id IN ( SELECT account_id FROM account )Execution plan attached.Fri, 26 Apr 2013 01:40:23 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1446428-3077-1.aspxHi all,I optimized a query by replacing table valued function with LEFT JOIN, and managed to reduce the Scan Count and Logical Reads from 1500,12000 to 25,150.But Query is still taking same time for execution. What could be the possible reason!!!Thu, 25 Apr 2013 05:32:51 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxI have a challenge that I was hoping to solve using the new SQL Server 2012 running balance capabilities, but it's a bit more complicated than I realized. I've attached a small spreadsheet with some sample data.Basically these are inventory transactions for multiple LotIDs that are all part of a single PudID group and I need to be able to calculate the running balance for the PudID, which is the PudIDAsOfBal column in the spreadsheet. I've included some comments on how that number is calculated. I added the "LotIDBalSeq" column thinking I could somehow sum up the last PudIDLotBal for each LotID as of the TranDate. The following code will return the results I have to work with. What I need is one more column with the PudIDAsOfBal:[code="sql"]SELECT PudID, TranDate, TranQty, RunBal, PudIDPct, LotID, PudIDLotBal, LotIDBalSeq, 'help?' PudIDAsOfBalFROM( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2 UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3 UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4 UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1 UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1 UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3 UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4 UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1) WORK[/code]I thought I could use some variation of SUM(PudIDLotBal) OVER (PARTITION... but having the multiple lots and needing the last balance for each lot as of the TranDate is over my head. Any help woud be greatly appreciated.Thanks,GinaMon, 15 Apr 2013 19:46:06 GMTgcressehttp://www.sqlservercentral.com/Forums/Topic1444673-3077-1.aspxI've got a 350 million row partitioned table that I'm trying to aggregate. The plan I get uses a HASH Aggregate. I would like to get a query plan that uses a streaming aggregate so results can be returned to the client faster. Here is the original query (Schema is at the end):[code]SELECT userid, sessionid, username FROM dbo.AttemptedLogin WHERE $Partition.PartitionByYear(datetime) = 1 AND sessionid IS NOT null GROUP BY userid, UserName, SessionID [/code]Schema for the Table and indexes are fairly straight forward. The clustered index is on (userid, datetime). There is a Non-clustered index on (Username, sessionid). Both indexes are partitioned on (DateTime). Since the query is limited to one partition and the columns being grouped on are either in the clustered index or the NC index key, the query optimizer should be able to generate a plan using a streaming aggregate that scans the NC index. However, I am unable to produce the desired plan. I've tried 4 variations: with/without MAXDOP 1, with/without query "ORDER GROUP" query hint. The plans for these are in the attachment.[code]--This is the query that should be producing a streaming aggSELECT userid, sessionid, username FROM dbo.AttemptedLogin WHERE $Partition.PartitionByYear(datetime) = 1 AND sessionid IS NOT null GROUP BY userid, UserName, SessionID --Still get a Hash Aggregate without parallelismSELECT userid, sessionid, username FROM dbo.AttemptedLogin WHERE $Partition.PartitionByYear(datetime) = 1 AND sessionid IS NOT null GROUP BY userid, UserName, SessionID OPTION (MAXDOP 1)--Prevent HASH Aggregation, and we get a distinct sort insteadSELECT userid, sessionid, username FROM dbo.AttemptedLogin WHERE $Partition.PartitionByYear(datetime) = 1 AND sessionid IS NOT null GROUP BY userid, UserName, SessionID OPTION (ORDER GROUP)--Still get distinct sortSELECT userid, sessionid, username FROM dbo.AttemptedLogin WHERE $Partition.PartitionByYear(datetime) = 1 AND sessionid IS NOT null GROUP BY userid, UserName, SessionID OPTION (MAXDOP 1, ORDER GROUP)[/code]It there something preventing the use of a streaming aggregate for this query, or is this a bug/limitation of the QO?Create Schema for the table:[code]CREATE PARTITION FUNCTION [PartitionByYear](datetime) AS RANGE RIGHT FOR VALUES (N'2005-01-01T00:00:00.000', N'2006-01-01T00:00:00.000', N'2007-01-01T00:00:00.000', N'2008-01-01T00:00:00.000', N'2009-01-01T00:00:00.000', N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2014-01-01T00:00:00.000')GO/****** Object: PartitionScheme [PartYearsToArchiveAndPrimary] Script Date: 4/19/2013 11:04:53 PM ******/CREATE PARTITION SCHEME [PartYearsToArchiveAndPrimary] AS PARTITION [PartitionByYear] TO ([Archive], [Archive], [Archive], [Archive], [Archive], [Archive], [Archive], [Archive], [PRIMARY], [PRIMARY])GOCREATE TABLE [dbo].[AttemptedLogin]( [ObjectId] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NOT NULL, [UserId] [int] NOT NULL, [UserName] [varchar](30) NULL, [Password] [varchar](30) NULL, [SessionID] [varchar](30) NULL, [IPAddress] [varchar](16) NULL, [ProxyAddress] [varchar](16) NULL, [CountryCode] [char](2) NULL, CONSTRAINT [PK_NewAttemptedLogin] PRIMARY KEY NONCLUSTERED ( [ObjectId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PartYearsToArchiveAndPrimary]([DateTime])GO/****** Object: Index [CIX_AttemptedLogin] Script Date: 4/19/2013 11:02:19 PM ******/CREATE CLUSTERED INDEX [CIX_AttemptedLogin] ON [dbo].[AttemptedLogin]( [UserId] ASC, [DateTime] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartYearsToArchiveAndPrimary]([DateTime])GO/****** Object: Index [IX_AttemptedLogin_UserNameSessionID] Script Date: 4/19/2013 11:02:20 PM ******/CREATE NONCLUSTERED INDEX [IX_AttemptedLogin_UserNameSessionID] ON [dbo].[AttemptedLogin]( [UserName] ASC, [SessionID] ASC)WHERE ([SessionId] IS NOT NULL)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PartYearsToArchiveAndPrimary]([DateTime])GO[/code]Thanks!Fri, 19 Apr 2013 22:15:54 GMTSpringTownDBAhttp://www.sqlservercentral.com/Forums/Topic1444234-3077-1.aspxHi All,Can I short circuit WHERE clause in this query without using CASE...[b]select * from company c1 where country = 'USA' or country = 'JPN'[/b]One more Issue:[b]Query 1[/b].select * from company c1 where country = case when 1=1 then 'USA'else 'JPN' end(107 row(s) affected)Table 'company'. Scan count 1, logical reads 7[b]Query 2[/b].select * from company c1 where country = 'USA' or country = 'JPN'(112 row(s) affected)Table 'company'. Scan count 1, logical reads 7Why I/O Statistics are same for both the above queries.Thu, 18 Apr 2013 23:34:05 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1444213-3077-1.aspxAll 3 of these AS Clause below will work in MS T-SQL.But my question is which on is more proper type of formatting for T-SQL (Now and in the future of later version of MS SQL?)[code="sql"]---- Sample 1SELECT 'Hello' AS 'Greeting';---- Sample 2SELECT 'Hello' AS Greeting;---- Sample 3SELECT 'Hello' AS [Greeting]; [/code]I'm looking for the real reason why one way is better than other? Ok we all know the [] and '' will allow you to do a space in column names. (A small benefit) Like AS [Today Greeting] or AS 'Today Greeting' But I would like to know if one way is possible a faster performance/or less back end resources of SQL engine behind the real code. Maybe this might be a silly/dumb question, but does anyone know the real answer and can prove it?Thu, 18 Apr 2013 21:13:15 GMTSQL Knighthttp://www.sqlservercentral.com/Forums/Topic1443992-3077-1.aspxBy default Oracle does everything in transactions. Once a I start a change the current state of the DB is frozen (from the transaction's perspective) until the transaction completes.If I start a transaction in SQL Server am I assured that records added to a source table, after the transaction is running, will not be visible to me during the transaction? I believe this touches on isolation level and such, but I do not see any isolation settings for "Begin Transaction". I do not understand the isolation level settings all that well either (which may be obvious :-) ).Thanks.Thu, 18 Apr 2013 10:30:25 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic1443523-3077-1.aspxI have set up DDL events notifications using service broker. I now am trying to set up a trigger on a certain table to fire an email on a new event. I have one trigger shredding the xml and inserting into certain tables.I then tried to create a new triggeron a specific table but when I make a change the new trigger is somehow disabling service broker queue. I'm new to tql trigger and service broker. I have gathered the information to build the event data from multiple places on the web. It works, just trying to get the email to send out on change with certain criteria is failing on me. I can add the sql if needed, maybe its the trigger.Thanks in advanceWed, 17 Apr 2013 15:58:42 GMTD-SQLhttp://www.sqlservercentral.com/Forums/Topic1443634-3077-1.aspxCan this query be written using LEFT JOIN to "person" as part of the FROM clause, rather than doing subquery?Query, IO statics and execution plan is attached. Unfortunately test data is not available.Regards.Thu, 18 Apr 2013 01:33:32 GMTT.Ashishhttp://www.sqlservercentral.com/Forums/Topic1442019-3077-1.aspxI am currently working on project where we are extracting data from some AX tables. The data from these extracts is transformed in vary ways to be consumed by various applications. One of the of the transformations requires many column values to be concatenated into one column values. The query that is currently used to do this was written by a contractor who has now left. It uses the PATH mode FOR XML Path to concatenate values. The query currently takes 24 minutes to run. We are currently in development but ideally in production this data needs to be extracted every 7 minutes. I would just like to know if there is a way I can tune the query or rewrite it in another way that could speed up the retrieval. The source tables contain about 4- 5 million records.The query is shown below[code="sql"]SELECT i.ITEMID AS CRItemID, i.PSTPRODUCTID AS CRProductID, i.NAMEALIAS AS CRName, i.CREATIONPERFORMER AS CRPerformer, i.CREATIONDURATION AS CRDuration, i.CREATIONSTATUS AS CRStatus, i.[MODIFIEDDATETIME] AS CRModifiedDateTime, STUFF ((SELECT ' ; ' + n.CRNUMBERTYPEID + '|#|' + n.CRNUMBER + CASE n.CRSOCIETYCODE WHEN '0' THEN '' ELSE '|#|' + n.CRSOCIETYCODE END FROM CRALTNUMBERS n WHERE n.CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRNumbers, STUFF ((SELECT ' ; ' + t .CRTITLE FROM CRALTTITLES t WHERE t .CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRTitles, STUFF ((SELECT ' ; ' + LTRIM(m.FIRSTNAME + ' ' + m.NAME) FROM MATCHNAME m WHERE m.CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRNames, STUFF ((SELECT ' ; ' + b.BOMID FROM BOM b WHERE b.ITEMID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRParents, STUFF ((SELECT ' ; ' + b.ITEMID FROM BOM b WHERE b.BOMID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRChildren, STUFF ((SELECT ' ; ' + p.DATA FROM PSTPRODTMPLDATA p WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Episode title' FOR XML PATH('')), 1, 3, '') AS CREpisodeTitle, STUFF ((SELECT ' ; ' + p.DATA FROM PSTPRODTMPLDATA p WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial brand' FOR XML PATH('')), 1, 3, '') AS CRComBrand, STUFF ((SELECT ' ; ' + p.DATA FROM PSTPRODTMPLDATA p WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial product' FOR XML PATH('')), 1, 3, '') AS CRComProduct, STUFF ((SELECT ' ; ' + p.DATA FROM PSTPRODTMPLDATA p WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial type' FOR XML PATH('')), 1, 3, '') AS CRComType, STUFF ((SELECT ' ; ' + p.DATA FROM PSTPRODTMPLDATA p WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Country of origin' FOR XML PATH('')), 1, 3, '') AS CRCountry, STUFF ((SELECT ' ; ' + p.DATA FROM PSTPRODTMPLDATA p WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Year of production' FOR XML PATH('')), 1, 3, '') AS CRYearFROM INVENTTABLE i[/code]I have also attached a copy of the execution plan of the latest query run. Any help would be much appreciated.Sat, 13 Apr 2013 02:58:21 GMTeseosaoregiehttp://www.sqlservercentral.com/Forums/Topic1442699-3077-1.aspxHello guys,Are there any guidelines in giving an estimate with regards the storage for non-clustered indexes on an existing table.Kind regardsTue, 16 Apr 2013 05:57:03 GMTphebsixhttp://www.sqlservercentral.com/Forums/Topic1441531-3077-1.aspxJust moved from learning mysql to SQL Server 2012, and already having an issue.Here is my code:CREATE DATABASE ApressFinancial ON PRIMARY( Name = N' ApressFinancial ',FILENAME = N' C:\Program Files\Microsft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\APRESSFINANCIAL.mdf' ,SIZE = 4096KB ,MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON( NAME= N'ApressFinancial_log', FILENAME= N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\APRESSFINANCIAL_log.ldf' ,SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)COLLATE SQL_LATIN1_General_CP1_CI_ASGOHere is my errorMsg 5105, Level 16, State 2, Line 1A file activation error occurred. The physical file name ' C:\Program Files\Microsft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\APRESSFINANCIAL.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.Msg 1802, Level 16, State 1, Line 1CREATE DATABASE failed. Some file names listed could not be created. Check related errors.Thu, 11 Apr 2013 20:01:59 GMTdob111283