Problems creating "such" query

Hi guys,

There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.

Eg. Table A contains 1,2,3 | Table B contains 1,3,5

The similar numbers: 1, 3
The different numbers: 2, 5

My query results need to show 2 & 5 as they are not in both the tables.

Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.

Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )Here are 2. One that uses outer joins and the other uses a combination of union, minus and intersect.

1)
select a.a
from A LEFT OUTER JOIN B on a.a = b.b
where b.b is null
union
select b.b
from A RIGHT OUTER JOIN B on a.a = b.b
where a.a is null;

2)
(union)
minus
(intersection*)

* - difference in other DBMS's|||hey, thanks r123456

i really got what i wan, but that was a trial for me, may i know is it possible for the query to work if there are ten tables?

same thing applies, i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j.

Is it possible? can help me on this? thanks a lot anyway!|||You're talking about an EXTREMELY intensive query there. Is it necessary to do this in SQL?

Essentially you need to ask access to compare A to B, A to C, A to D, A to E, A to F, B to C, B to D, B to E, C to E, C to D, E to D

etc etc

This will be obscene to do in SQL, I would highly recommend scripting a small function to do it for you, depending on how you intend to use the data.|||Your comment ->
"i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j."

I have assumed you wish to return a single set containing all numbers from accross all tables that do not appear in the intersection of all tables.

If this is the case then for more than say 2-3 tables I would personally write a recursive code block. Essentially for a given number of tables any value in table1 that does not appear in table2 can be immediately disregarded as it instantly fails the intersection.

Thus instead of constructing a 10-way join or 10-way union minus 10-way intersection accross all tables, by using a recursive procedure, one is able to compare a continously decreasing set of tuples with each remaining table, providing the majority of numbers do not form the intersection.

Example
----

Table 1 - Table 2 returns a set of 6 matching tuples. Now these 2 tables can be disregarded, and only the 6 resulting tuples need be continued. After comparing to the next table, only 4 rows are returned. As the procedure continues this set decreases in size.|||But can i know how do i go about doing a recursive code block? can i run it using a macro? i'm really new to Access2000, hope u guys will help me. i'm willing to try though.

r123456,
i understand what you are trying to say, basically, compare table 1 & 2 first, then get the different numbers from these two tables, then get the result to compare with other tables am i right?

regards,
wensheng|||As you are using Access not SQL Server I don't know to what extent Access supports these types of queries.

For example a typical example of recursion may be:

List all parts and subparts along with all their prices required to make a specific device. Ie. Device requires 5 main parts and each main part requires 10-20 sub parts etc. This query can not be written in pure SQL hence external code is required, on ORACLE this can be PL/SQL. SQL Server would probably contain a similar concept.

Access however, I could not tell you. If it does not, then the above queries can be modified. The outer join needs to be altered sligtly whereas the (union) / (intersect) can simply be duplicated. Consider the following,

TableA Table B
--- ----
1 1
3 3
5 4

The intersection can be constructed as an INNER JOIN instead (note, intersection compares all columns not just a candidate key, which in this example is suitable), giving the first 2 rows. An obvious next step

would be:
select (a, b)
from a, b
where (a, b) NOT IN (the intersection returned set), this however would return a set of unwanted combinations that remained from the CP.

To resolve this one of the following can be done,
1) Union the tables instead of a cartesian product to eliminate combinations, then where union column NOT IN any of the columns returned by the inner join.
2) Perform an inner join on all tables with the join condition being the collection of 10 NOT IN / NOT EXISTS operators to check their column with any column in the intersection.

Clearly the 2nd option is not an option. Regarding option 1, the (minus and intersection) clauses have essentially been re-written. The question can now be simplified.

Is it faster to use (minus & intersect) as opposed to where NOT IN (INNER JOIN). I would suggest INTERSECT.|||Hi r1234567 again, thanks for replying promptly.. thanks a lot. appreciated. but somehow i tried something strange.. i think this kind of stupid idea can only come from my brain, wanna take a look? just see below Query Statement:

select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN B on [A].[Object ID]=[B].[Object ID]
where [B].[Object ID] is null;

UNION select [B].[Object ID], [B].[Object Description]
from A RIGHT OUTER JOIN B on [A].[Object ID] = [B].[Object ID]
where [A].[Object ID] is null;

UNION select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN C on [A].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;

UNION select [C].[Object ID], [C].[Object Description]
from A RIGHT OUTER JOIN C on [A].[Object ID] = [C].[Object ID]
where [A].[Object ID] is null;

UNION select [B].[Object ID], [B].[Object Description]
from B LEFT OUTER JOIN C on [B].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;

UNION select [C].[Object ID], [C].[Object Description]
from B RIGHT OUTER JOIN C on [B].[Object ID] = [C].[Object ID]
where [B].[Object ID] is null;

Somehow, this gives the result of what i want man! thanks a lot! i really appreciate u guys for helping.. especially r1234567! really.|||That is a valid solution for only three tables, please be cautioned that for each table you add, the number of select statements you will need to include will grow exponentially.|||Hi Teddy,

i know the number of sql statements are going to grow exponentially.
However the few solutions other than sql queries i ain't sure. I'm not a programmer or what, that's why i dun understand.

I'm sorry. I did this Union Queries till the 7th table. when i reach 8th table, there was an error, " Query too complex! "

So i stopped at 7th table. i think that should be enough. = )|||I'm not up to speed on my vb lately as I've been working primarily in delphi lately, but I can outline a basic concept. I would do this using an array and a for loop. Essentially declare an array to store your final value list. Open your first data set with SELECT * FROM A and scroll through the record set to assign values to the array...

Essentially you would need to declare an array, assign the first table to the array, then compare it to the next table. Scroll through each record in the next table, if a match is found in the array, delete the matching field in the array, otherwise add the field to the array from the dataset. Then compare to the next table and do the same.

basically you'd have a for loop that would iterate and either append or remove depending on a match. I hope that makes any sort of sense heh..

Problems calling a stored procedures depending on parameters

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.

I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)
The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).

On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:

ALTERPROCEDURE [dbo].[S_SPROC]
(@.IDint,@.TYPECHAR(10),@.NAMECHAR(20) OUTPUT)
AS

SELECT @.NAME= NAME
FROM SPROCS
WHERE [ID]= @.ID
AND [TYPE]= @.TYPE

Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.
Has anyone done something similar before, or could point me in the right direction to solving this problem.

Thanks
Phil

Since @.NAME is an output parameter, you need to indicate that in your Command object (ParameterDirection.InputOutput or ParameterDirection.Output). That allows the parameter's value to be retrieved after the command has been executed.

Alternatively, you could select the data like you would in a normal data retrieval, and not worry about using an output parameter.

|||

Thanks for your reply Mark, I will try adding the ParameterDirection part.

If I use normal data retrieval how can I select the appropriate stored procedure when I try filling my table adapter from the dataset?

Thanks

|||

I assumed you would be performing an operation to select the stored proc name, then another operation to execute that stored proc.

|||

Yes that is what I am trying to do, but not so sure on how to go about it. Do you have any code examples?

Thanks

|||

hi mate,

Here is a sample

Dim cmd_ObjectpathAsNew SqlCommand("Select * from [" & tabelName &"]", sqlCon)

Dim adapterAsNew SqlDataAdapter(cmd_Objectpath)

Dim resultAsNew DataTable

adapter.Fill(result)

ForEach rowAs DataRowIn result.Rows

////do the process u want

next

|||

The code I have so far is:

Dim IDAs Int32
Dim TypeAsString

PrivateSub SimpleButton1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles SimpleButton1.Click

ID =Me.TextBox1.Text
Type =Me.TextBox2.Text

Me.SPROCSTableAdapter.Fill(Me.DataSet1.SPROCS, ID, Type)

GetSprocName("AUMS_VALID")

Try

'Logic is a seperate VB file containing further code
Logic.run_SQL_fill_dataset(Me.sqlDataAdapter1, DataSet1.GEN_VALID)
Catch exAs Exception

EndTry

EndSub

PrivateFunction GetSprocName(ByVal st1)AsString
' Gets the names for the sprocs so each table can be filled with differant data. Using value 1 for param 1 just to test
Me.SQLCommand_GetSprocName.Parameters(1).Value = 1
Me.SQLCommand_GetSprocName.Parameters(2).Value = st1.ToString()
Logic.run_SQL_command(Me.sqlConnection1,Me.SQLCommand_GetSprocName)
' This is is where the app seems to fail
ReturnMe.SQLCommand_GetSprocName.Parameters(3).Value.ToString()

EndFunction

**** Code in Logic File: *****

'Sub to run SQLcommand, checks the connection and haddles errors

PublicSharedSub run_SQL_command(ByVal sqlcon1As SqlClient.SqlConnection,ByVal sqlcom1As SqlClient.SqlCommand)

Try
If sqlcon1.State <> ConnectionState.ClosedThen' connection check
sqlcon1.Close()
EndIf

If sqlcon1.State = ConnectionState.ClosedThen' connection check

sqlcon1.Open()

EndIf

sqlcom1.ExecuteNonQuery()

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Catch exAs Exception

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Error_box(ex,"Error on Running SQL Command")'Can place more better code here later

MsgBox(sqlcom1.CommandText.ToString)

EndTry

EndSub

PublicSharedSub run_SQL_fill_dataset(ByVal sqladapterAs SqlClient.SqlDataAdapter,ByVal datatableAs Data.DataTable)

Try

datatable.Clear()

sqladapter.Fill(datatable)

Catch exAs Exception

Error_box(ex,"Error on fill on dataset.")

EndTry

|||

Hi,

I'm afraid that there's something wrong in your code. What we can provide is a general process of communicating with a stored procedure from a .NET application.

Let's take the stored procedure you provided as the sample.

ALTER PROCEDURE [dbo].[S_SPROC]
( @.ID int, @.TYPE CHAR(10), @.NAME CHAR(20) OUTPUT )
AS

SELECT @.NAME = NAME
FROM SPROCS
WHERE [ID] = @.ID
AND [TYPE] = @.TYPE

In your procs, there are 2 input parameters and an output parameter. Then in your application, you should following the steps below:

1. Create the connection which links to the database.
a) Dim myconn As New SqlConnection(ConnectionString)

2. Create the SqlCommand object which execute the procs.
Dim sc As New SqlCommand()
sc.CommandType = CommandType.StoredProcedure
sc.CommandText = "YourProcsName"
sc.Connection = myconn

3. Setting your parameters and add them to SqlCommand object.

Dim sp1 As New SqlParameter()
sp1.ParameterName = "Parameter1"
sp1.Value = ""

Dim sp2 As New SqlParameter()
sp2.ParameterName = "Parameter2"
sp2.Value = ""

Dim sp3 As New SqlParameter()
sp3.ParameterName = "Parameter3"
sp3.Size = 10
sp3.Direction = ParameterDirection.Output

sc.Parameters.Add(sp1)
sc.Parameters.Add(sp2)
sc.Parameters.Add(sp3)

4. Open the connection, execute the process, and get the output parameter.

myconn.Open()
sc.ExecuteNonQuery()
myconn.Close()
Dim c As String = sp.Value.ToString()

After all, you can get the output parameter from the variable C.

Besides, this is a WebForm support forum, if you are developing WindowForm application, it would be better for you to go to MSDN forum where you can get more help.

Thanks.

|||

Thanks for your reply - it has been a big help.

Phil

PROBLEM: Grouping tasks: Connections disappear ?

Guys,

anyone seen this before?

I've just finished me lovely big package.. decided to tidy it up and make it look nice...
(when will I ever learn?)

So I selected a few tasks, right clicked and selected "group". Named the group.....

And now all the connectors between the tasks in the group have disappeared.... tried ungrouping.. still not there...

The package still runs fine... the tasks are executed in the right sequence and all that... but the connectors are jut not visible...

If I try to recreate the connector I get an error dialog saying "Cannot create connector. Only one workflow can exist between the same two executeables.". So they must still exist...

It'd be ok to leave it as it is I suppose.. if I didn't now need to modify the precedence constraint on one of the connectors.... and I can't actually select the feckin connector..

Any suggestions? Any way to refresh the display? Have already exited the environment and come back in.. will try rebooting machine now anyway...

PJ

I've seen problems like this before. Sometimes moving the tasks about makes them reappear. If not, you might be stuck.

In my opinion its a bug.

-Jamie

|||

They're back!

Phew..

I started copying and pasting new versions of the tasks... thinking I could delete the old ones and reconnect the new ones...

And after the first copy and paste... the connections reappeared...magic!

Suppose its good to have a note about this somewhere in the forums for anyone else that comes on this problem...

Now I'm off to get this thing into Sourcesafe (which I should have done from the start)

Thanks Jamie,

PJ

Problem writing XML files.

Hi guys,

I have a stored procedure that I will subsequently post below this
message. What the stored procedure does is, it reads some specific
tables in a database and created XML off it.

The problem comes in when the data is bigger than a few hundred/
thousand characters. The data is truncated and the XML file is not
fully made.

Now i have been reading some posts by some people and tried using TEXT/
NTEXT/ IMAGE type to write the files but they give me errors which,
again, I am adding to this email.

STORED PROC:
****************************

CREATE PROCEDURE usrp_sp_makexmlfile
@.str varchar(50),
@.templatefile varchar(255),
@.ReturnValue as image OUT

AS
SET NOCOUNT ON
DECLARE @.strVar as varchar(8000)
/*DECLARE @.ReturnValue as varchar(255)*/

Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
Set @.templatefile = 'c:\template.tpl'

EXEC (@.strVar)

SELECT @.ReturnValue
GO

THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
************************************************** *******

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
the property Size has an invalid size: 0

Also, if i try to add an integer value to the image/text/ntext like
8000 or something less than that, it tells me that the result has to be
discarded because the current process has had some error.

Can someone give me a suggestion on how to resolve this or an example
that would illustrate the solution? Thank you all in advance.

Pi.Hi

You may want to check out http://sqlxml.org/faqs.aspx?faq=29 but there may
be issues with line breaks for sp_makewebtask or look at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp

John

"Pi" <3.something@.gmail.com> wrote in message
news:1126041751.328093.220490@.f14g2000cwb.googlegr oups.com...
> Hi guys,
> I have a stored procedure that I will subsequently post below this
> message. What the stored procedure does is, it reads some specific
> tables in a database and created XML off it.
> The problem comes in when the data is bigger than a few hundred/
> thousand characters. The data is truncated and the XML file is not
> fully made.
> Now i have been reading some posts by some people and tried using TEXT/
> NTEXT/ IMAGE type to write the files but they give me errors which,
> again, I am adding to this email.
> STORED PROC:
> ****************************
> CREATE PROCEDURE usrp_sp_makexmlfile
> @.str varchar(50),
> @.templatefile varchar(255),
> @.ReturnValue as image OUT
> AS
> SET NOCOUNT ON
> DECLARE @.strVar as varchar(8000)
> /*DECLARE @.ReturnValue as varchar(255)*/
> Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
> Set @.templatefile = 'c:\template.tpl'
> EXEC (@.strVar)
> SELECT @.ReturnValue
> GO
>
>
> THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
> ************************************************** *******
> An unhandled exception of type 'System.InvalidOperationException'
> occurred in system.data.dll
> Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
> the property Size has an invalid size: 0
>
> Also, if i try to add an integer value to the image/text/ntext like
> 8000 or something less than that, it tells me that the result has to be
> discarded because the current process has had some error.
>
> Can someone give me a suggestion on how to resolve this or an example
> that would illustrate the solution? Thank you all in advance.
> Pi.|||According to the Sybase ASE manual at :

<http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X
<snip>
Restrictions on text and image columns
text and image columns cannot be used:
*As parameters to stored procedures or as values passed to
these parameters
*As local variables
</snip|||Hi

With Microsoft SQL Server text is a valid datatype for parameter.

John
"ZeldorBlat" <zeldorblat@.gmail.com> wrote in message
news:1126057842.315787.150600@.g14g2000cwa.googlegr oups.com...
> According to the Sybase ASE manual at :
> <http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X>
> <snip>
> Restrictions on text and image columns
> text and image columns cannot be used:
> *As parameters to stored procedures or as values passed to
> these parameters
> *As local variables
> </snip

Problem with Visual Studio 2005 pro + SQLserver 2005 developer

Hello

Im pretty new to development so.... be nice guys.

I have VS2005 pro and SQL Server 2005 developer edition installed. When I open VS, in the server explorer i can connet to databases located on the SQL directory (SQL Server runs perfectly) But when i try to add an sql server to my project( add new item --> SQL database) it gives me an error saying "Connection to SQL server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component....."

HELP! donno what VS wants!

It seems that Visual Studio has inbuilt support only for Express edition of SQL Server 2005.