|
i have to create a table item which has an attribute item category....category is a multivalued attribute...can anyone tell how to implement this?
create table item_info(itemno varchar(5),item_sno varchar(30),condition varchar(20),
color varchar(15),
corigin varchar(20),
Year_prod integer, PRIMARY KEY(itemno,item_sno))
|
|
|
|
|
you need to create a lookup table to store the category values and then a bridge table to store the categories for items.
CREATE TABLE Categories (
CategoryID int,
CategoryName varchar(100)
)
CREATE TABLE ItemCategories (
Itemno varchar(5),
CategoryID int
)
You can store multiple rows in the ItemCategories table for each item.
|
|
|
|
|
Create two tables as:
Item_Category
{ItemID int identity(1,1) primary key, Item_CategoryName NVARCHAR(100))
AND
another one is
Item
{
ItemSrNo int identity(1,1) primary key,
ItemID int, --foreign key references values from Item_Catory Table
Column3, ,,,n
}
|
|
|
|
|
Hi, in my project I tried to backup my database and then restore using SMO. While backing up and restoring it gives no errors and catches no exception. All of them seem to work great. However, after I perform backup to a file, I deleted the data and/or a table from my database to see whether restoring operation will perform correctly or not. However, when I deleted a table or just data in the table, if I perform restore operation, neither the table nor the data in the table comes back. As a result either backup or restore operation does not work correctly. My backup and restore methods are as in the following :
public bool BackUpDB(string DBpath)
{
try
{
ServerConnection srvConn = new ServerConnection(Program.serverName);
srvConn.LoginSecure = true;
srvr = new Server(srvConn);
Backup bkpDatabase = new Backup();
bkpDatabase.Action = BackupActionType.Database;
bkpDatabase.Database = Program.databaseName;
bkpDatabase.Incremental = false;
bkpDatabase.Initialize = true;
bkpDatabase.LogTruncation = BackupTruncateLogType.Truncate;
BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath , DeviceType.File);
bkpDatabase.Devices.Add(bkpDevice);
bkpDatabase.SqlBackupAsync(srvr);
return true;
}
catch (Exception)
{
return false;
}
}
The following is my restore method :
public bool RestoreDB(string DBpath)
{
try
{
ServerConnection srvConn = new ServerConnection(Program.serverName);
srvConn.LoginSecure = true;
srvr = new Server(srvConn);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
if (!srvr.Databases.Contains(Program.databaseName))
{
try
{
DbConnection dbcon = new DbConnection("Data Source=" + Program.serverName + ";Initial Catalog=master;Integrated Security=True");
dbcon.ExecuteCommand("CREATE DATABASE "+Program.databaseName, null);
}
catch (Exception)
{
MessageBox.Show("Veritabanı yaratılırken hata oluştu. Program yine de yedeğinizi geri yüklemeye çalışacak. Ancak bu değişiklik programınıza yansımayabilir. Bu durumda bizimle iletişime geçiniz.","Uyarı",MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
}
rstDatabase.Database = Program.databaseName;
rstDatabase.Devices.AddDevice(@DBpath, DeviceType.File);
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestoreAsync(srvr);
return true;
}
catch (Exception)
{
return false;
}
}
I searched on this issue a lot and all of the codes seems correct however I could not understand why it does not perform correctly. Anyone can help me on this issue ? Please it is a bit urgent, Thanks...
|
|
|
|
|
Just a thought - I have not used SMO in many years but does the restore process drop all other connections to the database, I know if you try and restore via SSMS and there is an existing connection it fails, generates a warning but it does fail. Also the connection YOU are using may need to be to the master database not the one you are trying to restore!
I love you exception, I wonder what it says 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
Any idea to select this data ?
NGB CCU 0 0 0 0 6
NGB CCU 0 0 4 0 0
SHA SIN 0 0 0 0 4
SHA SIN 0 0 0 1 0
SHA SIN 0 0 25 0 0
To be result as below
NGB CCU 0 0 4 0 6
SHA SIN 0 0 25 1 4
Thanks and best Regards.
|
|
|
|
|
Its a simple sum() and Group By query
Select col1, col2, sum(col3), sum(col4) etc
Group By col1,col2
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh! yes such a simple case  I didn't get that idea. Thank you.
But, Any Idea again?
To get this data I had to use the following query, the query is already complicated.
Is there any way to Sum up the data as you suggested without dumping this data into temp Table?
NGB CCU 0 0 0 0 0 0 0 0 6
NGB CCU 0 0 0 0 0 0 4 0 0
SHA SIN 0 0 0 0 0 0 0 0 4
SHA SIN 0 0 0 0 0 0 0 1 0
SHA SIN 0 0 0 0 0 0 25 0 0
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
(case when container_code like '%R%20' Then qty else '' end ) as 'R_D20',
0 as 'R_D40',
(case when container_code like '%HR%40' Then qty else '' end ) as 'R_H40',
(case when container_code ='FL20' OR container_code ='OT20' Then qty else '' end ) as 'OT_D20',
(case when container_code ='FL40' OR container_code ='OT40' Then qty else '' end ) as 'OT_D40',
0 as 'OT_H40',
(case when container_code='D20' Then qty else '' end ) as 'N_D20',
(case when container_code='D40' Then qty else '' end ) as 'N_D40',
(case when container_code='HC40' Then qty else '' end ) as 'N_H40'
From myTable
order by POL,POD
modified 27-Sep-11 3:55am.
|
|
|
|
|
Try this:
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
Sum(case when container_code like '%R%20' Then qty else 0 end ) as 'R_D20',
0 as 'R_D40',
Sum(case when container_code like '%HR%40' Then qty else 0 end ) as 'R_H40',
Sum(case when container_code ='FL20' OR container_code ='OT20' Then qty else 0 end ) as 'OT_D20',
Sum(case when container_code ='FL40' OR container_code ='OT40' Then qty else 0 end ) as 'OT_D40',
0 as 'OT_H40',
Sum(case when container_code='D20' Then qty else 0 end ) as 'N_D20',
Sum(case when container_code='D40' Then qty else 0 end ) as 'N_D40',
Sum(case when container_code='HC40' Then qty else 0 end ) as 'N_H40'
From myTable
Group by POL,POD
order by POL,POD
Note that I changed your empty strings to 0 to avoid mixing of types.
|
|
|
|
|
Hi thank you for the reply.
When I use Group By POL,POD as your suggested
Get error for
"Container_Code and Qty is invalid in the select list because it is not contained in either an aggregate function or the Group By clause."
When I add these two in Group by Clause
Group by POL,POD,container_code,qty
Got the result as same as without using Group By.
|
|
|
|
|
Dear
You can use group by clause.
select c1, c2, isnull(sum(c3),0), isnull(sum(c4),0)
from tbl
group by c1, c2
use having clause for again filter your output. use it after grope by clause.
|
|
|
|
|
Thank you for your reply.
Group By doesn't work for my case.
|
|
|
|
|
hi
i want implementation data warehous on the sql server please guide
thanks
|
|
|
|
|
Buy a book, read some articles, SQL Server Central[^] is a good place to start.
It is way too big a subject to get a response in a forum thread.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have deleted some tables containing data and images from Database but database size is not reduced. any body guide me .Why not reduced?
|
|
|
|
|
If you are talking about Microsoft Access database, then you can use "compact and repair" to reduce the size of your file.
|
|
|
|
|
Why should it be reduced?
Most databases work in this way; you deleted items in the database, you didn't make the database smaller. Any deleted items aren't "physically" removed, they're just marked as being "non-existent". Imagine a database having to remove a single string from 5 trillion others, and save a concatenated form back again. Databases are fast by doing things efficiently.
No real "removals", but "marks". If you want to reclaim the space, you'll have to "shrink" your database. Access has it's way, as mentioned, and Sql Server has some sprocs.
Bastard Programmer from Hell 
|
|
|
|
|
Any body please guide from where I can download SQL Server 2008 ?
|
|
|
|
|
There are many versions, for modest projects I prefer SQL Server 2008R2 Express Edition, here[^]. For others, search the Microsoft site, or google.
|
|
|
|
|
Your thought please on this topic in the QA forum.
QA : Do we need SP's anymore?
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Caveat - I am VERY pro store procs, I not allow direct string access to the database.
I disagree with you're premise that they are simpler to debug in strings, this may be true if you have no TSQL experience but not if you have even a modicum of skill.
If you are doing fairly standard CRUD work then your arguments are absolutely valid. However if you are doing any serious volume manipulation then procedures are easier to build and tune. I can't imagine writing a 100 line proc as strings, it just would not make sense.
As for processing (complex, volume) data in the business layer this just does not work. We had a team use Enterprise library and do all the processing in the BL layer, they needed 2 application servers to match the DB server performance.
I do sympathise with your stand, I recently had to get involved in PLSQL, my skills are a decade out of date, what a bloody nightmare. I tweaked my code generator to write PLSQL CRUD procs and get an Oracle specialist to do anyhting more complex.
Also any (LOB) developer worth paying should know SQL and the database they are working on!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your input.
I agree there are times where they are absolutely necessary ( mostly server side heavy manipulations). My problem is they are being pushed where they aren't needed.
The problem with DB's (tables structures and SP's) is that they go rigid quickly and you dare not change them, I have had clients where 1500 SP's were quite common and no one knew how they worked.
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Mehdi Gholam wrote: where 1500 SP's were quite common and no one knew how they worked
Thats because the stupid bastards (management) don't retain the IP (as in the business knowledge) and the skills to manage their own data.
So many organisations feels they can outsource their development work, get a monkey to do the work and maintain a viable business plan - idiots.
And I'm a contractor who makes a very comfortable living from this business practice. Don't push that button I can go on for hours on this subject!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
finger poised... push... click...
Come on lets have some of your war stories!
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Mehdi Gholam wrote: Come on lets have some of your war stories!
Not a chance, both of us have better things to do 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
