SQL Server 2000 Randomly Slow

Jan 21, 2008

Hello there,

I'm using a SQL Server 2000 with a dozen of databases. The databases are rather small (all sizes together sum up to 10 GB).

The entire server gets extremelly slow from time to time (lasting a few days when it happens and suddently coming back to normality). A profiler trace doesn't show anything strange (besides a lot of SQL Agent entries).

I pretty much tried to isolate every single application that makes use of the databases in that server and see if it was the cause of the problem, but I couldn't find any correlation.

I know the computer where this server runs is quite fragmented. Is there anyway I could get to know if this is the cause of my performance issues?

I don't know if this happens, but once the server simply went down for some 3 hours, and I wasn't able to bring it up in anyway. It eventually started working again by itself. The only thing I did in the meantime was to run DBCC CHECKDB a few times, always getting the response "No error found on the database".


Any hint on that?

View 1 Replies


ADVERTISEMENT

Sql Server 2000 Randomly Slowing Down

Jan 22, 2008

Hello there,

I have a SQL Server 2000 with a dozen of databases. The databases are rather small (all sum up to 10 GB).

The entire server gets extremelly slow from time to time (lasting a few days when it happens and suddently coming back to normality). A profiler trace doesn't show anything strange (besides a lot of SQL Agent entries).

I pretty much tried to isolate every single application that makes use of the databases in that server and see if it was the cause of the problem, but I couldn't find any correlation.

I know the computer where this server runs is quite fragmented. Is there any way I could make sure this is the cause of my performance issues?

I don't know if this might help, but once the server simply went down for some 3 hours, and I wasn't able to bring it up in any way. It eventually started working again by itself. The only thing I did in the meantime was to run DBCC CHECKDB a few times, always getting the response "No errors found on the database".


Any hint on that?

View 3 Replies View Related

How To Randomly Select Records From Sql Server 2000 To ASP.NET(vb Language)

Mar 19, 2008

Hai friends,,
  I have a table name "Student"
it contain 2 fields no ,name
no      name
1        Raja
2        Larsen
3        Ravi
4        Ankit
5        Eban
my questions  is I have a webform random.aspx
whenever any user open a webform random.aspx
it should display anyone of name  in a random order..... CODING:-
Dim cn As New System.Data.sqlclient.SqlConnectionDim rd As sqlDataReader
cn.ConnectionString = "Persist Security Info=False;User ID=sa;Initial Catalog=master;password=david;"
cn.Open()Dim cmd1 As New SqlCommand("select no,name from Student", cn)  ' how i can chage to random order....
rd = cmd1.ExecuteReader
rd.Read()
Response.Write(rd(0) & "." & rd(1))
rd.Close()
thank u..
Ambrose...
 
 

View 4 Replies View Related

SQL Server 2000 Slow Down

Apr 19, 2008



Hi,
I am using SQL2K for a intranet web application database. And I have several database in it.
After a long periode of time (about 6 month) the the application is slowing down when accessing the data.
I use full log for the database.
I also set an autobackup for the database for every 15 minute, but I think this is not what cause the slow down.
The database seem to slow down more and more every day.
I keep only the last 3 days transaction data and every day I delete transaction data older then 4 day, so the number of records in the transaction table is just about 3000 to 5000 records only.
Please help me. Thanks.

View 3 Replies View Related

I Need Help With A Slow Sql 2000 Server

Sep 30, 2007

Hi,
We have a server that s too slow. The tables have about between 1 million and 3 million records each. We have about 30 tables for production. The other tables are mostly look up tables (just for personnel and categories and so on, only static tables)

1/ do u guys think that s too much data, I mean about 3 millions records in a table. Because the manager said, may be if u archive some records and delete them from the tables, the system might become quick and performant?

2/ When we check the activity monitor in management in entreprise manager, we find a process Id 55 that has the value 1 in the Blocking column, and other processes having that process ID 55 in the Blocked by column.
Does the fact that the blocking column for a process equals 1 mean that the process is having a problem, and how do we know what causes the problem for sure?

3/When we check Performance monitor, pls note that we have RAID5 with 5 disks, we see that Avg. Disk Queue Length counter ranging between 20 and 70 and especially if we run a query for example, although simple select query in one of the tables, the Avg. Disk Queue Length counter goes to the roof to 100 % in performance monitor.

I don t think we have a pb with the CPU, it looks good, not going to the roof.

For your info, we have the web server on that same SQL server machine as well, but the web server s working well, only sql server and the client application that accesses it is slow, the web pages are pretty fast.

Am I following the good path to diagnose what s wrong. It s my first time doing this, and I am trying to learn.

I appreciate your guidance.

View 4 Replies View Related

Why SP Runs Very Slow In Sql Server 2000 Job!

Sep 27, 2001

Hello ,
I'm a newbie,I programmed a store procedure,and when it runs in query analyzer,
I get results very fast,but when I add it in a job,then this job run very very slow,
(I add records and delete records in the SP), WHY?


TIA!

James

View 2 Replies View Related

Update In SQL Server 2000 Slow?

Jul 20, 2005

I have two tables:T1 : Key as bigint, Data as char(20) - size: 61M recordsT2 : Key as bigint, Data as char(20) - size: 5M recordsT2 is the smaller, with 5 million records.They both have clustered indexes on Key.I want to do:update T1 set Data = T2.Datafrom T2where T2.Key = T1.KeyThe goal is to match Key values, and only update the data field of T1if they match. SQL server seems to optimize this query fairly well,doing an inner merge join on the Key fields, however, it then does aHash match to get the data fields and this is taking FOREVER. Ittakes something like 40 mins to do the above query, where it seems tome, the data could be updated much more efficiently. I would expectto see just a merge and update, like I would see in the followingquery:update T1 set Data = [someconstantdata]from T2where T2.Key = T1.Key and T2.Data = [someconstantdata]The above works VERY quickly, and if I were to perform the above query5 mil times(assuming that my data is completely unique in T2 and Iwould need to) it would finish very quickly, much sooner than theprevious query. Why won't SQL server just match these up while it ismerging the data and update in one step? Can I make it do this? If Iextracted the data in sorted order into a flat file, I could write aprogram in ten minutes to merge the two tables, and update in onestep, and it would fly through this, but I imagine that SQL server iscapable of doing it, and I am just missing it.Any advice would be GREATLY appreciated!

View 3 Replies View Related

Slow Insert In SQL Server 2000

Dec 26, 2007

Hi everybody !!! I have a problem with database SQL server 2000, and I need anyone help me.

I have PC machine with hardware configuration following:

+ RAM : 512M

+ CPU : 2.4 GHz (intel Pentium 4)

+ System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 1

(this is call Machine1 )

and other Server machine with hardware configuration the following :

+ RAM : 2G

+ CPU : 3.6 GHz( intel Xeon)

+System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 2

(use RAID 5)

(this is call Machine2 )

.And SQL Server 2000 is installed in these machine.

I created one table and one script to insert several records into this table. and script for inserting:

declare @count int , @max int

set @count =1

set @max = 5000

WHILE (@count < @max or @count = @max)

BEGIN

insert into TBAT_STOCKEOD(TRDT,SEQNO,COMPID,TRANSTYPE) values('20071219',cast(@count as varchar(5)),'13215','1')

set @count = @count +1

END

I execute this script on Machine1 and Machine2 :

+ Machine1 : spent 3 seconds.

+ Machine2 : spent 30 seconds. I don't understand why Machine2 is slower than Machine1 ( while configuration of Machine2 is better than Machine1's ). I don't know what happen.




I checked resource usages in perfmon when SQL server execute script and result following :

Machine1:

Proccessor used 100%

whereas,Machine2 used physical Disk 100%

Is there the way to configurate SQL server to improve speed writting of hard disk?

View 5 Replies View Related

Slow Query Execution, SQL Server 2000

Jul 20, 2005

Hi All,I have a table that currently contains approx. 8 million records.I'm running a SELECT query against this table that in somecircumstances is either very quick (ie results returned in QueryAnalyzer almost instantaneously), or very slow (ie 30 to 40 seconds toreturn results), and I'm trying to work out how I improve performance.Essentially the query I'm running is nothing more complex than:SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id[tier] is a smallint column with a non-clustered, non-unique index onit. [member_id] is a numeric column with a clustered, unique index onit.When I supply a [tier] value of 1, it returns results instantaneously.I have no idea if this is meaningful, but the tier = 1 records wereloaded first into the table, and comprise approximately 5 millionrecords.When I supply a [tier] value of 2, the results take 30 to 40 seconds.tier =2 records were loaded second, and comprise approximately 3million records.I've tried running an execution plan, and while I'm no expert, itappears to me that the index on tier isn't being used, even if I use:tier = CAST(2 as SMALLINT)I'm wondering if anyone can give me ANY advice on how to get anybetter performance out of this SELECT statement?Also, out of curiosity, can a disk defragment have a positive impacton SELECT query performance?Any help very much appreciated!Much warmth,Murray

View 4 Replies View Related

SLOW Connection To SQL Server 2000 From Enterprise Manager

Sep 9, 2004

I have had a problem with Enterprise Manager connecting to SQL Server. At first this problem was experienced with one particular network user... whichever PC he logged onto, Enterprise Manager took ages to connect to SQL Server and every operation was painfully slow. Creating a new Windows NT logon fixed the problem.

I now have this same problem but only on my PC. It doesn't matter which Windows NT logon I use, using Enterprise Manager is painfully slow. I've tried creating a new Windows NT profile, checking the hard drive for errors, defragmenting the disk, reinstalling Enterprise Manager etc but nothing works.

What is strange is that connections from VB applications on my PC are fast. It is only Enterprise Manager that is slow.

I am using the latest service pack for SQL Server.

I thought it could be a problem with the Enterprise Manager registry values but don't want to start messing with them!

Has anyone else experienced this problem? Any advice would be great as I can't find any help from microsoft other than installing the latest service pack for SQL Server.

Thanks

View 4 Replies View Related

Import Data From SQL Server 2000 Incredibly Slow

Sep 6, 2006

I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?

View 2 Replies View Related

SQL Server 2005 Stored Procedure Is Very Slow Vs. SQL Server 2000

Apr 18, 2008

Hi there,

I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.

I could pin the problem down to this query:


DECLARE @Package_ID bigint

DECLARE @Email varchar(80)

DECLARE @Customer_ID bigint

DECLARE @Payment_Type tinyint

DECLARE @Payment_Status tinyint

DECLARE @Booking_Type tinyint

SELECT @Package_ID = NULL

SELECT @Email = NULL

SELECT @Customer_ID = NULL

SELECT @Payment_Type = NULL

SELECT @Payment_Status = NULL

SELECT @Booking_Type = NULL

CREATE TABLE #TempTable(

PACKAGE_ID bigint,

PRIMARY KEY (PACKAGE_ID))

INSERT INTO

#TempTable

SELECT

PACKAGE.PACKAGE_ID

FROM

PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID

LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID

LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID

WHERE

PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)

AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)

AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)

AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)

AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)

-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented

--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'

GROUP BY

PACKAGE.PACKAGE_ID

DROP TABLE #TempTable


The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.

On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:





942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000

Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.

Thanks again for any help

Best regards,
Pascal

View 9 Replies View Related

Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?

May 8, 2006

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

View 5 Replies View Related

MS Access Linked Tables To SQL Server 2000 Slow On Vista

Mar 13, 2007

I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.

I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.

What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.

I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.

It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?

Thanks in advance for any help on this

View 1 Replies View Related

Visual Basic Slow Down Sql Server 2000 Running A Stored Procedure

Nov 30, 2007

I hav the following problem. I have written an stored procedure in sql server 2000 as the following
CREATE PROCEDURE dbo.pa_rellena
@pFechaInicio datetime

AS
declare @pFechaFin datetime
declare @auxcod_cen char(10)

declare @importeEfectivo decimal(17,2)
declare @importeTarjetas1 decimal(17,2)
declare @importeTarjetas2 decimal(17,2)
declare @importeVales decimal(17,2)
declare @importeTalones decimal(17,2)
declare @importeGastos decimal(17,2)


select @pFechaFin=@pFechaInicio+1


--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado
if object_id('tmpCentros') is not null
drop table tmpCentros

if object_id('tmpCentros2') is not null
drop table tmpCentros2

if object_id('tmpMaxCajas') is not null
drop table tmpMaxCajas

if object_id('tmpCajasCentro') is not null
drop table tmpCajasCentro

if object_id('tmpVales') is not null
drop table tmpVales

if object_id('tmpDiarioEfectivo') is not null
drop table tmpDiarioEfectivo

if object_id('tmpDiarioTalones') is not null
drop table tmpDiarioTalones

if object_id('tmpDiarioTarjetas') is not null
drop table tmpDiarioTarjetas

if object_id('tmpDiarioSegundaForma') is not null
drop table tmpDiarioSegundaForma

if object_id('tmpDiarioGastosTarjetas') is not null
drop table tmpDiarioGastosTarjetas

if object_id('temp1') is not null
drop table temp1

--Seleccionamos todos los centros de Salvador Bachiller
select * into tmpCentros2
from centros
where centros.tienda=1
order by cod_cen

--Seleccionamos el maximo de cajas por cada centro

select cod_cen, max(cod_caja) as cajas into tmpMaxCajas
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen
order by cod_cen

--Mezclamos los centros con el maximo de cajas
select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros
from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen

--Cajas por centro
select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin

--Los vales de cada centro
select cod_cen,sum(importe) as imp1 into tmpVales
from vales where
fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen

--Efectivo de cada centro
select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo
from diario
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01'
group by cod_cen

--Talones por centro
select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones
from centros inner join diario on centros.cod_cen=diario.cod_cen
where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02'
group by centros.cod_cen

--Tarjetas por centro
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas
from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen

--Segunda Froma de Pago
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma
from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen

--Comisiones tarjetas de pago
select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas
from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
/*
--Venta neta por centro
declare cursortemporal cursor for select cod_cen from TmpCentros2

open cursortemporal
delete detallecaja_aux
fetch next from cursortemporal into @auxcod_cen
while @@fetch_status=0
Begin
select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen
select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen
select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen
select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen
select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen
select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen

select @importeVales=isnull(@importeVales,0)
select @importeEfectivo=isnull(@importeEfectivo,0)
select @importeTalones=isnull(@importeTalones,0)
select @importeTarjetas1=isnull(@importeTarjetas1,0)
select @importeTarjetas2=isnull(@importeTarjetas2,0)
select @importeGastos=isnull(@importeGastos,0)

print @auxcod_cen
print @importeVales
print @importeEfectivo
print @importeTalones
print @importeTarjetas1
print @importeTarjetas2
print @importeGastos

insert into detallecaja_aux (cod_cen,importe1)
values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos)
fetch next from cursortemporal into @auxcod_cen

select @importeVales=0
select @importeEfectivo=0
select @importeTalones=0
select @importeTarjetas1=0
select @importeTarjetas2=0
select @importeGastos=0
end

close cursortemporal
*/
select * from detallecaja_aux
GO

When I try to run it from visual basic it slow down the sql server.

What can I do?

View 2 Replies View Related

Randomly Getting SQL Server Does Not Exist Or Access Denied

Apr 18, 2008

Randomly getting  "SQL Server does not exist or access denied" error from my server.  All sites on my server lose connection to the MSSQL database on a windows 2005 server.  Anyone know? There is truly no pattern, it just randomly loses access to the SQL server which is on the localhost.  It's often different pages being browsed when it decides to crash.  Sometimes it's a couple hours between crashes and sometimes just a few minutes. I've heard that I should check the log files, but I'm really not even sure what to check.  Any suggestions appreciated.  Traffic is really light on the server right now too. 

View 1 Replies View Related

Sql Server 2005 Managment Studio Crashes Randomly

Jan 4, 2006

Hi all, i am getting these type of errors in the managment tool where i try to look a stored p. or see the schema list or listing tables in a database.

THe errors looks like this one below but it does change in the InvalidArgument=Value of '***' *** area it gets numbers 448 1 5 or something else and after the error management studio doesnt work correctly, in the summory window nothing shows up, .

here is the detailed error message Please help.



 See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.ArgumentOutOfRangeException: InvalidArgument=Value of '448' is not valid for 'index'.
Parameter name: index
   at System.Windows.Forms.ListView.ListViewItemCollection.get_Item(Int32 index)
   at System.Windows.Forms.ListView.set_VirtualListSize(Int32 value)
   at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.RightPaneListViewer.set_VirtualListSize(Int32 value)
   at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.RightPaneListViewer.PopulateView()
   at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.RightPaneListViewer.Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.IReportView.SetReportSource(ReportSource reportSource)
   at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.RightPaneControl.InternalSetReportSource(ReportSource reportSource, IReportView newView)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
AppIDPackage
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/AppIDPackage.DLL
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
Microsoft.SqlServer.SqlTools.VSIntegration
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/Microsoft.SqlServer.SqlTools.VSIntegration.DLL
----------------------------------------
Microsoft.VisualStudio.Shell.Interop
    Assembly Version: 7.1.40304.0
    Win32 Version: 7.0.4054
    CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.Shell.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.Interop.dll
----------------------------------------
Microsoft.VisualStudio.OLE.Interop
    Assembly Version: 7.1.40304.0
    Win32 Version: 7.0.4054
    CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.OLE.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.OLE.Interop.dll
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Microsoft.SqlServer.SqlTDiagM
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.SqlTDiagM/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.SqlTDiagM.dll
----------------------------------------
Microsoft.DataWarehouse.SQM
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/Microsoft.DataWarehouse.SQM.DLL
----------------------------------------
Microsoft.SqlServer.Instapi
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.Instapi/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.Instapi.dll
----------------------------------------
ObjectExplorer
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/ObjectExplorer.DLL
----------------------------------------
ConnectionDlg
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/ConnectionDlg.DLL
----------------------------------------
SqlWorkbench.Interfaces
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/SqlWorkbench.Interfaces.DLL
----------------------------------------
Microsoft.SqlServer.CustomControls
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.CustomControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.CustomControls.dll
----------------------------------------
Accessibility
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
----------------------------------------
SqlMgmt
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/SqlMgmt.DLL
----------------------------------------
Microsoft.SqlServer.RegSvrEnum
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.RegSvrEnum/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.RegSvrEnum.dll
----------------------------------------
EnvDTE
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC/EnvDTE/8.0.0.0__b03f5f7f11d50a3a/EnvDTE.dll
----------------------------------------
Microsoft.NetEnterpriseServers.ExceptionMessageBox
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/9.0.242.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
Microsoft.SqlServer.ConnectionInfo
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.ConnectionInfo/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.ConnectionInfo.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
Microsoft.SqlServer.SmoEnum
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.SmoEnum/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.SmoEnum.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------
Microsoft.SqlServer.Smo
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.Smo/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.Smo.dll
----------------------------------------
Microsoft.SqlServer.SqlEnum
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.SqlEnum/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.SqlEnum.dll
----------------------------------------
Microsoft.SqlServer.BatchParser
    Assembly Version: 9.0.242.0
    Win32 Version: 2005.090.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/Microsoft.SqlServer.BatchParser/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.BatchParser.dll
----------------------------------------
msvcm80
    Assembly Version: 8.0.50608.0
    Win32 Version: 8.00.50727.42
    CodeBase: file:///C:/WINDOWS/WinSxS/x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd/msvcm80.dll
----------------------------------------
System.Management
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
ObjectExplorerReplication
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/ObjectExplorerReplication.DLL
----------------------------------------
Microsoft.ReportViewer.WinForms
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.42
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.ReportViewer.WinForms/8.0.0.0__b03f5f7f11d50a3a/Microsoft.ReportViewer.WinForms.dll
----------------------------------------
SQLEditors
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/SQLEditors.DLL
----------------------------------------
Microsoft.VisualStudio.TextManager.Interop
    Assembly Version: 7.1.40304.0
    Win32 Version: 7.0.4054
    CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.TextManager.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.TextManager.Interop.dll
----------------------------------------
SqlManagerUI
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/VSShell/Common7/IDE/SqlManagerUi.DLL
----------------------------------------
Microsoft.AnalysisServices
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.AnalysisServices/9.0.242.0__89845dcd8080cc91/Microsoft.AnalysisServices.dll
----------------------------------------
Microsoft.SqlServer.GridControl
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.GridControl/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.GridControl.dll
----------------------------------------
Microsoft.SqlServer.DlgGrid
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DlgGrid/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DlgGrid.dll
----------------------------------------
Microsoft.SqlServer.DataStorage
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1399.00
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataStorage/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataStorage.dll
----------------------------------------
System.Design
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Design/2.0.0.0__b03f5f7f11d50a3a/System.Design.dll
----------------------------------------
Microsoft.VisualStudio
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 built by: RTM
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio/2.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.dll
----------------------------------------
Microsoft.VisualStudio.Shell
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio.Shell/2.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.dll
----------------------------------------
Microsoft.VisualStudio.Shell.Interop.8.0
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.Shell.Interop.8.0/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.Interop.8.0.dll
----------------------------------------
System.Drawing.Design
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing.Design/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.Design.dll
----------------------------------------
Microsoft.VisualStudio.Designer.Interfaces
    Assembly Version: 1.0.5000.0
    Win32 Version: 1.1.4322.573
    CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.Designer.Interfaces/1.0.5000.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Designer.Interfaces.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.


 

View 8 Replies View Related

SQL Server 2012 :: Randomly Delete Records Based On Some Condition

Mar 19, 2014

create table #sample
(
Name varchar(100),
value int

[code]....

From that I wanted to delete some records based on following condition. randomly select any number of records but sum(value) = 125 and name = xxx

View 2 Replies View Related

SQL Server 2014 :: Group Up Records Randomly Into N Number Of Batches

Jul 6, 2015

I need to group up the records randomly into ā€˜nā€™ number of batches. That can be done by NTILE, but I want group up similar records in single group.

Say for example, following is the list of records I have in my table which I want to group into 5 batches

A123
A124
A124
A123
A127

After Ntile I will get the below,

Desired output is, Need output like Ntile but all same id should reside in single batch

Even if I n=5, maximum possibility of batches are 3 only.

View 2 Replies View Related

Slow Select On Sql 2000 With Like '%value'

Jul 20, 2005

Hello.I test some query on sql server 2000 (sp2 on OS windows 2000) and iwant to know why a simple query like this :select * from Table Where Column like '%value'is more slow on 2000 than on sql 7.And this case arrive only if the % character is in the begin.If you test this :select * from Table Where Column like 'v%alue'then it's more fast on 2000.I look the execution plan, there is a difference but this differenceis the same in all the case i test (for the two query i write in thismessage for example).I don't understand why and so, if someone have an explanation, andperhaps a solution ...Excuse my poor english language.And thanks for time people spend to answer me.

View 2 Replies View Related

Slow Sql Update MS SQL 2000

Jul 20, 2005

I have the following statement that takes quite a long time. Longestof any of my SQL statment updates.UPDATE F_REGISTRATION_STD_SESSIONSET PREVIOUS_YEAR_SESSION_ID = (SELECT s.previous_year_session_idFROM F_REGISTRATION_STD_SESSION R,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr SWHEREr.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_IDand s.previous_year_SESSION_ID = r.SESSION_IDand s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID)STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexedWhat I want to accomplish:I want to know if there was a student registration from the prior yearof a registration.Example, if there is a registration for Fall 2004, was there also aregistration for the same student Fall 2003?Maybe there is a better way to approach this?TIARob

View 10 Replies View Related

SQL Slow From MSDE 2000 To SQL Express

Feb 2, 2007

I posted this in one of the VB forums but I'm starting to think itmight be more appropriate to have it here, since it really seems to bea SQL server (MSDE/Express 2005) problem:Hey, all, I have a problem with queries against a SQL server instancethat I just found and is causing me fits. I hope someone can pointmein the right direction, please. TIA.Basically, I got a Vista OS machine to test my VB6 app on it as someof my clients will be switching over in the coming months. I went toa local Circuit City during early business hours in the middle of theweek and I installed my application on each of 5 PC's on the newVistaOS (Tuesday, when it was released). I had read that MSDE 2000, whichI normally use as my DB is not supported on Vista so I had downloadedand was using SQL Express 2005. Each system had at least a 1.9 GHzdual core processor and 1 GB of RAM. One process in my program findsrecords in one table that do not match records in another table andthen reports those un-matched entries. On my development machine(laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHzCentrino (IIRC)) the process takes less than 30 seconds consistently.On each of those 5 systems at Circuit City the process took 5 minutes(on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes oneach of two Gateway systems (the model numbers of which I forget atthe moment). Each of these computers should be much faster than mylaptop, and some had twice the RAM, and all had SATA or SATA IIdrivesinstead of my piddly 5400 laptop drive, I would have thought they'dall be faster but were abysmally slow.So, seeing a huge difference in the time, and to try to keep thisshort and sweet, I fired up another computer I have, running XP SP2,on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and myapplication and ran the process. < 30 seconds on each of multipleruns. Second, I unloaded MSDE 2000 and installed SQL Express 2005andmoved the DB to it (sp_attach_db) which caused some upgrading(messages reported in OSQL about update/upgrade). When it was done Irebooted, to be sure, and the ran the program and the process again.On the same data, on the same computer, the process took 7-9 minutesconsistently on each of several runs. This makes this part of theapplication unusable, and even the simple stuff like grabbing asinglerecord from the DB (maybe 5 columns of no more than 500 bytes total)is noticeably slower on the SQL Express 2005 than on MSDE 2000.So, the problem seems to be with my interaction with the DB. I amusing ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with aconnection string like: Driver={SQL Server};server=(local)caredata;database=caredata;Uid=sa; Pwd=<password>I use the RecordsSet Object to open the data similar to this:oRS.OpenstrSQL$, oCN, adOpenKeysetafter the oCN object has had the connection string set and the objectis opened.Considering that the same computer, against the same data, with thesame program, takes about 14 times (or more) longer to run, then ithas to be either that SQL Express 2005 is slow OR that my program isinteracting with it in an incorrect manner.Can someone point me in the right direction, please?Thank you.--HCSo, the problem isn't Vista

View 11 Replies View Related

Application Slow - Running Double Take 4.4 On SQL 2000

Jun 6, 2005

Hi,

We are having SQL2000 Advance Server.
4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.

We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose.
The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.

We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference.
Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.

We have set just like the Tech support recommended.

I would appreciate, if someone give us some recommendations to run the double take without any problem.

Thanks in advance.
Anu

View 3 Replies View Related

Application Slow After Migration From 2000 To 2005

Mar 8, 2008

Hi,

Recently our database has been migrated from SQL 2000 to SQL 2005 on a new server(machine) with windows 2003(previously windows 2000). If the database is retained on the same machine but with a named instance of 2005, the application(websphere 5.1) is behaving normal whereas if i configure the aplication to the new server it is running slow for some of the queries but not all.

This change will have to be implemented in production very soon. Any advise will be of great benefit

Thanks

Raam

View 6 Replies View Related

Access 2007 Connection To SQL 2000 REALLY Slow

Dec 19, 2006

I have an Access2000 ADP that I want to run under Access2007. The problem I have is that some forms take up to 45 seconds to open in Access2007! These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:

SELECT * FROM ArReceivableType

Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows. They open instantly in Access2000.

I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:

select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'

It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys. My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.

Any suggestion on how to NOT have Access2007 do this? Right now, Access2000 works great for this enterprise app, but I really like the new Access2007 features (and I don't want to still be developing Access2000 apps in 2010).

View 3 Replies View Related

SLOW Performance On Table With Image Fields (SQL 2000)

Nov 15, 2006

HiWe have a SQL server 2000 SP4 on a windows 2003 2x3Ghz XEON 4 GB ram.We have a table looking like this with currently 6 rows. Total data is aprox10 kb i all row all together.CREATE TABLE [dbo].[BIOMETRICPROFILE] ([BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,[FINGERPRINTTEMPLATE1] [image] NOT NULL ,[FINGERPRINTTEMPLATE2] [image] NOT NULL ,[FINGERPRINTTEMPLATE3] [image] NOT NULL ,[FINGERPRINTTEMPLATE4] [image] NOT NULL ,[FINGERPRINTTEMPLATE5] [image] NOT NULL ,[FINGERPRINTTEMPLATE6] [image] NOT NULL ,[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOselect * from BIOMETRICPROFILE takes ~4 seconds (!) to execute thourgh Queryanalyzer. Alle other tables has no performance problems.We have a SQL 2005 express instalation on the same server. If we restore abackup from the sql 2000 database the query takes aprox ~ 15 ms.What isgoing on here?Has SQL 2000 problems with image fields? or how can we find the problem?RegardsAnders

View 2 Replies View Related

VERY Slow Generate Scripts On SQL 2005 Compared To SQL 2000

Aug 1, 2007

i was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.

average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.

can anyone tell why ? thx in advance.

View 2 Replies View Related

Data Transfer From Lotus Notes Very Slow Compared To SQL 2000 DTS

Sep 7, 2006

To extract data from an ODBC source, try the following:

Add an ADO.Net Connection Manager.
Edit the Connection Manager editor and select the ODBC Data Provider
Configure the Connection Manager to use your DSN or connection string
Add a Data Flow Task to your package.
Add a Data Reader Source adapter to your data flow
Edit the Data Reader source adapter to use the ADO.Net connection manager that you added.
Edit the Data Reader source to query for the data you wish to extract.

hth

Donald

Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.

When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.

Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?

Thanks!

View 3 Replies View Related

Selecting Records Randomly With SQL

Jan 29, 2008

I'm looking for a bit of SQL code that will select some entries randomly from an SQL table.

For instance I'd like to feed a parameter in that contains the number
20, and the returned record contains 20 randomly and distinct selected
records.

Anyone know how this can be done? (never came across randomly select records) Appreciate any help  

View 6 Replies View Related

Randomly Selecting From A Table.

May 3, 2004

lets say that this select statement will give me 100 records..

"Select UserID from Users where location = 'FL'"

from these 100 records, I want to randomly get 5 records. whats the best way to go about doing this

View 1 Replies View Related

Randomly Pick Records

Apr 10, 2001

I need to randomly pick one or more records from a query e.g

select c_id, c_name
from c_table
where cat_id = 52

There may be more than one records for cat_id = 52. and I need to pick 3 of them randomly.

Thanks in advance!

View 3 Replies View Related

Choosing Randomly A Record

Jun 8, 2002

Hello,
I have a table with some data in it.
What I want to do is to create a query that returns me randomly
one of the records of the table. Can this be done?

If this is not possible from SQL server I have thought an
alternative way. This is:

I want to return all rows of the table with SELECT *,
but I want the select to return in the first column an
autoincreament number for each row without the need to add
an autoincrement field in the table. e.g

Table
------
Banana
Tomatoe
Aple
...
...
Orange

Result from select
------------------
1 Banana
2 Tomatoe
3 Aple
. ....
. ....
23 Orange

Can this be done?
At least this way
1) I can travel to the end of the results (from ASP),
2) read the ID of the last row
3) Create a random integer number from 1 to last ID,
4) and finaly select the appropriate random row from that integer.


Can anybody help me please?

Thanks for any help in advance!

Yours, sincerely

Efthymios Kalyviotis
ekalyviotis@comerclub.gr

View 1 Replies View Related

How To Retrieve Data Randomly?

Jul 21, 2004

I have a table called Questions and I need to retrieve 10 rows of data randomly each time. If I use

SELECT TOP 10 Question FROM Questions

I'll get the same questions each time when I execute the sql statement. Is there a way to get the random data? Thanks.

View 1 Replies View Related







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