Sp in Ms-Sql 2000 - Database Discussion Boards

Re: Backup_Restore

Tobias Schoenig3-Feb-08 19:42

Tobias Schoenig

3-Feb-08 19:42

simworld wrote:
RESTORE DATABASE student_database
FROM XYZ
WITH DBO_ONLY,
NORECOVERY,
STATS

You restore the database with the 'NORECOVERY'-option - this means that the database remains offline and in recovery-mode (normally used within database-mirroring). Remove this option and the first error-message should disappear.

Re: Backup_Restore

simworld4-Feb-08 6:49

simworld

4-Feb-08 6:49

thank u very much

inner join on datetime timing out

mejner3-Feb-08 3:17

mejner

3-Feb-08 3:17

Hi all,

I have a big table with 30 million rows (and growing) which I need to join to a table containing dates. The result is a bar graph diagram.

But when I join with time over 12 hours the join times out

SELECT table1.time, table1.value
FROM table2 INNER JOIN table1 ON table1.time > table2.time_up AND table1.time < table2.time_down
ORDER BY table1.time

This should result in any thing from a couple of hundreds row to several thousands.

I'm doing this in a Stored Procedure. I've put indexes on table1.time.

Is this join simply to large for SQL Server 2005? Or how can I optimize this search?

Re: inner join on datetime timing out

Mycroft Holmes3-Feb-08 21:48

Mycroft Holmes

3-Feb-08 21:48

At a guess you have datetime fields with time extensions and need to join on the date only. You killer is the <> comparison.

If this is the case I suggest you convert both dates to a string with a date only format eg
on Convert(varchar(20),table1.time,103) = Convert(varchar(20),table2.time ,103)

If you are using the time as part of your join (ie between 1am and 3pm) then you have a problem and will need to work around it some other method (split the date and time fields, maybe change the time part to int for hours 0-23)

Never underestimate the power of human stupidity
RAH

Re: inner join on datetime timing out

mejner3-Feb-08 23:09

mejner

3-Feb-08 23:09

OK, thanks.

I'll try that out. Unfourtunally I need to join on hours as well. And in a perfect world on minutes as well.

Re: inner join on datetime timing out

andyharman3-Feb-08 23:00

andyharman

3-Feb-08 23:00

Did you use a clustered index for your "time" index on Table1? That would be considerably more efficient that using a non-clustered index.

Re: inner join on datetime timing out

mejner4-Feb-08 0:39

mejner

4-Feb-08 0:39

WOW! I've tried different indexes on the large table but I never got i right.

Now this baby:

SELECT Intervallvarden.storhet, Intervallvarden.id_intervallvarde, Intervallvarden.varde, Intervallvarden.tid, Intervallvarden.kanal_nr, MatpunktInstrument.instrumenttyp, MatpunktInstrument.instrumentnummer
FROM MatpunktInstrumentParameter
INNER JOIN MatpunktInstrument
ON MatpunktInstrumentParameter.id_matpunkt_instrument_parameter = MatpunktInstrument.id_matpunkt_instrument_parameter
INNER JOIN Intervallvarden
ON Intervallvarden.instrument_typ = MatpunktInstrument.instrumenttyp
AND Intervallvarden.instrument_nr = MatpunktInstrument.instrumentnummer
AND Intervallvarden.tid > MatpunktInstrumentParameter.tid_upp
WHERE (MatpunktInstrumentParameter.id_matpunkt = 2575)
AND (Intervallvarden.tid > '2007-10-01 00:00')
AND (Intervallvarden.tid < '2007-11-30 23:00')
ORDER BY intervallvarden.tid

Runs in two seconds! Table "Intervallvarden" being the 30 milion rows table.

Thanks allot!

Sp in Ms-Sql 2000

r_mohd1-Feb-08 19:59

r_mohd

1-Feb-08 19:59

hi all,

i have developed an application in vb.net with Ms-Sql as back end.
on one stage i am facing an proble while i creating an store procedure.
the coding is as under:-

CREATE PROCEDURE ProcedureName
(
@Sid bigint
)
as
declare @Cityid bigint
begin

Select splrid,splrname,splrundrgrup from finactsplrmstr where splrid=@Sid
Select adrsemail from finactadrsmstr where concrnid=@Sid

set @Cityid=(select adrsctyid from finactadrsmstr where concrnid=@Sid)
select * from finactCscmstr where cscid=@Cityid
end
GO

further When i called this procedure in vb.net it gives not proper output,
say i want to display a record using a sql command in a list view.
i want output like this:

Name: Under Group: Email Address: City: State: Country:

the result which received only first select statement i.e Name and Under Group.

kinldy help me.

thanks in advance:

rmshah
Developer

Re: Sp in Ms-Sql 2000

pmarfleet1-Feb-08 23:04

pmarfleet

1-Feb-08 23:04

I'm not sure what your problem is. Are you having difficulty accessing the multiple resultsets returned by the query?

If you are filling a DataSet, each resultset will form a separate DataTable in your DataSet.

If you are using a SqlDataReader, call NextResult to move between resultsets.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Sp in Ms-Sql 2000

r_mohd3-Feb-08 22:24

r_mohd

3-Feb-08 22:24

thanks
sorry for delay actualy my system was out of order, further i am sending exact code which i am using

Private Sub sel_rec()
Dim LstviewRcord As ListViewItem
Try
Actgrpcmd = New SqlCommand("FinAct_SplrMstr_Select_ByName", Conn)
Actgrpcmd.CommandType = CommandType.StoredProcedure
Actgrpcmd.Parameters.AddWithValue("@sid", LstviewName.Text)
Actrdr = Actgrpcmd.ExecuteReader
Actrdr.Read()
LstviewRcord = LstvewAct.Items.Add(Actrdr("SupplierId"))
LstviewRcord.SubItems.Add(Actrdr("Suppliername"))
LstviewRcord.SubItems.Add(Actrdr("Supplierundrgrup"))
LstviewRcord.SubItems.Add(Actrdr("csccityname"))
LstviewRcord.SubItems.Add(Actrdr("cscstatename"))
LstviewRcord.SubItems.Add(Actrdr("csccontry"))
Actrdr.NextResult()
LstviewRcord.SubItems.Add(Actrdr("adrsemail"))

Catch ex As Exception
MsgBox(ex.Message)
Finally

Actgrpcmd = Nothing
Actrdr.Close()
End Try
End sub

but i not able to fect all record, only first statement's result i can get.
help me

rmshah
Developer

Re: Sp in Ms-Sql 2000

pmarfleet3-Feb-08 23:44

pmarfleet

3-Feb-08 23:44

r_mohd wrote:
but i not able to fect all record, only first statement's result i can get.

Does your stored procedure return multiple records? If so, you need to set up a loop to traverse them.

For instance:

While Actrdr.Read()
  ' Do stuff
End While

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Sp in Ms-Sql 2000

r_mohd4-Feb-08 0:28

r_mohd

4-Feb-08 0:28

still not able to get the record.

here i am giving u more details

i have two tables say tbl_Supplier and tbl_City.

in table city i have stored info about Supplier with city id which comes from tbl_City.

In tbl_city i have stored City name, State name and Country name.

Now I want to display a record in a Listvew, which has following columns.

Supplier Name,Under Group, Email Address, City Name,State Name,Contry Name

i trying to get this using a SP in mssql

Coding of sp and Sql Command already sent u
pl help

rmshah
Developer

Re: Sp in Ms-Sql 2000

pmarfleet4-Feb-08 0:40

pmarfleet

4-Feb-08 0:40

Your stored procedure looks overcomplicated. You could achieve the same result with 1 SELECT statement if you employed JOINs between the tables.

Also, you should consider giving your tables/fields more meaningful names. Your current naming conventions are quite cryptic. It would be difficult for someone else to understand what they mean without having to consult your systems specification.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Sp in Ms-Sql 2000

r_mohd4-Feb-08 18:38

r_mohd

4-Feb-08 18:38

Thanks a lot for sugessting me Joins

yes i have done this with multi joins.

Thanks again Laugh | :laugh:

rmshah
Developer

Re: Sp in Ms-Sql 2000

Tim Carmichael4-Feb-08 5:44

Tim Carmichael

4-Feb-08 5:44

Assuming all you want is the last set of values, place 'SET NOCOUNT ON' at the top of your stored procedure.

Tim

Msql foreign keys

Veldrain1-Feb-08 19:29

Veldrain

1-Feb-08 19:29

I am using an mSQL database for a small VB project. When it comes to makeing foreign key, what types match? The normal int datatype claims it is not compatiable with UniqueIdentifier. Am I missing something or does this differ from access and regular SQL?

Re: Msql foreign keys

pmarfleet1-Feb-08 22:58

pmarfleet

1-Feb-08 22:58

uniqueidentifier in SQL Server is a GUID. If you want an integer field that automatically increments, create an int field and set its IDENTITY property to true.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Sending mail..

Dio221-Feb-08 8:54

Dio22

1-Feb-08 8:54

One of my many bosses came to me today and asked me to write a stored procedure that will email him if a table doesnt get updated every 15mins. The server is Microsoft Server 2003 with MSSQL Server 2000. I've read around 2000 ways to do this. Each as confusing as the next. I'm a college student with little experiance in the matter. Any help would be appreciated. Thanks Smile | :)

Re: Sending mail..

Krish - KP1-Feb-08 19:25

Krish - KP

1-Feb-08 19:25

Useing sp_OACreate procedure, create a CDO message object
This requires NO extra configurations at SQL Server

Regards
KP

Re: Sending mail..

Marek Grzenkowicz4-Feb-08 0:04

Marek Grzenkowicz

4-Feb-08 0:04

http://sql-server-performance.com/Community/forums/p/20506/114757.aspx[^]

Trigger for Replication database table...

Talsaniya Jayesh1-Feb-08 5:48

Talsaniya Jayesh

1-Feb-08 5:48

Hi

I have permission to a databae that is replicated.. i want to have a trigger on that database table.. is it possible? will the update/insert trigger will work.? my database is updated every 5 to 10 mins..
Please help me out..

Jes

How to Pass Parameter to a Scheduled Job

Neeraj Kr1-Feb-08 1:39

Neeraj Kr

1-Feb-08 1:39

Is there any method of passing parameter to a scheduled job of SQL Server?

-----Have A Nice Day-----

Re: How to Pass Parameter to a Scheduled Job

Michael Potter1-Feb-08 8:45

Michael Potter

1-Feb-08 8:45

Have the job reference a table. Set the column(s) in the table to whatever parameters you want.

What is Wrong with the Following Query

Vimalsoft(Pty) Ltd1-Feb-08 0:36

Vimalsoft(Pty) Ltd

1-Feb-08 0:36

hi all i have the Following Query

<br />
if Exists(select * from sde.Property_Backup p1<br />
 where substring(p1.func_key,1,5)='GEOSS' <br />
 aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key,<br />
								p2.Attrib_code<br />
								from sde.Property_Backup p2<br />
								where p2.func_key = 'PV000000'<br />
								And p1.lis_key = p2.lis_key<br />
								And P2.aCTIVE =1))<br />
begin<br />
<br />
Update sde.Property_Backup p3<br />
		set p3.Attrib_code = p12.Attrib_code<br />
		where p2.lis_key = p1.lis_key<br />
		and substring(p2.func_key,1,5)='GEOSS' <br />
       And P1.aCTIVE =1<br />
end<br />
<br />

And i get the Following Error when run it,

Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'p3'.

what is wrong

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: What is Wrong with the Following Query

Tobias Schoenig1-Feb-08 1:34

Tobias Schoenig

1-Feb-08 1:34

Vuyiswa wrote:
Update sde.Property_Backup p3
set p3.Attrib_code = p12.Attrib_code

Why are you including sde.Property_Backup? If p3 is a table, it would be right to write:
Update p3 set p3.Attrib_code = p12.Attrib_code.

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