Incorrect syntax near ':' or '\'

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax near ':' or '\'

Author

Topic

Helpy
Starting Member

5 Posts

Posted - 2011-07-06 : 04:01:15

Hello,
I need to restore a database having a changing name by using a backup file having also a changing URL. I have to do this by launching a Sqlcmd from PowerShell that launch my sql treatment (attachDB.sql) working with parameters passed through the Sqlcmd (-v option) .

1) If i try to send in one parameter $(restoreFile) the full address of the file used to restore the database, i get an error: "invalid argument" due to characters ":" and/or "\"(see below)

PS C:\totingpricemanager\scripts> Sqlcmd -S servername -U user -P password -i c:\totingPriceManager\scripts\SQL\attachDB.sql -v database= baseRefCoutsbttProd -v restoreFile = "c:\totingPriceManager\backupDatabases\LucTest_baseRefCoutsBttD
ev_new.sav"
Sqlcmd: ':\totingPriceManager\backupDatabases\LucTest_baseRefCoutsBttDev_new.sav': Invalid argument. Enter '-?' for help
.PS C:\totingpricemanager\scripts>

So my idea was to split this information in multiple parameters (-v) and to rebuilt it during the SQL treatment.

2) Doing this, my Sqlcmd have the following parameters:
-v database= baseRefCoutsbttProd -v disquec = "c" -v rep1 = "totingPriceManager" -v rep2 = "backupDatabases"
-v restoreFile = "LucTest_baseRefCoutsBttDev_new.sav"

but i have the following problem to concatenate:
Incorrect syntax near ':'.
Incorrect syntax near '\'.

(see below)

PS C:\totingpricemanager\scripts> Sqlcmd -S servername -U user -P password -i c:\totingPriceManager\scripts\SQL\attach
DBTEST.sql -v database= baseRefCoutsbttProd -v disque = "c" -v rep1 = "totingPriceManager" -v rep2 = "backupDatabases"
-v restoreFile = "LucTest_baseRefCoutsBttDev_new.sav"
Changed database context to 'master'.
Msg 102, Level 15, State 1, Server servername, Line 35
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Server servername, Line 36
Incorrect syntax near '\'.
PS C:\totingpricemanager\scripts>

With this part of code:
...
DECLARE @adresse as char(255);
DECLARE @repertoire1 as char(50);
DECLARE @repertoire2 as char(50);
DECLARE @antiSlash as char(1);
DECLARE @deuxpoints as char(1);
set @repertoire1 = $(rep1);
set @repertoire2 = $(rep2);
set @deuxpoints = '':'';
set @antiSlash = ''\'';
set @adresse = $(disque) + @antiSlash + @repertoire1 + @antiSlash + @repertoire2 + @antiSlash + $(restoreFile);

insert into #backupInformation exec('restore filelistonly from disk = ''' + @adresse +'''')

...

Sure my code is wrong but i'm not used with transact sql, could someone help me please ?
Luc

obiron
Starting Member

23 Posts

Posted - 2011-07-06 : 08:06:27

quote:
set @deuxpoints = '':'';
set @antiSlash = ''\'';

Use single quotes instead of double quotes

Helpy
Starting Member

5 Posts

Posted - 2011-07-06 : 09:27:13

Actually it s not double cotes but 2 single cotes before and after characters : and \ but i'v tried with only one cote or double cotes with no more success.
Could somebody give me an example of concatenation of strings in TSQL with characters : and \
Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-06 : 09:48:20

Use single quotes and tell us what the error message is then.

It may still error, but I doubt it will be the same error message...

Corey

I Has Returned!!

Helpy
Starting Member

5 Posts

Posted - 2011-07-06 : 10:21:21

I edit my sql file with notepad++ and if i try to put only one single cote all text become grey after the character \ and when executing, i get a kind of >> prompt (see below) that i understand as an inconsistency in the file. It seems that \ have a particular behavior. is there an escape special character to use before to avoid this ?

PS C:\totingpricemanager\scripts> Sqlcmd -S servername -U user -P password -i c:\totingPriceManager\scripts\SQL\attach
DBTEST.sql -v database= baseRefCoutsbttProd\ -v disque = "c" -v rep1 = "totingPriceManager" -v rep2 = "backupDatabases"
-v restoreFile = "LucTest_baseRefCoutsBttDev_new.sav
>>

Luc

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-06 : 10:25:31

putting it in double quotes in command line should prevent issues there.

In SQL code, you use single quotes to identify text.

PS C:\totingpricemanager\scripts> Sqlcmd -S servername -U user -P password -i "c:\totingPriceManager\scripts\SQL\attach
DBTEST.sql" -v database= baseRefCoutsbttProd\ -v disque = "c" -v rep1 = "totingPriceManager" -v rep2 = "backupDatabases"
-v restoreFile = "LucTest_baseRefCoutsBttDev_new.sav"

Corey

I Has Returned!!

Helpy
Starting Member

5 Posts

Posted - 2011-07-06 : 10:45:34

You are wright for the double cotes. Now the message received is:

PS C:\Users\nat-toting> Sqlcmd -S SV42035NAT9008 -U sa -P Passw0rd -i "c:\totingPriceManager\scripts\SQL\attachDBTEST.sq
l" -v database = baseRefCoutsBttProd -v disque = "c" -v rep1 ="totingPriceManager" -v rep2 = "backupdatabases" -v restor
eFile = "LucTest_baseRefCoutsBttDev_new.sav"
Changed database context to 'master'.
Msg 207, Level 16, State 1, Server SV42035NAT9008, Line 33
Invalid column name 'totingPriceManager'.
Msg 207, Level 16, State 1, Server SV42035NAT9008, Line 34
Invalid column name 'backupdatabases'.
Msg 207, Level 16, State 1, Server SV42035NAT9008, Line 37
Invalid column name 'c'.
Msg 4104, Level 16, State 1, Server SV42035NAT9008, Line 37
The multi-part identifier "LucTest_baseRefCoutsBttDev_new.sav" could not be bound.
PS C:\Users\nat-toting>

With the following SQL file:

*
* Script : attachDB.sql
* Description : Script de restauration d'une base de donnée à partir d'une sauvegarde.
* Author :
* Date création : 24/06/2010
* Historique maj :
*/

/* Début du script */
USE MASTER
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$(database)]') AND type in (N'U'))
ALTER DATABASE $(database) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

/* Creation d'une table temporaire pour la lecture des informations de la sauvegarde */
create table #backupInformation (LogicalName varchar(100),
PhysicalName varchar(100),
Type varchar(1),
FileGroupName varchar(50) ,
Size bigint ,
MaxSize bigint,
FileId int,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes int,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN bigint,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit, IsPresent bit )

DECLARE @adresse as char(255);
/* set @adresse = 'C:\totingPriceManager\backupDatabases\LucTest_baseRefCoutsBttDev_new.sav'; */

DECLARE @repertoire1 as char(50);
DECLARE @repertoire2 as char(50);
DECLARE @antiSlash as char(1);
DECLARE @deuxpoints as char(1);
set @repertoire1 = $(rep1);
set @repertoire2 = $(rep2);
set @deuxpoints = ':';
set @antiSlash = '\';
set @adresse = $(disque) + @deuxpoints + @antiSlash + @repertoire1 + @antiSlash + @repertoire2 + @antiSlash + $(restoreFile);

/* Insertion de informations de la sauvegarde dans la table */
insert into #backupInformation exec('restore filelistonly from disk = ''' + @adresse +'''')

DECLARE @logicalNameD varchar(255);
DECLARE @logicalNameL varchar(255);

/* Récupération des inforamtions de nom de fichier */
select top 1 @logicalNameD = LogicalName from #backupInformation where Type = 'D';
select top 1 @logicalNameL = LogicalName from #backupInformation where Type = 'L';

/* Suppression de la table temporaire */
DROP TABLE #backupInformation

/* Restauration de la base à partir de la sauvegarde */
RESTORE DATABASE $(database) FROM DISK = @adresse WITH REPLACE, MOVE @logicalNameD TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\$(database).mdf', MOVE @logicalNameL TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\$(database).ldf'

GO

Luc

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-06 : 11:28:16

the database variable seemed to work without quotes... are you sure you need the quotes on the rest?

PS C:\totingpricemanager\scripts> Sqlcmd -S servername -U user -P password -i "c:\totingPriceManager\scripts\SQL\attach
DBTEST.sql" -v database= baseRefCoutsbttProd -v disque = c -v rep1 = totingPriceManager -v rep2 = backupDatabases
-v restoreFile = LucTest_baseRefCoutsBttDev_new.sav

Corey

I Has Returned!!

Helpy
Starting Member

5 Posts

Posted - 2011-07-06 : 11:47:43

If i launch de Sqlcmd command with or without all -v parameters having double cotes it gives the same result.

I realy think that the issue is in the sql file during the setting of @adresse parareter. If i use the line (without comment)
/* set @adresse = 'C:\totingPriceManager\backupDatabases\LucTest_baseRefCoutsBttDev_new.sav'; */
to set it my database restore file works properly.

I definitely think the \ and : are misunderstood by sql treatment.

Thanks 7th.night to help me.

Luc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources