June 13, 2011 at 8:57 am
I have a table thats evolving into a look up table for windows/sql servers. I'd like to include drive letters as some attributes to this table. I have these columns named Drive1,Drive2,Drive3..etc..
Columns are server name (sqlserver), WinServer (....windows server), Drive1,Drive2,etc..
I'm collecting free space per server in another table. It contains a list of server names, drive letters and space statistics.
Columns are WinServer, DriveLetter, Freespace
What I'm trying to accomplish with this code is to grab the latest statistics, Load them into a temp table ordered descending by drive letter.
Loop through this table, grab a count for WinServer occurances, name an idex variable as a concatenation of the word 'Drive' and the current value of index. After updating this value in the lookup table, delete the current record from the temp table so the index variable is updated accordingly. Please see the code below.
Is it possible to use the @wrkingDrive in the update statement as such?
DECLARE @drive varchar(10), @index int, @svr varchar(128), @wrkingDrive varchar(10)
CREATE TABLE #TMP_Drives (svr varchar(120),drv varchar(10))
INSERT INTO #TMP_Drives
Select ServerName,DriveLetter
from ServerDriveSpace
Where RunDT = '2011-06-13 10:00:05:467'
order by ServerName,DriveLetter desc
Select *
from #TMP_Drives
DECLARE crsr CURSOR FOR
Select *
from #TMP_Drives
OPEN crsr
FETCH NEXT FROM crsr
INTO @svr,@Drive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @index = ''
SET @index = (SELECT COUNT(*) from #TMP_Drives Where svr = @svr)
SET @wrkingDrive = 'Drive' + CONVERT(VARCHAR(1),@index)
PRINT @svr
UPDATE server_dim
SET @wrkingDrive = @drive
Where WinServer = @svr
DELETE FROM #TMP_Drives where svr = @svr and drv = @drive
FETCH NEXT FROM crsr
END
CLOSE crsr;
DEALLOCATE crsr;
DROP TABLE #TMP_Drives
June 13, 2011 at 10:46 am
So I'm realizing that i'm probably just redefining the @wrkingDrive variable to be equal to @drive.
This got me thinking to declare a variable to house the entire update statement. I was able to get this cranking with the following code:
DECLARE @drive varchar(10), @index int, @svr varchar(128), @wrkingDrive varchar(10),@SQL varchar(max)
CREATE TABLE #TMP_Drives (svr varchar(120),drv varchar(10))
INSERT INTO #TMP_Drives
Select ServerName,DriveLetter
from ServerDriveSpace
Where RunDT = '2011-06-13 10:00:05:467'
order by ServerName,DriveLetter desc
Select *
from #TMP_Drives
DECLARE crsr CURSOR FOR
Select *
from #TMP_Drives
OPEN crsr
FETCH NEXT FROM crsr
INTO @svr,@Drive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @index = ''
SET @index = (SELECT COUNT(*) from #TMP_Drives Where svr = @svr)
SET @wrkingDrive = 'Drive' + CONVERT(VARCHAR(1),@index)
PRINT @svr
SET @sql = 'UPDATE server_dim SET ' + @wrkingDrive + ' = ''' + @drive+ ''' WHERE WinServer = ''' + @svr + ''''
PRINT @sql
Exec (@SQL)
DELETE FROM #TMP_Drives where svr = @svr and drv = @drive
FETCH NEXT FROM crsr
END
CLOSE crsr;
DEALLOCATE crsr;
DROP TABLE #TMP_Drives
Select *
from server_dim
June 13, 2011 at 1:21 pm
I am guessing that this can be converted to a set based solution and get rid of that horribly slow cursor RBAR logic. Post some ddl and sample data along with desired output and there are tons of people here willing to help eliminate your **cough**cursor**cough**
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2011 at 6:52 am
If you feel like it, I'd love to see it. We're really only talking maybe 200 records though. I had initially created the server table as simply a list of SQL server instances. After creating the second table to house hard drive free space, I decided to add to this table DiskLetters and a threshold I could set that would signal an alert when free space was below the threshold.
The goal here would be to loop through the latest load of the second table and derive drive letters on a server basis to load into the original table which had now evolved into a look up table. This should get you going below.
For the Server lookup table:
/****** Object: Table [dbo].[server_dim] Script Date: 06/14/2011 08:07:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[server_dim](
[server_name] [varchar](50) NOT NULL,
[WinServer] [varchar](128) NULL,
[Drive1] [varchar](10) NULL,
[Drive2] [varchar](10) NULL,
[Drive3] [varchar](10) NULL,
[Drive4] [varchar](10) NULL,
[Drive5] [varchar](10) NULL,
[Thresh1] [numeric](10, 5) NULL,
[Thresh2] [numeric](10, 5) NULL,
[Thresh3] [numeric](10, 5) NULL,
[Thresh4] [numeric](10, 5) NULL,
[Thresh5] [numeric](10, 5) NULL,
[Drive6] [varchar](10) NULL,
[Thresh6] [numeric](10, 5) NULL,
CONSTRAINT [PK_server_dim] PRIMARY KEY CLUSTERED
(
[server_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into server_dim (server_name,WinServer) VALUES ('SQLServer1','Winbox1')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer2','Winbox2')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer3','Winbox3')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer4','Winbox4')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer5','Winbox5')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer6','Winbox6')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer7','Winbox7')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer8','Winbox8')
insert into server_dim (server_name,WinServer) VALUES ('SQLServer9','Winbox9')
For server Drive space
/****** Object: Table [dbo].[ServerDriveSpace] Script Date: 06/14/2011 08:14:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerDriveSpace](
[RunDT] [datetime] NULL,
[ServerName] [varchar](128) NULL,
[DriveLetter] [varchar](5) NULL,
[DriveSize] [numeric](20, 5) NULL,
[FreeSpace] [numeric](20, 5) NULL,
[PercentFree] [numeric](20, 5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox1','C:','15359.74609','239.69531','1.56054')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox1','E:','153596.99609','58392.35937','38.01660')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox2','C:','69973.57421','55464.53515','79.26497')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox2','E:','819196.99609','705736.67578','86.14981')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox2','F:','409593.99609','369015.44921','90.09298')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox2','G:','76794.99609','59683.69140','77.71820')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox2','H:','51197.99609','50861.44921','99.34265')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox3','C:','15359.74609','3400.92968','22.14183')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox3','E:','1048569.99609','714730.28125','68.16238')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox3','F:','1048569.99609','632576.59375','60.32755')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox3','G:','76794.99609','64076.10937','83.43787')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox3','H:','71671.99609','69035.07421','96.32084')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox4','C:','139975.05859','129733.54296','92.68332')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox4','E:','176999.99609','37303.03906','21.07516')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox4','F:','169999.99609','61747.77343','36.32222')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox4','G:','89999.99609','89279.48046','99.19942')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox4','H:','54517.99609','51408.41015','94.29622')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox5','C:','20001.54296','5595.54687','27.97557')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox5','D:','12000.93359','1698.06640','14.14945')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox5','E:','51198.96875','11068.49218','21.61858')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox6','C:','10945.05859','2394.77343','21.87995')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox6','D:','5123.88671','5.60937','0.10947')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox6','E:','10235.99609','4556.48437','44.51432')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox6','P:','53900.62109','36202.87890','67.16597')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox7','C:','25599.58984','13902.57031','54.30778')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox7','D:','10243.82421','8143.80468','79.49965')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox7','E:','511992.99609','510229.14062','99.65549')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox7','F:','34126.16796','34057.47656','99.79871')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox8','C:','25603.56250','9140.78515','35.70122')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox8','D:','10244.57421','8144.53515','79.50096')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox8','E:','102397.99609','22807.61328','22.27349')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox8','F:','34114.58984','12925.40234','37.88819')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox8','G:','76795.05859','67053.41015','87.31474')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox9','C:','139975.05859','130358.40625','93.12973')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox9','D:','51197.99609','50611.26953','98.85400')
INSERT INTO ServerDriveSpace VALUES ('Jun 14 2011 6:00AM','WinBox9','E:','102397.99609','100474.67968','98.12172')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply