I am upgrading SQL 7 to SQL 2000. The upgrade process has been running for over 3 hours. I have about 9G in databases on the server. Does anyone know if this is taking too long? Compaq Proliant 7000, dual Xeon II processors, 786MB memory. "Setup is updating your system..." has been running for 3 hours...
TIA
Janet
When attemping to install SQL 2005 Express using the following command line arguments
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE
or
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE SECURITYMODE=SQL SAPWD=MY_PASSWORD
the following error occurs.
Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
We have a server instance on SQL Server 2005 SP2 build 3042. We have a 32 bit x86 server. We attempted to upgrade to SP2 build 3054 KB934458. And we got the following error as stated in the Summary.txt file.
Code Snippet
**********************************************************************************
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
Error Number : 29538
Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
----------------------------------------------------------------------------------
The client tools and workstation components were successfully installed. The server is still reporting build 3042.
Here is the portions of the HotFix.log file.
Code Snippet
05/12/2008 09:19:09.041 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
05/12/2008 09:19:09.072 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
05/12/2008 09:19:09.103 Registry: Cannot read registry key value "Debug"
05/12/2008 09:21:29.382 MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
05/12/2008 09:22:33.678 MSP returned 1603: A fatal error occurred during installation.
05/12/2008 09:22:33.724 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
Any help would be appreciated.
Hi,
We are performing a SQL 2005 SP1 upgrade from SQL 2000 on our Windows 2003 SP1 Standard Edition.
When we run the upgrade, we got a error "Upgrade Advisor Return -1" as a pop-up window.
When we run the Upgrade Advisor separately, we get this error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------
Program Location:
at System.Xml.Schema.SchemaInfo..ctor()
at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable)
at System.Xml.XmlReaderSettings.get_Schemas()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh()
at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
We are in a lost for solutions. We do hope to hear from anyone asap...
Greatly appreciated for any kind assistance.
Thanks.
Hi,
We are running a test upgrade form sql 2000 standard edition 32 bit to sql 2005 developer edition 32bit. Followed through all the steps and specified the account(SA priveleges and currently used by the 2000 version) and is the local admin on the box for the services and the setup seemed to move fine, except for when it got to the point of installing database services - This is the error message that I got:
MSSQLServer could not be started. Verify you have sufficient priveleges to start system services. The was no choice but to hit cancel and then the set up progressed with a final message of 'SEtup failed'.
Here is a portion of the error log:
Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
Database Mirroring Transport is disabled in the endpoint configuration.
Starting up database 'master'.
Converting database 'master' from version 589 to the current version 611.
Database 'master' running the upgrade step from version 589 to version 590.
Error: 946, Severity: 14, State: 1.
Cannot open database 'master' version 589. Upgrade the database to the latest version.
System Task Aggregate global counters produced an error that was not handled. Major: 9, Minor: 46, Severity:14, State:1
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 15151, severity 16, state 1, address 01C4C50B. Use the exception number to determine the cause.
Error: 15151, Severity: 16, State: 1.
Cannot find the endpoint 'TSQL Local Machine', because it does not exist or you do not have permission.
Any help would be greatly appreciated
Thanks KR
Greetings,
I have a client whose DBA upgraded their SQL 2000 database to SQL 2005. They still have it running in 8.0 compatibility mode, for various reasons, and would like me to help them develop a migration strategy to get them to full 9.0 compatibility.
My question is: can I run the Upgrade Advisor on a SQL 2005 engine database running in 8.0 compatibility mode and get anything useful? I already know there will be some issues with existing DTS packages but it would be nice to catch any other issues in advance.
I know that the Upgrade Advisor was run before the upgrade took place but to my knowledge the report was not saved and none of the recommendations were followed.
Any help would be greatly appreciated.
Cheers,
Lon
My SQL statement:
SQL Server seems to think my SQL Statement is too long........ I'm not sure why.
"INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,DateInitiated) select 'Benjamin Short' ,'6' ,'2' ,'Printer' ,'40' ,'' ,'';"
Error message:
The identifier that starts with 'INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,' is too long. Maximum length is 128.
Hey, ive got a listof events that have occured on my site, updates etc. and Im trying to show how long ago the updates happened. For instance, say the date of an update is : 16/10/2007 15:16:03 I want the Label to say "Happened over 2 months ago" etc.Now ive tryed to use an IF statment but I cant seem to get it right : DateTime dt = Convert.ToDateTime("16/10/2007 15:16:03"); if(dt.ToShortDateString() == DateTime.Now.ToShortDateString()) { //happened today if(dt.ToShortTimeString() == DateTime.Now.ToShortTimeString()||dt.ToShortTimeString() < DateTime.Now.AddMinutes(-1).ToShortTimeString()) { //happened within a minute UpdateLabel.Text = "About A Minute Ago"; } } else if (dt.ToShortTimeString() == DateTime.Now.AddDays(-1).ToShortTimeString()) { //happened yesturday UpdateLabel.Text = "Updated Yesturday"; } else if (dt.ToShortDateString() == DateTime.Now.ToShortDateString() || dt.ToShortDateString <= DateTime.Now.AddDays(-7).ToShortDateString()) { UpdateLabel.Text = "Updated Last Week"; }Any ideas where Im going wrong? Im probally staring it straight in the face, but I cant see it. Thanks in advance John
View 1 Replies View RelatedSQL 7.0 SP1 - NT 4.00 EE SP6a - Cluster - Multiprocessor(2)-VB 6.0 SP4.
Maybe a very stupid question,but I need to know if it is possible to assign a low priority to a VB exe that query for long time eating all the CPU (100% in task manager).
Thank you.
Franco
I have a challenge and am not very versed with coding this situation. The situation is I am trying to put long text (more that 255 char) in a ntext field in SQL7, but the max char can input is only 255. Anyone can advice on how to put in more that 255 char in a field as well as how to select back the record whice more than 255 char.
Please give advice if you encounter this problem before.
Regards.
Anyone have same sample on how to put in and retrive long text (more then 255 characters).
Please Comment.
------------
Ray Miao at 7/19/00 8:24:08 AM
You should use readtext and writetext to access text column. By the way, nchar and nvarchar can hold up to 4000 characteres.
------------
Wong at 7/18/00 11:43:15 PM
I have a challenge and am not very versed with coding this situation. The situation is I am trying to put long text (more that 255 char) in a ntext field in SQL7, but the max char can input is only 255. Anyone can advice on how to put in more that 255 char in a field as well as how to select back the record whice more than 255 char.
Please give advice if you encounter this problem before.
Regards.
How long exactly is the text datatype in non-unicode characters for SQLS7? I guess I need to find out how long my input is and break it up into multiple text fields, but what's the max length?
View 3 Replies View RelatedI have created a database which has a table whose name is 128 characters long; this table has a column whose name is 128 characters long. When I issue the command sp_helpconstraint table_name, SQL Server 7 returns the message "string or binary data would be truncated."
Any suggestions?
Thanks!
Hi
When writing a stored procedure,sometimes the line of sql code could be very long and be more than 128 caracthers.
I got an error message saying that the code is vey long and the maximum is 128.
But at the same time, this is erratic in MSSQL server because it works sometimes even with a line of code of more than 800 caracters...
Have you ever experienced the same problem??
What is the solution?
Thanks
Hi,
Roughly how long would it take to shrink a 300GB db, its been running over 8 hours already (throught enterprise manager)?
Thanks in advance
aido.
Dual P4 xeon 2gb ram 350gb scsi raid array
Hello.
I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.
SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)
I have tried to rewrite the query to an INNER JOIN instead.
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)
but the query still takes 1,5 seconds.
It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.
How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?
The index I have on the table is a clustered index on (column 2).
Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.
If I remove some columns from the SELECT-list the query will execute alot faster:
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.
Booth the CPU and the elapsed time goes down and now appears to be more normal.
So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?
Any ideas?
I am running Microsoft SQL Server 2000 - 8.00.2039
Hi everyone,
I have tranferred my database from Access to SQL. In some cases where there is a very long text in the field of "content" (thats the name of the field) appears the sign <Long Text> while in some other fieldsof the "content" the text appears normally...
I have used ntext 16 as a datatype... How can i dissapear this sign from the field...is there any way?? Thank you
Maybe a 'dumb' question, but I can't figure it out. I can't tell from the logs since they are periodically truncated, so how can I tell when the system was last restarted?
I would like to know the period of time that a dmv has been updating a particular view.
Your help is most appreciated.
-- M
Hey SQL experts.
How Long will it take to restore 460GB database and what are the precaution we should take care of
The following ALTER takes about 2 hours in my environment. totalnumber of records is about 2.8 million. IS this typical? Is there away to speed up this process.BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLESET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONALTER TABLE dbo.PERSON ADDFL_CNSL_NTFY char(1) NOT NULL CONSTRAINT DF_PERSON_FL_CNSL_NTFYDEFAULT '',CD_INTRP_NEED smallint NOT NULL CONSTRAINT DF_PERSON_CD_INTRP_NEEDDEFAULT 0GOCOMMITThanks for any tips on this issue....
View 4 Replies View RelatedHi,I am still not very proficient in SQLServer. So apology if thequestion sounds basic.We have a script to clean old unwanted data. It basically deletesall rows which are more than 2 weeks old. It deletes data from33 tables and the number of rows in each table runs into few millions.What I see in the script (not written by me :-) ) is that all data isdeleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. AsI have background in informix, such an action in Informix may resultin "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.Also won't it have performance problem if all rows are marked lockedtill they are committed.TIA.
View 3 Replies View Relatedhi there, i have following code
SET QUOTED_IDENTIFIER ON
DECLARE @sTableDiff varchar(1000)
SET @sTableDiff= " ""C:Program FilesMicrosoft SQL Server90COM ablediff"" -sourceserver ""VSNET1"" -sourceuser ""sa"" -sourcepassword ""sa2345"" -sourcedatabase ""PMSTEMP"" -sourcetable ""DEMAT"" -destinationserver ""VSNET1"" -destinationuser ""sa"" -destinationpassword ""sa2345"" -destinationdatabase ""PMSRESTORE"" -destinationtable ""DEMAT"" -f ""c:Diff"""
PRINT @sTableDiff
EXEC XP_CMDSHELL @sTableDiff
When i execute it, throws error as
Msg 103, Level 15, State 4, Line 3
The identifier that starts with ' "C:Program FilesMicrosoft SQL Server90COM ablediff" -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sour' is too long. Maximum length is 128.
When i split the Identifier as
SET QUOTED_IDENTIFIER ON
DECLARE @sFull varchar(1000)
DECLARE @s1 varchar(1000)
DECLARE @s2 varchar(1000)
DECLARE @s3 varchar(1000)
DECLARE @s4 varchar(1000)
SET @s1= '""C:Program FilesMicrosoft SQL Server90COM ablediff""'
SET @s2= ' -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sourcedatabase "PMSTEMP" '
SET @s3=' -sourcetable "DEMAT" -destinationserver "VSNET1" -destinationuser "sa" -destinationpassword '
SET @s4=' "sysadm" -destinationdatabase "PMSRESTORE" -destinationtable "DEMAT" -f "c:Diff"'
SET @sFull = @s1 +@s2 + @s3 + @s4
EXEC XP_CMDSHELL @sFull
this executes fine but the optput is as
The input line is too long.
NULL
What is this?
how could i execute such long commad using CMDSHELL?
Regards,
Thansks.
Gurpreet S. Gill
Problems Encountered Upgrading SQL 2005 SP1 to SQL 2005 SP2 (9.0.3042.1):
In Summary:
Maintenance Plans Broken
Database Mail Broken
Reporting Services Report Viewer web part displays US Format dates instead of report design English-Australian. .
In Detail:
Microsoft SQL Server 2005 SP2 was downloaded from Microsoft.com. This download occurred after 5 March 2007 and the file version was 9.0.3042.1 (SQLServer2005SP2-KB921896-x86-ENU.exe)
During the Installation of SP2 by a Domain Administrator an error occurred on the Database Engine. A Hotfix Exception was raised and the setup completed with errors.
The Error Log reported a problem:
----------------------------------------------------------------------------------
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29534
Error Description : MSP Error: 29534 Service 'MSSQLSERVER' could not be started. Verify that you have sufficient privileges to start system services. The error code is (1067) The process terminated unexpectedly.
----------------------------------------------------------------------------------
The SQL Server service could not be manually started. An error was reported in the event viewer:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
It was noted that a new file mssqlsystemresource.ldf had been copied to the %data% folder.
Note: As part of separating data and log files, both the master and mssqlsystemresource databases had been previously moved to another volume %data% whilst their respective log files had been moved to a separate volume also %log%. This had occurred after SP1 was initially installed and months prior to the SP2 install using Microsoft KB articles to detach and reattach the databases. It appears that the SP2 installer does not work correctly if mssqlsystemresource mdf and ldf files are not in the same location.
Whilst the Engine could not be started €“ the new mssqlsystemresource.ldf file was manually moved to the %log% file, overwriting the previous version.
The SQL Server Service was now able to start without any errors.
Since the SQL Server Service has been started two major errors have become apparent:
The following error is recorded when trying to create a new maintenance plan:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'from_msx'.
Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
Also when using the maintenance plan wizard the following error is recorded when the Wizard attempts to save the plan at the end:
TITLE: Maintenance Plan Wizard Progress
------------------------------
Saving maintenance plan failed.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
Database Mail is also broken on the server. The Database Mail Log reports an €œActivation Failure€? when a test email is sent. (the email does not arrive). The SQL Log and event viewer also report the following message:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Another error affects Reporting Services reports that are accessed using the SharePoint V2.0 Web parts ( rsWebparts.cab) . The date picker in the Report Viewer web part now only recognises US format dates. It is important to note that the same reports run through the Report Manager web site work correctly using DD/MM/YYYY (English-Australian ) date format for the report picker.
It is noted that the rsWebparts.cab file was not updated by SQL 2005 SP2 install. However, the behaviour of the Report Viewer web part has definitely been affected by the SP2 install.
Are the first two issues related? Are they due to the fact that mssqlsystemresource mdf and ldf files had been moved to an unexpected location prior to SP2? How can this be fixed?
Are the Reporting Services SharePoint (V2) web parts going to be updated? We are not in a position to upgrade our Portal 2003/SharePoint Servcies V2 installation at the present time.
Here is a fairly basic question in which I'd like assistance. In determining a standard for a schema, for simplicity, my thinking is that using the long data type would meet our purposes.
Are there any thoughts on mapping out where we would not need to take advantage of long vs short? I realize the space but for this particular table, space won't an issue.
Thanks!
I have a program that allows various users to login to the sql server. On the login window there is a dropdown that lists all the databases on the server and the user can select which database they wish to login to. To get the list of databases on the server I use a login created for the program we will call 'worker'. The program logs in as worker and runs "SELECT * FROM sys.databases". Before I load the login window, I check to make sure the login worker exists or has been corrupted (in case a user deletes it or changes the password, etc) and if it has been corrupted or deleted, I recreate the login using the "CREATE LOGIN worker WITH PASSWORD = '123' " function.
The problem occurs after I recreate the worker login. The login is created successfully but when the login window appears and logs in as the worker login to get a list of the databases, I get an error saying the login failed for worker. If open the login window again, everything works fine (the worker login isn't created again as it already exists). Further, if I run the same code but put a break point in the code and step through everything, it works the first time.
Is there an amount of time that is necessary to wait for the CREATE LOGIN function to be executed?
Hello.
I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.
Code Snippet
SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC
Code Snippet
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)
I have tried to rewrite the query to an INNER JOIN instead.
Code Snippet
|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)
but the query still takes 1,5 seconds.
It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.
How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?
The index I have on the table is a clustered index on (column 2).
Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.
If I remove some columns from the SELECT-list the query will execute alot faster:
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.
Booth the CPU and the elapsed time goes down and now appears to be more normal.
So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?
Any ideas?
I am running Microsoft SQL Server 2000 - 8.00.2039
SQL 2000 -> SQL 2005 upgrade on windows server 2003 ... We've done numerous side by side types of upgrades which have the advantages of being able to migrate things at a slower pace with limited downtime. I have a situation where it seems like it would be the least amount of work to do an in place upgrade involving 22 transactional in-house developed databases and associated in-house developed applications. There's no replication, no DTS packages, nothing I can think of that would complicate the upgrade. I was wondering if there's people out there that have done lots of these and haven't had any problems OR if there have been lots of problems. Anyway, any advice would be appreciated.
John
What is the equivalent to a long data type in SQL Server 2005?
View 3 Replies View RelatedI have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
View 3 Replies View Related Hi
I
have a textbox field that take 2000 characters from user..Then I used a store
procedure to save that user input into database through an insert statement, but
for some reason it just never store the whole string of 2000 characters but only
store some of it (like 100 or something) .. Seems like a data type problem…(I am using SQL server 2000)
This
is what I have defined:
---------------------------------------
In
storeprocedure:
@iidea2
varchar(2000)
Greetings,
I have a problem that I'm not sure how to handle. In the application I am working on I will be importing fixed length strings from a CD into the database. Each specific character in the string represents some value.
I can't decide if I should just create a single field of 895 characters or create 895 single character fields. When I need data from the database I won't need all 895 characters of information, I'll only need one or a small subset of values.
Does it really matter which approach I take?
Thanks in advance for any advice/tips you can provide.
Zack
Hi everyone.... I'm trying to execute this update statement... It takes an eternity... any ideas on how to rewrite or speed it up?
It's a several step process... below is everything that i run, one step at a time. The final update statement is what takes so long. It should only affect about 2600 rows out of a potential 9000. That's why I'm confused on the response time
select d.olddevicename, de.device, d.newdevicename into #temp9
from dns d, devices de
where de.device = d.olddevicename
update #temp9 set device = newdevicename where olddevicename = device
update devices set device = #temp9.device from #temp9, devices where #temp9.device in
(select #temp9.device from #temp9, devices where #temp9.olddevicename = devices.device)
Thank you!
J.
Hi all,
I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.
The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?
NOTE: the sql maintanace job does not include the backup of database and transaction log.