Database Discussion Boards

Luis Alonso Ramos11-Jan-06 15:00

11-Jan-06 15:00

It suddenly started working on my SQL Server (development) as fast as exepected, but it now is slow again.

I installed the update on the production server (it's for a small business, and they have been told that this particular report will be slow for a while) and it's also very very slow. Running the SP with OSQL is also slow, but not as much as from the application.

Does anyone have any idea of what's going on? I'm going crazy! Sigh | :sigh:

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

-- modified at 21:00 Wednesday 11th January, 2006

Re: Update - still not working

nguyenvhn11-Jan-06 21:25

nguyenvhn

11-Jan-06 21:25

I am so crazy too when discover your problem. Could you send me the database file? (Compress and attach to email or by yousendit).

I try to create the database but I can not insert so much data.

My email address: happyrainy@gmail.com

Re: Update - still not working

Luis Alonso Ramos12-Jan-06 6:48

Luis Alonso Ramos

12-Jan-06 6:48

Thanks, but late yesterday we found a solution. Basically we changed the code from declaring a table variable to creating a temporary table, and now it's always as "fast" as it should be:

CREATE TABLE #Abonos (ID_semana int, ID_cliente int, Abono int)
INSERT INTO #Abonos
    SELECT R.ID_semana, R.ID_cliente, MIN(R.ID_abono)
      FROM Recibos R
      WHERE R.Estatus = 1 AND R.ID_semana >= @ID_semana1 AND R.ID_semana <= @ID_semana2
      GROUP BY R.ID_semana, R.ID_cliente

CREATE TABLE #AbonosPorDia (ID_semana int, DiaPago int, Pagados int)
INSERT INTO #AbonosPorDia
    SELECT OS.ID_semana, Recibos.DiaPago, COUNT(Recibos.ID_recibo) AS Pagados
      FROM Operacion_Semanas OS
      INNER JOIN Recibos ON OS.ID_semana = Recibos.ID_semana
      INNER JOIN #Abonos A ON Recibos.ID_abono = A.Abono
      WHERE OS.ID_semana >= @ID_semana1 AND OS.ID_semana <= @ID_semana2
      GROUP BY OS.ID_semana, Recibos.DiaPago
      ORDER BY OS.ID_semana

Thanks anyway for trying to help, I really appreciate it!

-- LuisR

nguyenvhn wrote:
My email address: happy#rainy@gmail#com

P.S. I don't recommend posting your e-mail address like this. CodeProject is commonly crawled by spiders looking for e-mail addresses for spam lists. Modify your post to remove it, or at least disguise it:

happyrainy at gmail dot com
happyrainy.gmail@com (swap @ and .)

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

I cannot update the Data grid fields for the following example:

kenn_rosie10-Jan-06 6:44

kenn_rosie

10-Jan-06 6:44

I cannot update the Data grid fields for the following example:
The grid and 2 buttons are visable but I cannot mainpulate (update) the data

Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()
Me.cnPubs = New System.Data.SqlClient.SqlConnection
Me.daAuthors = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
Me.dsPubs = New ADO.dsPubs
CType(Me.dsPubs, System.ComponentModel.ISupportInitialize).BeginInit()
'
'cnPubs
'
Me.cnPubs.ConnectionString = "workstation id=DOZER;packet size=4096;integrated security=SSPI;data source=dozer;" & _
"persist security info=False;initial catalog=pubs"
'
'daAuthors
'
Me.daAuthors.DeleteCommand = Me.SqlDeleteCommand1
Me.daAuthors.InsertCommand = Me.SqlInsertCommand1
Me.daAuthors.SelectCommand = Me.SqlSelectCommand1
Me.daAuthors.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "authors", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("au_id", "au_id"), New System.Data.Common.DataColumnMapping("au_lname", "au_lname"), New System.Data.Common.DataColumnMapping("au_fname", "au_fname")})})
Me.daAuthors.UpdateCommand = Me.SqlUpdateCommand1
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname FROM authors"
Me.SqlSelectCommand1.Connection = Me.cnPubs
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname) VALUES (@au_id, @au_lname, @au_fna" & _
"me); SELECT au_id, au_lname, au_fname FROM authors WHERE (au_id = @au_id)"
Me.SqlInsertCommand1.Connection = Me.cnPubs
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "UPDATE authors SET au_id = @au_id, au_lname = @au_lname, au_fname = @au_fname WHE" & _
"RE (au_id = @Original_au_id) AND (au_fname = @Original_au_fname) AND (au_lname =" & _
" @Original_au_lname); SELECT au_id, au_lname, au_fname FROM authors WHERE (au_id" & _
" = @au_id)"
Me.SqlUpdateCommand1.Connection = Me.cnPubs
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "au_id", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "au_fname", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "au_lname", System.Data.DataRowVersion.Original, Nothing))
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "DELETE FROM authors WHERE (au_id = @Original_au_id) AND (au_fname = @Original_au_" & _
"fname) AND (au_lname = @Original_au_lname)"
Me.SqlDeleteCommand1.Connection = Me.cnPubs
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "au_id", System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "au_fname", System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "au_lname", System.Data.DataRowVersion.Original, Nothing))
'
'dsPubs
'
Me.dsPubs.DataSetName = "dsPubs"
Me.dsPubs.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.dsPubs, System.ComponentModel.ISupportInitialize).EndInit()

End Sub
Protected WithEvents cnPubs As System.Data.SqlClient.SqlConnection
Protected WithEvents daAuthors As System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents dsPubs As ADO.dsPubs
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents btnRefresh As System.Web.UI.WebControls.Button
Protected WithEvents btnSave As System.Web.UI.WebControls.Button

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

dsPubs.Clear()
daAuthors.Fill(dsPubs)
DataGrid1.DataBind()

End Sub

Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
daAuthors.Fill(dsPubs.authors)

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
daAuthors.Update(dsPubs.authors)

End Sub
End Class

ODBC - Getting table names ?

Weckmann10-Jan-06 1:50

Weckmann

10-Jan-06 1:50

Hello all,

I want to get access to some databases which are registered in Windows with ODBC.

I already managed to get the registered database names, files, servers... everything.

What I am now missing is how I could get the table names of those databases??

I can open an OdbcConnection to any of those databases, but I cannot read anything out of them because I dont know the table names which are in those databases.

So, how can I programmatically get the table names of a database where I already have an OdbcConnection successfully established and open?

Thanks for any help.

Best regards,
Bertram Weckmann

24hr Time Format

tadhg889-Jan-06 23:56

tadhg88

9-Jan-06 23:56

Hi I am having a bit of trouble trying to display my times in 24hr format, at the moment it is displaying the times with am and pm im just wondering if anyone has any ideas of how to do this
Tim

select ID, SUBSTRING(CAST(Date as nVarChar), 13,20) as Date from Session where (SquadId = 5) and CONVERT(smalldatetime, CONVERT(varchar(8), Date, 112)) = '01/10/2006'

Re: 24hr Time Format

Colin Angus Mackay10-Jan-06 0:19

Colin Angus Mackay

10-Jan-06 0:19

Use CONVERT[^] to convert the date into just a time. For example: CONVERT(varchar(5), Date, 108)

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: 24hr Time Format

tadhg8810-Jan-06 1:15

tadhg88

10-Jan-06 1:15

yes indeed thats exactly what i needed thanks alot man i appreciate it

Help for sql query(joins)

rs_net9-Jan-06 18:20

rs_net

9-Jan-06 18:20

hi all,
this is my query.
I have 3tables(a,b,c).
i am connecting table a with table b using columns
pinnumber(in a table) with Usernumber(in table b).
if this query is correct then only i have to compare
third table column (menuid)with second table column(accessid)

pls help me out of this problem.

Regards
sharmi

rs_net

Re: Help for sql query(joins)

Colin Angus Mackay9-Jan-06 20:52

Colin Angus Mackay

9-Jan-06 20:52

Your description isn't very easy to understand. What are the primary keys of each table? What are their foreign key counterparts in other tables?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Why the ODP.NET could not be installed?

rushing9-Jan-06 15:25

rushing

9-Jan-06 15:25

When I run the setup.exe in Windows 2000 sp4, there is a dialog which said 'Could not find the main class.Program will exit.'
Then caption of the dialog is 'Java Virtural Machine Laucher'.
Then the program exit.
I tried many computer, some of them is good , and some of them like this.
The OS is Windows 2000 sp4, database is Oracle9i, .net framework is 2.0 beta 2.
Why?
Thanks!

SQL Server DataAdapter

thebison9-Jan-06 10:02

thebison

9-Jan-06 10:02

Hi,

I'm hoping someone can help me with this (hopefully very simple) problem I am experiencing as a newbie to SQL Server and ASP.NET. I have created a database in SQL Server 2000, and am attempting to show the records from one of the tables in a Visual C# program using Visual Studio.NET 2002.

I am following an online tutorial and have successfully added an SqlConnection in Visual Studio, and can see all relevant tables and columns in my Server Explorer. When I then try to add a SqlDataAdapter to my form, the wizard takes me through the configuration step by step. The final step is to build a query which I have completed successfully (a simple SELECT which I have tested previously using Query Analyser), until I click 'Next' and get the following error message:

Generated SELECT Statement.
Exception from HRESULT: 0x800700C1.

I'm sure there is something simple that I am doing wrong here, as it seems such a straightforward thing to do. Any advice would be much appreciated!

Many Thanks!

Running SQL server 2000 & 2005 side by side

Tiger4569-Jan-06 1:00

Tiger456

9-Jan-06 1:00

Hai

My system is already installed with SQL server 2000 . Is it possible for me to install SQL server 2005 BETA CTP in the same machine and run both side by side.

Regards

Re: Running SQL server 2000 & 2005 side by side

Colin Angus Mackay9-Jan-06 1:12

Colin Angus Mackay

9-Jan-06 1:12

Yes, but I wouldn't recommend using the beta any more. The beta period is over why don't you use the final version?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Running SQL server 2000 & 2005 side by side

Tiger4569-Jan-06 1:46

Tiger456

9-Jan-06 1:46

Thanks for ur reply.

i installed 2005. The Server Mangaement Studio still connect to version 8.0. SQL server express instance is not running . how can i manually run the instance or please suggest any other alernative.

In 2000 we had service manager, is anything similar in 2005

Regards

Re: Running SQL server 2000 & 2005 side by side

Colin Angus Mackay9-Jan-06 20:57

Colin Angus Mackay

9-Jan-06 20:57

anig1234 wrote:
how can i manually run the instance

Use the SQL Server Configuation manager.

anig1234 wrote:
In 2000 we had service manager, is anything similar in 2005

The SQL Server Configuration manager is similar, but much expanded as you can do a lot more than jist start and stop services.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Running SQL server 2000 & 2005 side by side

Tiger45610-Jan-06 17:24

Tiger456

10-Jan-06 17:24

Thanks For the reply.

Woruld you please tell me the steps to follow and make Server management studio use SQL server version 9.0

Is there any way to Switch between version 8.0 and 9.0 in SMS.

Regards

Re: Running SQL server 2000 & 2005 side by side

Colin Angus Mackay10-Jan-06 20:41

Colin Angus Mackay

10-Jan-06 20:41

anig1234 wrote:
Woruld you please tell me the steps to follow and make Server management studio use SQL server version 9.0

It is installed with SQL Server 2005 (version 9.0 as you put it) and will automatically pick up on any SQL Servers on the local machine - At least mine did - you should not have to do any additional work.

anig1234 wrote:
Is there any way to Switch between version 8.0 and 9.0 in SMS.

You can have multiple SQL Servers running on a physical machine simultaneously. There is no need to "switch" between them.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Running SQL server 2000 & 2005 side by side

dl4gbe12-Jan-06 2:48

dl4gbe

12-Jan-06 2:48

Hallo,

Most of the utilities won't work with the beta. At least I never got them running on my server. The latest version is ok. No problem with the server management studio.

I run sql server 2000 (msde) and sql server 2005 (express) since half a year already side by side. My only problem was. I could only use osql. Everything else was not working.

Now I again installed side by side on another server using the latest express version. Everything is working with little problems.

Sincerely

Chris

Remote SQL Database

HakunaMatada9-Jan-06 0:26

HakunaMatada

9-Jan-06 0:26

I have a small query regarding SQL server 2000. I want to create a database whose mdf and ldf files will reside on a different computer. Now this computer is a Linux Server. My criteria is that, the mdf and ldf files will reside on a Linux server and I should be able to access those files from the SQL server installed on my system. Is this possible? If so, how do I go about doing it?

Thanks...

---
With best regards,
A Manchester United Fan

The Genius of a true fool is that he can mess up a foolproof plan!

Re: Remote SQL Database

Colin Angus Mackay9-Jan-06 1:14

Colin Angus Mackay

9-Jan-06 1:14

Save My Soul - (SMS) wrote:
My criteria is that, the mdf and ldf files will reside on a Linux server and I should be able to access those files from the SQL server installed on my system. Is this possible?

No. The files used by SQL Server must reside on the same physical machine that the SQL Server service is running on.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

sql: count product's item, how about the zero item?

zrocb818-Jan-06 12:27

zrocb81

8-Jan-06 12:27

Hi, everyone. Need help here. Let's straight to question:

I have 2 tables:
tb1 -- productID, productDescription
tb2 -- productID, itemID, itemDescription......blabla

In my tb1:
productID | productDescription
-------------------------------
HAD | Hardware
TOY | Toy
CAR | Car
STA | Stationary

and in my tb 2:
productID | itemID | itemDescription ......blabla
-------------------------------------------------
HAD | mou1 | mouse1
HAD | mou2 | mouse2
HAD | mou3 | mouse3
...
..
TOY | scar1 | small car1
TOY | scar2 | small car2
...
..
.

Assume that
there got 7 items under TOY, 18 under HAD, 18 under CAR,
and ___No Item Under STA___ <--- I have problem with this. keep reading.
--------------------------------------------------------------

i'm using MSSQL,
this is the sql command i worked so far:

select tb1.productID, count (tb2.itemID) as CountedProducts
from tb1, tb2 where
tb1.productID=tb2.productID
group by tb1.productID

/*result*/
TOY 7
HAD 18
CAR 18
/********************/

but the expected result i want is suppose to be like this
STA 0
TOY 7
HAD 18
CAR 18

anyone please help. thx.

Re: sql: count product's item, how about the zero item?

nguyenvhn8-Jan-06 17:08

nguyenvhn

8-Jan-06 17:08

You have used INNER JOIN query. Please use LEFT OUTER JOIN

Re: sql: count product's item, how about the zero item?

zrocb818-Jan-06 17:34

zrocb81

8-Jan-06 17:34

hi nguyenvhn
thx a lot of very many uncounted muchesss.

ref: article about the LEFT OUTER JOIN
http://www.devx.com/dbzone/Article/17403/0/page/4

hopefully this help those who has similar case as mine.

How can i shutdown database in oracle 10g

Ta_Tee4737-Jan-06 17:26

Ta_Tee473

7-Jan-06 17:26

i don't know username/password that connection as SYSDBA
how can i do. please somebody let me know

thank a lot

Best Regrad
Tatee

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.