SQL Server sqlcmd error with sql file but works running same statement in sql console

7kqas0il 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(90)

I have this file.sql :

INSERT INTO "Parcelas" ("PlanEstudio", "LineaEstudio", "NIF", "Plan", "Cosecha", "Linea", "Modulo", "CombCoberturas", "Referencia", "Hoja", "Parcela", "Cultivo", "Variedad", "GrupoVarietal", "Comarca", "ProvSigPac", "TermSigPac", "AgreSigPac", "ZonaSigPac", "PoliSigPac", "ParcSigPac", "ReciSigPac", "Superficie", "Arboles", "Edad", "ProdPrinc", "ProdComp", "Precio", "ValorProcPrinc", "ValorProdComp", "PRE", "PRF", "RendAsig2021") 
VALUES (2022, 300, '43729050R', 2020, 2021, 300, '2', 2, 'K826975', 2, 11, 5, 26, '31-08', 9, 25, 900, 0, 0, 26, 371, 1, 0.86, 1016, 18, 35000, 0, 0.25, 8750, 0, 35000, 22229, 0);

And I get this error with sqlcmd -i :

$ /opt/mssql-tools/bin/sqlcmd -S "$SERVER" -d "$DATABASE" -U "$USER" -P "$PASSWORD" -i a.sql

Msg 102, Level 15, State 1, Server BBDD-DES, Line 1
Incorrect syntax near 'Parcelas'.

But if I don't use -i and execute the same insert, I don't have problems:

$ /opt/mssql-tools/bin/sqlcmd -S "$SERVER" -d "$DATABASE" -U "$USER" -P "$PASSWORD"
1> INSERT INTO "Parcelas" ("PlanEstudio", "LineaEstudio", "NIF", "Plan", "Cosecha", "Linea", "Modulo", "CombCoberturas", "Referencia", "Hoja", "Parcela", "Cultivo", "Variedad", "GrupoVarietal", "Comarca", "ProvSigPac", "TermSigPac", "AgreSigPac", "ZonaSigPac", "PoliSigPac", "ParcSigPac", "ReciSigPac", "Superficie", "Arboles", "Edad", "ProdPrinc", "ProdComp", "Precio", "ValorProcPrinc", "ValorProdComp", "PRE", "PRF", "RendAsig2021") VALUES (2022,300,'43729050R',2020,2021,300,'2',2,'K826975',2,11,5,26,'31-08',9,25,900,0,0,26,371,1,0.86,1016,18,35000,0,0.25,8750,0,35000,22229,0);
2>

Why I can't run this insert using a .sql file?

sql-server

来源：https://stackoverflow.com/questions/75853687/sqlcmd-error-with-sql-file-but-works-running-same-statement-in-sql-console

1条答案

按热度按时间

7vux5j2d1#

The problem is that, by default, sqlcmd does not enable quoted identifiers. As a result "Parcelas" is read as a a literal string, not as a delimited object name.

Considering that none of your objects appear to need delimit identified, you could just get rid of them, or alternatively use the T-SQL delimit identifier, brackets ( [] ). Though, in truth, it's best to pass the -I (Enable Quoted Identifiers) when using sqlcmd :

Note this proof of concept, for example:

touch test.sql
echo 'SELECT "name" from sys.databases;' > test.sql
sqlcmd -S localhost -i test.sql -U thom

This returns the following table:

----
name
name
name
name
name
name
name
name
name
name
name
name
name
name
name
name
name
name

Notice many rows with the value name . If I pass the -I switch, I get the actual database names:

sqlcmd -S localhost -i test.sql -U thom -I

name                                                                                                                            
--------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
Sandbox                                                                                                                         
TestBed                                                                                                                         
TestBed_HDD                                                                                                                     
WebDB                                                                                                                           
UTF8                                                                                                                            
Wordpress                                                                                                                       
Temporal                                                                                                                        
CaseSensitive                                                                                                                   
Maintenance                                                                                                                     
TimDB                                                                                                                           
ImportSample                                                                                                                    
Utility                                                                                                                         
AdventureWorks                                                                                                                  
SpeedTest

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server sqlcmd error with sql file but works running same statement in sql console

1条答案

相关问题

热门标签

最新问答