Post reply

need help with a script

  • January 23, 2012 at 8:17 am

    #255337

    Hi all,

    Can someone tell me what is wrong with this script. It keeps saying:

    Msg 8114, Level 16, State 12, Line 59

    Error converting data type varchar to nvarchar.

    I just don't get it.

    Any help would be greatly appreciated.

    Cheers,

    Cor

    DECLARE

    @data_file_path VARCHAR(512)

    , @log_file_path VARCHAR(512)

    , @backup_path VARCHAR(512)

    , @backup_extension VARCHAR(4)

    , @mdf_extension VARCHAR(4)

    , @ldf_extension VARCHAR(4)

    , @cnv_extension VARCHAR(4)

    , @backup_name NVARCHAR(8)

    , @database_name VARCHAR(32)

    , @restore_name VARCHAR(32)

    , @logical_data_name VARCHAR(64)

    , @logical_data_1_name VARCHAR(64)

    , @logical_log_name VARCHAR(64)

    , @data_file_full_path VARCHAR(512)

    , @log_file_full_path VARCHAR(512)

    , @full_backup_path VARCHAR(MAX)

    , @cmd VARCHAR(128)

    , @cnv VARCHAR(1)

    -- ** VARIABLES THAT MUST BE SET **--

    SET @data_file_path = 'D:\Data\'

    SET @log_file_path = 'D:\Logs\'

    SET @backup_path = 'D:\Klantenmap\'

    -- **----------------------------**--

    SET @backup_extension = '.bak'

    SET @mdf_extension = '.mdf'

    SET @ldf_extension = '.ldf'

    SET @cnv_extension = '_cnv'

    -- ** -------------------------------------** --

    DECLARE curDBNames CURSOR FOR

    SELECT backup_name FROM Unit4_Beheer.dbo.DATABASES_TO_RESTORE

    where cnv = 'Y'

    OPEN curDBNames

    FETCH NEXT FROM curDBNames INTO @database_name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @restore_name = @database_name + @cnv_extension

    print @restore_name

    SET @data_file_full_path = @data_file_path + @restore_name + @mdf_extension

    print @data_file_full_path

    SET @log_file_full_path = @log_file_path + @restore_name + @ldf_extension

    print @log_file_full_path

    print @restore_name

    RESTORE DATABASE @restore_name FROM DISK = @full_backup_path WITH FILE = 1,

    MOVE 'U4SConvert_Data' TO @data_file_full_path,

    MOVE 'U4SConvert_Log' TO @log_file_full_path

    FETCH NEXT FROM curDBNames INTO @database_name

    END

    CLOSE curDBNames

    DEALLOCATE curDBNames

  • January 23, 2012 at 8:34 am

    #1437156

    cor_perlee (1/23/2012)

    , @database_name VARCHAR(32)

    database name would normally be a data type of SYSNAME or NVARCHAR(128). varchar(32) could result in the database name being truncated

    What is the schema definition of this table

    Unit4_Beheer.dbo.DATABASES_TO_RESTORE

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 24, 2012 at 1:59 am

    #1437507

    Schema definition:

    USE [Unit4_Beheer]

    GO

    /****** Object: Table [dbo].[DATABASES_TO_RESTORE] Script Date: 01/24/2012 09:58:48 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[DATABASES_TO_RESTORE](

    [rownum] [int] IDENTITY(1,1) NOT NULL,

    [backup_name] [varchar](8) NULL,

    [cnv] [varchar](1) NULL,

    CONSTRAINT [PK_DATABASES_TO_RESTORE] PRIMARY KEY CLUSTERED

    (

    [rownum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Cheers,

    Cor

  • January 24, 2012 at 2:16 am

    #1437513

    Try this

    Declare @data_file_full_path nvarchar(255),@log_file_full_path nvarchar(255)

    and when assigning values to @log_file_full_path and @data_file_full_path convert them to nvarchar

    RESTORE DATABASE @restore_name FROM DISK = @full_backup_path WITH FILE = 1,

    MOVE N'U4SConvert_Data' TO @data_file_full_path,

    MOVE N'U4SConvert_Log' TO @log_file_full_path

    Thanks,
    GG;-)

  • January 24, 2012 at 2:35 am

    #1437523

    Thanks for your reply 🙂

    I made the following:

    DECLARE

    @data_file_path VARCHAR(512)

    , @log_file_path VARCHAR(512)

    , @backup_path VARCHAR(512)

    , @backup_extension VARCHAR(4)

    , @mdf_extension VARCHAR(4)

    , @ldf_extension VARCHAR(4)

    , @cnv_extension VARCHAR(4)

    , @backup_name NVARCHAR(8)

    , @database_name VARCHAR(32)

    , @restore_name VARCHAR(32)

    , @logical_data_name VARCHAR(64)

    , @logical_data_1_name VARCHAR(64)

    , @logical_log_name VARCHAR(64)

    , @data_file_full_path NVARCHAR(255)

    , @log_file_full_path NVARCHAR(255)

    , @full_backup_path VARCHAR(MAX)

    , @cmd VARCHAR(128)

    , @cnv VARCHAR(1)

    -- ** VARIABLES THAT MUST BE SET **--

    SET @data_file_path = 'D:\Data\'

    SET @log_file_path = 'D:\Logs\'

    SET @backup_path = 'D:\Klantenmap\'

    -- **----------------------------**--

    SET @backup_extension = '.bak'

    SET @mdf_extension = '.mdf'

    SET @ldf_extension = '.ldf'

    SET @cnv_extension = '_cnv'

    -- ** -------------------------------------** --

    DECLARE curDBNames CURSOR FOR

    SELECT backup_name FROM Unit4_Beheer.dbo.DATABASES_TO_RESTORE

    where cnv = 'Y'

    OPEN curDBNames

    FETCH NEXT FROM curDBNames INTO @database_name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @restore_name = @database_name + @cnv_extension

    print @restore_name

    SET @data_file_full_path = @data_file_path + @restore_name + @mdf_extension

    SELECT CONVERT(NVARCHAR(255), @data_file_full_path)

    print @data_file_full_path

    SET @log_file_full_path = @log_file_path + @restore_name + @ldf_extension

    SELECT CONVERT(NVARCHAR(255), @log_file_full_path)

    print @log_file_full_path

    print @restore_name

    RESTORE DATABASE @restore_name FROM DISK = @full_backup_path WITH FILE = 1,

    MOVE 'U4SConvert_Data' TO @data_file_full_path,

    MOVE 'U4SConvert_Log' TO @log_file_full_path

    FETCH NEXT FROM curDBNames INTO @database_name

    END

    CLOSE curDBNames

    DEALLOCATE curDBNames

    ------------------------------------------------------------------------------------

    Same result I'm afraid.

    Msg 8114, Level 16, State 12, Line 66

    Error converting data type varchar to nvarchar.

    Cheers,

    Cor

  • January 24, 2012 at 2:49 am

    #1437531

    Try this:

    DECLARE

    @data_file_path VARCHAR(512)

    , @log_file_path VARCHAR(512)

    , @backup_path VARCHAR(512)

    , @backup_extension VARCHAR(4)

    , @mdf_extension VARCHAR(4)

    , @ldf_extension VARCHAR(4)

    , @cnv_extension VARCHAR(4)

    , @backup_name NVARCHAR(8)

    , @database_name VARCHAR(32)

    , @restore_name VARCHAR(32)

    , @logical_data_name VARCHAR(64)

    , @logical_data_1_name VARCHAR(64)

    , @logical_log_name VARCHAR(64)

    , @data_file_full_path NVARCHAR(255)

    , @log_file_full_path NVARCHAR(255)

    , @full_backup_path VARCHAR(MAX)

    , @cmd VARCHAR(128)

    , @cnv VARCHAR(1)

    -- ** VARIABLES THAT MUST BE SET **--

    SET @data_file_path = 'D:\Data\'

    SET @log_file_path = 'D:\Logs\'

    SET @backup_path = 'D:\Klantenmap\'

    -- **----------------------------**--

    SET @backup_extension = '.bak'

    SET @mdf_extension = '.mdf'

    SET @ldf_extension = '.ldf'

    SET @cnv_extension = '_cnv'

    -- ** -------------------------------------** --

    DECLARE curDBNames CURSOR FOR

    SELECT backup_name FROM Unit4_Beheer.dbo.DATABASES_TO_RESTORE

    where cnv = 'Y'

    OPEN curDBNames

    FETCH NEXT FROM curDBNames INTO @database_name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @restore_name = @database_name + @cnv_extension

    print @restore_name

    Select @data_file_full_path = CONVERT(NVARCHAR(255),@data_file_path + @restore_name + @mdf_extension)

    --SELECT CONVERT(NVARCHAR(255), @data_file_full_path)

    print @data_file_full_path

    Select @log_file_full_path = CONVERT(NVARCHAR(255),@log_file_path + @restore_name + @ldf_extension )

    --SELECT CONVERT(NVARCHAR(255), @log_file_full_path)

    print @log_file_full_path

    print @restore_name

    RESTORE DATABASE @restore_name FROM DISK = @full_backup_path WITH FILE = 1,

    MOVE N'U4SConvert_Data' TO @data_file_full_path,

    MOVE N'U4SConvert_Log' TO @log_file_full_path

    FETCH NEXT FROM curDBNames INTO @database_name

    END

    CLOSE curDBNames

    DEALLOCATE curDBNames

    Thanks,
    GG;-)

  • January 24, 2012 at 3:03 am

    #1437540

    Same result unfortunately

    Msg 8114, Level 16, State 12, Line 64

    Error converting data type varchar to nvarchar.

    Cheers,

    Cor

  • January 24, 2012 at 3:06 am

    #1437543

    -- use a more appropriate value for the size of @log_file_path:

    SET @log_file_full_path [NVARCHAR(255)]

    = @log_file_path [VARCHAR(512)]

    + @database_name [VARCHAR(32)]

    + @cnv_extension [VARCHAR(4)]

    + @ldf_extension [VARCHAR(4)]

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 24, 2012 at 3:15 am

    #1437549

    Man @full_backup_path is empty, please fill it.

    Thanks,
    GG;-)

  • January 24, 2012 at 3:22 am

    #1437555

    Can't believe I missed that. How stupid of me.

    Thanks!

    Cheers,

    cor

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply