Databases
StackOverflow2013
Postsdbo
POSQL Server Returns a "Query Executed With Errors" and doesn't finish executing though told
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Returns a "Query Executed With Errors" and doesn't finish executing though told
    primarykey
    data
    text
    <p>I'm no expert in SQL Server and this question may be a little convoluted, but I have to try something!</p> <p>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.</p> <p>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.</p> <p>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..</p> <p>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. </p> <p>This is just a query run in SQL Server - it's not part of a bigger project, it stands alone.</p> <pre><code>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 </code></pre> <p>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?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1261648
    1. USuser1261648
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you saying that you are getting the error in **both** environments, but in Dev it continues after the error and in Prod it stops right after? If so, are you using the same tools to run your SQL script in both environments?
      singulars
      1. POSQL Server Returns a "Query Executed With Errors" and doesn't finish executing though told
      1. USPM 77-1
    2. COYou might want to check the "Remote query timeout" property on your production server. By default it is set to 10 minutes. Depending on the load on your server at the time your query is run that might account for why you see a variance of the number of databases it gets through before ending. You can access the timeout property through the SQL Enterprise Manager GUI, select server, right-click, select properties, and click Connections on the Server Properties dialog.
      singulars
      1. POSQL Server Returns a "Query Executed With Errors" and doesn't finish executing though told
      1. This table or related slice is empty.
    3. COAnd make sure that SET XACT_ABORT is the same in both.
      singulars
      1. POSQL Server Returns a "Query Executed With Errors" and doesn't finish executing though told
      1. USRBarryYoung
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload