Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts

Tuesday, March 27, 2012

@@identity

hi,

I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:


SELECT
@.OrderID = @.@.Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
OrderID,
ProductID,
Quantity,
UnitCost
)
SELECT
@.OrderID,
ShoppingCart.ProductID,
ShoppingCart.Quantity,
Prices.UnitCost
FROM
ShoppingCart INNER JOIN
Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
CartID = @.CartID

is there any way to rewrite this statement so that I can put it in the form insert()values(). ?is OrderID an autonumbering field?

if so - you shouldn't include it in your INSERT statement|||YEs it is ! Why do you not recommend putting it in the insert stetment ?

Anyways, I realized that my error wasn't really in the stored procedure, but in the function calling it.

Thanks.|||SQL will put the identity into the table automatically, what you're trying to do is over-write the value with the same number again...hence the duplicate key problem. So like the poster said, don't do it, get the help out and read what an identity column acutally is and how to use it.|||I'm not really overwriting the @.@.identity value, but assigning it to a variable. I then use the value of that variable in an insert statement on a totally separate table.
I solved my primary key issue and it wasn't related to the sql transaction i posted here, ....there was a logic error in my code.

So now even though its all working, you still think that the sql statement I'm using is incorrect ?|||Sorry I admit I was assuming the code that went before your example. The only suggest I would make would be to consider using SCOPE_IDENTITY rather than IDENTIY, tends to be safer in the long run.sql

@@error in vb6

Hi everyone!
How can I read the value of @.@.error from within vb6 source? Or how can I check the success of a transaction (comit/rollback) from within vb6 frontend source?
Thank you!I believe you will have to pass it back as an output parameter from your stored procedure. That's how I do it anyway.|||To capture @.@.error you can either store its value into an output parameter, or return it in the form of a result set.

To trap an execution error and notify your FE of a specific section of BE code that encountered the error use RAISERROR. In your VB code precede the call to SQL with ON ERROR (either RESUME NEXT and check for error right below, or GOTO <LABEL> and do the error trapping there).

EDITED: At the end make sure to turn error-fall-through OFF by issueing ON ERROR GOTO 0. Failure to do so often yields hours of debugging.|||ON ERROR GOTO 0. Failure to do so often yields hours of debugging.

Couldn't agree more. That's why I don't use it. Way too many sleepless nights trying to figure out why the stupid app isn't giving me the results I expect.

On Error Goto BeerFridge

Now that works :)

Saturday, February 25, 2012

.Net <-> SqlServer admin actions

Hi,
Is it possible in a programmatic way (.Net) to tell the SqlServer 2000 to do
the following things?
1.. Backup the transaction protocol
2.. Restore a database backup
3.. Run the actions from the transaction protocol
This scenario is useful for me to get the database state in case of an error
(application error and not SQL or hardware error) so that I can reproduce
and correct the error.
Any answer could help me a lot.
Sincerely,
George.Hi
Yes, there are T-SQL commands as BACKUP LOG ,RESTORE DATABASE ,BACKUP
DATABASE for more details please refer to the BOL. Personally , I prefer
calling a stored procedure from .NET for instance that does such jobs .

> 3.. Run the actions from the transaction protocol
What do you mean?
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:OpyWjCtOGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is it possible in a programmatic way (.Net) to tell the SqlServer 2000 to
> do the following things?
>
> 1.. Backup the transaction protocol
> 2.. Restore a database backup
> 3.. Run the actions from the transaction protocol
>
> This scenario is useful for me to get the database state in case of an
> error (application error and not SQL or hardware error) so that I can
> reproduce and correct the error.
>
> Any answer could help me a lot.
>
> Sincerely,
> George.
>|||Hi,
With point 3 I wanted to say that after restoring the DB I have to restore
the transaction protocol in order to get the db state for which the error
occurred.
George.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMGDSFtOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi
> Yes, there are T-SQL commands as BACKUP LOG ,RESTORE DATABASE ,BACKUP
> DATABASE for more details please refer to the BOL. Personally , I prefer
> calling a stored procedure from .NET for instance that does such jobs .
>
> What do you mean?
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:OpyWjCtOGHA.312@.TK2MSFTNGP12.phx.gbl...
>|||Hi
Ok, yes you can do that as well. There is an option of RESTORE LOG
DataBaseName FROM Disk
WITH RECOVERY, STOPAT = 'Jul 1, 2005 10:00 AM'
Agaim , much more details you can find in the BOL
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:O2u3xatOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> With point 3 I wanted to say that after restoring the DB I have to restore
> the transaction protocol in order to get the db state for which the error
> occurred.
>
> George.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMGDSFtOGHA.4052@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Uri,
Your answer helps me a lot. I will try to implement these things.
George.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ua9GzgtOGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi
> Ok, yes you can do that as well. There is an option of RESTORE LOG
> DataBaseName FROM Disk
> WITH RECOVERY, STOPAT = 'Jul 1, 2005 10:00 AM'
> Agaim , much more details you can find in the BOL
>
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:O2u3xatOGHA.1088@.tk2msftngp13.phx.gbl...
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
--
> ---- --
--
> ----
--
> ----
--
> ----
--
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
..ldf -> transaction log file
..mdf -> "master" data file
..ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
---- --
----
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)
Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
..ldf -> transaction log file
..mdf -> "master" data file
..ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
---- --
----
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)
|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

Sunday, February 19, 2012

...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
Corey Young
Hi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/default...b;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:

> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and B.
> When I execute the following query from within SQL Server Management Studio
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
> 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young

...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B
.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
Corey YoungHi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/defaul...kb;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:

> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and
B.
> When I execute the following query from within SQL Server Management Studi
o
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
> 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young

...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn = 'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
--
Corey YoungHi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:
> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and B.
> When I execute the following query from within SQL Server Management Studio
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn => 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young

.......A floating point exception occurred in the user process.........

I'm getting this error:
System.Data.SqlClient.SqlException: A floating point exception occurred in the user process. Current transaction is canceled. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at ...etc etc
when updating one particular table, but not when updating another using nearly the same code.
I've searched and searched on the inet, but with no joy.
Microsoft have several different manifestations of it, under variousdifferent circumstances, but the solutions all seem to involveinstalling SP4, which by the looks of it is a massive procedure thatyou have to go to university to be competent to do.
In any case, according to Enterprise Manager, I already have SP4included. On the other hand, the one file of mine I checked the date ofagainst MS's hotfix file list was 2002 not 2003, so who knows?
My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).
According to the info on sqlDataAdaptor.update, thesqlError that gets returned as part of the sqlException containsfields Class, which gives the seriousness of the error, and State,which identifies the exact error. I get Class=16 (user-fixable)and State=1, which we have to go to SQL Books Online to discover themeaning of. SQL BO probably sounded like a good idea to W.Gates,but it took me half an hour to get anywhere near finding out what that'1' meant. When I did, it was (guess): 'A floating pointexception occurred in the user process. Current transaction iscanceled.' !!!
Can anyone please tell me what exactly might be causing the error?
Graham Rounce

What is your create table statement like is float one of your data types? Another thing most T-SQL quantitative functions are in float but we usually don't use float as data type for rounding problems. Post your stored proc and create table statement and I may be able to help you.|||That would be very terrific. Actually, the update command was made by Visual Studio and looks like this:
'SqlUpdateCommand2
'
Me.SqlUpdateCommand2.CommandText= "UPDATE prices SET price_type = @.price_type, price_code_number =@.price_code_numbe" & _
"r, price_code = @.price_code, price_description = @.price_description, price_rrp =" & _
" @.price_rrp, price_icibuysatxpercentdiscount = @.price_icibuysatxpercentdiscount," & _
" price_icibuysatx = @.price_icibuysatx, price_markupper = @.price_markupper, price" & _
"_absmargin = @.price_absmargin, price_up_icibuysatxpercentdiscount = @.price_up_rh" & _
"lbuysatxpercentdiscount, price_up_sellatxdiscounttofullprice = @.price_up_sellatx" & _
"discounttofullprice, priceoff_rrp = @.priceoff_rrp, priceoff_percofnewbussellingp" & _
"rice = @.priceoff_percofnewbussellingprice, priceoff_icibuysatxpercentdiscount = " & _
"@.priceoff_icibuysatxpercentdiscount, priceoff_icibuysatx = @.priceoff_icibuysatx," & _
" priceoff_markupper = @.priceoff_markupper, priceoff_absmargin = @.priceoff_absmar" & _
"gin, pricehot_rrp = @.pricehot_rrp, pricehot_percofnewbussellingprice = @.pricehot" & _
"_percofnewbussellingprice, pricehot_icibuysatxpercentdiscount = @.pricehot_icibuy" & _
"satxpercentdiscount, pricehot_icibuysatx = @.pricehot_icibuysatx, pricehot_markup" & _
"per = @.pricehot_markupper, pricehot_absmargin = @.pricehot_absmargin, price828_rr" & _
"p = @.price828_rrp, price828_percofnewbussellingprice = @.price828_percofnewbussel" & _
"lingprice, price828_icibuysatxpercentdiscount = @.price828_icibuysatxpercentdisco" & _
"unt, price828_icibuysatx = @.price828_icibuysatx, price828_markupper = @.price828_" & _
"markupper, price828_absmargin = @.price828_absmargin, price_installtime = @.price_" & _
"installtime, price_quotetext = @.price_quotetext, price_defaultnote_1 = @.price_de" & _
"faultnote_1, price_defaultnote_2 = @.price_defaultnote_2, priceoff_calcrrp = @.pri" & _
"ceoff_calcrrp, pricehot_calcrrp = @.pricehot_calcrrp, price828_calcrrp = @.price82" & _
"8_calcrrp, priceoff_calccost = @.priceoff_calccost, pricehot_calccost = @.pricehot" & _
"_calccost, price828_calccost = @.price828_calccost, priceoff_calcsellprice = @.pri" & _
"ceoff_calcsellprice, pricehot_calcsellprice = @.pricehot_calcsellprice, price828_" & _
"calcsellprice = @.price828_calcsellprice, priceoff_calcmargin = @.priceoff_calcmar" & _
"gin, pricehot_calcmargin = @.pricehot_calcmargin, price828_calcmargin = @.price828" & _
"_calcmargin, priceoff_calcmarginper = @.priceoff_calcmarginper, pricehot_calcmarg" & _
"inper = @.pricehot_calcmarginper, price828_calcmarginper = @.price828_calcmarginpe" & _
"r, price_calccost = @.price_calccost, price_calcsellprice = @.price_calcsellprice," & _
" price_calcmargin = @.price_calcmargin, price_calcmarginper = @.price_calcmarginpe" & _
"r, price_up_calccost = @.price_up_calccost, price_up_calcsellprice = @.price_up_ca" & _
"lcsellprice, price_up_calcmargin = @.price_up_calcmargin, price_up_calcmarginper " & _
"= @.price_up_calcmarginper, price_hw_mxcode = @.price_hw_mxcode, price_order = @.pr" & _
"ice_order, price_mxmandatory = @.price_mxmandatory, price_variable = @.price_varia" & _
"ble, price_notes = @.price_notes WHERE (price_ID = @.Original_price_ID) AND (price" & _
"828_absmargin = @.Original_price828_absmargin OR @.Original_price828_absmargin IS " & _
"NULL AND price828_absmargin IS NULL) AND (price828_calccost = @.Original_price828" & _
"_calccost OR @.Original_price828_calccost IS NULL AND price828_calccost IS NULL) " & _
"AND (price828_calcmargin = @.Original_price828_calcmargin OR @.Original_price828_c" & _
"alcmargin IS NULL AND price828_calcmargin IS NULL) AND (price828_calcmarginper =" & _
" @.Original_price828_calcmarginper OR @.Original_price828_calcmarginper IS NULL AN" & _
"D price828_calcmarginper IS NULL) AND (price828_calcrrp = @.Original_price828_cal" & _
"crrp OR @.Original_price828_calcrrp IS NULL AND price828_calcrrp IS NULL) AND (pr" & _
"ice828_calcsellprice = @.Original_price828_calcsellprice OR @.Original_price828_ca" & _
"lcsellprice IS NULL AND price828_calcsellprice IS NULL) AND (price828_markupper " & _
"= @.Original_price828_markupper OR @.Original_price828_markupper IS NULL AND price" & _
"828_markupper IS NULL) AND (price828_percofnewbussellingprice = @.Original_price8" & _
"28_percofnewbussellingprice OR @.Original_price828_percofnewbussellingprice IS NU" & _
"LL AND price828_percofnewbussellingprice IS NULL) AND (price828_icibuysatx = @.Or" & _
"iginal_price828_icibuysatx OR @.Original_price828_icibuysatx IS NULL AND price828" & _
"_icibuysatx IS NULL) AND (price828_icibuysatxpercentdiscount = @.Original_price82" & _
"8_icibuysatxpercentdiscount OR @.Original_price828_icibuysatxpercentdiscount IS N" & _
"ULL AND price828_icibuysatxpercentdiscount IS NULL) AND (price828_rrp = @.Origina" & _
"l_price828_rrp OR @.Original_price828_rrp IS NULL AND price828_rrp IS NULL) AND (" & _
"price_absmargin = @.Original_price_absmargin OR @.Original_price_absmargin IS NULL" & _
" AND price_absmargin IS NULL) AND (price_calccost = @.Original_price_calccost OR " & _
"@.Original_price_calccost IS NULL AND price_calccost IS NULL) AND (price_calcmarg" & _
"in = @.Original_price_calcmargin OR @.Original_price_calcmargin IS NULL AND price_" & _
"calcmargin IS NULL) AND (price_calcmarginper = @.Original_price_calcmarginper OR " & _
"@.Original_price_calcmarginper IS NULL AND price_calcmarginper IS NULL) AND (pric" & _
"e_calcsellprice = @.Original_price_calcsellprice OR @.Original_price_calcsellprice" & _
" IS NULL AND price_calcsellprice IS NULL) AND (price_code = @.Original_price_code" & _
" OR @.Original_price_code IS NULL AND price_code IS NULL) AND (price_code_number " & _
"= @.Original_price_code_number OR @.Original_price_code_number IS NULL AND price_c" & _
"ode_number IS NULL) AND (price_defaultnote_1 = @.Original_price_defaultnote_1 OR " & _
"@.Original_price_defaultnote_1 IS NULL AND price_defaultnote_1 IS NULL) AND (pric" & _
"e_defaultnote_2 = @.Original_price_defaultnote_2 OR @.Original_price_defaultnote_2" & _
" IS NULL AND price_defaultnote_2 IS NULL) AND (price_description = @.Original_pri" & _
"ce_description OR @.Original_price_description IS NULL AND price_description IS N" & _
"ULL) AND (price_hw_mxcode = @.Original_price_hw_mxcode OR @.Original_price_hw_mxco" & _
"de IS NULL AND price_hw_mxcode IS NULL) AND (price_installtime = @.Original_price" & _
"_installtime OR @.Original_price_installtime IS NULL AND price_installtime IS NUL" & _
"L) AND (price_markupper = @.Original_price_markupper OR @.Original_price_markupper" & _
" IS NULL AND price_markupper IS NULL) AND (price_mxmandatory = @.Original_price_m" & _
"xmandatory) AND (price_notes = @.Original_price_notes OR @.Original_price_notes IS" & _
" NULL AND price_notes IS NULL) AND (price_order = @.Original_price_order OR @.Orig" & _
"inal_price_order IS NULL AND price_order IS NULL) AND (price_icibuysatx = @.Origi" & _
"nal_price_icibuysatx OR @.Original_price_icibuysatx IS NULL AND price_icibuysatx " & _
"IS NULL) AND (price_icibuysatxpercentdiscount = @.Original_price_icibuysatxpercen" & _
"tdiscount OR @.Original_price_icibuysatxpercentdiscount IS NULL AND price_icibuys" & _
"atxpercentdiscount IS NULL) AND (price_rrp = @.Original_price_rrp OR @.Original_pr" & _
"ice_rrp IS NULL AND price_rrp IS NULL) AND (price_type = @.Original_price_type OR" & _
" @.Original_price_type IS NULL AND price_type IS NULL) AND (price_up_calccost = @." & _
"Original_price_up_calccost OR @.Original_price_up_calccost IS NULL AND price_up_c" & _
"alccost IS NULL) AND (price_up_calcmargin = @.Original_price_up_calcmargin OR @.Or" & _
"iginal_price_up_calcmargin IS NULL AND price_up_calcmargin IS NULL) AND (price_u" & _
"p_calcmarginper = @.Original_price_up_calcmarginper OR @.Original_price_up_calcmar" & _
"ginper IS NULL AND price_up_calcmarginper IS NULL) AND (price_up_calcsellprice =" & _
" @.Original_price_up_calcsellprice OR @.Original_price_up_calcsellprice IS NULL AN" & _
"D price_up_calcsellprice IS NULL) AND (price_up_icibuysatxpercentdiscount = @.Ori" & _
"ginal_price_up_icibuysatxpercentdiscount OR @.Original_price_up_icibuysatxpercent" & _
"discount IS NULL AND price_up_icibuysatxpercentdiscount IS NULL) AND (price_up_s" & _
"ellatxdiscounttofullprice = @.Original_price_up_sellatxdiscounttofullprice OR @.Or" & _
"iginal_price_up_sellatxdiscounttofullprice IS NULL AND price_up_sellatxdiscountt" & _
"ofullprice IS NULL) AND (price_variable = @.Original_price_variable) AND (priceho" & _
"t_absmargin = @.Original_pricehot_absmargin OR @.Original_pricehot_absmargin IS NU" & _
"LL AND pricehot_absmargin IS NULL) AND (pricehot_calccost = @.Original_pricehot_c" & _
"alccost OR @.Original_pricehot_calccost IS NULL AND pricehot_calccost IS NULL) AN" & _
"D (pricehot_calcmargin = @.Original_pricehot_calcmargin OR @.Original_pricehot_cal" & _
"cmargin IS NULL AND pricehot_calcmargin IS NULL) AND (pricehot_calcmarginper = @." & _
"Original_pricehot_calcmarginper OR @.Original_pricehot_calcmarginper IS NULL AND " & _
"pricehot_calcmarginper IS NULL) AND (pricehot_calcrrp = @.Original_pricehot_calcr" & _
"rp OR @.Original_pricehot_calcrrp IS NULL AND pricehot_calcrrp IS NULL) AND (pric" & _
"ehot_calcsellprice = @.Original_pricehot_calcsellprice OR @.Original_pricehot_calc" & _
"sellprice IS NULL AND pricehot_calcsellprice IS NULL) AND (pricehot_markupper = " & _
"@.Original_pricehot_markupper OR @.Original_pricehot_markupper IS NULL AND priceho" & _
"t_markupper IS NULL) AND (pricehot_percofnewbussellingprice = @.Original_pricehot" & _
"_percofnewbussellingprice OR @.Original_pricehot_percofnewbussellingprice IS NULL" & _
" AND pricehot_percofnewbussellingprice IS NULL) AND (pricehot_icibuysatx = @.Orig" & _
"inal_pricehot_icibuysatx OR @.Original_pricehot_icibuysatx IS NULL AND pricehot_r" & _
"hlbuysatx IS NULL) AND (pricehot_icibuysatxpercentdiscount = @.Original_pricehot_" & _
"icibuysatxpercentdiscount OR @.Original_pricehot_icibuysatxpercentdiscount IS NUL" & _
"L AND pricehot_icibuysatxpercentdiscount IS NULL) AND (pricehot_rrp = @.Original_" & _
"pricehot_rrp OR @.Original_pricehot_rrp IS NULL AND pricehot_rrp IS NULL) AND (pr" & _
"iceoff_absmargin = @.Original_priceoff_absmargin OR @.Original_priceoff_absmargin " & _
"IS NULL AND priceoff_absmargin IS NULL) AND (priceoff_calccost = @.Original_price" & _
"off_calccost OR @.Original_priceoff_calccost IS NULL AND priceoff_calccost IS NUL" & _
"L) AND (priceoff_calcmargin = @.Original_priceoff_calcmargin OR @.Original_priceof" & _
"f_calcmargin IS NULL AND priceoff_calcmargin IS NULL) AND (priceoff_calcmarginpe" & _
"r = @.Original_priceoff_calcmarginper OR @.Original_priceoff_calcmarginper IS NULL" & _
" AND priceoff_calcmarginper IS NULL) AND (priceoff_calcrrp = @.Original_priceoff_" & _
"calcrrp OR @.Original_priceoff_calcrrp IS NULL AND priceoff_calcrrp IS NULL) AND " & _
"(priceoff_calcsellprice = @.Original_priceoff_calcsellprice OR @.Original_priceoff" & _
"_calcsellprice IS NULL AND priceoff_calcsellprice IS NULL) AND (priceoff_markupp" & _
"er = @.Original_priceoff_markupper OR @.Original_priceoff_markupper IS NULL AND pr" & _
"iceoff_markupper IS NULL) AND (priceoff_percofnewbussellingprice = @.Original_pri" & _
"ceoff_percofnewbussellingprice OR @.Original_priceoff_percofnewbussellingprice IS" & _
" NULL AND priceoff_percofnewbussellingprice IS NULL) AND (priceoff_icibuysatx = " & _
"@.Original_priceoff_icibuysatx OR @.Original_priceoff_icibuysatx IS NULL AND price" & _
"off_icibuysatx IS NULL) AND (priceoff_icibuysatxpercentdiscount = @.Original_pric" & _
"eoff_icibuysatxpercentdiscount OR @.Original_priceoff_icibuysatxpercentdiscount I" & _
"S NULL AND priceoff_icibuysatxpercentdiscount IS NULL) AND (priceoff_rrp = @.Orig" & _
"inal_priceoff_rrp OR @.Original_priceoff_rrp IS NULL AND priceoff_rrp IS NULL); S" & _
"ELECT price_ID, price_type, price_code_number, price_code, price_description, pr" & _
"ice_rrp, price_icibuysatxpercentdiscount, price_icibuysatx, price_markupper, pri" & _
"ce_absmargin, price_up_icibuysatxpercentdiscount, price_up_sellatxdiscounttofull" & _
"price, priceoff_rrp, priceoff_percofnewbussellingprice, priceoff_icibuysatxperce" & _
"ntdiscount, priceoff_icibuysatx, priceoff_markupper, priceoff_absmargin, priceho" & _
"t_rrp, pricehot_percofnewbussellingprice, pricehot_icibuysatxpercentdiscount, pr" & _
"icehot_icibuysatx, pricehot_markupper, pricehot_absmargin, price828_rrp, price82" & _
"8_percofnewbussellingprice, price828_icibuysatxpercentdiscount, price828_icibuys" & _
"atx, price828_markupper, price828_absmargin, price_installtime, price_quotetext," & _
" price_defaultnote_1, price_defaultnote_2, priceoff_calcrrp, pricehot_calcrrp, p" & _
"rice828_calcrrp, priceoff_calccost, pricehot_calccost, price828_calccost, priceo" & _
"ff_calcsellprice, pricehot_calcsellprice, price828_calcsellprice, priceoff_calcm" & _
"argin, pricehot_calcmargin, price828_calcmargin, priceoff_calcmarginper, priceho" & _
"t_calcmarginper, price828_calcmarginper, price_calccost, price_calcsellprice, pr" & _
"ice_calcmargin, price_calcmarginper, price_up_calccost, price_up_calcsellprice, " & _
"price_up_calcmargin, price_up_calcmarginper, price_hw_mxcode, price_order, price" & _
"_mxmandatory, price_variable, price_notes FROM prices WHERE (price_ID = @.price_I" & _
"D) ORDER BY price_description"
Pretty lengthy?*!$!
The Create statement I don't have, as the database was converted fromAccess. Other tables from it work ok, though, after they had theprimary key put back in. The data types are a mixture ofmoney & real - some of those were empty, and I did tryputting 0's in them prior to the update in case it was that, but Ithought it shouldn't really be necessary and it had no effect. Also, there are some ints and nvarchars and bits, and one each ofvarchar and ntext.
Is that enough? It would be, as I said, extremely terrific if you could help.
Thanks,
Graham Rounce
|||

There is little difference between Float and Real in SQL Server because the synonym for Real is Float. Try to change that to Decimal or Numeric and I have problem with your ISNULL because it replaces NULL values with 0. Try the links below to see how to deal with NULL values and see if you can use a CASE statement. One more thing COUNT (*) is the only aggregate function in SQL Server that calculates NULL values but you don't have to use ISNULL all th time. Post again if the problem continues and the SQL Server BOL (books online) is the best in the business but you will do better with Boolean searches. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.akadia.com/services/dealing_with_null_values.html

|||

Hiagain, and thanks for the reply. Having slept on it, I thought I'd tryreducing the size of the query by not including variouscolumns when configuring the DataAdaptor in Visual Studio. Iun-included half of them, and it worked fine. Then Iprogressively re-included blocks of columns until it stopped workingagain. Below is the column list. The ones marked '?'are the ones which, when included, cause the error to happen (and when re-un-included stop it happening again!).
They don't look very different to other columns in the table, and I'mthinking it was just the sheer size of the generated query thatdefeated the server.
Unless you disagree, this looks like a bug? Of course, onewouldn't expect to be able to use infinite-sized tables, but I'd expect a relevant warning, not an inexplicable error?

Thanks,

Graham Rounce

int 4 included
nvarchar 50 included
int 4 included
nvarchar 50 included
nvarchar 255 included
money 8 included
real 4 included
money 8 included
real 4 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
real 4 included
ntext 16 included
nvarchar 50 included
nvarchar 50 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
real 4 ?
real 4 ?
real 4 ?
money 8 ?
money 8 ?
money 8 ?
real 4 not included
money 8 not included
money 8 not included
money 8 not included
real 4 not included
nvarchar 20 included
int 4 included
bit 1 included
bit 1 included
varchar 8000 included

|||It is not the size of your query but change all the Real and Money to Decimal or Numeric. The numeric data in the Real column is over flowing, and I know there are existing issues with money rounding to less than a dollar going back to 1999. In Decimal and Numeric remember to set the precission and scale. Post again and be patient. Hope this helps.|||Hi again, and thanks. Just one more q if you don'tmind. Can I just change those types in 'Design Table' in theEnterprise Manager? I mean, it looks like I can, but will thecontents still be ok? I'm using the table at the momentwithout about a third of the columns as they didn't really need to bethere anyway, but it seems like it would be good practice to sort itout.
Can I just go in and change the types? It can't really be as easy as that? (Smiley thing)
Graham Rounce
|||I don't mind at all just open the BOL (books online) search pane and enter ALTER table, the first answer covered all you need. Hope this helps.|||It looks like Alter Table does everything except make the tea! (Andthat could be in there somewhere, too). I'm quite happy to changethe Types in Enterprise Manager, as it's only a one-offconversion. Is that likely to cause any problems, as long as Ichange the prog to suit?
Thanks again,

|||I don't think it will cause any problem and I am glad to see you are close to a solution. Hope this helps.|||Yes indeed. Thanks very much.