January 23, 2012 at 8:17 am
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
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
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
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
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
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
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
-- 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)]
For better assistance in answering your questions, please read this[/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
Man @full_backup_path is empty, please fill it.
Thanks,
GG;-)
January 24, 2012 at 3:22 am
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
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