I'm no expert in SQL Server and this question may be a little convoluted, but I have to try something!
I'm running through each database on a server in order to gather some stats. In development of the correct SQL code, I was using a relatively small server (~150 databases). I get the result that the query has executed with errors, and this is fine - it's expected. All the stuff that needs to be done gets done and I get my results.
Now my problem is when I use the live server. This is granted, a little bigger with much more data. I don't mind my query taking a long time to run, my problem is that when it does run, eventually it stops (it could stop after 20 databases have been done, it could stop after 30 - it's never the same). It has the same Query executed with errors thing, but as I mentioned, this is fine - I've worked perfectly well with this error before (it's a table that's not in every database). So basically, the query, though it is not finished, stops anyway.
I'm just wondering, does anyone know what may be wrong? I'll post my query but I'm not sure if this is the problem, seeing as it's working fine on the dev server..
I'm tearing my hair out trying to figure this out - Google ain't being friendly today either. As I said, I'm no expert - I'm relatively new to real-life development so I could be doing something naive.
This is just a query run in SQL Server - it's not part of a bigger project, it stands alone.
declare @name nvarchar(100)
declare @sql varchar(2000)
declare @sql2 varchar (2000)
declare @repname nvarchar (200), @time DateTime, @pos int
declare @rn nvarchar (200), @ts DateTime, @p int
DECLARE GetDatabaseName CURSOR LOCAL FOR
SELECT Name FROM sys.sysdatabases WHERE name not in ('master', 'tempdb', etc') order by name
OPEN GetDatabaseName
FETCH NEXT FROM GetDatabaseName INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON
print @name
DROP table Results
DROP table TrueResults
DROP table temp
Create table Results (Position int, Name nvarchar(200), TimeStamp DateTime)
Create table TrueResults (Position int, Name nvarchar (200), TimeStamp DateTime)
Create table temp (Position int, Name nvarchar (200), TimeStamp DateTime)
ALTER TABLE Results
ALTER COLUMN Name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
set @sql2 = 'insert into Results select ROW_NUMBER()over (order by TimeStamp), Name, TimeStamp from ' + @name + '.dbo.MyTableName'
exec (@sql2)
Declare Curses CURSOR
FOR select Name, TimeStamp, Position from Results
Open Curses
while @@FETCH_STATUS = 0
BEGIN
fetch next from Curses
into @rname, @time, @pos
if exists (select * from Results where @rname= 'What I'm Looking For')
BEGIN
if exists (select * from Results where Position = @pos + 1 and Name != 'What I'm Looking For')
BEGIN
insert into temp (Position, Name, TimeStamp)
values (@pos, @rname, @time)
set @pos = (select temp.Position from temp, Results where temp.Position = Results.Position and Results.Position = @pos)+1
set @p = (select Position from Results where Position = @pos)
set @rn = (select Name from Results where Position = @pos)
set @ts = (select TimeStamp from Results where Position = @pos)
insert into TrueResults (Position, Name, TimeStamp)
values (@p, @rn, @ts)
END
END
END
CLOSE Curses
DEALLOCATE Curses
select @name
set @sql = 'select Name, count(Name) as RCount from TrueResults group by Name order by RCount desc'
exec (@sql)
FETCH NEXT FROM GetDatabaseName INTO @name
END
CLOSE GetDatabaseName
DEALLOCATE GetDatabaseName
GO
I've tried the things mentioned here to no avail. I did however add in a clause to only return results from the last 7 days - this made the query run for longer. Changed it to 1 day and it ran the whole way through. (The table has a timestamp attribute). So now I'm wondering if it's to do with the sheer volume of data that's being returned? I wasn't aware that there was any limit, as such. Could something like that cause a problem like this, though? Could it be a memory issue?
