We have about 10 sites using SQL Server 2000 SP1 or 3 or 3a, across two servers. The publisher/distributor performs transactional replication on three databases to the 'secondary' server.
Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.
Before I start posting schema or index details anyone come across this before?
I have a statement that has been running great for the past hour but now it will not pull the info any longer and just gives me a null
DECLARE @Text VARCHAR(2000) SELECT @Text = COALESCE(@Text + '', '') + x.memotext FROM (SELECT TOP 100 PERCENT memotext FROM customermemoheader WHERE memonumber = 'TERMS' and customernumber = '0009' ORDER BY seqnumber) AS x
SELECT @Text AS MemoText
I have verified in the tables that the info is there by running the select statement from within the (). It has worked for 8000 records and now it no longer works. Any help would be much appreciated.
Setup: Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit SQL Server 2014 Express - 32 Bit
Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.
I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.
My PO table CREATE TABLE [dbo].[PO]( [ID] [int] IDENTITY(1,1) NOT NULL, [Quote_Number] [varchar](max) NULL, [Customer] [varchar](max) NULL, [CustomerPO] [varchar](max) NULL, [PO_Received_Date] [datetime] NULL, [Total_PO_Value] [decimal](18, 2) NULL,
Hi guys, The code below works, but very slow (almost 7 minutes to retrieve data) and during the process makes the entire local network almost to "freeze". Someone told me it may be related to "indexes.." Can anyone help to fix / improve the code in order to make it faster? Thanks in advance, Aldo.
Code Snippet SELECT '1' AS 'X', Accounts.ACCOUNTKEY AS 'Accounts.AccountKey', Accounts.FULLNAME AS 'Accounts.FullName', Accounts.FILTER AS 'Accounts.Filter', Accounts.SORTGROUP AS 'Accounts.SortGroup', Accounts.COUNTRY AS 'Accounts.Country', Accounts_Sort_Names.SORTCODENAME AS 'AccountsSortNames.SortCodeName', JurnalTransMoves.ID AS 'JurnalTransMoves.ID', JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION', JurnalTrans.TRANSID AS 'JurnalTrans.TRANSID', JurnalTrans.BATCHNO AS 'JurnalTrans.BATCHNO', JurnalTrans.TRANSTYPE AS 'JurnalTrans.TRANSTYPE', JurnalTrans.TRANSCREDID AS 'JurnalTrans.TRANSCREDID', JurnalTrans.STATUS AS 'JurnalTrans_STATUS', JurnalTrans.SUF AS 'JurnalTrans.SUF', JurnalTrans.DUEDATE AS 'JurnalTrans.DUEDATE', JurnalTrans.VALUEDATE AS 'JurnalTrans.VALUEDATE', CAR_PURCHASE_VALUE.SUF AS 'CAR_PURCHASE_VALUE.SUF', CAR_SALE_VALUE.SUF AS 'CAR_SALE_VALUE.SUF', CAR_PASCAL.NOTE AS 'CAR_PASCAL.NOTE', CAR_COMPANY.NOTE AS 'CAR_COMPANY.NOTE', CAR_RELATED_TO_COMPANY.NOTE AS 'CAR_RELATED_TO_COMPANY.NOTE', CAR_PAZOMAT.NOTE AS 'CAR_PAZOMAT.NOTE', CAR_DRIVER.NOTE AS 'CAR_DRIVER.NOTE', CAR_NUMBER.NOTE AS 'CAR_NUMBER.NOTE', CAR_STATUS.NOTE AS 'CAR_STATUS.NOTE', CAR_YEAR.NOTE AS 'CAR_YEAR.NOTE', CAR_MODEL.NOTE AS 'CAR_MODEL.NOTE', CAR_MANUFACTURER.NOTE AS 'CAR_MANUFACTURER.NOTE', CASE WHEN CAST(DATEPART(month, JurnalTrans.VALUEDATE) AS int) >= 1 AND CAST(DATEPART(month, JurnalTrans.VALUEDATE) AS int) < 10 THEN CAST(DATEPART(year, JurnalTrans.VALUEDATE) AS char(4)) + '-0' + CAST(DATEPART(month, JurnalTrans.VALUEDATE) AS char(1)) WHEN CAST(DATEPART(month, JurnalTrans.VALUEDATE) AS int) >= 10 AND CAST(DATEPART(month, JurnalTrans.VALUEDATE) AS int) <= 12 THEN CAST(DATEPART(year, JurnalTrans.VALUEDATE) AS char(4)) + '-' + CAST(DATEPART(month, JurnalTrans.VALUEDATE) AS char(2)) ELSE 'UnKnown' END AS 'MonthAndYear', DATEPART(month, JurnalTrans.VALUEDATE) AS 'Month', CASE WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 1 THEN 'Quarter 1' WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 2 THEN 'Quarter 2' WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 3 THEN 'Quarter 3' WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 4 THEN 'Quarter 4' ELSE 'UnKnown' END AS 'Quarter', CASE WHEN CAST(DATEPART(quarter,JurnalTrans.VALUEDATE) AS int) = 1 THEN 'Half 1' WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 2 THEN 'Half 1' WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 3 THEN 'Half 2' WHEN CAST(DATEPART(quarter, JurnalTrans.VALUEDATE) AS int) = 4 THEN 'Half 2' ELSE 'UnKnown' END AS 'Half', DATEPART(year, JurnalTrans.VALUEDATE) AS 'Year', '1' AS 'Y' FROM JURNALTRANS AS JurnalTrans INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves ON JurnalTrans.TRANSDEBID = JurnalTransMoves.ACCOUNTKEY AND JurnalTrans.TRANSID = JurnalTransMoves.TRANSID INNER JOIN ACCOUNTS AS Accounts ON JurnalTrans.TRANSDEBID = Accounts.ACCOUNTKEY LEFT OUTER JOIN ACCSORTNAMES AS Accounts_Sort_Names ON Accounts.SORTGROUP = Accounts_Sort_Names.ACCSORTCODE LEFT OUTER JOIN EXTRASUMS AS CAR_PURCHASE_VALUE ON Accounts.ACCOUNTKEY = CAR_PURCHASE_VALUE.KEF AND CAR_PURCHASE_VALUE.SUFID IN (1) LEFT OUTER JOIN EXTRASUMS AS CAR_SALE_VALUE ON Accounts.ACCOUNTKEY = CAR_SALE_VALUE.KEF AND CAR_SALE_VALUE.SUFID IN (2) LEFT OUTER JOIN EXTRANOTES AS CAR_PASCAL ON Accounts.ACCOUNTKEY = CAR_PASCAL.KEF AND CAR_PASCAL.NOTEID IN (12) LEFT OUTER JOIN EXTRANOTES AS CAR_COMPANY ON Accounts.ACCOUNTKEY = CAR_COMPANY.KEF AND CAR_COMPANY.NOTEID IN (13) LEFT OUTER JOIN EXTRANOTES AS CAR_RELATED_TO_COMPANY ON Accounts.ACCOUNTKEY = CAR_RELATED_TO_COMPANY.KEF AND CAR_RELATED_TO_COMPANY.NOTEID IN (10) LEFT OUTER JOIN EXTRANOTES AS CAR_PAZOMAT ON Accounts.ACCOUNTKEY = CAR_PAZOMAT.KEF AND CAR_PAZOMAT.NOTEID IN (11) LEFT OUTER JOIN EXTRANOTES AS CAR_DRIVER ON Accounts.ACCOUNTKEY = CAR_DRIVER.KEF AND CAR_DRIVER.NOTEID IN (9) LEFT OUTER JOIN EXTRANOTES AS CAR_NUMBER ON Accounts.ACCOUNTKEY = CAR_NUMBER.KEF AND CAR_NUMBER.NOTEID IN (8) LEFT OUTER JOIN EXTRANOTES AS CAR_STATUS ON Accounts.ACCOUNTKEY = CAR_STATUS.KEF AND CAR_STATUS.NOTEID IN (7) LEFT OUTER JOIN EXTRANOTES AS CAR_YEAR ON Accounts.ACCOUNTKEY = CAR_YEAR.KEF AND CAR_YEAR.NOTEID IN (6) LEFT OUTER JOIN EXTRANOTES AS CAR_MODEL ON Accounts.ACCOUNTKEY = CAR_MODEL.KEF AND CAR_MODEL.NOTEID IN (5) LEFT OUTER JOIN EXTRANOTES AS CAR_MANUFACTURER ON Accounts.ACCOUNTKEY = CAR_MANUFACTURER.KEF AND CAR_MANUFACTURER.NOTEID IN (4) WHERE (Accounts.SORTGROUP BETWEEN 154 AND 154) AND (Accounts.ACCOUNTKEY BETWEEN '0' AND 'ZZZZZZZZZZZZZZ') AND (JurnalTrans.VALUEDATE BETWEEN CONVERT(DATETIME, '2005/01/01', 102) AND CONVERT(DATETIME, '2007/08/16', 102)) AND (JurnalTrans.DUEDATE BETWEEN CONVERT(DATETIME, '1800/01/01', 102) AND CONVERT(DATETIME, '2050/12/31', 102)) ORDER BY Accounts.SORTGROUP, Accounts.ACCOUNTKEY, Accounts.FULLNAME, JurnalTrans.DESCRIPTION
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Hi All I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new record the id will be 21plese help me in this
I have a snapshot replication is running and now I want to stop the replication for a while. Is it possible to do that? If it is then where I can set to stop it? Please help.
I use EM to handle 2 SQL servers. One I can `stop`; the other I can`t. (except I think I used to be able to do so).
When I select the `stop` I get the following message from EM:
"An error 1051 - (A stop control has bee sent to a service which other running services are dependent on) occurred while performing the service operation on the MSSQLServer service."
How do I track down what this other running service is? How do I stop SQL?
I wanted to remove my Northwind database. But that database is currently used for replication. I'll have to stop the replication first before I can remove it.
Hi, Can anybody tell me how to stop the execution of a T-SQL statement at once? I have tried Alt+Break but its taking a long time to stop.Whats the reason?Plz suggest....I am dealing with a database containing 24343000 data. Joydeep
I have a performance issue with a Cognos report against SQL Server 2005. The total running time of the report is 1 minute 15, and using SQL Profiler I found out that 1m13 is spent preparing SQL. Execution and generating the report takes up 2 seconds; no problem there. The SQL is the same every time I run the report, yet SQL Server spends 1m13 preparing it every single time! I'm no DBA, but as far as I understand that's not what's supposed to happen; once prepared, the SQL should execute quickly every time.
Is there a way to stop SQL from preparing the statement every time?
(Cognos 8 against SQL Server 2005 through OLEDB. Oh, and this query takes about a second when run in EM.)
Hi,I am doing some resource hungry tasks (some extraction and loadingthrough DTS), for which each time the SQL Server Log files gets filledup!Is there any way to stop the logging (like as during restore)?Thanks in advance.-surajit
i'm desperate! I have a application in my SQL Express and only one database for it. But right now it have 3,97gb, without log file. i'm already buying a SQL Enterprise to upgrade this, take some days, but my question is: when this size came to 4,00gb my application will be stop working ? my business stop ???
Error: 0xC0047039 at Load work$$MyDataFile from flat file, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load work$$MyDataFile from flat file, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
I did not request a shutdown! Someone please give me a global switch to tell SSIS to keep going. Why should I not load 20,00 records because of one bad record?
I have tediously changed every field on every tool to "Ignore Error". Why should I still have to do this? I mean why does SSIS default to failure? Is there some switch I could change in the XML that would change this default behaviour?
The problem with the data in this case: a NewLine character was missing so it looked to the data flow as one very long line.
In one of my packages, I have a script component to do a transformation and I am inserting the output to table. Within that script component, if the data does not match my requirement, I have to stop the job. For that, I am using 'componentmetadata.fireError' to raise an error. The problem is, now even when an error is raised, the job completes by loading the remaining records and then aborts. But I want to abort the job as soon it raises an error. How to do this?
I create a publication with 1 article with a parameterized filter. Empty table without records. Then i went to create a snapshot through snapshot agent. After snapshot was created, data are inserted with "Insert Into..Select" SQL statement. A total of 20 millions records (10GB) are inserted. When i try to sync with a SQL compact edition, the synchronization % stop at 0 for more than 1 hour. Does that mean it is not working?
Hi, The following stored procedure (SP) is using a dynamic sql to build the query. How can this be written using a standard sql. i.e. NOT dynamically being built. Initially I thought I can have something like the following query but it does not seem to be working for when the where caluse parameters are not passed. So I ended up using the dynamic sql as it returns the correct data. Can you see how the SP can be altered please? Thanks
------------------------this query does not return the correct data where the where parameters are not passed.------------ select * from tbl_Management where
([Year] is null or [Year] = @Year) AND (YearPeriod is null or YearPeriod = @YearPeriod) AND (AreaCode is null or AreaCode = @AreaCode)
@Year int = null, @YearPeriod int = null, @AreaCode varchar(3) = null
as
declare @sql varchar(1000)
set @sql = 'select' set @sql = @sql + ' ID' set @sql = @sql + ' ,AreaCode' set @sql = @sql + ' ,Year' set @sql = @sql + ' ,YearPeriod' set @sql = @sql + ' ,A1=A2+A3' set @sql = @sql + ' ,A2,A3' set @sql = @sql + ' ,B1=B2+B3' set @sql = @sql + ' ,X1=convert(int, ((B2+B3)*1.0/(A2+A3))*100)' set @sql = @sql + ' from' set @sql = @sql + ' tbl_Management' set @sql = @sql + ' where'
if (@Year > 0) begin set @sql = @sql + ' [Year] = ' + convert(varchar(4), @Year) set @sql = @sql + ' AND' end if (@YearPeriod > 0) begin set @sql = @sql + ' YearPeriod = ' + convert(varchar(2), @YearPeriod) set @sql = @sql + ' AND' end if (@ProgrammeAreaCode is not null) begin set @sql = @sql + ' AreaCode = ''' + convert(varchar(3), @AreaCode) + '''' set @sql = @sql + ' AND' end
--trim off the last AND... set @sql = left(@sql, len(@sql) - 3)
We have automated the process of backing up and restoring the databases. But the problem is One database is restoring from several hrs ..though it is supposed to take only a few mins. So there is some issue with it and I want to find out how I can stop the restoring process. Its not a command am running in query analyser or Enterprise manager, but its automated so i cant see the command to stop it. Any ideas?
