multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Alessandro" <giumalex@.tiscali.it> wrote in message news:<g8tkc.30640$eR5.16238@.tornado.fastwebnet.it>...
> Hi, here is the problem...
>...
> I can't realize how to write this 'simple' problem... can anyone help me?
> Many thanks,
> Alex

Hi Alex.. to work with SQL , query and paramter look at this site :

Http://www.RealTimeInformatica.it/Stany

Bye Bye ...

ps: anche in Italiano !;)

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Multiple Updates In Store Procedure?

I am a total newbie to SQL Server, but have a good idea about how things operate..

I have a "job" that runs multiple (10) update queries for me in different tables.

Can I create a stored procedure to do these so that I can make one call to them?

If not, how can I call the job to start external to MS SQL?>>Can I create a stored procedure to do these so that I can make one call to them?

ofcourse. if you have all the required parameters you definetely can.

hth

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

Multiple select in a Store problem

Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
StefanoCould you try putting SET NOCOUNT ON at the beginning of the insert?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ste" <ste@.3civettesulcomoinews.it> wrote in message
news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano|||Thank you, that's ok
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ha scritto nel messaggio
news:eOp4aflHFHA.2620@.tk2msftngp13.phx.gbl...
> Could you try putting SET NOCOUNT ON at the beginning of the insert?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ste" <ste@.3civettesulcomoinews.it> wrote in message
> news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi, I have a problem...
> Supposed that in a store procedure, I have two operation for example:
> insert into mytable field1,field2 values ('bob','bobby')
> select max(id) as id_last from mytable
> go
> in this case I have returned a recordset of one record, of last record
> inserted, ok?
> With ODBC Connection this work fine, but if I use SQLOLEDB Connection
> like:
> Provider=SQLOLEDB;Data Source=172.16.1.102;Network
> Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
> return this error:
> ADODB.Recordset error '800a0cc1'
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
> I use SQL2000Server in a machine and ASP on IIS6 in another machine
> Why
> thank you in advance and sorry for my bad english
> Stefano
>
>

Multiple select in a Store problem

Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano
Could you try putting SET NOCOUNT ON at the beginning of the insert?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ste" <ste@.3civettesulcomoinews.it> wrote in message
news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano
|||Thank you, that's ok
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ha scritto nel messaggio
news:eOp4aflHFHA.2620@.tk2msftngp13.phx.gbl...
> Could you try putting SET NOCOUNT ON at the beginning of the insert?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ste" <ste@.3civettesulcomoinews.it> wrote in message
> news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi, I have a problem...
> Supposed that in a store procedure, I have two operation for example:
> insert into mytable field1,field2 values ('bob','bobby')
> select max(id) as id_last from mytable
> go
> in this case I have returned a recordset of one record, of last record
> inserted, ok?
> With ODBC Connection this work fine, but if I use SQLOLEDB Connection
> like:
> Provider=SQLOLEDB;Data Source=172.16.1.102;Network
> Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
> return this error:
> ADODB.Recordset error '800a0cc1'
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
> I use SQL2000Server in a machine and ASP on IIS6 in another machine
> Why
> thank you in advance and sorry for my bad english
> Stefano
>
>