|
i have 2 tables. TABLEA and TABLEB. the structures of both the tables are same. the columns are "branch_name" , "book_ISBN" and "book_name".
branch_name is the primary key in both the tables
the data in the tables are
TABLEA has data as
branch_name book_ISBN book_name
book1 123 vishu
book2 456 vishu
TABLEB has data as
branch_name book_ISBN book_name
book1 123 vishu
book2 567 vishu
as you can see, the 2nd row in both the tables has mismatch in "Book_ISBN" column.
i used a query to find the mismatch in data
SELECT A.branch_name,
CASE WHEN A.branch_name=B.branch_name THEN 'Y' ELSE 'N' END as "Comp_Branch",
A.book_ISBN,
CASE WHEN A.book_ISBN=B.book_ISBN THEN 'Y' ELSE 'N' END as "Comp_book_ISBN",
A.book_name,
CASE WHEN A.book_name=B.book_name THEN 'Y' ELSE 'N' END as "Comp_book_name"
FROM Tablea AS A
LEFT OUTER JOIN Tableb AS B ON A.branch_name = B.branch_name
the result came as
book1 Y 123 Y vishu Y
book2 Y 456 N vishu Y
which shows that "456" of TABLEA is not matching with "567" of TABLEB
now if i want that only the rows having "N" (means mismatch) should be displayed, then how to do this in the above comparison query ?
Please reply ASAP
Vishal
|
|
|
|
|
You've got a number of options.
First of all, you could use the comparisons that are in your case statements as a where clause ...
WHERE<br />
A.branch_name<>B.branch_name<br />
OR A.book_ISBN<>B.book_ISBN<br />
OR A.book_name<>B.book_name
Or you could make your original query into an in-line table and select from it:
Select * FROM<br />
(<br />
SELECT A.branch_name,<br />
CASE WHEN A.branch_name=B.branch_name THEN 'Y' ELSE 'N' END as "Comp_Branch",<br />
A.book_ISBN, <br />
CASE WHEN A.book_ISBN=B.book_ISBN THEN 'Y' ELSE 'N' END as "Comp_book_ISBN",<br />
A.book_name,<br />
CASE WHEN A.book_name=B.book_name THEN 'Y' ELSE 'N' END as "Comp_book_name"<br />
FROM Tablea AS A<br />
LEFT OUTER JOIN Tableb AS B ON A.branch_name = B.branch_name<br />
) Comparison<br />
WHERE Comp_Branch = 'N' or Comp_book_ISBN = 'N' or Comp_book_name = 'N'
If you have multiple uses for this information, (e.g. sometimes you just want to see the comparisons where the ISBNs are different) then you may want to create a view from your original query and then select from the view:
Select * from COMPARISON_VIEW<br />
WHERE Comp_Branch = 'N' or Comp_book_ISBN = 'N' or Comp_book_name = 'N'
Jim Conigliaro
jconigliaro@ieee.org
-- modified at 8:11 Friday 19th May, 2006
|
|
|
|
|
If we use SQL Exception in DA Class
sqltransacrion trans=new ...
try
{
call SP
trans.commit
}
catch(sqlException ex)
{
ex.msg
trans.rollback
}
or
//SqlConnection con = new SqlConnection();
//SqlCommand com = new SqlCommand("procname", con);
//com.CommandType = CommandType.StoredProcedure;
//com.Parameters.Add(@Arg, SqlDbType.Text).Value = @Arg;
//com.ExecuteNonQuery();
create proc procname @Arg
as
insert into values..
if(@@Error<>0)
raiserror(...)
what is the difference in the errors raising in the above two calls
what is the difference between the error throwing
-
|
|
|
|
|
Hello,
In SSMSE I mistakenly ran a create table query on my database in SQL Server 7.0. I then launched SQL Server Enterprise Manager, and noticed that one of my databases are greyed out with (Suspect) labeled beside it.
I checked C:\MSSQL7\Data to see if my database was present, with the right file size.
How can I enable that database?
Thanks in advance.
|
|
|
|
|
(Is it interveal or interval or something else?)
Anyway, I have a database, and I was wandering if it is possible through SQL to substract 2 dates from the cells in the tables.
eg. I have a start time and end time, and I want to know what's the time that between... how much time did it take etc...
My teacher has showed me something with Sum and CDate SQL Methods, but I want a second opinion (and even if some better ideas).
Thanks alot...
NaNg.
|
|
|
|
|
If your database is in Microsoft's SQL Server, then look up DATEDIFF().
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I use access in the meanwhile
|
|
|
|
|
It's DateDiff() there too:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has thesenamed arguments:
Part Description
interval Required.String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek Optional. Aconstant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
Settings
The intervalargument has these settings:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
The firstdayofweek argument has these settings:
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
I am working on a project and to test I would like to make a copy of the SQL database just so I don't mess up the original data. How can I do this?
|
|
|
|
|
Do a complete backup of the database then restore it with a different name.
"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
|
|
|
|
|
Create a database with new name and export all data of existing database to new one.
Saqib
|
|
|
|
|
But if you do that you don't get the stored procedures, UDFs, users, etc. In other words, it is not a copy of the database, it is just a copy of the data.
"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
|
|
|
|
|
get backup of existing database, create new database and restore that backup to new created database, this would now contain all things in it.
Saqib
|
|
|
|
|
the best thing what i suggest is , right click on the database for ehich u need a copy .. then generate the sql script then follow the steps like showall->scriptall..then u find it will create a file with all sql statements...after that u do..
1. copy the complete sql statments to query analyzer...and in first line give the command
create database (new database name )
...copy all the sql statements
then execute ( i mean f5) that's it
every problem has a solution...
|
|
|
|
|
I search some detailed information about some aspect of commands that are generated in VS8 for TableAdapters:
 How are calculated @IsNull_<columnname> parameters that are present in the update query?
Does anybody know something about it?
I understand the use of these parameters but I can't find where the parameters are calculated.
Why this question?
I need to change the automatically generated query because in the table that I use there is a calculated column.
The automatic gerated UpdateCommand include the calculated column in the set section of the query so this doesn't work.
So I changed the query of UpdateCommand using the Query Builder.
When I exit from the query Builder the parameters collection all parameters of type @IsNull_<columnname> change, DbType change from Int to AnsiString and ProviderType is cleared (then the Update method doesn't work). Probably this is a bug.
I can define manually the update command, but I would like to know more about the method used by the DataAdapter to initialize the various @IsNull_<columnname> inserted automatically.
Environment: VS8, SQL Server 2000, .Net Framework 2.0
Thanks in advance
Roberto Ferraris
|
|
|
|
|
Searching in MSDN and in the automatic generated code I find some more info, that I attach to the original question, wishing this could help someone else.
The automatic generated code for the parameters of update command is like this:
new SqlParameter(
"@IsNull_DtClassification",
System.Data.SqlDbType.Int,
0,
System.Data.ParameterDirection.Input,
0,
0,
"DtClassification",
System.Data.DataRowVersion.Original,
true,
null,
"",
"",
"");
From SqlParameter.SourceColumnNullMapping documentation I find that the @IsNull_FieldName is used directly by the SqlCommandBuilder (that I think is the same used by the TableAdapter Configuration Wizard.
Here I find "@IsNull_FieldName contains 1 if the source field contains null, and 0 if it does not. This mechanism allows for a performance optimization in SQL Server, and provides for common code that works across multiple providers."
The problem at this point is that if I try to modify the query manually removing the calculated field from the query, the resulting command generate parameters like the following, where there is no source column and data types is wrong:
new System.Data.SqlClient.SqlParameter(
"@IsNull_DtClassification",
System.Data.SqlDbType.VarChar,
1024,
System.Data.ParameterDirection.Input,
0,
0,
"",
System.Data.DataRowVersion.Original,
false, null, "", "", "")
I thing this is a bug of VS
Bye
Roberto Ferraris
|
|
|
|
|
Hi ppl,
I have created an ODBC connection using the ODBC wizzard.
I am using VB.NEt and SQL server
The DSN name : Rch1
Description of Data source : SqlRch
SQL Server Connection "MT1"
This code is not working
Public conn As New OdbcConnection("Provider=sqlodbc;server=Rch1 ;Database=Rch;Trusted_Connection=YES;")
But wont connect.
I get the following error:
An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll
Additional information: System error.
Tnx
-- modified at 11:01 Wednesday 17th May, 2006
|
|
|
|
|
hi
i want to add a column to table using alter statement and i want it to be added after a particular column.
this is done using after clause in oracle but i want to know its eqivalent in sql server
Sanjeev
|
|
|
|
|
How does the column order make a difference?
|
|
|
|
|
yes u are right , it makes no difference.
But still i want to know how can i do that ???
If any body knows ?
|
|
|
|
|
Ok, given that, you made me interested in how...
Try this[^].
|
|
|
|
|
it was all about swaping existing columns.
columns can be added at desired place using enterprises manager but i m tryng to do it using alter table or ..... some thin that i dont know.
sanjeev
|
|
|
|
|
I think if you read it through, they were saying that behind the scenes enterprise manager copies all your data into a temp table, drops the current table, then recreates it with your new table order and copies the data back over.
Dead easy... 
|
|
|
|
|
thanks for your reply.
but you r not geting wat i mean to say ?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
