Recently in SqlServer Category
I've seen code in pre-Sql Server 2005 (i.e. 2000) where PIVOT-esque functions were carried out and my god were they complicated, messy, HORRID beasts. Put quite bluntly, if I never have to see that code again it'll be far too soon.
I was only reminded of this by coming across a post by Dan Wahlin titled "Pivots to the Rescue - Providing Flexible SQL Server Queries" that gives a good explanation and use-case for them. Something that I'm sure I'll find a use for in the near future!
I was only reminded of this by coming across a post by Dan Wahlin titled "Pivots to the Rescue - Providing Flexible SQL Server Queries" that gives a good explanation and use-case for them. Something that I'm sure I'll find a use for in the near future!
One of the useful system stored procedures in Sql Server is the sp_spaceused sproc for, funnily enough, seeing how much space a table or database is using. I recently found a MySql equivalent in the MySql Forums, 'Stored procedure for reporting database/object storage usage'.
Previously: Sql Server 2005 - Surface Area Configuration won't save
Just a quick update; installing Service Pack 3 (yes, I know I said SP2 in the previous post, my bad!) was a non-starter as the SP install attempted to connect to the instance and failed. The message did point me in the direction of the Sql Native Client being corrupted.
Uninstalling and reinstalling the Native Client allowed the service pack to install, allowed the local Sql Server Management Studio to connect and also allowed me to change the "allow external connections" from "Local Only" to "Local & Remote Connections" on both named pipes and TCP/IP. Unfortunately, I can't prove that this allowed external connections to succeed as I didn't have access to another machine on the network.
This morning, the machine is once again refusing to save the "external connections" settings in the Surface Area Configuration Manager, again! :( (This may have something to do with anothe person working on SSL on IIS on the box concerned though, perhaps?)
Just a quick update; installing Service Pack 3 (yes, I know I said SP2 in the previous post, my bad!) was a non-starter as the SP install attempted to connect to the instance and failed. The message did point me in the direction of the Sql Native Client being corrupted.
Uninstalling and reinstalling the Native Client allowed the service pack to install, allowed the local Sql Server Management Studio to connect and also allowed me to change the "allow external connections" from "Local Only" to "Local & Remote Connections" on both named pipes and TCP/IP. Unfortunately, I can't prove that this allowed external connections to succeed as I didn't have access to another machine on the network.
This morning, the machine is once again refusing to save the "external connections" settings in the Surface Area Configuration Manager, again! :( (This may have something to do with anothe person working on SSL on IIS on the box concerned though, perhaps?)
Has anyone ever see that?
An instance of Sql Server 2005 that I have is refusing connections with "No process is on the other end of the pipe". A bit of googling revealed that this might be because the instance was/is set to only accept local connections. Great, except for the fact that I was trying to connect locally!
Now, I tried opening up the Surface Area Configuration Manager and lo-and-behold, it's showing "Local Connections" as the only allowabe way in. Changing it to "Local and Remote" for both named pipes and TCP/IP, hitting save and restarting the instance resulted in the same error message from Sql Server Management Studio. Re-visiting the Configuration Manager, it appears that it either didn't bother to save the configuration, or it was being wiped by something.
Next thing to try: Sql Server 2005 Service Pack 2!!
An instance of Sql Server 2005 that I have is refusing connections with "No process is on the other end of the pipe". A bit of googling revealed that this might be because the instance was/is set to only accept local connections. Great, except for the fact that I was trying to connect locally!
Now, I tried opening up the Surface Area Configuration Manager and lo-and-behold, it's showing "Local Connections" as the only allowabe way in. Changing it to "Local and Remote" for both named pipes and TCP/IP, hitting save and restarting the instance resulted in the same error message from Sql Server Management Studio. Re-visiting the Configuration Manager, it appears that it either didn't bother to save the configuration, or it was being wiped by something.
Next thing to try: Sql Server 2005 Service Pack 2!!
