Why Am I Not Getting Data?

1

Assuming all the databases and tables are in place, Can someone tell me why this script is not producing any data. It only shows columns, but not data. If I run this script outside of the CURSOR is runs and produces data. I am trying to run the script on all of the user databases on the instance, but again, it does not produce any data when run within the cursor. It shows for every database (0 row(s) affected)

DECLARE @DBNameVar NVARCHAR(300) DECLARE @Statement NVARCHAR(1000)
Declare GetDatabaseSize CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN GetDatabaseSize
FETCH NEXT FROM GetDatabaseSize INTO @DBNameVar

WHILE @@FETCH_STATUS = 0
BEGIN
Set @statement = 'USE ' + @DBNameVAR
Exec sp_executesql @statement
Select cast(round(sum(cast(ImportSize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))
as SourceSize, ImportID,
cast(round(sum(cast(TotalSize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))
as ExpandedSize, DateAdded
From Dataimports Group By ImportID, DateAdded

	FETCH NEXT FrOM GetDatabaseSize INTO @DBNameVar

END

CLOSE GetDatabaseSize
DEALLOCATE GetDatabaseSize

2

You have to run the USE and the SELECT query in the same session. Right now the USE is running in a separate session.

SET @statement = 'USE ' + @DBNameVAR + '; SELECT....'
EXEC sp_executesql @statement

3

Tara,

That was the answer, but one last question. I am trying to query on a date range (Month of July), but it gives me a syntax error each time. After the FOM Dataimports statements I am including a Where Clause to query on specific dates, but it continually gives me a syntax error. Am I putting it in the wrong place.

FROM Dataimports WHERE datecreated BETWEEN '07/01/2016' AND '07/31/2016'. If I use single quotes I get a syntax error on the date itself. If I use double quotes, it gives the following error:

Invalid object name 'Dataports'. What am I doing wrong here. I just want to query on the dates I mentioned. Thanks.

4

Always use PRINT @statement to debug what's going on with the string you are building.

Well where does Dataimports exist? Is it in the database you are switching to in the USE?

5

Yes, the script is going through every user database, and each user database contains the table 'dataimports'.

6

Try this instead:

WHERE datecreated BETWEEN CAST('2016-07-01' AS DATE) AND CAST('2016-07-31' AS DATE)
7

Using single quotes, I get "Incorrect syntax near '2016'. Using double quotes 'Invalid object name Dataimports.

8

Show us what you have and show us what PRINT displays.

9 
DECLARE @DBNameVar NVARCHAR(300) DECLARE @Statement NVARCHAR(1000)
Declare GetDatabaseSize CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN GetDatabaseSize
FETCH NEXT FROM GetDatabaseSize INTO @DBNameVar

WHILE @@FETCH_STATUS = 0
BEGIN
	Set @statement = 'USE ' + @DBNameVAR +
	' Select cast(round(sum(cast(ImportSize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2)) 
	as SourceSize, ImportID,
	cast(round(sum(cast(TotalSize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))
	as ExpandedSize, DateAdded
	From Dataimports
	WHERE datecreated BETWEEN CAST(''2016-07-01'' AS DATE) AND CAST(''2016-07-31'' AS DATE)
	Group By ImportID, DateAdded'
	Exec sp_executesql @statement
	FETCH NEXT FrOM GetDatabaseSize INTO @DBNameVar
END

CLOSE GetDatabaseSize
DEALLOCATE GetDatabaseSize

Please note that the dates are incapsulated with two single quotes (two before the first date and two after)

10

Here is my query I am running, the Print @statement, and the error I'm getting now:

DECLARE @DBNameVar NVARCHAR(300)DECLARE @Statement NVARCHAR(1000)
Declare GetDatabaseSize CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb', 'TunnelVision', 'RB8', 'ReportServer', 'TPI_TV', 'ReportServerTempDB', 'TVNSRLHashes')

OPEN GetDatabaseSize
FETCH NEXT FROM GetDatabaseSize INTO @DBNameVar

WHILE @@FETCH_STATUS = 0
BEGIN
Set @statement = 'USE ' + @DBNameVAR +
'
(Select cast(round(sum(cast(filesize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))as ExportTotalSize,
E.ExportName, E.ExportDate, EX.Exportid from dbo.files F WHERE f.datecreated BETWEEN CAST(''2016-07-01'' AS DATE) and CAST(''2016-07-31'' AS DATE)
Inner Join dbo.exportsets EX on F.FileID = EX.Fileid
Inner Join dbo.exports E on EX.ExportID = E.Exportid
Group by f.datecreated,E.ExportDate,Ex.Exportid, E.ExportName
Order By E.ExportDate Asc)'
PRINT @statement
Exec sp_executesql @statement

	FETCH NEXT FrOM GetDatabaseSize INTO @DBNameVar

END

CLOSE GetDatabaseSize
DEALLOCATE GetDatabaseSize

Print @statement
USE TVDB314
(Select cast(round(sum(cast(filesize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))as ExportTotalSize,
E.ExportName, E.ExportDate, EX.Exportid from dbo.files F WHERE f.datecreated BETWEEN CAST('2016-07-01' AS DATE) and CAST('2016-07-31' AS DATE)
Inner Join dbo.exportsets EX on F.FileID = EX.Fileid
Inner Join dbo.exports E on EX.ExportID = E.Exportid

	Group by f.datecreated,E.ExportDate,Ex.Exportid, E.ExportName 
	Order By E.ExportDate Asc)

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Inner'.

11

Remove the parentheses around the query. Notice how bitsmed's version doesn't have those outer parentheses. Next move the joins to after the FROM. You can't just stick the joins wherever in a query.

FROM Table1
INNER JOIN ...
INNER JOIN...
...

12

I'm at a lost right now. Moved the Join Statements after the FROM. Now getting a syntax error "Incorrect syntax near the keyword 'Order'.

Set @statement = 'USE ' + @DBNameVAR +
'
(Select cast(round(sum(cast(filesize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))as ExportTotalSize,
E.ExportName, E.ExportDate, EX.Exportid from dbo.files F
Inner Join dbo.exportsets EX on F.FileID = EX.Fileid
Inner Join dbo.exports E on EX.ExportID = E.Exportid
WHERE f.datecreated BETWEEN CAST(''2016-07-01'' AS DATE) and CAST(''2016-07-31'' AS DATE)
Group by f.datecreated,E.ExportDate,Ex.Exportid, E.ExportName
Order By E.ExportDate Asc'
PRINT @statement
Exec sp_executesql @statement

	FETCH NEXT FrOM GetDatabaseSize INTO @DBNameVar
13

You didn't remove the first parenthesis.

Set @statement = 'USE ' + @DBNameVAR +
'
Select...

1 Like
14

Ok...That was my mistake. Thank you so much. The Query is working now.

15

I suggest using datatype sysname - i.e.

DECLARE @DBNameVar sysname

otherwise you are just guessing as to the necessary width

Likewise with

personally I would declare that with a much more generous width, to make sure that the concatenated string didn't get truncated in future. 4000 is a "boundary-size" (or 8000 for Varchar) so if you are sure your string will never exceed that then I suggest using 4000 - there is no penalty for using 4000 instead of, say, 1000. If you might exceed 4000 characters then use NVARCHAR(MAX) to be sure to always have enough space.

Rather than

which will presumably break any time someone adds a database which does not include the files, exports, exportsets tables? could you not process all DBs only if they DO include those tables?