Indexes Stop Working
Nov 23, 2004
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?
View 4 Replies
ADVERTISEMENT
Sep 7, 2007
Hey
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.
THanks
View 14 Replies
View Related
Jul 14, 2004
I have a weird problem. I started my laptop this morning and was unable to start my SQL Server. Every time I try to start it I get the below error:
A connection could not be established to (local)
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registeration properties.
Any ideas?
Thanks
View 7 Replies
View Related
Nov 4, 2015
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,
[Code] ....
View 10 Replies
View Related
Aug 16, 2007
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
View 4 Replies
View Related
Jul 1, 2014
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?
View 5 Replies
View Related
Sep 18, 2007
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?
View 4 Replies
View Related
Jun 25, 2015
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.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Mar 3, 2006
hi
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 ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
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)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
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.
Please I would appreciate any advice...
View 2 Replies
View Related
Mar 11, 2002
Hi All,
I'd like to remotly stop an instance of SQL Server
but this command does not seem to work....
net stop mssql$<server name> (eg. net stop mssql$prod)
...any ideas? I'll want to start this sql server again too.
David.
View 1 Replies
View Related
Jan 30, 2008
Is there any way to stop SQL CLR on database apart from using
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
I want to stop this functionality on database...any idea...
View 1 Replies
View Related
Apr 10, 2008
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
View 3 Replies
View Related
Feb 25, 2004
I have these two tables and I cant prevent duplicates.
SEARCH
Item
ItemID
Info
CATEGORYDATA
CategoryID
ItemID
SELECT DISTINCT SEARCH.ItemId, SEARCH.Item, CATEGORYDATA.CategoryId
FROM SEARCH
INNER JOIN CATEGORYDATA
ON SEARCH.ItemID = CATEGORYDATA.ItemID
And I get something like:ItemID Item CategoryID
1 item1 1
3 item3 1
1 item1 2 <---duplicate
1 item1 3 <---duplicate
2 item2 3
4 item4 3
Thanks in advance
View 4 Replies
View Related
Apr 29, 2002
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.
Thanks for Help!
View 2 Replies
View Related
Apr 25, 2000
Hello,
Does any one know how I can start and stop the sql services with the net use command?
Thanks,
Anthony
View 1 Replies
View Related
Sep 1, 1998
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?
All help greatly appreciated.
Judith
View 1 Replies
View Related
May 10, 2002
I need to remotely start and stop SQL from another machine from a program or command line. Thanks in advance.
View 1 Replies
View Related
Aug 29, 2002
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.
So how to stop the replication?
Thanks!
amy
View 1 Replies
View Related
Feb 13, 2006
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
View 5 Replies
View Related
Nov 30, 2007
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.)
View 14 Replies
View Related
Jun 29, 2015
How do you stop Replication?It is causing blocking. URLs....
View 9 Replies
View Related
Feb 26, 2008
Hey Guys, I have a question. I know you can put a 'STOP' command in VBA code. Is there something like that for SQL Server Stored Procedures?
View 3 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Jul 20, 2005
Hi.I need to stop mssql service via command row.How can I do?Tnk,Luca
View 1 Replies
View Related
Oct 31, 2007
Hello,
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 ???
Thanks
View 1 Replies
View Related
Apr 22, 2008
Here are some production error messages:
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.
Hoping for a solution,
IanO
View 1 Replies
View Related
May 26, 2006
Hi,
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?
Thanks.
View 8 Replies
View Related
Aug 6, 2007
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?
Thank you and have a nice day.
View 1 Replies
View Related
Jun 9, 2006
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)
---------------------------------------------------------------
create procedure usp_PatientManagementAdminGet
@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)
exec sp_sqlexec @sql
View 3 Replies
View Related
Aug 3, 2006
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?
View 2 Replies
View Related
Nov 20, 2006
Does UrlEncode have any impact on SQL injection? How would I go about protecting my site?
View 3 Replies
View Related
