Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas

"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
sql

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Referential Integrity In Mutiple Tables. Need Help.

I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.

Example:

Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|

Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|

Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|

Table 4
======|====|
StateID Name
======|====|

How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?You have it already...you just need to enforce the relationship with a FOREIGN KEY CONSTRAINT

And please don't tell me that StateID is an entity column...|||I can only relate table 4 with either table 2 or 3 but not both. what do you mean by entity table? Excuse my ignorance.

Thanks|||I'm not sure I follow you...

USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table4 (
StateID int PRIMARY KEY
, [Name] varchar(50)
)

CREATE TABLE Table2 (
BillingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table2 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)

CREATE TABLE Table3 (
ShippingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table3 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)
GO

INSERT INTO Table3(ShippingAddressID, StateID) SELECT 1,1
INSERT INTO Table2(BillingAddressID, StateID) SELECT 1,1
GO
INSERT INTO Table4(StateID, [Name]) SELECT 1, 'New Jersey'
GO
INSERT INTO Table3(ShippingAddressID, StateID, OtherFields) SELECT 1,1, 1
INSERT INTO Table2(BillingAddressID , StateID, OtherFields) SELECT 1,1, 1
GO

SELECT 'Table3' AS Source, l.ShippingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table3 l
JOIN Table4 r
ON l.StateID = r.StateID
UNION ALL
SELECT 'Table2' AS Source, l.BillingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table2 l
JOIN Table4 r
ON l.StateID = r.StateID
GO

SET NOCOUNT OFF
DROP TABLE Table2
DROP TABLE Table3
DROP TABLE Table4
GO|||Oh, I meant IDENTITY Column by the way...not Entity...sorry|||why wouldn't StateID be an identity column?

are you perhaps suggesting that a natural key like NJ would be more suitable?

better hide, because all the surrogate key fanatics are gonna come gunnin' for ya...|||Hey...it's their perogative...let them join till the cows come home...

And there are times for it...but not as many as it's abused...

Here's a question. Would you use an identity column for a phone number? SSN? Stock Market symbol?|||Would you use an identity column for a phone number? SSN? Stock Market symbol?me? never

you must have me confused with those surrogate key fanatics, who blindly require an identity column in every table...

i know a guy on, ahem, another forum web site, who once took a poll on the subject

see Surrogate Keys.The Devils spawn (OK Not really) (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)

Referential Integrity - linking multiple tables to transaction table

I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:

Transaction Table
-TranId
-Calc Amount

Table 1 (the amount is inserted into the transaction table)
- Tb1Id
- Tb1Amt

Table 2 (an amount is calculated based on the percentage and inserted
into the transaction table)
-Tbl2Id
-Tb2Percentage

Table 3 (the amount is inserted into the transaction table)
-Tbl3Id
-Tbl3Amut

Table 4 (an amount is calculated based on the percentage and inserted
into the transaction table. )
-Tbl2Id
-Tb2Percentage

How do I create referential integrity between the Transaction table and
the rest of the tables. When I make changes to the values in Table 1 -
4, I need to be able to reflect this in the Transaction table.

Thanks.Can you make "Transaction Table" into a view
based on joins between your other 4 tables?

Where does TransID come from?|||Thanks,

Trans ID is generated - identify column.

We thought of using a view. But the when the core object is set to
LIVE, all changes from then on will be tracked as new transactions in
the transaction table. So in essence, you have have a row in Table 1 -
4 that has multiple transactions in the transacation table. We could
add a column to the Transaction Table set it to refer to the Ids of
Table 1 - 4. But we won't be able to use the DBMS contraints to enfore
this. Possibly a middle table to each Table 1 to 4?|||(heyvinay@.gmail.com) writes:
> I have transaction table where the rows entered into the transaction
> can come a result of changes that take place if four different tables.
> So the situation is as follows:
> Transaction Table
> -TranId
> -Calc Amount
> Table 1 (the amount is inserted into the transaction table)
> - Tb1Id
> - Tb1Amt
> Table 2 (an amount is calculated based on the percentage and inserted
> into the transaction table)
> -Tbl2Id
> -Tb2Percentage
> Table 3 (the amount is inserted into the transaction table)
> -Tbl3Id
> -Tbl3Amut
> Table 4 (an amount is calculated based on the percentage and inserted
> into the transaction table. )
> -Tbl2Id
> -Tb2Percentage
> How do I create referential integrity between the Transaction table and
> the rest of the tables. When I make changes to the values in Table 1 -
> 4, I need to be able to reflect this in the Transaction table.

Depends on what you mean with changes, but obviously if you change an
amount in one table and you want that to affect the CalcAmount in the
Transaction table, then you should consider a trigger. For that to
work, the Transaction table need to have one FK column per referencing
table.

Your description was quite brief, and very abstract. It is not at all
impossible that there is a better design, if you can give more meat of
what is behind the various tables.

--
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|||Thanks.
Well the application we are designing is for Premium calcuation. The
transaction table will store all the premium calcuation breakdowns.
The reference to table 1 - 4 are various elements within the system
that will generated chargeable premiums. Eg. the product selected;
discounts applied to the customer; specific endorsements; Payment
menthod discounts etc. Once policy is live, and changes to the source
tables (eg product, discount amount) etc or even the cancellation of
the policy will raise NEW records in the transaction tables.

We can manage all this from code directly to ensure all is in sync, but
I prefer to apply referential integrity managed by the DBMS.

You wrote: "For that to work, the Transaction table need to have one FK
column per referencing table. " - does MS SQL allow null values for
foreign key constraints?

Thanks|||(heyvinay@.gmail.com) writes:
> We can manage all this from code directly to ensure all is in sync, but
> I prefer to apply referential integrity managed by the DBMS.

Referential integrity is about the integrity of - references. That is,
if the Orders table has a FK constraint to table Customers, you cannot
add an Order for a non-existing customer, and you cannot delete a customer
that has an order.

As I understood it, you want one data in one table be the result of data
in other tables. This cannot be achieved with referential integrity. You
can use views, and under some circumstances you can materialise a view.

> You wrote: "For that to work, the Transaction table need to have one FK
> column per referencing table. " - does MS SQL allow null values for
> foreign key constraints?

Yes. For instance, in a Customers table, you may have Citizen column
table that has a foreign-key constraint to the Countries table. But
this column must be nullable, since some people stateless. Not talking
of juridical persons, who normally are not citizens.

--
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|||Off the top of my head, two fundamental ways.

Insert into the 4 tables, and have triggers on those tables for insert,
update, and delete that add rows to the trans table.

Two. utilize standardized stored procedures you use to add/edit/update
the 4 tables. Inside the stored procedures, do a begin trans/ commit
around the changes and the trans table. In this way, you can pretty
much ensure that it will keep up.

The stored procedure has the advantage of allowing more business logic
around changes. You can validate data changes external to the app.
However, you MUST enforce that all changes to the 4 tables MUST uses
the specified stored procedures.

The Triggers have the advantage of "simplicity." Again though,. you are
somewhat limited in what logic you can use.

Two comments you didn't ask for. Be very stingy with indexes on the
trans table. I'm not saying ot have none, BUT don't have a lot of
indexes. And the indexes you do have should be fairly unique. Trans
tables get very large very fast, and they really slow everything down
in a sneaky insidious sort of way in a few years.

Secondly, it can be hard to get the :"right" infomration into the trans
table. As a test to see whether you have all data you need, manually
try to take each trans record, and recreate the final data results. In
a perfect world, your trans table will allow a picture of exactly what
changed when and how, allowing hte final results to be seen.
For a final test, just go ahead and write the program that will read
the trans data and recreate teh table. This is almost never trivial,
but almost always worthwhile.
-dougsql

Referencing tables on a remote server

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:

> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>

Referencing tables on a remote server

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>

Reference axis(2) members in MDX

Hello everybody.

I've got a little problem: I want to reference axis(2) members in a calculated member, so I can use multiple selection.

I've tried:

with member [Measures].[Test] as 'settostr(axis(2))'

select {[Measures].[Test]} on 0,

{[Dim Sales Territory].[Sales Territory Region].members} on 1

from [Adventure Works DW]

where ([Dim Geography].[Country Region Code].&[US],[Dim Geography].[Country Region Code].&[CA])

However, I get an error.

Is there any other way?

Thanks a lot,

Santi

The Axis function cannot be used on the filter axis.The Axis function cannot be used on the filter axis.

http://msdn2.microsoft.com/en-us/library/ms145531.aspx

your shuldn't use Axis function if you whant to use multiple filter

|||

Thanks Vladimir.

Then, assuming I have several members on the filter axis, how can I find out what those members are?

Is it at all possible?

The thing is, I'm trying to enable multiple selection in a dimension, and the selected members go directly to the where clause of the query. Since I have some calculated measures, I can′t reference those members using currentmember.

Thanks again for your answer.

|||What the client do you use? Do you have a full control on it.|||

You could use the Existing function:

>>

with member [Measures].[Test] as

settostr(Existing [Geography].[Country].[Country].Members)

select {[Measures].[Test]} on 0

from [Adventure Works]

where {[Geography].[Country].&[United States],

[Geography].[Country].&[Canada]}

-

Test
{[Geography].[Country].&[Canada],[Geography].[Country].&[United States]}

>>

This technique is discussed in detail here:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

>>

|||

Thanks a lot Deepak!

Didn't know about Exist and Existing at all.

I've also read Mosha's blog entry, and it explains exactly the internals of what I'm trying to acomplish.