Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL SP query just hanging? no results?

Posted on 2011-09-09
13
Medium Priority
?
443 Views
Last Modified: 2012-05-12
Hello,

I have a SP that works fine when i leave out a join in a sql string , but when i add in the join the query just hangs no results come , have left it hanging for 10min and had to stop. So i cant even use profiler as cant get results to use for DTA. the bit that causes issue is the following

SELECT @STR = @STR + '
                   
		 SELECT '''+DBASE+''' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM ' + DBASE + '.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN ' + DBASE + '.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]
                      
            '
        From ##tmpUserDBList 

Open in new window

0
Comment
Question by:deanmachine333
11 Comments
 

Author Comment

by:deanmachine333
ID: 36509835
Quick note i have tested the sub query and the join works fine but not in the SQL string???
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36509861
have you printed out the select statement before executing it and confirmed it looks correct?
0
 

Author Comment

by:deanmachine333
ID: 36509886
Hiya, if i take out the following code

           INNER JOIN ' + DBASE + '.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]
the query works fine , i have also tested this section with join and works fine aswell -

SELECT     		a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM testdatabase.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN testdatabase.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36510041
missing @     ?

 INNER JOIN ' +@DBASE + '.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]


print the constructed @str before executing it

print @str
insert 
	  into  ##tmpUserDBase
	  exec (@STR)

Open in new window


and you'll find its malformed...
0
 

Author Comment

by:deanmachine333
ID: 36510160
nope it enters the correct value for the 'DBASE'  when i print it out.
0
 
LVL 2

Expert Comment

by:John_Bon
ID: 36511369
try this in your proc

             'SELECT ' +  @DBASE + ' as [DBASE]
                        ,a.[DET_NOMINALDR] as [Nominal Code]
                        ,a.[DET_DATE] as [Date]
                        ,a.[DET_PERIODNUMBR] as [Period]
                        ,a.[DET_YEAR] as [Year]
                        ,a.[DET_DESCRIPTION] as [Narrative]
                        ,a.[DET_NETT] as [D/C]
                        ,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM ' + @DBASE + '.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN ' + @DBASE + '.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36514819
>>nope it enters the correct value for the 'DBASE'  when i print it out. <<
You may want to double check that.
0
 

Author Comment

by:deanmachine333
ID: 36521748
I have tried different variations of the @dbase and +@dbase+ and still either hanging or it errors out.
If i set the @dbase variable to one value ie testdb1 and run it it comes back with nothing in result window but get error
Msg 207, Level 16, State 1, Line 3
Invalid column name 'TESTDB1'.
And that is using '+@dbase+' in all 3 locations
SELECT '+@dbase+' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM ' + @dbase + '.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN ' + @dbase + '.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]

Open in new window


If i set the @dbase in the select as '''+@dbase+''' and run it still only using one database the query just hangs (runs non stop) - i also added the print line in to see what values where insereted . I have to stop query and get the following in the print screen
SELECT 'TESTDB1' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB1.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB1.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]
                      
            
Query was cancelled by user.

Open in new window


If i add 2 databases to use TESTDB1 and TESTDB2 it still hangs and i have to stop query but get the following print out
 SELECT 'TESTDB2 ' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB2 .[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB2 .[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]
                      
            
                   
		 SELECT 'TESTDB2 ' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB2 .[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB2 .[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]

Open in new window


If i take out both '+@dbase+' and just use '+dbase+' the the from clause's it hangs but i get the following print out
SELECT 'TESTDB2' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB1.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB1.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]
                      
            
                   
		 SELECT 'TESTDB2' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB2.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB2.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]

Open in new window



and finally if i change all the @dbase to be dbase so code looks like this
SELECT '''+dbase+''' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM ' + dbase + '.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN ' + dbase + '.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]

Open in new window

it hangs but get the following
SELECT 'TESTDB1' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB1.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB1.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]
                      
            
                   
		 SELECT 'TESTDB2' as [DBASE]
				,a.[DET_NOMINALDR] as [Nominal Code]
				,a.[DET_DATE] as [Date]
				,a.[DET_PERIODNUMBR] as [Period]
				,a.[DET_YEAR] as [Year]
				,a.[DET_DESCRIPTION] as [Narrative]
				,a.[DET_NETT] as [D/C]
				,b.[NT_GROSS_CONTRA] as [Contra Entry]

           FROM TESTDB2.[dbo].[SL_PL_NL_DETAIL] a
           INNER JOIN TESTDB2.[dbo].[NL_TRANSACTIONS] b
           ON a.[DET_HEADER_REF]  = b.[NT_HEADER_REF]

Open in new window


0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36532418
What does the execution plan look like?  Why are you using dynamic query?
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 36536512
That's no weird. Depends on how many records have in each table and wich are the datatypes for the columns that you are using for join. Also check if those columns are primary key in each table.

Good luck.
0
 

Author Closing Comment

by:deanmachine333
ID: 36566609
Sorry my mistake should have been joining on different columns

should have been

D.DET_HEADER_KEY = T.NT_HEADER_KEY
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

782 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