Database

database Performance......

30-Jun-09 4:53

Hi,

The random numbers can be generated in multiple ways.

But in all the cases the questions should have a unique QuestionId( a Primary Key)

Step 1:

Create a table (say tblQuestions) with QuestionId(int Primary Key), Questions(Varchar(50))

Step 2:

Insert some values:

QuestionId Questions
---------------------
1	Q1
2	Q2
3	Q3
4	Q4
5	Q5
6	Q6
7	Q7
8	Q8
9	Q9
10	Q10
11	Q11
12	Q12
13	Q20
14	Q25
15	Q30

Solution 1:

Suppose you have a fixed set of questions (say 1 to 30).

Fire this query

SELECT QUESTIONS 

FROM TBLQUESTIONS 

WHERE QUESTIONID = ROUND(((30 - 1) * RAND() + 1), 0)

where 30 is the upper limit and 1 is lower limit

Solution 2:

Suppose your question may grow to any length dynamically

Fire this query

SELECT QUESTIONS 

FROM TBLQUESTIONS 

WHERE QUESTIONID = CAST(RAND() * N AS INT)

Where N is any number.

Means if you have records(i.e. Questions) from 1 to 10, N = 10

if you have records(i.e. Questions) from 1 to 50, N = 50

if you have records(i.e. Questions) from 1 to 100, N = 100 etc.

Solution 3:

For the same above scenario, the following query can also be use

SELECT QUESTIONS 

FROM TBLQUESTIONS 

WHERE QUESTIONID = ABS(CAST(NEWID() AS binary(6)) %N) + 1

Where N is any number.

Solution 4:

Send the random numbers from your front end application to the backend either in INLINE SQL or in STORED PROC, and retieve the questions based on the QuestionId.

Hope this helps
Smile | :)

Niladri Biswas

modified on Tuesday, June 30, 2009 11:26 AM

SQL server 2003

jamith30-Jun-09 2:52

jamith

30-Jun-09 2:52

How i connect a database in the main server to a client machine i am using SQL 2003

Re: SQL server 2003

Jerry Hammond30-Jun-09 13:25

Jerry Hammond

30-Jun-09 13:25

jamith wrote:
How i connect a database in the main server to a client machine i am using SQL 2003

You are? How interesting.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: SQL server 2003

Mycroft Holmes30-Jun-09 17:51

Mycroft Holmes

30-Jun-09 17:51

You have that reversed, the client conection to the server - see connectionstrings.com

Never underestimate the power of human stupidity
RAH

SQL 2005 Replication

Abdul Rahman Hamidy30-Jun-09 2:29

Abdul Rahman Hamidy

30-Jun-09 2:29

Dear All,
I have established transactional with updatable subscription replication among two sql 2005 instance.
Server A as Publisher and Server B as subscriber, both have same db
the questions is how can i keep existing data into server b Database.

i have changed the status for "Action if name is in use to Keep existing object unchanged" in my publication, but still got no success?

your help is really appreciated

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Isaac Gordon29-Jun-09 23:59

Isaac Gordon

29-Jun-09 23:59

Hi Experts
i Have Data of 2 millions of record i am using C#.NET
as a front end then suggest me which database is better SQL Server or MS Access .we have perform sorting and searching also on this database
.
Dinesh

Re: database Performance......

Blue_Boy30-Jun-09 0:15

Blue_Boy

30-Jun-09 0:15

I suggest you to chose SQL Server.

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.

Re: database Performance......

Niladri_Biswas30-Jun-09 0:26

Re: database Performance......

30-Jun-09 0:26

MS Access can support even 2 million of records.

But considering other factors like

Feature SQL Server 2000 Access 2000
database size 1,048,516 TB 2 GB plus
linked tables size
objects in a database 2,147,483,647 32,768
user name length 128 20
password length 128 14
table name length 128 64
column name length 128 64
index name length 128 64
Number of concurrent users limited 255
columns per table 1024 255
table size limited 1 GB
number of indexes in a table 250 32
number of columns in an index 16 10
bytes per row 8060 2000
number of tables in a query 256 32
columns per SELECT statement 4096 255
nested subqueries 32 50
number of enforced relationships 253 32
(obtained from http://www.allinterview.com/showanswers/63402.html[^])

it is better to go with SQL SERVER.

However, cost is also a factor!

But if your requirement is not so high, you can go with SQL SERVER EXPRESS EDITION. which is free

But the license is limited.(hope 10 users)

Hope this answers your question.
Smile | :)

Niladri Biswas

Isaac Gordon30-Jun-09 0:33

Isaac Gordon

30-Jun-09 0:33

thank sir

Hidden tables

mistryshailesh29-Jun-09 21:52

mistryshailesh

29-Jun-09 21:52

Can v create permanent hidden tables in sql 2000 and how?

Re: Hidden tables

Niladri_Biswas29-Jun-09 23:10

OLEDB network failure recovery problem.

29-Jun-09 23:10

Hi,
Though the question is not very clear to me but if you mean that you want to give preference of View access over table access to the users,then the answer is

DENY SELECT ON TABLE TO USER

GRANT SELECT ON VIEW TO USER

If you want to hide the system tables in SQL SERVER 2000, follow this steps:

1) Choose a registered server

2) Right click on that and select Edit SQL Server Registration properties

3)Uncheck

Show system databases and system objects

Hope this helps
Smile | :)

Niladri Biswas

CherezZaboro29-Jun-09 6:57

CherezZaboro

29-Jun-09 6:57

Hi everybody!
I have my own kind of server that for each client keeps an oledb connection opened. It also uses a thread pool for serving the clients. Now let's say the connection between my server and SQL server breaks for whatever reason. While it's down if my server attempts to do anything with the database it obviously fails. Then network issue gets solved.
If my server attempts to continue manipulate the same oledb connection - it still fails. If I reconnect it fails anyway as long as there are those other olddb connections for those other clients of mine. Only when all oledb connections are closed I can start connecting successfully to the SQL server. It seems that in multithreaded/same exe session environment all oledb connections must be closed and reopened in order to recover. Is that true? Or is there a better way to force oledb to revive connections?
Please help!
Thanks a lot in advance.

EncryptbyKey VARCHAR(50)

Saamir29-Jun-09 6:42

Saamir

29-Jun-09 6:42

Hi guys,
I am having a weird issue, I am trying the following and for some reason varchar(50) value is set to null when I encrypt or decrypt it. If I change it from varchar to nvarchar it works. Our table in the database is already set to varchar to store the encrypted information and can't change it due to licensing issues. Please help, what am I doing wrong?

OPEN SYMMETRIC KEY DSBEncryptionKey DECRYPTION BY CERTIFICATE EncryptDsb
DECLARE @encryptedNum varchar(50) -- varchar doesn't work here, nvarchar works

SELECT @encryptedNum=EncryptByKey(Key_GUID('DSBEncryptionKey '), '111000614');
select CONVERT(varchar(50),decryptbykeyautocert(cert_id('EncryptDsb'), NULL, @encryptedNum))

FYI, my symetric key uses AES_256 algorithm.

Please help!

Sameer

My Sql Query Help

Hulicat29-Jun-09 6:26

29-Jun-09 6:26

I have a query that is joining tables and calculating the SUM.

It seems to be working, however; the "SUM" is double what it should be.

Any ideas:

Select, spendaccount, spend.fromcode, spend.keyword,
spend.kwtype, SUM (Rev.trans)
Trans, SUM (rev.newaccounts)
newaccounts, SUM (rev.gross) Gross, SUM (rev.refunds)
Refunds, SUM (rev.net) Net, spend.clicks, spend.spend

From spend, Rev wherespend.fromcode=
rev.fromcode

group by spend.fromcode

Regards,
Hulicat

Niladri_Biswas29-Jun-09 6:54

29-Jun-09 6:54

Hi,
Which SUM you are talking about?

SUM (Rev.trans)?

SUM (rev.newaccounts)?

SUM (rev.gross)?
SUM (rev.refunds)?

SUM (rev.net) Net?

Sniff | :^)

Niladri Biswas

Hulicat29-Jun-09 11:23

Re: My Sql Query Help [modified]

29-Jun-09 11:23

Hello and thanks for the reply; I am not sure on the approach but here is what I am trying to do:

Spend table-> spend (SUM'd), clicks, kytype account
Rev table-> transactions, newaccounts, gross, refunds, net (SUM'd),

From spend, Rev wherespend.fromcode=
rev.fromcode

----all in one output--------

Regards,
Hulicat

Niladri_Biswas29-Jun-09 19:33

29-Jun-09 19:33

I am assuming certain things.

I have created both the tables and has inserted certain values. Please look into it whether you also have the same values or not!

tblSpend

COLUMN NAME			DATATYPE	
-----------			-------
SPENDACCOUNT			int	
FROMCODE			int	
KEYWORD				varchar(50)	
KWTYPE				int	
CLICKS				int	
SPEND				int

Values in tblSpend are

101	1	KeyWord1	1	1	10
102	2	KeyWord2	1	10	11
103	3	KeyWord3	2	21	15
104	4	KeyWord4	3	22	16
105	5	KeyWord5	5	11	11
106	6	KeyWord6	2	33	12
107	7	KeyWord7	1	111	11
108	8	KeyWord8	2	22	2
109	9	KeyWord9	1	1	1
110	10	KeyWord10	1	2	3

tblRev

COLUMN NAME			DATATYPE	
-----------			-------
NEWACCOUNTS			int
GROSS				int
REFUNDS				int
NET				int
TRANS				int
FROMCODE			int

Values in tblRev are

1	10	20	10	10	1
2	20	30	10	20	2
3	30	40	15	25	3
4	40	50	45	5	4
5	50	100	50	50	5
6	60	400	200	200	6
7	70	100	100	0	7
8	70	100	50	50	7
6	111	300	120	180	6

The Query is

SELECT 

		S.SPENDACCOUNT, S.KEYWORD, 
		S.KWTYPE,S.CLICKS,S.SPEND,

		D.FROMCODE,D.TRANS,D.NEWACCOUNTS,
		D.GROSS,D.REFUNDS,D.NET

	FROM (

				SELECT 
						T.FROMCODE,
						SUM (R.TRANS) TRANS,
						SUM (R.NEWACCOUNTS) NEWACCOUNTS,	
						SUM (R.GROSS) GROSS,	
						SUM (R.REFUNDS) REFUNDS,	
						SUM (R.NET) NET

				FROM TBLSPEND T, TBLREV R 
				WHERE 
						T.FROMCODE= R.FROMCODE

				GROUP BY T.FROMCODE 

			) D, TBLSPEND S

WHERE  S.FROMCODE   = D.FROMCODE

OUTPUT:

101	KeyWord1	1	1	10	1	1	10	20	10	10
102	KeyWord2	1	10	11	2	2	20	30	10	20
103	KeyWord3	2	21	15	3	3	30	40	15	25
104	KeyWord4	3	22	16	4	4	40	50	45	5
105	KeyWord5	5	11	11	5	5	50	100	50	50
106	KeyWord6	2	33	12	6	12	171	700	320	380
107	KeyWord7	1	111	11	7	15	140	200	150	50

Hope this helps
Smile | :)

Niladri Biswas

modified on Tuesday, June 30, 2009 4:26 AM

Hulicat1-Jul-09 7:06

1-Jul-09 7:06

Hello Niladri_Biswas, thanks for your help.

Actually I know what the problem is I am just can't figure out the query.

Example of data a problem.

Fromcode Spend.spend rev.gross
ABC 10 70
ABC 20 50
ABC 10 20

Results look like the following:
rev.gross sum'd + spend.spend

Fromcode spend.spend rev.gross
abc 140 (expected 90)
abc 160 (expected 100)
abc etc

I want the total for each column based on the fromcode but not joined sums if that makes sense.

Thanks and Regards

Regards,
Hulicat

smcnulty200029-Jun-09 21:19

smcnulty2000

29-Jun-09 21:19

If you want to solve a problem like this; sometimes you can figure out what is going on by running a similar query to the one you do have but instead of using SUM use COUNT. This is just a debugging method, not the solution.

I suspect if you did that with your original query all of the lines that you saw double would come out with a count of 2 or higher.

Maybe you should try something more like this for your query:

select 
spend.account,
spend.fromcode,
spend.keyword,
spend.kwtype, 
(select sum(rev.trans) from rev where rev.fromcode=spend.fromcode) trans, 
(select sum(rev.newaccounts) from rev where rev.fromcode=spend.fromcode) newaccounts, 
(select sum(rev.gross) from rev where rev.fromcode=spend.fromcode) gross,
(select sum(rev.refunds) from rev where rev.fromcode=spend.fromcode) refunds,
(select sum(rev.net) from rev where rev.fromcode=spend.fromcode) net, 
spend.clicks, 
spend.spend
from spend 
order by spend.fromcode

I don't have mysql running so I can't test it myself.

_____________________________
When life hands you marmots, make marmalade.

Hulicat30-Jun-09 3:59

30-Jun-09 3:59

Thanks "smcnulty2000" and "Niladri_Biswas" your help and input was greatly appreciated.

Regards

Regards,
Hulicat

Hulicat30-Jun-09 7:30