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_pathVARCHAR(512)

    , @log_file_pathVARCHAR(512)

    , @backup_pathVARCHAR(512)

    , @backup_extensionVARCHAR(4)

    , @mdf_extensionVARCHAR(4)

    , @ldf_extensionVARCHAR(4)

    , @cnv_extensionVARCHAR(4)

    , @backup_nameNVARCHAR(8)

    , @database_nameVARCHAR(32)

    , @restore_nameVARCHAR(32)

    , @logical_data_nameVARCHAR(64)

    , @logical_data_1_nameVARCHAR(64)

    , @logical_log_nameVARCHAR(64)

    , @data_file_full_pathVARCHAR(512)

    , @log_file_full_pathVARCHAR(512)

    , @full_backup_pathVARCHAR(MAX)

    , @cmdVARCHAR(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_nameVARCHAR(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_pathVARCHAR(512)

    , @log_file_pathVARCHAR(512)

    , @backup_pathVARCHAR(512)

    , @backup_extensionVARCHAR(4)

    , @mdf_extensionVARCHAR(4)

    , @ldf_extensionVARCHAR(4)

    , @cnv_extensionVARCHAR(4)

    , @backup_nameNVARCHAR(8)

    , @database_nameVARCHAR(32)

    , @restore_nameVARCHAR(32)

    , @logical_data_nameVARCHAR(64)

    , @logical_data_1_nameVARCHAR(64)

    , @logical_log_nameVARCHAR(64)

    , @data_file_full_pathNVARCHAR(255)

    , @log_file_full_pathNVARCHAR(255)

    , @full_backup_pathVARCHAR(MAX)

    , @cmdVARCHAR(128)

    , @cnvVARCHAR(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 10 (of 10 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy