Friday, August 9, 2013

[SQL 2012] Backward compatibility mode for SQL Server 2012

[SQL 2012] Backward compatibility mode for SQL Server 2012

Backward compatibility mode for SQL Server 2012

Posted: 09 Aug 2013 01:39 AM PDT

I got a requirement that I need to install SQL 2012 and make sure that its compatible to host SQL 2008 R2 applications only.I went to the model database properties and changed the compatibility and tested creating a new database and that got created in SQL 2008 mode.Was this method right?Also, is there a seperate compatibility mode for SQL 2008 and SQL 2008 R2?

Always On Primary Offline = Secondary Offline

Posted: 09 Aug 2013 01:57 AM PDT

Setup FCI01 – SEVER A,B,C,D Named Instance 1- Primary ReplicaPreferred Nodes A and BFCI02- SEVER A,B,C,D Named Instance 2 – Secondary ReplicaPreferred Nodes C and D.Quorum Disk- For VotingSERVER E – DFR server holding a secondary replica.Always On Listener – on NODE A – NAMED INSTANCE 1FCI01 -Primary running on Node AFCI02 – Secondary running on Node CSERVER E – Secondary DR replica.1 Primary 2 Secondary copies.----------------------------------------------------------------------------------Perform a manual Windows Failover to NODE B. The Always On Listener goes offline.This can be resolved by reverting back to NODE A. Or failing over the listener to convert the Secondary AOG to the AOG Primary before doing the Windows failover . Perform the windows failover the Fail to NODE B. Then fail the AOG back to Instance 1 . Always On Listener will now be running on Node B.However if there was a hardware failure to NODE A while it was running instance 1 and the Always On Listener was using this node A. How to recover the listener as it will be pointing to Node A as it is not windows cluster aware and only recognises the node name. Databases are in SYNC Pending mode and all dbs in the AOG are not accessible.Primary offline = Secondary offline. I know this is fixed in SQL 2014 but what is the work around?

Query returning one extra record

Posted: 08 Aug 2013 06:25 PM PDT

I have a query that is returning two copies of the last record. The record exists only once - there is an index on the table to prevent two such records, and examining the actual table confirms that it is present only once. The field [EvidenceLetter] selects groups of related records. No other value besides 'E' exhibits this behavior.It is definitely the same record - my application displays the unique autonumber primary key, a when I select one (either one) of the duplicates in the application and make a change, the change is immediately reflected in both displayed copies of the record.Both the application AND SSMS give the same results. I can't think of anything that might be causing such a thing. Can anyone give me some ideas on where to start looking for the problem?I call it from a MSAccess application as a pass-through query, and from SSMS via an EXEC statement, like this:[code="vb"]Exec NoFiltr.spPodrobnosti @What = 'Zaznamy', @LtrFiltr = 'E'[/code]The execution plan is attached, and here is the query generated and executed by the stored procedure:[code="vb"]SELECT * FROM ( SELECT P.PodrobnostiAutoID, P.EvidenceLetter, P.EvidenceNumber, P.EvidenceExtra, P.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok, P.PocetKusu, P.NepublikovatYN, P.Lokalita, tT.Zkratka Typ, P.Original, P.Popis, P.Poznamka, P.SystemAutoID, P.SeriesAutoID, P.StageAutoID, P.SubStageAutoID, P.LithographicUnitAutoID, P.LithographicSubUnitAutoID, P.ZoneAutoID, P.GroupAutoID, P.OrderAutoID, P.FamilyAutoID, P.GenusAutoID, P.SubGenusAutoID, P.SpeciesAutoID, P.SubSpeciesAutoID, P.ODAutoID, P.PDAutoID, P.OriginAutoID, P.TypAutoID, case when P.EvidenceLetter Is Not Null then P.EvidenceLetter + ' ' + convert(varchar(5),P.EvidenceNumber) + case when P.EvidenceExtra <> '' then ' (' + P.EvidenceExtra + ')' else '' end else '' end AS DrEvid, case AkcesitPred when 'A' then A.AkcesitPred + '/' when 'Br' then A.AkcesitPred + '-' else '' end + convert(varchar(5),A.Akcesit) + '/' + convert(varchar(4),A.Rok) AKC, CASE WHEN tDF.DepozitarAutoID IS NULL THEN '' ELSE tDF.DepozitarFormatted END + CASE WHEN UlozisteDocasne IS NULL THEN '' ELSE Replace(Replace(' {' + UlozisteDocasne + '}','{{','{'),'}}','}') END AS Ulozeni, tNI.Clovek + ', ' + left(CONVERT(varchar(20), tNI.Datum, 21),10) As Inventarizace, dbo.fnsTaxonomieFormatted(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS Taxonomie, dbo.fnsTaxonomie(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS TaxonomiePlain, dbo.fnsStratigrafie(tSy.[System], tSe.Series, tSt.Stage, tSSt.SubStage, tLU.LithographicUnit, tLSU.LithographicSubUnit, tZo.Zone) AS Stratigrafie FROM Podrobnosti P INNER JOIN dbo.fntStringsToTable ('E') L on L.EvidenceLetter = P.EvidenceLetter INNER JOIN dbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID LEFT JOIN vwTableOfDepozitars tDF ON P.DepozitarAutoID = tDF.DepozitarAutoID LEFT JOIN vwNejnovejsiInventarizace tNI ON P.PodrobnostiAutoID = tNI.PodrobnostiAutoID LEFT JOIN TableOfGenuss tGe ON P.GenusAutoID = tGe.GenusAutoID LEFT JOIN TableOfSubGenuss tSGe ON P.SubGenusAutoID = tSGe.SubGenusAutoID LEFT JOIN TableOfSpeciess tSp ON P.SpeciesAutoID = tSp.SpeciesAutoID LEFT JOIN TableOfSubSpeciess tSSp ON P.SubSpeciesAutoID = tSSp.SubSpeciesAutoID LEFT JOIN TableOfFamilys tFa ON P.FamilyAutoID = tFa.FamilyAutoID LEFT JOIN TableOfOrders tOrd ON P.OrderAutoID = tOrd.OrderAutoID LEFT JOIN TableOfGroups tGr ON P.GroupAutoID = tGr.GroupAutoID LEFT JOIN TableOfSystems tSy ON P.SystemAutoID = tSy.SystemAutoID LEFT JOIN TableOfSeriess tSe ON P.SeriesAutoID = tSe.SeriesAutoID LEFT JOIN TableOfStages tSt ON P.StageAutoID = tSt.StageAutoID LEFT JOIN TableOfSubStages tSSt ON P.SubStageAutoID = tSSt.SubStageAutoID LEFT JOIN TableOfLithographicUnits tLU ON P.LithographicUnitAutoID = tLU.LithographicUnitAutoID LEFT JOIN TableOfLithographicSubUnits tLSU ON P.LithographicSubUnitAutoID = tLSU.LithographicSubUnitAutoID LEFT JOIN TableOfZones tZo ON P.ZoneAutoID = tZo.ZoneAutoID LEFT JOIN TableOfTyps tT ON P.TypAutoID = tT.TypAutoID ) PP WHERE 1=1 Order By PP.EvidenceLetter, PP.EvidenceNumber, PP.EvidenceExtra[/code]

SQL Server Benchmark Testing - Urgent

Posted: 08 Aug 2013 02:11 PM PDT

[b]Is SQLIOsim an older tool? Is SQLIOStress a newer tool? I want to stress test a SQL 2012 environment which is a VM environment.Also, other than HammerDB, is there another TPC-C tool which is a freeware?Any other ideas/tools for SQL Server bench mark testing?[/b]I am trying to test one of our environment which is pre-production and is a clustered environment and hooked up to SAN which is RAID-DP.The following are the statements I am trying to run and it errors out saying there is not enough space on the drive even though it has about a TB free.This is what I have in params.txtL:\sqlIO\testfile.dat 2 0x0 100This is what I have in the batch file which contains the sqlio commandsqlio -kW -t16 -s180 -dl -o8 -frandom -b16 -BH -LS Testfile.dat[b]Error is:[/b]do_ovlp_io: WriteFileEx: There is not enough space on disk.file offset high: 0x00000493 low: 0x3a54c000As you can see even though I say 100MB file, it creates a test file for 3.5 GB or something and finally fails saying there is not enough disk space when the L drive contains about 935GB free.Any ideas?

using a multi-valued parameter in SSRS 2012

Posted: 08 Aug 2013 04:54 PM PDT

If I use a single-valued parameter, it works a champ.I'm trying (and apparently failing) to follow Brian Knight's example, which is here: [url=http://www.sqlservercentral.com/articles/Video/64369/][/url]I changed my stored procedure, so now it looks like this:[code="sql"]ALTER PROC uspEnrollReport @StartWeek INT, @EndWeek INT, @ProtocolList VARCHAR(1000)ASBEGIN WITH EnrollData (Protocol, StudyWeek, WeeklyGoal, ActualEnrolls) AS ( SELECT eg.ProtocolNo, eg.WeekNumber, eg.Goal, COUNT(e_PatientID) AS EnrollCount FROM EnrollmentGoal eg INNER JOIN enroll e ON eg.ProtocolNo=e_ProtocolNo AND eg.WeekNumber=PWeek GROUP BY eg.ProtocolNo, eg.WeekNumber, eg.Goal ) SELECT Protocol, StudyWeek, WeeklyGoal, ActualEnrolls, ActualEnrolls - WeeklyGoal AS OverUnder FROM EnrollData WHERE StudyWeek BETWEEN @StartWeek AND @EndWeek AND Protocol [b]IN (@ProtocolList)[/b];END[/code]I changed the parameter to be multi-value (check the box) - simple enough.Everything appears to work, but when I run the report, it only filters for the first checked item. What blindingly obvious thing am I doing wrong?Thanks!Pieter

"Run Immediately" Disabled in Import Wizard

Posted: 08 Aug 2013 07:57 AM PDT

I just started using SQL Server 2012 but have used 2000, 2005 and 2008 and have always used the import wizard to import Excel files. The "run immediately" option is disabled on the wizard so my only option is to save the import as an SSIS package. I do a lot of ad-hoc imports and don't really want to save any import as a package unless I will continually use it. My questions is how do you enable the "run immediately" option on the wizard? Thanks.

Rant: Can`t Wait Until They Bring MDI Windows Back to SSMS in 2012 ... But by then I Will Probably Be Gone ...

Posted: 08 Aug 2013 07:52 AM PDT

... after almost 15 years of using SQL Server As great as SQL Server is ... this is yet another reason I am switching to the LAMP stack. Aside: Anyone remember [ctrl-B]? Used to let you grab the splitter bar between the query window and the result pane instead of having to play air hockey with your mouse to grab the (nearly) invisible line to adjust the height (occurs to me that I should write a plug-in for that rather than just whine). No MDI: There is (another) rant here: http://connect.microsoft.com/SQLServer/feedback/details/713996/ssms-2012-bring-back-mdi-environmentThis is apparently the/a response by M$ "Posted by Microsoft on 12/15/2011 at 10:26 PMGot it.We lost this feature when SSMS moved to the VS2010 shell. Apparently, getting MDI back has been a strong ask on VS as well. See: http://connect.microsoft.com/VisualStudio/feedback/details/576371/put-mdi-back-asap Unfortunately, this is not something SSMS can fix on it's own until this feature comes back into the shell (which the VS team is looking at in their next release).I'm closing this bugs as external for now. Meanwhile, have you tried the MDI tabbed group feature? Right click on a tab, and choose to create a horizontal or vertical tab group. More info here: http://msdn.microsoft.com/en-us/library/cc165830.aspx"

SSRS - Values in X-axis and Y-axis

Posted: 08 Aug 2013 07:24 AM PDT

Hi Experts,I am new to SSRS. My requirement is to populate ElapsedTime column in Y-axis and Starttime column X-axis. Can you please let me know how to do it. I am confused with values, Series groups, category groupsAny input is appreciated

Always On troubles

Posted: 08 Aug 2013 05:26 AM PDT

Ok, I am about at my limit on this. I am trying to set up SQL 2012 Always On. I have tried setting it up with Two Instances on the same 2 node cluster. Did not work.Now I have two separate clusters. Cluster1 with the database I want to replicate to the other cluster. Cluster2 with no database yet.Getting an error that says The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster node. I get this message when I try to add the Cluster2 as a replica.What am I doing wrong?Thank you

Deny execution of a particular SQL statement

Posted: 08 Aug 2013 03:28 AM PDT

SQL Server 2012 Enterprise We have a database table/view that we want users to be able to select from, but we don't want them to be able to JOIN this object to other objects in a SQL statement.Any ideas on how to accomplish this? Custom error code triggered through a database level trigger? I don't see how this is possible using a DML, DDL or logon trigger.Evaluate SQL statements using extended events and then trigger a custom error code if needed? Is that possible?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog