Solved

error in Stored Proc: Conversion failed when converting date and/or time from character string.

Posted on 2011-09-09
11
254 Views
Last Modified: 2012-05-12
Trying to get pass this error on dynamic sql, it's the last SET statment that I can't get past.
I've tried CONVERT and stil no luck. Please help... thanks I appreciate the help.

--alter PROCEDURE dbo.AddFileGroup
--      @TableName varchar(200)
--AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

--

DECLARE
      @DataBaseName       varchar(50),
      @LastFGName       varchar(50),
      @NextFGName       varchar(50),
      @LastFileName       varchar(500),
      @NextFileName       varchar(500),
      @FGPrefix            varchar(10),
      @PartNumbr             varchar(4),
      @LowBoundary      datetime,
      @HighBoundary      datetime,
      @NewRange            datetime,
      @PartScheme            varchar(50),
      @PartFunction      varchar(50),
      @SQL                  nvarchar(2000)
      
DECLARE @PartitionInfo table
      (DataBase_Name       varchar(50),
       FG_Name       varchar(50),
       File_Name       varchar(500),
       Part_Numbr       varchar(4),
       Low_Boundary      datetime,
       High_Boundary      datetime,
       Part_Scheme      varchar(50),
       Part_Function      varchar(50),
       Part_Row_Cnt      int)

-- only inserting 1 row into the table the last row.

INSERT INTO @PartitionInfo      
SELECT TOP 1
      DB_NAME() AS 'DatabaseName'
      ,CASE
            WHEN fg.name IS NULL THEN ds.name
            ELSE fg.name
      END AS 'FileGroupName'
      ,physical_name AS 'FileLocation'
      ,p.partition_number AS 'PartitionNumber'
      ,CONVERT(datetime,prv_left.value)
      ,CONVERT(datetime,prv_right.value)
      ,ps.name as PartitionScheme
      ,pf.name as PartitionFunction
      ,CASE
                  WHEN p.index_id IN (0,1) THEN p.row_count
            ELSE 0
                  END AS 'RowCount'
FROM sys.dm_db_partition_stats p
      INNER JOIN sys.indexes i
            ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN sys.data_spaces ds
            ON ds.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_schemes ps
            ON ps.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_functions pf
            ON ps.function_id = pf.function_id
      LEFT OUTER JOIN sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
            AND dds.destination_id = p.partition_number
      LEFT OUTER JOIN sys.filegroups fg
            ON fg.data_space_id = dds.data_space_id
      INNER JOIN sys.database_files df
      ON fg.data_space_id = df.data_space_id
      LEFT OUTER JOIN sys.partition_range_values prv_right
            ON prv_right.function_id = ps.function_id
            AND prv_right.boundary_id = p.partition_number
      LEFT OUTER JOIN sys.partition_range_values prv_left
            ON prv_left.function_id = ps.function_id
            AND prv_left.boundary_id = p.partition_number - 1
WHERE
       i.index_id < 2
      AND OBJECT_NAME(i.object_id) = 'Test_Part'
--      AND OBJECT_NAME(i.object_id) = @TableName

ORDER BY FileGroupName desc


SET @LastFGName = (SELECT TOP 1 FG_NAME FROM @PartitionInfo)
PRINT N'FG_NAME: ' + @LastFGName

SET @FGPrefix = REPLACE(@LastFGName, RIGHT(@LastFGName, 1), '')
PRINT N'FGPrefix: ' + @FGPrefix
SET @NextFGName = @FGPrefix + RIGHT('0' + CAST(CAST(RIGHT(@LastFGName, 1) AS int) + 1 AS varchar(50)), 1)
PRINT N'NextFGName: ' + @NextFGName

SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NextFGName
PRINT @SQL

SET @LastFileName = (SELECT physical_name FROM sys.database_files WHERE name = @LastFGName)
PRINT N'LastFileName: ' + @LastFileName
SET @NextFileName = REPLACE(@LastFileName, @LastFGName, @NextFGName)
PRINT N'NextFileName: ' + @NextFileName

SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' +
      'NAME = ' + @NextFGName + ',' +
      'FILENAME = ''' + @NextFileName + ''', ' +
      'SIZE = 512KB, ' +
      'FILEGROWTH = 512KB) ' +
      'TO FILEGROUP ' + @NextFGName

SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 Part_Scheme FROM @PartitionInfo) + ' NEXT USED ' + @NextFGName + CHAR(13)

SET @NewRange = (select TOP 1 cast(convert(date,LOW_RANGE_VAL)as date)from part_info order by LOW_RANGE_VAL desc)
SET @NewRange = DATEADD(day, 7, @NewRange);
PRINT CONVERT(date, @NewRange)
PRINT @sql
--SET @NewRange = (SELECT TOP 1(Low_Boundary + 7) FROM @PartitionInfo)
--PRINT N'NewRange: ' + @NewRange
SET @SQL = @SQL + 'ALTER PAR TITION FUNCTION ' + (SELECT Part_Function FROM @PartitionInfo) + '() SPLIT RANGE ' + @NewRange + ')'

--PRINT @sql
--EXEC sp_executesql @SQL
END
0
Comment
Question by:partyon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36512541
Not sure what datatype you have in part_info for LOW_RANGE_VAL column and this is the place in my opinion where you need to focus and fix:

SET @NewRange = (select TOP 1 cast(convert(date,LOW_RANGE_VAL) as date) from part_info order by LOW_RANGE_VAL desc)
0
 

Author Comment

by:partyon
ID: 36512725
Sorry sent you the wrong version... this is the correct one.
--alter PROCEDURE dbo.AddFileGroup
--      @TableName varchar(200)
--AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

--

DECLARE
      @DataBaseName       varchar(50),
      @LastFGName       varchar(50),
      @NextFGName       varchar(50),
      @LastFileName       varchar(500),
      @NextFileName       varchar(500),
      @FGPrefix            varchar(10),
      @PartNumbr             varchar(4),
      @LowBoundary      datetime,
      @HighBoundary      datetime,
      @NewRange            datetime,
      @PartScheme            varchar(50),
      @PartFunction      varchar(50),
      @SQL                  nvarchar(2000)
      
DECLARE @PartitionInfo table
      (DataBase_Name       varchar(50),
       FG_Name       varchar(50),
       File_Name       varchar(500),
       Part_Numbr       varchar(4),
       Low_Boundary      datetime,
       High_Boundary      datetime,
       Part_Scheme      varchar(50),
       Part_Function      varchar(50),
       Part_Row_Cnt      int)

-- only inserting 1 row into the table the last row.

INSERT INTO @PartitionInfo      
SELECT TOP 1
      DB_NAME() AS 'DatabaseName'
      ,CASE
            WHEN fg.name IS NULL THEN ds.name
            ELSE fg.name
      END AS 'FileGroupName'
      ,physical_name AS 'FileLocation'
      ,p.partition_number AS 'PartitionNumber'
      ,CONVERT(datetime,prv_left.value)
      ,CONVERT(datetime,prv_right.value)
      ,ps.name as PartitionScheme
      ,pf.name as PartitionFunction
      ,CASE
                  WHEN p.index_id IN (0,1) THEN p.row_count
            ELSE 0
                  END AS 'RowCount'
FROM sys.dm_db_partition_stats p
      INNER JOIN sys.indexes i
            ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN sys.data_spaces ds
            ON ds.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_schemes ps
            ON ps.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_functions pf
            ON ps.function_id = pf.function_id
      LEFT OUTER JOIN sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
            AND dds.destination_id = p.partition_number
      LEFT OUTER JOIN sys.filegroups fg
            ON fg.data_space_id = dds.data_space_id
      INNER JOIN sys.database_files df
      ON fg.data_space_id = df.data_space_id
      LEFT OUTER JOIN sys.partition_range_values prv_right
            ON prv_right.function_id = ps.function_id
            AND prv_right.boundary_id = p.partition_number
      LEFT OUTER JOIN sys.partition_range_values prv_left
            ON prv_left.function_id = ps.function_id
            AND prv_left.boundary_id = p.partition_number - 1
WHERE
       i.index_id < 2
      AND OBJECT_NAME(i.object_id) = 'Test_Part'
--      AND OBJECT_NAME(i.object_id) = @TableName

ORDER BY FileGroupName desc


SET @LastFGName = (SELECT TOP 1 FG_NAME FROM @PartitionInfo)
PRINT N'FG_NAME: ' + @LastFGName

SET @FGPrefix = REPLACE(@LastFGName, RIGHT(@LastFGName, 1), '')
PRINT N'FGPrefix: ' + @FGPrefix
SET @NextFGName = @FGPrefix + RIGHT('0' + CAST(CAST(RIGHT(@LastFGName, 1) AS int) + 1 AS varchar(50)), 1)
PRINT N'NextFGName: ' + @NextFGName

SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NextFGName
PRINT @SQL

SET @LastFileName = (SELECT physical_name FROM sys.database_files WHERE name = @LastFGName)
PRINT N'LastFileName: ' + @LastFileName
SET @NextFileName = REPLACE(@LastFileName, @LastFGName, @NextFGName)
PRINT N'NextFileName: ' + @NextFileName

SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' +
      'NAME = ' + @NextFGName + ',' +
      'FILENAME = ''' + @NextFileName + ''', ' +
      'SIZE = 512KB, ' +
      'FILEGROWTH = 512KB) ' +
      'TO FILEGROUP ' + @NextFGName

SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 Part_Scheme FROM @PartitionInfo) + ' NEXT USED ' + @NextFGName + CHAR(13)

SET @NewRange = (select TOP 1 cast(convert(date,Low_Boundary)as date)from @PartitionInfo order by Low_Boundary desc)
SET @NewRange = DATEADD(day, 7, @NewRange);
PRINT CONVERT(date, @NewRange)
PRINT @sql

SET @SQL = @SQL + 'ALTER PAR TITION FUNCTION ' + (SELECT Part_Function FROM @PartitionInfo) + '() SPLIT RANGE ' + @NewRange + ')'

--PRINT @sql
--EXEC sp_executesql @SQL
END

===========================================================================
here is the output.
FG_NAME: FG6
FGPrefix: FG
NextFGName: FG7
ALTER DATABASE BARB ADD FILEGROUP FG7
LastFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG6.mdf
NextFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf
2011-09-04
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7ALTER PARTITION SCHEME ps_WEEKLY NEXT USED FG7

Msg 241, Level 16, State 1, Line 119
Conversion failed when converting date and/or time from character string.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512842
Can you please try the code below and post the output only? I just added few more PRINT commands and rearanged a few statements at the end but nothing is executed so it must be safe to run:

--alter PROCEDURE dbo.AddFileGroup
--      @TableName varchar(200)
--AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

--

DECLARE
      @DataBaseName       varchar(50),
      @LastFGName       varchar(50),
      @NextFGName       varchar(50),
      @LastFileName       varchar(500),
      @NextFileName       varchar(500),
      @FGPrefix            varchar(10),
      @PartNumbr             varchar(4),
      @LowBoundary      datetime,
      @HighBoundary      datetime,
      @NewRange            datetime,
      @PartScheme            varchar(50),
      @PartFunction      varchar(50),
      @SQL                  nvarchar(2000)
     
DECLARE @PartitionInfo table
      (DataBase_Name       varchar(50),
       FG_Name       varchar(50),
       File_Name       varchar(500),
       Part_Numbr       varchar(4),
       Low_Boundary      datetime,
       High_Boundary      datetime,
       Part_Scheme      varchar(50),
       Part_Function      varchar(50),
       Part_Row_Cnt      int)

-- only inserting 1 row into the table the last row.

INSERT INTO @PartitionInfo      
SELECT TOP 1
      DB_NAME() AS 'DatabaseName'
      ,CASE
            WHEN fg.name IS NULL THEN ds.name
            ELSE fg.name
      END AS 'FileGroupName'
      ,physical_name AS 'FileLocation'
      ,p.partition_number AS 'PartitionNumber'
      ,CONVERT(datetime,prv_left.value)
      ,CONVERT(datetime,prv_right.value)
      ,ps.name as PartitionScheme
      ,pf.name as PartitionFunction
      ,CASE
                  WHEN p.index_id IN (0,1) THEN p.row_count
            ELSE 0
                  END AS 'RowCount'
FROM sys.dm_db_partition_stats p
      INNER JOIN sys.indexes i
            ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN sys.data_spaces ds
            ON ds.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_schemes ps
            ON ps.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_functions pf
            ON ps.function_id = pf.function_id
      LEFT OUTER JOIN sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
            AND dds.destination_id = p.partition_number
      LEFT OUTER JOIN sys.filegroups fg
            ON fg.data_space_id = dds.data_space_id
      INNER JOIN sys.database_files df
      ON fg.data_space_id = df.data_space_id
      LEFT OUTER JOIN sys.partition_range_values prv_right
            ON prv_right.function_id = ps.function_id
            AND prv_right.boundary_id = p.partition_number
      LEFT OUTER JOIN sys.partition_range_values prv_left
            ON prv_left.function_id = ps.function_id
            AND prv_left.boundary_id = p.partition_number - 1
WHERE
       i.index_id < 2
      AND OBJECT_NAME(i.object_id) = 'Test_Part'
--      AND OBJECT_NAME(i.object_id) = @TableName

ORDER BY FileGroupName desc


SET @LastFGName = (SELECT TOP 1 FG_NAME FROM @PartitionInfo)
PRINT N'FG_NAME: ' + @LastFGName

SET @FGPrefix = REPLACE(@LastFGName, RIGHT(@LastFGName, 1), '')
PRINT N'FGPrefix: ' + @FGPrefix
SET @NextFGName = @FGPrefix + RIGHT('0' + CAST(CAST(RIGHT(@LastFGName, 1) AS int) + 1 AS varchar(50)), 1)
PRINT N'NextFGName: ' + @NextFGName

SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NextFGName
PRINT @SQL

SET @LastFileName = (SELECT physical_name FROM sys.database_files WHERE name = @LastFGName)
PRINT N'LastFileName: ' + @LastFileName
SET @NextFileName = REPLACE(@LastFileName, @LastFGName, @NextFGName)
PRINT N'NextFileName: ' + @NextFileName

SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' +
      'NAME = ' + @NextFGName + ',' +
      'FILENAME = ''' + @NextFileName + ''', ' +
      'SIZE = 512KB, ' +
      'FILEGROWTH = 512KB) ' +
      'TO FILEGROUP ' + @NextFGName
PRINT @sql

SET @NewRange = (select TOP 1 cast(convert(date,Low_Boundary)as date)from @PartitionInfo order by Low_Boundary desc)
PRINT @NewRange
SET @NewRange = DATEADD(day, 7, @NewRange);
PRINT @NewRange
PRINT CONVERT(date, @NewRange)

SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 Part_Scheme FROM @PartitionInfo) + ' NEXT USED ' + @NextFGName + CHAR(13)
PRINT @SQL
SET @SQL = @SQL + 'ALTER PAR TITION FUNCTION ' + (SELECT Part_Function FROM @PartitionInfo) + '() SPLIT RANGE ' + @NewRange + ')'
PRINT @SQL

--PRINT @sql
--EXEC sp_executesql @SQL
END

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:partyon
ID: 36513221
IOutput.........................................
FG_NAME: FG6
FGPrefix: FG
NextFGName: FG7
ALTER DATABASE BARB ADD FILEGROUP FG7
LastFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG6.mdf
NextFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7
Aug 28 2011 12:00AM
Sep  4 2011 12:00AM
2011-09-04
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7ALTER PARTITION SCHEME ps_WEEKLY NEXT USED FG7

Msg 241, Level 16, State 1, Line 114
Conversion failed when converting date and/or time from character string.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36513466
K I think I got it - last SET @SQL line I cast the cast(@NewRange as sysname) because is datetime data type and you can't mix with text to build the SQL string. sysname is my trick for "any" datatype no longer than 100 chars based on SQL implicit conversion.
Please try again updated below with PRING commands only so its safe to run:


--alter PROCEDURE dbo.AddFileGroup
--      @TableName varchar(200)
--AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

--

DECLARE
      @DataBaseName       varchar(50),
      @LastFGName       varchar(50),
      @NextFGName       varchar(50),
      @LastFileName       varchar(500),
      @NextFileName       varchar(500),
      @FGPrefix            varchar(10),
      @PartNumbr             varchar(4),
      @LowBoundary      datetime,
      @HighBoundary      datetime,
      @NewRange            datetime,
      @PartScheme            varchar(50),
      @PartFunction      varchar(50),
      @SQL                  nvarchar(2000)
     
DECLARE @PartitionInfo table
      (DataBase_Name       varchar(50),
       FG_Name       varchar(50),
       File_Name       varchar(500),
       Part_Numbr       varchar(4),
       Low_Boundary      datetime,
       High_Boundary      datetime,
       Part_Scheme      varchar(50),
       Part_Function      varchar(50),
       Part_Row_Cnt      int)

-- only inserting 1 row into the table the last row.

INSERT INTO @PartitionInfo      
SELECT TOP 1
      DB_NAME() AS 'DatabaseName'
      ,CASE
            WHEN fg.name IS NULL THEN ds.name
            ELSE fg.name
      END AS 'FileGroupName'
      ,physical_name AS 'FileLocation'
      ,p.partition_number AS 'PartitionNumber'
      ,CONVERT(datetime,prv_left.value)
      ,CONVERT(datetime,prv_right.value)
      ,ps.name as PartitionScheme
      ,pf.name as PartitionFunction
      ,CASE
                  WHEN p.index_id IN (0,1) THEN p.row_count
            ELSE 0
                  END AS 'RowCount'
FROM sys.dm_db_partition_stats p
      INNER JOIN sys.indexes i
            ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN sys.data_spaces ds
            ON ds.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_schemes ps
            ON ps.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_functions pf
            ON ps.function_id = pf.function_id
      LEFT OUTER JOIN sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
            AND dds.destination_id = p.partition_number
      LEFT OUTER JOIN sys.filegroups fg
            ON fg.data_space_id = dds.data_space_id
      INNER JOIN sys.database_files df
      ON fg.data_space_id = df.data_space_id
      LEFT OUTER JOIN sys.partition_range_values prv_right
            ON prv_right.function_id = ps.function_id
            AND prv_right.boundary_id = p.partition_number
      LEFT OUTER JOIN sys.partition_range_values prv_left
            ON prv_left.function_id = ps.function_id
            AND prv_left.boundary_id = p.partition_number - 1
WHERE
       i.index_id < 2
      AND OBJECT_NAME(i.object_id) = 'Test_Part'
--      AND OBJECT_NAME(i.object_id) = @TableName

ORDER BY FileGroupName desc


SET @LastFGName = (SELECT TOP 1 FG_NAME FROM @PartitionInfo)
PRINT N'FG_NAME: ' + @LastFGName

SET @FGPrefix = REPLACE(@LastFGName, RIGHT(@LastFGName, 1), '')
PRINT N'FGPrefix: ' + @FGPrefix
SET @NextFGName = @FGPrefix + RIGHT('0' + CAST(CAST(RIGHT(@LastFGName, 1) AS int) + 1 AS varchar(50)), 1)
PRINT N'NextFGName: ' + @NextFGName

SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NextFGName
PRINT @SQL

SET @LastFileName = (SELECT physical_name FROM sys.database_files WHERE name = @LastFGName)
PRINT N'LastFileName: ' + @LastFileName
SET @NextFileName = REPLACE(@LastFileName, @LastFGName, @NextFGName)
PRINT N'NextFileName: ' + @NextFileName

SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' +
      'NAME = ' + @NextFGName + ',' +
      'FILENAME = ''' + @NextFileName + ''', ' +
      'SIZE = 512KB, ' +
      'FILEGROWTH = 512KB) ' +
      'TO FILEGROUP ' + @NextFGName
PRINT @sql

SET @NewRange = (select TOP 1 cast(convert(date,Low_Boundary)as date)from @PartitionInfo order by Low_Boundary desc)
PRINT @NewRange
SET @NewRange = DATEADD(day, 7, @NewRange);
PRINT @NewRange
PRINT CONVERT(date, @NewRange)

SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 Part_Scheme FROM @PartitionInfo) + ' NEXT USED ' + @NextFGName + CHAR(13)
PRINT @SQL
SET @SQL = @SQL + 'ALTER PAR TITION FUNCTION ' + (SELECT Part_Function FROM @PartitionInfo) + '() SPLIT RANGE ' + cast(@NewRange as sysname) + ')'
PRINT @SQL

--PRINT @sql
--EXEC sp_executesql @SQL
END

0
 

Author Comment

by:partyon
ID: 36513498
This is the results of the select clause... 1 row will be inserting into the @PartitionInfo  Table
thought it might help...

databasename      filegroupname
-------------------------------------------
BARB               FG6
FileLocation
------------------------------------------------------------------------------------------------------
E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG6.mdf      
PartitionNum       LowBoundary                    High Boundary   partscheme           partfunction   rowcnt
-------------------------------------------------------------------------------------------------------------------------
6           2011-08-28 00:00:00.000    NULL                  ps_WEEKLY      pf_WEEKLY            0
0
 

Author Comment

by:partyon
ID: 36513523
Getting closer, but I need it in the format of '2011-09-04' not 'Sep  4 2011 12:00AM'

FG_NAME: FG6
FGPrefix: FG
NextFGName: FG7
ALTER DATABASE BARB ADD FILEGROUP FG7
LastFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG6.mdf
NextFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7
Aug 28 2011 12:00AM
Sep  4 2011 12:00AM
2011-09-04
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7ALTER PARTITION SCHEME ps_WEEKLY NEXT USED FG7

ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7ALTER PARTITION SCHEME ps_WEEKLY NEXT USED FG7
ALTER PAR TITION FUNCTION pf_WEEKLY() SPLIT RANGE Sep  4 2011 12:00AM)
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36513574
SET @NewRange = DATEPART(yy, @NewRange)+'-'+DATEPART(mm, @NewRange)+'-'+DATEPART(dd, @NewRange)

This should do it - it is included below:


--alter PROCEDURE dbo.AddFileGroup
--      @TableName varchar(200)
--AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

--

DECLARE
      @DataBaseName       varchar(50),
      @LastFGName       varchar(50),
      @NextFGName       varchar(50),
      @LastFileName       varchar(500),
      @NextFileName       varchar(500),
      @FGPrefix            varchar(10),
      @PartNumbr             varchar(4),
      @LowBoundary      datetime,
      @HighBoundary      datetime,
      @NewRange            datetime,
      @PartScheme            varchar(50),
      @PartFunction      varchar(50),
      @SQL                  nvarchar(2000)
     
DECLARE @PartitionInfo table
      (DataBase_Name       varchar(50),
       FG_Name       varchar(50),
       File_Name       varchar(500),
       Part_Numbr       varchar(4),
       Low_Boundary      datetime,
       High_Boundary      datetime,
       Part_Scheme      varchar(50),
       Part_Function      varchar(50),
       Part_Row_Cnt      int)

-- only inserting 1 row into the table the last row.

INSERT INTO @PartitionInfo      
SELECT TOP 1
      DB_NAME() AS 'DatabaseName'
      ,CASE
            WHEN fg.name IS NULL THEN ds.name
            ELSE fg.name
      END AS 'FileGroupName'
      ,physical_name AS 'FileLocation'
      ,p.partition_number AS 'PartitionNumber'
      ,CONVERT(datetime,prv_left.value)
      ,CONVERT(datetime,prv_right.value)
      ,ps.name as PartitionScheme
      ,pf.name as PartitionFunction
      ,CASE
                  WHEN p.index_id IN (0,1) THEN p.row_count
            ELSE 0
                  END AS 'RowCount'
FROM sys.dm_db_partition_stats p
      INNER JOIN sys.indexes i
            ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN sys.data_spaces ds
            ON ds.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_schemes ps
            ON ps.data_space_id = i.data_space_id
      LEFT OUTER JOIN sys.partition_functions pf
            ON ps.function_id = pf.function_id
      LEFT OUTER JOIN sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
            AND dds.destination_id = p.partition_number
      LEFT OUTER JOIN sys.filegroups fg
            ON fg.data_space_id = dds.data_space_id
      INNER JOIN sys.database_files df
      ON fg.data_space_id = df.data_space_id
      LEFT OUTER JOIN sys.partition_range_values prv_right
            ON prv_right.function_id = ps.function_id
            AND prv_right.boundary_id = p.partition_number
      LEFT OUTER JOIN sys.partition_range_values prv_left
            ON prv_left.function_id = ps.function_id
            AND prv_left.boundary_id = p.partition_number - 1
WHERE
       i.index_id < 2
      AND OBJECT_NAME(i.object_id) = 'Test_Part'
--      AND OBJECT_NAME(i.object_id) = @TableName

ORDER BY FileGroupName desc


SET @LastFGName = (SELECT TOP 1 FG_NAME FROM @PartitionInfo)
PRINT N'FG_NAME: ' + @LastFGName

SET @FGPrefix = REPLACE(@LastFGName, RIGHT(@LastFGName, 1), '')
PRINT N'FGPrefix: ' + @FGPrefix
SET @NextFGName = @FGPrefix + RIGHT('0' + CAST(CAST(RIGHT(@LastFGName, 1) AS int) + 1 AS varchar(50)), 1)
PRINT N'NextFGName: ' + @NextFGName

SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NextFGName
PRINT @SQL

SET @LastFileName = (SELECT physical_name FROM sys.database_files WHERE name = @LastFGName)
PRINT N'LastFileName: ' + @LastFileName
SET @NextFileName = REPLACE(@LastFileName, @LastFGName, @NextFGName)
PRINT N'NextFileName: ' + @NextFileName

SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' +
      'NAME = ' + @NextFGName + ',' +
      'FILENAME = ''' + @NextFileName + ''', ' +
      'SIZE = 512KB, ' +
      'FILEGROWTH = 512KB) ' +
      'TO FILEGROUP ' + @NextFGName
PRINT @sql

SET @NewRange = (select TOP 1 cast(convert(date,Low_Boundary)as date)from @PartitionInfo order by Low_Boundary desc)
PRINT @NewRange
SET @NewRange = DATEADD(day, 7, @NewRange);
PRINT @NewRange
SET @NewRange = DATEPART(yy, @NewRange)+'-'+DATEPART(mm, @NewRange)+'-'+DATEPART(dd, @NewRange)
PRINT @NewRange
PRINT CONVERT(date, @NewRange)

SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 Part_Scheme FROM @PartitionInfo) + ' NEXT USED ' + @NextFGName + CHAR(13)
PRINT @SQL
SET @SQL = @SQL + 'ALTER PAR TITION FUNCTION ' + (SELECT Part_Function FROM @PartitionInfo) + '() SPLIT RANGE ' + cast(@NewRange as sysname) + ')'
PRINT @SQL

--PRINT @sql
--EXEC sp_executesql @SQL
END
0
 

Accepted Solution

by:
partyon earned 0 total points
ID: 36513677
I just changed the DATETIME columns in the DECLARE clauses to DATE and it worked.
Thank you so much for your help.. much appreciated. I'll remember your little trick ;)

Output.................................
FG_NAME: FG6
FGPrefix: FG
NextFGName: FG7
ALTER DATABASE BARB ADD FILEGROUP FG7
LastFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG6.mdf
NextFileName: E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7
2011-08-28
2011-09-04
2011-09-04
ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7ALTER PARTITION SCHEME ps_WEEKLY NEXT USED FG7

ALTER DATABASE BARB ADD FILEGROUP FG7ALTER DATABASE BARB ADD FILE (NAME = FG7,FILENAME = 'E:\DATA\DATABASE\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\barb_FG7.mdf', SIZE = 512KB, FILEGROWTH = 512KB) TO FILEGROUP FG7ALTER PARTITION SCHEME ps_WEEKLY NEXT USED FG7
ALTER PAR TITION FUNCTION pf_WEEKLY() SPLIT RANGE 2011-09-04)
0
 

Author Closing Comment

by:partyon
ID: 36555804
The solution was very close to what I needed and was enough for me to figure out how to get my result in the format that I needed.
0
 

Author Comment

by:partyon
ID: 36530516
Well I don't agree with your scoring 0 points to Icohan they did provide me a solution that was able to help me reach my final solution so you should award them at least 80% of the score. = 200 points
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question