Re: 2 SQL quesions: address book - Database Discussion Boards

David Mujica23-Dec-09 5:58

23-Dec-09 5:58

You may want to build into your design the flexibility of supporting multiple databases, like Oracle, MS-SQL, MySQL, etc. This way let your customer determine which database he/she will deploy your application. Do this by building a Data Access Layer that implements all of the interaction with the database in one location.

Check this article out
Implementing a Provider Independent Data Access Layer in .NET[^]

Re: Support Multiple Databases

cdpace23-Dec-09 10:54

cdpace

23-Dec-09 10:54

Yes this is really a good idea but i want to ask you one thing. This way you are "saying" that has knowledge in IT and knows how deploy a database and maintain it etc. What I would like to implement in my application is a database that is installed automatically in the system when the user installs the application so the user doesn't need to have any IT know how.
Do you understand what I am saying? and thank you for your post anyways I agree with it and its a really good idea

sqlite

jashimu23-Dec-09 3:34

jashimu

23-Dec-09 3:34

Hi All,
I am trying to do some query in my sqlite database. I wanted to get average of some number, but could not do it.
if you please can, write a sql statement that get average of some number.
"SELECT AVE(w)FROM x WHERE y = z ";
It should return the ave of all w.

thanks in advance

Re: sqlite

dxlee23-Dec-09 4:17

dxlee

23-Dec-09 4:17

AVE is wrong. You should use AVG.

Re: sqlite

jashimu23-Dec-09 4:34

jashimu

23-Dec-09 4:34

Hi dxlee, it is really a silly mistake. I meant to do avg not ave and I could not figure it out what was wrong.

thanks a lot.

Re: sqlite

dxlee23-Dec-09 5:38

dxlee

23-Dec-09 5:38

I am glad I could help. I like sqlite. My electronic document manager uses sqlite and it is much faster than my previous version (which used Microsoft Access).

2 SQL quesions: address book

Dmitry Khudorozhkov22-Dec-09 10:27

Dmitry Khudorozhkov

22-Dec-09 10:27

Hi everyone,

this question may seem dumb, so sorry beforehand - I'm just starting to learn SQL Smile | :)

I want users to be have the address book (set of name/e-mail pairs) in my web application. To do this, I've created a db with 2 tables:

/* User info: */

CREATE TABLE `users`
(
`id` integer (12) NOT NULL AUTO_INCREMENT,
`name` varchar (50) UNIQUE NOT NULL,
`email` varchar (100) NOT NULL,
`password` varchar (50) NOT NULL, /* md5 of password */
PRIMARY KEY (`id`)
)
CHARACTER SET utf8 COLLATE utf8_general_ci;

/* Table with address info: */

CREATE TABLE `addresses`
(
`name` varchar (50) NOT NULL,
`email` varchar (100) NOT NULL,
`userid` integer (12) NOT NULL /* id of user (from the 1st table) that stored this record */
)
CHARACTER SET utf8 COLLATE utf8_general_ci;

First question: is this a good db structure?

Second question: what SQL query (join?) I must perform for user with name "name" to retrieve all his address records?

Thank you in advance.

Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).

Re: 2 SQL quesions: address book

Mycroft Holmes22-Dec-09 12:10

Mycroft Holmes

22-Dec-09 12:10

Dmitry Khudorozhkov wrote:
First question: is this a good db structure?

No, you need a primary key on the address table AddressID INT Auto increment. You also need a foreign key between User and address. This means you need to put the userID (currently the ID field on the user table) on the address table.

Then to get the address for a user something like

Select *
From Users
 inner join Addresses on Addresses.UserID = User.ID
where Users.ID = @IDno

Never underestimate the power of human stupidity
RAH

Re: 2 SQL quesions: address book

Dmitry Khudorozhkov22-Dec-09 13:17

Dmitry Khudorozhkov

22-Dec-09 13:17

Thanks a lot Mycroft.

Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).

Query to Test for SA [modified]

mobius11100122-Dec-09 7:30

mobius111001

22-Dec-09 7:30

What is a good query to execute to see if the account you are logged in as has sysadmin rights?

modified on Tuesday, December 22, 2009 1:48 PM

Re: Query to Test for SA

Dr.Walt Fair, PE22-Dec-09 7:47

Dr.Walt Fair, PE

22-Dec-09 7:47

Are you talking about South Africa or South America?

CQ de W5ALT

Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software

Re: Query to Test for SA

mobius11100122-Dec-09 7:52

mobius111001

22-Dec-09 7:52

Modified original message.

Re: Query to Test for SA

Giorgi Dalakishvili22-Dec-09 7:53

Giorgi Dalakishvili

22-Dec-09 7:53

Try dropping the databases. If you succeed it means that you have more permission then you need.

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

Re: Query to Test for SA

Luc Pattyn22-Dec-09 8:23

Luc Pattyn

22-Dec-09 8:23

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Re: Query to Test for SA

mobius11100122-Dec-09 7:58

mobius111001

22-Dec-09 7:58

Found this handy function IS_SRVROLEMEMBER ( 'role' [ , 'login' ] ). It will return either or a 1 or 0.

ex. SELECT IS_SRVROLEMEMBER( 'sysadmin', 'user_name' )

Problem solved.

How to store IList<string> in sql server</string>

GauravKP22-Dec-09 5:25

GauravKP

22-Dec-09 5:25

Which is best datatype used in sqlserver2008...to store IList<string> C#.

Re: How to store IList in sql server

J4amieC22-Dec-09 5:34

J4amieC

22-Dec-09 5:34

A table!

Re: How to store IList in sql server

Jordon4Kraftd23-Dec-09 10:03

Jordon4Kraftd

23-Dec-09 10:03

You could try xml also (sql 2005 and later i believe), its serializable and shouldn't be to difficult, but a table is the best way like the first response.

SQL Server Connect Test

mobius11100122-Dec-09 4:43

mobius111001

22-Dec-09 4:43

I'd like to be able to see if credentials (server/instance, username/password or win authentication) specified by an administrator are actually able to connect to a database at my application installation time. I'm using WISE if that matters at all. How can I go about accomplishing this? Can it be done with something like osql.exe??

Re: SQL Server Connect Test

David Mujica22-Dec-09 5:27

David Mujica

22-Dec-09 5:27

Yes, you can test your credentials. Execute a command like select count(*) from myTable and redirect the output to a file, then parse the file for the expected results and if there is no error you have validated that your username/password combination is correct.

Do this through osql.

Merge Replication Problem

Jay Royall21-Dec-09 23:05

Jay Royall

21-Dec-09 23:05

Hi,

I have configured 2 SQL Server 2005 instances to replicate using Merge Replication. This is just a test environment at the moment, therefore both instances are on different servers but within the same domain and both running under the same administrator accounts.

The replication seems to run smoothly in that the snapshot agent runs successfully and then the subscriber synchronises with no errors. But, when I run my app on the subscriber I get a error when trying to perform a particular task:

Invalid object name 'dbo.MSmerge_genvw_5A046C944AD94C85AA554B95189A5399'.

The said task is calling a stored procedure which I have defined and which I can see has been replicated to the subscriber. I am new to replication so not entirely sure what is going on. Any help would be greatly appreciated.

Also, it may be worth pointing out that the subscriber is an express version, therefore all agents are running on the publisher.

Thanks.

Re: Merge Replication Problem

Jay Royall23-Dec-09 1:59

Jay Royall

23-Dec-09 1:59

Just in case anyone is interested, I solved the problem by replacing the SQL Express Server with the full version. All seems to be working correctly now.

Query

sarang_k21-Dec-09 22:04

sarang_k

21-Dec-09 22:04

Hi all,

I created a table in which there r n fields and in that two of them are date field i.e., startdate and enddate.

Now i want to write a query,to fetch the records from table which comes under startdate and enddate.

so,i need a help how to write the query.

Thanks in advance.

Re: Query

Blue_Boy21-Dec-09 22:33

Blue_Boy

21-Dec-09 22:33

What you did so far? Have you try to write a query?
As I could understood you then query should be like:
select startdate,enddate from mytable where startdate >'2009.12.01' and enddate > '2009.12.22'

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

Re: Query

Niladri_Biswas22-Dec-09 16:32

Niladri_Biswas

22-Dec-09 16:32

Try this

declare @t table(somevalue varchar(100),startdate datetime, enddate datetime)
insert into @t 
	select 'somevalue1','01/14/2009', '01/20/2009' union all
	select 'somevalue2','01/15/2009', '01/22/2009' union all
	select 'somevalue3','01/18/2009', '01/31/2009' union all
	select 'somevalue4','02/01/2009', '02/10/2009' union all
	select 'somevalue5','05/14/2009', '05/20/2009'

I want to get the records where between '01/14/2009' and '02/01/2009' (inclusive)

Query:

select * from @t
where startdate >='01/14/2009' and enddate <='02/01/2009'

Output:

somevalue startdate enddate

somevalue1	2009-01-14 00:00:00.000	2009-01-20 00:00:00.000
somevalue2	2009-01-15 00:00:00.000	2009-01-22 00:00:00.000
somevalue3	2009-01-18 00:00:00.000	2009-01-31 00:00:00.000

Niladri Biswas

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