?
Solved

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

Posted on 2011-09-09
11
Medium Priority
?
256 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
ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

718 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