A question in SQL

Hi..
I have a question in SQL..
How can i make SELECT from two or more tables?Select a.col, b.col , a.id from table1 a, table2 b where a.id=b.id

This is one way! I am not sure what kind of query you wanted to run!|||Sreedhar has given you one method...For further information you might want to read up on JOINs in SQL Server's Books OnLine.|||A link to the MSDN library site that talks about the different join methodologies. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_66b7.asp

Personally, I would avoid Sreedhark's suggestion since it's an old join methodology that's becoming obsolete.

A question from a newbie

Can somebody tell me the correct syntax if I want to grant SELECT privileges
on all tables in a db?
I tried:
GRANT SELECT TO public;
Thanks.
VenkatAdd the user to the db_datareader fixed database role.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:A63A543C-1176-4AEF-BC2E-2FBA35D5DB63@.microsoft.com...
> Can somebody tell me the correct syntax if I want to grant SELECT
privileges
> on all tables in a db?
> I tried:
> GRANT SELECT TO public;
> Thanks.
> Venkat|||Geoff:
Thanks a lot. I was reading some of the postings on this forum that
suggested that I have to write a script to cycle through all the tables.
Your's is the most elegant solution.
Venkat
"Geoff N. Hiten" wrote:

> Add the user to the db_datareader fixed database role.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "vvenk" <vvenk@.discussions.microsoft.com> wrote in message
> news:A63A543C-1176-4AEF-BC2E-2FBA35D5DB63@.microsoft.com...
> privileges
>
>|||To add to Geoff's response, the built-in db_datareader and db_datawriter
roles are fine if these meet your security requirements. Granting
permissions to individual users or user-defined roles is appropriate when
you need more granular security and/or stored procedure execute permissions.
Utility scripts can be faster than granting permissions via Enterprise
Manager when you have a lot of objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:2FB6F0F8-D8B8-47B0-8743-DD51C39D4A5E@.microsoft.com...[vbcol=seagreen]
> Geoff:
> Thanks a lot. I was reading some of the postings on this forum that
> suggested that I have to write a script to cycle through all the tables.
> Your's is the most elegant solution.
> Venkat
> "Geoff N. Hiten" wrote:
>

a question for using bcp in stored procedure..

background: sql2k, nt5
is it possible to write a sp to do the following:
within the sp, create a #temp table
bcp a table from a mdb (Access) file to that #temp table directly (assuming
table structure would be the same).
or one has bring the Access table into sql server as a static table first?
would appreicate some sample (or psudo code). thank youYou could create a atored procedure that executes a DTS package via DTSRUN. The DTS package would be setup with the Access DB and specific Access table as the dats source and the SQL table as the destination

A question for the experts regarding Rank sql2000

I am trying to rank a table but cannot get the desired
result.
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
3 Sally 26
4 Tim 23
I am getting the above results from this query
SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points >= U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
I would like to achieve the following result. Is this possible?
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23
Your help would be apreciated.Please provide DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Ross" <andrew.ross@.racingworld.com> wrote in message
news:1124229285.283143.263590@.g43g2000cwa.googlegroups.com...
> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Do:
SELECT t1.alias,
SUM( CASE WHEN t2.points >= t1.points
THEN 1 ELSE 0 END) -
SUM( CASE WHEN t2.points = t1.points
THEN 1 ELSE 0 END) + 1 AS "rank"
FROM Users t1, Users t2
GROUP BY t1.alias
ORDER BY "rank" ;
The idea is to identify the number of ties within the dataset. For an easy
alternative, consider using the following query with subqueries:
SELECT t1.*,
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points >= t1.points) AS "rank",
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points = t1.points ) AS "tied counts"
FROM Users t1 ;
You can manipulate this query making it a derived table construct.
Anith|||Try
SELECT COUNT(U2.Points) + 1 AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points > U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
Regards,
Willson
http://www.wsantoso.net
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Hi Willson
This works great except that it drops the first record (1 Bud
82)
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23|||Oh yeah that, change the inner join to left outer JOIN as it's comparing
against those that has bigger point to get the rank. Seriously this is
something that is a lot more efficient to run on the client side rather than
SQL as noted by many other people in your previous thread.
"Ross" wrote:

> Hi Willson
> This works great except that it drops the first record (1 Bud
> 82)
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
>|||Thanks that works fine|||create table t (Name varchar(10), Points int)
go
insert into t
select 'Bud', 82 union all
select 'Bill', 37 union all
select 'Fred', 37 union all
select 'Sally', 26 union all
select 'Tim', 23
go
select a.Name,
a.Points,
(select count(distinct x.points) from t x where x.points >= a.points) [Rank]
from t a
order by rank
Rakesh
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>

a question for setup experts please

hello,
i have sql server 2000 on my local machine (with enterprise manager etc).
i want to host a website (on a dedicated server) which talks to sql server,
but i dont want to buy another license for the server machine that i am
going to host on (too much money!).
my question is:
is there a way around this? ie: is there a free engine or something that i
can install on my webserver which just stores the datafiles so my webpages
will work.
ideally i would like to DTS data to and from the live server, and also use
my local enterprise manager to view/update data on the live server?
is this possible?
thanksDepending on your requirements, you could deploy MSDE.
Not sure what this has to do with alt.photography though...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
> hello,
> i have sql server 2000 on my local machine (with enterprise manager etc).
> i want to host a website (on a dedicated server) which talks to sql
server,
> but i dont want to buy another license for the server machine that i am
> going to host on (too much money!).
> my question is:
> is there a way around this? ie: is there a free engine or something that
i
> can install on my webserver which just stores the datafiles so my webpages
> will work.
> ideally i would like to DTS data to and from the live server, and also use
> my local enterprise manager to view/update data on the live server?
> is this possible?
> thanks
>|||lol, yeah the alt.photography was a bit of a mistake!
Can you tell me more about MSDE please, and whether it will meet my
requirements mentioned below.
Thanks.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> Depending on your requirements, you could deploy MSDE.
> Not sure what this has to do with alt.photography though...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "tracy" <tracyy@.spamfreenyf.com> wrote in message
> news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
etc).
> server,
that
> i
webpages
use
>|||http://www.microsoft.com/sql/techin...eskChooseEd.asp
http://www.microsoft.com/sql/msde/
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:r5aXb.7185$ul3.128@.news-binary.blueyonder.co.uk...
> lol, yeah the alt.photography was a bit of a mistake!
> Can you tell me more about MSDE please, and whether it will meet my
> requirements mentioned below.
> Thanks.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> etc).
am
> that
> webpages
> use
>|||You could also post specific questions to *just*
microsoft.public.sqlserver.msde
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:r5aXb.7185$ul3.128@.news-binary.blueyonder.co.uk...
> lol, yeah the alt.photography was a bit of a mistake!
> Can you tell me more about MSDE please, and whether it will meet my
> requirements mentioned below.
> Thanks.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> etc).
am
> that
> webpages
> use
>|||If you still have questions about licensing, there are some useful links
here:
317428 INF: Where to Find Information About SQL Server Licensing
http://support.microsoft.com/?id=317428
Keep in mind that you can buy a developer edition of SQL Server for $49 to
develop your application. If the SQL Server edition you already have is
either Standard or Enterprise you could move that edition to your web
server and use the developer edition on your client. But you cannot use the
developer edition on your website.
MSDE might be legal for your web server if you have the appropriate
redistribution rights. MSDE also has some limitations on size and number of
concurrently executing queries. Check out the MSDE roadmap on MSDN as well
as the links from the KB above.
http://msdn.microsoft.com/library/d...-us/dnmsde/html
/msderoadmap.asp
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

a question for setup experts please

hello,
i have sql server 2000 on my local machine (with enterprise manager etc).
i want to host a website (on a dedicated server) which talks to sql server,
but i dont want to buy another license for the server machine that i am
going to host on (too much money!).
my question is:
is there a way around this? ie: is there a free engine or something that i
can install on my webserver which just stores the datafiles so my webpages
will work.
ideally i would like to DTS data to and from the live server, and also use
my local enterprise manager to view/update data on the live server?
is this possible?
thanksDepending on your requirements, you could deploy MSDE.
Not sure what this has to do with alt.photography though...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
> hello,
> i have sql server 2000 on my local machine (with enterprise manager etc).
> i want to host a website (on a dedicated server) which talks to sql
server,
> but i dont want to buy another license for the server machine that i am
> going to host on (too much money!).
> my question is:
> is there a way around this? ie: is there a free engine or something that
i
> can install on my webserver which just stores the datafiles so my webpages
> will work.
> ideally i would like to DTS data to and from the live server, and also use
> my local enterprise manager to view/update data on the live server?
> is this possible?
> thanks
>|||lol, yeah the alt.photography was a bit of a mistake!
Can you tell me more about MSDE please, and whether it will meet my
requirements mentioned below.
Thanks.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> Depending on your requirements, you could deploy MSDE.
> Not sure what this has to do with alt.photography though...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "tracy" <tracyy@.spamfreenyf.com> wrote in message
> news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
> > hello,
> >
> > i have sql server 2000 on my local machine (with enterprise manager
etc).
> >
> > i want to host a website (on a dedicated server) which talks to sql
> server,
> > but i dont want to buy another license for the server machine that i am
> > going to host on (too much money!).
> >
> > my question is:
> > is there a way around this? ie: is there a free engine or something
that
> i
> > can install on my webserver which just stores the datafiles so my
webpages
> > will work.
> >
> > ideally i would like to DTS data to and from the live server, and also
use
> > my local enterprise manager to view/update data on the live server?
> >
> > is this possible?
> >
> > thanks
> >
> >
>|||http://www.microsoft.com/sql/techinfo/planning/SQLReskChooseEd.asp
http://www.microsoft.com/sql/msde/
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:r5aXb.7185$ul3.128@.news-binary.blueyonder.co.uk...
> lol, yeah the alt.photography was a bit of a mistake!
> Can you tell me more about MSDE please, and whether it will meet my
> requirements mentioned below.
> Thanks.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> > Depending on your requirements, you could deploy MSDE.
> >
> > Not sure what this has to do with alt.photography though...
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "tracy" <tracyy@.spamfreenyf.com> wrote in message
> > news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
> > > hello,
> > >
> > > i have sql server 2000 on my local machine (with enterprise manager
> etc).
> > >
> > > i want to host a website (on a dedicated server) which talks to sql
> > server,
> > > but i dont want to buy another license for the server machine that i
am
> > > going to host on (too much money!).
> > >
> > > my question is:
> > > is there a way around this? ie: is there a free engine or something
> that
> > i
> > > can install on my webserver which just stores the datafiles so my
> webpages
> > > will work.
> > >
> > > ideally i would like to DTS data to and from the live server, and also
> use
> > > my local enterprise manager to view/update data on the live server?
> > >
> > > is this possible?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You could also post specific questions to *just*
microsoft.public.sqlserver.msde
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:r5aXb.7185$ul3.128@.news-binary.blueyonder.co.uk...
> lol, yeah the alt.photography was a bit of a mistake!
> Can you tell me more about MSDE please, and whether it will meet my
> requirements mentioned below.
> Thanks.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> > Depending on your requirements, you could deploy MSDE.
> >
> > Not sure what this has to do with alt.photography though...
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "tracy" <tracyy@.spamfreenyf.com> wrote in message
> > news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
> > > hello,
> > >
> > > i have sql server 2000 on my local machine (with enterprise manager
> etc).
> > >
> > > i want to host a website (on a dedicated server) which talks to sql
> > server,
> > > but i dont want to buy another license for the server machine that i
am
> > > going to host on (too much money!).
> > >
> > > my question is:
> > > is there a way around this? ie: is there a free engine or something
> that
> > i
> > > can install on my webserver which just stores the datafiles so my
> webpages
> > > will work.
> > >
> > > ideally i would like to DTS data to and from the live server, and also
> use
> > > my local enterprise manager to view/update data on the live server?
> > >
> > > is this possible?
> > >
> > > thanks
> > >
> > >
> >
> >
>

a question for setup experts please

hello,
i have sql server 2000 on my local machine (with enterprise manager etc).
i want to host a website (on a dedicated server) which talks to sql server,
but i dont want to buy another license for the server machine that i am
going to host on (too much money!).
my question is:
is there a way around this? ie: is there a free engine or something that i
can install on my webserver which just stores the datafiles so my webpages
will work.
ideally i would like to DTS data to and from the live server, and also use
my local enterprise manager to view/update data on the live server?
is this possible?
thanksDepending on your requirements, you could deploy MSDE.
Not sure what this has to do with alt.photography though...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
> hello,
> i have sql server 2000 on my local machine (with enterprise manager etc).
> i want to host a website (on a dedicated server) which talks to sql
server,
> but i dont want to buy another license for the server machine that i am
> going to host on (too much money!).
> my question is:
> is there a way around this? ie: is there a free engine or something that
i
> can install on my webserver which just stores the datafiles so my webpages
> will work.
> ideally i would like to DTS data to and from the live server, and also use
> my local enterprise manager to view/update data on the live server?
> is this possible?
> thanks
>|||lol, yeah the alt.photography was a bit of a mistake!
Can you tell me more about MSDE please, and whether it will meet my
requirements mentioned below.
Thanks.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> Depending on your requirements, you could deploy MSDE.
> Not sure what this has to do with alt.photography though...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "tracy" <tracyy@.spamfreenyf.com> wrote in message
> news:GX9Xb.7063$ul3.1002@.news-binary.blueyonder.co.uk...
etc).
> server,
that
> i
webpages
use
>|||http://www.microsoft.com/sql/techin...eskChooseEd.asp
http://www.microsoft.com/sql/msde/
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:r5aXb.7185$ul3.128@.news-binary.blueyonder.co.uk...
> lol, yeah the alt.photography was a bit of a mistake!
> Can you tell me more about MSDE please, and whether it will meet my
> requirements mentioned below.
> Thanks.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> etc).
am
> that
> webpages
> use
>|||You could also post specific questions to *just*
microsoft.public.sqlserver.msde
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"tracy" <tracyy@.spamfreenyf.com> wrote in message
news:r5aXb.7185$ul3.128@.news-binary.blueyonder.co.uk...
> lol, yeah the alt.photography was a bit of a mistake!
> Can you tell me more about MSDE please, and whether it will meet my
> requirements mentioned below.
> Thanks.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:OANb4um8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> etc).
am
> that
> webpages
> use
>