Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2005 queries not executing

Posted on 2007-08-02
12
Medium Priority
?
181 Views
Last Modified: 2010-03-19
Hi,

I have a very large SQL script that Crystal Reports XI is using to query my db.  Unfortunately I'm getting an error (see this thread: http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_22733281.html).

I've spent a fair chunk of time systematically taking the query apart (after copying it from CR XI using Database --> Show SQL Query and pasting it right into a new SQL 2005 query window).  But, no matter whether it was the original hugantic query, or any of a series of progressively simpler ones -- down to where it is now a simple SELECT statement with 15 columns from 1 table -- all of them are simply not executing.  That is, SQL Server 2005 is simply (and continuously) parsing the query with no result.  It's like it's caught in some infinite loop, although that certainly isn't the case here.

If there were anything wrong with my queries, it would take but a split second to return some kind of an error code at least...

Any ideas?
Thanks
0
Comment
Question by:jgerbitz
  • 7
  • 5
12 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 19619508
You may want to try and take it TABLE BY TABLE....that is to say...Start with your main table....run query....pulling in a few columns.

Then,.. add the next table...LINK IT...then pull in some a few more columns..for making sure you have at least 1 column from each table....then QUERY...and pull data.

Keep doing this until you can "systematically" find where the link problem is.......

MikeV
0
 

Author Comment

by:jgerbitz
ID: 19619606
Thanks Mike... but that's the strange bit, I can't even run this:

SELECT
PatientArrHOBMetStatus
FROM  
PatientArrival
WHERE PatientID = '13'

...without SQL Server going into some kind of an eternal parse (it should return '1').  When I click on either Parse or Execute, the little green circle icon continues to be active for minutes at a time.  I have to cancel out.

This obviously doesn't have anything to do with the query.  Do you know what's going on?

Jens
0
 
LVL 17

Expert Comment

by:MIKE
ID: 19619638
Just do THIS:

SELECT *
FROM PatientArrival

What do you get...?

Also...so are you using SQL SERVER ONLY...and not crystal right now...? IF so,...if SQL SERVER can't even pull data...then ...IT ITSELF is the problem...and not Crystal....Crystal is just WRITING SQL SCRIPTS....
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:MIKE
ID: 19619652
So have you EVER been able to pull ANY DATA from ANY TABLE before....?

..
0
 

Author Comment

by:jgerbitz
ID: 19619754
Absolutely... the data-gathering application has pages that repopulate with data stored in the db.  I just tested it again and it seems to be working fine.

I already tried exactly that query you suggested above, and no luck...!  This is wierd.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 19619815
I'm totally confused on what you are doing...?

IF you are using SQL SERVER and typing in a simple SELECT statement..and you cannot get data returned..then something major is wrong with your SQL SERVER Config...
0
 
LVL 17

Expert Comment

by:MIKE
ID: 19619820
What do you mean by "....THE DATA GATHERING APPLICATION.....?"
0
 

Author Comment

by:jgerbitz
ID: 19619891
Sorry.... The CR XI report I'm working on is just reporting using the data that was gathered by a C# application I developed.  The data that is collected is stored into the SQL 2005 db.  I know that the db works because when I run the app, data is pulled from the db to repopulate certain pages.  Does that make sense?

None of the queries in my application are particularly complex, but they're certainly a little more involved than the simple SELECT query above that doesn't work.
0
 
LVL 17

Accepted Solution

by:
MIKE earned 1000 total points
ID: 19620016
Well.......again....if you cannot get SQL SERVER...."Query Analyzer"...to pull data using a simple SELECT script...then ...you will NOT be able to get Crytal to do it.

So...are you SURE your C# application is actually "loading" the data into a literal TABLE in SQL SERVER.... OR...is your app just creating and deleting  TEMP tables...???

Bottomline is that your TABLES must be present in SQL SERVER. I don't thin your problem is Crystal Reports at all....there is something else going on here in C# or SQL SERVER or BOTH......

MikeV
0
 

Author Comment

by:jgerbitz
ID: 19620097
Well, how can I tell if temp tables are playing a part?  When I step through my code, the point where the application opens a connection to SQL, performs the ExecuteNonQuery() and then closes the connection seems to work fine, i.e. no exception is thrown in the Try...Catch.  And this particular statement has 3 INNER JOINs.

However, when I copy the same SQL statement from the Locals window and paste it into the "QA" and then try to run it, nothing happens.  So if temp tables ARE playing a role, how can I find out?

I'm going to re-start my system and see what happens.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 19620125
No errors...no nothing? When you paste into "QA" ...and execute...SOMETHING should happen...either the query...or ...a list of errors...or issues....that "QA" has with they script.....?

0
 

Author Comment

by:jgerbitz
ID: 19620222
Good news... whatever it was (nope, *nothing* was happening), it has been fixed with a system restart.  I get results from queries I run in the "QA"

Now I'm back to the original problem of finding out where the issue lies with the error message...

Thanks for your help on this.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question