| Author |
Topic |
|
Dan_2004
Starting Member
13 Posts |
 Posted - 2004-02-04 : 09:00:47
|
Hi,I have to create a text file with DTS, but the name of the text file could be the system date. Do you have a suggest to do it? Tia,Dan |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-04 : 09:03:27
|
| Put in an activex script to set the name of the file created by a transformation task.You could probably do the same thing with a dynamic properties task.==========================================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. |
 |
|
|
Dan_2004
Starting Member
13 Posts |
Posted - 2004-02-04 : 15:34:16
|
thanks NR for your information,but if i create a DTS package by the SQL Server enterprise Manager\DTS\Local Packages, can i use an 'Execute Task SQL' task with the following script to rename a file with the system date?DECLARE @cmd2 varchar(1000), @v_date varchar(40)SET @v_date = convert(varchar,getdate(),126)SET @cmd2 = 'rename c:\TEMP\Toto.txt ' + @v_date + '.txt'exec master..xp_cmdshell @cmd2 TiaDan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-04 : 15:40:09
|
| Have you tried it?Yes you can use DOS to modify file names and yes you can do that in an Execute SQL Task. But to use xp_cmdshell, you'll need sysadmin privileges. Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-04 : 15:42:08
|
| That should work - give it a try.I would use sometihg like replace(replace(replace(convert(varchar(19),getdate(),121),'-',''),' ','_'),':','')for the date.==========================================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. |
|
|
|
Dan_2004
Starting Member
13 Posts |
Posted - 2004-02-04 : 15:44:28
|
| Hi Tara,I tried it but i received an error message. I don't know another solution to resolve it. NR suggest me to write an Activex script but i don't know how can i do that. It's my first step with it and i search some helps everywhere!Can you suggest me something to resolve it?Tia,Dan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-04 : 15:52:28
|
| Run your command in Query Analyzer. Once it works there, move it into DTS.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-04 : 15:57:12
|
quote:
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure. When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Try this and tell me what happens:EXEC master..xp_cmdshell 'Dir c:\*.*' Brett8-)EDIT: Did we confirm s/he can't use 'shell?Also if you can...why not use bcp in a sproc? |
|
|
|
Dan_2004
Starting Member
13 Posts |
Posted - 2004-02-05 : 08:33:58
|
| Good morning everybody!Thanks for your suggestions, i aded the solution of NR and everything works fine!Thanks again,Dan |
|
|
|
|
How can i create a text file with DTS?