Query That Never Finishes In SQL2005 But Worked Fine In 2000
Nov 14, 2007
Hi,
I have a query which used to run fine on a rubbish SQL 2000 box in about a minute, but with SQL 2005 (SP2) it never finishes, even when left overnight. No errors in the logs or anything. It is the same database which was backed up from SQL 2000 and restored into 2005. Does anybody have any ideas?
Cheers
Steve
SELECT DISTINCT R1.RowVersionId, R2.EnumID AS A, R2.EnumID AS B, R4.EnumID AS C, R6.EnumID AS D, R8.EnumID AS E, R10.EnumID AS F, R12.EnumID AS G, R14.EnumID AS H
FROM
RowRuns AS R1
INNER JOIN XRunConfigEnum AS R2 ON R1.RunVersionID = R2.RunVersionId
INNER JOIN RowRuns AS R3 ON R1.RowVersionId=R3.RowVersionId
INNER JOIN XRunConfigEnum AS R4 ON R3.RunVersionID = R4.RunVersionId
INNER JOIN RowRuns AS R5 ON R1.RowVersionId=R5.RowVersionId
INNER JOIN XRunConfigEnum AS R6 ON R5.RunVersionID = R6.RunVersionId
INNER JOIN RowRuns AS R7 ON R1.RowVersionId=R7.RowVersionId
INNER JOIN XRunConfigEnum AS R8 ON R7.RunVersionID = R8.RunVersionId
INNER JOIN RowRuns AS R9 ON R1.RowVersionId=R9.RowVersionId
INNER JOIN XRunConfigEnum AS R10 ON R9.RunVersionID = R10.RunVersionId
INNER JOIN RowRuns AS R11 ON R1.RowVersionId=R11.RowVersionId
INNER JOIN XRunConfigEnum AS R12 ON R11.RunVersionID = R12.RunVersionId
INNER JOIN RowRuns AS R13 ON R1.RowVersionId=R13.RowVersionId
INNER JOIN XRunConfigEnum AS R14 ON R13.RunVersionID = R14.RunVersionId
WHERE
((R2.ParamID='ee72510e-3bab-49f6-1ff9-4d09cbe8670a' AND (R2.EnumID = '1a2868fb-72ef-e1d3-e79d-fbb5814ab481')))
AND
((R4.ParamID='7aadb3a4-3d13-8e0d-bfa4-4243ed1fdb35' AND (R4.EnumID = '745fb00c-0b16-7b4e-bf8f-da0f46777ca0')))
AND
((R6.ParamID='8c9aee3a-df1f-6ec5-131a-8fa0309ce1ff' AND (R6.EnumID = 'c7af1456-56bc-ba9c-f1e4-95cfd5542d10')))
if have a problem with a SELECT query that works very fine on SQL Server 2000 but not on 2005. I've transfered my db by creating a full backup and restoring the db on 2005. The db is working except this problem.
When i start executing it doesn't finish. I waited a couple of minutes. On 2000 it only takes about 6 seconds to run.
Here it is:
Code Snippet
SELECT * FROM PPS_TERMbesttmpwhere PPS_TermBestTmp.BestNr + CONVERT(varchar(30),CAST(REPLACE(PPS_TermBestTmp.Pos1, ',', '.') AS float),2)
NOT IN (SELECT PPS_TermBest.BestNr + CONVERT(varchar(30), PPS_TermBest.Pos1,2) FROM PPS_TermBest) Any ideas?
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.
I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.
/* Update WeekEnding Date for current weeks WeeklySales Records */ Update WeeklySalesHistory set weekendingdate = (SELECT LastTransDateTime from ReplicationControl where TableName = 'WEEKHST') where weekendingdate is null
Weekly sales has approx 100,000,000 rows Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.
The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.
Hi there, I have a query with a rather large (46 table) 1:1 join. Technically, I'm creating a view then trying to select from that view. I seem to be able to create the view ok but cannot resolve a select from it. The error message is:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 191, Level 15, State 1, Line 1 Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'WHERE'.
This worked in 2000 without a problem
Here is the join:
FROM [HUSKY].USEP0712E.dbo.AGAF_us T1 INNER JOIN [HUSKY].USEP0712E.dbo.AGAM_us T2 ON T1.CODE = T2.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGASN_us T3 ON T1.CODE = T3.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBF_us T4 ON T1.CODE = T4.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBLK_us T5 ON T1.CODE = T5.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBM_us T6 ON T1.CODE = T6.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHF_us T7 ON T1.CODE = T7.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHM_us T8 ON T1.CODE = T8.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHSP_us T9 ON T1.CODE = T9.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIF_us T10 ON T1.CODE = T10.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIM_us T11 ON T1.CODE = T11.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIND_us T12 ON T1.CODE = T12.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNH_us T13 ON T1.CODE = T13.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNHF_us T14 ON T1.CODE = T14.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNHM_us T15 ON T1.CODE = T15.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOF_us T16 ON T1.CODE = T16.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOM_us T17 ON T1.CODE = T17.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOTH_us T18 ON T1.CODE = T18.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWF_us T19 ON T1.CODE = T19.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWHT_us T20 ON T1.CODE = T20.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWM_us T21 ON T1.CODE = T21.CODE INNER JOIN [HUSKY].USEP0712E.dbo.WH_AOH_us T22 ON T1.CODE = T22.CODE INNER JOIN [HUSKY].USEP0712E.dbo.WNH_AONH_us T23 ON T1.CODE = T23.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGAF_us T24 ON T1.CODE = T24.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGAM_us T25 ON T1.CODE = T25.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGASN_us T26 ON T1.CODE = T26.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBF_us T27 ON T1.CODE = T27.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBLK_us T28 ON T1.CODE = T28.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBM_us T29 ON T1.CODE = T29.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHF_us T30 ON T1.CODE = T30.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHM_us T31 ON T1.CODE = T31.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHSP_us T32 ON T1.CODE = T32.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIF_us T33 ON T1.CODE = T33.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIM_us T34 ON T1.CODE = T34.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIND_us T35 ON T1.CODE = T35.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNH_us T36 ON T1.CODE = T36.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNHF_us T37 ON T1.CODE = T37.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNHM_us T38 ON T1.CODE = T38.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOF_us T39 ON T1.CODE = T39.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOM_us T40 ON T1.CODE = T40.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOTH_us T41 ON T1.CODE = T41.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWF_us T42 ON T1.CODE = T42.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWHT_us T43 ON T1.CODE = T43.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWM_us T44 ON T1.CODE = T44.CODE INNER JOIN [HUSKY].USEP0712P.dbo.WH_AOH_us T45 ON T1.CODE = T45.CODE INNER JOIN [HUSKY].USEP0712P.dbo.WNH_AONH_us T46 ON T1.CODE = T46.CODE
There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17 123.17 first row BB29 493.19 second ZZ3 18947.1 third row is longer And in hex format: 00: 41 31 37 20 20 20 20 20 ”‚ 31 32 33 2E 31 37 20 20 A17 123.17 10: 66 69 72 73 74 20 72 6F ”‚ 77 0D 0A 42 42 32 39 20 first row™Ş—™BB29 20: 20 00 20 34 39 33 2E 31 ”‚ 39 20 20 73 65 63 6F 6E 493.19 secon 30: 64 0D 0A 5A 5A 33 20 20 ”‚ 20 20 20 31 38 39 34 37 d™Ş—™ZZ3 18947 40: 2E 31 20 74 68 69 72 64 ”‚ 20 72 6F 77 00 69 73 20 .1 third row is 50: 6C 6F 6E 67 65 72 ”‚ longer
I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).
The table is:
CREATE TABLE eng (
src varchar (512)
)
When i upload this file into SQL2000 using DTS or Import wizard, the table contains:
select src, substring(src,9,8), len(src) from eng < src ><substr> <len> A17 123.17 first row 123.17 25 BB29 493.19 22 ZZ3 18947.1 third row 18947.1 35
As one can see, everything was imported, including binary zeros. And though SELECT * in SSMS truncates strings upon reaching 0x00's, still all information is stored in tables - SUBSTRINGs show that.
When i upload this file into SQL2005 using SSIS or Import wizard the result is following: < src ><substr> <len> A17 123.17 first row 123.17 25 BB29 4 ZZ3 18947.1 third row 18947.1 25
This time table is half-empty - all characters behind binary zeros in respective rows are lost.
I stumbled upon this problem while migrating my DTSes to SSIS packages. Do you think there is some workaround, or i need to turn on some checkbox or smth else could help? Please...
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
DML T-SQL works fine. But When I try to run SELECT query with joins that involves large tables (1.5 mil rows in each) in 2 databases, it instantly goes into "runnable" status and never finishes. It never finishes even when I have top 1 clause included. When I run insert or update that involves same tables, it works just fine.
Server facts:
W2003 SS 2K5 SP2 Db Comp level 90 (for all db) MAXODP = 1 (8 processor box)
Recently moved databases from server that had SS 2000.
When this query was running on box with SS 2000, it took about a minute to finish, but it completed.
I already tried: changing MAXODP settings, using OPTION (maxdop 1) etc.
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
Hi, I have problem running this query. It will time out for me...My database are small just about 200 members.I have a site for swaping appartments (rental). my query should lookfor matchin a triangle. Like this member A -> B->CA give his appartment to B. B gives his appartment to C and finallyC gives his appartment to ASoo my query looks for matching parameters like rooms, location, sizeandsoo on..I have one table for existing appartments and one for "whantedappartments"and 1 table called "intresse" where members can store "yes" or "no" ifthey are interessted in a appartment.I also have a table called "omrade" to store locations of interest.Hope you can helpe me with some tip soo i can run this query in a fewseconds instead of 20-30 secThanks MSELECTF.medlemsNr as medlemsNr, F.lfId AS lfId, F.ort AS ort, F.gatuadressAS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,F.storlek ASstorlek,F.hyra AS hyra, count(F.medlemsNr) As hitsFROMmedlem08 A, medlem08 B, medlem08 C, lagenhetF08 D,lagenhetO08 E, lagenhetF08 F, lagenhetO08 G, lagenhetF08 H,lagenhetO08 IWHERED.rum >= I.rumMin AND D.rum <= I.rumMax ANDD.storlek >= I.storlekMin AND D.storlek <= I.storlekMax ANDI.hyraMax = 0" & " OR D.hyra <= I.hyraMax) ANDI.balkong = '" & "" & "' OR D.balkong = I.balkong) AND(I.badkar = '" & "" & "' OR D.badkar = I.badkar) AND(I.bredband = '" & "" & "' OR D.bredband = I.bredband) AND(I.hiss = '" & "" & "' OR D.hiss = I.hiss) AND(I.spis = '" & "" & "' OR D.spis = I.spis) AND(I.brf = '" & "" & "' OR D.brf = I.brf) ANDD.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) ANDF.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 wheremedlemsNr1=A.medlemsNr) ANDH.rum >= G.rumMin AND H.rum <= G.rumMax ANDH.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND(G.hyraMax = 0" & " OR H.hyra <= G.hyraMax) AND(G.balkong = '" & "" & "' OR H.balkong = G.balkong) AND(G.badkar = '" & "" & "' OR H.badkar = G.badkar) AND(G.bredband = '" & "" & "' OR H.bredband = G.bredband) AND(G.spis = '" & "" & "' OR H.spis = G.spis) AND(G.brf = '" & "" & "' OR H.brf = G.brf) ANDH.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) ANDF.rum >= E.rumMin AND F.rum <= E.rumMax ANDF.storlek >= E.storlekMin AND F.storlek <= E.storlekMax AND(E.hyraMax = 0" & " OR F.hyra <= E.hyraMax) AND(E.balkong = '" & "" & "' OR F.balkong = E.balkong) AND(E.badkar = '" & "" & "' OR F.badkar = E.badkar) AND(E.bredband = '" & "" & "' OR F.bredband = E.bredband) AND(E.hiss = '" & "" & "' OR F.hiss = E.hiss) AND(E.spis = '" & "" & "' OR F.spis = E.spis) AND(E.brf = '" & "" & "' OR F.brf = E.brf) ANDF.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) ANDA.medlemsNr=D.medlemsNr AND A.medlemsNr=E.medlemsNr ANDB.medlemsNr<>A.medlemsNr AND C.medlemsNr<>A.medlemsNr ANDB.medlemsNr<>C.medlemsNr ANDB.sparr<>1 AND C.sparr<>1 ANDA.typ=11 AND A.medlemsNr=" & session("medlemsNr") & " ANDB.medlemsNr=F.medlemsNr AND B.medlemsNr=G.medlemsNr ANDB.typ=11 AND A.triangel=1 AND B.triangel=1 AND C.triangel=1 AND " &_C.medlemsNr=H.medlemsNr AND C.medlemsNr=I.medlemsNr ANDC.typ=11 group by F.lfId, F.medlemsNr,F.ort,F.gatuadress,F.gatuNr,F.rum,F.storlek,F.hyra
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
I converted an Access db to SQL Server 2005 Express using FullConvert Enterprise and the conversion went well. I ran a few queries. When I logged in today and run the same exact query (saved it) I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'.
I googled the error and someone in another forum said to check ownership, that it would throw that error if another user who was not dbo tried to run a query. I checked and the owner is sa. I logged in as sa and get the same error.
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin declare @current_date datetime set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from ( ( select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min" from topcat.class_contact where status ='callback' and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) <> 0) order by callback_date asc --order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc ) union ( select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'callback' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) = 0) order by callback_date asc ) union ( select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'No Connect' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) order by callback_date asc ) union ( select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc ) ) contact_queue order by priority desc, recency desc, callback_date asc, frequency desc
I am able to select the average, max, etc of the variable in a simple select statement but when I do the following, it doesn't work. The reason I am doing the following is because I am calculating the average and such over a 5 min interval, then saving the output as one line. That is why I condense average, max, min, and stdev into one variable which I then output. why it won't run when I have it like this but will run when it is like this?
will run:
SELECT AVG(NacTemp), MAX(NacTemp),MIN(NacTemp), STDEV(NacTemp) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] between DATEADD(minute, -5, GETUTCDATE()) and GETUTCDATE()
won't run but I need it to:
DECLARE @now datetime SET @now = GETUTCDATE() --Universal Time DECLARE @timeint int SET @timeint = '5' --time interval in minutes DECLARE @time datetimeSET @time = (SELECT MIN([UTCDeviceTimeStamp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, -@timeint,@now) AND @now) -- Timestamp data will be saved as DECLARE @comma varchar(4) SET @comma = ', '
I have created a lot of reports using this technique, but this is the first one that doesn't work. As there is absolutely nothing special about it, I can't figure out what the issue is.
I have one dataset that uses parameters chosen from two other dataset results. One dataset result runs just fine and returns values in reporting services, but the other returns blank in reporting services. In Visual Studio, both datasets return data.
I cannot for the life of me figure out what I've done that gives this result as I've never encountered it before and use this method of parametization quite frequently.
The dataset in question doesn't even join any tables, it's a direct select distinct field"1" from table"a".
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
database has been recently upgraded from ms sql 2000 to ms sql 2005.are there anything I need to be aware after upgrading to ms sql 2005?for my experience, i got an error if i use column alias in ORDER BYclause which was fine on ms sql 200.thanks
I need to make some changes to tables in a database. To do this requires that I "alter table" and "drop constraint" All constraints drop properly, except one. The last one will churn away forever in query analyzer and never drop. I am unable to make the necessary changes until this completes. I am guessing that there is a data issue that is keeping the constraint from being dropped. Any ideas on how to proceed? Thanks!
I have an activex script which executes a query that never finishes. If I recompile the query in Management Studio before I run this step, then the step will *usually* work fine. This is the last step in a very long update process so the data has changed a great deal when this step executes.
The query always works fine in Management studio btw.
I am using BI studio to run the package.
I have tried placing a step before this that marks the query for recompilation but that doesnt seem to work. Any ideas how to resolve this?
Dear All,I am developing a network application in asp.net. The database is in local machine having sql 2005. But the user database is already existent. So I am accessing that database which is sql2000. does it give any problem while connecting from sql2005 machine to sql2000. If yes it is giving me error as follows:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)connection string is :"Data Source=192.168.1.16,1433;Network Library=DBMSSOCN;Initial Catalog=mycatalog;User ID=myuserid;Password=mypwd;" Please correct me if I am wrong. Thanks and Regards.Fazal
I have a package which truncates files, fills them with data andshould send a mail with the files as attachment when it finishesrunning. In between, when the task is still running mail is sent withempty files. When I take a look at the directory (at the end of thetask), where the files are created I find out that the files are notempty.(e.g. in task: doA->doB->doC->SendMail. But instead of waiting tilldoB->doC finishes, it does the following: doA->SendMail ... may bebecause doB takes long.)Can I indicate anywhere that it should delay the mail until at the endof the task?Thanks in advance.Harp
Here is the situation I'm trying to set-up a package to ftp some files down if that part fails email me but if successful rename the 2 files to something else.
so what I set-up was a FTP tasks with the remote path set as a expresion since I needed to Download todays files. no problem there have that part working.
so then I set a notifaction event with failure constraint to email me. pretty easy so far.
then I set 2 tasks to rename the 2 files downloaded I'm sure I could do this part with a loop but what seperate tasks so I knew if any failed or could set-up notifaction tasks if I wanted. in the 2 rename tasks I set the sourcepath to an expression so it would know to read todays files and they could change from day to day.
then the destination part is hard coded for now to a file name.
now here is were the issue comes in I go to save it and it says the files don't exist from the rename piece. well of course not they haven't been FTPed yet.
so I tried delayevaluation on both the file system tasks but a no go still errors.
What I need it to do is basically say wait till the ftp is run these files won't be there. any help in the right direction would be appreciated.
FROM MQIC.DBO.OBSERVATION_F INNER JOIN MQIC.DBO.DATE_D
ON MQIC.DBO.OBSERVATION_F.DATE_KEY = MQIC.DBO.DATE_D.DATE_KEY
it runs to completion in about 15 min - during the entire time there is extensive used of CPU from Task Manager.
If I do the above statement, it seems to use lots of resources (50% +) for about 5 min, then falls to 5%. It just seems to sit there, for an hour + at which time I've killed the query.
This is actually the second time I tried this. The first time was on a different machine, with the P4, 3GB RAM, plenty of disk space, and using SQL-Server 2005 Standard - SP1. Exactly the same decrease in resources happened, and even though it ran several hours, no results.
Any thoughts here - not waiting long enough, memory leaks, etc.?
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too) EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType". Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
Will it be possible to do an in-place upgrade from SQL 2000 Server Enterprise SP4 32 bit running on top of 64 bit Windows 2003 Enterprise , clustered, to SQL 2005 Enterprise 64 bit? The 32 bit SQL 2000 to 64 bit SQL 2005 in place upgrade seems questionable to me... Anybody tried anything like this?
I have SQL 2000 Standard edition installed on a Windows server 2003 on a virutal box. SQL was upgraded to 2005 but during the install wizard, it did not prompt me for the Instance Name to upgrade. When done, I see the SQL Management Studio and when I connect, my databases are listed in the studio but the version number says 8.0.760 across the top of management studion which is SQL 2000 sp3.
Enterprise Manager is still available. It appears SQL 2005 was installed side by side with SQL 2000. Does that sound correct?
I downloaded SQL 2005 standard edition from my open license agreement. Don't know if that makes a difference.
I also tried upgrading SQL 2005 via the command prompt and it still shows verison number 8.0.760.
