Adventure Works DB

Hi,
I need Adventure Works Database (OLTP) for SQL Server 2000. Are there any
links available to download it?
Thanks in advance,
LeilaHi, Leila
Did you search for it on http://download.microsoft.com ?
Here it is:
http://www.microsoft.com/downloads/details.aspx?FamilyID=487c9c23-2356-436e-94a8-2bfb66f0abdc&DisplayLang=en
Razvan|||Great :-)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1134899730.713536.16340@.f14g2000cwb.googlegroups.com...
> Hi, Leila
> Did you search for it on http://download.microsoft.com ?
> Here it is:
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=487c9c23-2356-436e-
94a8-2bfb66f0abdc&DisplayLang=en
> Razvan
>sql

Advantages/Disadvantages of SQL 2005 Reporting Vs Crystal Report 1

Hi:
Can someone show me a link where I can get the Advantages/Disadvantages of
SQL 2005 Reporting Vs Crystal Report 10 ?
Thanks in advance.
--
Thanks,
SDRoyTry this:
http://www.crystalreportsbook.com/SSRSandCR_ExecSummary.asp
"SDRoy" wrote:
> Hi:
> Can someone show me a link where I can get the Advantages/Disadvantages of
> SQL 2005 Reporting Vs Crystal Report 10 ?
> Thanks in advance.
> --
> Thanks,
> SDRoy|||I just perused it and two things. One is that it was written prior to SQL
Server 2005 coming out. And, two, the licensing is incomplete. Apples to
Apples I have never heard that Crystal Reports is cheaper. The licensing
costs mentioned are per CPU for SQL Server which you might or might not have
to do. From what I have seen when we looked at Crystal Reports licensing
there is no way that you will get away with the $7,500 license fee that the
author compares to the Enterprise per processor license for SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John G." <John G.@.discussions.microsoft.com> wrote in message
news:9AF11023-0A1E-4CE8-87DC-81B7F583A2D0@.microsoft.com...
> Try this:
> http://www.crystalreportsbook.com/SSRSandCR_ExecSummary.asp
> "SDRoy" wrote:
>> Hi:
>> Can someone show me a link where I can get the Advantages/Disadvantages
>> of
>> SQL 2005 Reporting Vs Crystal Report 10 ?
>> Thanks in advance.
>> --
>> Thanks,
>> SDRoy

Advance Update Statement Sql Server

I am trying to speed up my update statements by removing inner select
statements.

Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)

I read some articles which said that I should be able to use an inner
join on the update statement like the following:

update orders set shipname = (select contactName from customers where
customerid = orders.customerID)

But every time that I run this statement I get the follwing error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

Any Help will be greatly appreciated.

Thank you.I think you posted the wrong UPDATE statement. Both those statements
are identical and valid syntax.

However, if performance is your concern then why not take SHIPNAME out
of the Orders table. It looks like it's redundant there.

--
David Portas
SQL Server MVP
--|||See "Changing Data Using the FROM Clause" and also example C under
UPDATE in Books Online (although it would be better to rewrite it with
INNER JOIN).

Simon|||Both statements were the same ?! I think I know what you meant to say
...

As long as the scalar query expression returns zero or one row, you
will be fine. If you use the proprietary FROM syntax, you will get an
unpredictable result from a multi-row result set.

The real cost of an update is in the physical disk access, not the
code.|||HeadScratcher (mayur@.servicemg.com) writes:
> I am trying to speed up my update statements by removing inner select
> statements.
> Example:
> update orders set shipname = (select contactName from
> customers where customerid = orders.customerID)
> I read some articles which said that I should be able to use an inner
> join on the update statement like the following:
> update orders set shipname = (select contactName from customers where
> customerid = orders.customerID)
> But every time that I run this statement I get the follwing error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'inner'.

Apparently there was some glitch in the editing. Anyway, this is what
you want:

UPDATE Orders
SET ShipName = c.ContactName
FROM Orders o
JOIN Customers c ON c.CustomerID = O.CustomerID

I suspect the problem is that you left out the FROM clause.

I left out INNER here, because this is implied.

I should add your original syntax is in alignment with ANSI standards,
whereas the syntax with FROM JOIN is proprietary to MS SQL Server and
Sybase (and possibly Informix). If you need portability, stick to the
original syntax. As long as you work with SQL Server only, do as you
please. Personally, I find the FROM/JOIN syntax very pleasant, as it
builds on the same paradigm as a regular SELECT statement. It is also
more effecient, if you need to update more than one column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> However, if performance is your concern then why not take SHIPNAME out
> of the Orders table. It looks like it's redundant there.

Nah, I would not recommend people to drop columns from their
Northwind databases. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I find the FROM/JOIN syntax very pleasant, as it builds on the same paradigm as a regular SELECT statement.<<

Arrrgh! The **consistent meaning in the Standard SQL model** of a
FROM clause is that a temporary working table is constructed, used and
dropped at the end of the statement. You would be updating a temporary
working table, not the base table.

The Sybase, Informix and MS SQL Server syntax might look the same, but
the semantics are all slightly different when you get to a 1:m
relationship. Moving the code is deadly -- it moves over to the next
platform and runs differently. With the ANSI syntax, the vendors have
to follow the same rules. This is a good thing.

Advance TSQL question

Hi,
I would like to find customers (same id) with different addresses:
Id Address
-- --
1 123 ABC
2 456 DEF
1 789 GHI
2 456 DEF
3 000 XYZ
As above example: my result would be Id=1.
Thanks in advanced,
Culamselect id, count(id) cnt from (select distinct id, address from tablename)
group by id having count(id) > 1
"culam" wrote:

> Hi,
> I would like to find customers (same id) with different addresses:
> Id Address
> -- --
> 1 123 ABC
> 2 456 DEF
> 1 789 GHI
> 2 456 DEF
> 3 000 XYZ
> As above example: my result would be Id=1.
> Thanks in advanced,
> Culam|||On Fri, 3 Feb 2006 03:34:15 -0800, culam wrote:

>Hi,
>I would like to find customers (same id) with different addresses:
>Id Address
>-- --
>1 123 ABC
>2 456 DEF
>1 789 GHI
>2 456 DEF
>3 000 XYZ
>As above example: my result would be Id=1.
>Thanks in advanced,
>Culam
Hi Culam,
Try:
SELECT Id
FROM YourTable
GROUP BY Id
HAVING MIN(Address) <> MAX(Address)
Hugo Kornelis, SQL Server MVP

advance TSQL programming (with optimization) training

Hi Everyone,
Sorry for this post, but I am not sure where to post this:
I would like to know if anyone has any idea on a good training center
in the Asia-Pacific Region for Advanced TSQL programming (SQL 2000)
that primarily focuses on optimization. The target audience are
Senior Developers who already knows TSQL but requires more
knowledge/training in writing optimized TSQL.
Thanks so much.
AramidAramid
Check out
http://www.solidqualitylearning.com/
"Aramid" <aramid@.hotmail.com> wrote in message
news:fmnnh1l6l5m6ujl4893u1p040ad5k3tk1j@.
4ax.com...
> Hi Everyone,
> Sorry for this post, but I am not sure where to post this:
> I would like to know if anyone has any idea on a good training center
> in the Asia-Pacific Region for Advanced TSQL programming (SQL 2000)
> that primarily focuses on optimization. The target audience are
> Senior Developers who already knows TSQL but requires more
> knowledge/training in writing optimized TSQL.
> Thanks so much.
> Aramid|||Aramid,
if you can get to any TSQL course run by Itzik Ben-Gan it would be well
worth the effort. Itzik was out in Australia a few months ago and is coming
back soon I believe. He out here October the 10th and 17th
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
Check out http://www.solidqualitylearning.com.au/
"Aramid" <aramid@.hotmail.com> wrote in message
news:fmnnh1l6l5m6ujl4893u1p040ad5k3tk1j@.
4ax.com...
> Hi Everyone,
> Sorry for this post, but I am not sure where to post this:
> I would like to know if anyone has any idea on a good training center
> in the Asia-Pacific Region for Advanced TSQL programming (SQL 2000)
> that primarily focuses on optimization. The target audience are
> Senior Developers who already knows TSQL but requires more
> knowledge/training in writing optimized TSQL.
> Thanks so much.
> Aramid|||Thanks Uri and Greg O, that was really helpful! :)
On Mon, 5 Sep 2005 09:26:21 +0300, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Aramid
>Check out
>http://www.solidqualitylearning.com/
>
>"Aramid" <aramid@.hotmail.com> wrote in message
> news:fmnnh1l6l5m6ujl4893u1p040ad5k3tk1j@.
4ax.com...
>

Advance Tab not available (Greyed Out)

Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.

DKB

Hi,

you can not edit data directly from Management Studio, refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178581&SiteID=1&PageID=1

Hemantgiri S. Goswami

|||

This is the process of setting up a grid for a new database being implemented. I expected to be able to select, edit, update or, delete for the grid. Although this has been a couple of days ago I will try to recreate the issue and record my actions and report the issue and sbmit it to msdn and see what come up.

DKB

advance subquery question

i wanT a sql select statement like this
select a,b,c from table 1
where x= alias.x and y=alias.y
(select x,y from table2 ) as alias
dont wanna use cursor
thanksTry this:
select a,b,c
from table1 AS t1
INNER JOIN
(select x,y from table2 ) as alias
ON t1.x= alias.x and t1.y=alias.y
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"joeydj" <joeydj@.discussions.microsoft.com> wrote in message
news:B5D657C6-ACDE-4DDD-BB0E-E23C78E811DE@.microsoft.com...
i wanT a sql select statement like this
select a,b,c from table 1
where x= alias.x and y=alias.y
(select x,y from table2 ) as alias
dont wanna use cursor
thanks|||select
table1.a,
table1.b,
table1.c
from
table1
INNER JOIN
table2 as alias
on table1.x = alias.x
and table1.y = alias.y
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"joeydj" wrote:

> i wanT a sql select statement like this
> select a,b,c from table 1
> where x= alias.x and y=alias.y
> (select x,y from table2 ) as alias
> dont wanna use cursor
> thanks
>|||select table1.a,table1.b,table1.c from table1
join table2
on table1.x = table2.x and table1.y = table2.y
"joeydj" wrote:

> i wanT a sql select statement like this
> select a,b,c from table 1
> where x= alias.x and y=alias.y
> (select x,y from table2 ) as alias
> dont wanna use cursor
> thanks
>

Advance SQL Statement Help

I have all my website access statics logging data into a SQL table with
the following structure:
id int identity
ip nvarchar 23
referer nvarchar 512
request nvarchar 512
website nvarchar 15
bytes int
process_time int
access_time datetime
Each time a page is loaded the values are logged. So if a single user
navigates 20 pages, there are 20 records in the database.
What I want to do is generate a sql statement that will return me all
the accesses to a specific website on a given day, grouped by the ip
address and sorted by the access_time.
Ideally it would return the ip addresses in date order based on their
first entry, with the responses per ip in their date order. Therefore
if
IP 216.113.235.52 had three hits at:
12:15:29
12:15:54
12:16:03
IP 216.113.214.190 had three hits at:
12:15:25
12:15:31
12:15:48
It would return a result set like:
216.113.214.190 @. 12:15:25
216.113.214.190 @. 12:15:31
216.113.214.190 @. 12:15:48
216.113.235.52 @. 12:15:29
216.113.235.52 @. 12:15:54
216.113.235.52 @. 12:16:03
What I'm doing now must not be very efficient as it takes several
seconds to return just a small list of data (roughly 3 seconds to
return 50 or so hits).
Currently I use two queries:
Query 1:
SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
<end_date> AND website LIKE '%<website>%' GROUP BY ip
OR
SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
<= <end_date> AND website LIKE '%<website>%'
Either of these gives me a unique list of ips on the given day
(unfortunately they're not sorted in date order :^( )
Then with this list of unique ips, I perform a second query, looping
through the ip addresses from the first query:
SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
This gives me the users path through the website in date order.
My problems are that:
1. The things just too slow.
2. I don't have a sorted list (the first user of the day may not
necessarily be the first listed).
Is it possible to generate a single query that will return the desired
results in order?
FWIW I'm accessing the database through JDBC.
Thanks in advance.SELECT website, ip, access_time
FROM access_log
WHERE
access_time >= '20060207 00:00:00.000'
AND access_time <= '20060208 00:00:00.000'
ORDER BY website, ip, accesstime ASC
This produces a listing of websites that were access by ip's, ordered by the
access_time. If you added the request column to this query, it would also
show you the path that each ip took through the website.
I've been doing a lot of work with analyzing web access logs lately. Let me
know if this was what you were looking for; if not I'll see what else I can
come up with.
"Tom Cole" wrote:

> I have all my website access statics logging data into a SQL table with
> the following structure:
> id int identity
> ip nvarchar 23
> referer nvarchar 512
> request nvarchar 512
> website nvarchar 15
> bytes int
> process_time int
> access_time datetime
> Each time a page is loaded the values are logged. So if a single user
> navigates 20 pages, there are 20 records in the database.
> What I want to do is generate a sql statement that will return me all
> the accesses to a specific website on a given day, grouped by the ip
> address and sorted by the access_time.
> Ideally it would return the ip addresses in date order based on their
> first entry, with the responses per ip in their date order. Therefore
> if
> IP 216.113.235.52 had three hits at:
> 12:15:29
> 12:15:54
> 12:16:03
> IP 216.113.214.190 had three hits at:
> 12:15:25
> 12:15:31
> 12:15:48
> It would return a result set like:
> 216.113.214.190 @. 12:15:25
> 216.113.214.190 @. 12:15:31
> 216.113.214.190 @. 12:15:48
> 216.113.235.52 @. 12:15:29
> 216.113.235.52 @. 12:15:54
> 216.113.235.52 @. 12:16:03
> What I'm doing now must not be very efficient as it takes several
> seconds to return just a small list of data (roughly 3 seconds to
> return 50 or so hits).
> Currently I use two queries:
> Query 1:
> SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
> <end_date> AND website LIKE '%<website>%' GROUP BY ip
> OR
> SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
> <= <end_date> AND website LIKE '%<website>%'
> Either of these gives me a unique list of ips on the given day
> (unfortunately they're not sorted in date order :^( )
> Then with this list of unique ips, I perform a second query, looping
> through the ip addresses from the first query:
> SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
> This gives me the users path through the website in date order.
> My problems are that:
> 1. The things just too slow.
> 2. I don't have a sorted list (the first user of the day may not
> necessarily be the first listed).
> Is it possible to generate a single query that will return the desired
> results in order?
> FWIW I'm accessing the database through JDBC.
> Thanks in advance.
>

Advance SQL question

Hello everybody,

i have a advance question about a specific sql problem:

My table A have for example 3 columns.
in the third column are words seperated by ~.

ID COL2 COL3
-----
1 ab test~dummy~ddd
2 cd testdata2~sjhfdg~sdf
3 ef sd~test
4 gh sd~cv

Now i want two lists:

1.) used Values for column 3:

Values
--
test
dummy
ddd
testdata2
sjhfdg
sdf
sd
cv

2.) used values plus ID
Value ID
----
test 1
test 3
sd 3
sd 4
cv 4
dummy 1
...

Is it posible to produce such a list with nearly one SQL -Statement or with
temporaly tables ?

Thanks in advance

T.Kindermann
Database Administrator

--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>Thomas Kindermann wrote:
> Is it posible to produce such a list with nearly one SQL -Statement ?

Yes, it is possible:

SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

SELECT ID, substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

This queries work with up to 250 words in each row.

However, it may be better to use other ways. For more informations, see
this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Razvan|||Am 23 Jun 2005 02:39:18 -0700 schrieb Razvan Socol:

> Thomas Kindermann wrote:
>> [1 zitierte Zeile ausgeblendet]
> Yes, it is possible:
> SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> SELECT ID, substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> This queries work with up to 250 words in each row.
> However, it may be better to use other ways. For more informations, see
> this excellent article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
> Razvan

GENIAL SUPER,

you are my good ;-))))))))

Thanks

Thomas
--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>

Advance SQL

Dear Expert,

How can I reuse the column, instead of select the whole things again.
Example as below :

select
column1 as A,
column2 as B,
column3 * A as C
from dummy ;

in MS SQL I have to do like this,

select
column1 as A,
column2 as B,
column3 * column1 as C
from dummy ;

Thanks

DesmondOn 1 Jun 2004 03:56:08 -0700, Desmond wrote:

>Dear Expert,
>How can I reuse the column, instead of select the whole things again.
>Example as below :
>select
> column1 as A,
> column2 as B,
> column3 * A as C
>from dummy ;
>in MS SQL I have to do like this,
>select
> column1 as A,
> column2 as B,
> column3 * column1 as C
>from dummy ;
>
>Thanks
>Desmond

Hi Desmond,

You can use a derived table:

select A, B, column3 * A as C
from (select column1 as A,
column2 as B,
column3
from dummy) AS t

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I reuse the column, instead of selecting the whole thing
again. <<

You can put it into a VIEW or derived table:

SELECT a, b, a*column3 AS c
FROM (SELECT column1, column2, column3
FROM Dummy) AS X(a, b, column3);

But that is not your problem. The real problem is that you do not
understand how SQL -- real SQL -- works.

Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things when they can.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors
are there. The table expression> AS <correlation name> option allows
you give a name to this working table which you then have to use for
the rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The "AS"
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause has been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the
SELECT clause list, and the sorting is done there. The ORDER BY
clause cannot have expression in it, or references to other columns
because the result set has been converted into a sequential file
structure and that is what is being sorted.

As you can see, things happen "all at once" in SQL, not from left to
right as they would in a sequential file/proceudral language model. In
those languages, these two statements produce different results:
**READ (a, b, c) FROM File_X;
**READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.

Advance programmers only

hi ,
I am using ASP.NET with SQL Server. I have a function ABC() which creates,open ,and then dispose sqlserver's connection .
I am using ABC() twice in one .aspx page ,my friend who build this function argued that this function will open only one connection in everypage ,no matter how many times we call function ABC() in a single page ,while i denies .
Please tell me ,because our whole company database acces relies on this single ABC() function.

Thanks in AdvanceYou don't need an advanced programmer for this one. Open() and Close() rely on Connection Pooling, and you'd be surprised how many connections "open" but really share one that's already opened.

If you have a function that's ABC(), you should really have a means of OpenConnection() and CloseConnection() available that calls the Connection.Dispose() method to clear out your memory also.

You should be ok to open/close the connection a few times in an aspx page.|||::while i denies .

Read the documentation :-)

::my friend who build this function argued that this function will open only one connection in
::everypage

Your friend is wrong.

You are wrong.

:-)

ABC opens, uses, then disposes the method.

Now, if you did a decent job with the connection string (a.k.a. as astandard connection string), then basically SQL Connections are pooled. This means when YOU close the SQL Connection, it will NOT be closed, but will go back into the pool, ready for reuse (and being closed a couple of minutes later).

The idea behind this is - rightly - that opening a NEW connection is pretty slow (password authentication, setting up streams etc.). So, when you open / close connections often, keeping it around is much more efficient. Sadly, managing this from an application's point of view is hard (has to be obeyed everywhere etc.), so this went into the system. Connection Pooling was part of the infrastructure for a long time before .NET came around.

So, when this is in place, then basically ABC () may NOT OPEN a connection, but return one already stored. Basically for this the connection string has to be identical :-) Note that this pool is cross page if the connection strings are identical - which is why I sy your friend is wrong. Because the one connection you "opened" further up in the page may be reused right now by another page, resulting in your page openring a second connection and expanding the pool.

NOW - when you have Transactions enabled on the bpage, and the page is thus running under COM# guidance, then things are a little harder. Because COM+ spawns a transaction, a disposed connection can ntot go back into the general pool befor ethe page completes - it thus stays bound to the page (actually to the transaction context the page runs in). So the second call to ABC () would not get A connection back (note the emphasis on "A"), but THE connection - the same connection, as it basically is unused at the moment and still bound to the transactional context.

To read up in the dcumentation:

* For the non COM+ cas: look for "Connection pooling".
* For the COM+ case - well, read the COM+ documentation. Note that ServicedComponent subclasses just implement COM+ for .NET, so you better go back to the original COM+ documentation if you want to get details of inner workings. A third party book is strongly advisable.

::Please tell me ,because our whole company database acces relies on this single ABC()
::function.

Given that the reuse of existing connections is totally transparent and does not change anything on the level of your appplication, could you elaborate how you think this can break your application?

Advance Problem , Just for the expeirinces

Forbidden
You don't have permission to access /localhost:8080/sas/(S(b2vekk55equugyjebolothq0))/Main1.aspx on this server.

what is the solution ?

The issue appears to be that you don't have access to a web page, but you are posting in the wrong forum.

Thanks

Laurentiu

Advance PIVOT function in SQL2005

Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?

I would like to use this for a daily report where the columns would be the dates?

Thanks.

GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorIDThis is not standardized SQL. You may want to ask in a product-specific group|||moving thread to SQL Server forum|||My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?Yes. To have an unknown number of columns\ values you need to use dynamic sql.
http://www.sommarskog.se/dynamic_sql.html

Advance ORDER BY

Hi,

I'm wondering if there's any advance order by 'function'/workaround,
which is reasonably efficient for MS SQL Server, without resorting to
some third party indexing/search engine to achieve the following.

The mechanism is to record each instance of a pattern match and order
by rows with most matches first (DESC). Simplistic match but that's a
separate issue.

Sample:
create table tmp (col varchar(50));
insert into tmp
values ('a barking dog');
insert into tmp
values ('a dog and cat fights over dog food');
insert into tmp
values ('lovable dog is not barking dog=nice dog');

The goal for the Sample is to return resultsets in the following
order:
lovable dog is not barking dog=nice dog -- 3 matches
a dog and cat fights over dog food -- 2 matches
a barking dog -- 1 match

Thanks."Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0309071031.7f6b9826@.posting.google.c om...
> Hi,
> I'm wondering if there's any advance order by 'function'/workaround,
> which is reasonably efficient for MS SQL Server, without resorting to
> some third party indexing/search engine to achieve the following.
> The mechanism is to record each instance of a pattern match and order
> by rows with most matches first (DESC). Simplistic match but that's a
> separate issue.
> Sample:
> create table tmp (col varchar(50));
> insert into tmp
> values ('a barking dog');
> insert into tmp
> values ('a dog and cat fights over dog food');
> insert into tmp
> values ('lovable dog is not barking dog=nice dog');
> The goal for the Sample is to return resultsets in the following
> order:
> lovable dog is not barking dog=nice dog -- 3 matches
> a dog and cat fights over dog food -- 2 matches
> a barking dog -- 1 match
> Thanks.

Here's one possibility:

select col
from tmp
order by len(replace(col, 'dog', '')) desc

Simon|||A slight correction on Simon's solution (which will return the longest
string, regardless of number of matches)

select col
from tmp
order by len(col) - len(replace(col, 'dog', '')) desc

Gert-Jan

Doug Baroter wrote:
> Hi,
> I'm wondering if there's any advance order by 'function'/workaround,
> which is reasonably efficient for MS SQL Server, without resorting to
> some third party indexing/search engine to achieve the following.
> The mechanism is to record each instance of a pattern match and order
> by rows with most matches first (DESC). Simplistic match but that's a
> separate issue.
> Sample:
> create table tmp (col varchar(50));
> insert into tmp
> values ('a barking dog');
> insert into tmp
> values ('a dog and cat fights over dog food');
> insert into tmp
> values ('lovable dog is not barking dog=nice dog');
> The goal for the Sample is to return resultsets in the following
> order:
> lovable dog is not barking dog=nice dog -- 3 matches
> a dog and cat fights over dog food -- 2 matches
> a barking dog -- 1 match
> Thanks.|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f5b7da7$1_3@.news.bluewin.ch...
> "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> news:fc254714.0309071031.7f6b9826@.posting.google.c om...
> > Hi,
> > I'm wondering if there's any advance order by 'function'/workaround,
> > which is reasonably efficient for MS SQL Server, without resorting to
> > some third party indexing/search engine to achieve the following.
> > The mechanism is to record each instance of a pattern match and order
> > by rows with most matches first (DESC). Simplistic match but that's a
> > separate issue.
> > Sample:
> > create table tmp (col varchar(50));
> > insert into tmp
> > values ('a barking dog');
> > insert into tmp
> > values ('a dog and cat fights over dog food');
> > insert into tmp
> > values ('lovable dog is not barking dog=nice dog');
> > The goal for the Sample is to return resultsets in the following
> > order:
> > lovable dog is not barking dog=nice dog -- 3 matches
> > a dog and cat fights over dog food -- 2 matches
> > a barking dog -- 1 match
> > Thanks.
> Here's one possibility:
> select col
> from tmp
> order by len(replace(col, 'dog', '')) desc
> Simon

Sorry - I posted that a bit too quickly. It should be this - the division by
3 is because your search term has 3 characters, so you can count the number
of replacements made this way:

select col
from tmp
order by (len(col) - len(replace(col, 'dog', ''))) / 3 desc

Simon|||Thank you. Gert-Jan's solution also works.
Can you explain why?

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3f5b822c_4@.news.bluewin.ch>...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:3f5b7da7$1_3@.news.bluewin.ch...
> > "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> > news:fc254714.0309071031.7f6b9826@.posting.google.c om...
> > > Hi,
> > > > I'm wondering if there's any advance order by 'function'/workaround,
> > > which is reasonably efficient for MS SQL Server, without resorting to
> > > some third party indexing/search engine to achieve the following.
> > > > The mechanism is to record each instance of a pattern match and order
> > > by rows with most matches first (DESC). Simplistic match but that's a
> > > separate issue.
> > > > Sample:
> > > create table tmp (col varchar(50));
> > > insert into tmp
> > > values ('a barking dog');
> > > insert into tmp
> > > values ('a dog and cat fights over dog food');
> > > insert into tmp
> > > values ('lovable dog is not barking dog=nice dog');
> > > > The goal for the Sample is to return resultsets in the following
> > > order:
> > > lovable dog is not barking dog=nice dog -- 3 matches
> > > a dog and cat fights over dog food -- 2 matches
> > > a barking dog -- 1 match
> > > > Thanks.
> > Here's one possibility:
> > select col
> > from tmp
> > order by len(replace(col, 'dog', '')) desc
> > Simon
> Sorry - I posted that a bit too quickly. It should be this - the division by
> 3 is because your search term has 3 characters, so you can count the number
> of replacements made this way:
> select col
> from tmp
> order by (len(col) - len(replace(col, 'dog', ''))) / 3 desc
> Simon|||>> Thank you. Gert-Jan's solution also works. Can you explain why? <<

Take the expression used in the ORDER BY clause and add it in the SELECT
list. The answer then becomes obvious.

--
- Anith
( Please reply to newsgroups only )|||Of course it does :-)

It works because for the ORDER BY clause you do not need the actual
number of occurrences. You just need them sorted. In that respect "1
barking", "2 dog and cat", "3 lovable dog" is the same as "3 barking",
"6 dog and cat", "9 lovable dog".

Gert-Jan

Doug Baroter wrote:
> Thank you. Gert-Jan's solution also works.
> Can you explain why?
> "Simon Hayes" <sql@.hayes.ch> wrote in message news:<3f5b822c_4@.news.bluewin.ch>...
> > "Simon Hayes" <sql@.hayes.ch> wrote in message
> > news:3f5b7da7$1_3@.news.bluewin.ch...
> > > > "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> > > news:fc254714.0309071031.7f6b9826@.posting.google.c om...
> > > > Hi,
> > > > > > I'm wondering if there's any advance order by 'function'/workaround,
> > > > which is reasonably efficient for MS SQL Server, without resorting to
> > > > some third party indexing/search engine to achieve the following.
> > > > > > The mechanism is to record each instance of a pattern match and order
> > > > by rows with most matches first (DESC). Simplistic match but that's a
> > > > separate issue.
> > > > > > Sample:
> > > > create table tmp (col varchar(50));
> > > > insert into tmp
> > > > values ('a barking dog');
> > > > insert into tmp
> > > > values ('a dog and cat fights over dog food');
> > > > insert into tmp
> > > > values ('lovable dog is not barking dog=nice dog');
> > > > > > The goal for the Sample is to return resultsets in the following
> > > > order:
> > > > lovable dog is not barking dog=nice dog -- 3 matches
> > > > a dog and cat fights over dog food -- 2 matches
> > > > a barking dog -- 1 match
> > > > > > Thanks.
> > > > Here's one possibility:
> > > > select col
> > > from tmp
> > > order by len(replace(col, 'dog', '')) desc
> > > > Simon
> > > > Sorry - I posted that a bit too quickly. It should be this - the division by
> > 3 is because your search term has 3 characters, so you can count the number
> > of replacements made this way:
> > select col
> > from tmp
> > order by (len(col) - len(replace(col, 'dog', ''))) / 3 desc
> > Simon