Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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

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
partyon
Asked:
partyon
  • 7
  • 4
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
partyonAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
partyonAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
partyonAuthor Commented:
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
 
partyonAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
partyonAuthor Commented:
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
 
partyonAuthor Commented:
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
 
partyonAuthor Commented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now