Index Fragmentation on SQL 2000

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - Writing accented characters to Oracle from Query Analyser I am trying to insert a record into a table on a linked Oracle server from Query Analyser. Everything works fine until I add an accented character. For example: INSERT INTO CONDOR_SKYE..CONDOR.BW_LANGUAGE_TRANSLATION VALUES ('QRYELT','YCH_L_1038_ZEUSG_M00','EN','20070208',null,'Verkaufsstellenumsatze') works fine. However if I add an accented character: INSERT INTO CONDOR_SKYE..CONDOR.BW_LANGUAGE_TRANSLATION VALUES('QRYELT','YCH_L_1038_ZEUSG_M00','EN','20070208',null,'Verkaufsstellenumsatze') then the insert fails with the following message: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-01401: inserted value too large for column] OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::InsertRow returned 0x80004005: ]. Any suggestions Tag: Index Fragmentation on SQL 2000
  - 2
    - Linked Server GUI not working in SQL 2005 SP2 According to the new features of Service Pack 2: "The Linked Servers node in Object Explorer now displays system catalogs and user database objects. As soon as the linked server is created, you can expand its node to show tables and views on the remote server." This works for all linked servers that are SQL Server based. But we have other linked servers that refer to a system DSN that links to Lotus Notes databases. When I try to expand the linked server to have a look at all the tables and views (as you can easily do in SQL 2000) I get the following error: "Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". This is the case for all non SQL linked servers that we have e.g. Progress Databases, Lotus Notes. Even though openqueries are working and we can run sp_tables_ex to successfully return a list of all tables and views. Why is it only failing for non SQL servers, and is there a fix Tag: Index Fragmentation on SQL 2000
  - 3
    - SSIS Expression result limit of 4000 bytes I have a package with an input column that is varchar(8000). I want to strip the first byte off of this column and put it in one result column and the remainder of the field I want to go to a second column. If the input column is an empty string, I want to return NULL. Pulling the first byte off works fine, no issues, however putting the remainder of the input column into an output column is giving me a little trouble. If I use this expression: LEN(FLD1) == 0 NULL(DT_WSTR,1) : SUBSTRING(FLD1, 2, LEN(FLD1) - 1)) I get an error that says my expression evaluates to a string of greater than 4000 bytes. If I do this instead: LEN(FLD1) == 0 NULL(DT_WSTR,1) : RTRIM(SUBSTRING(FLD1, 2, 7999)) The expression passes muster but I get the warning that I will be truncating the column at 4000 bytes. In actuality, I don't care if the result column is truncated after 4000 bytes. I find the second solution to be a bit clunky and I'm wondering if anyone can give me a reason why the first solution won't evaluate but the second will Tag: Index Fragmentation on SQL 2000
  - 4
    - Levels in the date dimension below the granularity attribute. If you run this query on the Adventure works cube you will see time members(months) below the granularity attribute(Quarter) in the dimension usage tab. These cell values are much higher than the levels above and probably points to an All Member. Code Snippet SELECT { [Employee].[Employee Department].DEFAULTMEMBER } ON COLUMNS , { [Date].[Calendar].[Calendar Year].&[2001], [Date].[Calendar].[Calendar Semester].&[2001]&[2], [Date].[Calendar].[Calendar Quarter].&[2001]&[3], [Date].[Calendar].[Calendar Quarter].&[2001]&[4], [Date].[Calendar].[Month].&[2001]&[10], [Date].[Calendar].[Month].&[2001]&[11], [Date].[Calendar].[Month].&[2001]&[12] } ON ROWS FROM [Sales Targets] WHERE ( [Measures].[Sales Amount Quota] ) Is there any possibility to hide these fake values Edit: I would like to use the same time dimension as with another fact table with a more detailed granularity. I know that I can create two separate time dimensions with different number of levels but if I can avoid that it would be good. The solution is probably to use scripting to hide these cell values. If you have a better idea I will be grateful. Kind Regards Thomas Ivarsson Tag: Index Fragmentation on SQL 2000
  - 5
    - MDX to aggregate measure over specific dimensions Hi, I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions). I was thinking that the solution would be to have an MDX expression of the form ([Measures].[Measure], [D1].CurrentMember, [D2].CurrentMember, [D3].CurrentMember, [D4].[(All)], [D5].[(All)]) but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by. My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about! Thanks in advance. James Tag: Index Fragmentation on SQL 2000
  - 6
    - SQL Statements with inner join too slow in 2005 runs faster in 2000 Hi all, I'm desperately looking for answers to following issue that we are currently experiencing on our test server. We inheritted one of our business app from another company. We replicated exact same hardware environment. The only difference is we are using Windows 2003 server and SQL Server 2005 whereas the other company who wrote this app is using Windows 2000 server and SQL Server 2000. We are experiencing huge performance problems running few types of queries. The Stored Procedure that takes 7 minutes to run, exact same code with same data takes about 45 minutes to complete on our end. I have narrowed it down to one view that is called in a trigger that inserts rows into a specific table. Below is the trigger code and the view. I was wondering if there is any bug in SQL Server 2005 executing such SQL queries with inner joins or inner join with a view in a trigger. Thanks in advance. ====================TRigger Starts here ========================== IF @ICNT > 0 AND @DCNT > 0 AND ( UPDATE(SA_LSS_Mthly_Per_Loop_Rate) OR UPDATE(SA_LTS_Mthly_Per_Loop_Rate) OR UPDATE(SA_HCL_Per_Loop_Mthly_Rate) OR UPDATE(SA_ICLS_Per_Loop_Mthly_Rate) OR UPDATE(SA_IAS_Per_Loop_Mthly_Rate) OR UPDATE(SVA_Per_Loop_Mthly_Rate) OR UPDATE(SNA_Per_Loop_Mthly_Rate)) BEGIN INSERT [_Calculation_Schedule]( [Applies_To_Month_Dt], [Calculation_Month_Dt], [Study_Area_ID], [Company_ID], [HC_Support_Type_Cd], [Reason_Cd], [Updated_Field_Name], [Prev_Value], [Curr_Value]) SELECT INSERTED.Applies_To_Month_Dt, apl_code.fn_Get_Process_Month_Dt(), NULL, cl.Company_ID, 'LSS', 'Rate', 'SA_LSS_Mthly_Per_Loop_Rate', DELETED.SA_LSS_Mthly_Per_Loop_Rate, INSERTED.SA_LSS_Mthly_Per_Loop_Rate FROM INSERTED INNER JOIN DELETED on DELETED.Study_Area_ID = INSERTED.Study_Area_ID AND DELETED.Entered_Month_Dt = INSERTED.Entered_Month_Dt AND DELETED.Applies_To_Month_Dt = INSERTED.Applies_To_Month_Dt INNER JOIN apl_code.vw_ILEC_Competitor_List cl ON cl.Study_Area_ID = INSERTED.Study_Area_ID --only when the rate's value changes WHERE ISNULL(DELETED.SA_LSS_Mthly_Per_Loop_Rate,0) <> ISNULL(INSERTED.SA_LSS_Mthly_Per_Loop_Rate,0) --and it is not a housecleaning query AND INSERTED.SA_LSS_Mthly_Per_Loop_Rate IS NOT NULL --only when it is a PPA AND INSERTED.Applies_To_Month_Dt <> apl_code.fn_Get_Process_Month_Dt() UNION SELECT INSERTED.Applies_To_Month_Dt, apl_code.fn_Get_Process_Month_Dt(), NULL, cl.Company_ID, 'LTS', 'Rate', 'SA_LTS_Mthly_Per_Loop_Rate', DELETED.SA_LTS_Mthly_Per_Loop_Rate, INSERTED.SA_LTS_Mthly_Per_Loop_Rate FROM INSERTED INNER JOIN DELETED on DELETED.Study_Area_ID = INSERTED.Study_Area_ID AND DELETED.Entered_Month_Dt = INSERTED.Entered_Month_Dt AND DELETED.Applies_To_Month_Dt = INSERTED.Applies_To_Month_Dt INNER JOIN apl_code.vw_ILEC_Competitor_List cl ON cl.Study_Area_ID = INSERTED.Study_Area_ID --only when the rate's value changes WHERE ISNULL(DELETED.SA_LTS_Mthly_Per_Loop_Rate,0) <> ISNULL(INSERTED.SA_LTS_Mthly_Per_Loop_Rate,0) --and it is not a housecleaning query AND INSERTED.SA_LTS_Mthly_Per_Loop_Rate IS NOT NULL --only when it is a PPA AND INSERTED.Applies_To_Month_Dt <> apl_code.fn_Get_Process_Month_Dt() UNION SELECT................................. ================ Trigger ends here =============================== ======= vw_ILEC_Competitor_List (View) Starts here ================= SELECT DISTINCT IV.Study_Area_ID, I.Company_ID, I.Company_Nm, I.Legal_Entity_Nm, I.Assigned_CLEC_ID FROM dbo.Company AS I INNER JOIN dbo.Study_Area_Segment_Company AS II INNER JOIN dbo.Study_Area_Segment AS III ON II.Segment_No = III.Segment_No INNER JOIN dbo.Study_Area AS IV ON III.Study_Area_ID = IV.Study_Area_ID ON I.Company_ID = II.Company_ID WHERE (I.ILEC_Ind = 'N') AND (I.Effective_Thru_Day_Dt IS NULL) AND (II.Effective_Thru_Day_Dt > GETDATE() OR II.Effective_Thru_Day_Dt IS NULL) AND (III.Effective_Thru_Day_Dt IS NULL) ======= vw_ILEC_Competitor_List (View) Starts here ================= Tag: Index Fragmentation on SQL 2000
  - 7
    - Distinct Count Problem I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message. Can anyone help with this Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key). David Tag: Index Fragmentation on SQL 2000
  - 8
    - sub-reports don't start in the first page Hi all, we are using SQL Server 2000 with RS SP1. We've built a report that use sub-reports, each sub-report is quite long (more than 1 page). It happens that the first sub-report begins in the 2nd page but the first page contains only the title. So we have the 1st page with only the title and all the data in the 2nd page (not very nice). Is it a problem that someone have solved Am I doing something wrong with building sub-reports Thanks in advance Luca Italy Tag: Index Fragmentation on SQL 2000
  - 9
    - Partitionning a Table Hi guys, I am creating a program, and for that i implements function to partionate a Table (which are too big). In Oracle it's simple : ALTER TABLE myTable SPLIT PARTITION .... But for MS Sql Server 2005, I encounter some problems. Do you have an idea How can i partitionate a Table without make TSQL Functions Is it possible how can I split Tables simply. Thanks ;) Tag: Index Fragmentation on SQL 2000
  - 10
    - Questions on attributes acting as both inputs and predictive? Hi, all experts here, Thanks a lot for your kind attention. I am having quesion about attributes acting as both inputs and predictive outputs in a mining models, I mean if we are going to predict the outputs, then I cant actually see the effects of themselves acting as inputs as well I dont really see through the points of it. Could please any experts here shed me any light on it. I am looking forward to hearing from you shortly and thanks a lot in advance. With best regards, Yours sincerely, Tag: Index Fragmentation on SQL 2000
  - 11
    - Subtotal question Hopefully this is a simple question. We have a report with several subreports that display certain totals by a group. What we want to do now it to show a running total on the main report. Is there a way to pass data from the subreport (the subtotal) to the main report for a Grand Total Thanks for any information. Tag: Index Fragmentation on SQL 2000
  - 12
    - How do I encapsulate Transactions in a data entry form with multiple insert and update statements? I have 3 tables - Sales, Purchases, and PurchasesAppropriation Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table. I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that (Note, this whole thing takes place in the form's button's click event) (Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1679851&SiteID=1 ) I have an insert statement passed in the beginning of the form's button's click event (to the Sale table) Code Snippet connstr.Open() Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr) cmd.ExecuteNonQuery() connstr.Close() Then I have it populate the dataset (with the data from the Purchase table) Code Snippet Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr) dataAdapter.Fill(dt) DataGridView1.DataSource = dt dataAdapter.Dispose() Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated. Code Snippet connstr.Open() Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr) cmd2.ExecuteNonQuery() connstr.Close() If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table. Code Snippet Sub MarkBalanceEmpty() connstr.Open() Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr) cmd4.ExecuteNonQuery() connstr.Close() End Sub Code Snippet Sub MarkBalancePartial() connstr.Open() Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr) cmd5.ExecuteNonQuery() connstr.Close() End Sub (Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.) Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table. Code Snippet connstr.Open() Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr) cmd3.ExecuteNonQuery() connstr.Close() And the whole thing is done. Now what I did was, add a Begin Transaction before the record in the sale table is inserted Code Snippet 'Dim TransStart as String = "Begin Transaction" connstr.Open() Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr) cmd0.ExecuteNonQuery() connstr.Close() and a Commit at the end, when everything is done (i.e after the cmd3 is executed) Code Snippet 'Dim TransEnd as String = "Commit Transaction Go" connstr.Open() Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr) cmd6.ExecuteNonQuery() connstr.Close() I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong Why can't I get the transaction to behave as it should I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP). Tag: Index Fragmentation on SQL 2000
  - 13
    - MessageSource expression causes false "max width" exception - why? Greetings, I have a package that uses a complex expression for the MessageSource property in a Send Mail task. I receive the email that is composed based on the expression and it is formatted properly. However, the application also throws an exception complaining that the width of the expression may be too wide. This is the error message (with the long expression represented by "...") from the exception: Error Code: -2147192616 Error Description: The result string for expression " ... " may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR. I tried wrapping the expression in SUBSTRING(expression, 1, 4000) to guarantee that the resolved expression could not cause an exception but that didn't help. Numbers lower than 4000 also do not solve the problem. Does anyone have a solution or advice Thanks, BCB Tag: Index Fragmentation on SQL 2000
  - 14
    - Check for column before adding a column How can I test to see if a column exists before adding a column to a sql mobile table thanks, Luis Tag: Index Fragmentation on SQL 2000
  - 15
    - Why is this Query Failing I am running 2 queries using a temp table My first query runs fine and creates the temp table #TEMPCOMM1 I then try to run the next query and I get the following error: Msg 245, Level 16, State 1, Line 17 Conversion failed when converting the varchar value ',' to data type int. Code Snippet Select Distinct DLSiteID , PropID , CommAnomalies = substring ( ( SELECT ',' + ErrorLevel as [text()] FROM #TEMPCOMM1 t1 WHERE t1 . DLSiteID = t2 . DLSiteID FOR XML path ( '' ), elements ), 3 , 1000 ) FROM #TEMPCOMM1 t2 Any idea on what I do to fix this problem Tag: Index Fragmentation on SQL 2000
  - 16
    - Error executing SQL Script from VB:NET Hi. I have a complete script from a Database. But when I try to execute this script (script.txt) from VB.NET shows me many errors. Basically, the special caracters (vbreturn, vbtab, etc.) How execute this script or format the text to generate the database without problems Thanks 4all Tag: Index Fragmentation on SQL 2000
- Next
  - 1
    - Store and Retrive Images (JPEG/PNG/GIF/JPG) in sql server express 2005 in ASP.net through C# Hi friends plese help me for my little things. I m student and develop a website. i want to know how images /pics and video and audio files are store in Sql server2005 database. and ya i want to store whole file not just its address. i using asp page like uploadImage.aspx in that i put UploadFiles control now i m confuesd to storing Images so plese help me and give code or some good easy trick at my level And once store image or video and audio file in sql database then after how i used in my asp.net page. which control i wish to used for this files so plese this also slove it. give also code or any easy trick for fullfil my task.. i hope u all help me Thanx in advance to u all friends Tag: Index Fragmentation on SQL 2000
  - 2
    - Cannot Apply SP2 to 2 new nodes in a 4 node cluster ? HI There We had an existing 2 node active / active cluster, 1 running a default instance of Sql Server 2005 Enterprise Edition 9.0.3152 (SP2 + Hotfixes) and the other running a named instance of the same version. We recently added 2 new nodes to the cluster, they were successfully added and we tested the cluster group failover successfuly to the new nodes. Last night we tried to install Sql Server 2005 Enterprise edition on the new nodes. I followed to proper proceudure of modifying the installation for both instances and selecting the 2 new nodes to apply them to. This went 100%. Sql Server 2005 successfully installed for both instances on the 2 new nodes, all log files were successful. We then tried to apply SP2, we tried the following: 1. We ran SP2 from the active node, but when we go to the screen to select what you want to apply SP2 too we could not select anything, if you clicked on database engine the message said that these instances were already at a later version and we could not proceed. This is how i successfully applied SP2 to the original 2 node cluster but it does not work for additional nodes to an exisitng cluster. This is also what all the documentation we could find said, refer to SP2 release notes under the topic "Failover Cluster Installation", it is also the method we found when googling. 2. We then tried what is described under SP2 release notes "Rebuild a SQL Server 2005 SP2 Failover Cluster Node". We ran SP2 from the new nodes while they were passive, but when we got the screen where you select what to apply the SP2 too we could not select database engine the message at the bottom said that SP2 must be run from the active node and that we were attempting it from the passive node, this is what we tried in step 1 described above. 3. This was a last resort. We were advised to try failing over the instance to the new node and then running SP2. Personally i thought this was a bad idea, one should never fail over a instance of sql server to a node with incompatible binary versions and secondly when we installed sql server on the new nodes a warnng popped up before hand stating that the instances were at a later version and that the new nodes must be at this version before attempting fail over. I thought that sql would not even start, to my surprie we successfully failed over the sql group to the new node, when we ran SP2 it looked good we could select the database engine on the new node to apply SP2 too, BUT after clicking next after a few seconds the SP2 installation just closed, NO INFORMAITONAL MESSAGES NO ERRORS NO WARNINGS it just closed an never came back. I had never seen this happen on a cluster before, needless to say this made me very nervous so we failed the sql group back to the original nodes and gave up. PLEASE can some tell me how to apply SP2 to 2 new nodes in a 4 node cluster all methods descibed in SP2 release notes and other documentation as descibed above in step 1 and 2 do not work ! Thank You Tag: Index Fragmentation on SQL 2000
  - 3
    - Data transfer from ODBC to Sql Server Hi Can Anybody tell me the different ways of transfering data from ODBC Layer to SQL Server using C#. I want to transfer using Threads,means by running 4 threads, I need to transfer 4 tables at a time. Though we have SQLBulk copy Object in .NET it's not useful in multi threader env. Can anybody help me in this. Tag: Index Fragmentation on SQL 2000
  - 4
    - Bulk insert error file problem I am doing a bulk insert of some data that contains a column of xml data. I get an error when trying to do the bulk insert and added the ERRORFILE option. What happens is the error file shows up while the command is running, but as soon as I get the actual error in the messages window of SQL Server Management Studio the error file disappears. The documentation indicates that the error file should contain the records that produce the error, but since the file disappears I'm not able to see the record(s) that produce the error. Please advise, any help is very much appreciated .. thanks, john I get the following in the messages window: Msg 9411, Level 16, State 1, Line 1 XML parsing: line 1, character 725, semicolon expected My bulk insert command: bulk insert Assessor . dbo . Taxes from '\\scux00\ExportSQL\exporttaxes.txt' with ( FORMATFILE = 'C:\Taxstatement2columns.xml' , MAXERRORS = 50 , ERRORFILE = 'C:\Errors.txt' ) the format file: < xml version="1.0" > <BCPFORMAT xmlns=" http://schemas.microsoft.com/sqlserver/2004/bulkload/format " xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance "> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\n" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="ParcelID" xsi:type="SQLCHAR"/> <COLUMN SOURCE="2" NAME="xmlStream" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT> Tag: Index Fragmentation on SQL 2000
  - 5
    - Is there any method to back up the database to place outside of the local server system? Hi, all experts here, Thank you very much for your kind attention. I am wondering if we could back up the databases to any place outside of the local server system As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient. Thanks a lot in advance for your help and I am looking forward to hearing from you shortly. With best regards, Yours sincerely, Tag: Index Fragmentation on SQL 2000
  - 6
    - Error "Connection is busy with results for another command" I have looked at other threads regarding errors similar to this, but I think mine is a bit different. I am using SQL2005 Standard Edition and my application is coded with C# using ADO.Net. OLEDB connection is used. The error occurs when the application has only one thread accessing the database. It does not happen consistently, so it is very puzzling. I am wondering if anyone could offer any tip to diagnose this. Thanks in advance! Tag: Index Fragmentation on SQL 2000
  - 7
    - SQL2K CmdExec Proxy Account Setup Hi, I have the following setup: - Win 2K Server, SP4 - SQL Srv 2K, SP4 - A 'DEV' domain, with an 'Administrator' account with all possible rights on the system I need to configure a CmdExec proxy account in order to allow non-sys-admin users to execute the master.dbo.xp_CmdShell procedure . When attempting to do this via QA as follows: EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'DEV', -- agent_domain_name N'Administrator', -- agent_username N'password' -- agent_password ...I get the following error: "The system cannot find the path specified." When attempting to do this via QA as follows (note: only change is adding domain to agent_username arg): EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'DEV', -- agent_domain_name N' DEV\ Administrator', -- agent_username N'password' -- agent_password ...I get the following error: "Error executing extended stored procedure: Specified user can not login" I have tried this through Enterprise Manager and get identical results, of course. I have also tried all of the following: - different OS user accounts, including local system accounts with local admin rights; - assigning the OS account to a SQL login with System Admin role/rights; - specifically assigning the above SQL login with EXEC rights on the master.dbo.xp_CmdShell procedure; - verifying local security policy settings, as per the following link: http://support.microsoft.com/ id=283811 ; - pulling out my hair and banging my head against the wall. Can anyone H E L P ! ! ! Thanks, Joe Tag: Index Fragmentation on SQL 2000
  - 8
    - Cubes and Cost-Forecast Hi, I'm experiencing trouble with the following requirement: We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table: projekt, date_of_forecast, date, costs PR-A, 2007-06-01, 2007-07-01, 2000 PR-A, 2007-06-05, 2007-07-01, 3000 PR-A, 2007-06-10, 2007-07-01, 2500 PR-A, 2007-06-10, 2007-07-15, 2000 For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15. The customer wants a cube wich allows an answer to the following question: Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ... How to accomplish the using SQL Server 2005 STANDARD-EDITION Do I need two Time-Dimensions or just one Is there a way to create a Cube-Skript for this requirement Thanks and best whishes Manfred Tag: Index Fragmentation on SQL 2000
  - 9
    - INSERTING NULL VALUES VIA STORED PROCEDURES Hi, Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this: errorParams[3].Value = (e.InnerException== null ) ( object ) DBNull .Value:( object )e.InnerException; When I do it like this ( it is a part of preparing values for insert ): errorParams[3].Value = e.InnerException no row is added. Is there any way to pass over that check : (e.InnerException== null ) ( and do it easier This the sample exception message for that issue: {System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@InnerException' , which was not supplied. Thanks, Pawe Tag: Index Fragmentation on SQL 2000
  - 10
    - Parse Error I am getting the following error whenever we tries to parse any SQL statements in Micorsoft SQL Management Studio 2005 ��.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.�� Please help me. Svas Tag: Index Fragmentation on SQL 2000
  - 11
    - Full text prefix search for phrase with noise word? I have data (in SQL Server 2005 SP2) that looks like: ID, Name 1, elbonia university of medical science 2, elb univ med sci 3, elbonia university, med sci 4, elbonia has universities, blah blah blah medium sized scientists And a query that looks like: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ med sci*"') I want it to get 1-3 and not 4, but it just gets 2 and 3. I think the noise word 'of' is throwing if off but if I try: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ of med sci*"') I get nothing! WTF Does a noise word break prefix searches The only message is " (0 row(s) affected)" SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science"') returns 1 but add the * for stemming: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science*"') and it also returns nothing. Thanks for looking! -Andrew Tag: Index Fragmentation on SQL 2000
  - 12
    - SQL Server Express error when using NUnit This question is for someone experienced with both SQL Server Express and NUnit. I think the answer might be simple, but I would like to understand what is going on and how to prevent it. I have a test class with a TestFixtureSetUp section that calls a method to drop and re-build my database from scratch, including tables for the Membership and Roles provider. This code works fine. After TestFixtureSetUp runs, I have a test to verify that there are 3 records in the Membership database which were added when the database was re-built. The code looks like this: [ Test ] public void MembershipUsersTest() { int expectedNumberOfUsers = 3; MembershipUserCollection users = System.Web.Security. Membership .GetAllUsers(); Assert .AreEqual(expectedNumberOfUsers, users.Count); } When this test runs along with the TextFixtureSetUp code, I get this error: System.Data.SqlClient.SqlException : Cannot open user default database. Login failed. Login failed for user 'TERRY-COMPUTER\Terry'. However, if I re-run this test but block the TestFixtureSetUp code that re-builds the database, the test passes fine. I'm guessing it's just a simple matter of NUnit somehow locking access to the database when the database is rebuilt, but I am not understanding why this is happening. My code for re-generating the database uses a "using" construct, so the connnections should be closing properly. using ( SqlConnection conn = new SqlConnection (connectionString)) { FileInfo file = new FileInfo (fullScriptPath); string script = file.OpenText().ReadToEnd(); Server server = new Server ( new ServerConnection (conn)); server.ConnectionContext.ExecuteNonQuery(script); } Any insights into why I am getting this error and how I can avoid it and still run my Membership test right after I re-build the db One additional piece of information that might be important. The code for rebuilding the databases uses the following connection string: @"Data Source=.\SQLExpress;Integrated Security=True" Whereas the Membership provider uses the following connection string which has a hard-coded path to the database: " Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=C:\MyApps\MyAppsWeb\App_Data\MyApp.mdf " Tag: Index Fragmentation on SQL 2000
  - 13
    - Best practice to copy a lot of data between databases Hi, I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables. Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows: Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database) Some people do some checks to see if the new data in the Test-database is correct If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database. For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up Is my current way of working good practice Thx! Tag: Index Fragmentation on SQL 2000
  - 14
    - DTS old style and Dynamic Properties Tasks Im using embedded SS2000 DTS packages in SSIS. These legacy packages use Dynamic Properties Tasks to pick up connection settings from a named ini file. The name of that ini file needs to change occasionally. To date the only way we have of doing this is resetting all the dynamic properties to point at the new ini file. Unfortuntely there are a lot of connections and properties and there is no budget to recreate these packages in SSIS. There must be an easier way ! Does anyone know how to quickly change Dynamic Properties Tasks to look at a new ini Tag: Index Fragmentation on SQL 2000
  - 15
    - Help plz - need query to get the following kind of output i want result to be in the required format given below: here is the result column A ---------------- 5 7 10 14 22 30 .... i want output to be in this form with a single sql statement: column A column B ---------------- ---------------- 5 2(difference b/n the current record and the next record) 7 3 10 4 14 8 22 8 30 0 can any one plz post the query which gives the above kind of result.. thnx in advance.. Tag: Index Fragmentation on SQL 2000
  - 16
    - RAISERROR does not cause SQL task to fail - why? Greetings, I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS. I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended. I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible Is there an alternative to using RAISERROR Thanks, BCB Tag: Index Fragmentation on SQL 2000

NathanS

I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running

Top

Re: Index Fragmentation on SQL 2000

Frank Kalis

NathanS wrote:

I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running

See if this helps: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Top

Re: Index Fragmentation on SQL 2000

hunchback

NathanS,

This article will help you decide which method will be better for your needs.

Microsoft SQL Server 2000 Index Defragmentation Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

AMB

Top

Re: Index Fragmentation on SQL 2000

Frank Kalis

hunchback wrote:

NathanS,

This article will help you decide which method will be better for your needs.

Microsoft SQL Server 2000 Index Defragmentation Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

AMB

Beaten by 1 minute.

Top

Re: Index Fragmentation on SQL 2000

hunchback

Hi Frank,

I saw it, you beat me by a minute or less. The good thing is the consistency of the answers.

Cheers,

AMB

Top

Re: Index Fragmentation on SQL 2000

Arnie Rowland

I would 'much' rather be 'beaten by one minute' that to be beaten by a baseball bat. ;-)

Top