error Msg 156 and msg 170

Hi!
I am trying to loop through all the databases using cursor.
Here is my stored proc
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_Assignments_file_count '))
DROP TABLE temp_Assignments_file_count
declare @.sql nvarchar(4000)
declare @.db varchar(300)
set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
ORDER BY name
CREATE TABLE temp_Assignments_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Title] nvarchar(100),
[File Count] int,
[File Size (MB)] decimal(10,4),
)
open cDB
FETCH NEXT FROM cDB INTO @.db
while (@.@.fetch_status = 0)
begin
SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
Name]'', ' +
'''' + @.db + '''' + '
as ''[Database Name]'', ' +
'max(b.title) as ''[Title]'',' +
'count(*) as ''[File Count]'',' +
'round(cast(sum(length) as decimal)/1048576/1024,10) as
''[File Size]''' +
'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
@.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
'where a.id in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments' +
'where lastprocesskey in (select id from ' + @.db +
'.dbo.filo_processlog where task = ''Create Assignments'' and
starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
'and a.id not in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments where lastprocesskey in' +
'(select id from ' + @.db + '.dbo.filo_processlog where task = ''Create Assignments'' and starttime < ''10/01/2007'')))'
INSERT temp_Assignments_file_count
EXEC sp_executesql @.sql
fetch cDB into @.db
end
close cDB
deallocate cDB
select * from temp_Assignments_file_count
I am getting the following error messages:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
any help would be appreciated.
TGDo:
PRINT @.sql
before you try to execute what you have in the variable and you will find a lot of problems with the
query that you built. Based on that you can debug your code so it produces a valid query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<jtammyg@.gmail.com> wrote in message news:1192645043.161544.136800@.v29g2000prd.googlegroups.com...
> Hi!
> I am trying to loop through all the databases using cursor.
> Here is my stored proc
> if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
> ('master..temp_Assignments_file_count '))
> DROP TABLE temp_Assignments_file_count
>
> declare @.sql nvarchar(4000)
> declare @.db varchar(300)
>
> set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
> ORDER BY name
>
> CREATE TABLE temp_Assignments_file_count([Server Name]
> nvarchar(40),
> [Database Name]
> nvarchar(100),
> [Title] nvarchar(100),
> [File Count] int,
> [File Size (MB)] decimal(10,4),
> )
>
> open cDB
> FETCH NEXT FROM cDB INTO @.db
> while (@.@.fetch_status = 0)
> begin
> SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
> Name]'', ' +
> '''' + @.db + '''' + '
> as ''[Database Name]'', ' +
> 'max(b.title) as ''[Title]'',' +
> 'count(*) as ''[File Count]'',' +
> 'round(cast(sum(length) as decimal)/1048576/1024,10) as
> ''[File Size]''' +
> 'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
> @.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
> 'where a.id in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments' +
> 'where lastprocesskey in (select id from ' + @.db +
> '.dbo.filo_processlog where task = ''Create Assignments'' and
> starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
> 'and a.id not in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments where lastprocesskey in' +
> '(select id from ' + @.db + '.dbo.filo_processlog where task => ''Create Assignments'' and starttime < ''10/01/2007'')))'
>
> INSERT temp_Assignments_file_count
> EXEC sp_executesql @.sql
>
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
>
> select * from temp_Assignments_file_count
>
>
> I am getting the following error messages:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> (0 row(s) affected)
>
> any help would be appreciated.
> TG
>

Error message when using SQLCE on WM2005 - ERROR MSG: Not enough storage is available to complet

Hi All,

I got this Message from my SQLCE.

I have a loop that insert rows into the DB,

Once in a few times I get this error message on one of the rows.

But, after that the next row is insert with no problem

Any one knows this reason?

The message is:

ERROR MSG: Not enough storage is available to complete this operation

The SQL was:

SQL Insert ERROR->

INSERT INTO OrderDetails (fileTimeStamp,orderPPCNum,storeID,itemID,itemName,quantityToPick,colorDescription,colorDetails,bases,diameter,axis,referenceNum,remarks,quantityPicked,quantityOrdered,status,datetime) VALUES('08211640','000121','0001','65700300000','-300 ????? ??????','1','','','','','0','143145','','','',0,getdate())

ERROR MSG: Not enough storage is available to complete this operation.

This error means: An out of memory condition occurred.

|||

Thanks for your answer..

The wierd thing is that I continue to insert many rows into the DB with no problem.

Once in few days I get a problem of one row. I have no problem with the data entry since I insert it again after the problem, while running the procedure again without any change and with no error.

What else can couse that?

If I have this error, is it means that I don't have enough storage and I was assuming that all other rows on that senario were not suppose to enter the DB, but they did.

|||

Another question,

If its the only reason the server send this error message, what should I do before starting to insert records into the DB?

Is there a way to clean the available storage area?