Changing Database Has Slowed Down Query?

Jul 15, 2015

I had a query that executed in about 20 seconds pulling from database 1. The only changes I made were to make it pull from database 2. It is currently running and has been for over an hour and a half.

View 2 Replies


ADVERTISEMENT

SQL Server 2000 Image Query Has Slowed To Almost A Stop

Jan 11, 2008

HI

I have a very simple query that simply does this: select * from badgetemplates where badgeid=39 , the badge template table has four fields, one of them is of type IMAGE, however the data on badge 39 IMAGE field only amounts to about 600kb, it used to run very quickly (a second or so) now it takes over a minute, i have checked this via the SQL profiler. What is strange is if I remove some of the data stored within the IMAGE field and reduce it to much smaller that 600kb it runs very quickly again. 600kb is fairly small and it used to return this very quickly as I say. Other queries are running at normal speed and the server perfomance does not seem to be reduced elsewhere, it only seems to have effected this only table but only with data over a certain size, the table itself only contains 20 or so rows. What could this be?!

Any help would be really appreciated as I have no clue.....

Dave

View 4 Replies View Related

Lock Problem And Application Is Slowed Down

Nov 4, 2006

Dear Memebers,I have a critical problem. I have an application is running on 64 bitmachine. It used to be running on 32 bit machine. That application isusing a Stored Procedure that uses SELECT, INSERT, UPDATE statements.Whenever this applicataion is being run all the processes are lockedand INSERTING operation becomes cumbersome. We tested again on 32 bitmachine however it happened again. So what might be the problem? Cansomebody help me ? Application creates a lot of processes in a minuteShould SQL Server be caple of recieving these fast inserting processes?As a DB Admin what should I do to find out whether this problem iscoming from SQL Server or not?If you respond me ASAP I really appreciate itRegardsLS

View 3 Replies View Related

Sql Slowed To A Crawl After Deleting Mass Rows

Apr 25, 2007

Hi,

I've deleted about 3-4 million rows from one of my tables as the data was old and no longer needed. The problem is that now queries are runnning extra slow. I am in the process of running taras isp_ALTER_INDEX however its taking quite a long time and seems to be slowing things down even further while its running as expected. (It's been running 4 hours already, I have stopped it and will rerun it a slower traffic period for the db server)

Just wondering if I have the right approach here or if anyone else has any suggestions.

Thanks for your help!

View 14 Replies View Related

Server Slowed After Upgrade To SQL Server Enterprise Edition 2005 32 Bit

Nov 22, 2007


I have upgraded a server with 4 CPU's 1.7 Dual Core AMD opteron.
Running with 16 GB RAM, on windows server 2003 enterprize edition.

and my server has really slowed down ?
An i missing something need urgent help

View 6 Replies View Related

Changing Database Mail Host Database

Jan 26, 2007

Hi

I am trying to change the host database (MSDB) of Database Mail to some new database (say NewDB) and

unable to make DatabaseMail90.exe point to this new database (NewDB).

Is it possible to do this?

Thanks

Uddemarri

View 4 Replies View Related

QUERY : Changing Row To Column

Apr 21, 2008

Hi all,

I have a problem writing a query to change row to column with some conditions and computations. It's appreciated if you guys can give some ideas.

Below is the sample data:
CREATE TABLE Item (Line int, Code nvarchar(8),Price decimal, Check nvarchar(1))
INSERT INTO Item VALUES (1,'001',200 ,'Y')
INSERT INTO Item VALUES (2,'002',300 ,'Y')
INSERT INTO Item VALUES (3,'003',500 ,'Y')
INSERT INTO Item VALUES (4,'004',1000,'N')
INSERT INTO Item VALUES (5,'005',2000,'N')

The expected result :
Line with check = 'N' must be converted to column(s).

Line Code Price Item004 Item005
1 001 200 1000 * (200/200+300+500) 2000 * (200/200+300+500)
2 002 300 1000 * (300/200+300+500) 2000 * (300/200+300+500)
3 003 500 1000 * (500/200+300+500) 2000 * (500/200+300+500)

Note : I would like to avoid using any cursors if possible.

Thanks in advance.

cheers,
erwine

... sql is fun...

View 3 Replies View Related

Changing The Query Based On Value

Jan 5, 2008

in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:

example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70

i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."

any idea on what i am doing wrong?

ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007
@SITEID INT,
@SENSOR VARCHAR(10),
@MIN INT,
@MAX INT,
@YEAR INT


AS
DECLARE @S AS VARCHAR(12)
SET @S=(
SELECT
CASE @SENSOR
WHEN 'TS1' THEN 'TS1MIN'
WHEN 'TS2' THEN 'TS2MIN'
WHEN 'TS3' THEN 'TS3MIN'
WHEN 'TS4' THEN 'TS4MIN'
WHEN 'TS5' THEN 'TS5MIN'
WHEN 'TS6' THEN 'TS6MIN'
WHEN 'TS7' THEN 'TS7MIN'
WHEN 'TS8' THEN 'TS8MIN'
END AS SENSORVALUE)




select top(10)*
into #tempMin
from sitecontroldataarchive
where siteid = @SITEID
and @S < @MIN --this is where the error occurs,
SELECT * FROM #tempMin

View 11 Replies View Related

Changing The Format For A SQL Server Query

Apr 3, 2006

Hi,

I have to generate a daily report of survey answers by users? My question is there a way to reformat the query so it generates a table or report with it showing the rows as columns instead.

Here is my initial query.

SELECT

dbo.Reporting_SurveyAnswers.DateCreated AS DateCreated

,dbo.Reporting_SurveyAnswers.questionid AS QuestionID

,dbo.Reporting_SurveyAnswers.surveyid AS SurveyID

,dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

,dbo.Reporting_SurveyAnswers.userid AS UserID

,dbo.Reporting_User.LastName1 AS LastName

,dbo.Reporting_User.FirstName AS FirstName

,dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

,dbo.Reporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer


FROM

dbo.Reporting_Surveys

INNER JOIN dbo.Reporting_SurveyQuestions

ON dbo.Reporting_Surveys.surveyid = dbo.Reporting_SurveyQuestions.surveyid

INNER JOIN dbo.Reporting_SurveyAnswers

ON dbo.Reporting_SurveyQuestions.QuestionID = dbo.Reporting_SurveyAnswers.QuestionID

INNER JOIN dbo.uvwReporting_User

ON dbo.Reporting_SurveyAnswers.userid = dbo.uvReporting_User.userid

WHERE

dbo.uvReporting_SurveyAnswers.surveyid = 1125

Order by dbo.Reporting_SurveyAnswers.DateCreated

,dbo.Reporting_SurveyQuestions.ordernumber

Select

dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

, dbo dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

To complicate matters, some of the users did not answer some of the questions and some of the questions are duplicated in the rows because the database assigned them one answer each.

Example. Question 18 says "Name all the industries you have worked in. Check all that apply.

What happens is lets say the user checks 4 different boxes. In the query results, it will show 4 rows with question 18 with each answer they checked off.

Any help would be appreciated.

Thanks

The Accidental Tourist

View 3 Replies View Related

Changing The MDX Query Based On A I/P Parameter. Is This Possible?

Jul 8, 2006

Hi



I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors.

First of all i want to know if this is possible and if yes, how?

Also , Is it possible to open a window form on clicking any report data

(Similar to assigning hyperlink, but i want to open a window form instead!!!)





regard



Sai

View 2 Replies View Related

Changing The Number Format In SQL Query

Jan 30, 2008

Hi


I have query

SELECT col1, a.Inv_Amount as Amount
FROM SPS_Oustandings a

I can get the result values for example as 12345.67 as result, but i need it in german format with , used as decimal point and . as thousand seperator

i need output as 12.345,67
My DataGrid (ASP.NET) is directly bound to SP which have this query, i want to show amount in above format in grid.

Please Help,
Thanks

View 6 Replies View Related

Changing Database Name

Nov 19, 2002

hi, I have created a database in sql server 2000. I need to duplicate the database under different name, how can I do that?
Also, can I rename a database name?
thanks
Ali

View 3 Replies View Related

Changing Field Lenght Via Query Analyzer

Jul 17, 2004

Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB

its 70 rights now I want change it to 120 its a nvarchar


how can I do it , I am using a ASP page


Thanks

View 1 Replies View Related

Changing Query To Point To DB Table But Has Errors

Aug 5, 2014

CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(255))

[Code] ....

I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.

What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.

My failed attempt:

Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL

[Code] ....

View 2 Replies View Related

Changing Database Name In DTS Transformation

Nov 26, 1999

I have come across a problem having developed my DTS packages on one database (dev server) which has a different name to the database on the Test/Live servers. I had planned for this in that I was changing the server and database source before executing the package. When I tested this it turns out that the file import transformation that references the table to import into specifies the whole database.owner.table name and the database portion has not changed and therefore the package fails. I've dumped the package using the dts utility to view the vb script and have found the property DestinationObjectName (part of the custom task object for the relevant step...) which looks like the one to change but is this really what has to be done?

regards
Ken

View 2 Replies View Related

Changing Database Context

Mar 15, 2001

Hi all,

I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:

Error 5701
Severity Level 10
Message Text:
Changed database context to '%.*ls'.

However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).

Any ideas why this would start happening?

View 1 Replies View Related

Changing My Default Database?

Apr 27, 2007

Hi.

I thought I had this solved but after running aspnet_regiis again
I am having the same problem.

I can't log onto my sql server because the default DB is master.
The following command using sqlcmd gives me the master db:


Code:


SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO



So, to try to fix this i ran:


Code:


EXEC sp_defaultdb 'sa', 'my_default_db'
GO



and no luck. I went over to the DB server and checked
the permissions under SERVER/SQLEXPRESS > SECURITY > LOGINS. All except NTAuthority and Administrators have
my intended DB as the default (including ASPNET).

This is frustrating because I know I have figured it out
before but I can't remember what I did. Help appreciated.

Thanks.

View 1 Replies View Related

Changing SQL Collation On Database

Jan 11, 2008

Hi there,

We currently install some of client's databases on SQL Server 2000 with the collation set to Latin1_General_BIN but we have one client that was installed and now running on SQL_Latin1_General_CP1_CI_AS and I was wondering if we can change the collation on the database to Latin1_General_BIN?

Would this have any adverse affects on the DB or the data within it? Our strings are nvarchar.

Thanks for your help

View 2 Replies View Related

Changing The Database To Unicode?

May 12, 2008

Hi,

I currently have a web application connected to a MSSQL 2005 database. We are signing some new clients to our web application who will be using a different language set. So were going have to change the database to Unicode? Anyone have any experience with this, or what are the steps implications involved?

Thanks.

d

View 1 Replies View Related

Cannot Access Database After Changing Password

Dec 11, 2006

that sounds obvious but I'm stuck...
I have an ASP.net application which someone else started and I continue.
on the SQL server there was a user X which was used to access the database from the ASP website.
 I accidently changed the password in the SQL server and now I cant restore the prior settings
(trying to reset password to blank or embedding the username and password in the connection string didnt work).
it stops on "connection.open" with -  "SQLException Login failed for user 'X'"
any ideas why that happens and how I can fix it?
 
thanks

View 1 Replies View Related

Changing My Database Path In Web.config

Nov 14, 2007

Hey guys,
I uploaded my website from VWD Express to ipower.  My database did not work, which I have learned upon some research should have not been a shock to me, but most definetely was.  When I contacted tech support they told my that my web.config files were pointing to my local paths.  That I should change them and I should be fine.  Elsewhere, in the knowledge base I found the strConnect that I am under the impression I need to use.
strConnect = "Provider=SQLOLEDB; Server=SQL-A1; UID=account_username; PWD=account_password; Database=user_db_name" oSQLServer.Connect strServer,strLogin,strPwd
The problem is, I'm a complete newbie, learning as I go and I can't find anything on where or how I insert this into my web.config to correct my paths.  IPower will not help with coding.  I know my current connection string is in web.config in the appsettings, that's about as far as my knowledge goes.
So, could anyone show me how I go about putting this into my web.config?  Examples are very helpful :]
On a side note, I've been reading that this is a common problem among newbies like myself.  So, is changing these files actually going to work, or am I wasting my time?  Also, before I get knee-deep into it, is the database-publishing kit put out by MS the way to go here?
 Thanks for any and all replies, you guys are always awesome.

View 6 Replies View Related

Changing Suspect Database To Normal

Apr 7, 2008

My sql database is in suspect mode, please help me with step by step how can i change the suspect status to normal status, please dont provid eme the links of other site and help me in your own language
 
iam getting following error:
 
TITLE: Microsoft SQL Server Management Studio------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ADDITIONAL INFORMATION:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=926&LinkId=20476
------------------------------BUTTONS:
OK------------------------------Thanks in advance

View 3 Replies View Related

Changing Collation_name Of SqlServer Database

Nov 2, 2003

Hi

I want to change collation_name of my SqlServer Database by running a script. But I don't know how.
Please help me.

Thanks

View 1 Replies View Related

Changing Database Structures Through Code

Apr 28, 2006

I am having a hard time finding materials on this subject.  I am guessing I am using the wrong keywords to search.  Basically, I want to be able to modify database tables through a web form.  They can add columns and delete columns through the form.  I would just want to default the type of column and the length.  I am sure it has been done, I was just wondering if anyone had some resources they could throw my way.  I would appreciate it.  Thanks.

View 1 Replies View Related

Changing The Default Location Of A New Database

Oct 16, 2006

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:Program FilesMicrosoft SQL ServerMSSQLData
I want to change that to:-
D:Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:Program FilesMicrosoft SQL ServerMSSQLData

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abel

View 3 Replies View Related

Changing Database Collation Setting?

Mar 6, 2012

I currently have a server with the collation set to SQL_Latin1_General_CP1_CI_AS.

However, some of the databases within the server are set to Latin1_General_BIN, probably because they were restored from another server some time ago. Also, even within the databases that have Latin1_General_BIN, some of the columns are set to SQL_Latin1_General_CP1_CI_AS, very confusing to say the least.

What i would like to do is change the database collation settings for these databases to match the server setting. I would also like to change all of the tables within these databases to have the columns also set to the server collation settings.

I'm looking for the steps that i would need to take to make sure i don't mess anything up as these databases have there own sets of views and sp's that run each day.

View 3 Replies View Related

Changing Collations For Master Database.

May 3, 2004

Hi there


I am using SQL server 2000 and
I want to Change Server Collations from SQL_Latin1_General_CP850_BIN to
SQL_Latin1_General_CP1_CS_AS.

Can anybody help me in this regard.

Rgds

Wilson

View 5 Replies View Related

Changing Database Within A Stored Procedure

Jul 20, 2005

I need to create a stored procedure in the master database that canaccess info to dynamically create a view in another database. Itdoesn't seem like it should be very hard, but I can't get it to work.Here's an example of what I want to do.CREATE PROCEDURE create_view @dbname sysnameASBEGINDECLARE @query varchar(1000)SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'EXEC(@query)ENDIn this case, I get an error with the word "go". Without it, I get a"CREATE VIEW must be the first statement in a batch" error. I tried asemicolon in place of "GO" but that didn't help either.Thanks

View 4 Replies View Related

DB Design :: Changing Database In Memory

Aug 27, 2015

I need to change the table codes to in memory optimized but every time I try to change I got an error message like you need to change database to memory optimize.

So, I try to change it  using this code:

ALTER DATABASE Coralreef_ ADD FILEGROUP Coralreef__mod CONTAINS MEMORY_ OPTIMIZED_ DATA.

When I used this  I got an another error:

Msg 10797, Level 15, State 2, Line 1..Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database.

So, How can I change database to in memory optimized database.

View 5 Replies View Related

Dynamic Database Source Changing

May 2, 2006

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

View 6 Replies View Related

Changing Working Database From SQL To SQL Mobile

Nov 9, 2007

I am working on an application that was initially set to connect to a SQL database.

The application is for a Pocket PC and now I have converted the original SQL database to a SQL Mobile database.

Does anyone know if there is a way to change the the code to be able to access the SQL mobile database without having to go through all the code and manually make the changes?

Thanks

View 1 Replies View Related

From Asp Classic && Asp.net To Access Database Changing To Sql Server

Dec 17, 2006

Hello, We are re-writing our site in asp.net using sql server.  Most of the site uses asp classic and it was to an access database.  During the conversion we have everything working correct to SQL Server except for the asp.net connection string.  There is an important part of the application using asp.net which works fine with our connection string to the access database.  To recap our problem is the connection string from asp.net to sql server. This code works fine for the asp.net to access in the web.cnfg file     </microsoft.web>     <connectionStrings>         <add name="SalesConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb" providerName="System.Data.OleDb"/>         <add name="ODBCSalesConnectionString" connectionString="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb"/>         <add name="RawConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb" providerName="System.Data.OleDb"/>         <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|inventoryStatus.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient"/>     </connectionStrings>     <system.web> The code in the global.asa which works fine for classic asp to sql server also works fine and is as follows     '--Project Data Connection         Application("sales_ConnectionString") = "Driver={SQL Native Client};Server=DMSERVER01;Database=SQLsalesMain;UID=nTrack;PWD=nTrack2k3"         Application("sales_ConnectionTimeout") = 15         Application("sales_CommandTimeout") = 30         Application("sales_CursorLocation") = 3         Application("sales_RuntimeUserName") = ""         Application("sales_RuntimePassword") = "" Our programmer who set this up is out for a couple of weeks and I would appreciate any help in the correct connection string from asp.net to the sql server database in the web.cnfg file Thanks

View 5 Replies View Related

Changing All VARCHAR To NVARCHAR Database-wide?

Jan 15, 2005

Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).

This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?

Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR

thanks!

-Bret

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved