This topic has been archived. It cannot be replied.
-
枫下家园 / 电脑用户 / HELP! How to dump the result of a sql sentense to a file under ms sql?For example:
unload to "aaa" select * from bbb
-oceandeep(北极熊® Zzz Zzz);
2002-9-25
{57}
(#766046@0)
-
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"多说两句,
很多sql server的用户(including the developers and the dbas)被MS 惯的只会用GUI,而忽略了更有效的solution.
-yangn(Raymond);
2002-9-25
{116}
(#766134@0)
-
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 workif
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
-oceandeep(北极熊® Zzz Zzz);
2002-9-25
{285}
(#766212@0)
-
if the default database is not the one you will work at, you have to specify the database and also notice the "space":isql /Sservername /Uusername /Ppassword /ddatabasename /Q"select * from employee" > "C:\outputfile
-yangn(Raymond);
2002-9-25
{103}
(#766228@0)
-
用DTS做的话, 3分钟就做好了, 还可以把转换规则保存下来, 下次修改. 估计看ISQL的说明也不止3分钟吧? 好好的工具, 为什么就不用?
-zhihaoxx(热爱FB的LOSER 甲);
2002-9-25
(#766253@0)
-
我就来和你讲讲区别。
-yangn(Raymond);
2002-9-25
{711}
(#766350@0)
-
gaoshou, It doesn't work :(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
-oceandeep(北极熊® Zzz Zzz);
2002-9-25
{395}
(#766419@0)
-
both bcp and isql can transfer data from sql to text file.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!
-yangn(Raymond);
2002-9-25
{316}
(#766459@0)
-
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 osqlisql 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
-handd(handd);
2002-9-25
{145}
(#766468@0)
-
Some clarifications.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.
-yangn(Raymond);
2002-10-3
{381}
(#779812@0)
-
各有各的好处, 我也不跟你争, 我的工作环境要跟不同的文件格式打交道, 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 muchBTW, how to get Today's date in SQL SERVER script?
like
select * from tablename where tablename.businessdate=TODAY
-oceandeep(北极熊® Zzz Zzz);
2002-9-25
{120}
(#766560@0)
-
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)