Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL SP query just hanging? no results?

Posted on 2011-09-09
13
Medium Priority
?
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

660 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