[MS SQL Server] SQL Server Browser |
| Posted: 11 Sep 2013 08:34 PM PDT Hi,I am working with a cluster with 2 nodes, active-active. The nodes have several instances and in total 3 different SQL server versions (SQL2005, SQL2008 and SQL2008R2); not an ideal situations but when I setup the cluster I needed a SQL2005 for one specific applications. Now I want to remove versions SQL2005 and SQL2008 so that I can install an extra instance 2008R2 and the new 2012 and that the end result is only 2 different SQL versions.The SQL Server Browser that is used is the one that is installed with 2005. What is the best way to install and configure the browser for 2008R2 and remove the browser that runs now on 2005? Can this be done while the cluster is working? When I failover all instances to one node, can I perform the install and uninstall on the passive node, fail over again and repeat everything for the other node? Or is it better to do this past working hours?I suppose I cannot have 2 browsers working at the same time since they would use the same port to listen. Any advise is welcome. Thanks.Vera |
| Transaction Isolation question Posted: 11 Sep 2013 08:24 AM PDT The isolation level of the database that I'm running the query on is READ COMMITTED. I ran query 1 and query 2.Query 1 is IF OBJECT_ID('tempdb..##my_name') IS NOT NULLBEGIN DROP TABLE ##my_name;END;CREATE TABLE ##my_name( id INT, first_name VARCHAR(20));INSERT INTO ##my_name (id, first_name)VALUES (1, 'dexter');BEGIN TRANUPDATE ##my_nameSET first_name = 'derek'WHERE id = 1;Query 2 is SELECT * FROM ##my_name WITH (NOLOCK);SELECT * FROM ##my_name;My question is that I was expecting "SELECT * from ##my_name" to hang without generating results because I haven't ran COMMIT TRAN or ROLLBACK TRAN yet. But I'm getting results as dirty read. I'm little confused here. Can someone help me out? |
| Change NULLS to blank in Dynamic Pivot Results Posted: 11 Sep 2013 06:44 AM PDT Subject says it all. I would like to change the NULLS to blank in my Dynamic Pivot results. Here is a small sample of data from my DistinctPages table:[code="other"]pageno groupid customized----------- ---------- ----------101105 cshaffre x105101 cshaffre x105110 cshaffre x122566 cshaffre x100100 darryl x101100 darryl x103800 darryl x100110 darryl x122201 dbouma x101151 dbouma x107102 dbouma x104102 dbouma x105121 dbouma x[/code]Here is the dynamic code I have:[code="sql"]DECLARE @query VARCHAR(4000), @groupids VARCHAR(8000)SELECT @groupids = STUFF(( SELECT DISTINCT '],[' + LTRIM(groupid) FROM DistinctPages ORDER BY '],[' + LTRIM(groupid) FOR XML PATH('') ), 1,2, '') + ']' SET @query ='SELECT * FROM (SELECT pageno, groupid, customized FROM DistinctPages)tPIVOT (MAX(customized) FOR groupidIN ('+@groupids+')) AS CustomizedPagesPerGroups'EXECUTE (@query)[/code]Here is a small sample of my pivot table results:[code="other"]pageno cshaffre darryl dbouma dsanders eliteadm FIRM french Gerry Ilee Irene lgentry matt mduran Mine mpadilla mwilton pmp rlandsin spanish sverne swachman train train01 vicky vlad VTest vvargas ymalluf----------- -------- ------ ------ -------- -------- ---- ------ ----- ---- ----- ------- ---- ------ ---- -------- ------- ---- -------- ------- ------ -------- ----- ------- ----- ---- ----- ------- -------100100 NULL x x x x x x x x NULL x x NULL NULL x x NULL x x x x x x x x x x NULL100101 NULL NULL NULL NULL x x x NULL NULL NULL NULL NULL NULL NULL NULL NULL x x x NULL NULL NULL NULL NULL x NULL NULL NULL100102 NULL NULL x NULL x x x x x NULL x x NULL NULL x NULL NULL x x x x x NULL NULL NULL NULL x NULL100104 NULL NULL NULL NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL NULL NULL100110 NULL x x NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL x NULL100113 NULL NULL x NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL NULL NULL100115 NULL NULL NULL NULL NULL NULL x NULL x NULL NULL NULL NULL NULL NULL NULL NULL NULL x NULL NULL NULL NULL NULL NULL NULL x NULL[/code]I would like to replace the NULLs you see with a blank or some other text that makes it easier to read.I was looking into ISNULL and COALESCE but I have not been able to implement it correctly.Can someone take a look at my code and give me an example of how to do what I need with either ISNULL or COALESCE? Please let me know if you need anything else. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment