IT Professionals

[Raghu]

Dear

Can we use this thread for any IT related discussions, ranging from Software development, Maintenance, support, Networking, and hardware (client/Server) issues.

Please post your questions here to get going

[Sanjeevi]

I remember there was a thread with same title :roll:

[Raghu]

can't find it any more :(

[dev]

My bro accidentally uninstaled google chrome...I've reinstalled it but is there any way to recover my lost bookmarks... renaming bookmarks.bak din't work...

[Raghu]

My bro accidentally uninstaled google chrome...I've reinstalled it but is there any way to recover my lost bookmarks... renaming bookmarks.bak din't work...

Devuda

What version of windows is that? there should be tool in windows that allows you to recover lost data!

[dev]

Raghu, it's XP...

[PARAMASHIVAN]

does any one know the 'DOS' to find a list of Servers on given domain?

I prefer DOS over VB script :)

[Raikkonen]

cmd > net view

not very sure though :lol:

[PARAMASHIVAN]

cmd > net view

not very sure though :lol:

sorry, i wanted to find the number of SQL server 2008 on windows 2003.. but your command will give all the windows server, any way

if you type in OSQL -L would give you the list, but if you want to complicate things, like i alwys do :lol: the following SQL script will do

/* Gives a list SQL servers on a given domain */

CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #servers(sname VARCHAR(255))

INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
-- play with ISQL -L too, results differ slightly

DELETE #servers WHERE sname='Servers:'

SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'

DROP TABLE #servers
END

exec ListLocalServers

[Raikkonen]

i don't have any servers installed at my home.. :oops:

i will try to read and understand this at office :lol:

[PARAMASHIVAN]

i don't have any servers installed at my home.. :oops:

i will try to read and understand this at office :lol:

enNa try paneenghala :lol:

r u SQL server Developer?? I am one :D

[Raikkonen]

ileeng :lol: sorry..

i'm more into network/web programming. :)

just got the mcse cert.. but not very familiar with sql server.

[PARAMASHIVAN]

ileeng :lol: sorry..

i'm more into network/web programming. :)

just got the mcse cert.. but not very familiar with sql server.

Oh cool. so u can do java scripting and .net programming right :) , if i have any doubts i shall ask, cos .Net & sql server are highly integrated , they work very closley :D

[Raikkonen]

cool..

i haven't worked with java for some years now. i can do the applets etc.. almost in advanced stage.

ya. i'm working in net environment. :thumbsup:

[PARAMASHIVAN]

Raiks

aama neenga enna 'Diamon' smuggling busniess panrEla?? Diamon hubber nu title kudithirukuranga :lol2:

[PARAMASHIVAN]

Hi

I have SQL table with a customer table in SQL server , having columns (name, lastname)
the last name contains values like (o'corner, o'brian) how would i search for these records iusingT-SQL

Select * from customers
where lastname like 'O'coner%'

it does not work cause of the single quote bewteen O and C... any way round it?

Raiks??

[Nerd]

Add another apostrophe. Something like,


select * from customers
where lastname like 'O''CORNER%'

[app_engine]

Paramasivan,
This is for you :
http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/

[app_engine]

To add to Nerd's post, if you're going to call the sql from a webpage etc, this kind of concatenation could pose sql-injection threat and this link gives a solution for that :
http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F

[PARAMASHIVAN]

Add another apostrophe. Something like,


select * from customers
where lastname like 'O''CORNER%'

Danks Nerd :)

[PARAMASHIVAN]

Paramasivan,
This is for you :
http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/

Danks, This guy is a SQL guru :notworthy:

[PARAMASHIVAN]

Does any one know how to convert 'american date format' to 'british date' format in SQL server , without changing the coalltion??

[PARAMASHIVAN]

ssabbaa I figured it out

here goes

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

[sakaLAKALAKAlaa Vallavar]

linux experts, how to share internet btw 2 laptops running ubuntu, thru wifi?

[PARAMASHIVAN]

Sorry sakala , I am no linux expert

[aanaa]

friend of mine - suggested



never envounted patterns like O', which is normally used to concatinate the strings in SQL. fpr normal names '%name%' should work.

Is it working ?

[PARAMASHIVAN]

aana

Unga avatar and signature :notworthy:

[aanaa]

:ty:

[PARAMASHIVAN]

Is any one familiar with the hardest thing in SQL code, known as Cursor? :banghead:

I will explain what I need to do..

I have table A , and Table B

Table A Table B
--------- ------------

First_name First_name
Last_name Last_name
Address1 Address1
Adreess2 Address2


What I want to do, see if the firstname and last_name in Table B exist in Table A, if so then I want to update Address1 and address2 in table A with a value. This is easy to do for couple of rows.

using

Update Table A
set address1='test',
address2='test2'
where first_name in (select first_name from table B
where first_name like 'Super')


but the problem is in Table B , i have 10,000 first_name with values in the address1.. I need update table A9Address1) with a value based on table B's contact name, hence you end up with 10,,000 different names, with 10,000 different address, if it was just one addreess it would have been very easy, I can use the above script.

any ideas any one ?

[app_engine]

Checking whether the first name / last name exists in table B is no big deal (for set processing, use EXISTS clause)

OTOH, if you have 10000 NEW addresses to be updated to table A, you got to STORE those addresses in a table (unless the requirement is to directly update from a flat file, then you need a script). Obviously, this flat file or table with new address should have the name reference as otherwise how you're going to relate?

[PARAMASHIVAN]

Checking whether the first name / last name exists in table B is no big deal (for set processing, use EXISTS clause)

OTOH, if you have 10000 NEW addresses to be updated to table A, you got to STORE those addresses in a table (unless the requirement is to directly update from a flat file, then you need a script). Obviously, this flat file or table with new address should have the name reference as otherwise how you're going to relate? :? what is that?

[app_engine]

abbreviation for "on the other hand"

[kirukan]

Is any one familiar with the hardest thing in SQL code, known as Cursor? :banghead:

I will explain what I need to do..

I have table A , and Table B

Table A Table B
--------- ------------

First_name First_name
Last_name Last_name
Address1 Address1
Adreess2 Address2


What I want to do, see if the firstname and last_name in Table B exist in Table A, if so then I want to update Address1 and address2 in table A with a value. This is easy to do for couple of rows.

using

Update Table A
set address1='test',
address2='test2'
where first_name in (select first_name from table B
where first_name like 'Super')


but the problem is in Table B , i have 10,000 first_name with values in the address1.. I need update table A9Address1) with a value based on table B's contact name, hence you end up with 10,,000 different names, with 10,000 different address, if it was just one addreess it would have been very easy, I can use the above script.

any ideas any one ?

Update A set address1=B.Address1,address2=B.address2
from TableA A, Table B B where A.FirstName=B.FirstName

Hope this helps

Nice to see this thread in this forum.

கிறுக்கன்

[PARAMASHIVAN]

Is any one familiar with the hardest thing in SQL code, known as Cursor? :banghead:

I will explain what I need to do..

I have table A , and Table B

Table A Table B
--------- ------------

First_name First_name
Last_name Last_name
Address1 Address1
Adreess2 Address2


What I want to do, see if the firstname and last_name in Table B exist in Table A, if so then I want to update Address1 and address2 in table A with a value. This is easy to do for couple of rows.

using

Update Table A
set address1='test',
address2='test2'
where first_name in (select first_name from table B
where first_name like 'Super')


but the problem is in Table B , i have 10,000 first_name with values in the address1.. I need update table A9Address1) with a value based on table B's contact name, hence you end up with 10,,000 different names, with 10,000 different address, if it was just one addreess it would have been very easy, I can use the above script.

any ideas any one ?

Update A set address1=B.Address1,address2=B.address2
from TableA A, Table B B where A.FirstName=B.FirstName

Hope this helps

Nice to see this thread in this forum.

கிறுக்கன்

Thanks kirukan, I will give it a go and see 8-)

[PARAMASHIVAN]

Any one here good at SQL 2008 / 2005 SSRS :huh:

[app_engine]

Rita Mulcahy passed away :

http://www.theicpm.com/certification/3538-breast-cancer-claims-the-life-of-rita-mulcahy-tragic-loss-to-the-world-of-project-management

(Excellent author of PMP training books)

[Benny Lava]

linux experts, how to share internet btw 2 laptops running ubuntu, thru wifi?

SKV, this is a late response but have you tried this link?

http://www.ubuntugeek.com/sharing-internet-connection-in-ubuntu.html

[sathya_1979]

Rita Mulcahy passed away :

http://www.theicpm.com/certification/3538-breast-cancer-claims-the-life-of-rita-mulcahy-tragic-loss-to-the-world-of-project-management

(Excellent author of PMP training books)
RIP, her book helped me a lot in earning the PMP certification. Really a big loss for Project Management Community :-(

[PARAMASHIVAN]

my hotmail account is infect, it is sending out automnatic messages to all the ppl in my contact list,

how do i stop this crap :banghead: pls help :cry2:

[app_engine]

my hotmail account is infect, it is sending out automnatic messages to all the ppl in my contact list,

how do i stop this crap :banghead: pls help :cry2:

Temporarily remove the contact list. (You can add them later once this menace dies down)

Also, immediately change your password!

[PARAMASHIVAN]

my hotmail account is infect, it is sending out automnatic messages to all the ppl in my contact list,

how do i stop this crap :banghead: pls help :cry2:

Temporarily remove the contact list. (You can add them later once this menace dies down)

Also, immediately change your password!

thanks app_engine,

I will try that :)

[vithagan]

Nice to see this thread..

Any Mainframe techies here?? Just to know.. futurela thevai patta helpukku inga varalamla adhukku :wink:

[PARAMASHIVAN]

Nice to see this thread..

Any Mainframe techies here?? Just to know.. futurela thevai patta helpukku inga varalamla adhukku :wink:

Vithagan

people still use mainframes :shock: :roll:

[vithagan]

Nice to see this thread..

Any Mainframe techies here?? Just to know.. futurela thevai patta helpukku inga varalamla adhukku :wink:

Vithagan

people still use mainframes :shock: :roll:

:yes: Absolutely.. many financial institutions still run on Mainframes. :)

Naanum latest technologiesku maaranumnu romba try panren.. mudiyala :(

[VinodKumar's]

Good thread :clap:

[VinodKumar's]

Nice to see this thread..

Any Mainframe techies here?? Just to know.. futurela thevai patta helpukku inga varalamla adhukku :wink:

Vithagan

people still use mainframes :shock: :roll:

:yes: Absolutely.. many financial institutions still run on Mainframes. :)

Naanum latest technologiesku maaranumnu romba try panren.. mudiyala :(

Vithagan,

Yean kavala paduringa. Mainframes lam azhiyavae azhiyaathu :thumbsup:

[vithagan]

Vithagan,

Yean kavala paduringa. Mainframes lam azhiyavae azhiyaathu :thumbsup:

:cool2:

[rajraj]

Nice to see this thread..

Any Mainframe techies here?? Just to know.. futurela thevai patta helpukku inga varalamla adhukku :wink:

Vithagan

people still use mainframes :shock: :roll:

Paramashivan: There is a company still manufacturing mainframes ! :lol:

[PARAMASHIVAN]

Nice to see this thread..

Any Mainframe techies here?? Just to know.. futurela thevai patta helpukku inga varalamla adhukku :wink:

Vithagan

people still use mainframes :shock: :roll:

Paramashivan: There is a company still manufacturing mainframes ! :lol:

You mean IBM :lol2:

[Lambretta]

'hope this is the right thread to post this- anyone who could gimme info. on TECHNICAL WRITER jobs?

[PARAMASHIVAN]

'hope this is the right thread to post this- anyone who could gimme info. on TECHNICAL WRITER jobs?

This is not a recruitment agency :rotfl: Google it Shya'm :yes:

[Lambretta]

'hope this is the right thread to post this- anyone who could gimme info. on TECHNICAL WRITER jobs?

This is not a recruitment agency :rotfl: Google it Shya'm :yes:
:ty: so much for the help, Raghu! :roll: :sigh2:

Evolo udhaivi senjenga! :bow: :evil:

[rajraj]

'hope this is the right thread to post this- anyone who could gimme info. on TECHNICAL WRITER jobs?

Lambretta: Contact Badri ( Moderator) who is a technical writer in Australia ! :)

[PARAMASHIVAN]

'hope this is the right thread to post this- anyone who could gimme info. on TECHNICAL WRITER jobs?

Lambretta: Contact Badri ( Moderator) who is a technical writer in Australia ! :)

Yes he is a technical author of cricinfo.com :lol2:

[PARAMASHIVAN]

Has any one worked 'Extensively' on SQL server 2005 / 2008 cursors?

[kirukan]

Has any one worked 'Extensively' on SQL server 2005 / 2008 cursors?
S to some extent.It has to be used only if its really necessary.

Kirukan

[PARAMASHIVAN]

Has any one worked 'Extensively' on SQL server 2005 / 2008 cursors?
S to some extent.It has to be used only if its really necessary.

Kirukan

Thanks Kirukan

just saw your post now, I will post my query here soon. :)

[PARAMASHIVAN]

Hi all

I have a question about 'Duplicates' in SQL server database tables. Consider Table_A, with columns called Col_A, Col_B, Col_c

now if I run this query, will it only find the 'Duplicate values' in the 'Column' fields rather than 'Duplicate rows' in the table?

Select Col_A from Table_A
group by Col_A
having count(*) > 1

[app_engine]

now if I run this query, will it only find the 'Duplicate values' in the 'Column' fields rather than 'Duplicate rows' in the table?


For duplicate rows, you need to group by all columns.

[PARAMASHIVAN]

Oh Thanks App :)

[PARAMASHIVAN]

app_engine

Small problem, I can not 'Group by' any columns that are of Ntext, Image data types, Do I need to convert them all into 'Int' data type ?? :roll:

[app_engine]

I haven't used group by on image fields but it works for character and numeric fields. If this is for a browser app, I suggest you look up to such communities who may have a quick-fix:-)

[PARAMASHIVAN]

thanks app_engine na

[kirukan]

app_engine

Small problem, I can not 'Group by' any columns that are of Ntext, Image data types, Do I need to convert them all into 'Int' data type ?? :roll:

Just saw your post

You can try this
--nText
SELECT
Convert(varchar(8000),nTextCol ),
MAX(Amount)
FROM
TableA
GROUP BY
Convert(varchar(8000),nTextCol )

--Image

SELECT
Convert(varbinary(8),ImageCol ),
MAX(Amount)
FROM
TableA
GROUP BY
Convert(varbinary(8),ImageCol )

This may help you

[PARAMASHIVAN]

Kirukan

You have not included the 'Having' clause :roll:
I thought the 'having (*) > 1' clause is the 'Only' way to find duplicates ? :roll:

[kirukan]

Kirukan

You have not included the 'Having' clause :roll:
I thought the 'having (*) > 1' clause is the 'Only' way to find duplicates ? :roll:

My suggestion was for adding image and ntext in group by clause...

[PARAMASHIVAN]

Oh thanks :)

[PARAMASHIVAN]

Does any one here know much about SSRS in SQL server 2005 ?? esp adding parameters to a report?

thanks

[bingleguy]

Parama .... U could use Report Builder 2.0 which is supposedly user friendly for designing reports

[PARAMASHIVAN]

Parama .... U could use Report Builder 2.0 which is supposedly user friendly for designing reports

Is it a free download ? :roll: never used it before, this SSRS is so irritating, Stored procedures are 'much better' to query the databse !

[bingleguy]

I guess Ys ....

[PARAMASHIVAN]

Has any one used 'Core FTP ' software? Is it any good ? :roll:

[PARAMASHIVAN]

Some one pls help. See the query below. when I run it I want all the first name/ last name grouped together, I have included them in the group by aggregate, but the in the out put it does not list first_name, Last_name grouped together

USE hospices
SELECT
dbo._contact.reference,
dbo._contact.first_name,
dbo._contact.last_name,
don2.value_gross AS ValueGross,
don2.value_net AS ValueNet,
'Individual' AS donorType
FROM dbo._Donation don2
INNER Join dbo._Contact ON don2.supporter_id = dbo._Contact.id
WHERE don2.legacy_id is null
and dbo._Contact.dutchess_norfolk_contact=1
and dbo._Contact.deceased=0
and don2.value_gross >=500
group by
dbo._contact.reference,
dbo._contact.first_name,
dbo._contact.last_name,
don2.value_gross,
don2.value_net
ORDER BY ValueGross DESC

[app_engine]

Hey, you're grouping by all the fields that you're selecting.

What value then you want to see aggregated? Is it count(*) or something else?

Your query will only get a hardcoded value "Individual" for each entry of reference, firstname, lastname, valuegross and valuenet.

If you want sum of, say value gross or value net then you need to only group by ref / first name / last name and place the other inside the desired function.

[PARAMASHIVAN]

Thanks na, I orderd by last_name and it works.

:P

many thanks for 'prompt' reply :notworthy:

[PARAMASHIVAN]

use hospices_test
select contact_id,event_id
from _event_response
group by contact_id,event_id
having count(*)> 1


The above statement returned all the duplicates, I have to delete all of them. I tried subquery , it does not work, as it can not return more than one value.

Is there any other way I can do this, Please help, this is quite urgent

[app_engine]

Params,

I've not worked recently on Microsoft SQL-Server and you may possibly want to try their solution given here :
http://support.microsoft.com/kb/139444

For Oracle, I normally use "rowid" as in this sql below :

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3...) ;

I'm not sure whether MS-SQL uses rowid, give it a try :-)

[PARAMASHIVAN]

Params,

I've not worked recently on Microsoft SQL-Server and you may possibly want to try their solution given here :
http://support.microsoft.com/kb/139444

For Oracle, I normally use "rowid" as in this sql below :

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3...) ;

I'm not sure whether MS-SQL uses rowid, give it a try :-)

Hello anneh

I have seen the MSDN website, wasn't any help :( will try your code and let you know.

many thanks for help :)

[PARAMASHIVAN]

sorry it does not work , there is no rowid in sql server, it is called row_number and I tried that and it doesn't work :|

no worries thanks any way

[app_engine]

This article also talks about using a temp table (in the case of SQL Server) :

http://database-programming.suite101.com/article.cfm/how_to_dedupe_data_from_tables_in_sql_server

May be there are other ways, but if you have grants to create a new table, that is possibly a quick (and dirty) solution.

[app_engine]

Another link suggests this for SQL-Server (looks like it has the partition by function)


To remove duplicates, on SQL Server 2005 you can use this query:

WITH Dups
AS
(SELECT *, ROW_NUMBER() OVER(
PARTITION BY col1, col2, col3, col4, col5, col6, col7, col8,
col9, col10
ORDER BY col1) AS seq
FROM Table)
DELETE Dups
WHERE seq > 1;


Give it a try :-)

[PARAMASHIVAN]

Thanks na for all these trouble you went to get the aboev info :clap:

Going for short sleep now (team meeting) :fatigue: will give it a go later

thanks again :)

[PARAMASHIVAN]

App anneh and others

What is the point of using 'Begin try' and 'end try' in SQL ?

:ty: in advance

[PARAMASHIVAN]

And the same question about 'Begin catch' and 'end catch' ?? is it something to do with error messages in SQL ??

[app_engine]

Param,
Not an Oracle thingy - SQL Server stuff and I've not used.

This technet link seems to explain this very well:

http://technet.microsoft.com/en-us/library/ms179296.aspx

Sounds like a debugging tool.

[PARAMASHIVAN]

:ty: app anneh

I had a feeling it would be something to do with error handling :)

[kirukan]

:ty: app anneh

I had a feeling it would be something to do with error handling :)
Yes you are right its for error handling introduced from sql2005...

It will be very handy in procedures

begin try
begin tran
insert into Table values(57,'AAA','AA')
insert into Table values(58,'AAA','AA')
insert into Table values(59,'AAA','AA')
insert into Table values(57,'AAA','AA')
commit
select 'Success'
end try
begin catch
select 'Failure'
rollback
end catch

Any DML exceptions will be caught (primary key ,Arithmetic overflow,string or binary etc)
-
kirukkan

[sathya_1979]

Regarding Status Reports:
Anybody here had sent / have an idea to reflect the status of a project (Daily Report) in terms of dollar value rather than just % of work completed?
Any easy defining and tracking mechanish, pls PM me.

[PARAMASHIVAN]

:ty: kirukan for your 'detailed' explanation.

[dev]

How to resolve 403-forbidden error?... I am not able to access few sites and I get this 403 error.

[PARAMASHIVAN]

App na and others

I have 3 columns called A,B, and C , I want to merge all the data in these columns and display in one column, how do I do that?

I tried

Select A + B + C from my table

it works, but it does not leave any spaces between the content of A, B and C

pls help

:ty:

[PARAMASHIVAN]

How to resolve 403-forbidden error?... I am not able to access few sites and I get this 403 error.


Dev

http://en.wikipedia.org/wiki/HTTP_403

[app_engine]

App na and others
I have 3 columns called A,B, and C , I want to merge all the data in these columns and display in one column, how do I do that?


Not sure about sql-server again, but in Oracle, you can "pipe" them together with spaces:

select a||' '||b||' '||c

[kirukan]

App na and others

I have 3 columns called A,B, and C , I want to merge all the data in these columns and display in one column, how do I do that?

I tried

Select A + B + C from my table

it works, but it does not leave any spaces between the content of A, B and C

pls help

:ty:
If you want leave space between columns you can add as shown below
select A+' '+B+' '+C
if any of the columns already have trailing blank spaces
then use
rtrim(A)+ ' '+rtrim(B)+' '+rtrim(C)
or
ltrim(A)+ ' '+ltrim(B)+' '+ltrim(C)

This can be used only for Char and varchar fields.If the field is of integer or date then u need to convert and join.
Hope this helps

[PARAMASHIVAN]

many thanks kirukan it worked :)

[PARAMASHIVAN]

thanka app na :)

[PARAMASHIVAN]

Not sure about sql-server again, but in Oracle, you can "pipe" them together with spaces:

select a||' '||b||' '||c[/quote]

thanks :) , but 'pipe' does not work in sql server, it only works in MY SQL and Oracle .

[PARAMASHIVAN]

Hi All

This script I have written below is highly complicated, there is no syntax error, but it is not updating the tables it should be, this stored procedures have nested stored procedures within in,

My question is why is not updating the following tables, bookings, event_resource, and quote

please help

many thanks


USE [hospices_test]
GO
/****** Object: StoredProcedure [dbo].[hth_conference_import_scheduled] Script Date: 10/13/2010 10:54:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[hth_conference_import_scheduled]
AS

set nocount on
set ansi_warnings off

DECLARE @guids table(guid uniqueidentifier) -- Table Var for Imported Contacts
DECLARE @user uniqueidentifier
DECLARE @current_guid uniqueidentifier
DECLARE @contact_id uniqueidentifier
DECLARE @first_name nvarchar(255)
DECLARE @surname nvarchar(255)
DECLARE @address1 nvarchar(255)
DECLARE @address2 nvarchar(255)
DECLARE @Address3 nvarchar(255)
DECLARE @Town nvarchar(100)
DECLARE @Post_code nvarchar(20)
DECLARE @county nvarchar(100)
DECLARE @email nvarchar(150)
DECLARE @telephone nvarchar(100)
DECLARE @fax nvarchar(100)
DECLARE @m_status INT
DECLARE @last_name nvarchar(255)
DECLARE @special_needs_text nvarchar(255)
DECLARE @dietary_needs_text nvarchar(255)
DECLARE @job_title nvarchar(100)
DECLARE @event_resource_id_tue uniqueidentifier
DECLARE @event_resource_id_wed uniqueidentifier
DECLARE @event_resource_id_all uniqueidentifier
DECLARE @diet nvarchar(200)
DECLARE @pos int
DECLARE @need varchar(200)
DECLARE @lookup_value int
DECLARE @resource_request_id uniqueidentifier
DECLARE @quote_id uniqueidentifier
DECLARE @pricing_option varchar(200)
DECLARE @event_response_id uniqueidentifier
DECLARE @id uniqueidentifier
DECLARE @description nvarchar(255)
DECLARE @cost_price float
DECLARE @list_price float
DECLARE @quote_line_id uniqueidentifier


select @event_resource_id_tue = id
from _event_resource (nolock)
where reference = 'Conf10/Tues1'

select @event_resource_id_wed = id
from _event_resource (nolock)
where reference = 'Conf10/Wed1'

select @event_resource_id_all = id
from _event_resource (nolock)
where reference = 'Conf10/All'


--Get the guids from the import table
INSERT INTO @guids select id from _Conference_import

SELECT TOP 1
@current_guid = GUID
FROM @guids

WHILE @current_guid IS NOT NULL
BEGIN
IF (@current_guid not in (select id from _booking))
BEGIN

-- grab the data from the import
SELECT @first_name=first_name,
@surname=surname,
@address1=address_1,
@address2=address_2,
@address3=address_3,
@town=town,
@post_code=post_code,
@county=county,
@email=email,
@telephone=phone,
@fax=fax,
@user = created_by,
@diet= Dietary_req,
@pricing_option=Pricing_option
FROM _Conference_import
WHERE id = @current_guid


BEGIN
-- find the contact or create a new one
EXEC dbo.hth_get_contact_id
@first_name,
@last_name,
@post_code,
@email,
1,
@contact_id OUTPUT,
@m_status output

-- update the contacts addres info
EXEC hth_update_contact_details
@contact_id,
@address1,
@address2,
@address3,
@town,
@county,
@post_code,
@telephone,
@Fax,
@email,
'Conference import'

--Work out what event response to use
set @event_response_id = case
when @pricing_option like 'two days%'
then @event_resource_id_all
when @pricing_option like 'single day%16 november%'
then @event_resource_id_tue
when @pricing_option like 'single day%17 november%'
then @event_resource_id_wed
else null
end

--Create the booking record
EXEC dbo.hth_create_default_record @current_guid, 'booking', @user

UPDATE [_Booking]
SET [contact_id] = @contact_id
,[event_resource_id] = @event_response_id
,[company_id] = (select top 1
e.id
from _company as e
where e.name = b.Organisation
)
,[job_title] = b.Job_title
,[dietary_needs_text] = b.Other_diet
,[special_needs_text] = b.Special_needs
,[mailing_address_type] = 5
,[mailing_address1] = b.Address_1
,[mailing_address2] = b.Address_2
,[mailing_address3] = b.Address_3
,[mailing_address_town] = b.Town
,[mailing_address_county] = b.County
,[mailing_address_postcode] = b.Post_code
,[correspondance_address1] = b.Address_1
,[correspondance_address2] = b.Address_2
,[correspondance_address3] = b.Address_3
,[correspondance_address_town] = b.Town
,[correspondance_address_county] = b.County
,[correspondance_address_postcode] = b.Post_code
,[mailing_address_iso_country_code] = isnull((
select top 1
d.iso_name
from Country_Locale as c, Country as d
where c.locale = 2057
and d.country_code = c.country_code
and c.country_name = b.Country
),'GBR')
,[booking_tel] = b.Phone
,[contact_type] = 1
,[booking_payment_method] = 2 --Invoice
,[booking_email] = b.Email
,[import_organisation] = b.Organisation
from _Booking as a, _Conference_import as b
where a.id = b.id
and a.id = @current_guid



--Add the lookups for dietary requirements
set @diet = isnull(@diet,'') + ','
set @pos = charindex(',',@diet)

while @pos > 1
begin
set @need = left(@diet,@pos -1)
set @diet = right(@diet,len(@diet)-@pos)
set @pos = charindex(',',@diet)

set @lookup_value = null

select @lookup_value = code
from [l_booking dietray requirements]
where description = @need

if @lookup_value is not null
begin
insert into entity_lookup
select @current_guid,
@lookup_value,
newid(),
0,
'dietary_needs'
end
end

--Add the resource request
SET @resource_request_id = newid()
EXEC dbo.hth_create_default_record @resource_request_id, 'resource_request', @user

UPDATE [_Resource_Request]
SET [class] = 2
,[contact_id] = @contact_id
,[places_requested] = 1
,[event_resource_id] = @event_response_id
,[booking_id] = @current_guid
,[request_no] = 1
,[waiting] = 0
WHERE id = @resource_request_id


--Add the Quote (header)
set @quote_id = newid()
EXEC dbo.hth_create_default_record @quote_id, 'quote', @user

UPDATE [_Quote]
SET [total_price] = 0
,[total_discount] = 0
,[total_margin] = 100
,[total_cost] = 0
,[quote_date] = getdate()
,[net_price] = 0
,[active_quote] = 1
,[contact_id] = @contact_id
,[source_entity] = 'Booking'
,[source_id] = @current_guid
WHERE id = @quote_id

-- Add the quote line

SELECT @id = id,
@description = DESCRIPTION,
@cost_price = cost_price,
@list_price = list_price
FROM product
WHERE ID = CASE
WHEN @pricing_option like 'Two days%standard rate%' then '271FB3FC-610E-4213-A3C3-AEEF6F1C74D0'
WHEN @pricing_option like 'Two days%discounted rate%' then '62354119-2368-4D15-9C9B-12456D647EED'
WHEN @pricing_option like 'Single day%17 November' then '4F1C6C79-840C-4775-B36F-238E298F47CE'
WHEN @pricing_option like 'Single day%16 Novembe' then 'F2CD6A29-2299-4E44-A7AF-238788866771'
ELSE NULL
END


SET @quote_line_id =newid()

EXEC dbo.hth_create_default_record @quote_line_id, 'quote_line', @user

UPDATE [_Quote_Line]
SET [customer_price] = @list_price
,[list_price] = @list_price
,[cost] = @cost_price
,[unit_customer_price] = @list_price
,[unit_list_price] = @list_price
,[unit_cost] = @cost_price
,[margin] = 0
,[margin_pc] = 100
,[discount] = 0
,[discount_pc] = 0
,[quantity] = 1
,[product_id] = @id
,[quote_id] = @quote_id
,[line_no] = 1
,[description] = @description
,[bacs] = 1
WHERE id = @quote_line_id

--TODO Calculate the quote value and update the quote header
UPDATE [_Quote]
SET [total_price] = (select sum(list_price) from _quote_line where quote_id = @quote_id)
,[total_cost] = (select sum(cost) from _quote_line where quote_id = @quote_id)
,[net_price] = (select sum(list_price) from _quote_line where quote_id = @quote_id)
WHERE id = @quote_id


END

END

--delete the current record from the temp table
DELETE
FROM @guids
WHERE guid = @current_guid

SET @current_guid = NULL

SELECT TOP 1
@current_guid = guid
FROM @guids
END

[app_engine]

Param,

Where do you "commit"?

:-)

[PARAMASHIVAN]

Param,

Where do you "commit"?

:-)

hi na :)

Do I need a commit? when I have not got a 'begin tran' command :roll:

[dev]

How to resolve 403-forbidden error?... I am not able to access few sites and I get this 403 error.


Dev

http://en.wikipedia.org/wiki/HTTP_403

PS, I guess it's coz of too much traffic... problem automaticaa solve aagidichu...:) Thanks...:)

[PARAMASHIVAN]

No Probes, Dev :)

[PARAMASHIVAN]

I am trying to do a bulk insert

BULK INSERT dbo.SSIS
FROM 'c:\conf2010 to be imported.csv'

but I keep getting an error saying

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.

any ideas pls ?

[PARAMASHIVAN]

Kirukan or others

Does any one know where Relication details are kept (in which database adnin which tables) ?

I thought it would be in MSDB database, but I cant find any tables relating to Subscription, publishing and distribution ?

thanks

[kirukan]

Kirukan or others

Does any one know where Relication details are kept (in which database adnin which tables) ?

I thought it would be in MSDB database, but I cant find any tables relating to Subscription, publishing and distribution ?

thanks

Hope u got answer for this if not....

By default it will be stored in database called distribution in distributor database server.User can customize his own...
http://msdn.microsoft.com/en-us/library/ms189755(SQL.90).aspx

Kirukan

[PARAMASHIVAN]

Thanks Kirukan :)

BTW Do you know much about SSIS packages in SQL server 2008 ?

I have a package that Copies 10 tables from a Database on SQL server 2005 to SQL server 2008 .

Now I have chosen the option to Drop and recreate tables as well as delete rows in the destination tables before copying the data accross, this should eliminate Primary key violations right? but I am still getting them

any ideas

thanks

[venkkiram]

Anyone familiar with OWSM? If so, need clarification for some of my queries in understanding the conceptual design.

[kirukan]

Thanks Kirukan :)

BTW Do you know much about SSIS packages in SQL server 2008 ?

I have a package that Copies 10 tables from a Database on SQL server 2005 to SQL server 2008 .

Now I have chosen the option to Drop and recreate tables as well as delete rows in the destination tables before copying the data accross, this should eliminate Primary key violations right? but I am still getting them

any ideas

thanks

Ideally it shud eliminate Primary key violation...you physically drop the table and check the same.If it persist even after that then compare the structure of the source and destination tables...it may be a genuine primary key violation...we use SQL compare for this.....

கிறுக்கன்

[PARAMASHIVAN]

Thanks Kirukuan

I will compare the structures of the source table and destination table and see

[PARAMASHIVAN]

I have a contact table, and a course table. Now some of them have attended the course and some have not, how would I find the ones who have not attended the course ?

would be be contact left outer join, right outer join??

pls advice

[app_engine]

There are only two tables. So, if you place the field of contact table on the left hand side of your query, then it should be left outer join.

i.e. say, if you join as
contact.emplid = course.emplid, then it has to be contact.emplid (+) = course.emplid, in Oralce.

In generic SQL, it can be written as :

select * from contact
left outer join course on
contact.emplid = course.emplid

[kirukan]

I have a contact table, and a course table. Now some of them have attended the course and some have not, how would I find the ones who have not attended the course ?

would be be contact left outer join, right outer join??

pls advice

There are many ways to achieve this please find below two of them

select c.* from contacts c left outer join course r on(c.contactid=r.contactid) where r.contactid is null

select * from contacts where contactid not in(select contactid from course)


Hope this helps
-
கிறுக்கன்

[PARAMASHIVAN]

Many thanks App anne and kirukan, much appreciated :thumbsup:

[PARAMASHIVAN]

Hi all

Nvarchar should support $$ sings right ?? I am doing a data import package from a csv file, and one of the columns contains this $$ sign? when I import, it fails. saying data concversion error.

The field this column being imported is of navarchar(250) data type.

any ideas

[PARAMASHIVAN]

Hi all

Is it it worth to be Microsoft certified to improve your job prospects ? :roll:

If any one is Microsoft certified, please advice ?

[littlemaster1982]

There are lot of certifications by Microsoft, Param. Which one you are asking about?

It helps to some extent in job search, if you ask me. If you get something like MVP or MCSE, it's definitely helpful.

[Nerd]

Hi all

Is it it worth to be Microsoft certified to improve your job prospects ? :roll:

If any one is Microsoft certified, please advice ?
I was Microsoft Certified once ( I say once because most certifications expire in two years). MCAD I think, I even forgot what was it called. I need to see the certificate. Yes, it sure helps you in marketing your resume and it has a side effect as well. People would expect a lot in the interviews :lol:

[PARAMASHIVAN]

There are lot of certifications by Microsoft, Param. Which one you are asking about?

It helps to some extent in job search, if you ask me. If you get something like MVP or MCSE, it's definitely helpful.

LM

I am talking MSCE, MCP etc . I Have a BSc(hons) 2.1 and MSc in Computing still most companies are asking for such Microsoft Certifications :| , thinking of doing some, but just cant be bothered to study :lol:

[PARAMASHIVAN]

( I say once because most certifications expire in two years).


Really :shock: so every two years you have to 'top up your self' :lol:




has a side effect as well. People would expect a lot in the interviews :lol:

:shaking: :lol:

[littlemaster1982]

There are lot of certifications by Microsoft, Param. Which one you are asking about?

It helps to some extent in job search, if you ask me. If you get something like MVP or MCSE, it's definitely helpful.

LM

I am talking MSCE, MCP etc . I Have a BSc(hons) 2.1 and MSc in Computing still most companies are asking for such Microsoft Certifications :| , thinking of doing some, but just cant be bothered to study :lol:

In which Microsoft technology you want to get certified? You will get an MCP if you just pass one exam. For MCSE, you have to pass 6 exams (4 mandatory papers + 2 electives/optional). MCSE certainly adds value to your resume. In the end, it all boils down to your skill in explaining what you know in interviews.

[PARAMASHIVAN]

LM

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

[littlemaster1982]

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.

[PARAMASHIVAN]

hmm Thanks LM :)

[varunlss12]

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.

[Puliyan_Biryani]

Found this link.
http://en.kioskea.net/forum/affich-118712-blue-screen-while-trying-to-install-xp


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.

[littlemaster1982]

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

[PARAMASHIVAN]

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 ??

[app_engine]

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 :-)

[PARAMASHIVAN]

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

[PARAMASHIVAN]

Has any one worked with

1) Active/Active clustered SQL Servers?

2) Active/Passive clustered SQL servers using DMware ?

[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 ?

[app_engine]

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/10MinuteSolution/16597/1954


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;

[kirukan]

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

-
கிறுக்கன்

[PARAMASHIVAN]

test test test

[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 ?

[app_engine]

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

[app_engine]

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

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

[PARAMASHIVAN]

Thanks app Na

but would the above script work in SQL Server?

[app_engine]

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

Try it out :-)

[PARAMASHIVAN]

Thanks App annEh :)

[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 ...

[kirukan]

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

[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

[kirukan]

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.

[PARAMASHIVAN]

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

[ramdas2005]

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

[PARAMASHIVAN]

Welcome to the thread Ramdas :)

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

[ramdas2005]

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.

[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

[ramdas2005]

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/?

[PARAMASHIVAN]

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 :)

[ramdas2005]

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

[PARAMASHIVAN]

Ramdas

I meant Private Message :)

[ramdas2005]

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

[PARAMASHIVAN]

Hi all

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

[kirukan]

Yes Param...

[PARAMASHIVAN]

Thanks Kirukun, I managed to resolve the matter :)

[19thmay]

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

[app_engine]

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

[PARAMASHIVAN]

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 ?

[PARAMASHIVAN]

Guys

Dont worry , I fixed it :) I used the 'Cast' statement for both values and it returned a Decimal value!

[kirukan]

select 1.000/2.000
select (1+.00)/(2+.00) another way of handling this.

[PARAMASHIVAN]

Thanks Kirukan :)

[Dinesh84]

Anybody using Ubuntu 10.04 here ? how to make windows applications work in ubuntu? i hear that there is a package called Wine HD.. but not sure how to use it.. :?

[PARAMASHIVAN]

Anybody using Ubuntu 10.04 here ? how to make windows applications work in ubuntu? i hear that there is a package called Wine HD.. but not sure how to use it.. :?

Sorry Don't know dinesh

[app_engine]

Quick question for Peoplesoft designers / developers if anyone is around...

How to edit German language labels / translate values on App designer?
(Note - not backend or through PIA but on app designer)

[PARAMASHIVAN]

Any one good at using 'Expressions' within SSRS 2008 ?

[sakaLAKALAKAlaa Vallavar]

Anybody using Ubuntu 10.04 here ? how to make windows applications work in ubuntu? i hear that there is a package called Wine HD.. but not sure how to use it.. :?
hinesh, Are you new to ubuntu? Then click the Applications Menu (Alt+F1) and you will find Ubuntu Software Center, as the last item. Click open it and enter 'wine' in search bar. Install the very 1st software which has wine glass icon. Once its over, You will find a 'Wine' item listed in your applications. All your windows apps go there

[PARAMASHIVAN]

Folks

Has any one worked with Expressions within SSRS 2008? I have an annoying problem. I have a column called (x) with integer values, It has data as follows

X
-----

12
33
44
89
78
38
90

All I want to do is Count all the numbers which are more than 50 , I have tried this

=count(iif(Fields!x,value > 50,1,0) , it does not seem to work, and google aint helping :banghead:, if any one knows a way pls share it .

thanks

[app_engine]

Peoplesoft HR "row level security" and hard coding

I've done only role based security stuff before and never had to indulge in this department tree / row level security business. Even with the current client, not exactly in my domain - but after seeing a couple of coworkers struggling last evening, volunteered to indulge in. Resulted in a crash course of row-level-security as well as staying late for a couple of hours.

The funny thing that caused the non-working was the naming of the "department tree" that is used for the row level security.

These people had their own fancy name for the tree. Peoplesoft insists on the name to be only "DEPT_SECURITY" :lol: (The uniqueness of each tree is ensured by other fields - setid, effdt)...once this was pointed out to them from the appropriate Peoplebook page, all went good :-)

[venkkiram]

Is your computer “male” or “female”??

1. Open Notepad

2. Type the following line in notepad:

CreateObject("SAPI.SpVoice").Speak"I love you"

3. Save file as: computer_gender.vbs

4. Run the file ... If you hear a male voice, you have a boy :) If you hear a female voice, you have a girl :)

Note: Turn on the speaker before running the file

Must Try :)

[PARAMASHIVAN]

Venki

The above post should be in the Jokes thread :)

[P_R]

People who can follow Hindi please watch this video
http://www.youtube.com/watch?v=9izUKE5bN0U


http://www.youtube.com/watch?v=9izUKE5bN0U

[P_R]

Same video with English subtitles: http://www.youtube.com/watch?v=ApQlMm39xr0&feature=share

[sakaLAKALAKAlaa Vallavar]

Microsoft has disclosed one of its research project - 'Drawbridge' operating-system
http://www.zdnet.com/blog/microsoft/microsoft-goes-public-with-its-drawbridge-operating-system-research-project/11009?tag=mantle_skin;content

http://i.zdnet.com/blogs/drawbridgelibraryos.png

Can any MS professional explain me in lay terms what is library OS approach and its specialty

thanks :)

[sakaLAKALAKAlaa Vallavar]

Want to listen ti IIT/IISc Engineering lectures?!? NPTEL that is National Programme on Technology Enhanced Learning, a Govt of India HRD Initiative has brought 90 such lectures. They are available online for free and also available for sale in DVD format.

See here :-
http://www.nptel.iitm.ac.in/
http://www.btechguru.com/nptel/courses.php?branch=ECE

[sakaLAKALAKAlaa Vallavar]

Advanced tools running on M-Lab to test your Internet connection - http://www.measurementlab.net/measurement-lab-tools

Useful to diagnose your broadband connection. Very useful for IT Admins

[PARAMASHIVAN]

Hi All

I want to know , what permissions and DBA Roles each SQL logins have againts each databases in SQL server 2008(r2), any ways to find out?

I tried this script (see below) , and I am not sure that this will bring the correct results


CREATE procedure [dbo].[List_DBRoles]
(
@database nvarchar(128)=null,
@user varchar(20)=null,
@dbo char(1)=null,
@access char(1)=null,
@security char(1)=null,
@ddl char(1)=null,
@datareader char(1)=null,
@datawriter char(1)=null,
@denyread char(1)=null,
@denywrite char(1)=null
)

as

declare @dbname varchar(200)
declare @mSql1 varchar(8000)

CREATE TABLE #DBROLES

(DBName sysname not null,
UserName sysname not null,
db_owner varchar(3) not null,
db_accessadmin varchar(3) not null,
db_securityadmin varchar(3) not null,
db_ddladmin varchar(3) not null,
db_datareader varchar(3) not null,
db_datawriter varchar(3) not null,
db_denydatareader varchar(3) not null,
db_denydatawriter varchar(3) not null,
Cur_Date datetime not null default getdate()
)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s

Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Select * from #DBRoles

where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND
((@user is null) OR (UserName LIKE '%'+@user+'%')) AND
((@dbo is null) OR (db_owner = 'Yes')) AND
((@access is null) OR (db_accessadmin = 'Yes')) AND
((@security is null) OR (db_securityadmin = 'Yes')) AND
((@ddl is null) OR (db_ddladmin = 'Yes')) AND
((@datareader is null) OR (db_datareader = 'Yes')) AND
((@datawriter is null) OR (db_datawriter = 'Yes')) AND
((@denyread is null) OR (db_denydatareader = 'Yes')) AND
((@denywrite is null) OR (db_denydatawriter = 'Yes'))


exec List_DBRoles

[PARAMASHIVAN]

Has any one worked with Multi Value variables in SSRS 2008 ?

[PARAMASHIVAN]

Hi All,

I need some help please :(

I have the following Stored procedure



USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[Sp_listjobhist] Script Date: 01/05/2012 10:02:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_listjobhist]
@START DATETIME,
@END DATETIME,
@JOBNAME varchar(255)


AS
BEGIN
SELECT sysjobhistory.server,
sysjobs.name
AS
job_name,
CASE sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE '???'
END
AS
run_status,
Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
Substring(CONVERT(VARCHAR
(8), run_date), 5, 2) + '-' +
Substring(CONVERT(VARCHAR(
8), run_date), 7, 2), '')
AS
[Run DATE],
Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
+
Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
)
+
':' +
Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
AS
[Run TIME],
Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
':' +
Substring(CONVERT(VARCHAR(7), run_duration+1000000),
4,
2)
+ ':' +
Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
''
) AS
[Duration],
sysjobhistory.step_id,
sysjobhistory.step_name,
sysjobhistory.MESSAGE
FROM msdb.dbo.sysjobhistory
INNER JOIN msdb.dbo.sysjobs
ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
WHERE sysjobhistory.run_date >= Datepart(yyyy, @START) * 10000 +
Datepart(mm, @START) * 100 +
Datepart
(dd, @START)
AND sysjobhistory.run_date <= Datepart(yyyy, @END) * 10000 +
Datepart(mm, @END) * 100 +
Datepart
(dd, @END)
AND sysjobs.name IN (@JOBNAME)

ORDER BY instance_id DESC
END


(It has 3 variable inputs, @start, @end, @Jobname) , when I exceute it like this

exec Sp_listjobhist '2012-01-03', '2012-01-05', (DARHelpDesk - Update Users,DARHelpDesk - Weekly Report), it comes with the an error.

[PARAMASHIVAN]

Has any one worked with SQL Cluster administrator?

[PARAMASHIVAN]

Any one worked with setting up websites within IIS?

[PARAMASHIVAN]

Any SSRS 2012 developers here ?

[selvakumar]

Param, Chances are high that you won't get the required help here. You may have to use your SAID and approach the software vendor. Given that it is Microsoft and that they have sound documentation and user guides for all their products, your success rate would be much higher if you approach the vendor. If you are in the middle of an evaluation phase, engage a VAR or Microsoft Partner to assist you during the evaluation phase. And not to forget the volume of books available on this topic.

[PARAMASHIVAN]

Param, Chances are high that you won't get the required help here. You may have to use your SAID and approach the software vendor. Given that it is Microsoft and that they have sound documentation and user guides for all their products, your success rate would be much higher if you approach the vendor. If you are in the middle of an evaluation phase, engage a VAR or Microsoft Partner to assist you during the evaluation phase. And not to forget the volume of books available on this topic.

Thanks Selva for the response, I realise it has just been released, I am using 2008 at the moment, I just wanted to If any one found any major differences between the two. :)

[selvakumar]

Welcome Param. I am not a SQLServer guy :P We rely on "ORACLE".

[PARAMASHIVAN]

Welcome Param. I am not a SQLServer guy :P We rely on "ORACLE".

Cool, I have used Oracle long back, but over here in UK, The Market for Orcale has gone down , while the market of SQL server has risen very high due to the introduction of " Business intelligence " aspect.

[PARAMASHIVAN]

Need some help folks !

I want to back a database based on most reacent created date (crdate) in SQL server. Basically I want backup a database based on this query


(select name,crdate from sysdatabases
where crdate in (select max(crdate) from sysdatabases
where name like 'wslog%'))

any ideas as to how to do this?

[PARAMASHIVAN]

Any one used "Partition by " function to identify Duplicate rows in SQL server 2014 Databases please ?

[kirukan]

Yes I have used but in 2008.Hope its same in 2014 as well.

kirukan

[PARAMASHIVAN]

Yes I have used but in 2008.Hope its same in 2014 as well.

kirukan

Thanks, I think it is better to identify duplicates using this than the traditional "having count (0) > 1 " , right ?

[kirukan]

Thanks, I think it is better to identify duplicates using this than the traditional "having count (0) > 1 " , right ?

To me it depends if you just need a list traditional itself will work better if you are going to do further analysis then partition will help.Below is an example from supplier master to identify two different suppliers given same beneficiary name for payment.

--With Partition
WITH party as(
SELECT ROW_NUMBER() OVER (PARTITION BY party_benfname
ORDER BY ptyh_id DESC ) RN,party_benfname,ptyh_id
FROM party_master)

select * from party where party_benfname in(select party_benfname from party where RN>1)

--Old style
select party_benfname,Ptyh_id from party_master where party_benfname in(
select party_benfname from party_master group by party_benfname having count(party_benfname)>1)

[PARAMASHIVAN]

Thanks Kirukan, In my scenario "Partition by " works better :)

[kirukan]

Thanks Kirukan, In my scenario "Partition by " works better :)
I am looking for a logical reason behind sql performance.It would be gr8 if you could throw some light on this.

Below is a query which was having performance issue. Removing the function fn_fraction has improved the performance from 20 sec to 2 sec. But My query is why the Table T is fully scanned when records are filtered in the inner join. In the profiler fn_fraction method is called for the number of rows in T table.
Output of this select is 0 rows. But T has 100k rows

G primary key = intCmpcd, intGlobalId, intInvId
I Index Key= intCmpcd, intAdjId
I Primary key= intCmpcd, intAssetId, intAssetTag
T Primary key= intCmpcd, intAssetId, intAssetTag

select *
FROM FI_Transaction_Inv_Dtl G
INNER JOIN FA_Asset_TagDtl I ON (I.intCmpCd=G.intCmpCd AND I.intAdjId=G.intInvId )
INNER JOIN FA_Asset_TagHdr T ON (T.intCmpCd=I.intCmpCd AND T.intAssetId=I.intAssetId AND T.intAssetTag=I.intAssetTag)
WHERE G.intCmpCd=1 AND G.intGlobalId=3463
AND ABS(dbo.fn_fraction(T.decAcqCostPO*1,1)-T.decAcqCost)<=1
As I could not get a logical answer for this its bothering me for the past 2,3 days.

[PARAMASHIVAN]

Hello Kirukan

What version of SQL server are you using ? 2012/2014/2106 ? Did you put the query into "Query execution planner" or "Database tuning adviser " ?

[kirukan]

Its running in 2008.

Yes checked the execution planner and Table T take 71% cost.Is it normal to scan entire table even though there will be no matching records based on Table G and I.
select *
FROM FI_Transaction_Inv_Dtl G
INNER JOIN FA_Asset_TagDtl I ON (I.intCmpCd=G.intCmpCd AND I.intAdjId=G.intInvId )
INNER JOIN FA_Asset_TagHdr T ON (T.intCmpCd=I.intCmpCd AND T.intAssetId=I.intAssetId AND T.intAssetTag=I.intAssetTag)
WHERE G.intCmpCd=1 AND G.intGlobalId=3463
AND ABS(dbo.fn_fraction(T.decAcqCostPO*1,1)-T.decAcqCost)<=1

[PARAMASHIVAN]

Its running in 2008.

Yes checked the execution planner and Table T take 71% cost.Is it normal to scan entire table even though there will be no matching records based on Table G and I.
select *
FROM FI_Transaction_Inv_Dtl G
INNER JOIN FA_Asset_TagDtl I ON (I.intCmpCd=G.intCmpCd AND I.intAdjId=G.intInvId )
INNER JOIN FA_Asset_TagHdr T ON (T.intCmpCd=I.intCmpCd AND T.intAssetId=I.intAssetId AND T.intAssetTag=I.intAssetTag)
WHERE G.intCmpCd=1 AND G.intGlobalId=3463
AND ABS(dbo.fn_fraction(T.decAcqCostPO*1,1)-T.decAcqCost)<=1


Have you tried partitioning the table ? is it a heap table ?