delete a text file

I have a Job which imports from a text file. The second step is to delete th
e
text file. I have use the following code and it reports success but the file
is still there. Any ideas?
EXEC master..xp_cmdshell 'Del D:\Documents and
Settings\Administrator\Desktop\testextra
ct.txt'Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Windows logon permission. I am always in as admin
"Aaron Bertrand [SQL Server MVP]" wrote:

> Who is SQL Server service starting as? My guess is that the account does
> not have write/modify permissions on a user's folder.
>
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>
>|||> Windows logon permission. I am always in as admin
In every single installation I have ever seen, *YOU* are not the user
account SQL Server is running as.
Go to the control panel on the SQL Server machine, open up the services
control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
the Log On tab. Dollars to donuts says that account is not your account.
But it is a given that *that* account needs permissions on the folder where
xp_cmdshell is attempting to play.|||You are right!! Placed the folder on another drive and it worked perfectly.
Thanks so much
"Aaron Bertrand [SQL Server MVP]" wrote:

> In every single installation I have ever seen, *YOU* are not the user
> account SQL Server is running as.
> Go to the control panel on the SQL Server machine, open up the services
> control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
> the Log On tab. Dollars to donuts says that account is not your account.
> But it is a given that *that* account needs permissions on the folder wher
e
> xp_cmdshell is attempting to play.
>
>

Delay in package starting when running from SQL Agent

Hi,

I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.

The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.

When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.

I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.

Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.

Thanks,

Sam

That sounds very strange. Although I'd guess its a SQL Server Agent problem rather than SSIS.

Can you replace the steps with something else - some simple command-line calls for example, and see if the same thing happens?

Do the log fiels for SQL Server Agent and SSIS tie up? i.e. The package may start 30 minutes late but did the job step start 30 minutes late (there's an important distinction here)?

-Jamie

|||

hi sam, I can think over that problem is that your sql agent is very busy attending other jobs ?

|||

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

|||

Enric,

Thanks for the reply, but this is the only job on the server at the moment, so that shouldn't be causing a problem.

Sam

|||

sam2005 wrote:

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

If that is the case then I would suggest that the delay is caused by the package going through validation. Set DelayValidation=TRUE on the package to see if this removes the delay. If it doesn't, set DelayValidation=TRUE on all your containers and tasks and see if this removes the delay.

If this solves the problem then you know that it is the validation step that is causing the delay. Try doing what i suggested above and then reply here and we'll take it from there!

-Jamie

|||

this is probably a longshot...

do you see this problem when you run package in bi studio?

is it possible that the service startup is slow?

there is a kb article that talks about problem in sp1

http://support.microsoft.com/?kbid=918644

|||

The DelayValidation at the package level, as suggested by Jamie, seems to have done the trick. I also found that there was a msmsgs.exe process running which was constantly using half the processor - killing this has sped things up even more.

Would the DelayValidation setting have any other impact on the package?

Sam

sql

define default for date report parameter / analysis services

I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.

So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.

SELECT measures.turnover ON COLUMNS,

nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS

FROM [Itdev1 Hk]

I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.

Now, how do I get it to default to the last valid member in the list?

I presume you are trying to have the latest date selected by default? If so, return your dataset in descending order (do an ORDER(<set>, DESC) on the rows), so that your latest date is at the top of the list.

|||this is helpful since now when I click the drop down the most likely values for me to use are at the top. but it has not caused any value to be selected by default.
|||You can use the same dataset in the default value query, which seems to collapse to the first row returned. I have no idea what the implications of doing this are, I happened on it by accident.|||

this is a helpful tip!

|||

i;m attempting to order by date in descending order. but it seems to sort in a random order when I do this ....

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date], desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

and then in alphabetical order when I sort like this

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date].MemberValue, desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

how do I get it to sort by real date?