MS SQL(2000) table export to excel

I′m looking for a script (sql transaction, or function) to export MS
SQL(2000) Table to formated excel sheet (cells width, text format, ...).how about using the DTS
to export that to excel
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Milo?,
You also can use BCP command. See Books Online.
Regards,
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Thank You Jose,
DTS export ist very easy, for my situation is the best, but it has litlle
problem with text encoding.
Milo?
?Jose G. de Jesus Jr MCP, MCDBA" nap_sal (nap_sala):
> how about using the DTS
> to export that to excel
>
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Milo?" wrote:
>sql

MS SQL Script Optimisation

Still a bit of a newbie to SQL, I've written a solution to a problem but I'm sure there is a neater fix.

I have two tables,

Table 1 (sales) which indexes product_id, customers and the date when the customer bought the product.
contains the columns Prod_Id (int), Customers (char), DateNo (int)

Table 2 (products) contains a list of all products in the system
contains the columns Prod_Id(int), ProductDescription(char) and Price (int)

I want to display a list to the top 10 most popular products.
Listing their description, price and the number of times they have been purchased in 2006. Does not matter who bought them.

My current approach is:
----------------------

CREATE TABLE #temptable
(
Prod_IdINTNOT NULL,
CounterINTNOT NULL
)

INSERT INTO #temptable
SELECT TOP 10 WITH TIES Prod_Id, Count(*)
FROM Sales
WHERE DateNo >= '20060101'
AND DateNo < '20060516'
GROUP BY Prod_Id
ORDER BY COUNT(*) DESC;

SELECT t.Counter, p.ProductDescription, p.Price
FROM Products AS p, #temptable AS t
WHERE t.Prod_Id = p.Prod_Id;

DROP TABLE #temptable

---------------

Any help would be appriciated.
Thank you.I havent tested this, as I'm lasy, but it should give the same result but a bit faster as its not creating and dropping tables

SELECT TOP 10 WITH TIES p.ProductDescription, p.Price ,Count(s.Prod_Id)
FROM Sales as s
INNER JOIN Products AS p
ON s.Prod_Id = p.Prod_Id
WHERE s.DateNo >= '20060101'
AND s.DateNo < '20060516'
GROUP BY p.ProductDescription, p.Price
ORDER BY Count(s.Prod_Id) DESC

Hope it works

MS SQL Port

How can I determine which port MS SQL is running.
I known that this can be set at SQL Server Network Utility, but I
wonder why my ASP.NET script can connect to SQL Server even I change
its running port.

Another thing I want to know... actually it doesn't concern with SQL
that is,
how can I watch all the enviroment variable in Windows XP, such as
%systemroot% and how can I set that one"=aKe=" <darkgod1987@.hotmail.com> wrote in message
news:36e545c4.0404140338.6b6e5440@.posting.google.c om...
> How can I determine which port MS SQL is running.
> I known that this can be set at SQL Server Network Utility, but I
> wonder why my ASP.NET script can connect to SQL Server even I change
> its running port.
> Another thing I want to know... actually it doesn't concern with SQL
> that is,
> how can I watch all the enviroment variable in Windows XP, such as
> %systemroot% and how can I set that one

You can look in the SQL Server Log for the port, in the registry, or use the
SQLDMO Registry2 object's TcpPort property.

SQL Server listens on two ports - 1433 and 1434 (by default). A client can
connect to port 1434 to get connection information about all the instances
on the server, inclluding their ports. See "Controlling Net-Libraries and
Communications Addresses" in Books Online.

As for environment variables, you can use SET in the Windows shell, or use
an interface such as WMI which returns all the current variables. WSH has a
built-in object for this, I believe, perhaps the Network object but I'm not
entirely sure.

Simon