Tuesday, May 7, 2013

[SQL Server 2008 issues] Select only 1 row from multiple 'OR' {satisfied} conditions

[SQL Server 2008 issues] Select only 1 row from multiple 'OR' {satisfied} conditions

Select only 1 row from multiple 'OR' {satisfied} conditions

Posted: 06 May 2013 05:47 PM PDT

Hello,I came across a situation in SQL 2008 to fetch only single row from a table even if each of the multiple OR condition satisfies for multiple records.[code="sql"]Insert into #Table1 (Col1, Col2, Col3) values (1, 2, 'Test')Insert into #Table1 (Col1, Col2, Col3) values (1, NULL, 'Test')Insert into #Table1 (Col1, Col2, Col3) values (NULL, NULL, 'Test')Select *from #Table1Where ((Col1 in (1, 2) and Col2 in (1, 2)) or ((Col1 in (1, 2) and Col2 is null) or (Col2 in (1, 2) and Col1 is null)) or (Col1 is null and Col2 is null)) and Col3 = 'Test'[/code]In the above code, lets says I have three records in table and each record satisfies only one but any condition mentioned in OR cases. If I execute the script above it will give me all three records. What I need is: Compare all records in tableIf all conditions in OR case satisfies, then display the record that satisfies the first OR condition only. If second and third conditions in OR case satisfies, then display the record that satisfies the second OR condition only.If only the third condition in OR case satisfies, then display that record only.Is it possible to do that in sql without using multiple selects for each condition? In above case, i need to display only the first record which satisfies (Col1 in (1, 2) and Col2 in (1, 2))

Need help rebuilding a SQL server with replicated databases

Posted: 02 May 2013 12:09 AM PDT

We have a production SQL server in a virtualized environment and we need to move it to a physical server to address performance issues with an application that accesses the database.The server has 3 user databases 2 of which are replicated. We are using transactional replication (one way) and the distributor is on a different server. We use the replicated copies of the database for reporting, and the published databases are currently approaching 200G in size. We have mad some changes to replication to make reporting easier (We convert xml stored in varchar(max) columns to xml during replication, and have persisted calculated columns added on the subscriber. Overall, we would expect to spend a couple days rebuilding replication if it comes down to that. We would prefer to be able to simply replace the publisher with the new physical server and continue on.Does anyone have any suggestions for a process that would allow us to do that, or will we just need to bite the bullet and rebuild replication after the publisher is recreated?thanks, Kevin

SQL Server outof space question.

Posted: 29 Apr 2013 12:52 AM PDT

Hello Experts,One of our clients server ran into this issue. The server has both application and SQL Server installed on it. The hard drive is completely full and we don't know what is causing it to be full but because it is full, we can't login to the SQL Server because there is not enough space to write to the transaction log. We can't stop the MSSQLSERVER service because there's no space and there's not anything I know of clearing to be able to clear enough space so we can delete the transaction log. What else could I do other than attaching another HDD and move/add data/log file to the new volume?Thanks in advance!

Reporting Services configuration manager 2008 URL Issues

Posted: 18 Jun 2009 01:49 AM PDT

HII got two XP sp3 machine having IIS 5.1 I install SQL 2008 on it and Configure my Report server by using Reporting Services Configuration Managerwith following settingServices account = Local SystemWeb URL = All assignedTCP port 8080Reporting mode = nativeSoand I got following URL http://level2/ReportServerwhen it click http://level2/ReportServer I can able to access My report and view reportbut when i try to enter same URL on Different machine i cant able to open reportI can able to see SQl database for this machice and able to ping the two machineI dont have any Domain account on my machine..i will be really helpful pls help me...

Naming the column for xml result set

Posted: 06 May 2013 04:25 AM PDT

I am trying to get the proper column name for my xml string.Kindly helpSELECTx + ' ' AS [text()]FROM(SELECT 'a'UNION ALLSELECT 'b') AS y (x)FOR XML PATH('')

Help with SQL Server backups slow ( backup performance slower than before after data purge)

Posted: 06 May 2013 08:09 AM PDT

Hi everyone, (searched SQL Central... I didn't find anything specific)Wanted to get some ideas on solutions. I have a DB about 3TB in size which usually took about 55-65mins on average to back up on SAN.A data file was added because due to low on space. A 500GB was added with no auto-growth (managed actively) ... other data files existing in past are allocated as 500GB files. --- backup was normal 55-65 mins on averageAfterwards sunday data purged 500GB -- backup ran slow after ..nearly about 2 hoursThen purged another sunday 500GB---same thing slow about about 2 hoursFinally another sunday 200GB -- same thing slow about 2 hours The database has indexes rebuilt during the weekend by a custom database script created by the vendor. The files are allocated 500GB so I thought fragmentation would be more logical than physical. However with indexes rebuilt so there should be less logical fragmentation in DB.This DB is backed up to a SAN. Perfmon shows higher than normal I/O responses times...BUT ONLY for this 3TB DB trimmed to 1.8TB. Because there's a Database approximately 500GB on same SAN and LUNs/Mountpoints... with no degradation in Backup performance. (No purging for 500GB DB) ALSO I skimmed at the custom re-index by vendor, it seems to rebuild the index for the table that had the data purged from it. (FYI data purge was to specific DB table)Idea/comments suggestions :blush:thanks for everyone's time!

SQL Server 2008 R2 Enterprise Installation Recommendation

Posted: 06 May 2013 06:56 AM PDT

Hello Peeps, I would appreciate if you guys can recommend a good installation setup for SQL Server 2008. Below are the Hardware/Software Specs.Specs:1. Sql Server 2008 R2 Enterprise 2. Intel Xeon 3.3GHZ - 16GB Ram3. Primary Drive - 2 * 1TB HD (RAID 1)4. Secondary Storage - 1TB single drive (another volume)Given:1. DB will be using Simple Recovery as we don't care to lose a days worth of data.2. We will not be using SQL Server's other Services (Replication, Analysis etc)Question:1. Where should we install the data files? In the Primary where I can benefit from RAID 1 or Secondary Storage where it will be separate from the OS except I dont have redundancy (although I can run a backup every night and I as mentioned, dont care if we lose some data)?2. Any other config options I should be considering?Thanks a bunch!

? on running a sp in dbmail

Posted: 06 May 2013 04:58 AM PDT

Hi ,I am trying to figure out how I can attach a file as an XML in dbmail and run a query that would produce the XML fileI found this example to get some out put and put it in an SPUSE [Test]GO/****** Object: StoredProcedure [dbo].[XML_Test_3] Script Date: 05/06/2013 13:53:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[XML_Test_3] -- Report ParametersAS SET NOCOUNT ON SET ANSI_WARNINGS OFF SELECT ( SELECT 'White' AS Color1,'Blue' AS Color2,'Black' AS Color3,'Light' AS 'Color4/@Special','Green' AS Color4,'Red' AS Color5FORXML PATH('Colors'),TYPE),( SELECT 'Apple' AS Fruits1,'Pineapple' AS Fruits2,'Grapes' AS Fruits3,'Melon' AS Fruits4FORXML PATH('Fruits'),TYPE)FOR XML PATH(''),ROOT('SampleXML')I want the attachment to be an XML file with this output <SampleXML> <Colors> <Color1>White</Color1> <Color2>Blue</Color2> <Color3>Black</Color3> <Color4 Special="Light">Green</Color4> <Color5>Red</Color5> </Colors> <Fruits> <Fruits1>Apple</Fruits1> <Fruits2>Pineapple</Fruits2> <Fruits3>Grapes</Fruits3> <Fruits4>Melon</Fruits4> </Fruits></SampleXML>ThanksJoe

2008 R2 upgrade advisor error

Posted: 26 May 2011 10:35 PM PDT

HiI have just started getting issues with the SQL 2008 R2 upgrade advisor. This tool has previously worked ok for me and a colleague who also has it installed (both windows 7 64 bit PC's). We have used the advisor on 16th Feb 2011 with no issues. We both now get the error below. I have tried multiple reboots and uninstalls with no help. The 2008 upgrade advisor works fine. There is one unanswered post on the Microsoft forums about this issue dated 25th May [url=http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/67a5f414-3b8e-4e9a-85b4-da6915e8cbd4/][/url] .[quote]"Method not found:'System.Collections.Generic.IEnumerable `1<Microsoft.SqlServer.Management.SqlParser.Parser.Error>Microsoft.SqlServer.Management.SqlParser.SqlCodeDom.SqlCodeObject.get_Errors()'[/quote]Microsoft.SqlServer.Management.SqlParser.Parser.dll in c:\Windows\Assembly is listed version 10.0.0.0 but has file version 10.50.1750.9 the version in C:\Program Files (x86)\Microsoft SQL Server 2008 R2 Upgrade Advisor\BPA\bin is version 10.50.1600.1Has anyone else encountered this problem?

SQL Audit Help - Does SQL Audit Really Store Audit Info ON The Windows Logs

Posted: 06 May 2013 07:00 AM PDT

First off I admit I am no SQL Audit guru but from what I have read so far it sounds to me like as if the SQL Audit feature in SQL 2008 R2 is more for traditional programmers then DB admins. If I read this info on SQL Audit correctly the info captured by SQL Audit is stored ONLY in Windows Event Logs. Can anyone familiar with SQL Audit confirm this or correct the statement if its incorrect? I can't believe that the only option for storage of audit info would be the windows log. Maybe it's just me but this seems like a decision few if any DB Admins would make. It does however sound like something traditional programmers would lovely embrace because it lets them avoid working with SQL language and Relational DB's. What I've always done (for the handful of tables we need to audit) is to create duplicate of the table and populate it with data whenever a DML change occurs via the use of triggers. I know that CDC would be great for doing this but it leaves out the WHO and so while you know what happened with your data when using CDC you have no idea who made the change and that's the other critical piece to auditing for us. Does SQL Audit allow only for storing in the Windows Event Log?

FOR XML EXPLICIT Help

Posted: 06 May 2013 02:07 AM PDT

Hello,I have the below SQL which is working great except for the <Type> element. All the types are appearing on the very last record rather than listed for each <BookingID> as I would like. Any advice or help is appreciated!Here is the SQL:SELECT 1 AS Tag, NULL AS Parent, NULL AS 'root!1!', NULL AS 'Event!2!BookingID!Element', NULL AS 'Event!2!EventName!cdata', NULL AS 'Event!2!TimeBookingStart!Element', NULL AS 'Event!2!TimeBookingEnd!Element', NULL AS 'Event!2!URL!Element', NULL AS 'Event!2!Description!cdata', NULL AS 'Event!2!Room!Element', NULL AS 'Event!2!Audience!Element', NULL AS 'Type!3!!cdata' UNION ALLSELECT DISTINCT 2 AS Tag, 1 AS Parent, NULL, BookingID, WebTitle, TimeBookingStart, TimeBookingEnd, URL, [Description], Room, Audience, NULLFROM WEBSITE_EVENTS2UNION ALLSELECT DISTINCT 3 AS Tag, 2 AS Parent, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, EventTypeFROM WEBSITE_EVENTS2FOR XML EXPLICITXML Output: (The <Type> for bookingId 14991 are displaying under bookingId 14992) <Event> <BookingID>14991</BookingID> <EventName><![CDATA[After Work Expeditions]]></EventName> <TimeBookingStart>2013-07-17T14:30:00</TimeBookingStart> <TimeBookingEnd>2013-07-17T16:00:00</TimeBookingEnd> <URL></URL> <Description><![CDATA[Text description here]]></Description> <Room>Room 1300L</Room> <Audience>Public</Audience> </Event> <Event> <BookingID>14992</BookingID> <EventName><![CDATA[Trustee Meeting]]></EventName> <TimeBookingStart>2013-07-19T11:00:00</TimeBookingStart> <TimeBookingEnd>2013-07-19T12:00:00</TimeBookingEnd> <URL></URL> <Description><![CDATA[This event is closed to the public. ]]></Description> <Room>Room 1300L</Room> <Audience>Private</Audience> <Type><![CDATA[Arts & Theatre]]></Type> <Type><![CDATA[Conference]]></Type> <Type><![CDATA[Lectures & Discussions]]></Type> <Type><![CDATA[Music & Entertainment]]></Type> </Event>There is a separate record for each bookingID/Type in the database. For example:BookingID EventType--------- -----------14991 Music & Entertainment14991 Arts & Theatre14992 Lectures & Discussions14992 Conference

SOX2 Compliance - SSAS Cube

Posted: 06 May 2013 02:35 AM PDT

We have enabled TDE on all databases that contain PII data for SOX2 compliance. However, how do we do this for the SSAS cube? We can clearly see that data is not encrypted in the files for the cube. Has anyone had to do this? There is very little information out there. For those of you who do not know... We do not need the data encrypted during transfer. This is simply if someone copied or stole the files, could they restore them or view them after the fact.

syntax needed

Posted: 06 May 2013 12:29 AM PDT

The uncommented part is not workingdeclare @str_folder_path varchar(1000)='\\Dataupload' ,@str_media_data_file_path_csv varchar(200)='Data File.csv'BULK INSERT vw_pkg_dataload -- FROM '\\Dataupload\Data File.csv' FROM '''' + @str_folder_path + '\' + @str_media_data_file_path_csv +'''' WITH ( FIRSTROW = 2, FIELDTERMINATOR ='|',ROWTERMINATOR = '' )

Concept of Session killing by Sql server.

Posted: 05 May 2013 08:48 PM PDT

Hi,I had make changes in server connection properties and set timeout time 300 Secs.Now i had connect to a database (say abcd) through query window ran one query on that and see number of session through sp_who2 in new query windows (master) and i got 3 session for that database(abcd).now i closed the query window(abcd) and i still got 2 session in sleeping state.Can anyone explain why it still shows in session, I am from Oracle background and as per my knowledge all session should be killed if i disconnect for that session.

SQL Server 2008 R2 Evaluation Edition Expired

Posted: 05 May 2013 09:09 PM PDT

Hi,I have installed SQL SERVER 2008 R2 Enterprise Edition French Version on my windows server 2008 R2 Standard Edition FRENCH Version. Now the evaluation period has expired and I cannot open SSMS anymore. I have bought a new device of SQL SERVER 2008 R2 Standard Edition English version with a valid Product Key of License. I would like you to explain me how I can [b]upgrade[/b] my SQL SERVER 2008 R2 FRENCH Version From Evaluation to a Standard Edition With my new product Key.Please I would not like to lose any data because my databases are in production.Thanks in advance.

Each client on its own VLAN

Posted: 05 May 2013 09:07 PM PDT

Hi guys, I'm going to be starting a new DBA job soon and thre's one aspect of it that I've never seen before. My employer will be a cloud hosting outfit and every customer apparently has their own instances on a separate VLAN i.e. they wont be able to see eachother.I've never managed a SQL estate laid out like this before. My question is what will I need in order for me to setup a central management server so i can see all these instances as one group in SSMS and run DBA queries against the whole etc?WHen I Googled SQL Server and VLAN there was not much coming back which made me wonder if every customer in its own VLAN was a common configuration for SQL Server.Anyway any pointer / tips would be very welcome!!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog