Database

Re: A very Good artical on Query optimization techniques SQL server database

6-Oct-10 3:41

I recently got this reference: http://use-the-index-luke.com/preface[^]

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

ptr_Electron6-Oct-10 3:53

ptr_Electron

6-Oct-10 3:53

Thanks

Re: A very Good artical on Query optimization techniques SQL server database

Luc Pattyn6-Oct-10 3:55

"Invalid object name" SqlException

6-Oct-10 3:55

you're welcome.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

patzerFish5-Oct-10 16:02

patzerFish

5-Oct-10 16:02

Here's what I'm trying to do, using C#:

1 - Connect to SQL Server
2 - Check if a database is present.
3 - Create the database if it isn't.
4 - Create a login/user pair for that database.
5 - Store the connection string for future use.

I have this done, confirmed by using SQL Server Management Studio Express.

The following method throws an SqlException:

private static SqlDataReader executeQuery(String query) {
    SqlCommand command = new SqlCommand(query, Connection);
    SqlDataReader reader;
    try {
        openConnection();
        reader = command.ExecuteReader();
        return reader;
    } catch {
        throw;
    } finally {
        //connection left open because the reader is worked on elsewhere.
    }
}

The query: SELECT ReportGroupName FROM dbo.ReportGroups
The error message: Invalid object name 'dbo.ReportGroups'.
The offending line of code: reader = command.ExecuteReader();

Some lines from the script I used to create the database, tables and user:

CREATE LOGIN [bim] WITH PASSWORD=N'*******', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginame = N'bim', @rolename = N'dbcreator'
CREATE USER [bim] FOR LOGIN [bim] WITH DEFAULT_SCHEMA=[dbo]
CREATE TABLE [dbo].[ReportGroups]

I have a feeling that the problem lies with how I setup the default user, since I'm still learning databases, and it's been pretty hit or miss. However, the application worked previously using the above method with the same query string - the exception has only occurred recently, after creating the database from a script rather than in using the visual editors in SQL Server Management Studio. The script I used to create the database was derived from exports of the original database and tables, with only modifications made to create a login and a user.

Any assistance on tracking down how to fix this error would be greatly appreciated.

Re: "Invalid object name" SqlException

patzerFish5-Oct-10 16:27

patzerFish

5-Oct-10 16:27

Hrm.

It appears that I wasn't setting InitialCatalog when building the connection string, at least not in the right place. You don't have to have an initial catalog when testing a connection to a database, which is code near what I'm doing, but you do have to have it to access database tables.

I was worried that it was something simple - turns out it was. Upon reflection, I'm kind of glad.

Grouped ordered precise select statement?

Emmet_Brown5-Oct-10 2:45

5-Oct-10 2:45

Hello everyone
I have a table named "volleyballpool8"
it has columns as ID, Pool, TeamNo, Team and Point
There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D

Now

I need to make a SELECT statement to form a table, which:

1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's
2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's
3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's
4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's

Therefore, in the end, I'll have a 4 rowed table

I'm somewhat new to SQL and I got confused...

Can anybody help?

D'Oh! | :doh:

btw: all of the fields are strings

Emmet_Brown5-Oct-10 3:24

5-Oct-10 3:24

I would appreciate any answer that makes the full query above but with some brute coding, I can handle myself in .cs part of the project if you only tell me how to get a "SECOND HIGHEST" from a table

for the highest of A's, I'm using

SELECT ID as row, Team, Point FROM cadet_games.volleyballpool8 WHERE Pool = 'A' and Point=(select max(point) from cadet_games.volleyballpool8)

If I don't get answer soon enough, I'll have to use multiple dataTables to concat later. But still I need some "second highest" query Frown | :(

Luc Pattyn5-Oct-10 3:30

5-Oct-10 3:30

Hi,

question: what is the second highest in this collection: 4, 4, 2, 1? is it 4 or 2?

Anyway, you should look here[^].

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Emmet_Brown5-Oct-10 3:32

5-Oct-10 3:32

well, there are lots of fields in this table and some of them are "for, against"...etc I'm adding those to my query as well, now, just think that Point column is unique Big Grin | :-D

btw, thanks for the answer, I'll have a look at it

Emmet_Brown5-Oct-10 3:34

5-Oct-10 3:34

Hey!

be sure that I've done that before I ask to you guys =)

I'm googling and querying at the same time Smile | :)

Emmet_Brown5-Oct-10 3:37

5-Oct-10 3:37

Couldn't find a single query to do all above, but decided to do it C# style

thanks all anyway Smile | :)

Luc Pattyn5-Oct-10 3:42

5-Oct-10 3:42

why is that? the first answer in the first Google hit shows a principle that should work well (assuming you want the second highest distinct value): have a subquery determining the maximum value, then query for the largest one that is less than the maximum.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Mycroft Holmes5-Oct-10 13:06

Mycroft Holmes

5-Oct-10 13:06

Impatient bugger aren't you.

Look into row_number and partition over in BOL.

With these 2 commands you can select the results grouped by team and ordered by points with a ranking per team. With that sub query you simply select the top 4 record to give you the results.

I can think of 2 other methods of achieving this in TSQL bit the above key word should give you the pointers for research.

Never underestimate the power of human stupidity
RAH

Using OPENXML in stored Procedure

Blue_Boy5-Oct-10 21:21

Blue_Boy

5-Oct-10 21:21

As Luc Pattyn said get top 1 value which is lower then max value,
Look this example and hope you will figure out with your case:

SELECT MAX(vlera) HighestValue,<br />
(SELECT TOP 1 m.vlera FROM mytable m WHERE m.vlera < (SELECT MAX(vlera) FROM mytable)  ORDER BY vlera DESC) secondHihgestValue<br />
 FROM mytable

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

meeram3955-Oct-10 1:26

meeram395

5-Oct-10 1:26

I have a set of records which I want to insert to the table at one stretch. For this, I am using OpenXML keyword in Sql Server 2005.

Following is my stored procedure:

ALTER procedure [dbo].[usp_InsertManyRows]

@XMLDOC xml

AS
declare @xml_hndl int 

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

Insert Into outputfiles
            (

            Processid

            )

Select 

            Processid

From
            OPENXML(@xml_hndl, '/Process/ProcessId')
            With
                        (

                        Processid int '/Process/ProcessId'

                        )

The table 'OuputFiles' contains column ProcessId, OutputFileName and ContributionFileName columns. When I tried to test the sp as below, it was inserting the same record multiple times. I am not sure why it is:

exec usp_InsertManyRows '<Process><ProcessId>8712</ProcessId><ProcessId>14444</ProcessId></Process>'

It is only inserting 8712 two times, not at all 14444.

I am using this for the first time. Please help me how to resolve this, as well as inserting the values for multiple columns like OutputFileName and ContributionFileName columns.

Thanks in advance

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Re: Using OPENXML in stored Procedure

Goutam Patra5-Oct-10 2:38

Goutam Patra

5-Oct-10 2:38

You can try like the following

For SQL

Select 
 Processid
From
 OPENXML(@xml_hndl, 'root/Process')
 With
  (
    ProcessId int

   )

Use XML as

'<root><Process ProcessId="8712"></Process> <Process ProcessId="1424"></Process></root>'

Look http://msdn.microsoft.com/en-us/library/aa276847%28SQL.80%29.aspx[^]

Number of Records [modified]

T.RATHA KRISHNAN5-Oct-10 1:15

T.RATHA KRISHNAN

5-Oct-10 1:15

Hi!
How to get the total records in a table? The following query returns an irrelevant number like 39685832(not the same all the time). I'm using SQLite. How to get the no.of records from a SQLite table?

"SELECT COUNT(*) FROM current"

modified on Tuesday, October 5, 2010 7:23 AM

NeverHeardOfMe5-Oct-10 1:26

NeverHeardOfMe

5-Oct-10 1:26

Your table does have a primary key field, or unique index at least, doesn't it? Try counting on that, eg

SELECT COUNT(ID) FROM tablename

Is "current" really your table name? I am not sure about suing that... it is a reserved word[^] - at least try enclosing it in delimters if you must use that name. Otherwise, change it.

T.RATHA KRISHNAN5-Oct-10 1:48

T.RATHA KRISHNAN

5-Oct-10 1:48

Hi!
This query doesn't work. Actually I'm passing SQLite query from C++. When I run my C++ program, it crashes and the console displays "SQLError: No such column ID". I've tested this with another table(profile instead of current)also. How to get the number of rows in a table?

NeverHeardOfMe5-Oct-10 1:52

NeverHeardOfMe

5-Oct-10 1:52

Well use the name of a column you do have then, if ytou haven't got an ID field. D'Oh! | :doh: