Wednesday, August 21, 2013

[SQL 2012] SQL server cluster Failover installation

[SQL 2012] SQL server cluster Failover installation

SQL server cluster Failover installation

Posted: 30 Jul 2013 01:16 AM PDT

Hi guys i am supposed to do a failover installation on Windows server.I have to create a cluster group, Do any one know any site or video or notes which guides me to get prepared.Rookie here :hehe:

Format varchar to currency.

Posted: 21 Aug 2013 02:24 AM PDT

How to format varchar to currency. Negative amounts to ($100.50) with parentheses?

Database refresh security sync

Posted: 21 Aug 2013 03:04 AM PDT

To return role permissionSELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' from sys.database_principals users inner join sys.database_role_members link on link.member_principal_id = users.principal_id inner join sys.database_principals roles on roles.principal_id = link.role_principal_idTo return the explicit permissions granted or denied objects in a database, execute the following statement in the database. Then execute the result.SELECTperms.state_desc AS State,permission_name AS [Permission],'ON',obj.name AS [on Object],'to',dPrinc.name AS [to User Name]FROM sys.database_permissions AS permsJOIN sys.database_principals AS dPrincON perms.grantee_principal_id = dPrinc.principal_idJOIN sys.objects AS objON perms.major_id = obj.object_idLEFT OUTER JOIN sys.server_principals AS sPrincON dPrinc.sid = sPrinc.sidFixing the OrphansUse database nameEXEC sp_change_users_login 'Report'If you already have a login id and password for this user, fix it by doing:EXEC sp_change_users_login 'Auto_Fix', 'user'

does it make difference nvarchar(50) or nvarchar(500)

Posted: 20 Aug 2013 07:49 PM PDT

dear friends,I have a large table with more than 10 million records and all fields are "string". I import data from csv file once in a month and this table will only be read by clients (web application)I used nvarchar(500) to be sure that field length will be enough in future. then I tried to experiment "what happens if I reduce the filed size to nvarchar(50)" ?I shrink database & files, changed 5 column types (among 50 columns) to nvarchar(50), shring again and mdf file size didn't changed. its meaningful because nvachar is not fixed length.my question is should I decrease the field sizes ? does it make any sense ? does it harm if I used "more than necessary" nvarchar lengths ?any suggestions ?thanks...

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

Biztalk Adapter Pack Data Provider for SAP

Posted: 20 Aug 2013 11:23 PM PDT

We are migrating from AS400 to SAP and need to include some of the SAP Master and Transactional data in our SQL Server 2012 data warehouse. We started down the path of using SAP standard and custom extractors to pull data from SAP into BW, then use SSIS packages to pull the data from BW to the data warehouse. This works but is slow to build and execute and there are some space concerns using this method. So we started researching other methods to pull data directly from SAP into the data warehouse. We found the BizTalk Adapter pack with the Data Provider for SAP and have installed it. It seems to work very well and performs much faster than the BW method. Have any of you used the BizTalk Adapter Pack Data Provider for SAP in SSIS packages to pull data from SAP? Can you share your thoughts on how well it worked, any problems you had, pros and cons of using this Data Provider vs. BW, etc.? We want to be sure we do our due diligence before we commit to this product.Thanks,Nancy

about exam 70-457 books

Posted: 07 Aug 2013 10:59 AM PDT

I plan to take 70-457 soon and search books about it.I download "Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012_PDF.pdf" from microsoft website. There are 2 parts in this book.Are these part1 for exam 70-457 and part2 for 70-458?I just read part1, but I saw somebody said there is no reference book for these exams, so I want to make sure.Thanks a lot.

ALTER PARTITION MERGE .... works in SSMS but not SQLAgent Batch

Posted: 20 Aug 2013 10:52 PM PDT

The following is performed on a SQL Server 2012 SP1 installation.I have been having an issue with stored procedure that performs a 'sliding window' solution on a partitioned table that is partitioned by date. The procedure will successfully execute in SSMS but identical TSQL executed in a SQL Agent job will fail with the following error:Executed as user: NT SERVICE\SQLAgent$SQL2012. The specified partition range value could not be found. [SQLSTATE 42000] (Error 7715). The step failedThe dynamic SQL statement it fails on is: [ALTER PARTITION FUNCTION SharepointStatsPF() MERGE RANGE ('01/09/2010');I have identified the cause of my problem; all logins explicitly created have the default language set to 'British English' whereas the 'NT SERVICE\SQLAgent$SQL2012' login, created by the installation process has the default language set to 'English'. Changing this to 'British English' resolves the problem.My question is, if the default language of the 'NT SERVICE\SQLAgent$SQL2012' login be permanently left as British English, would this cause problems elsewhere?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog