again problem with multiple joins within a string...HELPPP!

[jtammyg]

here is the code:

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


declare @sql nvarchar(4000)
declare @db varchar(300)



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


CREATE TABLE temp_startime([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[Files ID] int,
[Start Date] datetime,
[Count] int
)



open cDB
FETCH NEXT FROM cDB INTO @db
while (@@fetch_status = 0)
begin

SET @sql = 'SELECT @@SERVERNAME as ''[Server Name]'', ' +
'''' + @db + '''' + ' as ''[Database Name]'',' +
'a.[id] as ''[Files ID]'',' +
'd.startTime as ''[Start Date]'',' +
'count(*) as ''[Count]''' +
'FROM ' + @db + '.dbo.filo_files a join ' + @db + '.dbo.semantica_corpora b on a.[id] = b.documentkey ' +
'join ' + @db + '.dbo.filo_assignments c on on b.projectkey = c.[id] join ' + @db + '.dbo.filo_processlog d on c.lastprocesskey = d.[id] WHERE task = ''Create Assignments''' +
'and d.startTime >= ''9/1/2007'' AND d.startTime <= ''9/30/2007'' group by a.[id], d.startTime'



/*
select a.[id],d.starttime
from filo_files a
join semantica_corpora b on a.[id] = b.documentkey
join filo_assignments c on b.projectkey = c.id
join filo_processlog d on c.lastprocesskey = d.id
where d.task = 'Create Assignments' and d.starttime >= '9/1/2007' AND d.starttime <= '9/30/2007'
group by a.[id],d.starttime
*/



INSERT temp_startime

EXEC sp_executesql @sql


fetch cDB into @db
end
close cDB
deallocate cDB



select * from temp_startime




and I am getting this error message:

Server: Msg 170, Level 15, State 1, Line 39
Line 39: Incorrect syntax near 'and d.startTime >= '9/1/2007' AND d.startTime <= '9/30/2007' group by a.[id], d.startTime'.



I'm still struggling with this stuff!!!!

Thank you for your help in advanced!!!!

Tammy

 

[LNBruno]

Hey, Tammy.

Any time I run into a problem like this, I always print out the dynamic SQL.

Using PRINT @SQL generates the following results:

SELECT @@SERVERNAME as  '[Server Name]'
, 'master' as  '[Database Name]'
,a.[id] as '[Files ID]'
,d.startTime as '[Start Date]'
,count(*) as '[Count]'
FROM master.dbo.filo_files a 
join master.dbo.semantica_corpora b 
on a.[id] = b.documentkey 
join master.dbo.filo_assignments c 
[red][b]on on [/b][/red]b.projectkey = c.[id] 
join master.dbo.filo_processlog d 
on c.lastprocesskey = d.[id] 
WHERE task = 'Create Assignments'
and d.startTime >= '9/1/2007'
AND d.startTime <= '9/30/2007' 
group by a.[id], d.startTime

Even though the error is pointing you to the dates in the where clause, look at the bold red. You also seem to have some extraneous quotes with your aliases.

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[gmmastros]

In your other thread, SQLSister suggested that you print the @sql value. When you do this, the query will appear in the messages window. You can then copy/paste this to a new query window. When you try to run the query, you will undoubtedly get an error. The difference is that it will be a lot easier to debug the problem.

Truth is, I did this. I know what your problem is. However, I'd rather not 'just give you' the answer. I encourage you to find the problem yourself.

Here's what you should do.

1. After building the @sql variable, add a line: Print @sql
2. At the bottom of the Query Analyzer window, you will see a messages tab. The query that you are trying run will be displayed.
3. Copy/Paste the query to a new window.
4. Try running it.
5. You will likely see what the problem is.

Good luck.

-George

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

 

[jtammyg]

Hi LNBruno

Thanks so much for your reply!

how do u print the @sql?

sorry I know is a stupid question.

Thanks a lot!!!!!!!!!!!!!!!!!

Tammy

 

[LNBruno]

...with the PRINT statement:

declare @sql nvarchar(4000) 
declare @db varchar(300)
set @db = 'master' 

        SET @sql = 'SELECT @@SERVERNAME as  ''[Server Name]'', '              + 
                     '''' + @db + '''' + ' as  ''[Database Name]'','            +
            'a.[id] as ''[Files ID]'','                        +
            'd.startTime as ''[Start Date]'','                            + 
        'count(*) as ''[Count]'''                        +                    
            'FROM ' + @db + '.dbo.filo_files a join ' + @db + '.dbo.semantica_corpora b on a.[id] = b.documentkey '     +
        'join ' + @db + '.dbo.filo_assignments c on on b.projectkey = c.[id] join ' + @db + '.dbo.filo_processlog d on c.lastprocesskey = d.[id] WHERE task = ''Create Assignments'''    +
        'and d.startTime >= ''9/1/2007'' AND d.startTime <= ''9/30/2007'' group by a.[id], d.startTime'

[B][RED]PRINT @SQL[/RED][/B]

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[onpnt]

you can open multiple browsers at a time. Some even have cool tabs

_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[SQLDenis]

another way to print

DECLARE @SQL varchar(666)

SELECT @SQL = 'Select * from Bla where Bla <> ''bla'''
RAISERROR (@SQL,10,1) WITH NOWAIT

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jtammyg]

Hi!

I tried printing @sql1 and it does not help:


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


declare @sql nvarchar(4000)
declare @db varchar(300)



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


CREATE TABLE temp_startime([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[Files ID] int,
[Start Date] datetime,
[Count] int
)



open cDB
FETCH NEXT FROM cDB INTO @db
while (@@fetch_status = 0)
begin

SET @sql = 'SELECT @@SERVERNAME as ''[Server Name]'', ' +
'''' + @db + '''' + ' as ''[Database Name]'',' +
'a.[id] as ''[Files ID]'',' +
'd.startTime as ''[Start Date]'',' +
'count(*) as ''[Count]''' +
'FROM ' + @db + '.dbo.filo_files a join ' + @db + '.dbo.semantica_corpora b on a.[id] = b.documentkey ' +
'join ' + @db + '.dbo.filo_assignments c on b.projectkey = c.[id] join ' + @db + '.dbo.filo_processlog d on c.lastprocesskey = d.[id] WHERE task = ''Create Assignments''' +
'and d.startTime >= ''9/1/2007'' AND d.startTime <= ''9/30/2007'' group by a.[id], d.startTime'



/*
select a.[id],d.starttime
from filo_files a
join semantica_corpora b on a.[id] = b.documentkey
join filo_assignments c on b.projectkey = c.id
join filo_processlog d on c.lastprocesskey = d.id
where d.task = 'Create Assignments' and d.starttime >= '9/1/2007' AND d.starttime <= '9/30/2007'
group by a.[id],d.starttime
*/



INSERT temp_startime

EXEC sp_executesql @sql


fetch cDB into @db
end
close cDB
deallocate cDB


select * from temp_startime

--1) We are creating and populating the table temp_Source_Media_file_count with SOURCE MEDIA file information.
--This populates the Excel spreadsheet called 'AUTOMATED BILLING REPORT.xls'
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.name like 'client_%'
ORDER BY name


CREATE TABLE temp_Source_Media_file_count([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[Create Date] datetime,
[Files ID] int,
[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 @@SERVERNAME as ''[Server Name]'', ' +
'''' + @db1 + '''' + ' as ''[Database Name]'',' +
'NULL as ''[Create Date]'',' +
'[ID] as ''[Files ID]'',' +
'count(*) as ''[Original File Count]'',' +
'(select (cast(sum(length) as decimal)/1048576) from '+ @db1 + '.dbo.filo_files where rootcontainerkey is null and type = ''pst'') as ''[Original File Size (MB)]'''+
'FROM ' + @db1 + '.dbo.filo_Files where parentfilekey is not null group by [ID]'
*/


SET @sql1 = 'SELECT @@SERVERNAME as ''[Server Name]'', ' +
'''' + @db1 + '''' + ' as ''[Database Name]'',' +
'NULL as ''[Create Date]'',' +
'[ID] as ''[Files ID]'',' +
'count(*) as ''[Original File Count]'',' +
'cast(sum(length) as decimal)/1048576 as ''[Original File Size (MB)]''' +
'FROM ' + @db1 + '.dbo.filo_Files where ' + @db1 + ' in (select [Database Name] from temp_startime) and parentfilekey is not null group by [ID]'

print @sql1



I get the following error:


Server: Msg 170, Level 15, State 1, Line 49
Line 49: Incorrect syntax near '@sql1'.


Thanks a lot guys for all your help!!!!!!


Tammy

 

[SQLDenis]

missing END

print @sql1
end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Doesn't look like you have a FETCH NEXT, END, or CLOSE for your cursor.

Also, in debugging, you need to select (highlight) a block of code to run instead of simply running the entire script. Look at the example I posted.

< M!ke >
[small]"Oops" is never a good thing.[/small]