IT Professionals

LM

I am thinking of doing it in MS SQL Server , Please advice .

Go ahead and do it. It certainly gives you an advantage w.r.t interviews. If you go up to MCDBA, it would be much better.

hmm Thanks LM :)

Hi

i bought dell inspiron 15R i want to install Win xp along with win7 . But blue screen error occurs.. saying CHCKDSK F.. i want to use dual OS give some suggestions.. i'm installing win xp for mech design software Pro-e usage.

Found this link.
http://en.kioskea.net/forum/affich-1...-to-install-xp

Quote:

---

If you are trying to install the Windows XP from scratch on the laptop, please BE SURE that you put the Hard Drive on "Compatibility" mode in BIOS, which was defaulted to "AHCI" (Compatibility is also known as IDE).

Otherwise, you will experience the "blue screen" issue.

To change that setting, you need to do the following:
1. Press F2 when booting up the computer
2. Select 'Config', then 'Serial ATA (SATA)'
3. Change controller option to 'Compatibility' / 'IDE'

---

Check if the above solution works :D.

Adding to Puli's suggestion, check if your Win XP installation CD has SP2 as well. Most probably Puli's tip would work.

Has anyone worked with variables within SSIS?
I have created a variable called SQL within SSIS and it has some SQL code as the expression.

I now have a Data Flow task, and OLE DB Source editor withing the DFT. When I call this variable from OLE DB source editor, I get an error saying

"Command text was not set for the command Object"

Any idea as to what the problem is ??

Params,

As a general principle, it's not preferable to use 'SQL' as a variable :-)

It's possible some tools use it as a key word / command directive etc, though not explicitly stating to avoid using as a variable.

Just a suggestion. Not to imply that it has any bearing on this specific situation of yours but in general :-)

App na

I know about 'SQL' being the reserverd word, but in SSIS when you declare a variable you do not do this

Declare @variable varchar(255)

it is done differently, not through T - SQL

Has any one worked with

1) Active/Active clustered SQL Servers?

2) Active/Passive clustered SQL servers using DMware ?

App_engine anna and others

Has any one used the 'RANK' function in SQL Serever 2005/ 2008 or even in Oracle 8i to identify duplicate records ?

Param,
By duplicate if you mean only some fields (and not the entire row), then using the analytic function RANK is a very standard practice in Oracle.

e.g. many people with the same first name / last name, like "John Anderson" but different emplid's or SSNs are "duplicate" in the "name-sense" and you can use the analytic function to pick one and get rid of others.

However, if the purpose is for deleting two "identical" rows (no unique constraint defined for the table), RANK won't work.

The earlier case is nicely explained here :
http://www.devx.com/getHelpOn/10Minu...ion/16597/1954

Quote:

---

A SQL Solution: Deleting Duplicate Data with a Single SQL Statement Using RANK()
The Oracle 8i analytic function RANK() allows you to rank each item in a group.

In our case, we are using this function to assign dynamically sequential numbers in the group of duplicates sorted by the primary key. With RANK(), grouping is specified in the PARTITION BY clause and sort order for ranking is specified in the ORDER BY clause:

SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) SeqNumber
FROM Customers
ORDER BY LastName, FirstName;

Bingo! Now, values in the SeqNumber column, assigned by RANK(), allow you to separate all duplicate rows (SeqNumber > 1) from non-duplicates (SeqNumber = 1) and retrieve only those rows you want to delete:

SELECT ID, LastName, FirstName
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM Customers)
WHERE SeqNumber > 1;

---

Quote:

---

Originally Posted by PARAMASHIVAN

App_engine anna and others

Has any one used the 'RANK' function in SQL Serever 2005/ 2008 or even in Oracle 8i to identify duplicate records ?

---

If you wanna delete duplicate rows then the technique we used earlier was creating a temp table identical to the table.insert distinct data into the temp table from the original and then move the same back to original.

insert into @temp select distinct col1,col2,col3 from mytable
delete mytable
insert into mystable select col1,col2,col3 from @temp

-
கிறுக்கன்

test test test

Thanks Kirukukan and App na , I will try that. BTW, I have question , how do you find the second highest value or 3rd highest value in a column, say you have vcolumn called salary, so ' Select Max(salary) from table' would return the highest salary, but how about the second or thir highest ?

One technique in Oracle is by using rownum / order by :-)

e.g.

select * from
(select rownum r, CR
from
(select distinct comprate CR
from ps_compensation
order by comprate)
)
where r = 2

The above sql will get the 2nd lowest :-)

For highest, your order by should add "desc".

Thanks app Na

but would the above script work in SQL Server?

It had been years since I used a sql server database. Possibly in 2002...been in all-oracle world since then.

Try it out :-)

Thanks App annEh :)

Help needed App na / Kirukan pls

I have a table called Customer, with fields called customer_id, cust_name and nick name, I want a trigger that would update nick_name,
when ever a value is inserted into cust_name .

the sql command starts some thing like this

create trigger test
on customer
for update as

I amnot sure of the code after this ...

Quote:

---

Originally Posted by PARAMASHIVAN

Help needed App na / Kirukan pls

I have a table called Customer, with fields called customer_id, cust_name and nick name, I want a trigger that would update nick_name,
when ever a value is inserted into cust_name .

the sql command starts some thing like this

create trigger test
on customer
for update as

I amnot sure of the code after this ...

---

Create TRIGGER test ON customer FOR Update AS
set nocount on
declare @nickname varchar(50)

--- Selecting Columns form Updated table
select @nickname=u.Customername
from inserted u
update nickname=@nickname where Customername=@nickname

Hope this helps

Sorry for the late reply Kirukan, been very busy at work. Thanks for your help, I have done it now. One more Question

Say you have Storeprcedure called SP1 with a variable called @orderno varchar(20) and you have another procedure called SP2 without any variables, can you call SP1 's variable into SP2 and excecute SP2. Is it some thing like this

Create proc SP2
as
Exec SP1,@orderno

??

Thanks in advance

Quote:

---

Originally Posted by PARAMASHIVAN

Sorry for the late reply Kirukan, been very busy at work. Thanks for your help, I have done it now. One more Question

Say you have Storeprcedure called SP1 with a variable called @orderno varchar(20) and you have another procedure called SP2 without any variables, can you call SP1 's variable into SP2 and excecute SP2. Is it some thing like this

Create proc SP2
as
Exec SP1,@orderno

??

Thanks in advance

---

I dont think its possible unless u use global variable @@orderno.

Thanks Kirukan, I have used a Global variable , and it works ! :)

Quote:

---

Originally Posted by PARAMASHIVAN

Thanks Kirukukan and App na , I will try that. BTW, I have question , how do you find the second highest value or 3rd highest value in a column, say you have vcolumn called salary, so ' Select Max(salary) from table' would return the highest salary, but how about the second or thir highest ?

---

Hi Paramashivan,
One way to do this in sql server 2005/2008 is to use the RANK() function and get the salary ranked in desecending order, Run a select on the temp table/derived table and look for rank=2 or 3.

SELECT * FROM (SELECT RANK() OVER (ORDER BY Salary DESC) AS RankNum) as a
WHERE RankNum=2.
If you use a construct similar to the above, you should get your result.



Thank You

Welcome to the thread Ramdas :)

Thanks, I will try that and let you know :)

Quote:

---

Originally Posted by PARAMASHIVAN

Welcome to the thread Ramdas :)

Thanks, I will try that and let you know :)

---

Thank you Paramashivan , I also work with SSRS/SSIS, In case you work in those areas, i can share my experiences.

Ramdas

That's great stuff, I am actually working as SQL server / BI Developer, Primarily on SSIS , SSRS on 2008.

Have you got experience in Datawarehouse development, If so can you pls briefly explain the procedure of creating a datawarehouse? Like for Relational Database , you would normalise the data, come up with enitities, then draw up an entity relation ship diagram, then from that you would create the tables, Pk, FK, views, ...etc

I know in Datawarehouse, it is de-normalised data, and you have fact tables/dimensional tables..etc. Can you briefly explain them to me pls

thanks

Quote:

---

Originally Posted by PARAMASHIVAN

Ramdas

That's great stuff, I am actually working as SQL server / BI Developer, Primarily on SSIS , SSRS on 2008.

Have you got experience in Datawarehouse development, If so can you pls briefly explain the procedure of creating a datawarehouse? Like for Relational Database , you would normalise the data, come up with enitities, then draw up an entity relation ship diagram, then from that you would create the tables, Pk, FK, views, ...etc

I know in Datawarehouse, it is de-normalised data, and you have fact tables/dimensional tables..etc. Can you briefly explain them to me pls

thanks

---

Hi Paramashivan,
A datawarehouse/data mart contain fact and dimension tables. I am attaching couple of documents which explains fact and dimension tables. The document has sample fact and dimension tables. It also has an image describing Fact and Dimension table relationship. This should give you a good starting point.

Fact and dimension tables are usually populated through Stored procedures/SSIS packages with the source data coming form OLTP relational systems. The SP's and SSIS denormalise the data and populate the fact and dimension tables. Usually the databases for a datawarehouse would be on a different server. The SSIS package would grab data from different source tables in OLTP transfer it to staging tables on datawarehouse database. From the satging tables Stored procedures would transform the data into denormalise form and load them into fact and dimension tables.

Fact Table:
A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized to provide information about the history of the operation of the organization. Each fact table also includes a multipart index that contains as foreign keys the primary keys of related dimension tables, which contain the attributes of the fact records.

Also if you could give me you e-mail id we can correspond better for explanation of concepts.

On a lighter note : Rajini fan / kamal fan/?

Quote:

---

Originally Posted by ramdas2005

Hi Paramashivan,
A datawarehouse/data mart contain fact and dimension tables. I am attaching couple of documents which explains fact and dimension tables. The document has sample fact and dimension tables. It also has an image describing Fact and Dimension table relationship. This should give you a good starting point.

Fact and dimension tables are usually populated through Stored procedures/SSIS packages with the source data coming form OLTP relational systems. The SP's and SSIS denormalise the data and populate the fact and dimension tables. Usually the databases for a datawarehouse would be on a different server. The SSIS package would grab data from different source tables in OLTP transfer it to staging tables on datawarehouse database. From the satging tables Stored procedures would transform the data into denormalise form and load them into fact and dimension tables.

Fact Table:
A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized to provide information about the history of the operation of the organization. Each fact table also includes a multipart index that contains as foreign keys the primary keys of related dimension tables, which contain the attributes of the fact records.

Also if you could give me you e-mail id we can correspond better for explanation of concepts.

On a lighter note : Rajini fan / kamal fan/?

---

Many thanks , I just re called the Staging database stuff, I have used SSAS before, but not much. I am PMing you my email ID

On a Lighter Note, I have a huge Fan list :lol:
Shivaji +SPB +Rajni >Kamal +KJY+TMS+PJ+PBS+PS+SJ+KSC +Raghuvaran>Karthik :) Heroine lis is bigger :lol2: , So I wont post here :)

Hi paramashivan,
What is PMing, is it the e-Mail set in my profile or Instant Messaging...
Thank you

Ramdas

I meant Private Message :)

Hi Raghu,
Thanks for the e-mail, sent my e-mail id.

Hi all

Has anyone used inserted and deleted tables in SQL, regarding triggers.

Yes Param...

Thanks Kirukun, I managed to resolve the matter :)

Hi,

I have a question.

How to select (SQL) all the values (numbers) in the table which has 3 digit after decimal point.

For example say A is the table it has following values

200.24
300.456
123.22
1234.344

From this I need only

300.456
1234.344

19thmay,
What about doing a to_char and check for the fourth char starting from '.' (as to whether null)...just loud thinking...

Hi All

I am trying to do this in SQL

Select 1 / 2 as [answer]

The result I get is 0

I tried the cast function as follows

select cast((1)/(2) as decimal (10,2)) as [answer]

it is still not giving me a decimal result of 0.5, what should I do ?