Foreach loop process error

Hello,

In a Foreach loop component, i have this message at 2nd or 3rd loop but never at first.

[DTS.Pipeline] Error: The ProcessInput method on component "Sort" (716) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Does anyone can help me ?

Coroebus wrote:

Hello,

In a Foreach loop component, i have this message at 2nd or 3rd loop but never at first.

[DTS.Pipeline] Error: The ProcessInput method on component "Sort" (716) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Does anyone can help me ?

I *may* be wrong but I believe this is a known bug and has been fixed in the next CTP. Something to do with the Sort component grabbing too much memory when used inside a Foreach loop.

-Jamie|||Yes, we have fixed some of these already. It would be great if you can confirm this when the new CTP comes out.

thanks!|||

Ash Sharma wrote:

Yes, we have fixed some of these already. It would be great if you can confirm this when the new CTP comes out.

Any sharable info on when that might take place?

ForEach Loop Editor

Help,

Trying to through a process together and using the "ForEach Loop" task. When configuring the task and using the ForEach Loop Editor I do not have the "ForEach File Enumerator" in the drop down. Anyone know whats going on or what I need to do to make this appear. I only see 5 selections: ADO, ADO.Net Schema Rowset, Variable, NodeList, and SMO Enumerators. I am also using the SQL 2005 eval with SP1.

Thanks,

John

This has been asked many times before:

https://forums.microsoft.com/MSDN/Search/Search.aspx?words=enumerator+missing&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

-Jamie

|||

Jamie,

Thanks for the info, but feel uncomfortable since I have never compiled C# and not sure if I can do this within BIDS. After reading the posts it appears that some of the registry settings do not have the correct permissions. Would you happen to know which reg settings I need change the permissions for and what permissions they need to have?

Thanks,

John

Foreach loop container

Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop Container, and I don't know how to process it with Table, means for each record of this table I have to import from one folder some text files. so Idon't know how to connect my table to Foreach Loop container.

Thanks

1. Use Exec. SQL Task to query the table
2. Store the result record set in a variable
3. Use FE Loop to iterate over the record set

|||So it sounds like you want to loop over some files and then import that

file am I right?

Would this help?

Looping over files with the Foreach Loop

(http://www.sqlis.com/default.aspx?55)

Allan

"Mbd@.discussions.microsoft.com" wrote in

message

news:a05fa88a-80ad-4525-a833-370c4473b67a@.discussions.microsoft.com:

> Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop

> Container, and I don't know how to process it with Table, means for each

> record of this table I have to import from one folder some text files.

> so Idon't know how to connect my table to Foreach Loop container.

>

> Thanks|||Hi ,

thanks for your help, but is not what I need, maybe my request was not clear, so my question is : Looping over table with the Foreach Loop,

thanks in advance for your answer.

Mdb|||Hi,

Thanks for your replay, please how to do the third part ,

3.Use FE Loop to iterate over the record set

thanks|||

? how to do the third part

- In FE Loop use Foreach ADO Enumerator
- As ADO Object source variable - choose the variable used in the step (2) above
- In Variables Mapping page you can map column #1 to variable ..., column #2 to variable ..., etc

sql

Foreach loop container

Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop Container, and I don't know how to process it with Table, means for each record of this table I have to import from one folder some text files. so Idon't know how to connect my table to Foreach Loop container.

Thanks

1. Use Exec. SQL Task to query the table
2. Store the result record set in a variable
3. Use FE Loop to iterate over the record set

|||So it sounds like you want to loop over some files and then import that file am I right? Would this help? Looping over files with the Foreach Loop (http://www.sqlis.com/default.aspx?55) Allan "Mbd@.discussions.microsoft.com" wrote in message news:a05fa88a-80ad-4525-a833-370c4473b67a@.discussions.microsoft.com:
> Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop
> Container, and I don't know how to process it with Table, means for each
> record of this table I have to import from one folder some text files.
> so Idon't know how to connect my table to Foreach Loop container. >
> Thanks|||Hi ,

thanks for your help, but is not what I need, maybe my request was not clear, so my question is : Looping over table with the Foreach Loop,

thanks in advance for your answer.

Mdb|||Hi,

Thanks for your replay, please how to do the third part ,

3.Use FE Loop to iterate over the record set

thanks

|||

? how to do the third part

- In FE Loop use Foreach ADO Enumerator
- As ADO Object source variable - choose the variable used in the step (2) above
- In Variables Mapping page you can map column #1 to variable ..., column #2 to variable ..., etc

Force SQL Server Database offline

I had a user kill a process that is now doing a rollback that is locking up
the server. Is there any way to force that databse offline? I tried using
Alter Database offline in Query analyser and in Enterprise Manger. get 5070
error (user connected)
1) Please define "locking up the server".
2) I would imagine that you can't alter database state (i.e. take it
offline) while it is undergoing a transaction rollback. I could be wrong on
this though.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>I had a user kill a process that is now doing a rollback that is locking up
> the server. Is there any way to force that databse offline? I tried
> using
> Alter Database offline in Query analyser and in Enterprise Manger. get
> 5070
> error (user connected)
|||Yes it is in a rollback state. I guess locking up is the wrong choice of
words where. The transction is blocking other work on the DB that is more
important. I don't care about the state that the database would be left in.
"TheSQLGuru" wrote:

> 1) Please define "locking up the server".
> 2) I would imagine that you can't alter database state (i.e. take it
> offline) while it is undergoing a transaction rollback. I could be wrong on
> this though.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>
>
|||I don't think there is anything that can be done to stop a rolling back
transaction from affecting other work. Even pulling the plug on the server
will simply reset the rollback and it will restart when the server comes
back online. At least that is my understanding.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...[vbcol=seagreen]
> Yes it is in a rollback state. I guess locking up is the wrong choice of
> words where. The transction is blocking other work on the DB that is more
> important. I don't care about the state that the database would be left
> in.
> "TheSQLGuru" wrote:
|||Kevin,
Not that I recommend this, but... At least in SQL Server 2000 and earlier,
shutting down the SQL Server and restarting it could indeed be faster than
allowing a long rollback to continue. It seems that the roll-forward was
much faster than the rollback. The few times that I did this (in
desperation) I found no evidence of corruption. (But it is the sort of
thing to make a DBA nervous, which is why I don't recommend it.)
One Upon A Time... I uncovered one possible reason for a very long rollback
in SQL Server 2000:
I had an extremely busy database and it was very trigger heavy. It appeared
that all of the trigger activity, which in 2000 scanned the transaction log,
actually hindered the rollback log processing from making much headway.
This is inferred from monitoring a very long rollback. (Obviously, I did
not restart the SQL Server this time.) The scenario was:
1. Long, out-of-control process had to be terminated. KILL SPID
2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slowly
rewound to about 80% remaining to rollback.
3. At the end of the day, as the load on the database disappeared, it
finished rolling back in less than 30 minutes.
Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, then
0.5 hours to rollback 80%.
In SQL Server 2005 over the last 18 months I have not had a similar
experience, for what it is worth.
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13lbf5bgbd4uf8a@.corp.supernews.com...
>I don't think there is anything that can be done to stop a rolling back
>transaction from affecting other work. Even pulling the plug on the server
>will simply reset the rollback and it will restart when the server comes
>back online. At least that is my understanding.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
> news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
>
|||Even if you restart the service , SQL Server will recover each database on
startup. And on this praticular database with redo/undo transactions it may
take ages. If you have the backup along with log backup I'd reccomend to
stop the server, drop the database --restart and restore the database.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...[vbcol=seagreen]
> Russell;
>
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
|||Linchi,
Yes, that is a good question. The answer is, we had some code in those days
that caused this problem more than once. So, I got to practice both paths -
(a) restart the server, and (b) wait it out - at least two or three times.
Then the problem finally got solved.
So, despite everything we hold dear, it really was much (more than an hour)
faster. I do understand the logic of roll-forward and rollback, and the
arguments for why it is a bad idea and why it should not have worked. (But
it did.)
And, No, I have not done it in many years now. I just tell people that they
have to wait and that the code must be revised. :-)
RLF
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...[vbcol=seagreen]
> Russell;
>
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:

Force SQL Server Database offline

I had a user kill a process that is now doing a rollback that is locking up
the server. Is there any way to force that databse offline? I tried using
Alter Database offline in Query analyser and in Enterprise Manger. get 5070
error (user connected)1) Please define "locking up the server".
2) I would imagine that you can't alter database state (i.e. take it
offline) while it is undergoing a transaction rollback. I could be wrong on
this though.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>I had a user kill a process that is now doing a rollback that is locking up
> the server. Is there any way to force that databse offline? I tried
> using
> Alter Database offline in Query analyser and in Enterprise Manger. get
> 5070
> error (user connected)|||Yes it is in a rollback state. I guess locking up is the wrong choice of
words where. The transction is blocking other work on the DB that is more
important. I don't care about the state that the database would be left in.
"TheSQLGuru" wrote:

> 1) Please define "locking up the server".
> 2) I would imagine that you can't alter database state (i.e. take it
> offline) while it is undergoing a transaction rollback. I could be wrong
on
> this though.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>
>|||I don't think there is anything that can be done to stop a rolling back
transaction from affecting other work. Even pulling the plug on the server
will simply reset the rollback and it will restart when the server comes
back online. At least that is my understanding.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...[vbcol=seagreen]
> Yes it is in a rollback state. I guess locking up is the wrong choice of
> words where. The transction is blocking other work on the DB that is more
> important. I don't care about the state that the database would be left
> in.
> "TheSQLGuru" wrote:
>|||Kevin,
Not that I recommend this, but... At least in SQL Server 2000 and earlier,
shutting down the SQL Server and restarting it could indeed be faster than
allowing a long rollback to continue. It seems that the roll-forward was
much faster than the rollback. The few times that I did this (in
desperation) I found no evidence of corruption. (But it is the sort of
thing to make a DBA nervous, which is why I don't recommend it.)
One Upon A Time... I uncovered one possible reason for a very long rollback
in SQL Server 2000:
I had an extremely busy database and it was very trigger heavy. It appeared
that all of the trigger activity, which in 2000 scanned the transaction log,
actually hindered the rollback log processing from making much headway.
This is inferred from monitoring a very long rollback. (Obviously, I did
not restart the SQL Server this time.) The scenario was:
1. Long, out-of-control process had to be terminated. KILL SPID
2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slowly
rewound to about 80% remaining to rollback.
3. At the end of the day, as the load on the database disappeared, it
finished rolling back in less than 30 minutes.
Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, then
0.5 hours to rollback 80%.
In SQL Server 2005 over the last 18 months I have not had a similar
experience, for what it is worth.
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13lbf5bgbd4uf8a@.corp.supernews.com...
>I don't think there is anything that can be done to stop a rolling back
>transaction from affecting other work. Even pulling the plug on the server
>will simply reset the rollback and it will restart when the server comes
>back online. At least that is my understanding.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
> news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
>|||Russell;

> At least in SQL Server 2000 and earlier,
> shutting down the SQL Server and restarting it could indeed be faster than
> allowing a long rollback to continue.
How did you know that restarting SQL was faster in finishing a rollback? How
did you know that the rollback would not have taken as long to finish if you
had allowed it to finish without restarting SQL? Just curious.
Linchi
"Russell Fields" wrote:

> Kevin,
> Not that I recommend this, but... At least in SQL Server 2000 and earlie
r,
> shutting down the SQL Server and restarting it could indeed be faster than
> allowing a long rollback to continue. It seems that the roll-forward was
> much faster than the rollback. The few times that I did this (in
> desperation) I found no evidence of corruption. (But it is the sort of
> thing to make a DBA nervous, which is why I don't recommend it.)
> One Upon A Time... I uncovered one possible reason for a very long rollbac
k
> in SQL Server 2000:
> I had an extremely busy database and it was very trigger heavy. It appear
ed
> that all of the trigger activity, which in 2000 scanned the transaction lo
g,
> actually hindered the rollback log processing from making much headway.
> This is inferred from monitoring a very long rollback. (Obviously, I did
> not restart the SQL Server this time.) The scenario was:
> 1. Long, out-of-control process had to be terminated. KILL SPID
> 2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slow
ly
> rewound to about 80% remaining to rollback.
> 3. At the end of the day, as the load on the database disappeared, it
> finished rolling back in less than 30 minutes.
> Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, th
en
> 0.5 hours to rollback 80%.
> In SQL Server 2005 over the last 18 months I have not had a similar
> experience, for what it is worth.
> RLF
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13lbf5bgbd4uf8a@.corp.supernews.com...
>
>|||Even if you restart the service , SQL Server will recover each database on
startup. And on this praticular database with redo/undo transactions it may
take ages. If you have the backup along with log backup I'd reccomend to
stop the server, drop the database --restart and restore the database.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...[vbcol=seagreen]
> Russell;
>
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
>|||Linchi,
Yes, that is a good question. The answer is, we had some code in those days
that caused this problem more than once. So, I got to practice both paths -
(a) restart the server, and (b) wait it out - at least two or three times.
Then the problem finally got solved.
So, despite everything we hold dear, it really was much (more than an hour)
faster. I do understand the logic of roll-forward and rollback, and the
arguments for why it is a bad idea and why it should not have worked. (But
it did.)
And, No, I have not done it in many years now. I just tell people that they
have to wait and that the code must be revised. :-)
RLF
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...[vbcol=seagreen]
> Russell;
>
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
>

Force SQL Server Database offline

I had a user kill a process that is now doing a rollback that is locking up
the server. Is there any way to force that databse offline? I tried using
Alter Database offline in Query analyser and in Enterprise Manger. get 5070
error (user connected)1) Please define "locking up the server".
2) I would imagine that you can't alter database state (i.e. take it
offline) while it is undergoing a transaction rollback. I could be wrong on
this though.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>I had a user kill a process that is now doing a rollback that is locking up
> the server. Is there any way to force that databse offline? I tried
> using
> Alter Database offline in Query analyser and in Enterprise Manger. get
> 5070
> error (user connected)|||Yes it is in a rollback state. I guess locking up is the wrong choice of
words where. The transction is blocking other work on the DB that is more
important. I don't care about the state that the database would be left in.
"TheSQLGuru" wrote:
> 1) Please define "locking up the server".
> 2) I would imagine that you can't alter database state (i.e. take it
> offline) while it is undergoing a transaction rollback. I could be wrong on
> this though.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
> >I had a user kill a process that is now doing a rollback that is locking up
> > the server. Is there any way to force that databse offline? I tried
> > using
> > Alter Database offline in Query analyser and in Enterprise Manger. get
> > 5070
> > error (user connected)
>
>|||I don't think there is anything that can be done to stop a rolling back
transaction from affecting other work. Even pulling the plug on the server
will simply reset the rollback and it will restart when the server comes
back online. At least that is my understanding.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
> Yes it is in a rollback state. I guess locking up is the wrong choice of
> words where. The transction is blocking other work on the DB that is more
> important. I don't care about the state that the database would be left
> in.
> "TheSQLGuru" wrote:
>> 1) Please define "locking up the server".
>> 2) I would imagine that you can't alter database state (i.e. take it
>> offline) while it is undergoing a transaction rollback. I could be wrong
>> on
>> this though.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
>> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>> >I had a user kill a process that is now doing a rollback that is locking
>> >up
>> > the server. Is there any way to force that databse offline? I tried
>> > using
>> > Alter Database offline in Query analyser and in Enterprise Manger. get
>> > 5070
>> > error (user connected)
>>|||Kevin,
Not that I recommend this, but... At least in SQL Server 2000 and earlier,
shutting down the SQL Server and restarting it could indeed be faster than
allowing a long rollback to continue. It seems that the roll-forward was
much faster than the rollback. The few times that I did this (in
desperation) I found no evidence of corruption. (But it is the sort of
thing to make a DBA nervous, which is why I don't recommend it.)
One Upon A Time... I uncovered one possible reason for a very long rollback
in SQL Server 2000:
I had an extremely busy database and it was very trigger heavy. It appeared
that all of the trigger activity, which in 2000 scanned the transaction log,
actually hindered the rollback log processing from making much headway.
This is inferred from monitoring a very long rollback. (Obviously, I did
not restart the SQL Server this time.) The scenario was:
1. Long, out-of-control process had to be terminated. KILL SPID
2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slowly
rewound to about 80% remaining to rollback.
3. At the end of the day, as the load on the database disappeared, it
finished rolling back in less than 30 minutes.
Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, then
0.5 hours to rollback 80%.
In SQL Server 2005 over the last 18 months I have not had a similar
experience, for what it is worth.
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13lbf5bgbd4uf8a@.corp.supernews.com...
>I don't think there is anything that can be done to stop a rolling back
>transaction from affecting other work. Even pulling the plug on the server
>will simply reset the rollback and it will restart when the server comes
>back online. At least that is my understanding.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
> news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
>> Yes it is in a rollback state. I guess locking up is the wrong choice of
>> words where. The transction is blocking other work on the DB that is
>> more
>> important. I don't care about the state that the database would be left
>> in.
>> "TheSQLGuru" wrote:
>> 1) Please define "locking up the server".
>> 2) I would imagine that you can't alter database state (i.e. take it
>> offline) while it is undergoing a transaction rollback. I could be
>> wrong on
>> this though.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
>> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>> >I had a user kill a process that is now doing a rollback that is
>> >locking up
>> > the server. Is there any way to force that databse offline? I tried
>> > using
>> > Alter Database offline in Query analyser and in Enterprise Manger.
>> > get
>> > 5070
>> > error (user connected)
>>
>|||Russell;
> At least in SQL Server 2000 and earlier,
> shutting down the SQL Server and restarting it could indeed be faster than
> allowing a long rollback to continue.
How did you know that restarting SQL was faster in finishing a rollback? How
did you know that the rollback would not have taken as long to finish if you
had allowed it to finish without restarting SQL? Just curious.
Linchi
"Russell Fields" wrote:
> Kevin,
> Not that I recommend this, but... At least in SQL Server 2000 and earlier,
> shutting down the SQL Server and restarting it could indeed be faster than
> allowing a long rollback to continue. It seems that the roll-forward was
> much faster than the rollback. The few times that I did this (in
> desperation) I found no evidence of corruption. (But it is the sort of
> thing to make a DBA nervous, which is why I don't recommend it.)
> One Upon A Time... I uncovered one possible reason for a very long rollback
> in SQL Server 2000:
> I had an extremely busy database and it was very trigger heavy. It appeared
> that all of the trigger activity, which in 2000 scanned the transaction log,
> actually hindered the rollback log processing from making much headway.
> This is inferred from monitoring a very long rollback. (Obviously, I did
> not restart the SQL Server this time.) The scenario was:
> 1. Long, out-of-control process had to be terminated. KILL SPID
> 2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it slowly
> rewound to about 80% remaining to rollback.
> 3. At the end of the day, as the load on the database disappeared, it
> finished rolling back in less than 30 minutes.
> Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%, then
> 0.5 hours to rollback 80%.
> In SQL Server 2005 over the last 18 months I have not had a similar
> experience, for what it is worth.
> RLF
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13lbf5bgbd4uf8a@.corp.supernews.com...
> >I don't think there is anything that can be done to stop a rolling back
> >transaction from affecting other work. Even pulling the plug on the server
> >will simply reset the rollback and it will restart when the server comes
> >back online. At least that is my understanding.
> >
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> >
> >
> > "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
> > news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
> >> Yes it is in a rollback state. I guess locking up is the wrong choice of
> >> words where. The transction is blocking other work on the DB that is
> >> more
> >> important. I don't care about the state that the database would be left
> >> in.
> >>
> >> "TheSQLGuru" wrote:
> >>
> >> 1) Please define "locking up the server".
> >>
> >> 2) I would imagine that you can't alter database state (i.e. take it
> >> offline) while it is undergoing a transaction rollback. I could be
> >> wrong on
> >> this though.
> >>
> >> --
> >> Kevin G. Boles
> >> TheSQLGuru
> >> Indicium Resources, Inc.
> >>
> >>
> >> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in message
> >> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
> >> >I had a user kill a process that is now doing a rollback that is
> >> >locking up
> >> > the server. Is there any way to force that databse offline? I tried
> >> > using
> >> > Alter Database offline in Query analyser and in Enterprise Manger.
> >> > get
> >> > 5070
> >> > error (user connected)
> >>
> >>
> >>
> >
> >
>
>|||Even if you restart the service , SQL Server will recover each database on
startup. And on this praticular database with redo/undo transactions it may
take ages. If you have the backup along with log backup I'd reccomend to
stop the server, drop the database --restart and restore the database.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...
> Russell;
>> At least in SQL Server 2000 and earlier,
>> shutting down the SQL Server and restarting it could indeed be faster
>> than
>> allowing a long rollback to continue.
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
>> Kevin,
>> Not that I recommend this, but... At least in SQL Server 2000 and
>> earlier,
>> shutting down the SQL Server and restarting it could indeed be faster
>> than
>> allowing a long rollback to continue. It seems that the roll-forward was
>> much faster than the rollback. The few times that I did this (in
>> desperation) I found no evidence of corruption. (But it is the sort of
>> thing to make a DBA nervous, which is why I don't recommend it.)
>> One Upon A Time... I uncovered one possible reason for a very long
>> rollback
>> in SQL Server 2000:
>> I had an extremely busy database and it was very trigger heavy. It
>> appeared
>> that all of the trigger activity, which in 2000 scanned the transaction
>> log,
>> actually hindered the rollback log processing from making much headway.
>> This is inferred from monitoring a very long rollback. (Obviously, I did
>> not restart the SQL Server this time.) The scenario was:
>> 1. Long, out-of-control process had to be terminated. KILL SPID
>> 2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it
>> slowly
>> rewound to about 80% remaining to rollback.
>> 3. At the end of the day, as the load on the database disappeared, it
>> finished rolling back in less than 30 minutes.
>> Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%,
>> then
>> 0.5 hours to rollback 80%.
>> In SQL Server 2005 over the last 18 months I have not had a similar
>> experience, for what it is worth.
>> RLF
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13lbf5bgbd4uf8a@.corp.supernews.com...
>> >I don't think there is anything that can be done to stop a rolling back
>> >transaction from affecting other work. Even pulling the plug on the
>> >server
>> >will simply reset the rollback and it will restart when the server comes
>> >back online. At least that is my understanding.
>> >
>> > --
>> > Kevin G. Boles
>> > TheSQLGuru
>> > Indicium Resources, Inc.
>> >
>> >
>> > "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
>> > news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
>> >> Yes it is in a rollback state. I guess locking up is the wrong choice
>> >> of
>> >> words where. The transction is blocking other work on the DB that is
>> >> more
>> >> important. I don't care about the state that the database would be
>> >> left
>> >> in.
>> >>
>> >> "TheSQLGuru" wrote:
>> >>
>> >> 1) Please define "locking up the server".
>> >>
>> >> 2) I would imagine that you can't alter database state (i.e. take it
>> >> offline) while it is undergoing a transaction rollback. I could be
>> >> wrong on
>> >> this though.
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >>
>> >>
>> >> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>> >> >I had a user kill a process that is now doing a rollback that is
>> >> >locking up
>> >> > the server. Is there any way to force that databse offline? I
>> >> > tried
>> >> > using
>> >> > Alter Database offline in Query analyser and in Enterprise Manger.
>> >> > get
>> >> > 5070
>> >> > error (user connected)
>> >>
>> >>
>> >>
>> >
>> >
>>|||Linchi,
Yes, that is a good question. The answer is, we had some code in those days
that caused this problem more than once. So, I got to practice both paths -
(a) restart the server, and (b) wait it out - at least two or three times.
Then the problem finally got solved.
So, despite everything we hold dear, it really was much (more than an hour)
faster. I do understand the logic of roll-forward and rollback, and the
arguments for why it is a bad idea and why it should not have worked. (But
it did.)
And, No, I have not done it in many years now. I just tell people that they
have to wait and that the code must be revised. :-)
RLF
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3FDF4345-EF14-48AA-935A-0F4F36DA223F@.microsoft.com...
> Russell;
>> At least in SQL Server 2000 and earlier,
>> shutting down the SQL Server and restarting it could indeed be faster
>> than
>> allowing a long rollback to continue.
> How did you know that restarting SQL was faster in finishing a rollback?
> How
> did you know that the rollback would not have taken as long to finish if
> you
> had allowed it to finish without restarting SQL? Just curious.
> Linchi
> "Russell Fields" wrote:
>> Kevin,
>> Not that I recommend this, but... At least in SQL Server 2000 and
>> earlier,
>> shutting down the SQL Server and restarting it could indeed be faster
>> than
>> allowing a long rollback to continue. It seems that the roll-forward was
>> much faster than the rollback. The few times that I did this (in
>> desperation) I found no evidence of corruption. (But it is the sort of
>> thing to make a DBA nervous, which is why I don't recommend it.)
>> One Upon A Time... I uncovered one possible reason for a very long
>> rollback
>> in SQL Server 2000:
>> I had an extremely busy database and it was very trigger heavy. It
>> appeared
>> that all of the trigger activity, which in 2000 scanned the transaction
>> log,
>> actually hindered the rollback log processing from making much headway.
>> This is inferred from monitoring a very long rollback. (Obviously, I did
>> not restart the SQL Server this time.) The scenario was:
>> 1. Long, out-of-control process had to be terminated. KILL SPID
>> 2. Monitoring through the afternoon with KILL SPID WITH STATUSONLY it
>> slowly
>> rewound to about 80% remaining to rollback.
>> 3. At the end of the day, as the load on the database disappeared, it
>> finished rolling back in less than 30 minutes.
>> Assuming you those numbers were trustworthy: 3.5 hours to rollback 20%,
>> then
>> 0.5 hours to rollback 80%.
>> In SQL Server 2005 over the last 18 months I have not had a similar
>> experience, for what it is worth.
>> RLF
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13lbf5bgbd4uf8a@.corp.supernews.com...
>> >I don't think there is anything that can be done to stop a rolling back
>> >transaction from affecting other work. Even pulling the plug on the
>> >server
>> >will simply reset the rollback and it will restart when the server comes
>> >back online. At least that is my understanding.
>> >
>> > --
>> > Kevin G. Boles
>> > TheSQLGuru
>> > Indicium Resources, Inc.
>> >
>> >
>> > "Gary Mazzone" <GaryMazzone@.discussions.microsoft.com> wrote in message
>> > news:C47C0B62-B86D-46E1-9376-08E5F5E7FAD7@.microsoft.com...
>> >> Yes it is in a rollback state. I guess locking up is the wrong choice
>> >> of
>> >> words where. The transction is blocking other work on the DB that is
>> >> more
>> >> important. I don't care about the state that the database would be
>> >> left
>> >> in.
>> >>
>> >> "TheSQLGuru" wrote:
>> >>
>> >> 1) Please define "locking up the server".
>> >>
>> >> 2) I would imagine that you can't alter database state (i.e. take it
>> >> offline) while it is undergoing a transaction rollback. I could be
>> >> wrong on
>> >> this though.
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >>
>> >>
>> >> "Gary Mazzone" <Gary Mazzone@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D68070F1-6F2C-4813-86A7-10D2D8FDEA07@.microsoft.com...
>> >> >I had a user kill a process that is now doing a rollback that is
>> >> >locking up
>> >> > the server. Is there any way to force that databse offline? I
>> >> > tried
>> >> > using
>> >> > Alter Database offline in Query analyser and in Enterprise Manger.
>> >> > get
>> >> > 5070
>> >> > error (user connected)
>> >>
>> >>
>> >>
>> >
>> >
>>sql

force process termination on restore

Sorry if this is a dumb question, but we have several production databases
that get automatically backed up, Rar'd, FTP'd internally and then restored.
Problem is, if someone has a process open against the restored database (IE:
a report is being run or something) the restore fails.
There must be a way of making the restore wait until a process is finsihed
or to force a termination of any process (altho probably not a good practice)
that is running.
Basically, I'm just looking for a better backup/restore process. Any ideas?
Hi,
There are 2 approaches...
1. If you are restoring in non production then you could disconnect all the
users using below command
ALTER database <dbname> set single_user with rollback immediate
go
Restore database
go
ALTER database <dbname> set multi_user
2. Write a loop sort to check the sysprocesses table for this particular
database for any running process . If there
is any running processes
start1:
if (select status from master..sysprocesses where status='runnable' and
db_name(dbid)='pubs')>1
waitfor delay(......) -- set 1 minutedelay
goto start1
else
kill all the users connected
restore the database
I recommend the first method for development and test server databases.
Thanks
Hari
MCDBA
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?
|||Jason,
I had the same problem and wrote the following code to kill the users
connected to the database when I wanted to do to restore. Would this help
you?
***************************************
use master
go
select p.spid, d.name
into #temp_who
from sysprocesses p
join sysdatabases d on
p.dbid = d.dbid
where d.name = 'PUBS'
DECLARE @.objName int, @.sql NVARCHAR(50)
SET @.objName = 0
WHILE @.objName IS NOT NULL
BEGIN
SELECT @.objName = MIN(spid)
FROM #temp_who
WHERE spid > @.objName
IF @.objName IS NOT NULL
BEGIN
--Insert code to do stuff here.
set @.sql = 'kill ' + cast(@.objName as nvarchar)
exec sp_executesql @.sql
END
END
Good luck.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?
sql

force process termination on restore

Sorry if this is a dumb question, but we have several production databases
that get automatically backed up, Rar'd, FTP'd internally and then restored.
Problem is, if someone has a process open against the restored database (IE:
a report is being run or something) the restore fails.
There must be a way of making the restore wait until a process is finsihed
or to force a termination of any process (altho probably not a good practice
)
that is running.
Basically, I'm just looking for a better backup/restore process. Any ideas
?Hi,
There are 2 approaches...
1. If you are restoring in non production then you could disconnect all the
users using below command
ALTER database <dbname> set single_user with rollback immediate
go
Restore database
go
ALTER database <dbname> set multi_user
2. Write a loop sort to check the sysprocesses table for this particular
database for any running process . If there
is any running processes
start1:
if (select status from master..sysprocesses where status='runnable' and
db_name(dbid)='pubs')>1
waitfor delay(......) -- set 1 minutedelay
goto start1
else
kill all the users connected
restore the database
I recommend the first method for development and test server databases.
Thanks
Hari
MCDBA
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?|||Jason,
I had the same problem and wrote the following code to kill the users
connected to the database when I wanted to do to restore. Would this help
you?
***************************************
use master
go
select p.spid, d.name
into #temp_who
from sysprocesses p
join sysdatabases d on
p.dbid = d.dbid
where d.name = 'PUBS'
DECLARE @.objName int, @.sql NVARCHAR(50)
SET @.objName = 0
WHILE @.objName IS NOT NULL
BEGIN
SELECT @.objName = MIN(spid)
FROM #temp_who
WHERE spid > @.objName
IF @.objName IS NOT NULL
BEGIN
--Insert code to do stuff here.
set @.sql = 'kill ' + cast(@.objName as nvarchar)
exec sp_executesql @.sql
END
END
Good luck.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?

For/Next loop to process INSERT INTO statement

I reposted this because I was unable to achieve desired results from
previous recommendations.
Previous Post:
=================== I have the following code which does the following:
1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server
What I need for the code to do is to upload all rows on the worksheet. My
thought is that I might need a For/Next Loop, but I'm not sure where in the
code to place it. Any ideas on how/where would I modify the code to enable
it to (loop through) upload all rows, or iterate on each row having
data (those not deleted by the DeleteBlankRows procedure)?
Here's my code:
======================== Private Sub DeleteBlankRows()
Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next
End Sub
Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub
Thanks in advance.Try changing
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
to
For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i
HTH,
Bernie
MS Excel MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>I reposted this because I was unable to achieve desired results from previous recommendations.
> Previous Post:
> ===================> I have the following code which does the following:
> 1. Deletes all rows having a value of "0" in column C
> 2. Uploads the data in Row 2 to my SQL Server
> What I need for the code to do is to upload all rows on the worksheet. My thought is that I might
> need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on how/where
> would I modify the code to enable it to (loop through) upload all rows, or iterate on each row
> having
> data (those not deleted by the DeleteBlankRows procedure)?
>
> Here's my code:
> ========================> Private Sub DeleteBlankRows()
> Dim lastrow As Long
> Dim r As Long
> lastrow = Range("C" & Rows.Count).End(xlUp).Row
> For r = lastrow To 2 Step -1
> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
> ActiveSheet.Rows(r).Delete
> End If
> Next
> End Sub
> Sub InsertData()
> Dim oConn As Object
> Dim sSQL As String
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xx.x.xx.xx;" & _
> "Initial Catalog=xxx_xxx;" & _
> "User Id=xxxx;" & _
> "Password=xxxx"
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> oConn.Close
> Set oConn = Nothing
> End Sub
> Thanks in advance.
>|||Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
I was getting "Wrapped Around the Axle" trying to deal with Stored
Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
(somewhat), but just needed some tweaking -- thanks again for you help and
quick response. :)
"Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Try changing
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
> &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
> Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> to
> For i = 2 To Range("A65536").End(xlUp).Row
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
> '" & _
> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
> Range("E"&i).Value & "', '" & _
> Range("F"&i).Value & "')"
> oConn.Execute sSQL
> Next i
> HTH,
> Bernie
> MS Excel MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from
>>previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet.
>> My thought is that I might need a For/Next Loop, but I'm not sure where
>> in the code to place it. Any ideas on how/where would I modify the code
>> to enable it to (loop through) upload all rows, or iterate on each row
>> having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
>> &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>|||Doctor Jones,
I have to believe that there is a way to move an entire table into a database without looping. But
I have no experience with SQL, and have never used code like yours - still, I'm glad to hear that my
(probably sub-optimal) code worked out for you.
Bernie
MS Excel MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
news:uhXwiI6IHHA.816@.TK2MSFTNGP06.phx.gbl...
> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
> I was getting "Wrapped Around the Axle" trying to deal with Stored Procedures and Bulk Inserts
> (and whatnot) -- what I originally had worked (somewhat), but just needed some tweaking -- thanks
> again for you help and quick response. :)
>
> "Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
> news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Try changing
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> to
>> For i = 2 To Range("A65536").End(xlUp).Row
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>> Range("E"&i).Value & "', '" & _
>> Range("F"&i).Value & "')"
>> oConn.Execute sSQL
>> Next i
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
>> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet. My thought is that I
>> might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on
>> how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on
>> each row having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>>
>|||Bernie,
There are a plethora of methods to Insert an entire Table, but your For/Next
code does exactly what I needed it to do.
Several other methods (OPENROWSET -- BULK INSERT) weren't working for me --
based on SQL Server Security Settings and/or other issues.
Thanks again for your help! :)
"Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
news:eHaaO0DJHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Doctor Jones,
> I have to believe that there is a way to move an entire table into a
> database without looping. But I have no experience with SQL, and have
> never used code like yours - still, I'm glad to hear that my (probably
> sub-optimal) code worked out for you.
> Bernie
> MS Excel MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
> news:uhXwiI6IHHA.816@.TK2MSFTNGP06.phx.gbl...
>> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
>> I was getting "Wrapped Around the Axle" trying to deal with Stored
>> Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
>> (somewhat), but just needed some tweaking -- thanks again for you help
>> and quick response. :)
>>
>> "Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
>> news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Try changing
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>> '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> to
>> For i = 2 To Range("A65536").End(xlUp).Row
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
>> '" & _
>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>> Range("E"&i).Value & "', '" & _
>> Range("F"&i).Value & "')"
>> oConn.Execute sSQL
>> Next i
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
>> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from
>>previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet.
>> My thought is that I might need a For/Next Loop, but I'm not sure where
>> in the code to place it. Any ideas on how/where would I modify the
>> code to enable it to (loop through) upload all rows, or iterate on each
>> row having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>> '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>>
>>
>

FOR XML vs. ADO

Hi,
Not sure if this is the best newsgroup to ask this so let me know if there
is a better one. I am in the process of investigating converting a componen
t
which uses ADO recordsets to return data from a DB, to making use of the FOR
XML statement. When using recordsets the default date format which is outpu
t
is DD/MM/YYYY hh:mm:ss. However, when using FOR XML the output format is
YYYY-MM-DDThh:mm:ss. This is a bit of pain to be honest since the formats
need to be the same to ensure backwards compatibility. What are the reasons
for the differences? What would be the best way to perform the formatting o
f
the FOR XML output, taking into account that performance is paramount and
that any SQL conversions would have to be propogated to all of the existing
SQL stored procs/views?
Thanks.> When using recordsets the default date format which is output
> is DD/MM/YYYY hh:mm:ss.
That is not correct. When the data leaves SQL Server, it has no format. It i
s the client application
that formats the data. Your assumption of the above format is probably becau
se you had a regional
setting on the machine where you ran the client app that formatted the data
in such way.
For XML, it is a different story, as XML is not binary data (which datetime
is). So a format had to
be chosen, and the most reasonable choice would be the international standar
d for datetime
formatting, ISO 8601, which I believe also is common in other XML implementa
tions. I do not
recommend changing the formatting in your XML document as that would divert
from accepted standards.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:139B3299-FF59-4F4D-9D6E-CD502F4DDD70@.microsoft.com...
> Hi,
> Not sure if this is the best newsgroup to ask this so let me know if there
> is a better one. I am in the process of investigating converting a compon
ent
> which uses ADO recordsets to return data from a DB, to making use of the F
OR
> XML statement. When using recordsets the default date format which is out
put
> is DD/MM/YYYY hh:mm:ss. However, when using FOR XML the output format is
> YYYY-MM-DDThh:mm:ss. This is a bit of pain to be honest since the formats
> need to be the same to ensure backwards compatibility. What are the reaso
ns
> for the differences? What would be the best way to perform the formatting
of
> the FOR XML output, taking into account that performance is paramount and
> that any SQL conversions would have to be propogated to all of the existin
g
> SQL stored procs/views?
> Thanks.|||Regardless of what format the data has when it leaves SQL Server ADO formats
the date into the DD/MM/YYYY hh:mm:ss format by default, on our machines. B
y
using FOR XML I can speed up the component by approx 10% but the format must
remain the same. If this means breaking an accepted standard then so be it,
if a different format was returned it would essentially mean that every
single object within the business logic tier would have to checked and
altered to ensure that the new format is supported.
So taking the above into account do you have any suggestions for the second
question - What would be the best way to perform the formatting of the FOR
XML output, taking into account that performance is paramount and that any
SQL conversions would have to be propogated to all of the existing SQL store
d
procs/views?
"Tibor Karaszi" wrote:

> That is not correct. When the data leaves SQL Server, it has no format. It
is the client application
> that formats the data. Your assumption of the above format is probably bec
ause you had a regional
> setting on the machine where you ran the client app that formatted the dat
a in such way.
> For XML, it is a different story, as XML is not binary data (which datetim
e is). So a format had to
> be chosen, and the most reasonable choice would be the international stand
ard for datetime
> formatting, ISO 8601, which I believe also is common in other XML implemen
tations. I do not
> recommend changing the formatting in your XML document as that would diver
t from accepted standards.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:139B3299-FF59-4F4D-9D6E-CD502F4DDD70@.microsoft.com...
>|||Why would you think that datetime values need to be formatted at all - other
than for presentation purposes?
You could convert datetime values to character data and format it but that
would just break the domain. I.e. you'd have to convert it back in order to
use it programatically.
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Lee (Lee@.discussions.microsoft.com) writes:
> Regardless of what format the data has when it leaves SQL Server ADO
> formats the date into the DD/MM/YYYY hh:mm:ss format by default, on our
> machines. By using FOR XML I can speed up the component by approx 10%
> but the format must remain the same. If this means breaking an accepted
> standard then so be it, if a different format was returned it would
> essentially mean that every single object within the business logic tier
> would have to checked and altered to ensure that the new format is
> supported.
> So taking the above into account do you have any suggestions for the
> second question - What would be the best way to perform the formatting
> of the FOR XML output, taking into account that performance is paramount
> and that any SQL conversions would have to be propogated to all of the
> existing SQL stored procs/views?
You can use the convert function in a query force a certain date format,
look up "CAST and CONVERT" in Books Online. But it is an extremely poor
idea to do, because that means you are shoving a date format down the
throat of your users. The likelihood that I will use your is likely to
be miniscule, but I want to see dates formatted as YYYY-MM-DD.
So for a good implementation, you should recevie the XML document as you
do now, and then convert the date client-side according to the regional
settings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||By no means is this an ideal scenario, I understand that. The framework I a
m
working with is a number of years old and I fear that the business objects
expect a certain date format which means that if this new date format was
used everything would go boom.
The only thing I was trying to acheive was a small performance increase
without having to change the code in too many components which means that I
would have to maintain exactly the same input and output. FOR XML provides
a
nice performance boost but the output is not exactly the same hence risking
the breaking of other components.
"ML" wrote:

> Why would you think that datetime values need to be formatted at all - oth
er
> than for presentation purposes?
> You could convert datetime values to character data and format it but that
> would just break the domain. I.e. you'd have to convert it back in order t
o
> use it programatically.
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||Maybe I didnt explain the situation correctly. This has nothing to do with
the presentation or what the user sees. This is to do with the format of
date that the business objects depend on to perform the required processing.
If these existing components suddenly get a new date format then it would
bring everything tumbline down hence why it is imperative that the output
from the data access component remains the same as to ensure full
compatibility.
I get the impression from the various replies that what I want is not
possible without making alterations to several other pieces of code.
"Erland Sommarskog" wrote:

> Lee (Lee@.discussions.microsoft.com) writes:
> You can use the convert function in a query force a certain date format,
> look up "CAST and CONVERT" in Books Online. But it is an extremely poor
> idea to do, because that means you are shoving a date format down the
> throat of your users. The likelihood that I will use your is likely to
> be miniscule, but I want to see dates formatted as YYYY-MM-DD.
> So for a good implementation, you should recevie the XML document as you
> do now, and then convert the date client-side according to the regional
> settings.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
To the best of my knowledge, there's no setting for this. The only option I
can think of would be to
indeed have CONVERT around the datetime column in every SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:6AD5B648-3CCF-4368-A4C8-741D28F253E8@.microsoft.com...
> Maybe I didnt explain the situation correctly. This has nothing to do wit
h
> the presentation or what the user sees. This is to do with the format of
> date that the business objects depend on to perform the required processin
g.
> If these existing components suddenly get a new date format then it would
> bring everything tumbline down hence why it is imperative that the output
> from the data access component remains the same as to ensure full
> compatibility.
> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
> "Erland Sommarskog" wrote:
>|||Lee (Lee@.discussions.microsoft.com) writes:
> Maybe I didnt explain the situation correctly. This has nothing to do
> with the presentation or what the user sees. This is to do with the
> format of date that the business objects depend on to perform the
> required processing.
A horrible application design, in my opinion. If someone would change
the regional settings to US English, your business objects would choke -
or even worse: misinterpret the dates.

> If these existing components suddenly get a new date format then it would
> bring everything tumbline down hence why it is imperative that the output
> from the data access component remains the same as to ensure full
> compatibility.
> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
Since you were to introduce XML into the pot, it appears that you are
into changing code anyway. You can get back dates in various formats
with XML, if you use the convert() function to format them as strings
in SQL Server.
But if you add XML, you will have to change the code client-side as
well, so I don't really see the problem with converting dates from
the XML documents to the regional settings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

FOR XML streamed?

We are trying to write an XSLT report generator that is pipelined throughout all stages. The first step in the process is to get XML out of our database. We are using 2005 April CTP. The white paper by Michael Rys in the MSDN library seems to indicate that the FOR XML streaming behavior of SQL 2000 is still present in 2005. However, it seems to hint that if you use the TYPE directive to return your XML in an actual native XML field, then the XML is no longer streamed. The entire document may first be created and then sent back all at once as a scaler XML field.

Here are the 2 FOR XML clauses we are expirimenting with:

"For XML Path('PurchaseOrder'), Type, Root('PurchaseOrderReport')" - sends back an XML field instance.

"For XML Path('PurchaseOrder'), Root('PurchaseOrderReport')" - sends back an nvarchar(max) instance.

Can anyone answer if using the TYPE directive eliminates the streaming behavior and therefore defeats our attempt to pipeline?

Thanks!

The TYPE directive will create an XML datatype instance and thus will "Blob" on the server before sending the result back to give you the guarantee of a well-formed XML datatype instance.

So: yes, it eliminates the streaming. You can still pipeline after that on the client side though for the rest of your pipeline.

Best regards
Michael|||Thanks for the reply Michael!

Aside from the guarentee of well formed XML, are there other advantages/disadvantages to using the TYPE directive that we should concider when trying to decide between the typed or non-typed route?|||The TYPE directive constructs an XML data type instance, so you can use it for

(a) nested FOR XML
(b) assignment to XML columns, variables and parameters
(c) querying the XML data type result using XQuery

at the server. If your intent is to get the XML to the client in a streamable manner, and (a)-(c) don't apply, then you are better off without the TYPE directive. This also saves you the cost of data conversion to XML data type.

Hope this helps.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

for loop container to process all Excel files

I'm having a problem getting the for loop container to process all excel files in a folder. I set the collection folder to where my .xls files are, and i set a variable in the for loop container to the FileName. I then changed my source connection and added expressions for

ConnectionString:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=" + @.[User::ExtProperties]
(the ExtProperties was necessary to get the double quotes in. )

ServerName:
@.[User::FileName]

It cannot connect.
I used a similar process to loop thru Access .mdb files, but did not set the ServerName and did not need the ExtProperites because the Access connection did not need them, and I got that to work.

Any help would be very much appreciated.Did you get an error message with any useful information?

You should not need to specify "Server." And I'm not clear on the need to use a variables for Extended Properties, since they're probably not changing from one Excel file to another, and no extra quotes are necessary if your string is simply "Extended Properties=Excel 8.0," assuming the default value for HDR and not using IMEX.

You could use a short Script task within your loop to view the connection string resulting from the expression on each iteration.

-Doug|||I used the following expression for Connection String, and was able to successfully loop through all excel files. As Douglas correctly pointed out, you don't need ServerName. Use \ to get your quotes in, if you have to.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::Variable] + ";Extended Properties=\"Excel 8.0;HDR=NO\";"
thanks
Ranjeeta|||

I removed the ServerName expression and I changed my connectstring code to be like Ranjeeta's...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
It evaluates correctly, but When I run, I still get the same errors:

Error at LoadStagingTables [Liabilities[1641]]: The AcquireConnection method call to the connection manager "PlanFinancialsInput" failed with error code 0xC0202009.

Error at LoadStagingTables [DTS.Pipeline]: component "Liabilities" (1641) failed validation and returned error code 0xC020801C.

Error at LoadStagingTables [DTS.Pipeline]: One or more component failed validation.

Error at LoadStagingTables: There were errors during task validation.

Error at ProcessFinancials [Connection manager "PlanFinancialsInput"]: An OLE DB error has occurred. Error code 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)
If I change the connection back to just the first xls file, it works fine, but when I change back to using the connectstring, I get these errors.

Thanks very much for your help.

|||Success!!!!
I got it to work. I created a new package and tested a small .xls file just to see if I could get one to work and it worked. I didn't do anything different. But I still couldn't get my original package to work. So I compared properties at various levels and I found one different. I don't know how it got changed, but it was different. The DelayValidation on my original Data Flow task was set to False, but it was True for my test task. I changed it to True, then deleted my connectstring property and change the source to the excel file, then added the connectstring back again, and voila! it works!|||Were you using the September CTP? I'm running into the same issue and can't seem to get around it as you have.|||

No, the June CTP.
Version 9.00.1187.00

Make sure you have the delay validation property set to true on every data flow task or any task that would use your connection.

|||That did it! We had it set in each task in the data flow, but not the data flow itself. Thanks!|||

just looked at the forum.. you could try the following:

1. Foreach loop to process .xls files in a dir - set variable xlsDoc to filename

2. script to change an Excel Connection Manager to the loaded filename

Try

Dim xlConn As ConnectionManager = Dts.Connections("Excel Connection Manager")

xlConn.Properties("ExcelFilePath").SetValue(xlConn, Dts.Variables("xlsDoc").Value.ToString)

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

3. Foreach loop to process worksheets (poss load from a list which i'm doing - if you do, then trim the worksheet name from the ADO variable, to remove string padding, or it won't load properly)

4. Call your dataflow which uses the single 'Excel Connection Manager'...

The problem i'm getting now is when a worksheet doesn't exist, trying to trap/cancel the error, i've added handlers to the dataflow task, to the excel connection inside it etc, no joy...

|||

Hi
I am using SqlServer 2005 September CTP version.
I have created a package which uses 'Data Flow task' to read a Excel file,
a 'Script Component' which does some lookups and 'Sql Server Destination' to insert data into Sql server 2005 database.
The package runs sucessfully.Then I deployed the package using dtsinstall.exe,
it validate & deployed successfully,but when I tried to run the
package it gives me following error :

"Error : The AcquireConnection method call to
hte connection manager "Excel Connection
Manager" failed with error code 0xC0202009"

"Error : component "Excel Source" (133) failed
validation and return error code 0xC020801C.

I also tried changing DelayValidation to true
but it dosent work after deployment.

please suggest.
Thanks
Abhishek

|||

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks

Check this out; I think it has what you need. Otherwise, open a new thread and provide the specific errors you are getting

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278469&SiteID=1

|||

hi Ranjeeta,

i am having the same issues for looping the excell files. i has used all the settings for the same which you and all others has said but not successful. i am getting some connection manager failed and invalid argument error. Now i dont know what to do for it. plz help.

thanks....

for loop container to process all Excel files

I'm having a problem getting the for loop container to process all excel files in a folder. I set the collection folder to where my .xls files are, and i set a variable in the for loop container to the FileName. I then changed my source connection and added expressions for

ConnectionString:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=" + @.[User::ExtProperties]
(the ExtProperties was necessary to get the double quotes in. )

ServerName:
@.[User::FileName]

It cannot connect.
I used a similar process to loop thru Access .mdb files, but did not set the ServerName and did not need the ExtProperites because the Access connection did not need them, and I got that to work.

Any help would be very much appreciated.Did you get an error message with any useful information?

You should not need to specify "Server." And I'm not clear on the need to use a variables for Extended Properties, since they're probably not changing from one Excel file to another, and no extra quotes are necessary if your string is simply "Extended Properties=Excel 8.0," assuming the default value for HDR and not using IMEX.

You could use a short Script task within your loop to view the connection string resulting from the expression on each iteration.

-Doug
|||I used the following expression for Connection String, and was able to successfully loop through all excel files. As Douglas correctly pointed out, you don't need ServerName. Use \ to get your quotes in, if you have to.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::Variable] + ";Extended Properties=\"Excel 8.0;HDR=NO\";"
thanks
Ranjeeta|||

I removed the ServerName expression and I changed my connectstring code to be like Ranjeeta's...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
It evaluates correctly, but When I run, I still get the same errors:

Error at LoadStagingTables [Liabilities[1641]]: The AcquireConnection method call to the connection manager "PlanFinancialsInput" failed with error code 0xC0202009.

Error at LoadStagingTables [DTS.Pipeline]: component "Liabilities" (1641) failed validation and returned error code 0xC020801C.

Error at LoadStagingTables [DTS.Pipeline]: One or more component failed validation.

Error at LoadStagingTables: There were errors during task validation.

Error at ProcessFinancials [Connection manager "PlanFinancialsInput"]: An OLE DB error has occurred. Error code 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)
If I change the connection back to just the first xls file, it works fine, but when I change back to using the connectstring, I get these errors.

Thanks very much for your help.

|||Success!!!!
I got it to work. I created a new package and tested a small .xls file just to see if I could get one to work and it worked. I didn't do anything different. But I still couldn't get my original package to work. So I compared properties at various levels and I found one different. I don't know how it got changed, but it was different. The DelayValidation on my original Data Flow task was set to False, but it was True for my test task. I changed it to True, then deleted my connectstring property and change the source to the excel file, then added the connectstring back again, and voila! it works!
|||Were you using the September CTP? I'm running into the same issue and can't seem to get around it as you have.|||

No, the June CTP.
Version 9.00.1187.00

Make sure you have the delay validation property set to true on every data flow task or any task that would use your connection.

|||That did it! We had it set in each task in the data flow, but not the data flow itself. Thanks!|||

just looked at the forum.. you could try the following:

1. Foreach loop to process .xls files in a dir - set variable xlsDoc to filename

2. script to change an Excel Connection Manager to the loaded filename

Try

Dim xlConn As ConnectionManager = Dts.Connections("Excel Connection Manager")

xlConn.Properties("ExcelFilePath").SetValue(xlConn, Dts.Variables("xlsDoc").Value.ToString)

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

3. Foreach loop to process worksheets (poss load from a list which i'm doing - if you do, then trim the worksheet name from the ADO variable, to remove string padding, or it won't load properly)

4. Call your dataflow which uses the single 'Excel Connection Manager'...

The problem i'm getting now is when a worksheet doesn't exist, trying to trap/cancel the error, i've added handlers to the dataflow task, to the excel connection inside it etc, no joy...

|||

Hi
I am using SqlServer 2005 September CTP version.
I have created a package which uses 'Data Flow task' to read a Excel file,
a 'Script Component' which does some lookups and 'Sql Server Destination' to insert data into Sql server 2005 database.
The package runs sucessfully.Then I deployed the package using dtsinstall.exe,
it validate & deployed successfully,but when I tried to run the
package it gives me following error :

"Error : The AcquireConnection method call to
hte connection manager "Excel Connection
Manager" failed with error code 0xC0202009"

"Error : component "Excel Source" (133) failed
validation and return error code 0xC020801C.

I also tried changing DelayValidation to true
but it dosent work after deployment.

please suggest.
Thanks
Abhishek

|||

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks

Check this out; I think it has what you need. Otherwise, open a new thread and provide the specific errors you are getting

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278469&SiteID=1

|||

hi Ranjeeta,

i am having the same issues for looping the excell files. i has used all the settings for the same which you and all others has said but not successful. i am getting some connection manager failed and invalid argument error. Now i dont know what to do for it. plz help.

thanks....