Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dsmithdevelopment
Starting Member
1 Post |
 Posted - 2009-03-11 : 18:16:07
|
| I am having trouble figuring out how to concatenate properly and in the examples I have listed below the first is a hard-coded restore script (the one that is commented out), and the bottom script is my attempt to place the variables I have declared in the main script I am working on. Below each script is a brief explanation and the bottom script has the error message I am receiving when I try to run it. I am wondering if anyone has some advice as to the rules involved here for concatenating variables to strings in SQL and/or if someone knows of a research resource that would be cool too.-- RESTORE DATABASE [DBName] FROM DISK = N'V:\backupdatafolder\subfolder1\full\DBName_backup_200903110100.bak'-- WITH FILE = 1,-- MOVE N'DBName_Data' TO N'V:\finalRestorePath\DBName.MDF', -- MOVE N'DBName_Log' TO N'V:\finalRestorePath\DBName_1.ldf', -- MOVE N'sysft_DBName_CatalogA' TO N'V:\finalRestorePath\DBName_2.DBName_CatalogA', -- MOVE N'sysft_DBName_CatalogB' TO N'V:\finalRestorePath\DBName_3.DBName_CatalogB', -- MOVE N'sysft_Apex_CatalogA' TO N'V:\finalRestorePath\DBName_4.Apex_CatalogA',-- MOVE N'sysft_Apex_CatalogB' TO N'V:\finalRestorePath\DBName_5.Apex_CatalogB',-- NOUNLOAD, STATS = 10-- The restore block above runs and is correct-- The restore block above is also the original restore call that was produced with the -- restore and scripting tool in SQL management studio (right click DB select tasks-> restore-- and from there follow the wizard until the end then hit the "Script" button) RESTORE DATABASE @dbName FROM DISK = @CmpltRstrPth WITH FILE = 1, MOVE N'DBName_Data' TO @FnlRstrPth + '.MDF', MOVE N'DBName_Log' TO @FnlRstrPth + '_1.LDF', MOVE N'sysft_DBName_CatalogA' TO @FnlRstrPth + '_2.DBName_CatalogA', MOVE N'sysft_DBName_CatalogB' TO @FnlRstrPth + '_3.DBName_CatalogB', MOVE N'sysft_Apex_CatalogA' TO @FnlRstrPth + '_4.Apex_CatalogA', MOVE N'sysft_Apex_CatalogB' TO @FnlRstrPth + '_5.Apex_CatalogB', NOUNLOAD, STATS = 10-- The restore block above is not concatenated properly-- and when I run it I get this error; seen below:-- Msg 102, Level 15, State 1, Line 96-- Incorrect syntax near '+'. |
|
|
guptam
Posting Yak Master
161 Posts |
Posted - 2009-03-11 : 19:37:52
|
| The problem is not they are concatenated properly ... the problem is the Restore command doesn't like that kind of statement :).Try ...DECLARE @SQLStatement VARCHAR(8000)SET @SQLSTatement = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' + @CmpltRstrPth + '''WITH FILE = 1,MOVE N''DBName_Data'' TO ''' + @FnlRstrPth + '.MDF'',MOVE N''DBName_Log'' TO ''' + @FnlRstrPth + '_1.LDF'',MOVE N''sysft_DBName_CatalogA'' TO ''' + @FnlRstrPth + '_2.DBName_CatalogA'',MOVE N''sysft_DBName_CatalogB'' TO ''' + @FnlRstrPth + '_3.DBName_CatalogB'',MOVE N''sysft_Apex_CatalogA'' TO ''' + @FnlRstrPth + '_4.Apex_CatalogA'',MOVE N''sysft_Apex_CatalogB'' TO ''' + @FnlRstrPth + '_5.Apex_CatalogB'',NOUNLOAD, STATS = 10'PRINT @SQLStatement-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
concatenate variables and strings