Monday, August 5, 2013

[SQL 2012] Run at Certain Time of Morning

[SQL 2012] Run at Certain Time of Morning

Run at Certain Time of Morning

Posted: 05 Aug 2013 03:11 AM PDT

Hi All,Im trying to add a check to see what time it is in the morning before my stored procedure runs, I tried looking around for some examples but Im sure the simple solution is right before my eyes. [code="sql"]ASdeclare @today as date = cast(getdate()-1 as date)declare @HTML as varchar(max)declare @TitleHTML as varchar(max)declare @CDCIIHTML as varchar (max)declare @CDCsubj as varchar(20) = 'CDC Report'set @TitleHTML = N'<h1 align="Left">CDC Report</h1>'+ N'<h2 align="Left"> ' + CAST(GetDate()as varchar) + '</h2>'set @CDCIIHTML = N'<h2 align="Left"> CDC CII </h2>'+ N'<style> td {border: solid black 1px; text align: center; padding-left:5px;padding-right:6px;padding-top:1px;padding-bottom:1px;font-size:10pt;}</style>'+ N'<table> <width="100%" cellpadding="5"> <table style="font-family: Calibri">'+ N'<tr bgcolor="gray"> <td width ="150" ><b>SCHEDULE</b></td> <td width ="100"><b>NC</b></td>'+ N'<td width ="100"><b>DESCRIPTION</b></td>'+ N'<td width ="175"><b>UNIT</b></td>'+ N'<td width ="125"><b>VAR PCT</b></td>'+ N'<td width ="100"><b>QTY</b></td> <td width ="125"><b>USER ID</b></td>'+ N'<td width ="200"><b>DATE</b></td>'+ N'<td width ="250"><b>REASON</b></td>'+ CAST((SELECT td = [SCHEDULE],'', td = [NDC],'', td = [DESCRIPTION],'', td = [TOUNIT],'', td = [CYCLEVARPCT],'', td = [CYCLE QTY],'', td = isnull([USERID],''),'', td = [TO_CHAR(A.TIMEDATE,'MM/DD/YYYY')],'', td = isnull([REASON],''),'' FROM [CF].[dbo].[CDC Report] WHERE SCHEDULE = 'CII'FOR XML PATH('tr'), TYPE )AS NVARCHAR(MAX)) + N'</table>'SET @HTML = @TitleHtml + isnull(@CDCIIHTML,'')if (@HTML IS not null) AND (SELECT GETDATE() >= '09:00:00') --telling me here I have incorrect --syntax, and I cant figure out why? At the greater than and closing parenthesis after 9am.begin EXEC msdb.dbo.sp_Send_dbmail @profile_name = 'SQL_Server', @recipients = '' @subject = @CDCsubj, @body_format='HTML', @body = @HTMLEND[/code]Thanks

AlwaysOn Question \ Research

Posted: 05 Aug 2013 02:53 AM PDT

I have an infrastructure guy that is turned on by the AlwaysOn feature in SQL 2012, I like the idea for DR but not production all the time however I am willing to do the research to see what is the best option. I myself prefer traditional clustering for large databases, SharePoint and document containing DB's in SQL 2012, now we have AlwaysOn and they want to do a 2 node A\P cluster (AlwaysOn) for everything. Would this be the best, most efficient option for a production environment or should traditional clustering be set up with AlwaysOn to the DR site?

MDX SCOPE function

Posted: 04 Aug 2013 10:26 PM PDT

Hi All,I've got a measure group "Daily Sales" with a number of measures in (obviously) and I'd like to divide each of the measures in the measure group depending on which attribute I've picked from my utility dimension. In my utility dimension called 'divisor' there are only 2 values "1" and "2". In the "Daily Sales" measure group let's say there are 3 measures (Sales Value, Cost, Taxes).Is there a quicker way than doing this:[code="sql"]SCOPE([Divisor].[Divisor].&[2]);[Measures].[Sales Value] = [Measures].[Sales Value] / 2;[Measures].[Cost] = [Measures].[Cost] / 2;[Measures].[Taxes] = [Measures].[Taxes] / 2;END SCOPE;[/code]Help much appreciated :)Cheers,Jim.

Confused with my procedure

Posted: 04 Aug 2013 04:08 PM PDT

Hi ProfessionalsI have a procedure like so[code]ALTER procedure [dbo].[updatethecolumn] @columnname1 varchar(1000), @columnname2 varchar(1000)asbegin --Update the Software Manufacturer and the Product name if RTRIM(@columnname1) = 'softwaremanufacturer' and RTRIM(@columnname2) = 'productname' begin UPDATE dbsource SET @columnname1 = dbref.Amended_SW_Manufacturer, @columnname2= dbref.Amended_Product_Name FROM dbo.newtable dbsource INNER JOIN ( SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name FROM datalookuptable GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name ) dbref ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer --and dbref.Raw_Product_Version = dbsource.productversion and dbref.Raw_Product_Name = dbsource.productname end[/code]I have ran the procedure both ways like so[code]exec updatethecolumn softwaremanufacturer,productnameandexec updatethecolumn 'softwaremanufacturer','productname'[/code]here is my table data[code]Microsoft Access 2003 11.0.SP3 (jp) 22/04/2005 30/04/2008NULL Microsoft Access 2003 11.0.SP3 (jp) NULL 22/04/2005 30/04/2008[/code]which does not update for some strange reason and I dont know why.when I run my procedure manually it updates fine.[code]UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer, productname= dbref.Amended_Product_Name FROM dbo.newtable dbsource INNER JOIN ( SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name FROM datalookuptable GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name ) dbref ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer --and dbref.Raw_Product_Version = dbsource.productversion and dbref.Raw_Product_Name = dbsource.productname[/code]which produces the update like so[code]Microsoft Corporation Office Access 2003 11.0.SP3 (jp) 22/04/2005 30/04/2008NULL Microsoft Access 2003 11.0.SP3 (jp) NULL 22/04/2005 30/04/2008[code]anyone have any idea where I am going wrong

Re: Full text catalog maintenance procedures

Posted: 04 Aug 2013 01:34 PM PDT

I have a full text catalog containing a number of tables/views. Tables/views in the full text catalog have "Do not track changes" and "Manual" selected for "Track changes". A nightly job was created that executes the following command:[code]ALTER FULLTEXT CATALOG [catalog_name] REORGANIZE[/code]From a maintenance perspective, should any of the above be changed to ensure that catalogs are populated at least once a day? Should I be manually executing the following command on a daily basis for all tables in the fulltext catalog [catalog_name]:[code]ALTER FULLTEXT INDEX ON [table_name] START FULL POPULATION[/code]Or is this command implied by "reorganizing the full text catalog"?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog