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 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

ADP Parameterized list box stored proecedure /function

I am trying to populate a list box (MS Access - don't ask it is what my boss wants) with a table-valued function or a stored
procedure. The list box is used to navigate among records that populate the
form, so the same query can be used for the form and the list box thereby,
theoretically, reducing calls to the DB. The records are filtered on two
fields, we'll call them type (GUID) and name (nvarchar), whereby the values
are in two different controls on the form.

The form is using a function, fActiveCompanies, and the imput parameter
propety is completed. The form is able to navigate through the records with
no problem.

I am having difficulty create a SMOOTH method of populating the list box.
currently i am using a stored procedure that is called in VB and populates
the list box with a list of values.

I would prefer to not use a list of values and set the row source type to
Table/View/Stored Procedure.

Please point me in the correct direction for populaitng hte list box not
using a list of values... thank you

Access 2003 - Access Data Projects (ADP) - ADO
SQL Server 2000 (production) SQL Server Express 2005 (devlopment)

my current code for populating the list box is below.

Dim str As String: str = ""
Dim lst As String: lst = ""

Dim rds As ADODB.Recordset
Set rds = New ADODB.Recordset

Forms!frmcompanies!lstCompanies.RowSource = lst
str = "EXEC spActiveCompanies @.pIdTypeCompany='" &
Nz(Forms!frmcompanies!cboTblTypeEntity, "%") & "', @.pNameLegal='" &
Nz(Forms!frmcompanies!txtNav, "%") & "'"

rds.Open str, CurrentProject.Connection

Do Until rds.EOF

If lst = "" Then
lst = """" & rds(0) & """;""" & rds(1) & """"
Else
lst = lst & ";""" & rds(0) & """;""" & rds(1) & """"
End If

rds.MoveNext
Loop

Forms!frmcompanies!lstCompanies.RowSource = lst
Forms!frmcompanies!lstCompanies.Requery

rds.Close: Set rds = Nothing

Forms!frmcompanies.Requery

i mis-read one document, the parameters must be the same as the controls...

field = @.ComboBoxControlName

AdomdConnection.Cube() Can''t Find Some Cubes In SQL Server2005

I have established some cubes in SQL Server 2005.

But when I use AdomdConnection.Cube() Function to find these cubes, some of these can't be find.

Can someone tell me what reason may be or what I can do ? Thanks!!!

Program: .NET 2005

Server : SQL Server 2005

OS : Windows 2003

P.S. In these Cubes Can't be found, I delete few cubes and establish them again. Then these cubes can be found.

Hi,

The best way is to use a loop to list out all the cubes and check if all of them occur.

Say,

For each oCube in ODatabases.Cubes

Msgbox(oCube.Name)

Next

If all donot occur it might be an issue related to security. Check you have Database wide Privilege.

Thanks

Subhash Subramanyam

|||

Hi~

I use your way and list all the cubes, but some cubes still can not be listed.

And I'm sure I have the database wide privilege.

AdomdConnection.Cube() Can''t Find Some Cubes In SQL Server2005

I have established some cubes in SQL Server 2005.

But when I use AdomdConnection.Cube() Function to find these cubes, some of these can't be find.

Can someone tell me what reason may be or what I can do ? Thanks!!!

Program: .NET 2005

Server : SQL Server 2005

OS : Windows 2003

P.S. In these Cubes Can't be found, I delete few cubes and establish them again. Then these cubes can be found.

Hi,

The best way is to use a loop to list out all the cubes and check if all of them occur.

Say,

For each oCube in ODatabases.Cubes

Msgbox(oCube.Name)

Next

If all donot occur it might be an issue related to security. Check you have Database wide Privilege.

Thanks

Subhash Subramanyam

|||

Hi~

I use your way and list all the cubes, but some cubes still can not be listed.

And I'm sure I have the database wide privilege.

AdomdConnection.Cube() Can''t Find Some Cubes In SQL Server2005

I have established some cubes in SQL Server 2005.

But when I use AdomdConnection.Cube() Function to find these cubes, some of these can't be find.

Can someone tell me what reason may be or what I can do ? Thanks!!!

Program: .NET 2005

Server : SQL Server 2005

OS : Windows 2003

P.S. In these Cubes Can't be found, I delete few cubes and establish them again. Then these cubes can be found.

Hi,

The best way is to use a loop to list out all the cubes and check if all of them occur.

Say,

For each oCube in ODatabases.Cubes

Msgbox(oCube.Name)

Next

If all donot occur it might be an issue related to security. Check you have Database wide Privilege.

Thanks

Subhash Subramanyam

|||

Hi~

I use your way and list all the cubes, but some cubes still can not be listed.

And I'm sure I have the database wide privilege.

adodb.connection > DBMS Name property equivalent in ADO.NET ...

Hi,

One of my team member was porting a Visual Basic function into VB.NET. We were struck up while retrieving the DBMS Name property from the connection object. Actually in VB, the ADODB.connection object's properties will have an item called DBMS Name which will hold the database name like "Oracle" , "Access" , "SQL Server" like that based on the database that I connect.

I wonder if there is any equivalent property in ADO.NET. The Server Version property of ODBCConnection object returns "8.0...." in case of SQL Server and returns "09.01.0000 Oracle9i Enterprise Edition Release 9.2.0.1.0" in case of oracle. This is not as precise as it's ADODB counterpart.

I'm hardly in need of a solution or workaround for this scenario. Can anybody help.

Thanks in advance.

Hi Prathap,

The .Net Framework Data Access and Storage forum is the best forum with which to seek your answer (I see that you've already posted this question there).

Il-Sung.

ADO doesnt work without ODBC. Why?

In my application VB, I am effecting the connection with the SQL Server it saw connection string, without ODBC, thus does not function.

I need to create any ODBC connection to complete the connection. Why?

My Connection string:
"provider=SQLOLEDB;UID=Etiquetas;PWD=xxxxxxxx;DATAB ASE=Etiquetas;Server=BROMO;"
or
"driver={SQL SERVER};UID=Etiquetas;PWD=xxxxxxxx;DATABASE=Etique tas;Server=BROMO;"ADO is high level or top layer for OLE DB, it can handle ODBC too cauze Microsoft OLE DB Provider for ODBC drivers

see http://www.able-consulting.com/tech.htm for tons of connection samples...

OLE DB is generally faster than ODBC...

jiri

ADO Case-sensitive Filter

Using ADO 2.7, what is the best way to perform a case-sensitive filter? I have seen on other forums where folks have said that the StrComp function can be used inside of the .Filter method, but I haven't been able to get that to work. I am using VB 6 and ADO 2.7, and have a need to perform case sensitive filters. I know I am not the ony one who has needed to do this...

As always, your time is appreciated.

YOu you mean client based filtering or a server based (used SQL Server in the backend) ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I am attempting to do client sider filtering. The SQL Server installation has been installed to be case insensitive. I want to perform case sensitive filtering on my client side, disconnected recordset.

Your time is appreciated!

Thanks,
Langley111
|||

I thought of that :-) then you better post your question to one of the VB6 forums on the internet as they are more related to the problem than the SQL Server forums.

Jens K. Suessmeyer

http://www.sqlserver2005.de