@set sql6 = need to put a join table in here...HELP!!!!

[jtammyg]

hi!

Im running the folowing code to loop through all the databases and
insert the results in a temp table.


In this case I need to run a join within a @sql string, but it does
not work.


Any ideas on how I can achieve this?


Here is my code:


if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_files_to_review '))
DROP TABLE temp_files_to_review


declare @sql6 nvarchar(4000)
declare @db6 varchar(300)


set @db6 = 'master'
declare cDB6 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '09/01/2007' and sdb.name like 'client_%'
ORDER BY name


CREATE TABLE temp_files_to_review([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Create Date] datetime,
[To review File Count]
int,
[To review File Size (MB)] decimal(10,4)
)


open cDB6
FETCH NEXT FROM cDB6 INTO @db6
while (@@fetch_status = 0)
begin
set @sql6 = 'SELECT @@SERVERNAME as ''[Server
Name]'', ' +
'''' + @db6 + '''' +
' as ''[Database Name]'', ' +
'NULL as ''[Create Date]'',' +
' count(id) as ''[To review File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[To review File
Size (MB)]''' +
' FROM ' + @db6 + '.dbo.from filo_files where id in (select
documentkey from ' + @db6 + '.dbo.semantica_corpora where projectkey
in (select distinct id from ' + @db6 + '.dbo.filo_assignments where
status <> ''removed''))'


INSERT temp_files_to_review


EXEC sp_executesql @sql6


fetch cDB6 into @db6
end
close cDB6
deallocate cDB6


Thanks in advance.


Tammy

 

[bborissov]

I don't see any joins here. What didn't work?
BTW you could make code more readable if youput it in [ code ] [ /code ] tags (w/o spaces)

IF exists (select [id] from master..sysobjects where [id] = OBJECT_ID('master..temp_files_to_review '))
   DROP TABLE temp_files_to_review

declare @sql6 nvarchar(4000)
declare @db6 varchar(300)

set @db6 = 'master'
declare cDB6 cursor for
SELECT name from master..sysdatabases sdb
       WHERE sdb.crdate >= '09/01/2007' and sdb.name like 'client_%'
       ORDER BY name


CREATE TABLE temp_files_to_review([Server Name]              nvarchar(40),
                                  [Database Name]            nvarchar(100),
                                  [Create Date]              datetime,
                                  [To review File Count]     int,
                                  [To review File Size (MB)] decimal(10,4))


open cDB6
FETCH NEXT FROM cDB6 INTO @db6
while (@@fetch_status = 0)
       begin
           set @sql6 = 'SELECT @@SERVERNAME as ''[Server Name]'', ' +
                       '''' + @db6 + '''' +' as ''[Database Name]'', ' +
                       'NULL as ''[Create Date]'', ' +
                       'count(id) as ''[To review File Count]'', ' +
                       '(cast(Sum(length) as decimal)/1048576) as ''[To review File Size (MB)]''' +
                       ' FROM ' + @db6 + '.dbo.from filo_files where id in (select documentkey from ' +
                                                                                   @db6 + '.dbo.semantica_corpora where projectkey
                                                                                   in (select distinct id from ' + @db6 + '.dbo.filo_assignments where
                                                                                   status <> ''removed''))'

           INSERT temp_files_to_review
           EXEC(@sql6)
           fetch NEXT cDB6 into @db6
       end
close cDB6
deallocate cDB6

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jtammyg]

Hi Borislav!

Sorry I meant to say there are selects within selects and they do not seem to work.

This is the error message I get:


Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 1


I do not seem to be able to run either joins within a @sql string or nested selects.

How do I get around this issue?

Thanks a lot!

Tammy

 

[mrdenny]

What is outputted when you change the EXEC(@sql6) to print @sql6?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[gmmastros]

I see this...

[tt][blue]
' [!]FROM[/!] ' + @db6 + '.dbo.[!]from[/!] filo_files where id in (select documentkey from ' +
[/blue][/tt]

There are 2 ways to fix this problem, but it depends on your database.

1. Do you have a table named From? If so, they you need to put square brackets around the table name.

[tt][blue]
' [!]FROM[/!] ' + @db6 + '.dbo.[!][[/!]from[!]][/!] filo_files where id in (select documentkey from ' +
[/blue][/tt]

2. If (as I suspect, your actual table name is file_files, then just remove the second from.

[tt][blue]
' [!]FROM[/!] ' + @db6 + '[!].dbo.filo_files[/!] where id in (select documentkey from ' +
[/blue][/tt]


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

I'm looking at your query again and this line is probably the one causing the problem.

FROM ' + @db6 + '.dbo.from filo_files

FROM is a reserved word and can't be used at the name of a table without wrapping it in square brackets. Assuming that from isn't actually the name of the table the line should look like this.

FROM ' + @db6 + '.dbo.filo_files

If from is the name of the table it should look like this.

FROM ' + @db6 + '.dbo.[from] filo_files

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jtammyg]

Thank you so much! It's working now!!!!!

That was a typo.



Tammy