Database

Re: Improve the efficiency of Stored Procedures

3-Jul-06 21:53

Uma Kameswari wrote:
Are there any techniques /rules/measures that should be followed to develop stored procedures so that the efficieny of the appliaiton increases.

There is no single catch all answer. It depends on the scenarios that they are being used in.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Frank Kerrigan4-Jul-06 0:14

Re: Improve the efficiency of Stored Procedures

4-Jul-06 0:14

* Don't use cursors (there cursed)
* Set NoCount off
* have efficient SQL SELECT statements
* Sometimes using temp tables is quicker than complex joins from many tables

Look where you want to go not where you don't want to crash.

Bikers Bible

VK-Cadec11-Jul-06 8:56

VK-Cadec

11-Jul-06 8:56

You could use the "show plan" in SQL200 or "display estimated execution plan" in SQL2005 to see exactly how costly each process is and maybe modify it accordingly.

Thanks.

Delete or update

leckey3-Jul-06 3:38

leckey

3-Jul-06 3:38

I have an ASP datagrid in which the user can look up an existing record and update the fields, or insert a new record. So I have a couple of questions...

1. Is it considered better to delete the old record and insert the new (updated) record? I am rewriting this application and the old programmer did it this way which seemed odd.

2. If it is better to update, how do I program in SQL
if partNumber already exists, update
if PartNumber does not exist, insert

Thanks!

Re: Delete or update

Paul Conrad3-Jul-06 7:25

Paul Conrad

3-Jul-06 7:25

leckey wrote:
2. If it is better to update, how do I program in SQL
if partNumber already exists, update
if PartNumber does not exist, insert

You could use a stored procedure to do this.

Re: Delete or update

Frank Kerrigan3-Jul-06 22:14

Huge DataBases [modified]

3-Jul-06 22:14

something like this. 0 is default value for PartNumber and if it does not exist than it will be created and a new part number will be returned,

create proc myProc
@PartNumber int = 0,
@PartDesc varchar
AS

Declare @partNo_Used

if @PartNumber = 0
Begin
-- Insert into Table
insert parts ( partdesc ) Values ( @PartDesc)
--- Get identity or part number
Set @partNo_Used = @@Indentity
End
ELSE
Begin
-- Update Record
update parts
SET partdesc = @PartDesc
Where Partnumber = @PartNumber

@partNo_Used = @PartNumber
End

-- Return Part and Desc
SELECT @partNo_Used , @PartDesc

GO

---------------------------------------------------------------------------
Look where you want to go not where you don't want to crash.

Bikers Bible

hamidreza_buddy2-Jul-06 23:44

hamidreza_buddy

2-Jul-06 23:44

Hi I wanted to know how should i deal with huge databases.
i mean is there any special technique ?
or i can do processes things with those simple command and queries?
if there is, please give me resource url and Book names.
(it is a Database project for a Store and I've been told it has a giant DataBase and your application should be fast and ...)
thanks

-- modified at 5:50 Monday 3rd July, 2006

Re: Huge DataBases

Colin Angus Mackay3-Jul-06 1:52

3-Jul-06 1:52

hamidreza_buddy wrote:
I wanted to know how should i deal with huge databases.

There is no correct answer. You just have to be more aware of the scenarios within which it will be used and optimise it for those scenarios.

Re: Huge DataBases

Frank Kerrigan4-Jul-06 1:26

SQL SERVER 2005 - "error 193 installation net framework 2.0"

4-Jul-06 1:26

How huge is huge. I've tun dbs upto 10Gb with no real problems. If you want realtime analysis then you have to get inventive with your data design, indexes etc..

you can split tables across databases or even tune your disks and datafiles as well i.e have data and indexes on seperate raid arrays or even have different tables on different drives etc...

This all depends wether its transactions, logging or reporting etc... As Colin said its all realative to your application.

Look where you want to go not where you don't want to crash.

Bikers Bible

hasantayyar2-Jul-06 1:10

hasantayyar

2-Jul-06 1:10

hi everybody,
too many boys now angry with this problem.. and too many versions of this problem

this is mine:
i have no virtual machine and no servers (duing the installaion)
1. i have .net framework 2.0 and i have Ms visual studio 2005 .
2. i tried to install Ms SQL server 2005 Developer edition.
in first step [Server components, tools, Books Online, and samples] the installation sayas that net FRM 2.0 and support files required.
3. i click the install button and wow!! "error 193 installation Net Framework 2.0."

i cant understand, i have net framework 2.0 and it cant install it. :S

i uninstalled all visual studio 2005 and net framework 2.0 and all other sql services.

and the same problem appears again.

Note: i run the installation program from full DVD [includes x86 x64 and itanium]

and no servers are running while installations.
i closed antivirus and firewall programmes while installation.
but the same error - that damned "error 193"

and please help me, and feel good to help a good boy Smile | :)

Read RTF and Word file from BLOB

excession1-Jul-06 6:04

excession

1-Jul-06 6:04

Hi
I'm trying to read RTF and Word files from blob columns in SQL Server, but the files can't be opened in Word when i retreive them. What am I doing wrong?

<br />
FileStream fileStream = new FileStream(filename, FileMode.Create, FileAccess.Write);<br />
BinaryWriter binaryWriter = new BinaryWriter(fileStream);<br />
<br />
long totalBytes = sqlDataReader.GetBytes(blobColumn, 0, null, 0, int.MaxValue);<br />
<br />
byte[] outbyte = new byte[totalBytes];<br />
sqlDataReader.GetBytes(blobColumn, 0, outbyte, 0, (int)totalBytes); <br />
binaryWriter.Write(outbyte, 0, (int)totalBytes);<br />
binaryWriter.Flush();<br />
<br />
binaryWriter.Close();<br />
fileStream.Close();<br />

Re: Read RTF and Word file from BLOB

Paul Conrad4-Jul-06 14:10

Paul Conrad

4-Jul-06 14:10

Code looks okay.

Access database problem.

Stanciu Vlad1-Jul-06 2:57

Stanciu Vlad

1-Jul-06 2:57

I have a small access database at whitch I connect through OleDb. Nothing going bad so far.

I use C# for the client, so I connect using OleDBConnections, and querry the database through OleDbDataAdapters.
The problem apears at date/time fields in the database: using the data adapter the fields are revealed to the client as DateTime objects, but at update or insert commands I get an error message informing me that the date argument is mismatched.
After searching the net I found that date/time objects in Access should be passed into update/insert commands like strings between # chars. If I do like this the whole thing is ok, but is there any way that I can make the data adapter work with access?

Thank you.

protected internal static readonly ... and I wish the list could continue ...

Re: Access database problem.

Stanciu Vlad1-Jul-06 3:15

Stanciu Vlad

1-Jul-06 3:15

I have found what the problem was.

In my access database I had a field with a reserved name, and the command builder didn't sourounded it between []. So afther changing the field's name all things went ok.

protected internal static readonly ... and I wish the list could continue ...

moving database in sql (question again)

shaz jazz1-Jul-06 2:22

shaz jazz

1-Jul-06 2:22

in reply of moving database in sql Colin Angus Mackay once wrote :
Two ways I can think of.

1. Detach the database move the files and then attach at the other end. (You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)

2. Create a backup of the database, move the backup file to the new system, then restore the backup (you'll again have to wire up the logins to accounts for the same reasons given above).

now i'm havg same problem(i'm using sql server 2005 express edition), i hav tried both ways but at second server when i click database diagrams then it gives masseage that no valid owner of this database exits so can't work with database diagrams, in database properties i tried to enter different owners but didn't worked so what should i do??
further more what does this mean "(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)" i just cant get it?

May God help u solv ur problems as well,thx in advance!!

Colin Angus Mackay1-Jul-06 9:44

1-Jul-06 9:44

shaz jazz wrote:
further more what does this mean "(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)" i just cant get it?

I answered this question already here[^] If there is something you don't understand from that answer then what is it?

shaz jazz2-Jul-06 22:17

shaz jazz

2-Jul-06 22:17

what about this problem??? D'Oh! | :doh:

"now i'm havg same problem(i'm using sql server 2005 express edition), i hav tried both ways but at second server when i click database diagrams then it gives masseage that no valid owner of this database exits so can't work with database diagrams, in database properties i tried to enter different owners but didn't worked so what should i do??
further more what does this mean "(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)" i just cant get it?"

May God help u solv ur problems as well,thx in advance!!

Colin Angus Mackay2-Jul-06 22:59

2-Jul-06 22:59

Repeating yourself won't help because from my point of view I've answered your question. I showed you how to "wire up the logins again" which would appear to be the source of your other problem. I assume you have sorted out the users on the moved database - What is your problem now?

shaz jazz3-Jul-06 0:29

shaz jazz

3-Jul-06 0:29

is owner of database and login same??
i made a database in lab and i want 2 work on same db 8 home(ofcourse the servers r differnt!), now when i attached the db(8 home) using object explorer then it normally attaches but "Database Diagrams" are not shown and cannot be worked with because it gives error that there is no owner of this database.
now i went to properties of the database then files and here i entered the same owner which is the default owner of database created at this server(8 home), but it doesn't accepts it and gives error of unregistered owner!!i tried other owner option that comes with browse button on that screen bt it didn't worked, so what should i do??

May God help u solv ur problems as well,thx in advance!!

PlayByTheRules3-Jul-06 5:17

PlayByTheRules

3-Jul-06 5:17

shaz jazz wrote:
2

You mean "to", "too" or "two"?

shaz jazz wrote:
8

Are you really that lazy that you cannot type "at"? (It is the only word that fits, but it took me quite a few seconds to figure that out - Do you really expect a prompt reply when it takes people an unnecessary amount time to figure out what you are writing?!

shaz jazz wrote:
r

"are" - You know how difficult it is to read this crap?!

shaz jazz wrote:
u

Another weird abbreviation - Or are you now speaking Dutch?

shaz jazz wrote:
ur

"your" or "you're"?? C'mon if you want to be taken seriously don't write this SMS style crap.

shaz jazz wrote:
thx

THX is a quality standard for sound repoduction in cinemas and home cinema surround sound systems.

Colin Angus Mackay3-Jul-06 5:32

3-Jul-06 5:32

shaz jazz wrote:
is owner of database and login same??

I don't know - that depends on your database.

If the user in the database has db_owner rights then the user will be the owner of the database, and that user must be associated to a login.

shaz jazz wrote:
there is no owner of this database

Find out which user or users are marked as the database owners (they have db_owner rights on the database, or sysadmin rights over the whole server) and apply them on your database at home.

shaz jazz wrote:
but it doesn't accepts it and gives error of unregistered owner

I've never seen this error before, So I'm guessing that you have to use the same user as the owner as where the database was created. You need to use ensure that user has a valid login and that user is granted the db_owner rights on your server at home. As it is a lab experiment, you might want to elevate that to sysadmin rights (which will give that user the right to do anything on the server, not just the database).

Colin Angus Mackay3-Jul-06 5:34

3-Jul-06 5:34

Wait a moment: Are you moving a SQL Server 2000 database onto a SQL Server 2005 system? If so then the diagrams won't work - It is a known limitation, SQL Server 2005 does not read SQL Server 2000 diagrams and the tools for SQL Server 2005 won't create diagrams on a SQL Server 2000 database.

Frank Kerrigan3-Jul-06 22:18