Problem getting information regarding the application connected to

I am using MSDE 2000 ,
I would like to get information from master..sysprocesses table
to see what programs are connected to the database.
I would to verfy how many times a particular program is connected to the
database
Lets say i would like to check the number of times program X is connected to
the
database.
The select statement is like Select * from master..sysprocesses where
program_name ='X'
but unfortunately whenever i connect using programX , the Program_name field
is Empty.
So I am unable to compare against the program name. Is there any other table
from which
i can gather consistent information? Any workaround would also be welcome.
Thanks in advance
Prakash
hi Prakash,
Prakash wrote:
> I am using MSDE 2000 ,
> I would like to get information from master..sysprocesses table
> to see what programs are connected to the database.
> I would to verfy how many times a particular program is connected to
> the database
> Lets say i would like to check the number of times program X is
> connected to the
> database.
> The select statement is like Select * from master..sysprocesses where
> program_name ='X'
> but unfortunately whenever i connect using programX , the
> Program_name field is Empty.
> So I am unable to compare against the program name. Is there any
> other table from which
> i can gather consistent information? Any workaround would also be
> welcome.
> Thanks in advance
> Prakash
ADO automatically provides this kind of information to SQL Server, where
SQL-DMO requires you to explicitely set it like
objServer.ApplicationName = App.EXEName
can you please check your data access model?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Problem encountered with Filterparameter on SQLDataSource for GridView

Hi,

I have a GridView connected to a sqldatasource control. Everything is working great, updates, paging and filtering with one exception. When the user enters in a name like O'Reilly (with a single quote), the page errors. The error returned is:

Syntax error: Missing operand after 'Reilly' operator.

Here is the definition of the sqldatasource:

<asp:SqlDataSource ID="SqlDataSourcePersons" runat="server"
ConnectionString="<%$ ConnectionStrings:database %>"
SelectCommand="SELECT [Id], [FirstName], [LastName],, [PersonTypeId], [WorkerId], [_workerNTId], [Title], [City] FROM [Person]"
FilterExpression="(FirstName like '{0}%') AND (LastName like '{1}%') AND (WorkerId like '{2}%') AND (City like '{3}%')" ProviderName="System.Data.SqlClient">
<FilterParameters>
<asp:ControlParameter ControlID="TextBoxFirstName" Name="FirstName" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxLastName" Name="LastName" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxWorkerId" Name="WorkerId" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxCity" Name="City" DefaultValue="%" PropertyName="Text" Type="String" />
</FilterParameters>
</asp:SqlDataSource>

Any suggestions would be appreciated.

Thank you, Jim

Hey

If a filter expression contains reserved characters, such as a single quotation mark, those characters must be specified using escape characters. For example, the following expression shows how to use an escape character to include an apostrophe in the expression:CompanyName = 'Margie\'s Travel'.

Following link might helpful:

http://www.aspnetresources.com/blog/apostrophe_in_rowfilter.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskfilteringsortingdatausingdataview.asp

Problem converting rows to a string

I try to accomplish the following:

I have two tables which are connected via a third table (N:N
relationship):

Table 1 "Locations"
LocationID (Primary Key)

Table 2 "Specialists"
SpecialistID (Primary Key)
Name (varchar)

Table 3 "SpecialistLocations"
SpecialistID (Foreign Key)
LocationID (Foreign Key)
(both together are the primary key for this table)

Issuing the following command

SELECT
L.LocationID , S.[Name]
FROM
Locations AS L
LEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationID
LEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistID

results in the following table:

LocationID | Name
1Specialist 1
1Specialist 2
2Specialist 3
2Specialist 4
3Specialist 1
4Specialist 4

Now my problem: I would like to have the following output:

LocationID | Names
1Specialist 1, Specialist 2
2Specialist 3, Specialist 4
3Specialist 1
4Specialist 4

...which is grouping by LocationID and concatenating the specialist
names.
Any idea on how to do this?

Thank you very much,
Dennis(dnsstaiger@.gmx.net) writes:
> Now my problem: I would like to have the following output:
> LocationID | Names
> 1 Specialist 1, Specialist 2
> 2 Specialist 3, Specialist 4
> 3 Specialist 1
> 4 Specialist 4
> ...which is grouping by LocationID and concatenating the specialist
> names.
> Any idea on how to do this?

This is one of the rare cases where you need to set up a cursor and
iterate. In SQL 2000 there is no defined way to do this. (There is
a shortcut, but it relies on undefined behaviour, so I don't recommend it.)

In SQL 2005, currently in beta, the story is different. There you
actually have a way to this in a set-based statement, although the
syntax is somewhat bewildering. (It's actually a by-product, of all
the XML stuff they thrown in.)

--
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 typically do things like this in my application code, i.e. while the
locationid is the same, keep tacking values onto the other column's
display in a comma delim format.|||pb648174 (google@.webpaul.net) writes:
> I typically do things like this in my application code, i.e. while the
> locationid is the same, keep tacking values onto the other column's
> display in a comma delim format.

Yes, that is also a very common advice. But people insists on asking
about how doing this in SQL, that I've given up telling them to use
application code. (And sometimes the application is not any more
sophisticated than Query Analyzer.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Can you post the SQL 2005 code that will do this? I've been hoping SQL
2005 would have an aggregate function for strings that would turn it
into a delimited string.|||pb648174 (google@.webpaul.net) writes:
> Can you post the SQL 2005 code that will do this? I've been hoping SQL
> 2005 would have an aggregate function for strings that would turn it
> into a delimited string.

Sure, here it is:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

This gives you an output like:

ALFKI 10643,10692,10702,10835,10952,11011
ANATR 10308,10625,10759,10926
ANTON 10365,10507,10535,10573,10677,10682,10856

Now, I did definitely come with this on my own, but I got it from one
of the SQL Server developers.

The part that produces the comma separated list, is the text() function,
which is activated by the XML PATH('') at the bottom. The real point
of text() is probably not to produce a comma separated list, but it's
possible to do it.

Then then comma-separated list is combined with Customers through
CROSS APPLY. APPLY is another operator I have not fully digested
yet, but you use it when you want to call a table-valued functions
with parameters from other columns in the query; something you can't
do in SQL 2000.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp