Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hyukevain
Yak Posting Veteran
66 Posts |
 Posted - 2004-07-29 : 03:53:50
|
| Hello,I want to copy one of my table content to a text file.The text file name will be "Table Name" + One of the source table column value + "created data"Example -> Table Name : Company Field in Table Company : CompID VarChar(2), CompNo VarChar(2), CompName VarChar(50)The text file name will be "Company" + CompNo + Today DateHow can I get the CompNo value from Company Table and use it in my file name ?Can I get the CompNo value without open any new connection to my DB (not use any new recordset in my ActiveX script) ?Can I get the sample code ?Regards,Mike |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-29 : 06:09:28
|
| ? Why do you want to use dts for this. Have a look at bcp - it's simpler and faster.If you want to use dts then look at the dynamic properties task (I would advise setting a global variable then using that).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Hyukevain
Yak Posting Veteran
66 Posts |
Posted - 2004-07-29 : 07:58:17
|
| Hello nr,Currently I build an consolidation app, data from branch are exported each periode to text then send the text to HQ.Each branch have different CompNo.My text file name must be : TableName + CompNo + DataPeriod because the textfile name will be the table name in Consolidation DB (Need for tracing data from each branch in every period -> the database design already exists =P)The DBA not allowing me to access the DB thought my App directly, only allow DTS only (I don't know why)So, my DTS must read the CompNo Data from Company table and store in a variable (GV ?).I use Ms Sql Server 7, they don't have Dynamic Properties Task =(, so I need to configure the variable using ActiveX Script.Can I create DTS that don't have to use an ADODB Connection in my ActiveX Script to get the CompNo value (only the simplest way) ?Need them so badly.regards,Mike |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-29 : 11:15:53
|
| Still put the value in a global variable - makes testing and logging easier.You can get the value from the database viahttp://www.nigelrivett.net/DTSExecuteStoredProcedure.html(That's for an SP which should make the dba happy but if not embedded sql is similar)To use it you can either rename the file to something static or access the transformation task and change the filenameBoth mean altering a task.This is for a connectionhttp://www.nigelrivett.net/SetDTSRunTimeValues.htmlOther things are similar.For renaming the file just create a o/s command task and change the commend in an activex script.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Hyukevain
Yak Posting Veteran
66 Posts |
Posted - 2004-07-29 : 23:26:09
|
| nr,After I read many reference stated in your web, I become more interest to BCP.I have a few questions about BCP,First, can BCP run in the client side (no sql server installed at client) without running xp_CmdShell from server ? Because xp_CmdShell return the result text file that generated to server (I mean when I said C:\, it means C:\ in server not in client).If it can, what file, library, etc that I need to install them to client ?Is that true that bcp can't handle long query string in Ms Sql Server 7 ?Have some more example about bcp ?Thank'sMike |
|
|
|
|
|
|
|
|
Lookup in DTS