Solved

SQL SP query just hanging? no results?

Posted on 2011-09-09
13
428 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now