Guys
Dont worry , I fixed it :) I used the 'Cast' statement for both values and it returned a Decimal value!
Printable View
Guys
Dont worry , I fixed it :) I used the 'Cast' statement for both values and it returned a Decimal value!
select 1.000/2.000
select (1+.00)/(2+.00) another way of handling this.
Thanks Kirukan :)
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.. :?
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)
Any one good at using 'Expressions' within SSRS 2008 ?
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
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
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 :-)
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 :)
Venki
The above post should be in the Jokes thread :)
People who can follow Hindi please watch this video
http://www.youtube.com/watch?v=9izUKE5bN0U
http://www.youtube.com/watch?v=9izUKE5bN0U
Same video with English subtitles: http://www.youtube.com/watch?v=ApQlM...&feature=share
Microsoft has disclosed one of its research project - 'Drawbridge' operating-system
http://www.zdnet.com/blog/microsoft/...e_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 :)
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
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
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
Has any one worked with Multi Value variables in SSRS 2008 ?
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.
Has any one worked with SQL Cluster administrator?
Any one worked with setting up websites within IIS?
Any SSRS 2012 developers here ?
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.
Welcome Param. I am not a SQLServer guy :P We rely on "ORACLE".
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?
Any one used "Partition by " function to identify Duplicate rows in SQL server 2014 Databases please ?
Yes I have used but in 2008.Hope its same in 2014 as well.
kirukan
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)
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.
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 " ?
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