SQL SP query just hanging? no results?

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

deanmachine333Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
deanmachine333Author Commented:
Quick note i have tested the sub query and the join works fine but not in the SQL string???
0
 
LowfatspreadCommented:
have you printed out the select statement before executing it and confirmed it looks correct?
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
deanmachine333Author Commented:
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
 
LowfatspreadCommented:
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
 
deanmachine333Author Commented:
nope it enters the correct value for the 'DBASE'  when i print it out.
0
 
John_BonCommented:
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
 
Anthony PerkinsCommented:
>>nope it enters the correct value for the 'DBASE'  when i print it out. <<
You may want to double check that.
0
 
deanmachine333Author Commented:
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
 
Bob LearnedCommented:
What does the execution plan look like?  Why are you using dynamic query?
0
 
deanmachine333Author Commented:
Sorry my mistake should have been joining on different columns

should have been

D.DET_HEADER_KEY = T.NT_HEADER_KEY
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.