need help with a script

• cor_perlee

  Ten Centuries

  Points: 1059

  More actions

  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

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  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" 😉

• cor_perlee

  Ten Centuries

  Points: 1059

  More actions

  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

• Gaurav Gosain-395499

  Right there with Babe

  Points: 770

  More actions

  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;-)

• cor_perlee

  Ten Centuries

  Points: 1059

  More actions

  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

• Gaurav Gosain-395499

  Right there with Babe

  Points: 770

  More actions

  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;-)

• cor_perlee

  Ten Centuries

  Points: 1059

  More actions

  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

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  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]

• Gaurav Gosain-395499

  Right there with Babe

  Points: 770

  More actions

  January 24, 2012 at 3:15 am

  #1437549

  Man @full_backup_path is empty, please fill it.

  Thanks,
  GG;-)

• cor_perlee

  Ten Centuries

  Points: 1059

  More actions

  January 24, 2012 at 3:22 am

  #1437555

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

  Thanks!

  Cheers,

  cor