Friday, June 28, 2013

[SQL Server 2008 issues] maint plan failed

[SQL Server 2008 issues] maint plan failed

maint plan failed

Posted: 27 Jun 2013 06:22 PM PDT

My manitainence plan failing with the following error [SQLSTATE 42000] (Error 14650).how to resolve this ?

split data in sql server

Posted: 27 Jun 2013 03:49 PM PDT

hi friends i have small doubt in sql server plese tell me how to solve this issusetable data contains like tabl name: product pid , color , price 1 ,red,black,blue , 1000 2 ,blue,yellow , 500 3 ,blue , 750 4 ,white,red,blue,gray , 2500 based on this table data i want show out put like pid , color , price 1 , red , 1000 1 , black , 1000 1 , blue , 1000 2 , blue , 500 2 , yellow , 500 3 , blue , 750 4 , white , 2500 4 , red , 2500 4 , blue , 2500 4 , gray , 2500 plese tell me query how to solve this issue in sql server.

find maxsales details in sql server

Posted: 27 Jun 2013 05:05 PM PDT

hi friends i have small doubt in sql server plese tell me how to solve this issusetable data contains likepid pdate price1 2013-02-10 1001 2013-03-11 1001 2013-04-14 1002 2013-04-10 1002 2013-08-04 1003 2013-05-24 1001 2013-07-05 1002 2013-04-05 5002 2013-04-01 2002 2013-06-02 200based on this i want display max prodcut id sales details actualy i tryied like this way select pid,COUNT(pid)as b from product group by pid having max(pid)=1but its not give exactely result.plz tell me query how to solve this isuse in sql server.

Buying SQL - License

Posted: 27 Jun 2013 04:15 PM PDT

We need to buy MSQL Server 2008 Standard or 2012 for a very light application.I have quote:228-09884 Microsoft SQL Server 2012 Standard - License - $1863.58server - MOLP: Open Business - Win - Single Language359-05717 Microsoft SQL Server 2012 - License - 1 user CAL - 200.98MOLP: Open Business - Win - Single LanguageP73-05762 Microsoft Windows Server 2012 Standard - License - 849.33processors - MOLP: Open Business - Single LanguageR18-04281 Microsoft Windows Server 2012 - License - 1 user CAL 32.34- MOLP: Open Business - Single LanguageProduct Subtotal 1,946.23Tax 0.00Total 1,946.23Page 1Can someone help me to understand in English what is behind this quote?Reading from microsoft.com, regarding MOLP: Open Business Microsoft Open License Program (MOLP)MOLP is a volume discount agreement, designed for organizations with 2 to 500 PCs. The licenses are perpetual and media and software assurance are priced separately.Agreement Terms:[b]There is no contract and licenses can be purchased for a period of 2 years at the entry level pricing[/b]Question:IF MOLP is perpetual, why "licenses can be purchase for a period of 2 years"? What does it mean?Does this type of license will need to be renewed after 2 years?Can someone recommend me exactly what license to buy? Again, I need SQL Server 2008 or 2012.Also WINDOWS Server 2008 or 2012. I need both license for unlimited time.Thank you very much!PD: We cannot go with a SQL Express.

Return numbers in a range

Posted: 27 Jun 2013 02:10 AM PDT

Hi all, I have a table with 3 fields (index, startNumber, EndNumber) and i'm trying to make a select so it will return all number between startNumber and EndNumber, but just can't seem to find how, here's a more "graficl" view of what I want:Table Numbers:Index | StartNumber | End number 1 1000 1002 2 1550 1555Desired output:10001001 1002 1550 1551 15521553 1554 1555Hope someone can help me.Regards.

SQLCMD hanging

Posted: 27 Jun 2013 11:01 AM PDT

Hi AllI have a php exec command which runs SQLCMD as follows the sqlcmd command works by itself but when i run it in php it hangs, any ideas[code]exec('c:\WINDOWS\system32\cmd.exe /c START C:\inetpub\wwwroot\batchfiles\export.bat');[/code]this runs a sqlcmd batch file as follows[code]sqlcmd -S d3licsql02 -d TestData -U sa -P sqldba -Q "select * from dbo.newtable" -s "," -o "C:\inetpub\wwwroot\cleansed\result.csv"[/code]

Using ISNULL causes poor performance in SP?

Posted: 27 Jun 2013 05:02 AM PDT

I am looking at this query, which is very old, and uses ISNULL all over, when this query runs from QA it runs in 4 seconds, when it runs s a SP it runs in 1 1/2 minutes, i am wondering if the ISNULLS may have something to do with it?DECLARE @CRITERIA VARCHAR(20)DECLARE @CRITERIA_VALUE VARCHAR(50)SET @CRITERIA_VALUE = 'IN006'DECLARE @WEEK_ENDING DATETIMESET @WEEK_ENDING = '6/22/2013'DECLARE @VIEW_TYPE VARCHAR(50)DECLARE @CRITERIA_VALUE1 VARCHAR(50)SET @CRITERIA_VALUE1 = '0'DECLARE @CRITERIA_VALUE2 VARCHAR(50)SET @CRITERIA_VALUE2 = '0'SELECT CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE CASE WHEN PMS.Week_Ending IS NOT NULL THEN PMS.Week_Ending ELSE PMR.Week_Ending END END AS Week_Ending, ISNULL(Sum(Contract_Week_Sales),0) AS Contract_Week_Sales, ISNULL(Sum(Week_Spread),0) AS Week_Spread,ISNULL(Sum(PMS.PERMS_Total_Sales),0)+ISNULL(Sum(PMR.PERMS_Total_Sales),0) AS Perms_Week_Sales, ISNULL(Sum(Pass_Week_Sales),0) AS Pass_Week_Sales,ISNULL(Sum(Contract_Week_Sales),0)+ISNULL(Sum(PMS.PERMS_Total_Sales),0)+ISNULL(Sum(PMR.PERMS_Total_Sales),0) AS Total_Sales,ISNULL(SUM(TOTAL_COST),0) AS TOTAL_COST,ISNULL(Sum(Contractor_Week_Total_Cost),0) AS Contractor_Week_Total_Cost, ISNULL(Sum(C1099_TOTAL_COST),0) AS C1099_TOTAL_COST, ISNULL(Sum(Bench_Week_Total_Cost),0) AS Bench_Week_Total_Cost,ISNULL(MAX(MarkUp),0) AS MarkUp, ISNULL(MAX(Margin),0) AS Margin, ISNULL(Sum(TOTALADJSPREAD),0) AS TOTALADJSPREADFROM(SELECT CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE PS.Week_Ending END AS Week_Ending, ISNULL(Sum(CONTRACT_Total_Sales),0) AS Contract_Week_Sales, ISNULL(Sum(CONTRACT_Total_Spread),0)+ISNULL(Sum(PASS_Total_Spread),0) AS Week_Spread,ISNULL(Sum(PASS_Total_Sales),0) AS Pass_Week_Sales,ISNULL(Sum(CONTRACT_Total_Sales),0)+ISNULL(Sum(PASS_Total_Sales),0) AS Total_Sales,ISNULL(SUM(TOTAL_COST),0) AS TOTAL_COST,ISNULL(Sum(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) IN ('CONTRACTOR','') THEN ISNULL(((TOTAL_COST)),0) ELSE 0 END*ISNULL(C.LOADSMALL,PS.LOADSMALL)),0) AS Contractor_Week_Total_Cost, ISNULL(Sum(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) = '1099' THEN ISNULL(((TOTAL_COST)),0) ELSE 0 END*ISNULL(C.LOADSMALL,PS.LOADSMALL)),0) AS C1099_TOTAL_COST, ISNULL(Sum(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) = 'BENCH' THEN ISNULL(((TOTAL_COST)),0) ELSE 0 END*ISNULL(C.LOADSMALL,PS.LOADSMALL)),0) AS Bench_Week_Total_Cost, CASE WHEN ISNULL(SUM(TOTAL_COST),0) <> 0 THEN ((ISNULL(SUM((TOTALSPREAD)),0))/(ISNULL(SUM((TOTAL_COST)),0)))*100 ELSE 0 END AS MarkUp, CASE WHEN SUM(PRODUCTION) <> 0 THEN (ISNULL(SUM((PRODUCTION)),0) - ISNULL(SUM((TOTAL_COST)),0) - (SUM(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) IN ('CONTRACTOR','BENCH','') THEN ISNULL(((TOTAL_COST)),0)*ISNULL(C.LOADSMALL,PS.LOADSMALL) ELSE ISNULL(((TOTAL_COST)),0)*ISNULL(C.LOADSMALL,PS.LOADSMALL) END)))/ ISNULL(SUM((PRODUCTION)),0)*100 ELSE 0 END AS Margin, ISNULL(Sum(TOTALADJSPREAD),0)+ISNULL(Sum(PASS_Total_AdjSpread),0) AS TOTALADJSPREADFROM(SELECT Week_Ending, EMPLCLAS, LOADSMALL, ISNULL(Sum(PRODUCTION),0) AS PRODUCTION, ISNULL(Sum(TOTALSPREAD),0) AS TOTALSPREAD, ISNULL(Sum(TOTAL_COST),0) AS TOTAL_COST,ISNULL(Sum(PRODUCTION),0) AS CONTRACT_Total_Sales, ISNULL(Sum(TOTALSPREAD),0) AS CONTRACT_Total_Spread, ISNULL(Sum(TOTALADJSPREAD),0) AS TOTALADJSPREADFROM ( SELECT OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL, ISNULL(SUM((DISCOUNT_BILL*(PROD_PERC/100))/Devider),0) AS PRODUCTION, ISNULL(SUM((TOTAL_COST*(PROD_PERC/100))/Devider),0) AS TOTAL_COST, ISNULL(SUM((DISCOUNT_BILL*(PROD_PERC/100))/Devider),0) - ISNULL(SUM((TOTAL_COST*(PROD_PERC/100))/Devider),0) AS TOTALSPREAD,ISNULL(SUM(CASE WHEN EMPLCLAS = '1099' THEN (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER)*B.PROD_PERC/100 ELSE (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER) * B.PROD_PERC/100 END/Devider),0) AS TOTALADJSPREAD FROM ( SELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID, TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL, CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider, DISCOUNT_BILL, TOTAL_COST as TOTAL_COST, SPREAD FROM TECH.dbo.JUDGE_HOURSALLBILLRATES WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDINGAND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0) AND PTWEEKENDING IS NULLUNIONSELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID, TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL, CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider, DISCOUNT_BILL*CAN2US_RATE AS DISCOUNT_BILL, TOTAL_COST*CAN2US_RATE as TOTAL_COST, SPREAD*CAN2US_RATE AS SPREAD FROM TECH.dbo.JUDGE_HOURSALLBILLRATES_CAN WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDINGAND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0) AND PTWEEKENDING IS NULL) A INNER JOIN ( SELECT A.ITEMNMBR, A.SPLIT_ID, B.EMPLOYID, B.PROD_PERC, B.COMM_PERC, OFFICE, LOC_COSTCENTER, START_DATE, END_DATE FROM (SELECT DISTINCT ITEMNMBR, SPLIT_ID, START_DATE, ISNULL(END_DATE,'12/31/2999') AS END_DATE FROM TAP.dbo.CIR_MASTER_SPLIT_TABLE ) A INNER JOIN (SELECT EMPLOYID, SPLIT_ID, PROD_PERC, COMM_PERC, LOC_SHORT_NAME AS OFFICE, LOC_ID AS LOC_COSTCENTER FROM TAP.dbo.CIR_DETAIL_SPLIT_TABLE D INNER JOIN TAP.dbo.CIR_LOCATION_DEPARTMENT L ON D.LOC_ID = L.ID WHERE DB_UNIT_GP IN ('TECH','CATEC') ) B ON A.SPLIT_ID = B.SPLIT_ID ) B ON A.SPLIT_ID = B.SPLIT_ID WHERE EMPLOYID = @CRITERIA_VALUEGROUP BY OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL ) CONTRACT GROUP BY Week_Ending, EMPLCLAS, LOADSMALL) C FULL JOIN( SELECT Week_Ending, EMPLCLAS, LOADSMALL, ISNULL(Sum(PRODUCTION),0) AS PASS_Total_Sales, ISNULL(Sum(TOTALSPREAD),0) AS PASS_Total_Spread, ISNULL(Sum(TOTALADJSPREAD),0) AS PASS_Total_AdjSpread FROM ( SELECT OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL, 0 AS PRODUCTION, ISNULL(SUM((DISCOUNT_BILL*(PROD_PERC/100))/Devider),0) - ISNULL(SUM((TOTAL_COST*(PROD_PERC/100))/Devider),0) AS TOTALSPREAD,ISNULL(SUM(CASE WHEN EMPLCLAS = '1099' THEN (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER)*B.PROD_PERC/100 ELSE (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER) * B.PROD_PERC/100 END/Devider),0) AS TOTALADJSPREAD FROM ( SELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID,TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL, CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider, DISCOUNT_BILL, TOTAL_COST as TOTAL_COST, SPREAD FROM TECH.dbo.JUDGE_HOURSALLBILLRATES WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING AND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0) AND PTWEEKENDING IS NOT NULL UNIONSELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID,TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL, CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider, DISCOUNT_BILL*CAN2US_RATE AS DISCOUNT_BILL, TOTAL_COST*CAN2US_RATE as TOTAL_COST, SPREAD*CAN2US_RATE AS SPREAD FROM TECH.dbo.JUDGE_HOURSALLBILLRATES_CANWHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING AND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0) AND PTWEEKENDING IS NOT NULL ) A INNER JOIN ( SELECT A.ITEMNMBR, A.SPLIT_ID, B.EMPLOYID, B.PROD_PERC, B.COMM_PERC, OFFICE, LOC_COSTCENTER, START_DATE, END_DATE FROM (SELECT DISTINCT ITEMNMBR, SPLIT_ID, START_DATE, ISNULL(END_DATE,'12/31/2999') AS END_DATE FROM TAP.dbo.CIR_MASTER_SPLIT_TABLE ) A INNER JOIN (SELECT EMPLOYID, SPLIT_ID, PROD_PERC, COMM_PERC, LOC_SHORT_NAME AS OFFICE, LOC_ID AS LOC_COSTCENTER FROM TAP.dbo.CIR_DETAIL_SPLIT_TABLE D INNER JOIN TAP.dbo.CIR_LOCATION_DEPARTMENT L ON D.LOC_ID = L.ID ) B ON A.SPLIT_ID = B.SPLIT_ID ) B ON A.SPLIT_ID = B.SPLIT_ID WHERE EMPLOYID = @CRITERIA_VALUEGROUP BY OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL ) PASS GROUP BY Week_Ending, EMPLCLAS, LOADSMALL) PS ON C.Week_Ending = PS.Week_Ending AND C.EMPLCLAS = PS.EMPLCLASGROUP BY CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE PS.Week_Ending END) CFULL JOIN (SELECT Week_Ending, ISNULL(Sum(DISCOUNT_BILL),0) AS PERMS_Total_Sales FROM TECH.dbo.JUDGE_YTD_PERMS_SPLITS_PASS PWHERE Splits_Perms = 'PERMS' AND Salesperson IS NOT NULLAND Salesperson = @CRITERIA_VALUE GROUP BY Week_Ending HAVING Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING ) PMS ON C.Week_Ending = PMS.Week_Ending FULL JOIN (SELECT Week_Ending, ISNULL(Sum(DISCOUNT_BILL),0) AS PERMS_Total_Sales FROM TECH.dbo.JUDGE_YTD_PERMS_SPLITS_PASS PWHERE Splits_Perms = 'PERMS' AND Recruiter IS NOT NULL AND ISNULL(Recruiter,'') = @CRITERIA_VALUE AND ISNULL(Salesperson,'') <> @CRITERIA_VALUEGROUP BY Week_Ending HAVING Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING) PMR ON C.Week_Ending = PMR.Week_EndingGROUP BY CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE CASE WHEN PMS.Week_Ending IS NOT NULL THEN PMS.Week_Ending ELSE PMR.Week_Ending END END --EXECUTE REPORTS.dbo.JUDGE_WEEKLY_SNAPSHOT_ALL 'emp','IN006','6/22/2013','Combined'

Avoiding failing entire procedure if one SELECT statement fails

Posted: 27 Jun 2013 06:47 AM PDT

I have a few procedures with multiple SELECT statements (using UNION ALL) in a single procedure. Is there a way to avoid failure for the entire procedure if one of the SELECT statements fail?Below is a sample scenario. I know somethings could be done to kick out the offending data. However, the table illustrates what I'm facing. Is there a way to have SQL avoid t1 and t2 (since they can't CAST the VARCHAR) but still process t3 and 4? Thanks for any help![code="sql"]CREATE TABLE tbl( col1 varchar(25) not null, col2 varchar(25) not null )INSERT INTO tbl (col1, col2)VALUES ('t1', '3.21'), ('t1', '2.29'), ('t1', ''), ('t2', '21'), ('t2', ''), ('t2', '19'), ('t2', '20a'), ('t3', '3.11'), ('t3', '3.54'), ('t4', '18'), ('t4', '29'), ('t4', '26');/*Print the results of table creation*/SELECT tbl.col1, tbl.col2FROM tbl;[/code][code="sql"]/*Create procedure AvgCol which attempts to CAST the values in Col2 for t1, t2, t3, and t4*/CREATE PROCEDURE AvgCol ASSELECT tbl.col1, CAST(tbl.col2 AS Decimal(8,2)) AS col2_castFROM tblWHERE tbl.col1 = 't1' UNION ALL SELECT tbl.col1, CAST(tbl.col2 AS Decimal(8,2)) AS col2_castFROM tblWHERE tbl.col1 = 't2' UNION ALL SELECT tbl.col1, CAST(tbl.col2 AS Decimal(8,2)) AS col2_castFROM tblWHERE tbl.col1 = 't3' UNION ALLSELECT tbl.col1, CAST(tbl.col2 AS Decimal(8,2)) AS col2_castFROM tblWHERE tbl.col1 = 't4';[/code][code="sql"]/*Executes AvgCol. It fails because two of the SELECT statements are trying to cast a varchar('', '20a') to numeric*/EXEC AvgCol[/code]

bcp export problem

Posted: 27 Jun 2013 08:23 AM PDT

Hi AllI have a bcp export which works fine but I it is not showing my column headings which i really need.Any ideas[code]USE [TestData]GO/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 06/27/2013 12:23:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[exporttocsv]@importedquery nvarchar(1000), /* The query to be executed */@importedcsvname nvarchar(150) /* To name the exported file back to the original name */asBEGIN DECLARE @path varchar(50)DECLARE @filename varchar(30)DECLARE @dbname varchar(30)DECLARE @sql varchar(2000)SELECT @path = 'C:\inetpub\wwwroot\cleansed\'SELECT @filename = @importedcsvname + ' -c -t, -d 'SELECT @dbname = 'TestData'SELECT @sql = 'bcp "' + @importedquery + '" queryout ' + @path + @filename + @dbname--print @sqlEXEC master..xp_cmdshell @sqlend[/code]

Google Analytics SQL Import

Posted: 08 May 2012 09:07 PM PDT

Hi AllI was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS?A quick google brought up some C# scripts, a PHP web scraper and using the java client to export to CSV and then import it, but was wondering if anyone has managed to query the API and load it direct in SSIS to the DB?This was a task which was done a couple of years ago and I have been asked to re-ignite the flame to get it moving again and the previous way was to use xp_cmdshell to execute a file which loads it to CSV and imports it, which I have no trouble with, just wondering if anyone has a better solution.Thanks

SSRS Parameters - Depending one 1 parameter selection the other parameter needs to point to 2 different sets of data

Posted: 27 Jun 2013 06:23 AM PDT

Hello,Please help. This is urgent. I have a sales report dataset where I have items sold, ship date and destination date. Ship date is when the item is shipped from warehouse and destination date is when it reaches the destination. In the report dataset I have 2 columns - ship date and destination date. The requirement is -Have 3 parameters - WhichDate (ShipDate or DestinatioDate), StartDate (User select start date) and EndDate (User selects end date). If the user selects ShipDate from the first parameter then the StartDate and ShipDate entered in 2nd and 3rd parameters should filter on ShipDate. If the user selects DestinationDate from the first parameter then the StartDate and ShipDate entered in 2nd and 3rd parameters should filter on DestinationDate.I am struggling with it. Any help is appreciated.Thanks.

Magic Quadrant for Monitoring Software?

Posted: 27 Jun 2013 04:40 AM PDT

Hi all,Apologies for dropping this post into the SQL2008 General forum... but it kind of touches on most areas and platforms.My question - Has anyone published a [b][i]comprehensive [/i][/b]comparison of available SQL Server monitoring products? I've been doing quite a bit of searching (both here and elsewhere on the web) and can't seem to find what I'm looking/hoping for.Basically, I'd love to see a Gartner-esque magic quadrant that is specific to SQL Server monitoring tools. Our department is seriously looking into purchasing a solution, and it would be great if some of the leg work had already been done - allowing us to focus in on the true leaders in the market.I mean, I know about the usual players - Redgate, Idera, etc... but I'm hoping for some rankings from a fairly reputable/knowledgeable source.Any ideas?Thanks.

Renaming a Named Instance

Posted: 31 Jan 2012 01:14 PM PST

Is it possible to rename a named instance..?If yes, are there any issues..?

SQL Server Agent - Transact SQL script

Posted: 27 Jun 2013 06:19 AM PDT

Hi If I execute 3 stored procedures in a SQL Agent Steps (type Transact-SQL script) like this:exec storeproc1exec storeproc2exec storeproc3If the execution of the second stored procedure failed, does the execution of storeproc3 will go thru or the job will failed after failure of storeproc2?Thanks

Insert select statement into a table with multiple constraints

Posted: 27 Jun 2013 03:25 AM PDT

I'm trying to insert rows into a table that has multiple constraints. My statement is failing because SQL is looking at the first constraint field and sees that the value already exists, even though the second constraint is different. An example is VCHRNMBR='xyz' is already in the table with DOCTYPE=1 and I need to add a record to the table for VCHRNMBR='xyz' with DOCTYPE=6. Is there a way to accomplish this without modifying the constraints on the table prior to the insert?Here's the code: insert into PM30200 select a.VCHRNMBR ,a.DOCTYPE from PM30300 a where a.VCHRNMBR not in (select VCHRNMBR from PM30200 where DOCTYPE=6) and DOCTYPE=6All help is appreciated! Thanks!Trish

Is there any way to track tempdb usage when checkdb is running

Posted: 27 Jun 2013 06:36 AM PDT

please help me with this q:-)

Data Modeling tools that support "submodels"?

Posted: 27 Jun 2013 06:20 AM PDT

Complicated databases have complicated ERDs. Too complicated usually and hard for others to know where to start understanding the database. I am looking for a product that supports information "hiding" in the same way that modern programming languages hide info in functions\subroutines. All that remains in the main source code is a call(s) to the functions\subroutines. From a modeling perspective I would like the option to create my model as usual than create submodels containing related groups of tables. The main model would contain only a "hyperlink" like structure that I click to display\edit the submodel contents in a new window. Get it? The submodel holds the related details and the actual tables are visually removed from the main model (unless I specifically want all tables to be displayed on the main model.) This is more than just using the same color background for related tables. This is information hiding (encapsulation). So, can you recommend any third party products that support this approach?TIA,BD

Replication Distribution Agent Failure "memory mapped file read failed"

Posted: 27 Jun 2013 06:07 AM PDT

Please advise on resolution steps that worked for you for the below error description while delivering snapshot data to subscriber server through Distribution Agent. Error messages:• The process could not bulk copy into table '"dbo"."Table"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)Get help: http://help/MSSQL_REPL20037• memory mapped file read failed• To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)Get help: http://help/20253• bcp "DB"."dbo"."Table" in "\etworkpath\Table_69.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 –S<Subscriber Server Name> -T -w (Source: MSSQLServer, Error number: 20253)Get help: http://help/20253• Query timeout expired (Source: MSSQLServer, Error number: HYT00)Get help: http://help/HYT00

Resource Governor - How to Trace Events

Posted: 10 Jun 2013 04:05 AM PDT

I am trying to set up an automatic email when a user in a resource group goes over a threshhold. I found the resource governor event REQUEST_MAX_CPU_TIME_SEC that according to the documentation does exactly what I'm trying to do. (The reference is following.) I have set the event in the resource governor, but cannot figure out how to trace the event. I did not see the event in SQL profiler and have not been able to find any examples of this. I'm just beginning with the resource governor. I think I may be tracking down the wrong thing because everything I've found doesn't seem to relate to trapping the event and sending an email.The documentation says:1.Set a limit on CPU usage for the ad hoc group. (DONE)2.Monitor SQL Trace events (Resource Governor management class event). (???)3.Perform an action on the event. For example, ignore the event, send an e-mail, send a page, or execute the KILL command on the request. (???)http://msdn.microsoft.com/en-us/library/bb933944(v=sql.105).aspxAny help is appreciated.-Brian

Disable TDE

Posted: 11 Mar 2009 10:44 AM PDT

Guys,What is the best way to disable TDE. I uncheck the "Set Database Encryption On property" but I am still not able to restore or attach the database on a different server. The value of is_encrypted in sys.databases is 0 (after the uncheck).The error message while restoring the database on a different serverMsg 3283, Level 16, State 1, Line 1The file "AdventureWorks_Log" failed to initialize correctly. Examine the error logs for more details.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.and thumb print error while doing attach.

SQL Server 2008 R2 File Stream Sets Restricted Growth of Log File to 2TB

Posted: 27 Jun 2013 04:19 AM PDT

This is my first foray into using file streams. I have created three OLTP databases on my SQL 2008 R2 server for some new applications under development and then added File Stream to each. After a couple of weeks I got a notice that my log file drive was nearly full. Investigating the cause I found that on each one of the new databases, the log file was set to restricted growth at 2TB and as long as I have selected "Enable Autogrowth" it will not allow me to change that to something less or to unrestricted. Is this some sort of bug or by design?My databases and logs are being backed up; so it should be truncating the log; but, as the log grows it never allows shrinking on truncation because the restricted file size is not allowing it to shrink. I realize that allowing auto growth comes at a performance cost; but, I am okay with that normally as I have it set to 20%. I am however surprised by the linkage between auto growth and restricted growth size when using file streams. I don't recall any mention/warning of this in the documentation. Does it have something to do with my file group setup (My data and log are in the Primary file group, and by default the file stream is in its own file group)--in other words, do I need to put the tables associated with the file stream data into a different file group as well? Is there some other setting affecting this?

Failing agent job

Posted: 27 Jun 2013 03:26 AM PDT

Hi,Strange one bugging me have almost identical code running in another database on the server without issue.I have an agent job which runs[code="sql"]DELETE FROM [biostratweb].[dbo].[xx_FreeTextSearch_ABS][/code]However I always get an error:Date 27/06/2013 17:23:50Log Job History (BiblioWeb Merge and FreeTextSearch Update)Step ID 2Server SQLVMJob Name BiblioWeb Merge and FreeTextSearch UpdateStep Name Free Text updateDuration 00:00:00Sql Severity 16Sql Message ID 208Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: NEFTEX\spsqlagent. Invalid object name 'biostratweb.dbo.xx_FreeTextSearch_ABS'. [SQLSTATE 42S02] (Error 208). The step failed.When I run it directly it works fine.Any ideas, I am running SQL 2008.Many Thanks as always,Oliver

Converting "not in" to "not exists"

Posted: 27 Jun 2013 01:58 AM PDT

/* I'm trying to change "not in" to "not exists" and I don't have the syntax quite right. The first statement updates multiple rowswhile the second statement updates 0 rows. This is for SQL Server. */ update VehicleWorld set EstimatedDeletedDate=1where Vehicle_ID not in (select Vehicle_ID from CFS_Production..CORE_VehicleAdSearch ads) update VehicleWorld set EstimatedDeletedDate=1where not exists (select 1 from VehicleWorld car, CFS_Production..CORE_VehicleAdSearch adswhere car.Vehicle_ID = ads.Vehicle_ID)

Total no. of application hits

Posted: 27 Jun 2013 02:52 AM PDT

HelloIs there an easy way of calcluating how many times an application hits SQL server on daily basis. I was thinking of using SQL server profiler for counting how many times it logs in by its service account (using audit logon event) and that would be equivalent to no. of times application connects SQL server. Is there any other easy way. I have SQL server 2008 SP1 CU2 enterprise edition x64.Thanks in advance!!

Question of the Day

Posted: 27 Jun 2013 02:22 AM PDT

"Question of the Day" - keep it coming, hones everyone's SQL skills.:-)

INSERT Statement

Posted: 27 Jun 2013 01:53 AM PDT

I am looking to insert data into a table if the data is not already in the table. Is the below the correct statement to accomplish this?INSERT INTO DSD(req_id,value,comments,create_date,modify_date,modify_by,peps_id,place_id,act_date)SELECT DISTINCT smf6.req_id,smf6.value,smf6.comments,smf6.create_date,smf6.modify_date,smf6.modify_by,smf6.student_id,smf6.place_id,smf6.act_dateFROM smf6WHERE (smf6.RespID + smf6.req_id + smf6.peps_id)NOT IN (SELECT DSD.modify_by, DSD.req_id + DSD.peps_id from DSD)

What's the best index?

Posted: 27 Jun 2013 12:22 AM PDT

Hey,I have a query that goes along this line:[code="sql"]SELECT * FROM myTableWHERE col1=1 AND col2=2ORDER BY col3, col1 DESC[/code]What's the best index for this query?My guess is [b]col3 , col1 DESC , col2[/b]

?? on shrinking transaction log

Posted: 26 Jun 2013 11:58 PM PDT

HI,I'm trying to backup a LIVE db so I can restore it on a conversion db for testing.I ran into a problem that the transaction log is 114G. I did a bit of research and found thisALTER DATABASE [dbname] set recovery simple GOCHECKPOINTGODBCC SHRINKFILE (logicalname,1)GOALTER DATABASE [dbname] set recovery fullGOtested with a test db and works but just wondering what implications it may have...ThanksJoe

DDL trigger to prevent drop of a particular database

Posted: 26 Jun 2013 09:55 PM PDT

Can I write a DDL trigger to prevent drop of a particular database? I tried this but didn't work:CREATE TRIGGER Prevent_DB_Drop ON ALL SERVER FOR DROP_DATABASEAS DECLARE @oname NVARCHAR(100)SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')IF @oname IN ('testDB')BEGIN PRINT 'You are not allowed to drop this Database.' ROLLBACK;ENDGOENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER drop database test

Login failed for user

Posted: 26 Jun 2013 08:24 PM PDT

In one of my vb.net application i am getting error as "Login failed for user 'user'."But the user has full permission(sysadmin). Also this error does not come always.(So it is not due to permissions i guess, also this application works properly from a long time). So i am not able to troubleshoot it. So anyone experienced the same or can anyone help me to find out the error?

ORM Queries Prone to Excessive Blocking

Posted: 21 May 2013 02:01 AM PDT

Our application is using nHibernate as our ORM, and during some small load tests we've run I am noticing that the load test script is bogging down badly due to long waits and blocking. It mostly is affecting two tables (Alert and VehicleOfInterest - see create scripts below). These are the two queries. The INSERT into alert is grabbing a MISCELLANEOUS lock and is blocking the second statement below (a SELECT grabbing a shared lock):[code="sql"]INSERT INTO [Alert] (ModifiedDate, Type, CreatedDate, CreatedBy, SalespersonName, DupeDetectionRuleCriteria, OpportunityType, CustomerMatches, RemainingUsers, OrganizationId, AssignedToId, CustomerId, OpportunityId, SalesLeadId, VehicleOfInterestId, ActionId, NoteId, WantListId, ServiceLeadId, PartsLeadId, VehicleId, CompanyId, OrganizationTemplateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22); select SCOPE_IDENTITY()[/code] [code="sql"](@p0 bigint)select vehicleofi0_.Id as Id252_, vehicleofi0_.ModifiedDate as Modified2_252_, vehicleofi0_.Year as Year252_, vehicleofi0_.IsPrimary as IsPrimary252_, vehicleofi0_.Make as Make252_, vehicleofi0_.Model as Model252_, vehicleofi0_.Source as Source252_, vehicleofi0_.Vin as Vin252_, vehicleofi0_.Status as Status252_, vehicleofi0_.Type as Type252_, vehicleofi0_.OrganizationId as Organiz11_252_, vehicleofi0_.VehicleId as VehicleId252_, vehicleofi0_.SalesLeadId as SalesLe13_252_ from [VehicleOfInterest] vehicleofi0_ inner join [SalesLead] saleslead1_ on vehicleofi0_.SalesLeadId=saleslead1_.Id where saleslead1_.IsActive=1 and (saleslead1_.ClosedDate is null) and vehicleofi0_.VehicleId=@p0[/code] As a DBA, I have concerns with how nHibernate is hydrating the alert object with an Id (SCOPE_IDENTITY). However, it seems this is an extremely common method for populating an Id into a model. I've voiced my concerns with using SCOPE_IDENTITY, and we've found some other better options (high-low algorithm for instance), but these cannot be deployed immediately, as they are large refactors. I've created all necessary indexes, and the blocking is still prominent at low load on the hardware. Create Statement For Alert:[code="sql"]CREATE TABLE [dbo].[Alert]([Id] [bigint] NOT NULL IDENTITY(1, 1),[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[CreatedDate] [datetime] NOT NULL,[CreatedBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[SalespersonName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DupeDetectionRuleCriteria] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OpportunityType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CustomerMatches] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OrganizationId] [bigint] NOT NULL,[AssignedToId] [bigint] NOT NULL,[CustomerId] [bigint] NULL,[OpportunityId] [bigint] NULL,[SalesLeadId] [bigint] NULL,[VehicleOfInterestId] [bigint] NULL,[ActionId] [bigint] NULL,[NoteId] [bigint] NULL,[WantListId] [bigint] NULL,[ServiceLeadId] [bigint] NULL,[VehicleId] [bigint] NULL,[ExternalSourceId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ModifiedDate] [datetime] NULL CONSTRAINT [DF_Alert_ModifiedDate] DEFAULT (getutcdate()),[CompanyId] [bigint] NULL,[RemainingUsers] [int] NULL,[OrganizationTemplateId] [bigint] NULL,[PartsLeadId] [bigint] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [PK__Alert__3214EC071BFD2C07] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_OrganizationId_AssignedToId_Type] ON [dbo].[Alert] ([OrganizationId], [AssignedToId], [Type]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_AssignedToId] ON [dbo].[Alert] ([AssignedToId]) INCLUDE ([OrganizationId]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_ActionId] ON [dbo].[Alert] ([ActionId]) INCLUDE ([Id]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_CustomerId] ON [dbo].[Alert] ([CustomerId]) INCLUDE ([Id], [Type], [CreatedDate], [CreatedBy], [SalespersonName], [DupeDetectionRuleCriteria], [OpportunityType], [CustomerMatches], [OrganizationId], [AssignedToId], [OpportunityId], [SalesLeadId], [VehicleOfInterestId], [ActionId], [NoteId], [WantListId], [ServiceLeadId], [VehicleId], [ModifiedDate], [CompanyId], [RemainingUsers], [OrganizationTemplateId]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_VehicleOfInterestId] ON [dbo].[Alert] ([VehicleOfInterestId]) INCLUDE ([Id], [Type], [CreatedDate], [CreatedBy], [SalespersonName], [DupeDetectionRuleCriteria], [OpportunityType], [CustomerMatches], [OrganizationId], [AssignedToId], [CustomerId], [OpportunityId], [SalesLeadId], [ActionId], [NoteId], [WantListId], [ServiceLeadId], [VehicleId], [ModifiedDate], [CompanyId], [RemainingUsers], [OrganizationTemplateId], [PartsLeadId]) ON [PRIMARY]GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Action_ActionId_OrganizationEntityBaseId] FOREIGN KEY ([ActionId]) REFERENCES [dbo].[Action] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Company_CompanyId_OrganizationEntityBaseId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Customer_CustomerId_OrganizationEntityBaseId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Note_NoteId_OrganizationEntityBaseId] FOREIGN KEY ([NoteId]) REFERENCES [dbo].[Note] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Opportunity_OpportunityId_OrganizationEntityBaseId] FOREIGN KEY ([OpportunityId]) REFERENCES [dbo].[Opportunity] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Organization_OrganizationId_EntityBaseId] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_OrganizationTemplate_OrganizationTemplateId_OrganizationEntityBaseId] FOREIGN KEY ([OrganizationTemplateId]) REFERENCES [dbo].[OrganizationTemplate] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_PartsLead_PartsLeadId_LeadId] FOREIGN KEY ([PartsLeadId]) REFERENCES [dbo].[PartsLead] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Person_AssignedToId_OrganizationEntityBaseId] FOREIGN KEY ([AssignedToId]) REFERENCES [dbo].[Person] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_SalesLead_SalesLeadId_LeadId] FOREIGN KEY ([SalesLeadId]) REFERENCES [dbo].[SalesLead] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_ServiceLead_ServiceLeadId_LeadId] FOREIGN KEY ([ServiceLeadId]) REFERENCES [dbo].[ServiceLead] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Vehicle_VehicleId_EntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Vehicle_VehicleId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_VehicleOfInterest_VehicleOfInterestId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleOfInterestId]) REFERENCES [dbo].[VehicleOfInterest] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_WantList_WantListId_OrganizationEntityBaseId] FOREIGN KEY ([WantListId]) REFERENCES [dbo].[WantList] ([Id])GO[/code]Create Script for Voi:[code="sql"]CREATE TABLE [dbo].[VehicleOfInterest]([Id] [bigint] NOT NULL IDENTITY(1, 1),[Year] [int] NULL,[IsPrimary] [bit] NOT NULL,[Make] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Model] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Source] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Status] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[OrganizationId] [bigint] NOT NULL,[VehicleId] [bigint] NULL,[SalesLeadId] [bigint] NOT NULL,[ExternalSourceId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ModifiedDate] [datetime] NULL CONSTRAINT [DF_VehicleOfInterest_ModifiedDate] DEFAULT (getutcdate()),[Vin] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOALTER TABLE [dbo].[VehicleOfInterest] ADD CONSTRAINT [PK__VehicleO__3214EC07592635D8] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_IsPrimary] ON [dbo].[VehicleOfInterest] ([IsPrimary]) INCLUDE ([Year], [Make], [Model], [VehicleId], [SalesLeadId]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_SalesLeadId,IsPrimary_VehicleId] ON [dbo].[VehicleOfInterest] ([SalesLeadId], [IsPrimary], [VehicleId]) INCLUDE ([Year], [Make], [Model]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_VehicleId] ON [dbo].[VehicleOfInterest] ([VehicleId]) ON [PRIMARY]GOALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_Organization_OrganizationId_EntityBaseId] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([Id])GOALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_SalesLead_SalesLeadId_LeadId] FOREIGN KEY ([SalesLeadId]) REFERENCES [dbo].[SalesLead] ([Id])GOALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_Vehicle_VehicleId_EntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GOALTER TABLE [dbo].[VehicleOfInterest] ADD CONSTRAINT [FK_VehicleOfInterest_Vehicle_VehicleId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GO[/code]Does anybody see any glaring issues here?

Partitioning on Existing table with non clustered index in date column.

Posted: 26 Jun 2013 08:33 PM PDT

Hi All,I have one table with 10 lacks records. I partitioned that table on CreatedDate column with non clustered index( i am not removing clustered index on ID column, It is as part of primary key).It is inserting to data into relevant partition only. But i am verifying is that table partitioned or not by using below steps, in object Explorer-Database-->TestDB-->tables-->select partitioned table and Right click on table select properties --Storage File Group= PrimaryTable Partitioned = FalseIf create Partitioned with Clustered index , it is showing correctly Table Partitioned = True But i am creating with non clustered.Can any one explain, [b]Is that below steps are correct process to do the partition, If i create non clustered index on datetime column is there any performance issue on existing queries.[/b]CREATE TABLE tblPartition(ID int primary key identity(1,1),Name varchar(30),CreatedDate Datetime)insert into tblPartition(Name,CreatedDate)SELECT 'Name1','2013-05-26 13:53:47.650'union allselect 'Name2','2013-05-26 13:53:47.650'union allSELECT 'Name1','2013-06-26 13:53:47.650'union allselect 'Name2','2013-06-26 13:53:47.650'union allSELECT 'Name1','2013-07-26 13:53:47.650'union allselect 'Name2','2013-07-26 13:53:47.650'goCREATE PARTITION FUNCTION [PartitionFunction](datetime) AS RANGE RIGHT FOR VALUES (N'2013-05-31 23:59:59', N'2013-06-30 23:59:59', N'2013-07-31 23:59:59')CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunction] TO ([FGNdf10], [FGNdf11], [FGNdf12], [PRIMARY])CREATE [b]NONCLUSTERED[/b] INDEX [IX_PartitionScheme_CreatedDate] ON [dbo].[tblPartition]([CreatedDate])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreatedDate])
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog