×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@PEI

我就来和你讲讲区别。

yangn(Raymond)
1。GUI在很多情况下是能给人带来方便，但它是以牺牲系统的performance作为代价的。
2。DTS作为工具有它不可替代性，尤其是在data mart 或data warehousing的ETL阶段，它可以很方便的对data进行scrub,这点无论是isql,osql 还是bcp都作不到。
3。对于sql data向text file的传递， isql/osql有其优越性，后者速度上要快很多，这点在record 很多时尤其明显。
4。对于sql 6.5以下，DTS根本无从用起。
5。很多Access出身的用户对script 或command有一种莫名其妙的排斥。其实isql/osql的syntax并不复杂，当你在DTS里一步步click mouse时，isql/osql早就写好了。
6.如果sql server的工作离开enterprise manager就寸步难行，那么这个产品也就只能是属于玩具级别了。有些公司根本不允许在非DBA的workstation上装EM,难道就非要用SQL-DMO来实现DTS去作如此简单的文件输出？
(#766350@0)
Last Updated: 2002-9-25
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 枫下家园 / 电脑用户 / HELP! How to dump the result of a sql sentense to a file under ms sql? -oceandeep(北极熊® Zzz Zzz); 2002-9-25 {57} (#766046@0)
    For example:

    unload to "aaa" select * from bbb
    • DTS, Data Transformation Service in SQL Server -zhihaoxx(热爱FB的LOSER 甲); 2002-9-25 (#766052@0)
      • There isn't any gramma like "unload to" ????? -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766075@0)
        • not sure, I always use DTS or XML query when I need to transfer data to other destination -zhihaoxx(热爱FB的LOSER 甲); 2002-9-25 (#766087@0)
    • 最简单也最快速的办法是用isql or osql: isql /Sserver /Uuserid /Ppassword /Q"query" >"destination file" -yangn(Raymond); 2002-9-25 {116} (#766134@0)
      多说两句，
      很多sql server的用户（including the developers and the dbas)被MS 惯的只会用GUI,而忽略了更有效的solution.
      • Sorry, I am not familiar with SQL SERVER. I am now in the SQL Query Analyzer. How can I do that like what you said. -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766189@0)
        • run it in the command prompt window not the query analyzer. -yangn(Raymond); 2002-9-25 (#766202@0)
          • Thanks, I will try it, One more question, Can I ask the SQL SERVER to do a script daily automatically? -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766204@0)
            • yes. you can. just put that one in a batch file and set up a schedule job including the batch file under sqlagent. -yangn(Raymond); 2002-9-25 (#766231@0)
        • Sorry, it still doesn't work -oceandeep(北极熊® Zzz Zzz); 2002-9-25 {285} (#766212@0)
          if
          username ="user"
          password=" password"
          servername="servername"
          outputfilename="outputfile"


          then

          the command I should type in is:

          isql /Sservername/Uusername/Ppassword/Q"select * from employee" > "C:\outputfile"


          Am I right?

          but it doesn't work
          • if the default database is not the one you will work at, you have to specify the database and also notice the "space": -yangn(Raymond); 2002-9-25 {103} (#766228@0)
            isql /Sservername /Uusername /Ppassword /ddatabasename /Q"select * from employee" > "C:\outputfile
    • 用DTS做的话, 3分钟就做好了, 还可以把转换规则保存下来, 下次修改. 估计看ISQL的说明也不止3分钟吧? 好好的工具, 为什么就不用? -zhihaoxx(热爱FB的LOSER 甲); 2002-9-25 (#766253@0)
      • 我就来和你讲讲区别。 -yangn(Raymond); 2002-9-25 {711} (#766350@0)
        1。GUI在很多情况下是能给人带来方便，但它是以牺牲系统的performance作为代价的。
        2。DTS作为工具有它不可替代性，尤其是在data mart 或data warehousing的ETL阶段，它可以很方便的对data进行scrub,这点无论是isql,osql 还是bcp都作不到。
        3。对于sql data向text file的传递， isql/osql有其优越性，后者速度上要快很多，这点在record 很多时尤其明显。
        4。对于sql 6.5以下，DTS根本无从用起。
        5。很多Access出身的用户对script 或command有一种莫名其妙的排斥。其实isql/osql的syntax并不复杂，当你在DTS里一步步click mouse时，isql/osql早就写好了。
        6.如果sql server的工作离开enterprise manager就寸步难行，那么这个产品也就只能是属于玩具级别了。有些公司根本不允许在非DBA的workstation上装EM,难道就非要用SQL-DMO来实现DTS去作如此简单的文件输出？
        • gaoshou, It doesn't work :( -oceandeep(北极熊® Zzz Zzz); 2002-9-25 {395} (#766419@0)
          The information said it couldn't connect to the server.

          you taught me last time how to load a file into the database using the gramma below:
          bulk INSERT aaa_table FROM 'c:\aaa' WITH ( FIELDTERMINATOR = '\' )

          Is there any oppsite operation, just unload the information from a table into a file, In informix, it should be
          unload to "filename" select * from tablename


          Thanks
          • both bcp and isql can transfer data from sql to text file. -yangn(Raymond); 2002-9-25 {316} (#766459@0)
            1. if isql doesn't work, maybe your userID and password are not correct. just try : isql /SServername /Uusername /Ppassword
            if "1>" appears, your connection is ok otherwise check your security setting.

            2. using bcp
            bcp "SELECT * from table" queryout output.txt -c -Sservername -Usa -Ppassword

            good luck!
            • Thank you very much. Finally I realized there is a blank between each two parameters. -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766504@0)
          • isql and osql -handd(handd); 2002-9-25 {145} (#766468@0)
            isql is using "DB--Library'. Some features in 7.0 will not be supported. osql is using 'ODBC'. you have to set up ODBC link before you use it
            • Some clarifications. -yangn(Raymond); 2002-10-3 {381} (#779812@0)
              1. You are right. isql communicates with sql server through DB-Library, while osql communicates through ODBC.
              2. MS SQL 6.5 only supports isql and both MS SQL7 and MS SQL 2K support isql and osql.
              3. To use osql, you don't need to set up a separate ODBC link between the client and the server, what you need is the ODBC driver installed on the client where you want to run osql.
        • 各有各的好处, 我也不跟你争, 我的工作环境要跟不同的文件格式打交道, XML, XLS, PDF, TXT, CSV.....DTS是我的首选. 现在的情况是: 一个简单的TXT DUMP, 从11:26到13:36, > 两个小时都没有弄好 -zhihaoxx(热爱FB的LOSER 甲); 2002-9-25 (#766476@0)
          • 老兄，我不是要和你争什么。我可以说工作中我使用DTS的次数绝不比你少。只是有些概念问题讨论一下对大家都有帮助。 -yangn(Raymond); 2002-9-25 (#766539@0)
            • no offense -zhihaoxx(热爱FB的LOSER 甲); 2002-9-25 (#766547@0)
              • calm down please, you all are good persons and are willing to help others. Thank you very much -oceandeep(北极熊® Zzz Zzz); 2002-9-25 {120} (#766560@0)
                BTW, how to get Today's date in SQL SERVER script?
                like

                select * from tablename where tablename.businessdate=TODAY
                • use function getdate(), which component of the datetime you want to retrieve is up to you and the datepart() function can be used to split the year,month and day from the result. -yangn(Raymond); 2002-9-25 (#766568@0)
                  • Thank you, Can I use "getdate()-1", It seems doesn't work. -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766702@0)
                    • Do you mean you want to get the previous day? If this is the case, then use dateadd(day,-1,getdate()) -yangn(Raymond); 2002-9-25 (#766712@0)
                      • millions of thanks -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766922@0)
                      • the function getdate() gives both date and time, can I ask it give me the date only? -oceandeep(北极熊® Zzz Zzz); 2002-9-25 (#766990@0)
                        • 嘿嘿。我要收consulting fee了。 datepart(day,getdate()) returns the day part of the current time. -yangn(Raymond); 2002-9-25 (#767073@0)

More Topics

  • 从朋友那里拿了个三手三星Note20 5g，
  • 有谁把手机physical sim卡转为用e-sim 的？使用效果是不是一样的？
  • 以前在College 买的MS office 已经过期了,用不了。LibreOffice 能用吗？可以编辑以前的 MS Word 不？
  • 想在Android的手机里装上这些正规的字体，比方说,Times New Roman，Courier New。怎么装法？ Google Playstore里面的Font App尽是那些乌烟瘴气，花哩唿哨的comic字体，一味迎合年轻人和teenager口味，很讨厌。
  • 发现了ESIM Pay as you go plan: 充值即用，说是不会过期，不收手续费。有兴趣的可以看看，总结一下经验让大家一起分享。
    • 枫下论坛主坛 /  枫下家园 /  电脑用户