I've got a dynamic SQL query that is generated inside a SP, and is run using
the
EXEC (@.SQL)
or
EXEC sp_executesql @.SQL
This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.
All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data i
s
return '?' instead of unicode charachters.
This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!
Cheers
TrisTris (Tris@.discussions.microsoft.com) writes:
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.
As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.
I like to stress that this is all guessworks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@.microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
> Cheers
> Tris|||Hi, thanks for the responses.
Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.
Cheers
T
Showing posts with label generated. Show all posts
Showing posts with label generated. Show all posts
Thursday, March 29, 2012
Monday, March 12, 2012
Excessive locking
I inherited a hell hole of a system.
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders
would
it be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIA
Looks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders would
> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>
|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.
> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick. I am going to deal with one primay key
that is used in the invoicing run above. It's got a small impact for users
who could be affected.
> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders would
>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>
|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen
|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
>news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com.. .
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders
wouldit be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIA
Looks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders
would> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>
|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.
> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick
. I am going to deal with one primay keythat is used in the invoicing run above. It's got a small impact for users
who could be affected.
> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders
would>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>
|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen
|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
>news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com.. .
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>
Excessive locking
I inherited a hell hole of a system.
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders :) would
it be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIALooks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders :) would
> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.
> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick:). I am going to deal with one primay key
that is used in the invoicing run above. It's got a small impact for users
who could be affected.
> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>>I inherited a hell hole of a system.
>> This is how a key is generated in my current system:
>> --GET A NEW ORDERID
>> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>> (TABLOCKX))
>> --UPDATE LAST ORDERID TABLE
>> UPDATE LAST_ORDERID SET OrderID = @.OrderID
>> SET @.ErrCount = @.ErrCount + @.@.Error
>> --END UPDATE LAST_ORDERID
>>
>> Last_orderid has one column / one row, and is pretty useless IMO.
>> Past the idea of creating an identity column on Orderid in Orders :)
>> would it be good practice to put this in a begin transaction, commit
>> transaction to reduce the lock time?
>> TIA
>>
>>
>>
>>
>>
>|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders :) would
>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
>news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com...
>> Put it in a transaction to assure correct behavior, it won't save any
>> time, though.
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders :) would
it be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIALooks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders :) would
> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.
> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick:). I am going to deal with one primay key
that is used in the invoicing run above. It's got a small impact for users
who could be affected.
> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>>I inherited a hell hole of a system.
>> This is how a key is generated in my current system:
>> --GET A NEW ORDERID
>> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>> (TABLOCKX))
>> --UPDATE LAST ORDERID TABLE
>> UPDATE LAST_ORDERID SET OrderID = @.OrderID
>> SET @.ErrCount = @.ErrCount + @.@.Error
>> --END UPDATE LAST_ORDERID
>>
>> Last_orderid has one column / one row, and is pretty useless IMO.
>> Past the idea of creating an identity column on Orderid in Orders :)
>> would it be good practice to put this in a begin transaction, commit
>> transaction to reduce the lock time?
>> TIA
>>
>>
>>
>>
>>
>|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders :) would
>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
>news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.4ax.com...
>> Put it in a transaction to assure correct behavior, it won't save any
>> time, though.
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>
Excessive locking
I inherited a hell hole of a system.
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders would
it be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIALooks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders would
> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.
> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick. I am going to deal with one primay key
that is used in the invoicing run above. It's got a small impact for users
who could be affected.
> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders would
>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.
4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
> news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.
4ax.com...
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders
wouldit be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIALooks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders
would> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.
> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick
. I am going to deal with one primay keythat is used in the invoicing run above. It's got a small impact for users
who could be affected.
> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders
would>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.
4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
> news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.
4ax.com...
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>
Friday, March 9, 2012
EXCEPTION_FLT_UNDERFLOW
Hi,
I'm getting the message 'SqlDumpExceptionHandler: Process 12 generated fatal
exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL Server is terminating this
process.' and my connection is terminated.
what causes this error and how is it fixed.
using sql2k with sp3.
thanks.> Hi,
> I'm getting the message 'SqlDumpExceptionHandler: Process 12
> generated fatal exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL
> Server is terminating this process.' and my connection is terminated.
> what causes this error and how is it fixed.
> using sql2k with sp3.
Try checking the table with DBCC CHECKTABLE. DBCC DBREINDEX sometimes fixes
the problem. If it does not, it looks like a MS PSS case for me
(http://support.microsoft.com/defaul...estion.asp&SD=G
N&FR=0)
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks Sebastian,
I've been messing with the data and it appears that earlier versions of MSSQ
L were not strict with what you entered into a float column - you could over
load the datatype.
thanks for your help though.
I'm getting the message 'SqlDumpExceptionHandler: Process 12 generated fatal
exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL Server is terminating this
process.' and my connection is terminated.
what causes this error and how is it fixed.
using sql2k with sp3.
thanks.> Hi,
> I'm getting the message 'SqlDumpExceptionHandler: Process 12
> generated fatal exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL
> Server is terminating this process.' and my connection is terminated.
> what causes this error and how is it fixed.
> using sql2k with sp3.
Try checking the table with DBCC CHECKTABLE. DBCC DBREINDEX sometimes fixes
the problem. If it does not, it looks like a MS PSS case for me
(http://support.microsoft.com/defaul...estion.asp&SD=G
N&FR=0)
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks Sebastian,
I've been messing with the data and it appears that earlier versions of MSSQ
L were not strict with what you entered into a float column - you could over
load the datatype.
thanks for your help though.
EXCEPTION_ACCESS_VIOLATION While Excecuting Select Query.
Dear Freinds,
While Excecuting select Query in my client place ,the following error is coming.
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
When i got same database , i try restore in my pc and run the same...query its excecuting properly..with out giving any error..i tried lot of things ..nothing is working out..i am not able to find the root cause itself..why the exception is coming..
I attaching the Error log with this post.
Note: Check For the Keyword 'EXCEPTION_ACCESS_VIOLATION' in the Error Log File.
Any body can help me out in sorting this issue...then :beer: other wise i will be:eek:
With Reg,
Sathesh.Mapply service pack 3a or service pack 4 of Sql server 2000
While Excecuting select Query in my client place ,the following error is coming.
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
When i got same database , i try restore in my pc and run the same...query its excecuting properly..with out giving any error..i tried lot of things ..nothing is working out..i am not able to find the root cause itself..why the exception is coming..
I attaching the Error log with this post.
Note: Check For the Keyword 'EXCEPTION_ACCESS_VIOLATION' in the Error Log File.
Any body can help me out in sorting this issue...then :beer: other wise i will be:eek:
With Reg,
Sathesh.Mapply service pack 3a or service pack 4 of Sql server 2000
Wednesday, March 7, 2012
EXCEPTION_ACCESS_VIOLATION
Hi,
I am getting the following error when I am trying to
execute a Query through an ASP page.
SqlDumpExceptionHandler: Process 54 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
If somebody knows anything about this, please let me
know.
Thanx,
InderMake sure you have applied all of the current Service Packs for SQL
Server... AVs are generally bugs in the product... If you can't find
anything recent on MS web site (KB article, etc) you may wish to open a call
to PSS.
"Inder" <inderpal@.expresscolour.com> wrote in message
news:06f701c37117$416a0700$a301280a@.phx.gbl...
> Hi,
> I am getting the following error when I am trying to
> execute a Query through an ASP page.
> SqlDumpExceptionHandler: Process 54 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> If somebody knows anything about this, please let me
> know.
> Thanx,
> Inder
I am getting the following error when I am trying to
execute a Query through an ASP page.
SqlDumpExceptionHandler: Process 54 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
If somebody knows anything about this, please let me
know.
Thanx,
InderMake sure you have applied all of the current Service Packs for SQL
Server... AVs are generally bugs in the product... If you can't find
anything recent on MS web site (KB article, etc) you may wish to open a call
to PSS.
"Inder" <inderpal@.expresscolour.com> wrote in message
news:06f701c37117$416a0700$a301280a@.phx.gbl...
> Hi,
> I am getting the following error when I am trying to
> execute a Query through an ASP page.
> SqlDumpExceptionHandler: Process 54 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> If somebody knows anything about this, please let me
> know.
> Thanx,
> Inder
Exception thrown: database file cannot be found
Hi,
I'm developing a desktop C# app that uses SQL Everywhere as an embedded database.
I generated strongly typed DataSet and use that to populate a DataGrid on my app.
When the app first loads, it populates the DataGrid with a line like this:
I'm developing a desktop C# app that uses SQL Everywhere as an embedded database.
I generated strongly typed DataSet and use that to populate a DataGrid on my app.
When the app first loads, it populates the DataGrid with a line like this:
this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);
That all works fine. Later on, after adding more data to the database (through reading a csv file), I wanted to refresh the display on the DataGrid.
I used the same line of code:
this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);
however, this time, the following exception was thrown:
The database file cannot be found. Check the path to the database. [ File name = .\\Inventories.sdf ]
Does anyone know what may be going on? I saw this article about a bug in VS 2005 when using strongly typed DataSets (http://channel9.msdn.com/wiki/default.aspx/MobileDeveloper.DatabaseCannotBeFoundErrorInTypedDataset)
but that doesn't seem to apply here.
The connection string is identical both times that line of code is called so I'm a bit baffled with what's going on.
Any help would be appreciated. Thanks,
Jose
Windows CE does not support relative paths you're trying to use. You must specify correct absolute path to the database located on device file system.
Also keep in mind Windows CE does not have drive letters and can't see your desktop's C: (D:, etc) drive as many developers seem to believe.
|||I'm not running this on Windows CE. I'm trying out the new SQL Server Everywhere and using it on Windows XP.Thanks though.|||
If file can not be found that's probably because it can't be found. You can use File.Exists() to verify that.
|||Thanks for the input!!It sounds so simple and yet I hadn't thought about that. I kept looking for the complicated answer.
It turns out, my database file was there all along. The problem was, after opening and reading the CSV files to import into the db, the next time I tried to access the db the app was looking for the db file in the same directory where my CSVs where...and of course, it wasn't finding it.
So now, after reading a CSV and prior to re-querying the db, I use Directory.SetCurrentDirectory() to reset where the app looks for its db.
Thanks for the help, I had been stumped by this for a week.
-Jose|||
this one has had me scratching my head for a day so I'm glad I haven't spent a week...
I'm using a separate project as a class library with a *.sdf database so it can be re-used for several projects.
I had the exact problem, everything was good until I opened OpenFileDialog(), then things went south after that.
Just a note to your solution... (maybe you do this already)...but you can assign the OpenFileDialog.RestoreDirectory flag to true and then after it closes the original directory will be restored prior to the open dialog....that way you don't need the Directory.SetCurrentDirectory.
fileChooser = new OpenFileDialog();
fileChooser.RestoreDirectory = true;
gl
Exception thrown: database file cannot be found
Hi,
I'm developing a desktop C# app that uses SQL Everywhere as an embedded database.
I generated strongly typed DataSet and use that to populate a DataGrid on my app.
When the app first loads, it populates the DataGrid with a line like this:
I'm developing a desktop C# app that uses SQL Everywhere as an embedded database.
I generated strongly typed DataSet and use that to populate a DataGrid on my app.
When the app first loads, it populates the DataGrid with a line like this:
this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);
That all works fine. Later on, after adding more data to the database (through reading a csv file), I wanted to refresh the display on the DataGrid.
I used the same line of code:
this.sTORE_INV_LNTableAdapter.Fill(this.inventoriesDataSet.STORE_INV_LN);
however, this time, the following exception was thrown:
The database file cannot be found. Check the path to the database. [ File name = .\\Inventories.sdf ]
Does anyone know what may be going on? I saw this article about a bug in VS 2005 when using strongly typed DataSets (http://channel9.msdn.com/wiki/default.aspx/MobileDeveloper.DatabaseCannotBeFoundErrorInTypedDataset)
but that doesn't seem to apply here.
The connection string is identical both times that line of code is called so I'm a bit baffled with what's going on.
Any help would be appreciated. Thanks,
Jose
Windows CE does not support relative paths you're trying to use. You must specify correct absolute path to the database located on device file system.
Also keep in mind Windows CE does not have drive letters and can't see your desktop's C: (D:, etc) drive as many developers seem to believe.
|||I'm not running this on Windows CE. I'm trying out the new SQL Server Everywhere and using it on Windows XP.Thanks though.
|||
If file can not be found that's probably because it can't be found. You can use File.Exists() to verify that.
|||Thanks for the input!!It sounds so simple and yet I hadn't thought about that. I kept looking for the complicated answer.
It turns out, my database file was there all along. The problem was, after opening and reading the CSV files to import into the db, the next time I tried to access the db the app was looking for the db file in the same directory where my CSVs where...and of course, it wasn't finding it.
So now, after reading a CSV and prior to re-querying the db, I use Directory.SetCurrentDirectory() to reset where the app looks for its db.
Thanks for the help, I had been stumped by this for a week.
-Jose
|||
this one has had me scratching my head for a day so I'm glad I haven't spent a week...
I'm using a separate project as a class library with a *.sdf database so it can be re-used for several projects.
I had the exact problem, everything was good until I opened OpenFileDialog(), then things went south after that.
Just a note to your solution... (maybe you do this already)...but you can assign the OpenFileDialog.RestoreDirectory flag to true and then after it closes the original directory will be restored prior to the open dialog....that way you don't need the Directory.SetCurrentDirectory.
fileChooser = new OpenFileDialog();
fileChooser.RestoreDirectory = true;
gl
Sunday, February 26, 2012
Exception Access Violation
We are running SQL Server 2000, SP 3, and today have begun receiving the
following error: "Process 88 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
The error is occurring about every hour and appears to occur when an hourly
job is run to backup the transaction log for a db. The transaction log is
backed up and then the error appears to occur when SQL Server tries to write
a text report following the transaction log backup, the steps are part of a
database maintenance plan. This is the first time we've ever had a problem
with these jobs and they've been running for about 4 years now and no changes
have been made to the jobs.
Any help is appreciated. Thanks
In this case, you may have to call SQL Server support. SQL Server
has choked on something.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
write
> a text report following the transaction log backup, the steps are part of
a
> database maintenance plan. This is the first time we've ever had a
problem
> with these jobs and they've been running for about 4 years now and no
changes
> have been made to the jobs.
> Any help is appreciated. Thanks
|||While you should probably call PSS, the first thing they will ask is if you
are up to date on SPs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
> hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
> write
> a text report following the transaction log backup, the steps are part of
> a
> database maintenance plan. This is the first time we've ever had a
> problem
> with these jobs and they've been running for about 4 years now and no
> changes
> have been made to the jobs.
> Any help is appreciated. Thanks
following error: "Process 88 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
The error is occurring about every hour and appears to occur when an hourly
job is run to backup the transaction log for a db. The transaction log is
backed up and then the error appears to occur when SQL Server tries to write
a text report following the transaction log backup, the steps are part of a
database maintenance plan. This is the first time we've ever had a problem
with these jobs and they've been running for about 4 years now and no changes
have been made to the jobs.
Any help is appreciated. Thanks
In this case, you may have to call SQL Server support. SQL Server
has choked on something.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
write
> a text report following the transaction log backup, the steps are part of
a
> database maintenance plan. This is the first time we've ever had a
problem
> with these jobs and they've been running for about 4 years now and no
changes
> have been made to the jobs.
> Any help is appreciated. Thanks
|||While you should probably call PSS, the first thing they will ask is if you
are up to date on SPs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
> hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
> write
> a text report following the transaction log backup, the steps are part of
> a
> database maintenance plan. This is the first time we've ever had a
> problem
> with these jobs and they've been running for about 4 years now and no
> changes
> have been made to the jobs.
> Any help is appreciated. Thanks
Exception Access Violation
We are running SQL Server 2000, SP 3, and today have begun receiving the
following error: "Process 88 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
The error is occurring about every hour and appears to occur when an hourly
job is run to backup the transaction log for a db. The transaction log is
backed up and then the error appears to occur when SQL Server tries to write
a text report following the transaction log backup, the steps are part of a
database maintenance plan. This is the first time we've ever had a problem
with these jobs and they've been running for about 4 years now and no change
s
have been made to the jobs.
Any help is appreciated. ThanksIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
write
> a text report following the transaction log backup, the steps are part of
a
> database maintenance plan. This is the first time we've ever had a
problem
> with these jobs and they've been running for about 4 years now and no
changes
> have been made to the jobs.
> Any help is appreciated. Thanks|||While you should probably call PSS, the first thing they will ask is if you
are up to date on SPs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
> hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
> write
> a text report following the transaction log backup, the steps are part of
> a
> database maintenance plan. This is the first time we've ever had a
> problem
> with these jobs and they've been running for about 4 years now and no
> changes
> have been made to the jobs.
> Any help is appreciated. Thanks
following error: "Process 88 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
The error is occurring about every hour and appears to occur when an hourly
job is run to backup the transaction log for a db. The transaction log is
backed up and then the error appears to occur when SQL Server tries to write
a text report following the transaction log backup, the steps are part of a
database maintenance plan. This is the first time we've ever had a problem
with these jobs and they've been running for about 4 years now and no change
s
have been made to the jobs.
Any help is appreciated. ThanksIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
write
> a text report following the transaction log backup, the steps are part of
a
> database maintenance plan. This is the first time we've ever had a
problem
> with these jobs and they've been running for about 4 years now and no
changes
> have been made to the jobs.
> Any help is appreciated. Thanks|||While you should probably call PSS, the first thing they will ask is if you
are up to date on SPs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
> hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
> write
> a text report following the transaction log backup, the steps are part of
> a
> database maintenance plan. This is the first time we've ever had a
> problem
> with these jobs and they've been running for about 4 years now and no
> changes
> have been made to the jobs.
> Any help is appreciated. Thanks
Exception Access Violation
We are running SQL Server 2000, SP 3, and today have begun receiving the
following error: "Process 88 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
The error is occurring about every hour and appears to occur when an hourly
job is run to backup the transaction log for a db. The transaction log is
backed up and then the error appears to occur when SQL Server tries to write
a text report following the transaction log backup, the steps are part of a
database maintenance plan. This is the first time we've ever had a problem
with these jobs and they've been running for about 4 years now and no changes
have been made to the jobs.
Any help is appreciated. ThanksIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
write
> a text report following the transaction log backup, the steps are part of
a
> database maintenance plan. This is the first time we've ever had a
problem
> with these jobs and they've been running for about 4 years now and no
changes
> have been made to the jobs.
> Any help is appreciated. Thanks|||While you should probably call PSS, the first thing they will ask is if you
are up to date on SPs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
> hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
> write
> a text report following the transaction log backup, the steps are part of
> a
> database maintenance plan. This is the first time we've ever had a
> problem
> with these jobs and they've been running for about 4 years now and no
> changes
> have been made to the jobs.
> Any help is appreciated. Thanks
following error: "Process 88 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
The error is occurring about every hour and appears to occur when an hourly
job is run to backup the transaction log for a db. The transaction log is
backed up and then the error appears to occur when SQL Server tries to write
a text report following the transaction log backup, the steps are part of a
database maintenance plan. This is the first time we've ever had a problem
with these jobs and they've been running for about 4 years now and no changes
have been made to the jobs.
Any help is appreciated. ThanksIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
write
> a text report following the transaction log backup, the steps are part of
a
> database maintenance plan. This is the first time we've ever had a
problem
> with these jobs and they've been running for about 4 years now and no
changes
> have been made to the jobs.
> Any help is appreciated. Thanks|||While you should probably call PSS, the first thing they will ask is if you
are up to date on SPs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F7A1A30D-A8DD-41F7-A5B9-A3F162F6D7E8@.microsoft.com...
> We are running SQL Server 2000, SP 3, and today have begun receiving the
> following error: "Process 88 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> The error is occurring about every hour and appears to occur when an
> hourly
> job is run to backup the transaction log for a db. The transaction log is
> backed up and then the error appears to occur when SQL Server tries to
> write
> a text report following the transaction log backup, the steps are part of
> a
> database maintenance plan. This is the first time we've ever had a
> problem
> with these jobs and they've been running for about 4 years now and no
> changes
> have been made to the jobs.
> Any help is appreciated. Thanks
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION at 0x00402484
my sql server give me a error log,who can help me?
2007-07-06 09:58:46.09 spid56 SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
*******************************************************************************
*
* BEGIN STACK DUMP:
* 07/06/07 09:58:46 spid 56
*
* Exception Address = 00402484
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 199A0F64
* Input Buffer 190 bytes -
* delete from cms_paramdb1..Param where PatientiID = 427 and Occurtime =
* '2007-06-12 17:51:39'
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF 0071a000
* ntdll 7C920000 7C9B3FFF 00094000
* kernel32 7C800000 7C91BFFF 0011c000
* ADVAPI32 77DA0000 77E48FFF 000a9000
* RPCRT4 77E50000 77EE0FFF 00091000
* USER32 77D10000 77D9EFFF 0008f000
* GDI32 77EF0000 77F35FFF 00046000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BE0000 77C37FFF 00058000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 00300000 00310FFF 00011000
* ShimEng 5CC30000 5CC55FFF 00026000
* AcSpecfc 71540000 71580FFF 00041000
* ole32 76990000 76ACBFFF 0013c000
* SHELL32 773A0000 77B90FFF 007f1000
* SHLWAPI 77F40000 77FB5FFF 00076000
* WINMM 76B10000 76B39FFF 0002a000
* DDRAW 736D0000 73718FFF 00049000
* DCIMAN32 73B30000 73B35FFF 00006000
* USERENV 759D0000 75A7DFFF 000ae000
* MPR 71A90000 71AA1FFF 00012000
* PSAPI 76BC0000 76BCAFFF 0000b000
* comdlg32 76320000 76366FFF 00047000
* COMCTL32 5D170000 5D206FFF 00097000
* IMM32 76300000 7631CFFF 0001d000
* WS2_32 71A20000 71A36FFF 00017000
* WS2HELP 71A10000 71A17FFF 00008000
* LPK 62C20000 62C28FFF 00009000
* USP10 73FA0000 7400AFFF 0006b000
* comctl32 77180000 77281FFF 00102000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 5FDD0000 5FE23FFF 00054000
* wmi 76D00000 76D03FFF 00004000
* SSNETLIB 42CF0000 42D05FFF 00016000
* WSOCK32 71A40000 71A4AFFF 0000b000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 71F00000 71F03FFF 00004000
* SECUR32 77FC0000 77FD0FFF 00011000
* crypt32 765E0000 76671FFF 00092000
* MSASN1 76DB0000 76DC1FFF 00012000
* VERSION 77BD0000 77BD7FFF 00008000
* SSmsLPCn 42CD0000 42CD6FFF 00007000
* ntdsapi 76770000 76782FFF 00013000
* DNSAPI 76EF0000 76F16FFF 00027000
* WLDAP32 76F30000 76F5BFFF 0002c000
* sqlimage 4A400000 4A40CFFF 0000d000
* DBGHELP 020F0000 02102FFF 00013000
*
* Edi: 19991FFC: 16290F65 00000000 00000000 00000000 00000000 00000000
* Esi: 01F6D244: 19990F65 0000FFFF 00000000 00000000 01F6D2D8 00000000
* Eax: 19990F65: FFFFFFFF FFFFFFFF 75405AFF 8C969852 03DB62E5 FFFFFFFF
* Ebx: 19990000: 00000301 00FF0000 00000000 00000000 00000000 00060000
* Ecx: 0000FFFF:
* Edx: 00000026:
* Eip: 00402484: 0114B70F 83145689 EAC107C2 0A4C8D03 184E8902 752000F6
* Ebp: 01F6D264: 01F6D298 00576A9E 00000001 01F6D310 01F6D308 01F6D2D8
* SegCs: 0000001B:
* EFlags: 00010202: 0053005C 00730079 00650074 0033006D 005C0032 00620057
* Esp: 01F6D234: 01F6D244 0040289C 01F6D2D8 00408FEA 19990F65 0000FFFF
* SegSs: 00000023:
*******************************************************************************
-
Short Stack Dump
00402484 Module(sqlservr+00002484)
00576A9E Module(sqlservr+00176A9E) (SQLExit(unsigned long)+00021C8C)
0057620B Module(sqlservr+0017620B) (SQLExit(unsigned long)+000213F9)
005021AC Module(sqlservr+001021AC)
0041ED96 Module(sqlservr+0001ED96)
00442DD0 Module(sqlservr+00042DD0)
004BFD41 Module(sqlservr+000BFD41)
00427985 Module(sqlservr+00027985)
004271BA Module(sqlservr+000271BA)
0042EA36 Module(sqlservr+0002EA36)
0042E82D Module(sqlservr+0002E82D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
005A683F Module(sqlservr+001A683F) (SQLExit(unsigned long)+00051A2D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
00459A54 Module(sqlservr+00059A54)
004175D8 Module(sqlservr+000175D8)
410735D0 Module(UMS+000035D0) (UmsSystemUserContext::UmsSystemUserContext(class UmsScheduler *,struct UMS_SYSPARAMS *)+00000434)
4107382C Module(UMS+0000382C) (UmsScheduler::FiberEnabled(void)+000001D2)
77C0A243 Module(MSVCRT+0002A243) (_endthread+000000AF)
7C80B50B Module(kernel32+0000B50B) (GetModuleFileNameA+000001B4)
-
2007-07-06 09:58:46.26 spid56 Error: 0, Severity: 19, State: 0
2007-07-06 09:58:46.26 spid56 language_exec: Process 56 generated an access violation. SQL Server is terminating this process..
2007-07-06 09:58:50.28 spid57 Error: 7105, Severity: 22, State: 6
2007-07-06 09:58:50.28 spid57 Page (1:3568), slot 0 for text, ntext, or image node does not exist..
Hi,
which version of SQL Server are you running ?
Did you try to run DBCC CHECKDB already ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
This is probably a bug in SQL Server. Please contact Microsoft product support or provide feedback at http://connect.microsoft.com/sql. When providing feedback upload one of your errorlogs with the error, and any corresponding dump files (*.mdmp*) files that might be present in your log directory.
Thanks,
Fabricio.
|||Hmmmm. This was reported nearly a month ago. No responses other than 'contact support'.
And yet we're seeing the same thing....and wasting days talking to someone in Bangalore.
Is this the new Microsoft? Hello?
|||Have you contacted Microsoft support regading this issue? They are usually pretty quick with resolutions and you would not be wasting your time. If you have done so, can you please provide me with your case # and I'll make sure this issue gets resolved (please send it to fvoznika at microsoft.com).
Thanks,
Fabricio.
|||I've just started getting this EXCEPTION_ACCESS_VIOLATION (0xc0000005) on machines using Windows 2000 sp4 connecting to SQLServer. This is crashing JVMs (multiple Sun versions and BEA also) in the Java VM thread (outside our code). This has just started recently - perhaps with the last set of patches? Has anyone else seen this or know what I could do to get more information? Could this be related to updates to named pipes?Thanks!
-Brian Temple
|||Brian,
The problem you're describing is a crash in the client application connection to SQL Server, and not in SQL Server itself. You can try to post your question in SQL Server Data Access forum (sibling of this one) if you believe SQL Server JDBC is at fault or follow up with Sun as to why JVM is crashing.
Thanks,
Fabricio.
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION at 0x00402484
my sql server give me a error log,who can help me?
2007-07-06 09:58:46.09 spid56 SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
*******************************************************************************
*
* BEGIN STACK DUMP:
* 07/06/07 09:58:46 spid 56
*
* Exception Address = 00402484
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 199A0F64
* Input Buffer 190 bytes -
* delete from cms_paramdb1..Param where PatientiID = 427 and Occurtime =
* '2007-06-12 17:51:39'
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF 0071a000
* ntdll 7C920000 7C9B3FFF 00094000
* kernel32 7C800000 7C91BFFF 0011c000
* ADVAPI32 77DA0000 77E48FFF 000a9000
* RPCRT4 77E50000 77EE0FFF 00091000
* USER32 77D10000 77D9EFFF 0008f000
* GDI32 77EF0000 77F35FFF 00046000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BE0000 77C37FFF 00058000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 00300000 00310FFF 00011000
* ShimEng 5CC30000 5CC55FFF 00026000
* AcSpecfc 71540000 71580FFF 00041000
* ole32 76990000 76ACBFFF 0013c000
* SHELL32 773A0000 77B90FFF 007f1000
* SHLWAPI 77F40000 77FB5FFF 00076000
* WINMM 76B10000 76B39FFF 0002a000
* DDRAW 736D0000 73718FFF 00049000
* DCIMAN32 73B30000 73B35FFF 00006000
* USERENV 759D0000 75A7DFFF 000ae000
* MPR 71A90000 71AA1FFF 00012000
* PSAPI 76BC0000 76BCAFFF 0000b000
* comdlg32 76320000 76366FFF 00047000
* COMCTL32 5D170000 5D206FFF 00097000
* IMM32 76300000 7631CFFF 0001d000
* WS2_32 71A20000 71A36FFF 00017000
* WS2HELP 71A10000 71A17FFF 00008000
* LPK 62C20000 62C28FFF 00009000
* USP10 73FA0000 7400AFFF 0006b000
* comctl32 77180000 77281FFF 00102000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 5FDD0000 5FE23FFF 00054000
* wmi 76D00000 76D03FFF 00004000
* SSNETLIB 42CF0000 42D05FFF 00016000
* WSOCK32 71A40000 71A4AFFF 0000b000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 71F00000 71F03FFF 00004000
* SECUR32 77FC0000 77FD0FFF 00011000
* crypt32 765E0000 76671FFF 00092000
* MSASN1 76DB0000 76DC1FFF 00012000
* VERSION 77BD0000 77BD7FFF 00008000
* SSmsLPCn 42CD0000 42CD6FFF 00007000
* ntdsapi 76770000 76782FFF 00013000
* DNSAPI 76EF0000 76F16FFF 00027000
* WLDAP32 76F30000 76F5BFFF 0002c000
* sqlimage 4A400000 4A40CFFF 0000d000
* DBGHELP 020F0000 02102FFF 00013000
*
* Edi: 19991FFC: 16290F65 00000000 00000000 00000000 00000000 00000000
* Esi: 01F6D244: 19990F65 0000FFFF 00000000 00000000 01F6D2D8 00000000
* Eax: 19990F65: FFFFFFFF FFFFFFFF 75405AFF 8C969852 03DB62E5 FFFFFFFF
* Ebx: 19990000: 00000301 00FF0000 00000000 00000000 00000000 00060000
* Ecx: 0000FFFF:
* Edx: 00000026:
* Eip: 00402484: 0114B70F 83145689 EAC107C2 0A4C8D03 184E8902 752000F6
* Ebp: 01F6D264: 01F6D298 00576A9E 00000001 01F6D310 01F6D308 01F6D2D8
* SegCs: 0000001B:
* EFlags: 00010202: 0053005C 00730079 00650074 0033006D 005C0032 00620057
* Esp: 01F6D234: 01F6D244 0040289C 01F6D2D8 00408FEA 19990F65 0000FFFF
* SegSs: 00000023:
*******************************************************************************
-
Short Stack Dump
00402484 Module(sqlservr+00002484)
00576A9E Module(sqlservr+00176A9E) (SQLExit(unsigned long)+00021C8C)
0057620B Module(sqlservr+0017620B) (SQLExit(unsigned long)+000213F9)
005021AC Module(sqlservr+001021AC)
0041ED96 Module(sqlservr+0001ED96)
00442DD0 Module(sqlservr+00042DD0)
004BFD41 Module(sqlservr+000BFD41)
00427985 Module(sqlservr+00027985)
004271BA Module(sqlservr+000271BA)
0042EA36 Module(sqlservr+0002EA36)
0042E82D Module(sqlservr+0002E82D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
005A683F Module(sqlservr+001A683F) (SQLExit(unsigned long)+00051A2D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
00459A54 Module(sqlservr+00059A54)
004175D8 Module(sqlservr+000175D8)
410735D0 Module(UMS+000035D0) (UmsSystemUserContext::UmsSystemUserContext(class UmsScheduler *,struct UMS_SYSPARAMS *)+00000434)
4107382C Module(UMS+0000382C) (UmsScheduler::FiberEnabled(void)+000001D2)
77C0A243 Module(MSVCRT+0002A243) (_endthread+000000AF)
7C80B50B Module(kernel32+0000B50B) (GetModuleFileNameA+000001B4)
-
2007-07-06 09:58:46.26 spid56 Error: 0, Severity: 19, State: 0
2007-07-06 09:58:46.26 spid56 language_exec: Process 56 generated an access violation. SQL Server is terminating this process..
2007-07-06 09:58:50.28 spid57 Error: 7105, Severity: 22, State: 6
2007-07-06 09:58:50.28 spid57 Page (1:3568), slot 0 for text, ntext, or image node does not exist..
Hi,
which version of SQL Server are you running ?
Did you try to run DBCC CHECKDB already ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
This is probably a bug in SQL Server. Please contact Microsoft product support or provide feedback at http://connect.microsoft.com/sql. When providing feedback upload one of your errorlogs with the error, and any corresponding dump files (*.mdmp*) files that might be present in your log directory.
Thanks,
Fabricio.
|||Hmmmm. This was reported nearly a month ago. No responses other than 'contact support'.
And yet we're seeing the same thing....and wasting days talking to someone in Bangalore.
Is this the new Microsoft? Hello?
|||Have you contacted Microsoft support regading this issue? They are usually pretty quick with resolutions and you would not be wasting your time. If you have done so, can you please provide me with your case # and I'll make sure this issue gets resolved (please send it to fvoznika at microsoft.com).
Thanks,
Fabricio.
|||I've just started getting this EXCEPTION_ACCESS_VIOLATION (0xc0000005) on machines using Windows 2000 sp4 connecting to SQLServer. This is crashing JVMs (multiple Sun versions and BEA also) in the Java VM thread (outside our code). This has just started recently - perhaps with the last set of patches? Has anyone else seen this or know what I could do to get more information? Could this be related to updates to named pipes?Thanks!
-Brian Temple
|||Brian,
The problem you're describing is a crash in the client application connection to SQL Server, and not in SQL Server itself. You can try to post your question in SQL Server Data Access forum (sibling of this one) if you believe SQL Server JDBC is at fault or follow up with Sun as to why JVM is crashing.
Thanks,
Fabricio.
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION at 0x00402484
my sql server give me a error log,who can help me?
2007-07-06 09:58:46.09 spid56 SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
*******************************************************************************
*
* BEGIN STACK DUMP:
* 07/06/07 09:58:46 spid 56
*
* Exception Address = 00402484
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 199A0F64
* Input Buffer 190 bytes -
* delete from cms_paramdb1..Param where PatientiID = 427 and Occurtime =
* '2007-06-12 17:51:39'
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF 0071a000
* ntdll 7C920000 7C9B3FFF 00094000
* kernel32 7C800000 7C91BFFF 0011c000
* ADVAPI32 77DA0000 77E48FFF 000a9000
* RPCRT4 77E50000 77EE0FFF 00091000
* USER32 77D10000 77D9EFFF 0008f000
* GDI32 77EF0000 77F35FFF 00046000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BE0000 77C37FFF 00058000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 00300000 00310FFF 00011000
* ShimEng 5CC30000 5CC55FFF 00026000
* AcSpecfc 71540000 71580FFF 00041000
* ole32 76990000 76ACBFFF 0013c000
* SHELL32 773A0000 77B90FFF 007f1000
* SHLWAPI 77F40000 77FB5FFF 00076000
* WINMM 76B10000 76B39FFF 0002a000
* DDRAW 736D0000 73718FFF 00049000
* DCIMAN32 73B30000 73B35FFF 00006000
* USERENV 759D0000 75A7DFFF 000ae000
* MPR 71A90000 71AA1FFF 00012000
* PSAPI 76BC0000 76BCAFFF 0000b000
* comdlg32 76320000 76366FFF 00047000
* COMCTL32 5D170000 5D206FFF 00097000
* IMM32 76300000 7631CFFF 0001d000
* WS2_32 71A20000 71A36FFF 00017000
* WS2HELP 71A10000 71A17FFF 00008000
* LPK 62C20000 62C28FFF 00009000
* USP10 73FA0000 7400AFFF 0006b000
* comctl32 77180000 77281FFF 00102000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 5FDD0000 5FE23FFF 00054000
* wmi 76D00000 76D03FFF 00004000
* SSNETLIB 42CF0000 42D05FFF 00016000
* WSOCK32 71A40000 71A4AFFF 0000b000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 71F00000 71F03FFF 00004000
* SECUR32 77FC0000 77FD0FFF 00011000
* crypt32 765E0000 76671FFF 00092000
* MSASN1 76DB0000 76DC1FFF 00012000
* VERSION 77BD0000 77BD7FFF 00008000
* SSmsLPCn 42CD0000 42CD6FFF 00007000
* ntdsapi 76770000 76782FFF 00013000
* DNSAPI 76EF0000 76F16FFF 00027000
* WLDAP32 76F30000 76F5BFFF 0002c000
* sqlimage 4A400000 4A40CFFF 0000d000
* DBGHELP 020F0000 02102FFF 00013000
*
* Edi: 19991FFC: 16290F65 00000000 00000000 00000000 00000000 00000000
* Esi: 01F6D244: 19990F65 0000FFFF 00000000 00000000 01F6D2D8 00000000
* Eax: 19990F65: FFFFFFFF FFFFFFFF 75405AFF 8C969852 03DB62E5 FFFFFFFF
* Ebx: 19990000: 00000301 00FF0000 00000000 00000000 00000000 00060000
* Ecx: 0000FFFF:
* Edx: 00000026:
* Eip: 00402484: 0114B70F 83145689 EAC107C2 0A4C8D03 184E8902 752000F6
* Ebp: 01F6D264: 01F6D298 00576A9E 00000001 01F6D310 01F6D308 01F6D2D8
* SegCs: 0000001B:
* EFlags: 00010202: 0053005C 00730079 00650074 0033006D 005C0032 00620057
* Esp: 01F6D234: 01F6D244 0040289C 01F6D2D8 00408FEA 19990F65 0000FFFF
* SegSs: 00000023:
*******************************************************************************
-
Short Stack Dump
00402484 Module(sqlservr+00002484)
00576A9E Module(sqlservr+00176A9E) (SQLExit(unsigned long)+00021C8C)
0057620B Module(sqlservr+0017620B) (SQLExit(unsigned long)+000213F9)
005021AC Module(sqlservr+001021AC)
0041ED96 Module(sqlservr+0001ED96)
00442DD0 Module(sqlservr+00042DD0)
004BFD41 Module(sqlservr+000BFD41)
00427985 Module(sqlservr+00027985)
004271BA Module(sqlservr+000271BA)
0042EA36 Module(sqlservr+0002EA36)
0042E82D Module(sqlservr+0002E82D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
005A683F Module(sqlservr+001A683F) (SQLExit(unsigned long)+00051A2D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
00459A54 Module(sqlservr+00059A54)
004175D8 Module(sqlservr+000175D8)
410735D0 Module(UMS+000035D0) (UmsSystemUserContext::UmsSystemUserContext(class UmsScheduler *,struct UMS_SYSPARAMS *)+00000434)
4107382C Module(UMS+0000382C) (UmsScheduler::FiberEnabled(void)+000001D2)
77C0A243 Module(MSVCRT+0002A243) (_endthread+000000AF)
7C80B50B Module(kernel32+0000B50B) (GetModuleFileNameA+000001B4)
-
2007-07-06 09:58:46.26 spid56 Error: 0, Severity: 19, State: 0
2007-07-06 09:58:46.26 spid56 language_exec: Process 56 generated an access violation. SQL Server is terminating this process..
2007-07-06 09:58:50.28 spid57 Error: 7105, Severity: 22, State: 6
2007-07-06 09:58:50.28 spid57 Page (1:3568), slot 0 for text, ntext, or image node does not exist..
Hi,
which version of SQL Server are you running ?
Did you try to run DBCC CHECKDB already ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
This is probably a bug in SQL Server. Please contact Microsoft product support or provide feedback at http://connect.microsoft.com/sql. When providing feedback upload one of your errorlogs with the error, and any corresponding dump files (*.mdmp*) files that might be present in your log directory.
Thanks,
Fabricio.
|||Hmmmm. This was reported nearly a month ago. No responses other than 'contact support'.
And yet we're seeing the same thing....and wasting days talking to someone in Bangalore.
Is this the new Microsoft? Hello?
|||
Have you contacted Microsoft support regading this issue? They are usually pretty quick with resolutions and you would not be wasting your time. If you have done so, can you please provide me with your case # and I'll make sure this issue gets resolved (please send it to fvoznika at microsoft.com).
Thanks,
Fabricio.
|||I've just started getting this EXCEPTION_ACCESS_VIOLATION (0xc0000005) on machines using Windows 2000 sp4 connecting to SQLServer. This is crashing JVMs (multiple Sun versions and BEA also) in the Java VM thread (outside our code). This has just started recently - perhaps with the last set of patches? Has anyone else seen this or know what I could do to get more information? Could this be related to updates to named pipes?Thanks!
-Brian Temple
|||Brian,
The problem you're describing is a crash in the client application connection to SQL Server, and not in SQL Server itself. You can try to post your question in SQL Server Data Access forum (sibling of this one) if you believe SQL Server JDBC is at fault or follow up with Sun as to why JVM is crashing.
Thanks,
Fabricio.
Friday, February 24, 2012
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION at 0x00402484
my sql server give me a error log,who can help me?
2007-07-06 09:58:46.09 spid56 SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
*******************************************************************************
*
* BEGIN STACK DUMP:
* 07/06/07 09:58:46 spid 56
*
* Exception Address = 00402484
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 199A0F64
* Input Buffer 190 bytes -
* delete from cms_paramdb1..Param where PatientiID = 427 and Occurtime =
* '2007-06-12 17:51:39'
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF 0071a000
* ntdll 7C920000 7C9B3FFF 00094000
* kernel32 7C800000 7C91BFFF 0011c000
* ADVAPI32 77DA0000 77E48FFF 000a9000
* RPCRT4 77E50000 77EE0FFF 00091000
* USER32 77D10000 77D9EFFF 0008f000
* GDI32 77EF0000 77F35FFF 00046000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BE0000 77C37FFF 00058000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 00300000 00310FFF 00011000
* ShimEng 5CC30000 5CC55FFF 00026000
* AcSpecfc 71540000 71580FFF 00041000
* ole32 76990000 76ACBFFF 0013c000
* SHELL32 773A0000 77B90FFF 007f1000
* SHLWAPI 77F40000 77FB5FFF 00076000
* WINMM 76B10000 76B39FFF 0002a000
* DDRAW 736D0000 73718FFF 00049000
* DCIMAN32 73B30000 73B35FFF 00006000
* USERENV 759D0000 75A7DFFF 000ae000
* MPR 71A90000 71AA1FFF 00012000
* PSAPI 76BC0000 76BCAFFF 0000b000
* comdlg32 76320000 76366FFF 00047000
* COMCTL32 5D170000 5D206FFF 00097000
* IMM32 76300000 7631CFFF 0001d000
* WS2_32 71A20000 71A36FFF 00017000
* WS2HELP 71A10000 71A17FFF 00008000
* LPK 62C20000 62C28FFF 00009000
* USP10 73FA0000 7400AFFF 0006b000
* comctl32 77180000 77281FFF 00102000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 5FDD0000 5FE23FFF 00054000
* wmi 76D00000 76D03FFF 00004000
* SSNETLIB 42CF0000 42D05FFF 00016000
* WSOCK32 71A40000 71A4AFFF 0000b000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 71F00000 71F03FFF 00004000
* SECUR32 77FC0000 77FD0FFF 00011000
* crypt32 765E0000 76671FFF 00092000
* MSASN1 76DB0000 76DC1FFF 00012000
* VERSION 77BD0000 77BD7FFF 00008000
* SSmsLPCn 42CD0000 42CD6FFF 00007000
* ntdsapi 76770000 76782FFF 00013000
* DNSAPI 76EF0000 76F16FFF 00027000
* WLDAP32 76F30000 76F5BFFF 0002c000
* sqlimage 4A400000 4A40CFFF 0000d000
* DBGHELP 020F0000 02102FFF 00013000
*
* Edi: 19991FFC: 16290F65 00000000 00000000 00000000 00000000 00000000
* Esi: 01F6D244: 19990F65 0000FFFF 00000000 00000000 01F6D2D8 00000000
* Eax: 19990F65: FFFFFFFF FFFFFFFF 75405AFF 8C969852 03DB62E5 FFFFFFFF
* Ebx: 19990000: 00000301 00FF0000 00000000 00000000 00000000 00060000
* Ecx: 0000FFFF:
* Edx: 00000026:
* Eip: 00402484: 0114B70F 83145689 EAC107C2 0A4C8D03 184E8902 752000F6
* Ebp: 01F6D264: 01F6D298 00576A9E 00000001 01F6D310 01F6D308 01F6D2D8
* SegCs: 0000001B:
* EFlags: 00010202: 0053005C 00730079 00650074 0033006D 005C0032 00620057
* Esp: 01F6D234: 01F6D244 0040289C 01F6D2D8 00408FEA 19990F65 0000FFFF
* SegSs: 00000023:
*******************************************************************************
-
Short Stack Dump
00402484 Module(sqlservr+00002484)
00576A9E Module(sqlservr+00176A9E) (SQLExit(unsigned long)+00021C8C)
0057620B Module(sqlservr+0017620B) (SQLExit(unsigned long)+000213F9)
005021AC Module(sqlservr+001021AC)
0041ED96 Module(sqlservr+0001ED96)
00442DD0 Module(sqlservr+00042DD0)
004BFD41 Module(sqlservr+000BFD41)
00427985 Module(sqlservr+00027985)
004271BA Module(sqlservr+000271BA)
0042EA36 Module(sqlservr+0002EA36)
0042E82D Module(sqlservr+0002E82D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
005A683F Module(sqlservr+001A683F) (SQLExit(unsigned long)+00051A2D)
004160DB Module(sqlservr+000160DB)
00415765 Module(sqlservr+00015765)
00415410 Module(sqlservr+00015410)
00459A54 Module(sqlservr+00059A54)
004175D8 Module(sqlservr+000175D8)
410735D0 Module(UMS+000035D0) (UmsSystemUserContext::UmsSystemUserContext(class UmsScheduler *,struct UMS_SYSPARAMS *)+00000434)
4107382C Module(UMS+0000382C) (UmsScheduler::FiberEnabled(void)+000001D2)
77C0A243 Module(MSVCRT+0002A243) (_endthread+000000AF)
7C80B50B Module(kernel32+0000B50B) (GetModuleFileNameA+000001B4)
-
2007-07-06 09:58:46.26 spid56 Error: 0, Severity: 19, State: 0
2007-07-06 09:58:46.26 spid56 language_exec: Process 56 generated an access violation. SQL Server is terminating this process..
2007-07-06 09:58:50.28 spid57 Error: 7105, Severity: 22, State: 6
2007-07-06 09:58:50.28 spid57 Page (1:3568), slot 0 for text, ntext, or image node does not exist..
Hi,
which version of SQL Server are you running ?
Did you try to run DBCC CHECKDB already ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
This is probably a bug in SQL Server. Please contact Microsoft product support or provide feedback at http://connect.microsoft.com/sql. When providing feedback upload one of your errorlogs with the error, and any corresponding dump files (*.mdmp*) files that might be present in your log directory.
Thanks,
Fabricio.
|||Hmmmm. This was reported nearly a month ago. No responses other than 'contact support'.
And yet we're seeing the same thing....and wasting days talking to someone in Bangalore.
Is this the new Microsoft? Hello?
|||Have you contacted Microsoft support regading this issue? They are usually pretty quick with resolutions and you would not be wasting your time. If you have done so, can you please provide me with your case # and I'll make sure this issue gets resolved (please send it to fvoznika at microsoft.com).
Thanks,
Fabricio.
|||I've just started getting this EXCEPTION_ACCESS_VIOLATION (0xc0000005) on machines using Windows 2000 sp4 connecting to SQLServer. This is crashing JVMs (multiple Sun versions and BEA also) in the Java VM thread (outside our code). This has just started recently - perhaps with the last set of patches? Has anyone else seen this or know what I could do to get more information? Could this be related to updates to named pipes?Thanks!
-Brian Temple
|||Brian,
The problem you're describing is a crash in the client application connection to SQL Server, and not in SQL Server itself. You can try to post your question in SQL Server Data Access forum (sibling of this one) if you believe SQL Server JDBC is at fault or follow up with Sun as to why JVM is crashing.
Thanks,
Fabricio.
Sunday, February 19, 2012
Excel to Excel data transfer??Urgent
Hi,
This is my situation:
I have a report that is generated as Excel.The data for this report needs to
come from 2 sources
1.Database
2.Another Excel.
I was able to do the first i.e from the database.
How can I pull out data from another excel into this?
is there any reference material avaible for this.
Any help is appreciated.
Thanks,
SudhaHints: VBA ADO w/in Excel, not too difficult even for a novice. It's kinda like
winForms. When you get stuck, record a maco in Excel, then crack it open in VBA
and it will most likely lead you to many solutions.
If you want a user to initiate the Excel gathered data, then you can add a form
as a dialog and/or command button and/or options.
If you want it to be automated you can initiate on open, further you can even
write methods to pump that data into a subsequent Excel template for final
results were the data is formatted and no longer dynamic as a report should be.
HTH
JeffP....
"Sudha" <Sudha@.discussions.microsoft.com> wrote in message
news:A9154CC8-BE03-4363-9B6B-623FCEA35779@.microsoft.com...
> Hi,
> This is my situation:
> I have a report that is generated as Excel.The data for this report needs to
> come from 2 sources
> 1.Database
> 2.Another Excel.
> I was able to do the first i.e from the database.
> How can I pull out data from another excel into this?
> is there any reference material avaible for this.
> Any help is appreciated.
> Thanks,
> Sudha|||Hi,
Thanks for the reply.But I have to access Input Excel as a part of my report
generation so that the final report gets data from 2 sources database & Excel.
To Clarify further,How can I use Excel as my Datasource for my Report.
How should I do that?
Thanks,
Sudha
"JDP@.Work" wrote:
> Hints: VBA ADO w/in Excel, not too difficult even for a novice. It's kinda like
> winForms. When you get stuck, record a maco in Excel, then crack it open in VBA
> and it will most likely lead you to many solutions.
> If you want a user to initiate the Excel gathered data, then you can add a form
> as a dialog and/or command button and/or options.
> If you want it to be automated you can initiate on open, further you can even
> write methods to pump that data into a subsequent Excel template for final
> results were the data is formatted and no longer dynamic as a report should be.
> HTH
> JeffP....
> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
> news:A9154CC8-BE03-4363-9B6B-623FCEA35779@.microsoft.com...
> > Hi,
> >
> > This is my situation:
> >
> > I have a report that is generated as Excel.The data for this report needs to
> > come from 2 sources
> > 1.Database
> > 2.Another Excel.
> > I was able to do the first i.e from the database.
> > How can I pull out data from another excel into this?
> > is there any reference material avaible for this.
> >
> > Any help is appreciated.
> >
> > Thanks,
> > Sudha
>
>
This is my situation:
I have a report that is generated as Excel.The data for this report needs to
come from 2 sources
1.Database
2.Another Excel.
I was able to do the first i.e from the database.
How can I pull out data from another excel into this?
is there any reference material avaible for this.
Any help is appreciated.
Thanks,
SudhaHints: VBA ADO w/in Excel, not too difficult even for a novice. It's kinda like
winForms. When you get stuck, record a maco in Excel, then crack it open in VBA
and it will most likely lead you to many solutions.
If you want a user to initiate the Excel gathered data, then you can add a form
as a dialog and/or command button and/or options.
If you want it to be automated you can initiate on open, further you can even
write methods to pump that data into a subsequent Excel template for final
results were the data is formatted and no longer dynamic as a report should be.
HTH
JeffP....
"Sudha" <Sudha@.discussions.microsoft.com> wrote in message
news:A9154CC8-BE03-4363-9B6B-623FCEA35779@.microsoft.com...
> Hi,
> This is my situation:
> I have a report that is generated as Excel.The data for this report needs to
> come from 2 sources
> 1.Database
> 2.Another Excel.
> I was able to do the first i.e from the database.
> How can I pull out data from another excel into this?
> is there any reference material avaible for this.
> Any help is appreciated.
> Thanks,
> Sudha|||Hi,
Thanks for the reply.But I have to access Input Excel as a part of my report
generation so that the final report gets data from 2 sources database & Excel.
To Clarify further,How can I use Excel as my Datasource for my Report.
How should I do that?
Thanks,
Sudha
"JDP@.Work" wrote:
> Hints: VBA ADO w/in Excel, not too difficult even for a novice. It's kinda like
> winForms. When you get stuck, record a maco in Excel, then crack it open in VBA
> and it will most likely lead you to many solutions.
> If you want a user to initiate the Excel gathered data, then you can add a form
> as a dialog and/or command button and/or options.
> If you want it to be automated you can initiate on open, further you can even
> write methods to pump that data into a subsequent Excel template for final
> results were the data is formatted and no longer dynamic as a report should be.
> HTH
> JeffP....
> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
> news:A9154CC8-BE03-4363-9B6B-623FCEA35779@.microsoft.com...
> > Hi,
> >
> > This is my situation:
> >
> > I have a report that is generated as Excel.The data for this report needs to
> > come from 2 sources
> > 1.Database
> > 2.Another Excel.
> > I was able to do the first i.e from the database.
> > How can I pull out data from another excel into this?
> > is there any reference material avaible for this.
> >
> > Any help is appreciated.
> >
> > Thanks,
> > Sudha
>
>
Excel Report - Large File
SQL Server 2005 reporting service is being used to generate a large excel
report with about 30,000 rows. The size of file generated is ~15MB. but if
the same file is opened in Excel 2003 and saved back in XLS format itself,
the size of the file reduces to approximately 50% of the original size.
One of the initial guesses why this is occuring is that SQL Server 2005
reporting service generates unicode strings for the report data elements,
while excel saving it back, saves it as ANSI. (the report is in english only).
Let me know if the assumption is correct. If so what are means of fixing
this issue or else, is there any other option.On Oct 30, 12:46 am, Madhan Raj J
<MadhanR...@.discussions.microsoft.com> wrote:
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.
This is just a thought, but it might be related to the Excel export
driver that is used in SSRS: to your point, that it could be adding
extra items. One way to check this out is to probably run a comparison
between the 2 files (maybe using something like a trial version of
Araxis Merge >> http://www.araxis.com/merge/ ). Also, I'm not sure if
you want to go this far, but if the size is a major issue you can
either zip the excel file programmatically with a third party DLL (a
few open source ones available) -or- create a small ASP.NET
application (or console EXE) that basically reads in the Excel file
(via streamreader) and does a string replace on all occurrences of the
undesired format in question and stream it back out (via
streamwriter). Of course, I'm not 100% sure that this will work with
Excel; however, it's worth a shot. Also, I think that there is a
technology called Automation in .NET that might be able to help w/
this. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You got me curious so I did a little investiaging. I don't know if Excel
export is doing as you say. I do know that CSV export defaults to unicode.
You can change CSV to export in ASCII by making the following change in
rsreportserver.config (note commenting out the original line):
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
You could see if exporting to CSV ASCII format makes for a smaller file.
This will open up appropriately in Excel 2003 (Unicode shoves each row into
a single cell).
I decided to try the same thing with the Excel entry but I did not see any
difference.
If you make the CSV change go to the RS configuration tool click on Server
Status and stop and start Report Server. It only takes a few seconds to do
this. It will cause the configuration file changes to be processed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english
> only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.|||Thanks of rthe reply..
But my client needs to the report to be exported to excel only, as there is
significant formatting around the data. As you have observed, the device
setting for excel does not have the encoding options as in CSV.
"Bruce L-C [MVP]" wrote:
> You got me curious so I did a little investiaging. I don't know if Excel
> export is doing as you say. I do know that CSV export defaults to unicode.
> You can change CSV to export in ASCII by making the following change in
> rsreportserver.config (note commenting out the original line):
> <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> You could see if exporting to CSV ASCII format makes for a smaller file.
> This will open up appropriately in Excel 2003 (Unicode shoves each row into
> a single cell).
> I decided to try the same thing with the Excel entry but I did not see any
> difference.
> If you make the CSV change go to the RS configuration tool click on Server
> Status and stop and start Report Server. It only takes a few seconds to do
> this. It will cause the configuration file changes to be processed.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> > SQL Server 2005 reporting service is being used to generate a large excel
> > report with about 30,000 rows. The size of file generated is ~15MB. but if
> > the same file is opened in Excel 2003 and saved back in XLS format itself,
> > the size of the file reduces to approximately 50% of the original size.
> >
> > One of the initial guesses why this is occuring is that SQL Server 2005
> > reporting service generates unicode strings for the report data elements,
> > while excel saving it back, saves it as ANSI. (the report is in english
> > only).
> >
> > Let me know if the assumption is correct. If so what are means of fixing
> > this issue or else, is there any other option.
>
>|||Perhaps it is the formatting making the difference but I am not seeing any
difference in size when I save from Excel (versus the Excel created by RS).
One other issue, are you on SP2?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:A5146FAB-7087-45C7-A00E-9CA8A734AB7A@.microsoft.com...
> Thanks of rthe reply..
> But my client needs to the report to be exported to excel only, as there
> is
> significant formatting around the data. As you have observed, the device
> setting for excel does not have the encoding options as in CSV.
> "Bruce L-C [MVP]" wrote:
>> You got me curious so I did a little investiaging. I don't know if Excel
>> export is doing as you say. I do know that CSV export defaults to
>> unicode.
>> You can change CSV to export in ASCII by making the following change in
>> rsreportserver.config (note commenting out the original line):
>> <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> You could see if exporting to CSV ASCII format makes for a smaller file.
>> This will open up appropriately in Excel 2003 (Unicode shoves each row
>> into
>> a single cell).
>> I decided to try the same thing with the Excel entry but I did not see
>> any
>> difference.
>> If you make the CSV change go to the RS configuration tool click on
>> Server
>> Status and stop and start Report Server. It only takes a few seconds to
>> do
>> this. It will cause the configuration file changes to be processed.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
>> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
>> > SQL Server 2005 reporting service is being used to generate a large
>> > excel
>> > report with about 30,000 rows. The size of file generated is ~15MB. but
>> > if
>> > the same file is opened in Excel 2003 and saved back in XLS format
>> > itself,
>> > the size of the file reduces to approximately 50% of the original size.
>> >
>> > One of the initial guesses why this is occuring is that SQL Server 2005
>> > reporting service generates unicode strings for the report data
>> > elements,
>> > while excel saving it back, saves it as ANSI. (the report is in english
>> > only).
>> >
>> > Let me know if the assumption is correct. If so what are means of
>> > fixing
>> > this issue or else, is there any other option.
>>|||I have the same issue. When save directly from Reporting Services, the file
is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
out Unicode. When I opened the excel file in notepad, I noticed there's lots
of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
them. I am still investigating, please let me know if you find a solution.
There's not much formatting in the report, just the column headings, so I
can't figure out what the excess binary data is.
Thanks.
"Madhan Raj J" wrote:
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.|||I spoke too soon - it could be Unicode issue after all. In the large file,
the characters take two spaces, eg. "n e w " vs. "new" (in the smaller file).
"Pranil" wrote:
> I have the same issue. When save directly from Reporting Services, the file
> is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
> out Unicode. When I opened the excel file in notepad, I noticed there's lots
> of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
> them. I am still investigating, please let me know if you find a solution.
> There's not much formatting in the report, just the column headings, so I
> can't figure out what the excess binary data is.
> Thanks.
> "Madhan Raj J" wrote:
> > SQL Server 2005 reporting service is being used to generate a large excel
> > report with about 30,000 rows. The size of file generated is ~15MB. but if
> > the same file is opened in Excel 2003 and saved back in XLS format itself,
> > the size of the file reduces to approximately 50% of the original size.
> >
> > One of the initial guesses why this is occuring is that SQL Server 2005
> > reporting service generates unicode strings for the report data elements,
> > while excel saving it back, saves it as ANSI. (the report is in english only).
> >
> > Let me know if the assumption is correct. If so what are means of fixing
> > this issue or else, is there any other option.
report with about 30,000 rows. The size of file generated is ~15MB. but if
the same file is opened in Excel 2003 and saved back in XLS format itself,
the size of the file reduces to approximately 50% of the original size.
One of the initial guesses why this is occuring is that SQL Server 2005
reporting service generates unicode strings for the report data elements,
while excel saving it back, saves it as ANSI. (the report is in english only).
Let me know if the assumption is correct. If so what are means of fixing
this issue or else, is there any other option.On Oct 30, 12:46 am, Madhan Raj J
<MadhanR...@.discussions.microsoft.com> wrote:
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.
This is just a thought, but it might be related to the Excel export
driver that is used in SSRS: to your point, that it could be adding
extra items. One way to check this out is to probably run a comparison
between the 2 files (maybe using something like a trial version of
Araxis Merge >> http://www.araxis.com/merge/ ). Also, I'm not sure if
you want to go this far, but if the size is a major issue you can
either zip the excel file programmatically with a third party DLL (a
few open source ones available) -or- create a small ASP.NET
application (or console EXE) that basically reads in the Excel file
(via streamreader) and does a string replace on all occurrences of the
undesired format in question and stream it back out (via
streamwriter). Of course, I'm not 100% sure that this will work with
Excel; however, it's worth a shot. Also, I think that there is a
technology called Automation in .NET that might be able to help w/
this. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You got me curious so I did a little investiaging. I don't know if Excel
export is doing as you say. I do know that CSV export defaults to unicode.
You can change CSV to export in ASCII by making the following change in
rsreportserver.config (note commenting out the original line):
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
You could see if exporting to CSV ASCII format makes for a smaller file.
This will open up appropriately in Excel 2003 (Unicode shoves each row into
a single cell).
I decided to try the same thing with the Excel entry but I did not see any
difference.
If you make the CSV change go to the RS configuration tool click on Server
Status and stop and start Report Server. It only takes a few seconds to do
this. It will cause the configuration file changes to be processed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english
> only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.|||Thanks of rthe reply..
But my client needs to the report to be exported to excel only, as there is
significant formatting around the data. As you have observed, the device
setting for excel does not have the encoding options as in CSV.
"Bruce L-C [MVP]" wrote:
> You got me curious so I did a little investiaging. I don't know if Excel
> export is doing as you say. I do know that CSV export defaults to unicode.
> You can change CSV to export in ASCII by making the following change in
> rsreportserver.config (note commenting out the original line):
> <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> You could see if exporting to CSV ASCII format makes for a smaller file.
> This will open up appropriately in Excel 2003 (Unicode shoves each row into
> a single cell).
> I decided to try the same thing with the Excel entry but I did not see any
> difference.
> If you make the CSV change go to the RS configuration tool click on Server
> Status and stop and start Report Server. It only takes a few seconds to do
> this. It will cause the configuration file changes to be processed.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> > SQL Server 2005 reporting service is being used to generate a large excel
> > report with about 30,000 rows. The size of file generated is ~15MB. but if
> > the same file is opened in Excel 2003 and saved back in XLS format itself,
> > the size of the file reduces to approximately 50% of the original size.
> >
> > One of the initial guesses why this is occuring is that SQL Server 2005
> > reporting service generates unicode strings for the report data elements,
> > while excel saving it back, saves it as ANSI. (the report is in english
> > only).
> >
> > Let me know if the assumption is correct. If so what are means of fixing
> > this issue or else, is there any other option.
>
>|||Perhaps it is the formatting making the difference but I am not seeing any
difference in size when I save from Excel (versus the Excel created by RS).
One other issue, are you on SP2?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:A5146FAB-7087-45C7-A00E-9CA8A734AB7A@.microsoft.com...
> Thanks of rthe reply..
> But my client needs to the report to be exported to excel only, as there
> is
> significant formatting around the data. As you have observed, the device
> setting for excel does not have the encoding options as in CSV.
> "Bruce L-C [MVP]" wrote:
>> You got me curious so I did a little investiaging. I don't know if Excel
>> export is doing as you say. I do know that CSV export defaults to
>> unicode.
>> You can change CSV to export in ASCII by making the following change in
>> rsreportserver.config (note commenting out the original line):
>> <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> You could see if exporting to CSV ASCII format makes for a smaller file.
>> This will open up appropriately in Excel 2003 (Unicode shoves each row
>> into
>> a single cell).
>> I decided to try the same thing with the Excel entry but I did not see
>> any
>> difference.
>> If you make the CSV change go to the RS configuration tool click on
>> Server
>> Status and stop and start Report Server. It only takes a few seconds to
>> do
>> this. It will cause the configuration file changes to be processed.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
>> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
>> > SQL Server 2005 reporting service is being used to generate a large
>> > excel
>> > report with about 30,000 rows. The size of file generated is ~15MB. but
>> > if
>> > the same file is opened in Excel 2003 and saved back in XLS format
>> > itself,
>> > the size of the file reduces to approximately 50% of the original size.
>> >
>> > One of the initial guesses why this is occuring is that SQL Server 2005
>> > reporting service generates unicode strings for the report data
>> > elements,
>> > while excel saving it back, saves it as ANSI. (the report is in english
>> > only).
>> >
>> > Let me know if the assumption is correct. If so what are means of
>> > fixing
>> > this issue or else, is there any other option.
>>|||I have the same issue. When save directly from Reporting Services, the file
is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
out Unicode. When I opened the excel file in notepad, I noticed there's lots
of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
them. I am still investigating, please let me know if you find a solution.
There's not much formatting in the report, just the column headings, so I
can't figure out what the excess binary data is.
Thanks.
"Madhan Raj J" wrote:
> SQL Server 2005 reporting service is being used to generate a large excel
> report with about 30,000 rows. The size of file generated is ~15MB. but if
> the same file is opened in Excel 2003 and saved back in XLS format itself,
> the size of the file reduces to approximately 50% of the original size.
> One of the initial guesses why this is occuring is that SQL Server 2005
> reporting service generates unicode strings for the report data elements,
> while excel saving it back, saves it as ANSI. (the report is in english only).
> Let me know if the assumption is correct. If so what are means of fixing
> this issue or else, is there any other option.|||I spoke too soon - it could be Unicode issue after all. In the large file,
the characters take two spaces, eg. "n e w " vs. "new" (in the smaller file).
"Pranil" wrote:
> I have the same issue. When save directly from Reporting Services, the file
> is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
> out Unicode. When I opened the excel file in notepad, I noticed there's lots
> of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
> them. I am still investigating, please let me know if you find a solution.
> There's not much formatting in the report, just the column headings, so I
> can't figure out what the excess binary data is.
> Thanks.
> "Madhan Raj J" wrote:
> > SQL Server 2005 reporting service is being used to generate a large excel
> > report with about 30,000 rows. The size of file generated is ~15MB. but if
> > the same file is opened in Excel 2003 and saved back in XLS format itself,
> > the size of the file reduces to approximately 50% of the original size.
> >
> > One of the initial guesses why this is occuring is that SQL Server 2005
> > reporting service generates unicode strings for the report data elements,
> > while excel saving it back, saves it as ANSI. (the report is in english only).
> >
> > Let me know if the assumption is correct. If so what are means of fixing
> > this issue or else, is there any other option.
Wednesday, February 15, 2012
excel generated xml file to sql
hi,
i hv a report generated from a web-based application. although the extension
is '.xls', this is really an xml doc (am i correct?). i say this because if
i
'save as' the file, the extension that appears is 'XML spreadsheet'.
i don't want to open the file and save it as an excel file, because that
would mean there will be a manual process in my script. what is the best way
to import this file directly to sql? i tried DTS but DTS cannot recognize th
e
file.
thanks for your help!Do you want to import the XML as a BLOB or put it into tables?
Michael
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!|||i want to put it into a table. thanks in advance for the help.
cheers.
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>|||i want to put it into a table. the table can be existing, or not yet
existing. thanks in advance.
cheers
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>|||You may consider use Bulkload from Sqlxml or using OpenXml i nT-SQL if your
file size is not big.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
i hv a report generated from a web-based application. although the extension
is '.xls', this is really an xml doc (am i correct?). i say this because if
i
'save as' the file, the extension that appears is 'XML spreadsheet'.
i don't want to open the file and save it as an excel file, because that
would mean there will be a manual process in my script. what is the best way
to import this file directly to sql? i tried DTS but DTS cannot recognize th
e
file.
thanks for your help!Do you want to import the XML as a BLOB or put it into tables?
Michael
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!|||i want to put it into a table. thanks in advance for the help.
cheers.
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>|||i want to put it into a table. the table can be existing, or not yet
existing. thanks in advance.
cheers
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>|||You may consider use Bulkload from Sqlxml or using OpenXml i nT-SQL if your
file size is not big.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
excel generated xml file to sql
hi,
i hv a report generated from a web-based application. although the extension
is '.xls', this is really an xml doc (am i correct?). i say this because if i
'save as' the file, the extension that appears is 'XML spreadsheet'.
i don't want to open the file and save it as an excel file, because that
would mean there will be a manual process in my script. what is the best way
to import this file directly to sql? i tried DTS but DTS cannot recognize the
file.
thanks for your help!
Do you want to import the XML as a BLOB or put it into tables?
Michael
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
|||i want to put it into a table. thanks in advance for the help.
cheers.
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>
|||i want to put it into a table. the table can be existing, or not yet
existing. thanks in advance.
cheers
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>
|||You may consider use Bulkload from Sqlxml or using OpenXml i nT-SQL if your
file size is not big.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
i hv a report generated from a web-based application. although the extension
is '.xls', this is really an xml doc (am i correct?). i say this because if i
'save as' the file, the extension that appears is 'XML spreadsheet'.
i don't want to open the file and save it as an excel file, because that
would mean there will be a manual process in my script. what is the best way
to import this file directly to sql? i tried DTS but DTS cannot recognize the
file.
thanks for your help!
Do you want to import the XML as a BLOB or put it into tables?
Michael
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
|||i want to put it into a table. thanks in advance for the help.
cheers.
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>
|||i want to put it into a table. the table can be existing, or not yet
existing. thanks in advance.
cheers
"Michael Rys [MSFT]" wrote:
> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>
|||You may consider use Bulkload from Sqlxml or using OpenXml i nT-SQL if your
file size is not big.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
Subscribe to:
Posts (Atom)