Database

Re: sqlBase --> sql server

1-Apr-08 23:05

Simply, connection timeouts refer to the time it takes the Server to run a query and generate a result. If you reach connection timeout, your conenction to the database will be terminated and you will need to re-initialise the connection to the database.
When we talk about Pooling, what that refers to is that when you try and make a connection to the database, the server tries to make contact with the database engine. If you set the timeout value to 5, it will try for 5 seconds and if no connection has been established, it will return you an error (Connection Timeout). Which means that you didnt have a connection to begin with. If you do establish a connection and then leave the connection (Close()), you will again lose the connection and you will need to go through the same process of establishing the connection.

Hope that this helps.

Regards,
Ali

sqlBase --> sql server

teddddddddddd1-Apr-08 7:04

teddddddddddd

1-Apr-08 7:04

Hello

This is the statement, which I am use in Sql Base

select IF (@SCAN (@UPPER(enota),'MI') ,1 , 60 ) , 2
from
members;

How can I execute similiar statement in SQL Server?

Thanks

lp

Mark J. Miller2-Apr-08 9:47

Mark J. Miller

2-Apr-08 9:47

I can't really tell what it is you are trying to do. I tried checking SqlBase documentation for @SCAN but didn't find any description for it other than it was listed as a String function.

As a best guess, I imagine you'd use CASE in place of IF, CHARINDEX in place of @SCAN and UPPER in place of @UPPER.

CASE: http://msdn2.microsoft.com/en-us/library/ms181765.aspx[^]

CHARINDEX and UPPER: http://msdn2.microsoft.com/en-us/library/ms181984.aspx[^]

Mark's blog: developMENTALmadness.blogspot.com

Send table name to a stored procedure

Yevgeny Efter1-Apr-08 6:51

Yevgeny Efter

1-Apr-08 6:51

Hello!

I am writing a stored procedure that should insert a row in a table that it's name is sent to it as a parameter.

I do it this way:

CREATE PROCEDURE myProcedure

@tablename ntext,

AS

INSERT INTO @tablename(...)

and I get an error "Must declare the table variable "@tablename"".

What should I do?

Thanks in advance!

Have a nice day!

Re: Send table name to a stored procedure

Yevgeny Efter1-Apr-08 8:09

Yevgeny Efter

1-Apr-08 8:09

Solved the problem. I'm using EXEC instead of an explicit INSERT.

Have a nice day!

SQL or XML

Sutheesh chandran1-Apr-08 1:16

Sutheesh chandran

1-Apr-08 1:16

Which one is feasible to store data?

Sutheesh Ramachandran
Cybernet Software Systems,
T Nagar, Chennai 17.

Re: SQL or XML

Rob Philpott1-Apr-08 1:42

Rob Philpott

1-Apr-08 1:42

That's something of a broad question there. XML doesn't store anything - it's a mark up language. You store your data as XML, and you could do this in a file or a database or something.

SQL is a query language for querying relational databases. Typically databases are geared to storing things in a tabular fashion.

If the data you want to store is hierarchial in nature then XML is a good choice, if tabular then a database table is a good choice.

Sql Server 2005 has quite good support for XML and you can use XQuery within stored procs to query stuff stored as XML.

Regards,
Rob Philpott.

how can i copy a column data to a same column

koolprasad200331-Mar-08 23:58

koolprasad2003

31-Mar-08 23:58

hi there,
i have a column contain some values..i just want to copy the data of column to same column after orginal data.

means if the column contains 1,2,3 and i copy the same column in it then it will be 1,2,3,1,2,3
Hope u understand

what can do ???

Regards and thankx in advnace
Koolprasad2003 Smile | :)

Be a good listener...Because Opprtunity knoughts softly...N-Joy

Re: how can i copy a column data to a same column

Rob Philpott1-Apr-08 1:43

Rob Philpott

1-Apr-08 1:43

What datatype is the column?

Regards,
Rob Philpott.

Re: how can i copy a column data to a same column

Ashfield1-Apr-08 2:07

Ashfield

1-Apr-08 2:07

koolprasad2003 wrote:
means if the column contains 1,2,3 and i copy the same column in it then it will be 1,2,3,1,2,3

I assume from this the column is a varchar or char, so

update table1<br />
set col1 = col1 + ',' + col1

will do the job. Watch out for nulls though.

Hope this helps

Bob
Ashfield Consultants Ltd

SQL Query

Ali Rashid31-Mar-08 23:45

31-Mar-08 23:45

Hi Guys!
I am using the following Statements:

Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
From ( select ItemPrice, Count(*) as ItemPriceCount
from #Mytable
group by ItemPrice
Having Count(*) = 1) as MyTableWithUniqueItemPrices

which returns me the unique value in the table. The only problem is, how do I also get the itemID without the SQL Server listing all the values in the Table. My Original Question is pasted below for reference.

Thanks once again to all the guys who had helped me earlier.

Ali.
-----------------------------------------------------------------------------------------------

ORIGINAL QUESTION:
=================

I tried to look around but I could not find an answer for this question. I need to run an SQl Query which retuns me the highest Unique Value and the lowest Unique value from the same column. For example. Lets say the column name is itemPrice, and has the following values: 10, 10, 11, 12, 12, 13, 14, 14 respectively. How can I get the 11 and 13 as the result of my query. I have tried DISTINCT, MIN, MAX and HAVING but to no avail. I am using SQL Server 2000.

Blue_Boy1-Apr-08 3:28

1-Apr-08 3:28

did you check my answer in your original posted message?
Hope it helps.

I Love SQL

Ali Rashid1-Apr-08 6:01

1-Apr-08 6:01

Hey Blue Boy,
Thanks for the reply. I did and it returns the min and max value properly. But I am not getting the ID for the item.

Thanks for the help.

Ali Rashid1-Apr-08 6:07

1-Apr-08 6:07

Hey Blue Boy,
Sorry, wrote it the wrong way, it returns the id if I add it but it did not return the unique values I was looking for. The query given by Syed returns the values I want but it does not return the ID for the product.
Thanks.

Blue_Boy1-Apr-08 9:17

1-Apr-08 9:17

tell me the columns of the table.

Does your table have columns like this:

ID      itemPrice <br />
-----------------------<br />
1    |   10<br />
2    |   10<br />
3    |   11<br />
4    |   12<br />
5    |   12<br />
6    |   13<br />
7    |   14<br />
8    |   14

I Love SQL

Ali Rashid1-Apr-08 22:05

1-Apr-08 22:05

Hey Blue Boy! Big Grin | :-D

Thanks for the help mate. Yes the columns and the values that you are displaying are correct. But from the resultset, what I need is ID: 3 and value 11 as Min(itemPrice), and ID: 6 and value 13 as Max(itemPrice). What I need is the lowest unique or distinct (whatever you would like to call it) and the maximum value which does not have similar values (e.g 11 and 13 have only one entry where as others have more than one).
The other query that I mentioned in the last post returns me the values 11 and 13 but it is not returning the ID's (3 and 6) respectively.
I hope I make some sense now. Sorry if I failed to explain it properly earlier.
Thanks and kind regards,

Ali

Blue_Boy2-Apr-08 7:43

2-Apr-08 7:43

I guess you are looking for this

select id,itemprice from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1<br />
<br />
and id in( (select top 1  id from Table1 where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id asc )  <br />
, (select top 1  id from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id desc))

I Love SQL

Re: SQL Query [modified]

Syed Mehroz Alam2-Apr-08 9:30

Syed Mehroz Alam

2-Apr-08 9:30

Since we are sure that our Min/Max prices will be unique so we can write a subquery for retrieving the IDs, here's an addition to my original query.

Select MinUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MinUniquePrice ) as MinPriceID,
	MaxUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MaxUniquePrice ) as MaxPriceID
From
(
	Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
	 From
		( select ItemPrice, Count(*) as ItemPriceCount 
			from #Mytable 
			group by ItemPrice 
			Having Count(*) = 1 
		) as MyTableWithUniqueItemPrices
) As MyTableWithMinMaxItemPrices

But Blue_boy's approach was better. I really liked it. Big Grin | :-D

Regards,
Mehroz

My Articles

modified on Wednesday, April 2, 2008 3:55 PM

Blue_Boy3-Apr-08 12:14

3-Apr-08 12:14

thanks dude Wink | ;)

I Love SQL

mushfiqcs2-Apr-08 1:10

mushfiqcs

2-Apr-08 1:10

-- Solution 1
-- Say There are products in a table name products
-- with a column named proPrice.
-- Now insert your data 10, 10, 11, 12, 12, 13, 14, 14
-- Now run the scripts
-- Keep practicing. Take care mushfiqcs@yahoo.com
select proPrice, count(proPrice) from products
group by proPrice
Having count(proPrice)<2

S M Mushfiqur Rahman

mushfiqcs2-Apr-08 1:10

mushfiqcs

2-Apr-08 1:10

RAM usage

Dobromir Dimitrov31-Mar-08 21:55

Dobromir Dimitrov

31-Mar-08 21:55

Hello.
What I'd like to ask is if there is a way to configure SQL Server 2005 to use only a certain amount of RAM. I have a machine with 4GB RAM, and there are a lot of apps running on it. It is when SQL Server starts executing a query over a relatively large database (e.g. 20 - 30 GB)that things go bad for all other apps - SQL Server uses almost all of the available RAM, leaving only a very limited amount for all other applications to use.
Any suggestions?

Re: RAM usage

GuyThiebaut31-Mar-08 23:56

GuyThiebaut

31-Mar-08 23:56

Those are fairly large databases and depending on your queries you could be hitting the processor heavily - as you are discovering.

The only solution I can think of is to remove all those extra applications.
Good practise dictates that your SQL Server is a machine that is primarily a dedicated server i.e you have no other applications on it (other than an email client, backup application etc)

If you are using databases of the size you mention I suggest you get another machine on which to run the other applications.

Some more tips:
Keep your databases and transaction logs on separate disks.
Make sure that your applications are not on either of these disks.

I know this doesn't answer you question directly...

You always pass failure on the way to success.

Re: RAM usage

Ali Rashid1-Apr-08 23:28