ForEachLoop task not behaving as expected

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 > script2 > script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Do you have the tasks hooked together by precedence constraints? (The green arrows?)|||

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

Forcing Users to log out of SQL 2000

I am a begginer to SQL. I am truly limited with SQL language.

Every month we have to do a month end closing for our company. In order for us to do this we need to get everyone off the database to proceed. This process is horrible and generally takes at least 1/2hr to 45 min. to do so. i am looking for a command that i can use in query analyzer if possible to force the users out of the database. if that isnt an option can you recommend another program in which i can do this.? i am at a loss and need some help..... thanks

Did you have a look in the BOL for ALTER DATABASE ?

ALTER DATABASE SomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

or from the syntax with a delay of x seconds:

<termination> ::=
{
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
}

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

i am unsure of what you mean. as i stated i am truly a beginner with SQL.

can you explain it to me like i am 4? lol

thanks

|||

SUre thisis quite easy, the command kills all the user sessions immediately. If you want to wait a respective moment you can use the WAIT FOR keyword, but with the keyword IMMEDIATELY all User will be banned out of the database and their transactions will be roled back (if any transaction is open)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

what is the command?

can i use it in query analyze?

thanks again

|||You can use it in QA to force the users (as I mentioned before) to log out, current transactions will be rolled back. Make sure if this complies with your maintaince strategy (not every user is pleased to be logged out while he is doing a transaction :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

ok thank you i will try this at the end of the month

i do appriciate your time!!

thanks

Forcing a Job Failure Based on time

Hi everyone.
I have a SQL job in which the last step calls an outside cmd batch. This batch often gets suspended indefinitely. In order to allow the SQL job to complete I periodically run a scheduled command that kills the suspended batch. This allows the SQL job t
o end, but it does not end it in a failure. The job reports successful execution. Is there a creative way to force the job to return failure status in this case?
I appreciate your help
Oscar
Is there a way that you could include some commands within that non-SQL job
that will handle the timeout?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Forcing a Job Failure Based on time

Hi everyone.
I have a SQL job in which the last step calls an outside cmd batch. This ba
tch often gets suspended indefinitely. In order to allow the SQL job to com
plete I periodically run a scheduled command that kills the suspended batch.
This allows the SQL job t
o end, but it does not end it in a failure. The job reports successful exec
ution. Is there a creative way to force the job to return failure status in
this case?
I appreciate your help
OscarIs there a way that you could include some commands within that non-SQL job
that will handle the timeout?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Forcing a Job Failure Based on time

Hi everyone.
I have a SQL job in which the last step calls an outside cmd batch. This batch often gets suspended indefinitely. In order to allow the SQL job to complete I periodically run a scheduled command that kills the suspended batch. This allows the SQL job to end, but it does not end it in a failure. The job reports successful execution. Is there a creative way to force the job to return failure status in this case?
I appreciate your help
OscarIs there a way that you could include some commands within that non-SQL job
that will handle the timeout?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Force to date format MMDDYY

My sql :

SELECT....., date_ of_ shipment,.....

FROM......

WHERE.....

ORDER BY....

After that it will export to excel.

My problem: Some times I get a column of mixing date format MMDDYY and number.?

I have to format the whole excel column to get all date format.

My question: How can I write sql so it force the the result to be date format MMDDYY in excel? Where this clause in sql (where clause ?)

Thanks
Daniel

Code Snippet

select convert(varchar(8), getdate(), 1)

OR

select replace(convert(varchar(8), getdate(), 1), '/', '')

Depending on whether you want the slash separators or not.

Use code 101 instead of 1 in the Convert if you want 4 digit years.

|||

If my column name is date_of _shipment

Select convert (varchar(8), date_of_shipment,1) ?

Thnks

Daniel

|||

Right.

It will also lose it's name, so you may want to re-alias it.

Code Snippet

Select convert (varchar(8), date_of_shipment,1) AS date_of_shipment

|||

Thanks a millions. Look like it work great for what ever my excel column format.

Daniel

|||

I may need hour: min: second too

How about MMDDYYYYHHMMSS ?

Thanks
Daniel

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

force a specific order of record in a table

I want the record in table is sorted, I try to use index but not succesful,
why?
I am using SQLServer 2000Use ORDER BY clause
"kei" <kei@.discussions.microsoft.com> wrote in message
news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>I want the record in table is sorted, I try to use index but not succesful,
> why?
> I am using SQLServer 2000|||YOu could create a clustered index which defines the rows / data to be
physically sorted after the clustered key. but if you want to get the
data in an ordered way, the best thing is to use the ORDER BY clause.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||No, I means the physical order, not using sql statement, anyway, I think the
answer is clustered index, thx!!
"Uri Dimant" wrote:

> Use ORDER BY clause
>
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>
>|||kei
No, don't relay on phisycal order .If you wanted to get out a sorted result
use ORDER BY clause for your safety
"kei" <kei@.discussions.microsoft.com> wrote in message
news:47E2427C-C55B-4970-808F-8AF193920F36@.microsoft.com...[vbcol=seagreen]
> No, I means the physical order, not using sql statement, anyway, I think
> the
> answer is clustered index, thx!!
> "Uri Dimant" wrote:
>|||No that wouldn't actually solve it as Uri already explained.
The why is due to the definition of a table in relational
database systems. A table has unordered rows and columns.
The physical order of the columns and rows does not, should
not matter. You manage ordering through SQL statements and
order by clauses.
-Sue
On Wed, 26 Apr 2006 02:40:01 -0700, kei
<kei@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No, I means the physical order, not using sql statement, anyway, I think th
e
>answer is clustered index, thx!!
>"Uri Dimant" wrote:
>

force a specific order of record in a table

I want the record in table is sorted, I try to use index but not succesful,
why?
I am using SQLServer 2000YOu could create a clustered index which defines the rows / data to be
physically sorted after the clustered key. but if you want to get the
data in an ordered way, the best thing is to use the ORDER BY clause.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Use ORDER BY clause
"kei" <kei@.discussions.microsoft.com> wrote in message
news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>I want the record in table is sorted, I try to use index but not succesful,
> why?
> I am using SQLServer 2000|||kei
No, don't relay on phisycal order .If you wanted to get out a sorted result
use ORDER BY clause for your safety
"kei" <kei@.discussions.microsoft.com> wrote in message
news:47E2427C-C55B-4970-808F-8AF193920F36@.microsoft.com...
> No, I means the physical order, not using sql statement, anyway, I think
> the
> answer is clustered index, thx!!
> "Uri Dimant" wrote:
>> Use ORDER BY clause
>>
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>> >I want the record in table is sorted, I try to use index but not
>> >succesful,
>> > why?
>> > I am using SQLServer 2000
>>|||No, I means the physical order, not using sql statement, anyway, I think the
answer is clustered index, thx!!
"Uri Dimant" wrote:
> Use ORDER BY clause
>
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
> >I want the record in table is sorted, I try to use index but not succesful,
> > why?
> > I am using SQLServer 2000
>
>|||No that wouldn't actually solve it as Uri already explained.
The why is due to the definition of a table in relational
database systems. A table has unordered rows and columns.
The physical order of the columns and rows does not, should
not matter. You manage ordering through SQL statements and
order by clauses.
-Sue
On Wed, 26 Apr 2006 02:40:01 -0700, kei
<kei@.discussions.microsoft.com> wrote:
>No, I means the physical order, not using sql statement, anyway, I think the
>answer is clustered index, thx!!
>"Uri Dimant" wrote:
>> Use ORDER BY clause
>>
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:0DB8A9B7-CFD0-4671-B585-9B8E948E4C9E@.microsoft.com...
>> >I want the record in table is sorted, I try to use index but not succesful,
>> > why?
>> > I am using SQLServer 2000
>>

For each file - ordering?

Does anyone know in what order files are processed if using a for each container and file enumerator? Does it sort files alphabetically and loop through? Or use a date time stamp? Or something else? I need files to be processed in a certain order, and the ordering is in the filename, so the foreach file enumerator sorts on filename, then I'm ok, otherwise I need to figure something else out. Thanks.

alphabetically. But then you could always test it out on your side.
|||The sort order on this is not guaranteed (unless something has changed since the last time I researched it). As Phil mentioned, it typically does return results alphabetically, but it is not guaranteed to always return them that way. If ordering is critical, you should check out this post for some options and some script for sorting files: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460241&SiteID=1|||

I bet that whatever API the enumerator uses, ends with calling this Win32 function:

http://msdn2.microsoft.com/en-us/library/aa364418.aspx

The documentation for which says:

The order in which the search returns the files, such as alphabetical order, is not guaranteed, and is dependent on the file system. You cannot depend on any specific ordering behavior. If the data must be sorted, you must do the ordering yourself after obtaining all the results.

For each file - ordering?

Does anyone know in what order files are processed if using a for each container and file enumerator? Does it sort files alphabetically and loop through? Or use a date time stamp? Or something else? I need files to be processed in a certain order, and the ordering is in the filename, so the foreach file enumerator sorts on filename, then I'm ok, otherwise I need to figure something else out. Thanks.

alphabetically. But then you could always test it out on your side.
|||The sort order on this is not guaranteed (unless something has changed since the last time I researched it). As Phil mentioned, it typically does return results alphabetically, but it is not guaranteed to always return them that way. If ordering is critical, you should check out this post for some options and some script for sorting files: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460241&SiteID=1|||

I bet that whatever API the enumerator uses, ends with calling this Win32 function:

http://msdn2.microsoft.com/en-us/library/aa364418.aspx

The documentation for which says:

The order in which the search returns the files, such as alphabetical order, is not guaranteed, and is dependent on the file system. You cannot depend on any specific ordering behavior. If the data must be sorted, you must do the ordering yourself after obtaining all the results.