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????
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 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.
I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error
Error: 2008-03-24 13:52:40.22 Code: 0xC0202009 Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.".
I am able to run the package outside the sql job and also connect to the oracle. I have oracle 9i client installed on the server and sql server is 2005.
I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.
Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.
Hi, I am executing a SSIS package using dtexec. 64 bit version of dtexec works fine. But when i use 32 bit version of dtexec, it fails. i have local admin rights. Following is error description. Please help.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:24:30 AM Error: 2008-03-18 09:24:32.54 Code: 0xC0202009 Source: IMALCRM Connection manager "IMAL SRC" Description: An OLE DB error has occurred. Error code: 0x800703E6. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H result: 0x800703E6 Description: "Invalid access to memory location.". End Error Error: 2008-03-18 09:24:32.54 Code: 0xC020801C Source: Load Fund Detail V_FUND_DETAIL [16] Description: The AcquireConnection method call to the connection manager "IMA L SRC" failed with error code 0xC0202009. End Error Error: 2008-03-18 09:24:32.54 Code: 0xC0047017 Source: Load Fund Detail DTS.Pipeline Description: component "V_FUND_DETAIL" (16) failed validation and returned er ror code 0xC020801C. End Error Error: 2008-03-18 09:24:32.54 Code: 0xC004700C Source: Load Fund Detail DTS.Pipeline Description: One or more component failed validation. End Error Error: 2008-03-18 09:24:32.54 Code: 0xC0024107 Source: Load Fund Detail Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:24:30 AM Finished: 9:24:32 AM Elapsed: 2.078 seconds
If this post belongs somewhere else I appologize. I have spent several days trying to solve this problem with no luck. My site is online. Hosted at NeikoHosting. I can connect to the database remotely when adding a datacontrol. It tests fine. But when running the page it won't connect. Even if I go in and change the Web.Config connection string to a local Data Source provided to me by Neiko, it still won't work. It just won't connect. Here are the two connection strings in the Web.Config, minus my login info: Only the remote string will pass testing. Neither works on the site. <add name="yourchurchmychurchDBConnectionString" connectionString="Data Source=MSSQL2K-A;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" /> <add name="yourchurchmychurchDBConnectionString2" connectionString="Data Source=66.103.238.206;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" /> Here is the stack trace, if that helps. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I have a report which has multivalue parameters enabled and If i give NULL it displays everything correctly. But if I give different ClientId it doesnt do it in the report.. But if i run my sproc in VS2005 and in ssms it works the way i want it. this is my sproc
Code Snippet set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@StartDate datetime, @EndDate datetime, @ClientId nvarchar(max)= NULL AS Declare @SQLTEXT nvarchar(max) if @ClientId is NULL BEGIN SELECT o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId --cp.ClientId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId -- and o.CreatedByUserId = cp.UserId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId --cp.ClientId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId --AND cp.ClientId in ('+ convert(Varchar, @ClientId) + ' ) Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + convert(Varchar,@ClientId) + ') AND o.OrderDate BETWEEN ''' + Convert(varchar, @StartDate) + ''' AND ''' + convert(varchar, @EndDate) + ''' ORDER BY o.OrderId DESC' exec(@SQLTEXT) END --return (@SQLTEXT)
I have 2 datasets in this report one for the above sproc and other dataset that gives me the clientname and it is as follows
Code Snippet
ALTER Procedure [dbo].[usp_GetClientsAll]
@ClientId nvarchar(max) = NULL
AS
--Declare @ClientId nvarchar(max)
SELECT
NULL ClientId,
'<All Clients >' ClientName
FROM
Client
Union
SELECT
ClientId,
ClientName
FROM
Client
Where
ClientId = @ClientId
OR
(
ClientId = ClientId
OR
@ClientId IS NULL
)
In the first dataset Parameter list i have omitted ClientId but kept it in the report parameter.. So when i give select all it works.. but when i just select particular it gives me the same result as Select all..
I have a report which I have tested and works fine. now I'm trying to use it as a subreport. the "outer" or main report is very simple: it just has a company standard banner and some header/footer information, and then a single subreport. there is no passing of parameters between main report and sub report. the subreport does have its own parameter to govern its dataset, and provides its own default for that.
The error that I'm getting is this:
[rsErrorExecutingSubreport] An error occurred while executing the subreport €˜subreport1€™: An error has occurred during report processing.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. The data extension returned an error during reading the field.
[rsNone] An error has occurred during report processing.
Of course, this doesn't happen when I execute the subreport by itself. What kinds of things should I be looking at to get to the bottom of this. Thanks!
Hey I had a table with a column of data encrypted in a format. I was able to decrypt it and then encrypt it using Symmetric keys and then updating the table column with the data. Now, there is a user sp which needs to encrypt the password for the new user and put it in the table. I'm not being able to make it work. I have this so far. Something somewhere is wrong. I dont know where. Please help Thanks. I used the same script to do the encryption initially but that was for the whole column. I need to see the encrypted version of the @inTargetPassword variable. But it's not working. It doesn't give me an error but gives me wrong data...
People,I'm trying to publish my first website and am having a few problems.I've got Visual Web Developer 2005 Express and am trying to use the Personal Website Starter Kit. (my SQL server is SQL Server Express Edition 2005 - which is also running on my local machine)It seems to work fine when I run it on my localhost, as soon as I ftp it up to my web hosting company, I get an error message (see below) :-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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) My hypothesis is :-It would appear to me that when running locally, the starter kit website uses my installation of SQL 2005 Express Edition, but when I upload all the files, I'm guessing the application is still trying to point at a local instance of SQL on my local PC which it now cannot see. I'm guessing I need to somehow upload the SQL database onto my web host (I've purchased 100M of SQL Server 2005 space), and point the application at that SQL instance instead. But I don't know if I'm right about all this, or indeed how to do it if I am. Can anyone help?Much thanks in advance,Will
works fine in designer but when i load the report services I get the following error anybody know what to do there is one subreport with this report maybe the passing value but what could be wrong ????
Item has already been added. Key in dictionary: '9' Key being added: '9'
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.. I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc.. IF Exists( SELECT * FROM PlanEligibility WHERE PlanId = @PlanId ) BEGIN UPDATE PlanEligibility SET LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS ELSE @rsLengthOfService END, EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, EntryDates = @EntryDates, EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0 When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM, LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END, EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, OtherEmployeeExclusions = @OtherEmployeeExclusions WHERE PlanId = @PlanId END ELSE BEGIN INSERT INTO PlanEligibility ( PlanId, LengthOfService, EligibilityAge, EntryDates, EligiDifferentRequirementsMatch, LengthOfServiceMatch, EligibilityAgeMatch, OtherEmployeeExclusions ) VALUES ( @PlanId, Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS ELSE @rsLengthOfService END,--@rsLengthOfService, CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge, @EntryDates, Case When @PD_EmployeeContribution = 0 Then 0 When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END, CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch, @OtherEmployeeExclusions ) END Any help will be appreciated.. Regards, Karen
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.
We have a valid full backup of a database. We know it is valid, we have restored it twice from the network with no problems, but we do not have access to the network location from our sandbox environment.
The .bak file is sizable at about 9GB. The .bak file resides on our internal network, in a SAN drive. No problems there. When we copy the file from there into a sandbox environment to attempt the restore in the sandbox environment it gets corrupted. We've tried three different times and all three different times it gets corrupted. First time we copied the file over to the sandbox the restore went up to 50% and failed. The second time we copied the file again and attempted the restore again it failed at 70%.
The third time it failed at 60%. The error message we get during the restore is "...Read on ... failed: 13(The data is invalid) Msg 3013, Level 16, State 1, Line 1 Restore database is terminating abnormally."
Now some background here. To move the file our network team is doing this: they have this .vmdk file that they mount out in our production environment (which has access to the network location where the .bak file is), copy the file into the drive, then move the .vmdk file into the sandbox environment(which does not have access to the network location), mount the drive in the sandbox environment, and then I have access to the .bak file from within the sandbox environment.
Something in the process of using the .vmdk file to move the .bak file from production into the sandbox is causing the file to get corrupted.
I have a report problem. I'm using a parameter to dynamically control visibility for two tables. If the parameter is set to one value, I want to switch one of the tables to invisible, if the parameter is set to another, I want the other table to be invisible instead.
This all works fine in Visual Studio. When I publish it to my report server, the visibility controls no longer function and both tables always display. Any ideas here?
Here's the situation. I've got a DB with several tables. Let's pick out two of those tables from the bunch.. they both have approx 2500 rows in them. I take one and duplicate it (right click, All Tasks->Export Data, copy tables, etc) and it duplicates just fine into another DB.
Now, the second table.. takes FOREVER. Yet it has the same amount of rows in it! And this is a flat table export.. I'm not including dependent objects or anything like that.
Not to mention this is slow, but when I use Red-Gate SQL Data Compare, it gets stuck when it hits this specific table.
Hello I am receiving the dreaded mail error listed above. I can send out a test E-mail from Enterprise Manager to operators, but I cannot run this Transact query:
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
I have a big table A_newHistory (more than 2 million rows) with primary key fund_id + date_price . This table has to be updated every 2 hours from XML. Every row in XML must be inserted or updated (if current id and date already exist in the table) in the A_newHistory. The following procedure works but very slow... How can I optimize that?
================================================== ======= CREATE PROCEDURE spSaveFundsAdjustedClose @XML ntext AS DECLARE @fund_id int DECLARE @date_price datetime DECLARE @adj_closed float DECLARE @XMLDoc int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION
EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XML
DECLARE MutualFunds_Cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT * FROM OPENXML (@XMLDoc , '/xml/a', 1) WITH ([id] INT,[date] datetime, price float) OPEN MutualFunds_Cursor FETCH NEXT FROM MutualFunds_Cursor INTO @fund_id, @date_price, @adj_closed
WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS (SELECT * FROM A_newHistory WHERE id_fund = @fund_id AND date_price = @date_price) BEGIN UPDATE A_newHistory SET adj_close = @adj_closed WHERE id_fund = @fund_id AND date_price = @date_price END ELSE BEGIN INSERT INTO A_newHistory VALUES(@fund_id, @date_price, @adj_closed) END
IF @@Error <> 0 BEGIN ROLLBACK TRANSACTION SELECT -1 RETURN END
FETCH NEXT FROM MutualFunds_Cursor INTO @fund_id, @date_price, @adj_closed END
EXEC sp_xml_removedocument @XMLDoc CLOSE MutualFunds_Cursor DEALLOCATE MutualFunds_Cursor
COMMIT TRANSACTION SELECT 0 GO ================================================== =======
-- 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 am trying to drop an index usiong enterprise manager.The process hangs and Enterprise manager is not responding.When I checked the Current activity the process is in sleeping condition.I appreciate if any one can tell me what could be the reason for the process to be hung. thanks Mohan
I have a table which I am unable to drop the index. I tried to drop it via the TSQL and GUI interface (Indexes dialog box), but when I clicked on table refresh, the index still there.
Below is the result after DBCC commands:
1. DBCC checktable(TB_LOCLoan)
Checking TB_LOCLoan The total number of data pages in this table is 146542. Table has 1596232 data rows. Msg 605, Level 21, State 1 Attempt to fetch logical page 462136 in database 'DM_LOCLoan' belongs to object '1744009244', not to object 'TB_LOCLoan'.
2. DBCC newalloc(DM_LOCLoan)
TABLE: TB_LOCLoan OBJID = 832005995 INDID=0 FIRST=60168 ROOT=642778 DPAGES=146542 SORT=0 Data level: 0. 146542 Data Pages in 18393 extents. INDID=5 FIRST=463752 ROOT=463739 DPAGES=5304 SORT=1 Msg 2525, Level 16, State 1 Table Corrupt: Object id wrong; tables: alloc page 463616 extent id=463752 l page#=463752 objid in ext=-832005995 (name = -832005995) objid in page=832005995 (name = TB_LOCLoan) objid in sysindexes=832005995 (name = TB_LOCLoan) TOTAL # of extents = 18393
3. From Error Msg 2525, I have tried the suggested action
use DM_LOCLoan go select indid, name, object_name(id) from sysindexes where id=832005995 and distribution=463752 go
This query does not return any row.
I am running out of clue of how to proceed! Anybody come across this problem before? I am very eager to solve this problem as the table is hanging here, I can't even drop it to recreate and continue data loading.
According to sysindexes, I have a table with a primary key and an index. I'd like to drop the index. However, when I give the drop command, the message "Cannot drop the index 'eventlog._INDEX', because it does not exist in the system catalog." is returned.
I'm not sure what to look for. How do I remove the index?
Hi, all, I want to re-create an index on a production table. I got an error 644 "could not find index entry...". The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1 Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36. DBCC results for 'Mfg_DFSFNSF'. There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be: DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss) But that has to put the db under single user mode.
I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.
syntax: IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no go create index [idx_acct_no] ON [dbo].[accounts] ([acct_no]) go
Error when index does not exist: Server: Msg 3703, Level 11, State 7, Line 1 Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.
