Hi:
I've been receiving the below mentioned error messages in
my SQL Server Log for all databases ;
1. IO is thawed
2. IO is frozen for snapshot
Any idea as to for what reason this would be happening.
Thanks very much in advance!Most likely due to using dbcc freeze_io and thaw_io.
The commands are depreciated in SQL Server 2000 and are not
recommended to be used due to the depreciation as well as
the potential for these commands to hang the server.
-Sue
On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
<apatel@.esricanada.com> wrote:
>Hi:
>I've been receiving the below mentioned error messages in
>my SQL Server Log for all databases ;
>1. IO is thawed
>2. IO is frozen for snapshot
>Any idea as to for what reason this would be happening.
>Thanks very much in advance!|||Thanks Sue!
I haven't use the dbcc freeze_io and thaw_io. Would there
any process of job that would have used this in the
background?
Thanks
>--Original Message--
>Most likely due to using dbcc freeze_io and thaw_io.
>The commands are depreciated in SQL Server 2000 and are
not
>recommended to be used due to the depreciation as well as
>the potential for these commands to hang the server.
>-Sue
>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
><apatel@.esricanada.com> wrote:
>
in
>.
>|||I've read a couple posts where it seems some third party
vendor for backups or shadow copy backups uses this. SQL
Server itself or any of native functionality isn't likely to
be implementing this. I'd look at whatever third party
tools, products you may be using. You could look at the
times you are getting the messages logged and try to figure
out what's running at those times.
-Sue
On Tue, 13 Apr 2004 08:52:43 -0700, "Arpit"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue!
>I haven't use the dbcc freeze_io and thaw_io. Would there
>any process of job that would have used this in the
>background?
>Thanks
>not
>insql
Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Rebuilding indexes
Hi
I got the advice to rebuild the indexes on the databases once a year.
Espesially if the database have grown much. The question is: How do I
do that?
If I try to run the indexscript wich was run when the databases were
created, I only get the message that the indexes already exists.
Are there a command to automatically rebuild the indexes
automatically?
I got the advice to rebuild the indexes on the databases once a year.
Espesially if the database have grown much. The question is: How do I
do that?
If I try to run the indexscript wich was run when the databases were
created, I only get the message that the indexes already exists.
Are there a command to automatically rebuild the indexes
automatically?
Roger"Roger" <roger.valand@.tiscali.no> wrote in message
news:3ite40tfa67m2of2qq5m5uc8vlvreilko3@.4ax.com...
> Hi
> I got the advice to rebuild the indexes on the databases once a year.
> Espesially if the database have grown much. The question is: How do I
> do that?
> If I try to run the indexscript wich was run when the databases were
> created, I only get the message that the indexes already exists.
> Are there a command to automatically rebuild the indexes
> automatically?
> Roger
See DBCC DBREINDEX in Books Online, or use a maintenance plan. Once a year
sounds like a very conservative schedule, so you may want to consider weekly
rebuilds if you believe you need them.
Simon|||Checkout the whitepaper below - it will help you decide whether it's even
worth rebuilding the indexes and explain the pros and cons of the different
methods of doing so.
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger" <roger.valand@.tiscali.no> wrote in message
news:3ite40tfa67m2of2qq5m5uc8vlvreilko3@.4ax.com...
> Hi
> I got the advice to rebuild the indexes on the databases once a year.
> Espesially if the database have grown much. The question is: How do I
> do that?
> If I try to run the indexscript wich was run when the databases were
> created, I only get the message that the indexes already exists.
> Are there a command to automatically rebuild the indexes
> automatically?
> Roger|||Hi
Thank you
I'll dig into it
On Fri, 5 Mar 2004 10:28:48 -0800, "Paul S Randal [MS]"
<prandal@.online.microsoft.com> wrote:
>Checkout the whitepaper below - it will help you decide whether it's even
>worth rebuilding the indexes and explain the pros and cons of the different
>methods of doing so.
>http://www.microsoft.com/technet/tr...ze/ss2kidbp.aspsql
Tuesday, March 20, 2012
Rebuild fulltext
Hi!
I have a problem with a 4-node cluster. It was a 2-node sql2k on w2k.
We installed new hw with w2k3 and installed the old instances there
(restore). After that we added 2 more w2k3 nodes and installed the
original 2 instances on all 4 nodes. We also set up 2 new instances to
be a total of 4.
The problem is that when we put in the new hw (node 3 and 4) we saw
that we lost the control of the fulltext resource on the first 2
instances. It started failing and then failback, sometimes it started,
sometimes not. This started before we actually joined node 3-4 to the
clustrer. Since we don't use fulltext we took away the resource from
the cluster on these 2 instances. On the new instances (3 and 4) there
has newer been a problem. All nodes can run all 4 instances. There is
no problem with any other resource.
I have seen kb 812666 and I think that I should try rebuildning
fulltext. I think it looks very complex, is there anyone that have
done this?
/Peter
Peter,
Yes, KB article 812666 "How to recover a failed full-text search resource on
a clustered instance of SQL Server 2000" at
http://support.microsoft.com/default.aspx?kbid=812666 is complex, but since
you don't use the MSSearch (Fulltext ) service, and have already removed the
fulltext (Microsoft Search) service from the cluster resource, rebuilding
this service is not necessary/
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
news:415bfda4.4507871@.msnews.microsoft.com...
> Hi!
> I have a problem with a 4-node cluster. It was a 2-node sql2k on w2k.
> We installed new hw with w2k3 and installed the old instances there
> (restore). After that we added 2 more w2k3 nodes and installed the
> original 2 instances on all 4 nodes. We also set up 2 new instances to
> be a total of 4.
> The problem is that when we put in the new hw (node 3 and 4) we saw
> that we lost the control of the fulltext resource on the first 2
> instances. It started failing and then failback, sometimes it started,
> sometimes not. This started before we actually joined node 3-4 to the
> clustrer. Since we don't use fulltext we took away the resource from
> the cluster on these 2 instances. On the new instances (3 and 4) there
> has newer been a problem. All nodes can run all 4 instances. There is
> no problem with any other resource.
> I have seen kb 812666 and I think that I should try rebuildning
> fulltext. I think it looks very complex, is there anyone that have
> done this?
> /Peter
|||John,
thanks for the reply. My intention is to get all 4 nodes equal setup.
We want to be able to run new databases where it's best suitet and
don't depend on differenses between instances. That's way I want to do
this. I have to set up my test environment so I can test it before I
go in the production.
Have you done this in a cluster?
/Peter
On Thu, 30 Sep 2004 11:19:01 -0700, "John Kane" <jt-kane@.comcast.net>
wrotc:
>Peter,
>Yes, KB article 812666 "How to recover a failed full-text search resource on
>a clustered instance of SQL Server 2000" at
>http://support.microsoft.com/default.aspx?kbid=812666 is complex, but since
>you don't use the MSSearch (Fulltext ) service, and have already removed the
>fulltext (Microsoft Search) service from the cluster resource, rebuilding
>this service is not necessary/
>Note, you can also post FTS related questions to the newsgroup:
>microsoft.public.sqlserver.fulltext
>Regards,
>John
>
>
>"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
>news:415bfda4.4507871@.msnews.microsoft.com...
>
|||You're welcome, Peter,
Unfortunately, I do not have a clustered environment to test this KB
article, as I have only one home server and a laptop... ;-(
If you do have a test clustered environment, even one with just two nodes,
I'd recommend that you test using that environment.
Regards,
John
"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
news:415d70eb.31078308@.msnews.microsoft.com...[vbcol=seagreen]
> John,
> thanks for the reply. My intention is to get all 4 nodes equal setup.
> We want to be able to run new databases where it's best suitet and
> don't depend on differenses between instances. That's way I want to do
> this. I have to set up my test environment so I can test it before I
> go in the production.
> Have you done this in a cluster?
> /Peter
> On Thu, 30 Sep 2004 11:19:01 -0700, "John Kane" <jt-kane@.comcast.net>
> wrotc:
on[vbcol=seagreen]
since[vbcol=seagreen]
the
>
|||I'll definitive test it. It will be under vmware gsx.
/Peter
On Mon, 4 Oct 2004 10:57:02 -0700, "John Kane" <jt-kane@.comcast.net>
wrotc:
>You're welcome, Peter,
>Unfortunately, I do not have a clustered environment to test this KB
>article, as I have only one home server and a laptop... ;-(
>If you do have a test clustered environment, even one with just two nodes,
>I'd recommend that you test using that environment.
>Regards,
>John
>
>"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
>news:415d70eb.31078308@.msnews.microsoft.com...
>on
>since
>the
>
I have a problem with a 4-node cluster. It was a 2-node sql2k on w2k.
We installed new hw with w2k3 and installed the old instances there
(restore). After that we added 2 more w2k3 nodes and installed the
original 2 instances on all 4 nodes. We also set up 2 new instances to
be a total of 4.
The problem is that when we put in the new hw (node 3 and 4) we saw
that we lost the control of the fulltext resource on the first 2
instances. It started failing and then failback, sometimes it started,
sometimes not. This started before we actually joined node 3-4 to the
clustrer. Since we don't use fulltext we took away the resource from
the cluster on these 2 instances. On the new instances (3 and 4) there
has newer been a problem. All nodes can run all 4 instances. There is
no problem with any other resource.
I have seen kb 812666 and I think that I should try rebuildning
fulltext. I think it looks very complex, is there anyone that have
done this?
/Peter
Peter,
Yes, KB article 812666 "How to recover a failed full-text search resource on
a clustered instance of SQL Server 2000" at
http://support.microsoft.com/default.aspx?kbid=812666 is complex, but since
you don't use the MSSearch (Fulltext ) service, and have already removed the
fulltext (Microsoft Search) service from the cluster resource, rebuilding
this service is not necessary/
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
news:415bfda4.4507871@.msnews.microsoft.com...
> Hi!
> I have a problem with a 4-node cluster. It was a 2-node sql2k on w2k.
> We installed new hw with w2k3 and installed the old instances there
> (restore). After that we added 2 more w2k3 nodes and installed the
> original 2 instances on all 4 nodes. We also set up 2 new instances to
> be a total of 4.
> The problem is that when we put in the new hw (node 3 and 4) we saw
> that we lost the control of the fulltext resource on the first 2
> instances. It started failing and then failback, sometimes it started,
> sometimes not. This started before we actually joined node 3-4 to the
> clustrer. Since we don't use fulltext we took away the resource from
> the cluster on these 2 instances. On the new instances (3 and 4) there
> has newer been a problem. All nodes can run all 4 instances. There is
> no problem with any other resource.
> I have seen kb 812666 and I think that I should try rebuildning
> fulltext. I think it looks very complex, is there anyone that have
> done this?
> /Peter
|||John,
thanks for the reply. My intention is to get all 4 nodes equal setup.
We want to be able to run new databases where it's best suitet and
don't depend on differenses between instances. That's way I want to do
this. I have to set up my test environment so I can test it before I
go in the production.
Have you done this in a cluster?
/Peter
On Thu, 30 Sep 2004 11:19:01 -0700, "John Kane" <jt-kane@.comcast.net>
wrotc:
>Peter,
>Yes, KB article 812666 "How to recover a failed full-text search resource on
>a clustered instance of SQL Server 2000" at
>http://support.microsoft.com/default.aspx?kbid=812666 is complex, but since
>you don't use the MSSearch (Fulltext ) service, and have already removed the
>fulltext (Microsoft Search) service from the cluster resource, rebuilding
>this service is not necessary/
>Note, you can also post FTS related questions to the newsgroup:
>microsoft.public.sqlserver.fulltext
>Regards,
>John
>
>
>"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
>news:415bfda4.4507871@.msnews.microsoft.com...
>
|||You're welcome, Peter,
Unfortunately, I do not have a clustered environment to test this KB
article, as I have only one home server and a laptop... ;-(
If you do have a test clustered environment, even one with just two nodes,
I'd recommend that you test using that environment.
Regards,
John
"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
news:415d70eb.31078308@.msnews.microsoft.com...[vbcol=seagreen]
> John,
> thanks for the reply. My intention is to get all 4 nodes equal setup.
> We want to be able to run new databases where it's best suitet and
> don't depend on differenses between instances. That's way I want to do
> this. I have to set up my test environment so I can test it before I
> go in the production.
> Have you done this in a cluster?
> /Peter
> On Thu, 30 Sep 2004 11:19:01 -0700, "John Kane" <jt-kane@.comcast.net>
> wrotc:
on[vbcol=seagreen]
since[vbcol=seagreen]
the
>
|||I'll definitive test it. It will be under vmware gsx.
/Peter
On Mon, 4 Oct 2004 10:57:02 -0700, "John Kane" <jt-kane@.comcast.net>
wrotc:
>You're welcome, Peter,
>Unfortunately, I do not have a clustered environment to test this KB
>article, as I have only one home server and a laptop... ;-(
>If you do have a test clustered environment, even one with just two nodes,
>I'd recommend that you test using that environment.
>Regards,
>John
>
>"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
>news:415d70eb.31078308@.msnews.microsoft.com...
>on
>since
>the
>
Friday, March 9, 2012
reasons of CAs in private lan?
hi
i don't understand well :
why i need a to request to a CA for a certificate
if i just try to secure communications
in my private Lan ... between 2 servers
why i need to involve a third entinty ?
are not my servers CAs
for my Lan by themselves ?
thanks
i will appreciate
your comments
atte,
Hernn Castelo
SGA - UTN - FRBAHernan,
This is too broad of a topic to cover on the newsgroup forum. Please
read the following on understanding PKI.
http://www.microsoft.com/technet/ar...uate/featfunc/p
kiintro.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||ok
i'm sliding to that link
I'll be back
atte,
Hernn Castelo
SGA - UTN - FRBA
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> escribi en el men
saje
news:W88F34OyEHA.3436@.cpmsftngxa10.phx.gbl...
> Hernan,
> This is too broad of a topic to cover on the newsgroup forum. Please
> read the following on understanding PKI.
>
http://www.microsoft.com/technet/ar...uate/featfunc/p
> kiintro.mspx
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
i don't understand well :
why i need a to request to a CA for a certificate
if i just try to secure communications
in my private Lan ... between 2 servers
why i need to involve a third entinty ?
are not my servers CAs
for my Lan by themselves ?
thanks
i will appreciate
your comments
atte,
Hernn Castelo
SGA - UTN - FRBAHernan,
This is too broad of a topic to cover on the newsgroup forum. Please
read the following on understanding PKI.
http://www.microsoft.com/technet/ar...uate/featfunc/p
kiintro.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||ok
i'm sliding to that link
I'll be back
atte,
Hernn Castelo
SGA - UTN - FRBA
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> escribi en el men
saje
news:W88F34OyEHA.3436@.cpmsftngxa10.phx.gbl...
> Hernan,
> This is too broad of a topic to cover on the newsgroup forum. Please
> read the following on understanding PKI.
>
http://www.microsoft.com/technet/ar...uate/featfunc/p
> kiintro.mspx
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005
Hi
I'm trying to put a case together to encourage my employer to upgrade from
its SQL 2000 Enterprise farm to SQL Server 2005.
We're interested in Enterprise functionality, such as:
- Improvement in backups
- Monitoring improvements
- Resilience improvements (mirroring, replication, failover etc).
- Cost / payback case.
Can anyone point me in the right direction to be able to put together such a
case.
Thanks"Joe Spears" <joespears@.hotmail.co.uk> wrote in message
news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
> a case.
>
What's New in SQL Server 2005
[url]http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[/ur
l]
There's lots of cool stuff:
Mirroring.
Monitoring performance with Server reports and DMV's.
Transactional Replication.
The new database maintence plan desiner is great.
Database Mail.
SSIS.
But what does your organization struggle with? Stories that start "Remember
when we had that problem ...", help motivate the upgrade. Heve a concrete
vision for how SQL Server 2005 fits in your environment. That's the
business case.
David|||And do your COTS or applications support SQL 2005 yet?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23n5sSKQeGHA.3948@.TK2MSFTNGP03.phx.gbl...
> "Joe Spears" <joespears@.hotmail.co.uk> wrote in message
> news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
>
> What's New in SQL Server 2005
> [url]http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[/
url]
> There's lots of cool stuff:
> Mirroring.
> Monitoring performance with Server reports and DMV's.
> Transactional Replication.
> The new database maintence plan desiner is great.
> Database Mail.
> SSIS.
> But what does your organization struggle with? Stories that start
> "Remember when we had that problem ...", help motivate the upgrade. Heve
> a concrete vision for how SQL Server 2005 fits in your environment.
> That's the business case.
> David
>|||If you ask 10 people this question, you may get 10 different answers because
SQL2005 offers a wide array of new features and improvement. Personally, my
current reasons for upgrade are the following VLDB-related features (no
considerations given to rank these or other features systematically):
-- More robust DBCC
-- More granular backup/recovery
-- Table partitioning
-- x64 support
Linchi
"Joe Spears" wrote:
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
a
> case.
> Thanks
>
>
>|||Actually you'd probably get 40 different answers.
Start the process with all of the things that you struggle with currently.
1. Does reindexing lock up your tables and make applications unavailble? -
Online reindexing in 2005 Enterprise Edition
2. Do you have to do large scale data loads or archives that cause massive
contention making the data unavailable? - Table partitioning
3. Have you ever had a production issue where you simply scratched your head
because the engine is just a great big black box that you can't get detailed
diagnostics from? - Dynamic management views and functions
4. Have you ever needed to create an index in order to improve performance
at a critical point, but couldn't because it would lock up the entire
table? - Online index creation in the Enterprise Edition
5. Have you ever had an index corrupt causing everything to be
unavailable? - online index creation in Enterprise Edition
6. Have you ever had a table corrupted that required restoring the entire
database? - page level restores
7. Have you ever needed to restore just a single filegroup into a database
without taking everything else offline? - online restores in Enterprise
Edition
8. Have you ever needed to encrypt data within a table? - encryption
features
The list goes on and on and on. You'll note that I didn't pick on a single
feature that is classified as "cool". Management doesn't care about "cool".
They care about the databases being online, always. Each one of the items
above hit very definite problems that many people have run across in
production that caused databases to be unavailable and management to be
unhappy. 2005 eliminates all of the issues above. (You can keep filling in
the blanks with additional issues from your enviroment.)
If you need to make a case to management, talking about "cool" features is
going to get the request denied. Talking about things that happened in your
environment (and will likely happen again) which will no longer occur by
deploying SQL Server 2005 is going to get management to listen.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E309B075-01DC-4E40-B4E0-7FBA6F4272F3@.microsoft.com...[vbcol=seagreen]
> If you ask 10 people this question, you may get 10 different answers
> because
> SQL2005 offers a wide array of new features and improvement. Personally,
> my
> current reasons for upgrade are the following VLDB-related features (no
> considerations given to rank these or other features systematically):
> -- More robust DBCC
> -- More granular backup/recovery
> -- Table partitioning
> -- x64 support
> Linchi
> "Joe Spears" wrote:
>
I'm trying to put a case together to encourage my employer to upgrade from
its SQL 2000 Enterprise farm to SQL Server 2005.
We're interested in Enterprise functionality, such as:
- Improvement in backups
- Monitoring improvements
- Resilience improvements (mirroring, replication, failover etc).
- Cost / payback case.
Can anyone point me in the right direction to be able to put together such a
case.
Thanks"Joe Spears" <joespears@.hotmail.co.uk> wrote in message
news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
> a case.
>
What's New in SQL Server 2005
[url]http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[/ur
l]
There's lots of cool stuff:
Mirroring.
Monitoring performance with Server reports and DMV's.
Transactional Replication.
The new database maintence plan desiner is great.
Database Mail.
SSIS.
But what does your organization struggle with? Stories that start "Remember
when we had that problem ...", help motivate the upgrade. Heve a concrete
vision for how SQL Server 2005 fits in your environment. That's the
business case.
David|||And do your COTS or applications support SQL 2005 yet?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23n5sSKQeGHA.3948@.TK2MSFTNGP03.phx.gbl...
> "Joe Spears" <joespears@.hotmail.co.uk> wrote in message
> news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
>
> What's New in SQL Server 2005
> [url]http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[/
url]
> There's lots of cool stuff:
> Mirroring.
> Monitoring performance with Server reports and DMV's.
> Transactional Replication.
> The new database maintence plan desiner is great.
> Database Mail.
> SSIS.
> But what does your organization struggle with? Stories that start
> "Remember when we had that problem ...", help motivate the upgrade. Heve
> a concrete vision for how SQL Server 2005 fits in your environment.
> That's the business case.
> David
>|||If you ask 10 people this question, you may get 10 different answers because
SQL2005 offers a wide array of new features and improvement. Personally, my
current reasons for upgrade are the following VLDB-related features (no
considerations given to rank these or other features systematically):
-- More robust DBCC
-- More granular backup/recovery
-- Table partitioning
-- x64 support
Linchi
"Joe Spears" wrote:
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
a
> case.
> Thanks
>
>
>|||Actually you'd probably get 40 different answers.
Start the process with all of the things that you struggle with currently.
1. Does reindexing lock up your tables and make applications unavailble? -
Online reindexing in 2005 Enterprise Edition
2. Do you have to do large scale data loads or archives that cause massive
contention making the data unavailable? - Table partitioning
3. Have you ever had a production issue where you simply scratched your head
because the engine is just a great big black box that you can't get detailed
diagnostics from? - Dynamic management views and functions
4. Have you ever needed to create an index in order to improve performance
at a critical point, but couldn't because it would lock up the entire
table? - Online index creation in the Enterprise Edition
5. Have you ever had an index corrupt causing everything to be
unavailable? - online index creation in Enterprise Edition
6. Have you ever had a table corrupted that required restoring the entire
database? - page level restores
7. Have you ever needed to restore just a single filegroup into a database
without taking everything else offline? - online restores in Enterprise
Edition
8. Have you ever needed to encrypt data within a table? - encryption
features
The list goes on and on and on. You'll note that I didn't pick on a single
feature that is classified as "cool". Management doesn't care about "cool".
They care about the databases being online, always. Each one of the items
above hit very definite problems that many people have run across in
production that caused databases to be unavailable and management to be
unhappy. 2005 eliminates all of the issues above. (You can keep filling in
the blanks with additional issues from your enviroment.)
If you need to make a case to management, talking about "cool" features is
going to get the request denied. Talking about things that happened in your
environment (and will likely happen again) which will no longer occur by
deploying SQL Server 2005 is going to get management to listen.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E309B075-01DC-4E40-B4E0-7FBA6F4272F3@.microsoft.com...[vbcol=seagreen]
> If you ask 10 people this question, you may get 10 different answers
> because
> SQL2005 offers a wide array of new features and improvement. Personally,
> my
> current reasons for upgrade are the following VLDB-related features (no
> considerations given to rank these or other features systematically):
> -- More robust DBCC
> -- More granular backup/recovery
> -- Table partitioning
> -- x64 support
> Linchi
> "Joe Spears" wrote:
>
Wednesday, March 7, 2012
Rearrange column
Hi!
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Number
1 Testing 1
2 Testing2 2
3 Testing3 3
4 Testing4 4
5 Testing5 5
I want this:
ID Text Number
1 Testing 5
2 Testing2 4
3 Testing3 3
4 Testing4 2
5 Testing5 1
The numbers of rows varies so it must be dynamical and i want 2 inparameters
(startID and endID).
I hope someone can help me.
//MagnusNumber = endID - Number +1?
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:777484C3-3018-48ED-81D8-5E9BF071DD1C@.microsoft.com...
> Hi!
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Number
> 1 Testing 1
> 2 Testing2 2
> 3 Testing3 3
> 4 Testing4 4
> 5 Testing5 5
> I want this:
> ID Text Number
> 1 Testing 5
> 2 Testing2 4
> 3 Testing3 3
> 4 Testing4 2
> 5 Testing5 1
> The numbers of rows varies so it must be dynamical and i want 2
> inparameters
> (startID and endID).
> I hope someone can help me.
> //Magnus
>|||When you say "rearrange a column", exactly what transformation are you
looking for? If you only specify start and end rows then what
determines the order of the other values you don't specify?
If this column is always to be dynamic then it doesn't really make
sense to have it in the table at all. Derive it in a query like this:
SELECT A.au_id, A.au_lname,
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id<=A.au_id) AS number
FROM pubs.dbo.authors AS A
ORDER BY A.au_id ;
David Portas
SQL Server MVP
--|||The meaning with the column "Number" is to display data sorted in webform.
But sometime this displayorder must be changed.
The rest of the columns must stay the same.
//Magnus
"David Portas" wrote:
> When you say "rearrange a column", exactly what transformation are you
> looking for? If you only specify start and end rows then what
> determines the order of the other values you don't specify?
> If this column is always to be dynamic then it doesn't really make
> sense to have it in the table at all. Derive it in a query like this:
> SELECT A.au_id, A.au_lname,
> (SELECT COUNT(*)
> FROM pubs.dbo.authors
> WHERE au_id<=A.au_id) AS number
> FROM pubs.dbo.authors AS A
> ORDER BY A.au_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||Mange
create table #test
(
col int not null primary key,
col1 char(1)
)
insert into #test values (1,'a')
insert into #test values (2,'b')
insert into #test values (3,'c')
insert into #test values (4,'d')
select col,col1,
(select count(*) from #test t where t.col>=#test.col) from #test
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:5ABD8DA6-F591-4EFD-8C19-716042AB7010@.microsoft.com...
> The meaning with the column "Number" is to display data sorted in webform.
> But sometime this displayorder must be changed.
> The rest of the columns must stay the same.
> //Magnus
>
>
> "David Portas" wrote:
>|||>> The meaning with the column "Number" is to display data sorted in webform
. But sometime this displayorder must be changed. The rest of the columns mu
st stay the same. <<
Instead of the vague name "number", you should have used sometrhng like
"physical_display_position" so people maintaining the code would know
it is not a logical data element.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.|||It doesnt matter what the intention with the column is.
Do you mean that all sorting is done in the webform ?
"--CELKO--" wrote:
> Instead of the vague name "number", you should have used sometrhng like
> "physical_display_position" so people maintaining the code would know
> it is not a logical data element.
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
>
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Number
1 Testing 1
2 Testing2 2
3 Testing3 3
4 Testing4 4
5 Testing5 5
I want this:
ID Text Number
1 Testing 5
2 Testing2 4
3 Testing3 3
4 Testing4 2
5 Testing5 1
The numbers of rows varies so it must be dynamical and i want 2 inparameters
(startID and endID).
I hope someone can help me.
//MagnusNumber = endID - Number +1?
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:777484C3-3018-48ED-81D8-5E9BF071DD1C@.microsoft.com...
> Hi!
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Number
> 1 Testing 1
> 2 Testing2 2
> 3 Testing3 3
> 4 Testing4 4
> 5 Testing5 5
> I want this:
> ID Text Number
> 1 Testing 5
> 2 Testing2 4
> 3 Testing3 3
> 4 Testing4 2
> 5 Testing5 1
> The numbers of rows varies so it must be dynamical and i want 2
> inparameters
> (startID and endID).
> I hope someone can help me.
> //Magnus
>|||When you say "rearrange a column", exactly what transformation are you
looking for? If you only specify start and end rows then what
determines the order of the other values you don't specify?
If this column is always to be dynamic then it doesn't really make
sense to have it in the table at all. Derive it in a query like this:
SELECT A.au_id, A.au_lname,
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id<=A.au_id) AS number
FROM pubs.dbo.authors AS A
ORDER BY A.au_id ;
David Portas
SQL Server MVP
--|||The meaning with the column "Number" is to display data sorted in webform.
But sometime this displayorder must be changed.
The rest of the columns must stay the same.
//Magnus
"David Portas" wrote:
> When you say "rearrange a column", exactly what transformation are you
> looking for? If you only specify start and end rows then what
> determines the order of the other values you don't specify?
> If this column is always to be dynamic then it doesn't really make
> sense to have it in the table at all. Derive it in a query like this:
> SELECT A.au_id, A.au_lname,
> (SELECT COUNT(*)
> FROM pubs.dbo.authors
> WHERE au_id<=A.au_id) AS number
> FROM pubs.dbo.authors AS A
> ORDER BY A.au_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||Mange
create table #test
(
col int not null primary key,
col1 char(1)
)
insert into #test values (1,'a')
insert into #test values (2,'b')
insert into #test values (3,'c')
insert into #test values (4,'d')
select col,col1,
(select count(*) from #test t where t.col>=#test.col) from #test
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:5ABD8DA6-F591-4EFD-8C19-716042AB7010@.microsoft.com...
> The meaning with the column "Number" is to display data sorted in webform.
> But sometime this displayorder must be changed.
> The rest of the columns must stay the same.
> //Magnus
>
>
> "David Portas" wrote:
>|||>> The meaning with the column "Number" is to display data sorted in webform
. But sometime this displayorder must be changed. The rest of the columns mu
st stay the same. <<
Instead of the vague name "number", you should have used sometrhng like
"physical_display_position" so people maintaining the code would know
it is not a logical data element.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.|||It doesnt matter what the intention with the column is.
Do you mean that all sorting is done in the webform ?
"--CELKO--" wrote:
> Instead of the vague name "number", you should have used sometrhng like
> "physical_display_position" so people maintaining the code would know
> it is not a logical data element.
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
>
Rearrange column
Hi!
I'll try again
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Displayorder
1 Testing 100
2 Testing2 200
3 Testing3 300
4 Testing4 400
5 Testing5 500
The numbers of rows varies so i want 2 inparameters
(start_ID and End_ID).
Example:
start_ID, End_ID (2,4)
Will return.
ID Text Number
1 Testing 100
2 Testing2 400
3 Testing3 300
4 Testing4 200
5 Testing5 500
Can this be done?
I hope someone can help me.
//MagnusHi Magnus
Assuming that your Id columns are not contiguous then you will need to rank
them and do something like:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
SELECT [ASC].ID, [ASC].[Text],
CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
[DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
[Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [ASC]
JOIN
( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
You can still use [id] in the case statement if necessary.
John
"Mange" wrote:
> Hi!
> I'll try again
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Displayorder
> 1 Testing 100
> 2 Testing2 200
> 3 Testing3 300
> 4 Testing4 400
> 5 Testing5 500
> The numbers of rows varies so i want 2 inparameters
> (start_ID and End_ID).
> Example:
> start_ID, End_ID (2,4)
> Will return.
> ID Text Number
> 1 Testing 100
> 2 Testing2 400
> 3 Testing3 300
> 4 Testing4 200
> 5 Testing5 500
> Can this be done?
> I hope someone can help me.
> //Magnus
>|||Thanks but..
That doesnt save the result into that table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||> That doesnt save the result into that table.
No, it does not, but you can *use* it in your procedure to make it do whan
you need. :)
ML|||It doesnt work.
The result is the whole table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||Hi
It does what you specified with the data that you gave. You have not
specified what your restriction should be but you should be able to use a
WHERE clause in each of the derived tables to do what you require.
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||If you actually want to change the data try:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
UPDATE O
SET DisplayOrder = N.DisplayOrder
FROM MyDisplays O
JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.upper
AND N.id = @.lower )
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||Hi
To be more precise.
The result is exactly like if i would have used Select * from MyDisplays
"John Bell" wrote:
> Hi
> It does what you specified with the data that you gave. You have not
> specified what your restriction should be but you should be able to use a
> WHERE clause in each of the derived tables to do what you require.
> John
> "Mange" wrote:
>|||Many Thanks John you are a star.
I'm sorry about my bad english and my poor knowledge about SQL.
Thanks again it works know.
//Magnus
"John Bell" wrote:
> If you actually want to change the data try:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> UPDATE O
> SET DisplayOrder = N.DisplayOrder
> FROM MyDisplays O
> JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.uppe
r
> AND N.id = @.lower )
> John
> "Mange" wrote:
>
I'll try again
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Displayorder
1 Testing 100
2 Testing2 200
3 Testing3 300
4 Testing4 400
5 Testing5 500
The numbers of rows varies so i want 2 inparameters
(start_ID and End_ID).
Example:
start_ID, End_ID (2,4)
Will return.
ID Text Number
1 Testing 100
2 Testing2 400
3 Testing3 300
4 Testing4 200
5 Testing5 500
Can this be done?
I hope someone can help me.
//MagnusHi Magnus
Assuming that your Id columns are not contiguous then you will need to rank
them and do something like:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
SELECT [ASC].ID, [ASC].[Text],
CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
[DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
[Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [ASC]
JOIN
( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
You can still use [id] in the case statement if necessary.
John
"Mange" wrote:
> Hi!
> I'll try again
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Displayorder
> 1 Testing 100
> 2 Testing2 200
> 3 Testing3 300
> 4 Testing4 400
> 5 Testing5 500
> The numbers of rows varies so i want 2 inparameters
> (start_ID and End_ID).
> Example:
> start_ID, End_ID (2,4)
> Will return.
> ID Text Number
> 1 Testing 100
> 2 Testing2 400
> 3 Testing3 300
> 4 Testing4 200
> 5 Testing5 500
> Can this be done?
> I hope someone can help me.
> //Magnus
>|||Thanks but..
That doesnt save the result into that table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||> That doesnt save the result into that table.
No, it does not, but you can *use* it in your procedure to make it do whan
you need. :)
ML|||It doesnt work.
The result is the whole table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||Hi
It does what you specified with the data that you gave. You have not
specified what your restriction should be but you should be able to use a
WHERE clause in each of the derived tables to do what you require.
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||If you actually want to change the data try:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
UPDATE O
SET DisplayOrder = N.DisplayOrder
FROM MyDisplays O
JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.upper
AND N.id = @.lower )
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||Hi
To be more precise.
The result is exactly like if i would have used Select * from MyDisplays
"John Bell" wrote:
> Hi
> It does what you specified with the data that you gave. You have not
> specified what your restriction should be but you should be able to use a
> WHERE clause in each of the derived tables to do what you require.
> John
> "Mange" wrote:
>|||Many Thanks John you are a star.
I'm sorry about my bad english and my poor knowledge about SQL.
Thanks again it works know.
//Magnus
"John Bell" wrote:
> If you actually want to change the data try:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> UPDATE O
> SET DisplayOrder = N.DisplayOrder
> FROM MyDisplays O
> JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.uppe
r
> AND N.id = @.lower )
> John
> "Mange" wrote:
>
Really tough ADO Stored Procedure Question. Please Help!
Hi
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
Ian
Ian,
One thing you can try is session SETtings. Session settings overrides database options, and some API's
executes a number of SET commands when they initialize the connection. (Yes, this essentially makes the
corresponding database options useless.) I suggest you go through the SET settings and try adding the ones you
can imagine might affect this, in the beginning of your proc code. Note that one setting is handled
differently from the others: ANSI_NULLS. This has to be set at *creation time* of the procedure, from the
connection where you create the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <ian@.NoWhere.com> wrote in message news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
>
>
>
|||Hi Tibor
Ok i think that i have tried every combination of setting i can come up
with. Still not working
Here is the SP if you want to have a look.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
-- Name:
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
-- Version: 001
-- History: Created on 11/08/2004
-- Author: Ian Killoran
-- Version Date Modifier Description
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O9vnWWdhEHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian,
> One thing you can try is session SETtings. Session settings overrides
database options, and some API's
> executes a number of SET commands when they initialize the connection.
(Yes, this essentially makes the
> corresponding database options useless.) I suggest you go through the SET
settings and try adding the ones you
> can imagine might affect this, in the beginning of your proc code. Note
that one setting is handled
> differently from the others: ANSI_NULLS. This has to be set at *creation
time* of the procedure, from the
> connection where you create the proc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ian" <ian@.NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
glance it[vbcol=seagreen]
data is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no[vbcol=seagreen]
challenge.
>
|||Check to be sure you are connecting to the database via VB using the SAME
LOGON id as what you connected via Sql Analyzer. If your logon/permissions
are different, you could be hitting an error related to permissions and not
know it. The best way to find out if that is happening is to use the
Connection Objectss "Errors CVollection" in your VB. I didnt see any
mention of that, so here's a snippet of code to help you with that if you
need...
function Get_Child_Recs_Tools (msg, key)
'
' This proc gets a row count from the
' T_EQP_Tools table where fab_id = the key
' of fab_id of the T_EQP_Tools row that is
' about to be deleted.
' Any row count greater than 0 results in failure.
on error resume next
Get_Child_Recs_Tools = "NotOk"
tbl_primkey = cint(key)
sql_get = " Select Count(*) counter from TBLFAB where fabname = "
sql_get = sql_get & tbl_primkey
set cn = Server.CreateObject("ADODB.Connection")
cn.open str_Connect
set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql_get, cn, adOpenForwardOnly
rs.movefirst
txt_rowcount = rs("counter")
if cn.Errors.Count > 0 then
Rtnmsg = ""
For each Error in cn.errors
Rtnmsg = Rtnmsg & "Error # : " & Error.Number & ", "
Rtnmsg = Rtnmsg & "Error Description : " & Error.Description
Next
cn.Errors.Clear
else
if txt_rowcount = 0 then
Get_Child_Recs_Tools = "Ok"
end if
end if
end function
P.S. I snipped this from an ASP application, so you'll have to cut out the
"Server." keyword from the ADO Object references and maybe make a few other
touchups....
g'Luck
"Ian" <ian@.NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in
#Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and
inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance
it
> seems to work fine in the fact that there is no errors and when the data
is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was
data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from
Query
> Analyser and no errors are being thorn by the stored procedure. I have
also
> added Error handling just after the UPDATE statement and there is no
error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still
no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
>
>
>
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
Ian
Ian,
One thing you can try is session SETtings. Session settings overrides database options, and some API's
executes a number of SET commands when they initialize the connection. (Yes, this essentially makes the
corresponding database options useless.) I suggest you go through the SET settings and try adding the ones you
can imagine might affect this, in the beginning of your proc code. Note that one setting is handled
differently from the others: ANSI_NULLS. This has to be set at *creation time* of the procedure, from the
connection where you create the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <ian@.NoWhere.com> wrote in message news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
>
>
>
|||Hi Tibor
Ok i think that i have tried every combination of setting i can come up
with. Still not working
Here is the SP if you want to have a look.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
-- Name:
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
-- Version: 001
-- History: Created on 11/08/2004
-- Author: Ian Killoran
-- Version Date Modifier Description
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O9vnWWdhEHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian,
> One thing you can try is session SETtings. Session settings overrides
database options, and some API's
> executes a number of SET commands when they initialize the connection.
(Yes, this essentially makes the
> corresponding database options useless.) I suggest you go through the SET
settings and try adding the ones you
> can imagine might affect this, in the beginning of your proc code. Note
that one setting is handled
> differently from the others: ANSI_NULLS. This has to be set at *creation
time* of the procedure, from the
> connection where you create the proc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ian" <ian@.NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
glance it[vbcol=seagreen]
data is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no[vbcol=seagreen]
challenge.
>
|||Check to be sure you are connecting to the database via VB using the SAME
LOGON id as what you connected via Sql Analyzer. If your logon/permissions
are different, you could be hitting an error related to permissions and not
know it. The best way to find out if that is happening is to use the
Connection Objectss "Errors CVollection" in your VB. I didnt see any
mention of that, so here's a snippet of code to help you with that if you
need...
function Get_Child_Recs_Tools (msg, key)
'
' This proc gets a row count from the
' T_EQP_Tools table where fab_id = the key
' of fab_id of the T_EQP_Tools row that is
' about to be deleted.
' Any row count greater than 0 results in failure.
on error resume next
Get_Child_Recs_Tools = "NotOk"
tbl_primkey = cint(key)
sql_get = " Select Count(*) counter from TBLFAB where fabname = "
sql_get = sql_get & tbl_primkey
set cn = Server.CreateObject("ADODB.Connection")
cn.open str_Connect
set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql_get, cn, adOpenForwardOnly
rs.movefirst
txt_rowcount = rs("counter")
if cn.Errors.Count > 0 then
Rtnmsg = ""
For each Error in cn.errors
Rtnmsg = Rtnmsg & "Error # : " & Error.Number & ", "
Rtnmsg = Rtnmsg & "Error Description : " & Error.Description
Next
cn.Errors.Clear
else
if txt_rowcount = 0 then
Get_Child_Recs_Tools = "Ok"
end if
end if
end function
P.S. I snipped this from an ASP application, so you'll have to cut out the
"Server." keyword from the ADO Object references and maybe make a few other
touchups....
g'Luck
"Ian" <ian@.NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in
#Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and
inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance
it
> seems to work fine in the fact that there is no errors and when the data
is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was
data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from
Query
> Analyser and no errors are being thorn by the stored procedure. I have
also
> added Error handling just after the UPDATE statement and there is no
error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still
no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
>
>
>
Really tough ADO Stored Procedure Question. Please Help!
Hi
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
Ian
Is SET NOCOUNT ON the first line in the sproc?
--Mary
On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
>basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
>tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
>that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
>is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
>setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
|||Hi Mary
Yeah it is. Here is the Stored Procedure.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
-- Name:
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
-- Version: 001
-- History: Created on 11/08/2004
-- Author: Ian Killoran
-- Version Date Modifier Description
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:cvc9i0ttjvimrfsgbil9ec5smev8t71mod@.4ax.com... [vbcol=seagreen]
> Is SET NOCOUNT ON the first line in the sproc?
> --Mary
> On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
it[vbcol=seagreen]
is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no
>
|||Hi,
I'm more PowerBuilder and/or ADO .Net than straight ADO, but I have 1
possible cause for you.
Is you ADO DB Connection / VB6 application starting a transaction ?
Because, if you have start 2 transaction, effectively nesting them, then you
will need to issue 2 Commits.
Now, if the SQL Server is configured for implicit transactions, it might be
that when you close the connection, ADO automatically rolls back the open
transaction. And the SET before the CREATE of the Stored Procedure will not
have any effect on the caller, because they would have started the
transaction before calling the SP.
To check out what's going on, use SQL Profiler. There is a SQLTransaction
event and DTCTransaction event. You will need the Event Sub Class column to
see what type of action is carried out (begin, rollback, commit). The lookup
is in BOL.
Al
"Ian" wrote:
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
Ian
Is SET NOCOUNT ON the first line in the sproc?
--Mary
On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
>basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
>tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
>that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
>is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
>setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
|||Hi Mary
Yeah it is. Here is the Stored Procedure.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
-- Name:
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
-- Version: 001
-- History: Created on 11/08/2004
-- Author: Ian Killoran
-- Version Date Modifier Description
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:cvc9i0ttjvimrfsgbil9ec5smev8t71mod@.4ax.com... [vbcol=seagreen]
> Is SET NOCOUNT ON the first line in the sproc?
> --Mary
> On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
it[vbcol=seagreen]
is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no
>
|||Hi,
I'm more PowerBuilder and/or ADO .Net than straight ADO, but I have 1
possible cause for you.
Is you ADO DB Connection / VB6 application starting a transaction ?
Because, if you have start 2 transaction, effectively nesting them, then you
will need to issue 2 Commits.
Now, if the SQL Server is configured for implicit transactions, it might be
that when you close the connection, ADO automatically rolls back the open
transaction. And the SET before the CREATE of the Stored Procedure will not
have any effect on the caller, because they would have started the
transaction before calling the SP.
To check out what's going on, use SQL Profiler. There is a SQLTransaction
event and DTCTransaction event. You will need the Event Sub Class column to
see what type of action is carried out (begin, rollback, commit). The lookup
is in BOL.
Al
"Ian" wrote:
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
Really tough ADO Stored Procedure Question. Please Help!
Hi
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
IanIs SET NOCOUNT ON the first line in the sproc?
--Mary
On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
>basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
>tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
>that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2
.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance i
t
> seems to work fine in the fact that there is no errors and when the data i
s
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
>is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have als
o
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
>setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>|||Hi Mary
Yeah it is. Here is the Stored Procedure.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
---
-- Name:
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import
--
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
--
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
--
-- Version: 001
-- History: Created on 11/08/2004
--
-- Author: Ian Killoran
--
-- Version Date Modifier Description
--
--
--
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1
,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:cvc9i0ttjvimrfsgbil9ec5smev8t71mod@.
4ax.com...
> Is SET NOCOUNT ON the first line in the sproc?
> --Mary
> On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
it[vbcol=seagreen]
is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no[vbcol=seagreen]
>|||Hi,
I'm more PowerBuilder and/or ADO .Net than straight ADO, but I have 1
possible cause for you.
Is you ADO DB Connection / VB6 application starting a transaction ?
Because, if you have start 2 transaction, effectively nesting them, then you
will need to issue 2 Commits.
Now, if the SQL Server is configured for implicit transactions, it might be
that when you close the connection, ADO automatically rolls back the open
transaction. And the SET before the CREATE of the Stored Procedure will not
have any effect on the caller, because they would have started the
transaction before calling the SP.
To check out what's going on, use SQL Profiler. There is a SQLTransaction
event and DTCTransaction event. You will need the Event Sub Class column to
see what type of action is carried out (begin, rollback, commit). The lookup
is in BOL.
Al
"Ian" wrote:
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp
2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserte
d
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance
it
> seems to work fine in the fact that there is no errors and when the data
is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was dat
a
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Quer
y
> Analyser and no errors are being thorn by the stored procedure. I have al
so
> added Error handling just after the UPDATE statement and there is no erro
r
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still n
o
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
IanIs SET NOCOUNT ON the first line in the sproc?
--Mary
On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
>basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
>tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
>that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2
.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance i
t
> seems to work fine in the fact that there is no errors and when the data i
s
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
>is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have als
o
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
>setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>|||Hi Mary
Yeah it is. Here is the Stored Procedure.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
---
-- Name:
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import
--
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
--
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
--
-- Version: 001
-- History: Created on 11/08/2004
--
-- Author: Ian Killoran
--
-- Version Date Modifier Description
--
--
--
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1
,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:cvc9i0ttjvimrfsgbil9ec5smev8t71mod@.
4ax.com...
> Is SET NOCOUNT ON the first line in the sproc?
> --Mary
> On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
it[vbcol=seagreen]
is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no[vbcol=seagreen]
>|||Hi,
I'm more PowerBuilder and/or ADO .Net than straight ADO, but I have 1
possible cause for you.
Is you ADO DB Connection / VB6 application starting a transaction ?
Because, if you have start 2 transaction, effectively nesting them, then you
will need to issue 2 Commits.
Now, if the SQL Server is configured for implicit transactions, it might be
that when you close the connection, ADO automatically rolls back the open
transaction. And the SET before the CREATE of the Stored Procedure will not
have any effect on the caller, because they would have started the
transaction before calling the SP.
To check out what's going on, use SQL Profiler. There is a SQLTransaction
event and DTCTransaction event. You will need the Event Sub Class column to
see what type of action is carried out (begin, rollback, commit). The lookup
is in BOL.
Al
"Ian" wrote:
> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp
2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserte
d
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance
it
> seems to work fine in the fact that there is no errors and when the data
is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was dat
a
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Quer
y
> Analyser and no errors are being thorn by the stored procedure. I have al
so
> added Error handling just after the UPDATE statement and there is no erro
r
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still n
o
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>
Subscribe to:
Posts (Atom)