Retrieving tokenized params

I am implementing a multivariable search on a couple of tables in a DB. In
the front end the client requires that one of the criteria
of the search supports multiple choices (e.g. in a ListBox control). To my
knowledge, the only way to satisfy this requirement is to pass the back end
a string where the selected values are separated by some delimiter, e.g. in
the mock SP below. My qst: 1) is it logically possible to achieve
what the client wants in such a SP and if so, 2) how to pass every token
from @.param_array to the last AND condition. Any other suggestions/examples
to solve the problem are welcome.
TIA
CREATE PROCEDURE dbo.usp_MultivarSearch
(
@.param_1 <type> ,
@.param_2 <type> ,
..
@.param_n <type> ,
@.param_array varchar(256) -- e.g. 'val1|val2|val3|...|valn'
)
as
select <fields>
from <joined tables>
where <some_field> = @.param_1
and <some_other_field> = @.param_2
and ( <field_in_question> = val1 or <field_in_question> = val2
or ... <field_in_question> = valn )
GOSee the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan|||You can use the charindex() function for this.
"alto" <altodorov@.hotmail.com> wrote in message
news:OhdIKFfvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I am implementing a multivariable search on a couple of tables in a DB. In
>the front end the client requires that one of the criteria
> of the search supports multiple choices (e.g. in a ListBox control). To my
> knowledge, the only way to satisfy this requirement is to pass the back
> end
> a string where the selected values are separated by some delimiter, e.g.
> in the mock SP below. My qst: 1) is it logically possible to achieve
> what the client wants in such a SP and if so, 2) how to pass every token
> from @.param_array to the last AND condition. Any other
> suggestions/examples
> to solve the problem are welcome.
> TIA
> CREATE PROCEDURE dbo.usp_MultivarSearch
> (
> @.param_1 <type> ,
> @.param_2 <type> ,
> ...
> @.param_n <type> ,
> @.param_array varchar(256) -- e.g. 'val1|val2|val3|...|valn'
> )
> as
> select <fields>
> from <joined tables>
> where <some_field> = @.param_1
> and <some_other_field> = @.param_2
> and ( <field_in_question> = val1 or <field_in_question> = val2
> or ... <field_in_question> = valn )
> GO
>
>

Retrieving IP address of current client's connection

MS SQL 2000:

I can retrieve "host" name of the current connection from "sysprocesses" system table(select host from sysprocesses). But is it possible to get the IP address of the client connection instead of host?

I could not find any stored procedures or extended procedures that would let me retrieve such information.

thank you in advance.

Hi,

there is no such call AFAIK. But you can use the follwing procedure to evaluate your IPAdress:

CREATE PROCEDURE getIPAdress

(

@.Hostname VARCHAR(255)

)

AS

SET NOCOUNT ON

CREATE TABLE #Results

(

Results VARCHAR(4000)

)

DECLARE @.Commandstring VARCHAR(300)

SET @.Commandstring = 'ping ' + @.Hostname

INSERT INTO #Results

EXEC master..xp_cmdshell @.Commandstring

Select DISTINCT SUBSTRING(Results,12,CHARINDEX(':',Results)-12) AS HostIpAdress from #Results

Where Results LIKE 'Reply From%'

DROP TABLE #Results

GO

In SQL Server 2005, you can sure use a more sophisticated method of .NET.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you for the response.
I had tried the method and it works.
But I do not think I should be trying to retrieve IP address for now just because of the fact that I have to run "xp_cmdshell".
|||

IN 2005 you can try:

select * from sys.dm_exec_connections

This will give you the IP Address of any TCP/IP connections.

Louis

|||

Thank you for also giving the solution for SQL Server 2005.

I am downloading and about to give SQL Server Express 2005 a try just for that functionality.

Great opportunity to move toward SQL Server 2005 if it works out :)

*** EDIT ***

I have installed SQL Server 2005 and tried out the query and it worked as I wanted. :)

Retrieving Data Whilst Client Side

Hi

I know this is a nasty way of doing but I can't think of another way. I'm retrieving an ID from a treeview when it's clicked client side, and I want to pass this to a SQL Procedure and retrieve some other data relating to it and fill a list box. Can this be done?

Thanks
LbobWith the Treeview, you can just have it post back to the server, get the value selected, use it to query your database, and fill the drop down. Yes, it can be done.

Using the "MSXML2.XMLHTTP" object from javascript, you can get the data without doing a postback, but it's a little more complex.|||It's best not to do that though. It can become a really difficult thing to handle. Security would be one thing, but just the insane amount of keystrokes.

Retrieving Data in XML Format

Hi,
I use nested loop with inner joins to retrieve organization's chart from
EmployeeID-ReportsTo columns.
Our client uses XML and I must send the chart in XML format. How can I
perform this?
Any help would be greatly appreciated.
Leila
Did you look at the details about FOR XML clause in SQL Server Books Online?
Anith
|||Thanks Anith,
But I couldn't use FOR XML for my problem. When I query the sub-employees of
a particular employee, I must keep that in XML format. Then each
sub-employee may have sub-employees itself that should be inserted beneath
the related element.
Maybe I'm using wrong algorithm?
Please help me..
Thanks in advance..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> Did you look at the details about FOR XML clause in SQL Server Books
Online?
> --
> Anith
>
|||You will need to do this in your client tool. I suggest you post to a
newsgroup that supports whatever client application you are using.
Bob Barrows
Leila wrote:[vbcol=seagreen]
> Thanks Anith,
> But I couldn't use FOR XML for my problem. When I query the
> sub-employees of a particular employee, I must keep that in XML
> format. Then each sub-employee may have sub-employees itself that
> should be inserted beneath the related element.
> Maybe I'm using wrong algorithm?
> Please help me..
> Thanks in advance..
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Retrieving Data in XML Format

Hi,
I use nested loop with inner joins to retrieve organization's chart from
EmployeeID-ReportsTo columns.
Our client uses XML and I must send the chart in XML format. How can I
perform this?
Any help would be greatly appreciated.
LeilaDid you look at the details about FOR XML clause in SQL Server Books Online?
--
Anith|||Thanks Anith,
But I couldn't use FOR XML for my problem. When I query the sub-employees of
a particular employee, I must keep that in XML format. Then each
sub-employee may have sub-employees itself that should be inserted beneath
the related element.
Maybe I'm using wrong algorithm?
Please help me..
Thanks in advance..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> Did you look at the details about FOR XML clause in SQL Server Books
Online?
> --
> Anith
>|||You will need to do this in your client tool. I suggest you post to a
newsgroup that supports whatever client application you are using.
Bob Barrows
Leila wrote:
> Thanks Anith,
> But I couldn't use FOR XML for my problem. When I query the
> sub-employees of a particular employee, I must keep that in XML
> format. Then each sub-employee may have sub-employees itself that
> should be inserted beneath the related element.
> Maybe I'm using wrong algorithm?
> Please help me..
> Thanks in advance..
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
>> Did you look at the details about FOR XML clause in SQL Server Books
>> Online?
>> --
>> Anith
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

retrieving backup

Hi All

Bit of a situation so hopefully someone can advise.

I've a client who has a sql 2000 database that *unfortunately* hasn't
had a backup procedure in place. Its been running for just over a year
and on Monday it got, well, screwed. They have a 50 gig ldf file.
Yes, 50 gig. Is it possible to put in place a database maintenance
plan that will allow me to roll back to, say, last friday just from the
ldf file?

Thoughts? Any advice would be welcome.

MTIA

Mark<mark_drewersback@.yahoo.co.uk> wrote in message
news:1117042116.016174.212890@.g49g2000cwa.googlegr oups.com...
> Hi All
> Bit of a situation so hopefully someone can advise.
> I've a client who has a sql 2000 database that *unfortunately* hasn't
> had a backup procedure in place. Its been running for just over a year
> and on Monday it got, well, screwed. They have a 50 gig ldf file.
> Yes, 50 gig. Is it possible to put in place a database maintenance
> plan that will allow me to roll back to, say, last friday just from the
> ldf file?
> Thoughts? Any advice would be welcome.
> MTIA
> Mark

Most of the transactions in the log have presumably already been committed
to the database, so you could try sp_attach_db and/or
sp_attach_single_file_db, but there's no guarantee they will work (and you
don't say exactly what "screwed" means, so you might not even have the .mdf
anyway).

Apart from that, your most best option is probably to call Microsoft and see
what they can do - there's no other way to recover data directly from
unattached mdf/ldf files. If there's a year's worth of business data in
there, then the cost of getting Microsoft involved is most likely negligible
compared to the value of the data.

Simon|||By screwed, I mean, the app that is running from it is now reporting
duplicate primary key errors. We have the mdf file. What I'd like to
know is if we succesfully restored it using the mdf/ldf file then would
it be possible to roll back to the previous Friday, when it was running
fine, given that no back ups have taken place?

Thoughts?|||(mark_drewersback@.yahoo.co.uk) writes:
> By screwed, I mean, the app that is running from it is now reporting
> duplicate primary key errors. We have the mdf file. What I'd like to
> know is if we succesfully restored it using the mdf/ldf file then would
> it be possible to roll back to the previous Friday, when it was running
> fine, given that no back ups have taken place?

If there had been been a one-year old backup, and an unbroken transaction
log since then, yes.

If there is no backup at all, I don't think you can do it with the commands
that ships with SQL Server.

However, there are 3rd party tools out there that can read the transaction
log, and generate commands to undo transactions in the transaction log.
Lumigent Log Explorer (www.lumigent.com) has been in business for a long
time. Log Pi (www.logpi.com) is another alternative.

I would suggest that the best to examine more close why you get PK
duplicates.

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

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

Retrieving a distal query plan

Hi!
I have a consultant onsite at a client, and she's sending back
pictures of the query plan for a script with a performance issue. These
are useful, as they're turning out somewhat different from the
development and test machines. But, is there any way we can pass the
query plan so that I can look at it in Query Analyzer's fine query plan
renderer?
Thanks!
IonIon,
SQL Server 2005 has this feature, but not SQL Server 2000.
In SQL Server 2005 Management Studio, you can save and
reopen query plans saved as .sqlplan files (which are XML files).
There might be a third-party tool that can render the graphical plan
from the SHOWPLAN_TEXT text plan - at least it's conceivable -
but I don't know of one.
Steve Kass
Drew University
ionFreeman@.gmail.com wrote:

>Hi!
> I have a consultant onsite at a client, and she's sending back
>pictures of the query plan for a script with a performance issue. These
>are useful, as they're turning out somewhat different from the
>development and test machines. But, is there any way we can pass the
>query plan so that I can look at it in Query Analyzer's fine query plan
>renderer?
>Thanks!
>Ion
>
>|||I think I heard that you can use SQL Server 2005 Management Studio
against a 2000 database, and that it will allow you to save the plan
and send it as with any 2005 database.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Apr 2006 19:51:02 -0400, Steve Kass <skass@.drew.edu> wrote:
>Ion,
>SQL Server 2005 has this feature, but not SQL Server 2000.
>In SQL Server 2005 Management Studio, you can save and
>reopen query plans saved as .sqlplan files (which are XML files).
>There might be a third-party tool that can render the graphical plan
>from the SHOWPLAN_TEXT text plan - at least it's conceivable -
>but I don't know of one.
>Steve Kass
>Drew University
>
>ionFreeman@.gmail.com wrote:
>|||Thanks, guys. I'll see where this brings me.