1:使用sqlcmd运行一个.sql文件
第一次尝试:
1 bohemian@bohemian:~$ sqlcmd -U sa -P ************-H local -i /SqlTest/test1.sql 2 Sqlcmd: '/SqlTest/test1.sql': Invalid filename.
报错无效的文件名.我的文件没有问题,难道是-i的参数出了问题,或者没有登录到主机上去,把-H改成-S试试,成功登录.但我的文件和
1 bohemian@bohemian:~/SqlTest$ sqlcmd -U sa -P ******** -S 192.168.1.6 2 1> 3 bohemian@bohemian:~/SqlTest$ sqlcmd -U sa -P ********* -S localhost 4 1>
目录确实存在,也试过了/home/bohemian/SqlTest的绝对目录同样的错误Invalid filename,实在是找不到错误于是在Windows上试了试完全没问题
google搜索Sqlcmd&Ubuntu&.sql Invalid Filename后终于找到一些有用的回答https://www.sqlservercentral.com/forums/topic/sqlcmd-invalid-filename
问题出在数据库的配置上,查阅官方文档https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15 找到
改配置的办法
sudo chown mssql /tmp/masterdatabasedir
sudo chgrp mssql /tmp/masterdatabasedir
sudo /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /tmp/masterdatabasedir/master.mdf
sudo /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /tmp/masterdatabasedir/mastlog.ldf
同理更改log文件夹
重启服务后终于报了一个不一样的错误00
bohemian@bohemian:~$ sqlcmd -Usa -P****** -H local -i '/home/bohemian/SqlTest/test1.sql' Msg 5105, Level 16, State 2, Server bohemian, Line 1 出现文件激活错误。物理文件名 '~/SqlData/Sale_1.mdf' 可能不正确。请诊断并更正其他错误，然后重试此操作。 Msg 1802, Level 16, State 1, Server bohemian, Line 1 CREATE DATABASE 失败。无法创建列出的某些文件名。请查看相关错误。
更改.sql文件的相关位置后succeed
bohemian@bohemian:~$ sqlcmd -Usa -P************ -H local -i '/home/bohemian/SqlTest/test1.sql' bohemian@bohemian:~$
SELECT * FROM sysdatabases;
go
name dbid sid mode status status2 crdate reserved category cmptlevel filename version
-------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ----------- ----------------------- ----------------------- ----------- --------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------
master 1 0x01 0 65544 1090520064 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0 150 /var/opt/mssql/data/master.mdf 904
tempdb 2 0x01 0 65544 1090520064 2020-02-17 20:13:53.453 1900-01-01 00:00:00.000 0 150 /var/opt/mssql/data/tempdb.mdf 904
model 3 0x01 0 65536 1090519040 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0 150 /var/opt/mssql/data/model.mdf 904
msdb 4 0x01 0 65544 1627390976 2019-12-06 16:24:38.153 1900-01-01 00:00:00.000 0 150 /var/opt/mssql/data/MSDBData.mdf 904
TestDB 5 0x01 0 65536 1627389952 2020-02-14 10:33:40.363 1900-01-01 00:00:00.000 0 150 /var/opt/mssql/data/TestDB.mdf 904
Sale 6 0x01 0 65536 1627389952 2020-02-17 20:20:48.387 1900-01-01 00:00:00.000 0 150 /home/bohemian/SqlData/Sale_1.mdf 904
2:查看指定表的所有字段信息
select COLUMN_NAME from information_schema.COLUMNS where table_name = '要查询表的名字';
