hello,
I built my application using SQLClient classes and MSSQL database
i have another version of the database with Oracle, is changing to OracleClient classes the only change i have to do in order to run the program and the connection string?
Thanks
Nope...You also have to install the Oracle client, and give the accountthat your ASP.Net application is running under access to the directorythat the Oracle client is in.Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @.value for the number
SELECT TOP @.value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.
I believe that is possible in 2005 and not in 2000. You can however use SET ROWCOUNT in 2000.
DECLARE @.tintSET @.t = 10SET ROWCOUNT @.tSELECT *FROM YourTableSET ROWCOUNT 0|||
In 2005, the following works:
DECLARE @.t int
SET @.t = 20
SELECT TOP(@.t) * FROM yourTable
I have a strange problem that I cannot seem to solve. I have two server running Windows 2003 MSSQL on one IIS on the other.
Out of the blue I have slow queries that cause asp script timeouts and I cannot figure it out. I have moved some test code to other sites on the server and still have these long delays.
I have tried dropping the tables, then the database entirely and restoring and still slow. I run the same code on my test server at home running both iis and sql on the same server and it is a little slow but not too bad and the test server is a duron 1.6.
Here it is:
Code: ( text )
The data has not changed, it is only updated as we add documents and none have been added for a couple of months.
Its not really that much data.
Oracle 9i provides a "USING" clause option for inner joins, that
allows me to say:
SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
for our software make use of, but we also support SQL Server. There
is no USING option available, and
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
causes an ambiguous column error on KeyColumn.
Is there any equivalent to this Oracle functionality on SQL Server?
KingGreg>> "SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
causes an ambiguous column error on KeyColumn <<
And the USING clause is limited to equi-joins. But the real problem is
that good SQL programmers do not use "SELECT *" in production code. It
changes at run time and is too unclear and dangerous.
NATURAL JOIN and USING were two of the worst ideas we put into SQL-92.
I hope they get deprecated soon.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||On 14 May 2004 13:02:13 -0700, KingGreg wrote:
>All,
>Oracle 9i provides a "USING" clause option for inner joins, that
>allows me to say:
>SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
>assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
>for our software make use of, but we also support SQL Server. There
>is no USING option available, and
>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>causes an ambiguous column error on KeyColumn.
I can't reproduce this error:
create table TBL1 (KeyColumn int not null primary key)
create table TBL2 (KeyColumn int not null primary key)
insert TBL1 (KeyColumn)
values(1)
insert TBL1 (KeyColumn)
values(2)
insert TBL2 (KeyColumn)
values(1)
insert TBL2 (KeyColumn)
values(3)
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
drop table TBL1
drop table TBL2
KeyColumn KeyColumn
---- ----
1 1
(1 row(s) affected)
Can you post the actual SQL that returns this error, as I assume there is
an error somewhere in the query.
>Is there any equivalent to this Oracle functionality on SQL Server?
No, there isn't.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I understand that I was not clear because you have to be using derived
table. See below:
> create table TBL1 (KeyColumn int not null primary key)
> create table TBL2 (KeyColumn int not null primary key)
> insert TBL1 (KeyColumn)
> values(1)
> insert TBL1 (KeyColumn)
> values(2)
> insert TBL2 (KeyColumn)
> values(1)
> insert TBL2 (KeyColumn)
> values(3)
> SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> drop table TBL1
> drop table TBL2
>
> KeyColumn KeyColumn
> ---- ----
> 1 1
> (1 row(s) affected)
Try :
1 SELECT KeyColumn
2 FROM (
3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
4 ) TBL
Gives error : Column 'KeyColumn' specified multiple times for TBL
As you noted it does not fail when running line 3 by itself.
I guess I must be a bad SQL programmer, but this is nonetheless the
direction I am pursuing because of numerous other limitations in SQL
Server or Oracle that prevents using some other solution.
KingGreg|||Can you be more specific?
I agree with Joe that USING and NATURAL JOIN are undesiravel features,
especially since their only purpose in life seems to be to add convenience.
Obviously you are of a different opinion. As a developer I (and quite
likely MS folks listening in) am curious to learn where you see the
value ad.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||KingGreg wrote:
> I understand that I was not clear because you have to be using derived
> table. See below:
>
>>create table TBL1 (KeyColumn int not null primary key)
>>create table TBL2 (KeyColumn int not null primary key)
>>insert TBL1 (KeyColumn)
>>values(1)
>>insert TBL1 (KeyColumn)
>>values(2)
>>insert TBL2 (KeyColumn)
>>values(1)
>>insert TBL2 (KeyColumn)
>>values(3)
>>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>>drop table TBL1
>>drop table TBL2
>>
>>
>>KeyColumn KeyColumn
>>---- ----
>>1 1
>>
>>(1 row(s) affected)
>>
>
> Try :
> 1 SELECT KeyColumn
> 2 FROM (
> 3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> 4 ) TBL
> Gives error : Column 'KeyColumn' specified multiple times for TBL
> As you noted it does not fail when running line 3 by itself.
> I guess I must be a bad SQL programmer, but this is nonetheless the
> direction I am pursuing because of numerous other limitations in SQL
> Server or Oracle that prevents using some other solution.
> KingGreg
If in Oracle ... I suspect what you are trying to do is:
SELECT KeyColumn
FROM (
SELECT *
FROM TBL1
WHERE TBL1.KeyColumn = TBL2.KeyColumn);
Using ISO standard syntax. If in 9i or above you could also use
ANSI standard syntax.
--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||As you have an INNER JOIN it doesn't matter which value of keycolumn you
reference as long as you specify an alias. It's best to avoid using SELECT *
in production code anyway (except in an EXISTS subquery). Try this:
SELECT keycolumn
FROM
(SELECT Tbl1.keycolumn
FROM Tbl1 JOIN Tbl2
ON Tbl1.keycolumn = Tbl2.keycolumn) TBL
--
David Portas
SQL Server MVP
--|||Joe Celko wrote:
>>>"SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
> causes an ambiguous column error on KeyColumn <<
> And the USING clause is limited to equi-joins. But the real problem is
> that good SQL programmers do not use "SELECT *" in production code. It
> changes at run time and is too unclear and dangerous.
It's not dangerous if your client code accesses the return fields by
name, and not by number. It is, however, generally returning more
data than you need, so it's a waster of resources, and you still
shouldn't do it.
Bill
Hi,
I have two Windows machines in the Local Area Network. One of the machines has MSSQL Enterprise Server 8.0 installed whereas the other has MSSQL Server 2005 installed. When I try to establish the connectivity between these two servers, I can not do so. When I try to look up Network servers, the two machines can't see each other. How can I make them talk to each other? Is there some type of driver or some such thing to be installed which can help the matter?
Please let me know.
Need a bit more information to help you:
Are the instances default or named?
Do you have any firewall software running on either server?
What is the error msg you're getting?
Can you use SQLCMD to connect to the remote server?
Thanks for the response.
Answers to your questions:
1. The MSSQL Server instances are the default ones.
2. I don't think I have a firewall running on the servers.
3. When I go to SQL Server Management Studio and try to register the server by looking on the network, I can not find my other machine with Enterprise Manager 8.0. So I don't get any error, but I can not see the machine either.
4. Login failed for user 'xyz'.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
This is the error that I see on the command prompt.
Here's what I suggest: You may want to go through my answers above, but I think I don't know much about this new SQL version? Can you quickly list a set of steps I can follow to connect from SQL Server 2005 to another machine having SQL Enterprise Manager 8.0 running on it? Thanks in advance.
|||Enterprise Manager has nothing to do with remote connectivity. This is what I understand you're doing:
Machine 1: Running SQL Server 2000, default instance
Machine 2: Running SQL Server 2005, default instance
From Machine 2 running SQL Server Management Studio you want to connect to Machine 1.
First, make sure that Machine 1 is setup to accept remote connections. This is documented in Books Online for SQL Server 2000.
You'll want to make sure you're providing the correct credentials: Windows or SQL Server depending on how the instance is configured.
|||Hi Dan,
Thanks for the response. However, we somehow managed to do the connectivity between the 2 servers. Apparently, the SQL user which we were using to establish the connectivity did not work initially. We dropped and recreated the user and then it went through fine. Don't know why it did not connect initially.
I have another problem though. And I would really appreciate if someone could help with this real quick.
So, there's this MSSQL 2005 installed on a new machine, which has 40 G on C Drive and 250+ G on a D Drive. But all of my C Drive has been taken up by "tempdb.mdf". It has taken up nearly 26 G of space on this drive. Why should that be the case? I know that this is a System database that gets created when one installs MSSQL Server, but I have never seen it this big. Is there a problem with the installation that I can fix? Please let me know.
Thanks.
|||This is certainly suspicious. I'm not expert on tempdb so I recommend you post this issue to http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1. Someone there will certainly be able to help you.