Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
RozRoz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>|||run a query with a nolock hint to see where you are in
the process.
Mark Baekdal
www.dbghost.com
>--Original Message--
>Is there a tool, in SQL 2K or third party, that can look
>at an UPDATE, or even DELETE, I'm about to run,
>and "estimate" how long it'll run. I started a massive
>UPDATE on 37 million rows on one column. I stared the
>UPDATE 14 hours ago, and it's still running. I can't
run
>a query to determine how many records have already been
>updated, because the update has a lock on the table.
>I was just wondering was there something I could've done
>beforehand, that could've told me how long this UPDATE
>would take. I've played with the Execution Plan
feature,
>and it does give some useful information, but I'm
>specifically looking for costs in terms of "time".
>Thanks
>Roz
>.
>|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
>> Is there a tool, in SQL 2K or third party, that can look
>> at an UPDATE, or even DELETE, I'm about to run,
>> and "estimate" how long it'll run. I started a massive
>> UPDATE on 37 million rows on one column. I stared the
>> UPDATE 14 hours ago, and it's still running. I can't
run
>> a query to determine how many records have already been
>> updated, because the update has a lock on the table.
>> I was just wondering was there something I could've done
>> beforehand, that could've told me how long this UPDATE
>> would take. I've played with the Execution Plan
feature,
>> and it does give some useful information, but I'm
>> specifically looking for costs in terms of "time".
>> Thanks
>> Roz
>
>.
>|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
--
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> >--Original Message--
> >Roz
> >I would divide a long transaction into small. Have you
> checked transaction
> >log file? Did it grow?
> >SET ROWCOUNT 1000
> >WHILE 1 = 1
> >BEGIN
> >
> > UPDATE command
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> >
> > CHECKPOINT
> > END
> >END
> >
> >SET ROWCOUNT 0
> >
> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> >> Is there a tool, in SQL 2K or third party, that can look
> >> at an UPDATE, or even DELETE, I'm about to run,
> >> and "estimate" how long it'll run. I started a massive
> >> UPDATE on 37 million rows on one column. I stared the
> >> UPDATE 14 hours ago, and it's still running. I can't
> run
> >> a query to determine how many records have already been
> >> updated, because the update has a lock on the table.
> >>
> >> I was just wondering was there something I could've done
> >> beforehand, that could've told me how long this UPDATE
> >> would take. I've played with the Execution Plan
> feature,
> >> and it does give some useful information, but I'm
> >> specifically looking for costs in terms of "time".
> >>
> >> Thanks
> >> Roz
> >>
> >
> >
> >.
> >|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
>> Uri,
>> Yep, that's exactly what I did. I broke the Update into
>> 5000 records at a time. The Tlog is small, as it should
>> be since I'm Checkpointing quite frequently. But the
>> Update is still running. I guess it just takes this
>> long...
>> Roz
>>
>> >--Original Message--
>> >Roz
>> >I would divide a long transaction into small. Have you
>> checked transaction
>> >log file? Did it grow?
>> >SET ROWCOUNT 1000
>> >WHILE 1 = 1
>> >BEGIN
>> >
>> > UPDATE command
>> >
>> > IF @.@.ROWCOUNT = 0
>> > BEGIN
>> > BREAK
>> > END
>> > ELSE
>> > BEGIN
>> >
>> > CHECKPOINT
>> > END
>> >END
>> >
>> >SET ROWCOUNT 0
>> >
>> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
>> >> Is there a tool, in SQL 2K or third party, that can
look
>> >> at an UPDATE, or even DELETE, I'm about to run,
>> >> and "estimate" how long it'll run. I started a
massive
>> >> UPDATE on 37 million rows on one column. I stared
the
>> >> UPDATE 14 hours ago, and it's still running. I can't
>> run
>> >> a query to determine how many records have already
been
>> >> updated, because the update has a lock on the table.
>> >>
>> >> I was just wondering was there something I could've
done
>> >> beforehand, that could've told me how long this
UPDATE
>> >> would take. I've played with the Execution Plan
>> feature,
>> >> and it does give some useful information, but I'm
>> >> specifically looking for costs in terms of "time".
>> >>
>> >> Thanks
>> >> Roz
>> >>
>> >
>> >
>> >.
>> >
>.
>|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
--
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> >--Original Message--
> >That will give you 7400 separate transactions. It could
> still take a while,
> >but you will not cause your transaction log to grow
> uncontrollably. Using
> >smaller chunks gives you more options. You could insert
> into (or update) a
> >"logging" table after each pass through the while loop.
> That would give you
> >the ability to know how many you have done and how many
> more rows are left
> >to process.
> >
> >--
> >Keith
> >
> >
> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
> >> Uri,
> >>
> >> Yep, that's exactly what I did. I broke the Update into
> >> 5000 records at a time. The Tlog is small, as it should
> >> be since I'm Checkpointing quite frequently. But the
> >> Update is still running. I guess it just takes this
> >> long...
> >>
> >> Roz
> >>
> >>
> >> >--Original Message--
> >> >Roz
> >> >I would divide a long transaction into small. Have you
> >> checked transaction
> >> >log file? Did it grow?
> >> >SET ROWCOUNT 1000
> >> >WHILE 1 = 1
> >> >BEGIN
> >> >
> >> > UPDATE command
> >> >
> >> > IF @.@.ROWCOUNT = 0
> >> > BEGIN
> >> > BREAK
> >> > END
> >> > ELSE
> >> > BEGIN
> >> >
> >> > CHECKPOINT
> >> > END
> >> >END
> >> >
> >> >SET ROWCOUNT 0
> >> >
> >> >"Roz" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> >> >> Is there a tool, in SQL 2K or third party, that can
> look
> >> >> at an UPDATE, or even DELETE, I'm about to run,
> >> >> and "estimate" how long it'll run. I started a
> massive
> >> >> UPDATE on 37 million rows on one column. I stared
> the
> >> >> UPDATE 14 hours ago, and it's still running. I can't
> >> run
> >> >> a query to determine how many records have already
> been
> >> >> updated, because the update has a lock on the table.
> >> >>
> >> >> I was just wondering was there something I could've
> done
> >> >> beforehand, that could've told me how long this
> UPDATE
> >> >> would take. I've played with the Execution Plan
> >> feature,
> >> >> and it does give some useful information, but I'm
> >> >> specifically looking for costs in terms of "time".
> >> >>
> >> >> Thanks
> >> >> Roz
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >.
> >sql
Showing posts with label tool. Show all posts
Showing posts with label tool. Show all posts
Tuesday, March 20, 2012
A tool...how long will this take...
Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
Roz
Roz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>
|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
run[vbcol=seagreen]
feature,
>
>.
>
|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> checked transaction
> message
> run
> feature,
|||Roz
A good idea would be to display a running total of how many rows you have updated everytime you hit your 5000 transaction count. That way at least you would have an idea how long it will take that way. (Too late now I know)
Regards
John
|||Roz
A good idea would be to display a running total of how many rows you have updated everytime you hit your 5000 transaction count. That way at least you would have an idea how long it will take that way. (Too late now I know)
Regards
John
|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE
>.
>
|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE
>.
>
|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
Roz
Roz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>
|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2225101c45d0e$345d1fa0$a301280a@.phx.gbl...
run[vbcol=seagreen]
feature,
>
>.
>
|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> checked transaction
> message
> run
> feature,
|||Roz
A good idea would be to display a running total of how many rows you have updated everytime you hit your 5000 transaction count. That way at least you would have an idea how long it will take that way. (Too late now I know)
Regards
John
|||Roz
A good idea would be to display a running total of how many rows you have updated everytime you hit your 5000 transaction count. That way at least you would have an idea how long it will take that way. (Too late now I know)
Regards
John
|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE
>.
>
|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2243501c45d11$05c5e8e0$a101280a@.phx.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE
>.
>
|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
A tool...how long will this take...
Is there a tool, in SQL 2K or third party, that can look
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
RozRoz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx
.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2225101c45d0e$345d1fa0$a301280a@.phx
.gbl...
run[vbcol=seagreen]
feature,[vbcol=seagreen]
>
>.
>|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> checked transaction
> message
> run
> feature,|||Roz
A good idea would be to display a running total of how many rows you have up
dated everytime you hit your 5000 transaction count. That way at least you w
ould have an idea how long it will take that way. (Too late now I know)
Regards
John|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2243501c45d11$05c5e8e0$a101280a@.phx
.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE[vbcol=seagreen]
>.
>|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
>
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
at an UPDATE, or even DELETE, I'm about to run,
and "estimate" how long it'll run. I started a massive
UPDATE on 37 million rows on one column. I stared the
UPDATE 14 hours ago, and it's still running. I can't run
a query to determine how many records have already been
updated, because the update has a lock on the table.
I was just wondering was there something I could've done
beforehand, that could've told me how long this UPDATE
would take. I've played with the Execution Plan feature,
and it does give some useful information, but I'm
specifically looking for costs in terms of "time".
Thanks
RozRoz
I would divide a long transaction into small. Have you checked transaction
log file? Did it grow?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE command
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2225101c45d0e$345d1fa0$a301280a@.phx
.gbl...
> Is there a tool, in SQL 2K or third party, that can look
> at an UPDATE, or even DELETE, I'm about to run,
> and "estimate" how long it'll run. I started a massive
> UPDATE on 37 million rows on one column. I stared the
> UPDATE 14 hours ago, and it's still running. I can't run
> a query to determine how many records have already been
> updated, because the update has a lock on the table.
> I was just wondering was there something I could've done
> beforehand, that could've told me how long this UPDATE
> would take. I've played with the Execution Plan feature,
> and it does give some useful information, but I'm
> specifically looking for costs in terms of "time".
> Thanks
> Roz
>|||Uri,
Yep, that's exactly what I did. I broke the Update into
5000 records at a time. The Tlog is small, as it should
be since I'm Checkpointing quite frequently. But the
Update is still running. I guess it just takes this
long...
Roz
>--Original Message--
>Roz
>I would divide a long transaction into small. Have you
checked transaction
>log file? Did it grow?
>SET ROWCOUNT 1000
>WHILE 1 = 1
>BEGIN
> UPDATE command
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
>END
>SET ROWCOUNT 0
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2225101c45d0e$345d1fa0$a301280a@.phx
.gbl...
run[vbcol=seagreen]
feature,[vbcol=seagreen]
>
>.
>|||That will give you 7400 separate transactions. It could still take a while,
but you will not cause your transaction log to grow uncontrollably. Using
smaller chunks gives you more options. You could insert into (or update) a
"logging" table after each pass through the while loop. That would give you
the ability to know how many you have done and how many more rows are left
to process.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2243501c45d11$05c5e8e0$a101280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> Yep, that's exactly what I did. I broke the Update into
> 5000 records at a time. The Tlog is small, as it should
> be since I'm Checkpointing quite frequently. But the
> Update is still running. I guess it just takes this
> long...
> Roz
>
> checked transaction
> message
> run
> feature,|||Roz
A good idea would be to display a running total of how many rows you have up
dated everytime you hit your 5000 transaction count. That way at least you w
ould have an idea how long it will take that way. (Too late now I know)
Regards
John|||Beautiful. Very excellent ideas to try. I'll keep these
in mind next time I need to do such a massive update.
Thanks very, very much to all.
Roz
>--Original Message--
>That will give you 7400 separate transactions. It could
still take a while,
>but you will not cause your transaction log to grow
uncontrollably. Using
>smaller chunks gives you more options. You could insert
into (or update) a
>"logging" table after each pass through the while loop.
That would give you
>the ability to know how many you have done and how many
more rows are left
>to process.
>--
>Keith
>
>"Roz" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2243501c45d11$05c5e8e0$a101280a@.phx
.gbl...
look[vbcol=seagreen]
massive[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
done[vbcol=seagreen]
UPDATE[vbcol=seagreen]
>.
>|||By the way, limiting the rowcount to 5000 updates seems a little light. I
would probably try with 50,000 or even 100,000. Heck, you could set it to
10 if you wanted to...it is probably a balancing act of time vs resource
usage.
One more idea for inside the WHILE loop: you could perform a BACKUP LOG
<database> WITH NO_LOG within the while loop to clear the transaction log.
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:226eb01c45d23$f44297e0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Beautiful. Very excellent ideas to try. I'll keep these
> in mind next time I need to do such a massive update.
> Thanks very, very much to all.
> Roz
>
> still take a while,
> uncontrollably. Using
> into (or update) a
> That would give you
> more rows are left
> message
> look
> massive
> the
> been
> done
> UPDATE
A tool similar to SQL Impact from Quest?
Hi,
I am looking for a tool that is similar to SQL Impact (Quest). Quest has discontinued the tool.
This tool should be able to detect all database object dependencies for SQL Server, Sybase and Oracle. The objects should include tables, views, stored procedures, indexes and other objects. This should also detect DB object dependencies in front end applications as well.
Any suggestions are greatly appreciated...
Thanks!I am not familiar with SQL Impact, however a shareware app I wrote may help you out: sqlspec will tell you this information, but it works only against SQL Server 2000 and 2005.
The link to it is in my sig below if you want to check it out.
I am looking for a tool that is similar to SQL Impact (Quest). Quest has discontinued the tool.
This tool should be able to detect all database object dependencies for SQL Server, Sybase and Oracle. The objects should include tables, views, stored procedures, indexes and other objects. This should also detect DB object dependencies in front end applications as well.
Any suggestions are greatly appreciated...
Thanks!I am not familiar with SQL Impact, however a shareware app I wrote may help you out: sqlspec will tell you this information, but it works only against SQL Server 2000 and 2005.
The link to it is in my sig below if you want to check it out.
A tool for aggregation design XMLA file viewing/editing ?
Is the best way to specify the most useful aggregations to actually edit the XMLA for measure group and hand pick the aggregations I need?
In that case has anyone seen a basic tool for viewing the attributes in each aggregation and perh
In taht case has aaps even deleting them/editing the XMLA file a little bit? The reason I am looking for a tool is to make sense out of 2000 aggregations created by the wizard.
A new sample app for viewing and editing aggregations should become available as part of SP2...
Thanks,
Akshai
|||what is the name of that sample? I have SP2 final, but only samples under Microsoft SQL Server\90\Sample\Analysis Services are:\Administrator\ActivityViewer
\DataMining\Data Mining Web Controls
\DataMining\SQL Server Data Mining Plug-In Algorithms
\Programmability\AMO
\Tutorials\Lesson4
\Tutorials\Lesson7
I checked the Programmability\AMO sample, but all it does is provide drilldown into properties. I can drill into aggregations but it's very laborious to use to find see which attributes each aggregation aggregates, because you would need to click 2 times on each dimention in each aggregation to see which attributes it affects. I did not see an ability to create a new aggregation or change an aggregation.
Did I miss something?
Where is the sample that Edward and you mention ?
|||
It's called AggregationManager and is under Administrator/AggregationManager.
I don't think the samples are included in the main SP2 download. I think you have to download them separately. The SQL Server sample are now all available from codeplex. The download page for the SSAS samples is here http://www.codeplex.com/MSFTASProdSamples/Release/ProjectReleases.aspx?ReleaseId=2461
The AggManager sample only works with a deployed database. I am looking to add support for this tool directly in BIDS through a project I am working on at www.codeplex.com/BIDSHelper. I hope to get an initial beta release out in the next week or two if you are interested.
A tool for aggregation design XMLA file viewing/editing ?
Is the best way to specify the most useful aggregations to actually edit the XMLA for measure group and hand pick the aggregations I need?
In that case has anyone seen a basic tool for viewing the attributes in each aggregation and perh
In taht case has aaps even deleting them/editing the XMLA file a little bit? The reason I am looking for a tool is to make sense out of 2000 aggregations created by the wizard.
A new sample app for viewing and editing aggregations should become available as part of SP2...
Thanks,
Akshai
|||what is the name of that sample? I have SP2 final, but only samples under Microsoft SQL Server\90\Sample\Analysis Services are:
\Administrator\ActivityViewer
\DataMining\Data Mining Web Controls
\DataMining\SQL Server Data Mining Plug-In Algorithms
\Programmability\AMO
\Tutorials\Lesson4
\Tutorials\Lesson7
I checked the Programmability\AMO sample, but all it does is provide drilldown into properties. I can drill into aggregations but it's very laborious to use to find see which attributes each aggregation aggregates, because you would need to click 2 times on each dimention in each aggregation to see which attributes it affects. I did not see an ability to create a new aggregation or change an aggregation.
Did I miss something?
Where is the sample that Edward and you mention ?|||
It's called AggregationManager and is under Administrator/AggregationManager.
I don't think the samples are included in the main SP2 download. I think you have to download them separately. The SQL Server sample are now all available from codeplex. The download page for the SSAS samples is here http://www.codeplex.com/MSFTASProdSamples/Release/ProjectReleases.aspx?ReleaseId=2461
The AggManager sample only works with a deployed database. I am looking to add support for this tool directly in BIDS through a project I am working on at www.codeplex.com/BIDSHelper. I hope to get an initial beta release out in the next week or two if you are interested.
Thursday, March 8, 2012
A simple INSERT Problem
Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
Rick
Try stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick
|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
Rick
|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
Rick
Try stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick
|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
Rick
|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
A simple INSERT Problem
Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments
.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
RickTry stepping through the debugger and check to see if the connection is open
.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT stamen
ts.
> Having the same connection and user id. When I execute the stored procedur
e
> in the Query Analyzer console I have no problem, but when I execute it in
a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??
)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" f
rom your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from t
he INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments
.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
RickTry stepping through the debugger and check to see if the connection is open
.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT stamen
ts.
> Having the same connection and user id. When I execute the stored procedur
e
> in the Query Analyzer console I have no problem, but when I execute it in
a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??
)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" f
rom your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from t
he INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
A simple INSERT Problem
Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(¡?)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
--
RickTry stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(¡?)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(¡?)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
--
RickTry stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(¡?)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Sunday, February 19, 2012
A problem with bcp tool
I need to transfer some data from a file .txt to a SQL Server.
I use bcp tool. The problem is about a column named 'FILE', that I've
declared as:
[FILE] VARCHAR(150) NOT NULL
I use bcp tool. The problem is about a column named 'FILE', that I've
declared as:
[FILE] VARCHAR(150) NOT NULL
The command I launch is the following:
bcp.exe EC_CUNEO01.PROJECT.ANTRACCIATO in data_file.txt -f
format_file.fmt -e error_file.err -m 10 -t\t -r\n -k -b1000 -U user -P
password -S server
In the format_file I've declared the format of the columns, referring to the
'FILE' one as:
3 SQLCHAR 0 150 "\t" 3 [FILE]
But when I try to import the data, the system returns the error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'FILE'.
Does anybody know why?
Thank you."Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckok38$1hs$1@.atlantis.cu.mi.it...
>I need to transfer some data from a file .txt to a SQL Server.
> I use bcp tool. The problem is about a column named 'FILE', that I've
> declared as:
> [FILE] VARCHAR(150) NOT NULL
> The command I launch is the following:
> bcp.exe EC_CUNEO01.PROJECT.ANTRACCIATO in data_file.txt -f
> format_file.fmt -e error_file.err -m 10 -t\t -r\n -k -b1000 -U user -P
> password -S server
> In the format_file I've declared the format of the columns, referring to
> the
> 'FILE' one as:
> 3 SQLCHAR 0 150 "\t" 3 [FILE]
> But when I try to import the data, the system returns the error:
> SQLState = 37000, NativeError = 156
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
> near
> the keyword 'FILE'.
> Does anybody know why?
> Thank you.
I would guess it's because FILE is a reserved keyword in MSSQL. According to
BOL, the correct column name is not required in a format file anyway, so you
could try replacing it with a non-reserved word to see what happens.
Alternatively, try using DTS, which is often a bit 'smarter' about these
things, but you should probably change your column name to a non-reserved
word anyway. I appreciate that you might have no control over that, however.
Simon|||> I would guess it's because FILE is a reserved keyword in MSSQL. According
to
> BOL, the correct column name is not required in a format file anyway, so
you
> could try replacing it with a non-reserved word to see what happens.
> Alternatively, try using DTS, which is often a bit 'smarter' about these
> things, but you should probably change your column name to a non-reserved
> word anyway. I appreciate that you might have no control over that,
however.
> Simon
I would maintain the name of the column because my application already runs
on a DB2 preexisting Database, which contains columns with this name; I
would make less change as possible...
Thank you, I will try DTS!
Bye
Fede
Saturday, February 11, 2012
A good TSQL editor/debugger
Greetings.
Which is the best tool to use for editing SQL , and debugging SPs?
Must have features are auto-complete of column members and SP parameters and
good query plan/server trace outputs.
Thanks.
Elias.
Hi,
Query Analyzer is the best tool to do all. You could do all the belwo:-
1. Execution plans
2. Set statistics IO ON, SET STATISTICS TIME ON
3. Index tuning
4. Debugging
The list goes on and on...
Thanks
Hari
SQL Server MVP.
"elias448" <elias448@.discussions.microsoft.com> wrote in message
news:E3D4D66B-EDD4-4844-B0F0-37EDD8AEC284@.microsoft.com...
> Greetings.
> Which is the best tool to use for editing SQL , and debugging SPs?
> Must have features are auto-complete of column members and SP parameters
> and
> good query plan/server trace outputs.
> Thanks.
> Elias.
|||Thanks for the reply. I was looking for a tool that has more advanced
editing options, for example, auto-completion of table column names (in
the same way Intellisense works in VB).
|||One possibility is SQL-Programmer from BMC which has
intellisense and a debugger:
http://www.bmc.com/products/products..._0_105,00.html
-Sue
On 7 Jun 2005 05:56:11 -0700, "Liakos"
<elias448@.hotmail.com> wrote:
>Thanks for the reply. I was looking for a tool that has more advanced
>editing options, for example, auto-completion of table column names (in
>the same way Intellisense works in VB).
|||FWIW, yesterday I downloaded and started using ApexSQL Edit.
Can't say I've tested it thoroughly yet, but so far I'm pretty
impressed. It does many of the things you mentioned. Download the
free trial and give it a whirl.
http://www.apexsql.com
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On 7 Jun 2005 05:56:11 -0700, "Liakos" <elias448@.hotmail.com> wrote:
>Thanks for the reply. I was looking for a tool that has more advanced
>editing options, for example, auto-completion of table column names (in
>the same way Intellisense works in VB).
|||You should also check out SQL IDE from Imceda Software (http://www.imceda.com). If you're an Oracle person, TOAD reigns supreme. (TOAD is also in beta for SQL Server at http://www.toadsoft.com/toadssbeta.html.)
Hope this helps,
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Thanks for the reply. I was looking for a tool that has more advanced
> editing options, for example, auto-completion of table column names
> (in the same way Intellisense works in VB).
>
|||Or give http://www.agileinfollc.com DataStudio a try for table editing.
John King
http://www.agileinfollc.com
"Kevin Kline [MVP]" <kevin.kline[NOSPAM]@.quest.com> wrote in message
news:uQqMjlEfFHA.3304@.TK2MSFTNGP12.phx.gbl...
> You should also check out SQL IDE from Imceda Software
> (http://www.imceda.com). If you're an Oracle person, TOAD reigns supreme.
> (TOAD is also in beta for SQL Server at
> http://www.toadsoft.com/toadssbeta.html.)
> Hope this helps,
> -Kev
> ~~~
> -Kevin Kline
> Quest Software (www.quest.com)
> SQL Server MVP
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
Which is the best tool to use for editing SQL , and debugging SPs?
Must have features are auto-complete of column members and SP parameters and
good query plan/server trace outputs.
Thanks.
Elias.
Hi,
Query Analyzer is the best tool to do all. You could do all the belwo:-
1. Execution plans
2. Set statistics IO ON, SET STATISTICS TIME ON
3. Index tuning
4. Debugging
The list goes on and on...
Thanks
Hari
SQL Server MVP.
"elias448" <elias448@.discussions.microsoft.com> wrote in message
news:E3D4D66B-EDD4-4844-B0F0-37EDD8AEC284@.microsoft.com...
> Greetings.
> Which is the best tool to use for editing SQL , and debugging SPs?
> Must have features are auto-complete of column members and SP parameters
> and
> good query plan/server trace outputs.
> Thanks.
> Elias.
|||Thanks for the reply. I was looking for a tool that has more advanced
editing options, for example, auto-completion of table column names (in
the same way Intellisense works in VB).
|||One possibility is SQL-Programmer from BMC which has
intellisense and a debugger:
http://www.bmc.com/products/products..._0_105,00.html
-Sue
On 7 Jun 2005 05:56:11 -0700, "Liakos"
<elias448@.hotmail.com> wrote:
>Thanks for the reply. I was looking for a tool that has more advanced
>editing options, for example, auto-completion of table column names (in
>the same way Intellisense works in VB).
|||FWIW, yesterday I downloaded and started using ApexSQL Edit.
Can't say I've tested it thoroughly yet, but so far I'm pretty
impressed. It does many of the things you mentioned. Download the
free trial and give it a whirl.
http://www.apexsql.com
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On 7 Jun 2005 05:56:11 -0700, "Liakos" <elias448@.hotmail.com> wrote:
>Thanks for the reply. I was looking for a tool that has more advanced
>editing options, for example, auto-completion of table column names (in
>the same way Intellisense works in VB).
|||You should also check out SQL IDE from Imceda Software (http://www.imceda.com). If you're an Oracle person, TOAD reigns supreme. (TOAD is also in beta for SQL Server at http://www.toadsoft.com/toadssbeta.html.)
Hope this helps,
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Thanks for the reply. I was looking for a tool that has more advanced
> editing options, for example, auto-completion of table column names
> (in the same way Intellisense works in VB).
>
|||Or give http://www.agileinfollc.com DataStudio a try for table editing.
John King
http://www.agileinfollc.com
"Kevin Kline [MVP]" <kevin.kline[NOSPAM]@.quest.com> wrote in message
news:uQqMjlEfFHA.3304@.TK2MSFTNGP12.phx.gbl...
> You should also check out SQL IDE from Imceda Software
> (http://www.imceda.com). If you're an Oracle person, TOAD reigns supreme.
> (TOAD is also in beta for SQL Server at
> http://www.toadsoft.com/toadssbeta.html.)
> Hope this helps,
> -Kev
> ~~~
> -Kevin Kline
> Quest Software (www.quest.com)
> SQL Server MVP
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
Thursday, February 9, 2012
A good Database Modelling Tool for Sql Server 2005
Hello i am looking for a good database modelling tool in order to design an
sql server database.
I have recently come across with DBDesigner 4. Can you suggest me a better
alternative?
Hello,
Go for microsoft Visio. Download a trial version from below URL:-
http://office.microsoft.com/en-us/visio/default.aspx
Thanks
Hari
"WoodenSWord" <WoodenSWord@.discussions.microsoft.com> wrote in message
news:2BE42E3C-457B-47F4-9B48-5909C70BF5FB@.microsoft.com...
> Hello i am looking for a good database modelling tool in order to design
> an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||If you can persuade your company to fork out the cash, I'd recommend ERWIN :
http://www3.ca.com/solutions/Product.aspx?ID=260
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I use Erwin but it is really expensive for most people. ER/Studio from
Embarcadero is also very good and much cheaper -
http://www.embarcadero.com/products/erstudio/index.html.
Roman
"WoodenSWord" wrote:
> Hello i am looking for a good database modelling tool in order to design an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||We can 2nd that. We're a small agency and find er-studio almost
indespensible.
The developers are always after me for a new 'database map' ...
It's helpful to have a large format plotter around if you're going to be
creating systems with hundreds of tables and relations. Or current just
barely readable er-studio map for our water rights system measures roughly 3
x 4 feet.
Barry
in Oregon
"Roman Rehak" <RomanRehak@.discussions.microsoft.com> wrote in message
news:B79EFCE6-677E-4AD9-88A8-1579587F15BD@.microsoft.com...[vbcol=seagreen]
> I use Erwin but it is really expensive for most people. ER/Studio from
> Embarcadero is also very good and much cheaper -
> http://www.embarcadero.com/products/erstudio/index.html.
> Roman
> "WoodenSWord" wrote:
sql server database.
I have recently come across with DBDesigner 4. Can you suggest me a better
alternative?
Hello,
Go for microsoft Visio. Download a trial version from below URL:-
http://office.microsoft.com/en-us/visio/default.aspx
Thanks
Hari
"WoodenSWord" <WoodenSWord@.discussions.microsoft.com> wrote in message
news:2BE42E3C-457B-47F4-9B48-5909C70BF5FB@.microsoft.com...
> Hello i am looking for a good database modelling tool in order to design
> an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||If you can persuade your company to fork out the cash, I'd recommend ERWIN :
http://www3.ca.com/solutions/Product.aspx?ID=260
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I use Erwin but it is really expensive for most people. ER/Studio from
Embarcadero is also very good and much cheaper -
http://www.embarcadero.com/products/erstudio/index.html.
Roman
"WoodenSWord" wrote:
> Hello i am looking for a good database modelling tool in order to design an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||We can 2nd that. We're a small agency and find er-studio almost
indespensible.
The developers are always after me for a new 'database map' ...
It's helpful to have a large format plotter around if you're going to be
creating systems with hundreds of tables and relations. Or current just
barely readable er-studio map for our water rights system measures roughly 3
x 4 feet.
Barry
in Oregon
"Roman Rehak" <RomanRehak@.discussions.microsoft.com> wrote in message
news:B79EFCE6-677E-4AD9-88A8-1579587F15BD@.microsoft.com...[vbcol=seagreen]
> I use Erwin but it is really expensive for most people. ER/Studio from
> Embarcadero is also very good and much cheaper -
> http://www.embarcadero.com/products/erstudio/index.html.
> Roman
> "WoodenSWord" wrote:
A Few Questions
We are looking at RS2005 as a possible reporting tool, and trying to figure
out a few things with not much luck, so I'll try asking here. The questions:
1. How can you hit multiple data sources within one data set?
We have cases where we want to take information from two different
databases, say a MS SQL and an Oracle, and join information from these two
into one dataset, is this possible and if so how?
2. How do you a new field within the report set?
Say in one of the fields (call it field x) being returned you want to create
another field (call it field y) and populate it with a certain set of
values. For example, if field x = "A" then we want to populate field y with
a value of 10. If field x = "F" then we want to populate field y with a
value of 15.
3. How do you deal with multiple selects in one statement. Example TYPE in
(10,20,30,70,90)?
In Reporting services you would have to use multiple OR statements. This
would be bad if we had a lot of other inclusions or exclusions.
ThanksOn Mar 16, 9:22 am, "Nobody" <trin...@.nobody.com> wrote:
> We are looking at RS2005 as a possible reporting tool, and trying to figure
> out a few things with not much luck, so I'll try asking here. The questions:
> 1. How can you hit multiple data sources within one data set?
> We have cases where we want to take information from two different
> databases, say a MS SQL and an Oracle, and join information from these two
> into one dataset, is this possible and if so how?
> 2. How do you a new field within the report set?
> Say in one of the fields (call it field x) being returned you want to create
> another field (call it field y) and populate it with a certain set of
> values. For example, if field x = "A" then we want to populate field y with
> a value of 10. If field x = "F" then we want to populate field y with a
> value of 15.
> 3. How do you deal with multiple selects in one statement. Example TYPE in
> (10,20,30,70,90)?
> In Reporting services you would have to use multiple OR statements. This
> would be bad if we had a lot of other inclusions or exclusions.
> Thanks
1. Refer to Amarnath's response on a previous post concerning this
question:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/9dc8aa59b6f9271f/d54db9f92d839b88?lnk=gst&q=oracle+sql+server&rnum=2#d54db9f92d839b88
2. For field y, in Layout view, select field y, right-click it and
select 'Expression...' in the Edit Expression dialog, enter in either
of the following:
=switch(Fields!x.Value = "A", 10, Fields!x.Value = "F", 15, true, 0)
-or-
=iif(Fields!x.Value = "A", 10, 15)
3. This question is not very clear to me. So I'll do the best I can
here:
In the report query the SQL syntax should be: select ... from ...
where TYPE in (10,20,30,70,90)
In the report in general, a multi-select parameter can be used for the
TYPE options and use a temporary table, while loop (or cursor) and
keyword 'patindex' (in the report stored procedure/query) to parse the
values in the multi-select parameter that is returned. The results
should be put into a temp table and then 'select ... from ... where
TYPE in (select TYPE from #TempTable)' should do the trick.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks, I'll pass that on to the person here that had those questions.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1174103874.201065.41030@.n76g2000hsh.googlegroups.com...
> 1. Refer to Amarnath's response on a previous post concerning this
> question:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/9dc8aa59b6f9271f/d54db9f92d839b88?lnk=gst&q=oracle+sql+server&rnum=2#d54db9f92d839b88
> 2. For field y, in Layout view, select field y, right-click it and
> select 'Expression...' in the Edit Expression dialog, enter in either
> of the following:
> =switch(Fields!x.Value = "A", 10, Fields!x.Value = "F", 15, true, 0)
> -or-
> =iif(Fields!x.Value = "A", 10, 15)
> 3. This question is not very clear to me. So I'll do the best I can
> here:
> In the report query the SQL syntax should be: select ... from ...
> where TYPE in (10,20,30,70,90)
> In the report in general, a multi-select parameter can be used for the
> TYPE options and use a temporary table, while loop (or cursor) and
> keyword 'patindex' (in the report stored procedure/query) to parse the
> values in the multi-select parameter that is returned. The results
> should be put into a temp table and then 'select ... from ... where
> TYPE in (select TYPE from #TempTable)' should do the trick.
> Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
out a few things with not much luck, so I'll try asking here. The questions:
1. How can you hit multiple data sources within one data set?
We have cases where we want to take information from two different
databases, say a MS SQL and an Oracle, and join information from these two
into one dataset, is this possible and if so how?
2. How do you a new field within the report set?
Say in one of the fields (call it field x) being returned you want to create
another field (call it field y) and populate it with a certain set of
values. For example, if field x = "A" then we want to populate field y with
a value of 10. If field x = "F" then we want to populate field y with a
value of 15.
3. How do you deal with multiple selects in one statement. Example TYPE in
(10,20,30,70,90)?
In Reporting services you would have to use multiple OR statements. This
would be bad if we had a lot of other inclusions or exclusions.
ThanksOn Mar 16, 9:22 am, "Nobody" <trin...@.nobody.com> wrote:
> We are looking at RS2005 as a possible reporting tool, and trying to figure
> out a few things with not much luck, so I'll try asking here. The questions:
> 1. How can you hit multiple data sources within one data set?
> We have cases where we want to take information from two different
> databases, say a MS SQL and an Oracle, and join information from these two
> into one dataset, is this possible and if so how?
> 2. How do you a new field within the report set?
> Say in one of the fields (call it field x) being returned you want to create
> another field (call it field y) and populate it with a certain set of
> values. For example, if field x = "A" then we want to populate field y with
> a value of 10. If field x = "F" then we want to populate field y with a
> value of 15.
> 3. How do you deal with multiple selects in one statement. Example TYPE in
> (10,20,30,70,90)?
> In Reporting services you would have to use multiple OR statements. This
> would be bad if we had a lot of other inclusions or exclusions.
> Thanks
1. Refer to Amarnath's response on a previous post concerning this
question:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/9dc8aa59b6f9271f/d54db9f92d839b88?lnk=gst&q=oracle+sql+server&rnum=2#d54db9f92d839b88
2. For field y, in Layout view, select field y, right-click it and
select 'Expression...' in the Edit Expression dialog, enter in either
of the following:
=switch(Fields!x.Value = "A", 10, Fields!x.Value = "F", 15, true, 0)
-or-
=iif(Fields!x.Value = "A", 10, 15)
3. This question is not very clear to me. So I'll do the best I can
here:
In the report query the SQL syntax should be: select ... from ...
where TYPE in (10,20,30,70,90)
In the report in general, a multi-select parameter can be used for the
TYPE options and use a temporary table, while loop (or cursor) and
keyword 'patindex' (in the report stored procedure/query) to parse the
values in the multi-select parameter that is returned. The results
should be put into a temp table and then 'select ... from ... where
TYPE in (select TYPE from #TempTable)' should do the trick.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks, I'll pass that on to the person here that had those questions.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1174103874.201065.41030@.n76g2000hsh.googlegroups.com...
> 1. Refer to Amarnath's response on a previous post concerning this
> question:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/9dc8aa59b6f9271f/d54db9f92d839b88?lnk=gst&q=oracle+sql+server&rnum=2#d54db9f92d839b88
> 2. For field y, in Layout view, select field y, right-click it and
> select 'Expression...' in the Edit Expression dialog, enter in either
> of the following:
> =switch(Fields!x.Value = "A", 10, Fields!x.Value = "F", 15, true, 0)
> -or-
> =iif(Fields!x.Value = "A", 10, 15)
> 3. This question is not very clear to me. So I'll do the best I can
> here:
> In the report query the SQL syntax should be: select ... from ...
> where TYPE in (10,20,30,70,90)
> In the report in general, a multi-select parameter can be used for the
> TYPE options and use a temporary table, while loop (or cursor) and
> keyword 'patindex' (in the report stored procedure/query) to parse the
> values in the multi-select parameter that is returned. The results
> should be put into a temp table and then 'select ... from ... where
> TYPE in (select TYPE from #TempTable)' should do the trick.
> Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
Subscribe to:
Posts (Atom)