Database

Ashfield27-Jun-08 3:51

Re: select top query [modified]

27-Jun-08 3:51

I suspect you are in sql server 2000, which will not accept a parameter for the top clause. In 2005 it would work. You need to create and execute the sql dynamically for it to work in 2000.

Bob
Ashfield Consultants Ltd

SomeGuyThatIsMe27-Jun-08 8:58

SomeGuyThatIsMe

27-Jun-08 8:58

in sql server 2000 try

create procedure ss(@s int)

as

SET ROWCOUNT @s
select sno from summa1 order by newid
SET ROWCOUNT 0

ROWCOUNT should turn pink..i havnt used it in a while so i may have typed it wrong. I do not believe that Rowcount is available in sql server 2k5, it was replaced with something but i dont remember what.

EDIT: ROWCOUNT is the correct one..@@ROWCOUNT wont work.

you could also use dynamic sql like

EXEC 'SELECT TOP ' + CAST(@s AS CHAR) + ' sno FROM summa1 ORDER BY newid'

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

modified on Friday, June 27, 2008 4:16 PM

Ashfield27-Jun-08 9:29

27-Jun-08 9:29

I don't think this would work, I'm pretty sure @@rowcount is read only. I don't have sql server 2000 installed, but I have never seen @@rowcount being set, only ever read.

What I think is meant is set rowcount, but this cannot be set to a parameter, so you are back to dynamic sql

SomeGuyThatIsMe wrote:
I do not believe that Rowcount is available in sql server 2k5,

Both rowcount (to set the number of rows returned) and @@rowcount (the number of rows affected) are both still alive and kicking in SQL Server 2005

Bob
Ashfield Consultants Ltd

SomeGuyThatIsMe27-Jun-08 10:16

SomeGuyThatIsMe

27-Jun-08 10:16

It works fine i have it done in multiple places mostly in stored procedures, and in some other queries. SET ROWCOUNT it works well for randomizing the data you pull from a table with no PK. @@ROWCOUNT might be read only, ROWCOUNT is the correct one to use.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Ashfield27-Jun-08 10:22

27-Jun-08 10:22

Sorry, you can set rowcount to a parameter, I just couldn't type when I tried it Blush | :O

Bob
Ashfield Consultants Ltd

Alsvha29-Jun-08 20:35

Alsvha

29-Jun-08 20:35

Although be aware that as far as I know, ROWCOUNT is being deprecated in 2K5 and it is removed from SQL Server 2K8 (or perhaps the one after 2K8)

---------------------------
Blogging about SQL, Technology and many other things

error connecting to remote sql server

SomeGuyThatIsMe30-Jun-08 2:09

SomeGuyThatIsMe

30-Jun-08 2:09

yep thats what i read...they are replacing it with something, but i dont think it works quite the same.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

karthi8427-Jun-08 1:49

karthi84

27-Jun-08 1:49

hi,
when i try to connect to a remote sql server 2008 from my application i get the below error.

'An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)'

but the remote sql server is accessible from other machines through the same application without any error.

thanks & regards
karthikeyan

Marek Grzenkowicz27-Jun-08 2:50

Marek Grzenkowicz

27-Jun-08 2:50

Check this link[^]. Is there any other error number (apart from 40)?

karthi8427-Jun-08 3:09

karthi84

27-Jun-08 3:09

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

this was the complete error message. please note that i am connecting to sql server 2008 on a server and sql 2005 which is installed with visual studio 2005 is in the local machine.

Marek Grzenkowicz27-Jun-08 4:10

Marek Grzenkowicz

27-Jun-08 4:10

karthi84 wrote:
please note that i am connecting to sql server 2008 on a server

Are you positive about this?

I am asking because it seems weird to me that the error message mentions SQL Server 2005 (When connecting to SQL Server 2005 ...) when you are connecting to SQL Server 2008.

karthi8430-Jun-08 1:10

karthi84

30-Jun-08 1:10

ya i am sure about that. i have installed sql 2008 ctp5 "Katmai" in my machine and have VS 2005 along with it. when i try to connect to the database through my application i get this error message.

thanks & regards
karthikeyan

Records in sql server

iamdking26-Jun-08 23:51

iamdking

26-Jun-08 23:51

Hello friends, Can we create record in sql server. (similar to record in oracle). to hold different datatype fields?

Amit

Mycroft Holmes27-Jun-08 1:11

Mycroft Holmes

27-Jun-08 1:11

Well that has got to be one of the lamest questions I have EVER come across. PLEASE, please do some reading, do some reaserch, open your freakin eyes and THINK, SQL Server is a database, admittedly less robust/powerful than Oracle but right up there for functionality, how could it NOT support different data type in it's table format.

iamdking - you are an idiot. Sorry, could not resist and it may be unjustified but hey it's out there.

Never underestimate the power of human stupidity
RAH

iamdking27-Jun-08 1:48

iamdking

27-Jun-08 1:48

Thank You.
For your kind reply. I know I am idiot. But the fact is that I just started working on SQL Server and didnt found any record type variable in msdn. Thats why I ask such stupid question. Offcourse I didnt get answer ..But thanks for your quick response. $Shucks | :-\$

Amit

Ashfield27-Jun-08 2:41

27-Jun-08 2:41

See what you mean, I just tried searching msdn for datatypes and it took some finding. If you have enterprise manager the help in there is better for this, but the link below may be of help.

SQL Server

Bob
Ashfield Consultants Ltd

I need to transfer data between tables within a database

Mycroft Holmes27-Jun-08 4:13

Mycroft Holmes

27-Jun-08 4:13

Amit, I should apologise just because I had a cow of a day I should not have bitched at you.

And I really could not resist having a go at iamdaking moniker;)

Never underestimate the power of human stupidity
RAH

jmohan26-Jun-08 20:37

jmohan

26-Jun-08 20:37

Hi all,

I am using MySQL 5.0 database. I need to transfer the data from one table to another within a mysql database based on the particular time interval. Is it possible to transfer data among the tables a database automatically. Please help me.

Thanks in Advance,

J. Mohan

J. Mohan

Re: I need to transfer data between tables within a database

Mycroft Holmes27-Jun-08 1:15

Mycroft Holmes

27-Jun-08 1:15

Hmm I don't know about MySQL but most databases have no idea what time is. To do this you need a running process (to keep track of the time) and to periodically execute the transfer method. In SQL Server I would set it up as a job. If there is no concept of a job in MySQL then you may need to write an external process to do the scheduling for you.

Never underestimate the power of human stupidity
RAH

Best practice?

econner26-Jun-08 15:52

econner

26-Jun-08 15:52

With the following table I am looking for suggestions on the best way to query the table for the follow criteria.

ID SalesID FKey FValue
1 1 12345 1.25
2 1 555 2.25
3 1 775 3.00
4 2 432 2.00
5 2 12345 1.00
6 3 321 1.11
7 3 223 1.00
8 4 12345 1.25
9 4 775 3.00
10 4 222 1.00

I need to query the table for the SalesID number for the sales that sold both '12345' and '775'. This would be only SalesIDs 1 and 4.

Querying FKey = '12345' OR FKey = '775' would return 1,2, and 4 and not just 1 and 4.

Re: Best practice?

Krish - KP26-Jun-08 17:33

Krish - KP

26-Jun-08 17:33

Can be done only using corelated subqueries

SELECT Distinct SalesID FROM fky a WHERE 
EXISTS (SELECT * FROM fky b WHERE a.SalesID = b.SalesID AND fkey = 12345)
	AND 
EXISTS (SELECT * FROM fky c WHERE a.SalesID = c.SalesID AND fkey = 775)

Regards
KP

Re: Best practice?

econner27-Jun-08 10:47

econner

27-Jun-08 10:47

Thanks. This worked great.

Create Clustered Index on VIEW

Ahmet GULBAY26-Jun-08 3:24

Ahmet GULBAY

26-Jun-08 3:24

Hi,

We have two tables and we views like:

1) create view ... select bno from A : we can create a clustered index on this view
2) create view ... select bno from B : we can create a clustered index on this view
3) create view ...
select A.bno
from A
LEFT JOIN B ON B.bno=A.bno
: we cannot create a clustered index for this view, it gives error like:
"... one or more disallowed construct"

How can we solve this problem.

Thanks for your help (:

Re: Create Clustered Index on VIEW

Ashfield26-Jun-08 3:44