Solved

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

Posted on 2011-09-09
11
243 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
  • 7
  • 4
11 Comments
 
LVL 39

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 39

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
 

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 39

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
The curse of the end user strikes again      

You’ve updated all your end user’s email signatures. Hooray! But guess what? They’re playing around with the HTML, adding stupid taglines and ruining the imagery. Find out how you can save your signatures from end users today.

 

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 39

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
A procedure for exporting installed hotfix details of remote computers using powershell
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now