|
you put dummy data in table then check the query will work fine...
|
|
|
|
|
suppose i have a table
Color Shape
Red Cirle
Red Square
Yellow Square
and i want 2 records of (Red, Circle) and (Yellow, Square)
your command will return me 3 records out of which i dont want middle one.
Thank for taking interest
Shajeel
|
|
|
|
|
you can simply use:
<br />
select * from table <br />
where col1 IN('val1' , 'val2') and col2 IN ('val3' , 'val4')<br />
it will work...
Javed
|
|
|
|
|
Now i have an Xml like this
11<Root>
11 <Element>
11 <Table1 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table2 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table3 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table1 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table2 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table3 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table1 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table2 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table3 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table1 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table2 Value as XML attributes/>
11 </Element>
11 <Element>
11 <Table3 Value as XML attributes/>
11 </Element>
11</Root>
The above xml having 3 different table values in 4 set of nodes to update 3 tables by using open XML
How will u achieve this target
The above XML nodes are stable in all times as 3 elements
//////
Is it possible to change as 4 elements and update to 4 tables at run time
This second scenario how will the stored procedure will update the 4th table
The stored procedure is created for first scenario to update 3 tables
Can i possible to achieve my second target by the same procedure
Tell me the suggestions
thanx
-
|
|
|
|
|
Can u guide me to do a bulk update into more than one table from getting a single xml ine the form of attribute centric and element centric mapping, Tell the procedure to create the stored procedure itself, now i am having XML as a string to update a table
Reply with Source Pls
Thanks
-
|
|
|
|
|
I have one stored procedure, and it having UPDATE and INSERT statements, The INSERT / UPDATE queries are inside a Begin transaction, if the transaction fails then rollback the transaction otherwise commit it.
If the error occur rollback the transaction as well as i want to catch the exception and throwuh it to the user in the DA Class
I am calling the stored peocedure in the DA Class
please tell me any suggestions
Awaiting for reply
thanks
-
|
|
|
|
|
What was wrong with the previous answers[^] you received. If you are looking for more information then what information do you need? If the question was misinterpreted then repeating the exact same request will not assist those who are trying to help.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi,
i have 2 tables. Both have the same structure.
i want queries which compares
1) each and every row of the first table to the corresponding row in the 2nd table. Here, it should retrive the row which is not matching in both the tables. the comparison should be check each and every column in both the tables
2)the rows that are present in first table and not in 2nd table and VICE-VERSA
Please reply ASAP
Vishal
-- modified at 2:27 Wednesday 17th May, 2006
|
|
|
|
|
This will return all rows in Table1 that are different or missing in Table2
SELECT *
FROM Table1 AS t1
LEFT OUTER JOIN Table2 AS t2
ON t1.PrimaryKey = t2.PrimaryKey AND t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2....
WHERE t2.PrimaryKey IS NULL To get all the rows in Table2 that are different or missing in Table 1 replace the LEFT OUTER JOIN with a RIGHT OUTER JOIN and change the WHERE clause to test t1
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
This is certainly helpful. Thanks a lot 
Now, is there any way we can display additional information for "actually which columns the data is not matching" ?
Vishal
|
|
|
|
|
Hi Vishal!
Take a look at my blog "Compare the content of two tables" at http://www.cubido.at/Blog/tabid/176/EntryID/77/Default.aspx. It describes how this can be done with a tool that comes with SQL Server 2005.
Regards,
Rainer.
Rainer Stropek
cubido business solutions gmbh
Email r.stropek@cubido.at
Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx
|
|
|
|
|
Hi,
I have one table that table contains n-rows
now i want to get one column in each row ,how can i achieve this
|
|
|
|
|
SELECT ColumnName FROM Table
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi,
Please help me by providing some information/code snippet on the following
1)How I can copy/retrieve the data which is stored as image type in sql table.T
he contained data may not be an image it may be a word file also.
Using sql queries how I can get back this data.Please show me the right way.
Thanks very much.
Thank You,
Rahul.P.Menon.
|
|
|
|
|
Hello, I am trying to develop a program that uses data from a DBMS. But the problem is that I don't know which one(DBMS) I should use. The program is going to handle a couple of tables with a few thousands of records each. I have in mind MS-ACCESS and MySql but recently I discovered FIREBIRD which is released under the GPL licence(completely free to distribute with my software or at least this is what I understood).I am new to theese matters so please I need your advise...
|
|
|
|
|
Have you considered SQL Express, it is also free to distribute and will more than be able to handle the workload you are describing. It also has a GUI admin console and fully integrates into all versions (even the free express ones) of Visual Studio. As far as Firebird, never used it so can't say.
Al
|
|
|
|
|
For a few thousand rows and freely distributable you may want to consider the size of the extra files you are distrubuting and how easy it is to have your installation file connect everything up on installation.
* SQL Server 2005 Express Edidtion is quite bulky, so you may not want that.
* SQLITE might also be appropriate for you - It is public domain and I think there are only one to two DLLs that you need to add to you to your install.
What I'd do is draw up a matrix containing all the database products you are looking at across the top and all the features you are interested in down the side. For example:
DB -> SqlServer MySQL Firebird SQLITE
/Feature Express
Redist.. Y ? ? Y
Install
Size Large ? ? 1-2 DLLs
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 3:58 Wednesday 17th May, 2006
|
|
|
|
|
Hi, folks!
I hope I can explain this properly...
I have two tables. One is a list of people's first and last names and there is an Identity column to uniquely select the proper row.
The other table is a list of email forwards; in other words I want to keep track of how a particular email is passed about with the group. This table has a column that uniquely id's the message, a column for the id of the forwarder and a column for the id of the forwardee. The id's come from the usernames table. So far, so good.
<br />
TABLE [Employees] (<br />
[idnum] [int] IDENTITY (1, 1) NOT NULL ,<br />
[firstname] [varchar] (50) NOT NULL ,<br />
[lastname] [varchar] (50) NOT NULL ,<br />
)<br />
<br />
TABLE [tblDownLogForwards] (<br />
[idnum] [int] IDENTITY (1, 1) NOT NULL ,<br />
[TicketNumber] [int] NOT NULL ,<br />
[IdForwardFrom] [int] NOT NULL ,<br />
[IdForwardTo] [int] NOT NULL ,<br />
[ForwardDate] [datetime] NOT NULL <br />
)<br />
Now, I want to report these forwards, but of course I want to make it easy to interpret so instead of the id's I want to include the user's first and last names, something like this:
<br />
msgNum - ForwarderFirstName - ForwarderLastName - ForwardeeFirstName - ForwardeeLastName<br />
How do I specify an SQL statement to replace the user id's with their names? I thought the following might do the trick, but it only selects records having the same id as forwarder and forwardee:
<br />
select ticketnumber, forwarddate, Employees.lastname as FromLast, Employees.firstname as FromFirst, Employees.lastname as ToLast, Employees.firstname as ToFirst from tblDownlogforwards inner join Employees on Employees.idnum = tblDownlogforwards.IdForwardFrom and Employees.idnum = tblDownlogforwards.IdForwardTo<br />
If I elliminate the last equality, I get records where the ForwardTo columns have the same person's name, even when another id is present.
I'm very perplexed by this...
Thanks!
'til next we type...
HAVE FUN!! -- Jesse
-- modified at 14:17 Tuesday 16th May, 2006
|
|
|
|
|
SELECT ticketnumber,forwarddate, FR.lastname AS FromLast,FR.FirstName as FromFirst, FT.lastname AS ToLast, FT.FirstName as ToFirst
FROM tblDownLogForwards INNER JOIN Employees FR ON tblDownLogForwards.IdForwardFrom = FR.idnum
INNER JOIN Employees FT ON tblDownLogForwards.IdForwardTo = FT.idnum
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Eric,
Bingo! Thanks a bunch!
BTW, love your .sig. A guy I worked with many years ago once wore a tee-shirt which said on the back:
"I must hurry and catch up with the others, for I am their leader!" I never knew where it came from. Thanks!
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
I have one stored procedure, and it having UPDATE and INSERT statements, The INSERT / UPDATE queries are inside a Begin transaction, if the transaction fails then rollback the transaction otherwise commit it.
If the error occur rollback the transaction as well as i want to catch the exception and throwuh it to the user in the DA Class
I am calling the stored peocedure in the DA Class
please tell me any suggestions
Awaiting for reply
thanks
-
|
|
|
|
|
IF @@ERROR <> 0
ROLLBACK
RAISEERROR...
ELSE
COMMIT
|
|
|
|
|
Or don't have the transaction in teh sproc, but handle it through the DA class:
SqlTransaction tran = new SqlTransaction(conn);
try
{
... call sproc
tran.Commit();
}
catch (SqlException sqlEx)
{
tran.Rollback();
}
|
|
|
|
|
Be careful with this approach. Some Transactions may still get automatically rolled back, which will cause the rollback statement in the code to throw another exception.
Typically, SQL server rolls back transactions when a severe error or a connection loss occurs. This can be handled by issuing the rollback transaction in a nested try catch, and handling the error if the transaction was already rolled back.
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
how can i return 0 (false) for an error to insert data in SQL database. i am using ASP.Net (using vb.net) to call the stored procedure....
my procedure is...
CREATE PROCEDURE Insert_Book (
@arg_Title VARCHAR(255),
@arg_Price VARCHAR,
@arg_PublishDate DATETIME,
@arg_BookID INT OUT)
AS
DECLARE @rc Int
BEGIN
INSERT INTO
Book (
Title,
Price,
PublishDate )
VALUES (
@arg_Title,
@arg_Price,
@arg_PublishDate );
/*SELECT @arg_BookID = @@IDENTITY;*/
IF @@ERROR <> 0
BEGIN
/*SELECT @rc = 0*/
/*Print convert(Varchar,"An error occurred while adding the new Book information")*/
RETURN (99)
END
ELSE
BEGIN
/*SELECT @rc =99*/
/*PRINT Convert(Varchar,"The new author information has been loaded")*/
RETURN (0)
END
procedure call is...
con = New clarion.connection
Conn = New SqlClient.SqlConnection
Conn.ConnectionString = con.GetConnectionString()
Dim cmdtxt As SqlClient.SqlParameter
Dim objErr As SqlClient.SqlException
Dim txtAbbreviation As String
Dim txtReturnCode As String
Conn.Open()
Dim cmd As New SqlClient.SqlCommand("Insert_Book", Conn)
cmd.CommandType = CommandType.StoredProcedure
cmdtxt = cmd.Parameters.Add("@arg_Title", "Nisha")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@arg_Price", "")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@arg_PublishDate", "01/05/06")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@arg_BookID", "")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@@Error", SqlDbType.Int)
cmdtxt.Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
Conn.Close()
If Convert.ToInt32(cmd.Parameters.Item("@@error").Value) <> 0 Then
txtAbbreviation = Convert.ToString(cmd.Parameters.Item("RC").Value)
txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value)
Else
txtAbbreviation = Convert.ToString(cmd.Parameters.Item("@arg_Price").Value)
txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value)
End If
Dinesh Sharma
Software Engineer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
