标题: 可否用批处理对SQL数据库进行安装和操作? [打印本页]
作者: maya0su 时间: 2006-11-19 08:08 标题: 可否用批处理对SQL数据库进行安装和操作?
1.有二个数据库文件1.mdb 和 2.mdb
请问如何用批处理把这二个文件导入到MSSQL里?
2.能否在批处理里对MSSQL的表进行操作?
对MSSQL不熟悉,故问此问题....
作者: chenall 时间: 2006-11-19 10:59
记得好像有一个命令行的ODBC程序,可以在命令行调用ODBC组件,可以找找看.
作者: maya0su 时间: 2006-11-20 09:29
找了,不行的....是个32位的界面数据源管理!
不能在命令行里运行....
还有人能回答吗?
作者: 3742668 时间: 2006-11-20 13:41
建议用bat+vbs来完成吧。
只要创建adodb.connection对象，使用
"Provider=SQLOLE DB; Data Source=FENG-F64129027D; User ID=sa;Password=; Initial Catalog=数据名"来连接就可以了。
由于只做过mdb和xls以及xml之间的转换，没有接触过sql，所以无法给出具体的代码了。
作者: maya0su 时间: 2006-11-26 09:25
首先谢谢版主
我找到了可以使用的命令行程序如下
C:\Program Files\Microsoft SQL Server\80\Tools\Binn 的目录
uncol.exe odbccmpt.exe cdw.exe itwiz.exe wiztrace.exe
profiler.exe isqlw.exe dtsrun.exe sqlftwiz.exe dcomscm.exe
osql.exe bcp.exe isql.exe dtswiz.exe sqlmangr.exe
scm.exe rebuildm.exe cnfgsvr.exe svrnetcn.exe dtsrunui.exe(=dtsrun.exe)
sqladhlp.exe
21 个文件 2,299,448 字节
0 个目录 8,476,901,376 可用字节
==================================================
Usage: odbccmpt <name of the executable file> [/v:Number] [/P] [/T] [/R] [/d]
[/v:Number Client compatibility level (6 or 7)]
[/P Requires connection peek during SQL_COPT_SS_CONNECTION_DEAD]
[/T Turns off translations (used by applications without a DSN)]
[/R Ignores calls to reset connection
[/D Resets option to original state]
Only one option per ODBCCMPT execution allowed.
Examples how to enter compatibility flag:
odbccmpt APPLICATIONX /T
odbccmpt APPLICATIONX /v:6
Example to remove compatibility flag:
odbccmpt APPLICATIONX /T /d
==================================================
用法: itwiz -D 数据库-名称 {-i workload-file | -t workload-table-name} -o 脚本-文件名
[-S servername] [-U username]
[-P password] [-f tuning-feature-set]
[-K keep-existing-indexes] [-M recommendation-quality]
[-B storage-bound] [-n number-of-queries]
[-C max-multicolumn-width] [-T table-list-file]
[-m minimum-improvement] [-E]
[-F] [-v]
==================================================
用法: dtsrun /option [value] [/option [value]] ...
Options（“/?”将显示该屏幕；可以用“-”代替“/”）:
包检索:
/~S 服务器名称
/~U 用户名
/~P 密码
/E <使用信任连接，不使用 /U /P>
/~N 包名称
/~M 包密码
/~G 包 Guid 字符串
/~V 包版本 Guid 字符串
/~F 结构化存储 UNC 文件名（如果还指定了 /S，则将被重写）
/~R 知识库数据库名称 <若空白则使用默认值；从知识库数据库中装载包>
包操作（替代存储包设置）:
/~A 全局变量名: typeid=值 <可以引用整个字符串（包括名称: typeid）>
/~L 日志文件名
/~W 将完成状态写入 Windows 事件日志 <True 或 False>
DTSRun 操作（默认情况为执行包）:
/!X <不要执行；将包检索到 /F 文件名>
/!D <不要执行；从 SQL Server 中除去包（无法从存储文件中除去）>
/!Y <不要执行；输出加密命令行>
/!C <将命令行复制到 Windows 剪贴板上（可以与 /!Y 和 /!X 一起使用）>
注意:
~ 是可选的；如果出现，参数就是由加密值组成的十六进制文本 (0x313233...)
命令开关和值之间的空白区域是可选的
在各个值之间嵌入的空白区域必须嵌入双引号中
如果一个选项被多次指定，则以最后一次为准（除非是多次指定 /A）
==================================================
sqlfwiz.exe [/n][/s][/p][/d]
n=使用windows身份验证
s=服务器名称
u=用户名
p=密码
d=数据库名称
==================================================
用法: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> 批处理
控制台宽度自动调整
宽消息
默认错误级别为 — 1 对 1
[-? show syntax summary]
==================================================用法: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
==================================================isql: unknown option ?
usage: isql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-x max text size]
[-L list servers] [-c cmdend]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary (this screen)]
==================================================sqladhlp -i [-lcid xxxx] to install the service with optional decimal Locale ID, default 1033
sqladhlp -r to remove the service
============================================
作者: maya0su 时间: 2006-11-26 09:26
distrib.exe 用法
[-PacketSize <..>]
[-PollingInterval <..>]
[-MessageInterval <..>]
[-Continuous ]
[-Output <..>]
[-OutputVerboseLevel <0|1|2>]
[-LoginTimeout <..>]
[-QueryTimeout <..>]
[-DefinitionFile <..>]
[-AlignFixup ]
==================================================
logread.exe 用法
[-KeepAliveMessageInterval <..>]
-Publisher <..>
-PublisherDB <..>
[-PublisherSecurityMode <0|1>]
[-PublisherLogin <..> ]
[-PublisherPassword <..> ]
[-Distributor <..> ]
[-DistributorSecurityMode <0|1>]
[-DistributorLogin <..> ]
[-DistributorPassword <..> ]
[-Buffers <..> ]
[-SyncLogging]
[-HistoryVerboseLevel <0|1|2> ]
[-PacketSize <..>]
[-ReadBatchSize <..>]
[-ReadBatchThreshold <..> ]
[-MaxCmdsInTran <..>]
[-PollingInterval <..>]
[-MessageInterval <..>]
[-Continuous ]
[-Output <..>]
[-OutputVerboseLevel <0|1|2>]
[-LoginTimeout <..>]
[-QueryTimeout <..>]
[-DefinitionFile <..>]
[-AlignFixup ]
==================================================
replmerg.exe 用法
(-Publisher <..>) | (-PublisherNetwork <..> -PublisherAddress <..>)
-PublisherDB <..>
-Subscriber <..>
-SubscriberDB <..>
-Publication <..>
[-Hostname <..>]
[-PollingInterval <in seconds>]
[-ValidateInterval <in minutes>]
[-SubscriptionType <0|1|2>]
[-SubscriberConflictClean <0|1>]
[-PublisherSecurityMode <0|1>]
[-PublisherLogin <..>]
[-PublisherPassword <..>]
[-SubscriberSecurityMode <0|1>]
[-SubscriberLogin <..>]
[(-SubscriberPassword | -SubscriberEncryptedPassword) <..>]
[-SubscriberType <0|1|2|3|4|5|6|7|8>]
[-Validate <0|1|2|3>]
[-FastRowCount <0|1>]
[-HistoryVerboseLevel <0|1|2|3>]
[-ProfileName <..>]
[-MaxBcpThreads <..>]
[-UseInprocLoader]
[-InteractiveResolution <0|1>]
[-KeepAliveMessageInterval <..>]
[-PollingInterval <..>]
[-StartQueueTimeout <..>]
[-SrcThreads <..>]
[-DestThreads <..>]
[-ForceConvergenceLevel <0|1|(2 (Publisher|Subscriber|Both))]
[-MetadataRetentionCleanup <0|1>]
[-InputMessageFile <..>]
[-InputMessageFromPublisher <0|1>]
[-OutputMessageFile <..>]
[(-Distributor <..>) | (-DistributorNetwork <..> -DistributorAddress <..>])
[-DistributorSecurityMode <0|1>]
[-DistributorLogin <..>]
[(-DistributorPassword | -DistributorEncryptedPassword) <..>]
[-MaxDownloadChanges <..>]
[-MaxUploadChanges <..>]
[-UploadGenerationsPerBatch <..>]
[-DownloadGenerationsPerBatch <..>]
[-UploadReadChangesPerBatch <..>]
[-DownloadReadChangesPerBatch <..>]
[-UploadWriteChangesPerBatch <..>]
[-DownloadWriteChangesPerBatch <..>]
[-SubscriberDBAddOption 0|1|2|3]
[-SubscriberDatabasePath <..>]
[-FileTransferType <0|1>]
[-FtpAddress <..>]
[-FtpPort <..>]
[-FtpUserName <..>]
[-FtpPassword <..>]
[-ExchangeType <1|2|3>]
[-AltSnapshotFolder <..>]
[-SyncToAlternate <0|1>]
[-DynamicSnapshotLocation <..>]
[-Continuous ]
[-Output <..>]
[-OutputVerboseLevel <0|1|2>]
[-LoginTimeout <..>]
[-QueryTimeout <..>]
[-DefinitionFile <..>]
[-AlignFixup ]
==================================================
snapshot -Publisher <..>
-PublisherDB <..>
-Publication <..>
[-ReplicationType <1|2> ]
[-Distributor <..> ]
[-DistributorSecurityMode <0|1>]
[-DistributorLogin <..> ]
[-DistributorPassword <..> ]
[-PublisherSecurityMode <0|1>]
[-PublisherLogin <..> ]
[-PublisherPassword <..> ]
[-MaxBcpThreads <..> ]
[-BcpBatchSize <..> ]
[-HistoryVerboseLevel <0|1|2|3> ]
[-RowDelimiter <..> ]
[-FieldDelimiter <..> ]
[-70Subscribers ]
[-ProfileName ]
Merge Replication Dynamic Snapshot Parameters:
[-DynamicFilterLogin <..> ]
[-DynamicFilterHostName <..> ]
[-DynamicSnapshotLocation <..> ]
[-StartQueueTimeout <..> ]
[-UsePerArticleContentsView <..> ]
[-Continuous ]
[-Output <..>]
[-OutputVerboseLevel <0|1|2>]
[-LoginTimeout <..>]
[-QueryTimeout <..>]
[-DefinitionFile <..>]
[-AlignFixup ]
==================================================
Usage: sqldumper [ProcessID [ThreadId [Flags[:MinidumpOverrideFlags] [SqlInfoPtr [DumpDir [ExceptionRecordPtr [ContextPtr]]]]]]] [-filtered <filtered dump init parameters>]
作者: maya0su 时间: 2006-11-26 09:27 标题: 可否用批处理对SQL数据库进行安装和操作?
可否用批处理对SQL数据库进行安装和操作?
附件 1:scm.jpg (2006-11-26 09:27, 66.03 K, 下载附件所需积分 1点 ,下载次数： 13)
作者: maya0su 时间: 2006-11-26 09:28
以上为操作SQL的命令行工具,具体方法研究中
希望有更多的朋友,参加讨论!
作者: electronixtar 时间: 2006-11-27 01:13
如果没记错的话，用 isql 和 osql 可以，当初学习 SQL 的时候碰到过
[ Last edited by electronixtar on 2006-11-27 at 01:14 AM ]