join within a string?

[jtammyg]

hi!

I am having an error

Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near ' .dbo.filo_files b on a.[id] = b.ownerkey WHERE b.parentFileKey Is Null'.


I don't see what is wrong.

Maybe I cannot have a join in a string?


The code is below.

Thanks!





--1) We are creating and populating the table temp_Source_Media_file_count with SOURCE MEDIA file information.
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID ('master..temp_Source_Media_file_count '))
DROP TABLE temp_Source_Media_file_count


declare @sql1 nvarchar(4000)
declare @db1 varchar(300)



set @db1 = 'master'
declare cDB1 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '10/1/2007' and sdb.name like 'client_%'
ORDER BY name


CREATE TABLE temp_Source_Media_file_count([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[Custodian Name] nvarchar(300),
[Create Date] datetime,
[Original File Count] int,
[Original File Size (MB)] decimal(10,4)
)



open cDB1
FETCH NEXT FROM cDB1 INTO @db1
while (@@fetch_status = 0)
begin

SET @sql1 = 'SELECT SELECT @@SERVERNAME as ''[Server Name]'', ' +
'''' + @db1 + '''' + ' as ''[Database Name]'',' +
'a.name as ''[Custodian Name]'',' +
'NULL as ''[Create Date]'',' +
'count(*) as ''[Original File Count]'',' +
'(cast(Sum(ff.length) as decimal)/1048576) as ''[Original File Size (MB)]''' +
'FROM ' + @db1 + '.dbo.filwners a left join ' + @db1 + ' .dbo.filo_files b on a.[id] = b.ownerkey WHERE b.parentFileKey Is Null GROUP BY a.name'


INSERT temp_Source_Media_file_count

EXEC sp_executesql @sql1


fetch cDB1 into @db1
end
close cDB1
deallocate cDB1



UPDATE temp_Source_Media_file_count
SET temp_Source_Media_file_count.[Create Date]
= (SELECT crDate
FROM master..sysdatabases AS C1
WHERE C1.name = temp_Source_Media_file_count.[Database Name])





select * from temp_Source_Media_file_count

 

[SQLSister]

Try doing a print @sql statment andsee if you have made a mistake in creating your dynamic SQl

"NOTHING is more important in a database than integrity." ESquared

 

[jtammyg]

SQLSister,

It still gives me the error in the select statement.


Any other ideas here?

Can a join be done within the string or not?

Thanks!

Tammy

 

[gmmastros]

>> Can a join be done within the string or not?

Yes.

The problem is that you have a fatal flaw in your code where you are building the string, and it has nothing to do with the joins.

Take a real close look at this line of code. I bet the problem jumps right out at you.

        SET @sql1 = '[red]SELECT[/red] [blue]SELECT[/blue] @@SERVERNAME as  ''[Server Name]'', '      +

-George

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

 

[jtammyg]

George,

I already have found the problem. It was the one you pointed out AND

'(cast(Sum(ff.length) as decimal)/1048576) as ''[Original File Size (MB)]''' +


see the alias ff in length?

Tammy

 

[gmmastros]

I'm not surprised. I couldn't test the query because I don't have the same tables you have.

I'm curious. Why do you create a permanent table in your database? You know that you could use a temporary table instead.

Remove the line at the top that drops the table, and then replace:

temp_Source_Media_file_count

With:

[!]#[/!]temp_Source_Media_file_count



-George

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