SQL query looks OK in management studio but throws memory exception when building reports by Data Tools

up vote
0
down vote

favorite

I have fours SQL tables (with different number of rows and column) from those I want to build a new table for reporting purpose based on some rules. I built query statements and run in management studio. In this case, I get some response from management studio with some data but if I try to use those SQL queries in data source to build a report in Visual Studio, I get memory exception. What can I do for this?
Here is the SQL statements I used

SELECT Intable.Fra, EqTable.Name, Rf.Data

  FROM EqTable,InTable,RfTable

  WHERE RfTable.Name = EqTable.Name  AND EqTable.Name] NOT LIKE '%Ann%';

The equivalent tables are shown in the following diagram.
enter image description here

asked Jun 8 '16 at 16:26

RotatingWheel

458516

Have you tried creating a view with that query? That might work.
– Joe C
Jun 8 '16 at 16:36

add a comment |

up vote
0
down vote

favorite

SELECT Intable.Fra, EqTable.Name, Rf.Data

  FROM EqTable,InTable,RfTable

  WHERE RfTable.Name = EqTable.Name  AND EqTable.Name] NOT LIKE '%Ann%';

The equivalent tables are shown in the following diagram.
enter image description here

asked Jun 8 '16 at 16:26

RotatingWheel

458516

Have you tried creating a view with that query? That might work.
– Joe C
Jun 8 '16 at 16:36

add a comment |

up vote
0
down vote

favorite

SELECT Intable.Fra, EqTable.Name, Rf.Data

  FROM EqTable,InTable,RfTable

  WHERE RfTable.Name = EqTable.Name  AND EqTable.Name] NOT LIKE '%Ann%';

The equivalent tables are shown in the following diagram.
enter image description here

asked Jun 8 '16 at 16:26

RotatingWheel

458516

SELECT Intable.Fra, EqTable.Name, Rf.Data

  FROM EqTable,InTable,RfTable

  WHERE RfTable.Name = EqTable.Name  AND EqTable.Name] NOT LIKE '%Ann%';

The equivalent tables are shown in the following diagram.
enter image description here

sql-server visual-studio-2012

asked Jun 8 '16 at 16:26

RotatingWheel

458516

asked Jun 8 '16 at 16:26

RotatingWheel

458516

asked Jun 8 '16 at 16:26

RotatingWheel

458516

asked Jun 8 '16 at 16:26

RotatingWheel

458516

asked Jun 8 '16 at 16:26

RotatingWheel

458516

Have you tried creating a view with that query? That might work.
– Joe C
Jun 8 '16 at 16:36

add a comment |

Have you tried creating a view with that query? That might work.
– Joe C
Jun 8 '16 at 16:36

Have you tried creating a view with that query? That might work.
– Joe C
Jun 8 '16 at 16:36

add a comment |

2 Answers
2

active

oldest

votes

up vote
0
down vote

I can see two possibilities:

You have an additional "]" character included in your SQL but this maybe a typo

Do you need a join for the table [inTable]?

answered Jun 8 '16 at 16:35

Paul

3419

I could not use join here because there is no match in ID(I have limited knowledge in SQL). Could you show me how can I use join here? Thanks
– RotatingWheel
Jun 8 '16 at 18:37

Without knowing what your schema is - no sorry. Your expanded result set will be the result of a cartesian join which will inflate the amount of results returned either correctly or incorrectly. See google
– Paul
Jun 9 '16 at 8:40

add a comment |

up vote
0
down vote

This is almost certainly because you are using the ANSI-89 style join. You should use the "newer" ANSI-92 style join.

Bad habits to kick : using old-style JOINs

What has happened here is you have joined RfTable and EqTable by Name. But then you have created a cross join to InTable. The memory exception is probably because once you create this cross join the amount of rows is staggering.

What I really don't understand though is you said you have 4 tables but only 3 of them are in your query.

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

I have shown 3 tables but in actual code I have 4 tables.
– RotatingWheel
Jun 8 '16 at 18:33

So the query you posted isn't the query that you are actually using? How do you expect anybody to be able to help when you don't even give them correct information?
– Sean Lange
Jun 8 '16 at 18:39

Sorry, I just wanted to avoid actual code other wise it is same.
– RotatingWheel
Jun 8 '16 at 18:45

Well then use proper join syntax and avoid the cross joins. When you run your query in SSMS how many rows are you getting back?
– Sean Lange
Jun 8 '16 at 19:01

Thanks, with my actual database I get 1490535 rows With SSMS it looks fine. Since most online examples hows if there are matches in Keys then join works and in my case there are no matches in ID, so I am confused.
– RotatingWheel
Jun 8 '16 at 19:14

|
show 5 more comments

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f37708011%2fsql-query-looks-ok-in-management-studio-but-throws-memory-exception-when-buildin%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
0
down vote

I can see two possibilities:

You have an additional "]" character included in your SQL but this maybe a typo

Do you need a join for the table [inTable]?

answered Jun 8 '16 at 16:35

Paul

3419

I could not use join here because there is no match in ID(I have limited knowledge in SQL). Could you show me how can I use join here? Thanks
– RotatingWheel
Jun 8 '16 at 18:37

Without knowing what your schema is - no sorry. Your expanded result set will be the result of a cartesian join which will inflate the amount of results returned either correctly or incorrectly. See google
– Paul
Jun 9 '16 at 8:40

add a comment |

up vote
0
down vote

I can see two possibilities:

You have an additional "]" character included in your SQL but this maybe a typo

Do you need a join for the table [inTable]?

answered Jun 8 '16 at 16:35

Paul

3419

I could not use join here because there is no match in ID(I have limited knowledge in SQL). Could you show me how can I use join here? Thanks
– RotatingWheel
Jun 8 '16 at 18:37

Without knowing what your schema is - no sorry. Your expanded result set will be the result of a cartesian join which will inflate the amount of results returned either correctly or incorrectly. See google
– Paul
Jun 9 '16 at 8:40

add a comment |

up vote
0
down vote

I can see two possibilities:

You have an additional "]" character included in your SQL but this maybe a typo

Do you need a join for the table [inTable]?

answered Jun 8 '16 at 16:35

Paul

3419

I can see two possibilities:

You have an additional "]" character included in your SQL but this maybe a typo

Do you need a join for the table [inTable]?

answered Jun 8 '16 at 16:35

Paul

3419

answered Jun 8 '16 at 16:35

Paul

3419

answered Jun 8 '16 at 16:35

Paul

3419

answered Jun 8 '16 at 16:35

Paul

3419

I could not use join here because there is no match in ID(I have limited knowledge in SQL). Could you show me how can I use join here? Thanks
– RotatingWheel
Jun 8 '16 at 18:37

Without knowing what your schema is - no sorry. Your expanded result set will be the result of a cartesian join which will inflate the amount of results returned either correctly or incorrectly. See google
– Paul
Jun 9 '16 at 8:40

add a comment |

I could not use join here because there is no match in ID(I have limited knowledge in SQL). Could you show me how can I use join here? Thanks
– RotatingWheel
Jun 8 '16 at 18:37

Without knowing what your schema is - no sorry. Your expanded result set will be the result of a cartesian join which will inflate the amount of results returned either correctly or incorrectly. See google
– Paul
Jun 9 '16 at 8:40

I could not use join here because there is no match in ID(I have limited knowledge in SQL). Could you show me how can I use join here? Thanks
– RotatingWheel
Jun 8 '16 at 18:37

Without knowing what your schema is - no sorry. Your expanded result set will be the result of a cartesian join which will inflate the amount of results returned either correctly or incorrectly. See google
– Paul
Jun 9 '16 at 8:40

add a comment |

up vote
0
down vote

This is almost certainly because you are using the ANSI-89 style join. You should use the "newer" ANSI-92 style join.

Bad habits to kick : using old-style JOINs

What I really don't understand though is you said you have 4 tables but only 3 of them are in your query.

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

I have shown 3 tables but in actual code I have 4 tables.
– RotatingWheel
Jun 8 '16 at 18:33

So the query you posted isn't the query that you are actually using? How do you expect anybody to be able to help when you don't even give them correct information?
– Sean Lange
Jun 8 '16 at 18:39

Sorry, I just wanted to avoid actual code other wise it is same.
– RotatingWheel
Jun 8 '16 at 18:45

Well then use proper join syntax and avoid the cross joins. When you run your query in SSMS how many rows are you getting back?
– Sean Lange
Jun 8 '16 at 19:01

Thanks, with my actual database I get 1490535 rows With SSMS it looks fine. Since most online examples hows if there are matches in Keys then join works and in my case there are no matches in ID, so I am confused.
– RotatingWheel
Jun 8 '16 at 19:14

|
show 5 more comments

up vote
0
down vote

This is almost certainly because you are using the ANSI-89 style join. You should use the "newer" ANSI-92 style join.

Bad habits to kick : using old-style JOINs

What I really don't understand though is you said you have 4 tables but only 3 of them are in your query.

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

I have shown 3 tables but in actual code I have 4 tables.
– RotatingWheel
Jun 8 '16 at 18:33

So the query you posted isn't the query that you are actually using? How do you expect anybody to be able to help when you don't even give them correct information?
– Sean Lange
Jun 8 '16 at 18:39

Sorry, I just wanted to avoid actual code other wise it is same.
– RotatingWheel
Jun 8 '16 at 18:45

Well then use proper join syntax and avoid the cross joins. When you run your query in SSMS how many rows are you getting back?
– Sean Lange
Jun 8 '16 at 19:01

Thanks, with my actual database I get 1490535 rows With SSMS it looks fine. Since most online examples hows if there are matches in Keys then join works and in my case there are no matches in ID, so I am confused.
– RotatingWheel
Jun 8 '16 at 19:14

|
show 5 more comments

up vote
0
down vote

This is almost certainly because you are using the ANSI-89 style join. You should use the "newer" ANSI-92 style join.

Bad habits to kick : using old-style JOINs

What I really don't understand though is you said you have 4 tables but only 3 of them are in your query.

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

This is almost certainly because you are using the ANSI-89 style join. You should use the "newer" ANSI-92 style join.

Bad habits to kick : using old-style JOINs

What I really don't understand though is you said you have 4 tables but only 3 of them are in your query.

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

edited Nov 11 at 13:55

Aaron Bertrand

206k27361402

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

answered Jun 8 '16 at 16:36

Sean Lange

24.3k21735

I have shown 3 tables but in actual code I have 4 tables.
– RotatingWheel
Jun 8 '16 at 18:33

So the query you posted isn't the query that you are actually using? How do you expect anybody to be able to help when you don't even give them correct information?
– Sean Lange
Jun 8 '16 at 18:39

Sorry, I just wanted to avoid actual code other wise it is same.
– RotatingWheel
Jun 8 '16 at 18:45

Well then use proper join syntax and avoid the cross joins. When you run your query in SSMS how many rows are you getting back?
– Sean Lange
Jun 8 '16 at 19:01

Thanks, with my actual database I get 1490535 rows With SSMS it looks fine. Since most online examples hows if there are matches in Keys then join works and in my case there are no matches in ID, so I am confused.
– RotatingWheel
Jun 8 '16 at 19:14

|
show 5 more comments

I have shown 3 tables but in actual code I have 4 tables.
– RotatingWheel
Jun 8 '16 at 18:33

So the query you posted isn't the query that you are actually using? How do you expect anybody to be able to help when you don't even give them correct information?
– Sean Lange
Jun 8 '16 at 18:39

Sorry, I just wanted to avoid actual code other wise it is same.
– RotatingWheel
Jun 8 '16 at 18:45

Well then use proper join syntax and avoid the cross joins. When you run your query in SSMS how many rows are you getting back?
– Sean Lange
Jun 8 '16 at 19:01

Thanks, with my actual database I get 1490535 rows With SSMS it looks fine. Since most online examples hows if there are matches in Keys then join works and in my case there are no matches in ID, so I am confused.
– RotatingWheel
Jun 8 '16 at 19:14

I have shown 3 tables but in actual code I have 4 tables.
– RotatingWheel
Jun 8 '16 at 18:33

So the query you posted isn't the query that you are actually using? How do you expect anybody to be able to help when you don't even give them correct information?
– Sean Lange
Jun 8 '16 at 18:39

Sorry, I just wanted to avoid actual code other wise it is same.
– RotatingWheel
Jun 8 '16 at 18:45

Well then use proper join syntax and avoid the cross joins. When you run your query in SSMS how many rows are you getting back?
– Sean Lange
Jun 8 '16 at 19:01

Thanks, with my actual database I get 1490535 rows With SSMS it looks fine. Since most online examples hows if there are matches in Keys then join works and in my case there are no matches in ID, so I am confused.
– RotatingWheel
Jun 8 '16 at 19:14

|
show 5 more comments

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk