Solved

SQL SP query just hanging? no results?

Posted on 2011-09-09
13
430 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

Accepted Solution

by:
Vitor Montalvão earned 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 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