Wednesday, October 10, 2012

How to format amount do not display numbers after decimal point? e.g. if the value in the database 139000.82 what function to use (other than FormatCurrency)to display amount in the SSRS 2008 R2 report such as $139,000?


Please follow the below steps to achieve the answer.




Next





Set the expressions as like below.


="Post Code:" + Fields!Post_CODE.Value +  VbCrLf  + "Area:" + Fields!AREA.Value + VbCrlf +
 "Count of Admission Number:" + cstr(Count(Fields!ADMISSION_NO.Value, "DataSet1")) + VbCrlf +
 "Estimated VALUE:" + cstr(Format(Sum(Fields!ESTIMATED_VALUE.Value, "DataSet1"),"C0")) +  VbCrLf  +
  "Averege of Each Employee VALUE:" + cstr(Format(Sum(Fields!ESTIMATED_VALUE.Value,
   "DataSet1")/Count(Fields!ADMISSION_NO.Value, "DataSet1"),"C0"))


That’s it.

How to drill through from parent report to child report in SSRS 2008 R2?


Right click the textbox and set the steps as shown below.





 Click action->Go to Report->Give Report Name->select any parameters to pass from parent report to child report.



That’s it.

How to set exception based on some amount value in % total field or Highlight anything >10% by another colour in SSRS 2008R2?


Right click the total textbox and set as like below.




Next go to backgroundcolor property of that textbox.




Set Expression as like below.




=iif( iif(iif(isnothing(Sum(Fields!Amount.Value,"Year")),0,Sum(Fields!AMOUNT.Value)/Sum(Fields!AMOUNT.Value,"Year"))=0,nothing,Sum(Fields!AMOUNT.Value)/Sum(Fields!AMOUNT.Value,"Year"))>0.1 ,"Green","White")

Thats it.

How to set Tooltip for Spark line chart in SSRS 2008R2 and how to format the amount in zero decimals?


Right click spark line chart and Go to ‘Series Property’ of Spark line and set tool tip as per below screenshot.






Set Tooltip as per the following image.






=Format(Sum(Fields!Amount.Value),"C0")

CO denotes zero decimlas value.

That's it.



How to show values of parameters chosen in the dropdown list on Textbox in the SSRS 2008 R2 report?


Drag and drop text box and go to properties--> do the steps as below.





Select the parameter from parameters category of Expression Tab and select the appropriate value
Parameters!employeer_post_code.Value
I got this by using join Join (Parameters!employeer_post_code.Value, ", ")
Finally click OK.

That’s’ it.

How do i format the currency field as £ UK pounds sterling for the entire SSRS 2008 R2 report?


Set as language properties under report properties. It should be set to English UK (en-GB) as shown below.


o/p:




Query to find who has access to a report folder or a particular report in SSRS2008 R2?


SELECT
            distinct(C.Path),
            U.UserName
FROM
            Catalog C
INNER JOIN
            PolicyUserRole PUR
            ON
            C.PolicyID = PUR.PolicyID
INNER JOIN
            Users U
            ON
            PUR.UserID = U.UserID
           
SELECT
            distinct(C.Path),
            U.UserName
FROM
            Catalog C
INNER JOIN
            PolicyUserRole PUR
            ON
            C.PolicyID = PUR.PolicyID
INNER JOIN
            Users U
            ON
            PUR.UserID = U.UserID where Path like '/POC/Farm Dashboard POC'

How to find when Microsoft going to end the support for any SQL server versions?


Please look into the below website http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL
Here you can search any Microsoft Products lifecycle as like below.


[Execute SQL Task] Error in SSIS Merge statement


[Execute SQL Task] Error: Executing the query "EXEC PC_Procedure_name1
EXEC PC_Procedure_name2..." failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Resolution:

One of the options is presence of duplicates, before running the merge statement, please try to check the duplicates by running below query by combining more than 1 columns.
Scripts for finding duplicates by combining n number of columns
SELECT
q.KEY_FIELD,
COUNT(*)
FROM
(
SELECT
--(column1 ,column2,coulmn3)
CAST(column1 AS VARCHAR)
+CASTcolumn2 AS VARCHAR)
+CAST(coulmn3 AS VARCHAR)
 KEY_FIELD,*
 FROM Sourcetablename
 --where CAST(column1 AS VARCHAR)+CAST+CASTcolumn2 AS VARCHAR)+CAST(coulmn3 AS -----VARCHAR)='12059754183842012-08-26 00:00:00'
 ) q
 GROUP BY q.key_field
 ORDER BY COUNT(*) desc

 --307130
 SELECT COUNT(1) FROM  sourcetablename
 --10510844
 --10510844
Then change the MERGE Statement by adding one more column in the ON Clause to do perform merge.

[OLE DB Destination] Error in SSIS:

[OLE DB Destination [9]] Error: There was an error with input column
SYSTEM_DATE_TIME (130) on input "OLE DB Destination Input" (22). The column status returned was: "The
Value could not be converted because of a potential loss of data."
Resolution:
Check any invalid date value in source csv file, if yes then, change it to 01\01\1753.(Or try to change the
 Invalid date value to valid date value).

That’s all.

[SSIS.Pipeline] Error :

[SSIS.Pipeline] Warning: The output column "Columnname" (127) on output "Flat File Source Output" (2)
 and component "Flat File Source" (1) is not subsequently used in the Data Flow task. Removing this
 unused output column can increase Data Flow task performance.
Resolution:
Check the all columns in source table match with columns in destination table, if not uncheck extra column
 From the 'Available External Columns' in the Flat file Source Editor by right clicking the Flat file Source.

Database MAIL( sp_send_dbmail) Error in SQL2008 R2:


The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-08-31T18:10:23). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay). 

While sending mail from SQL job through sp_send_dbmail procedure in SQL2008R2, i am getting above error.2 identical emails are send (with same distribution list and same body), with one minute of difference.

Resolution:

One of the email id in the recipients causing the above issue. So I have resolved that issue by changing the wrong mail id in the recipients list to correct mail id.

Eg .(if SMTP server related to your office smtp server. Then sp_send_dbmail will not send mail to another office server mail id in the recipients list.)

SSIS2008R2 Error: 0xC0209303 at Package:



When i trying to load data from MS access (MDB) to SQL ,i am getting the below error.

Error: 0xC0209303 at Package, Connection manager "mdbfilename": SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available.  Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
Error: 0xC020801C at Data Flow Task mdbfilename, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager " mdbfilename " failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task mdbfilename, SSIS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

Resolution:

It seems that SSIS project are now on a 64 bit machine. And that there is no 64 bit OLE DB provider Microsoft.Jet.OLEDB.4.0.So the fix is in running this project under 32 bit modes

Go to the SSIS project properties page
          à Then Click Debugging under configuration properties
          àSelect “Run64BitRuntime” property to false
          àSave the project
                              ->RUN Build again
          àThen check the package runs to a successful completion.

That's it.

How to find the SQLlogins have proper password except blank,samelogin name as password And ‘password’ as password?



--To find the blank password set for any sql logins.
--PWDCOMPARE Hashes a password and compares the hash to the    hash of an existing password.   PWDCOMPARE can be used to search for blank SQL Server login passwords or common weak passwords.

SELECT name FROM sys.sql_logins
WHERE PWDCOMPARE('', password_hash) = 1 ;

--To find the password set as like the same name of sql login name    for any sql logins.

SELECT name FROM sys.sql_logins
WHERE PWDCOMPARE(name, password_hash) = 1 ;

--To find the password set as like 'password' for sql logins

SELECT name FROM sys.sql_logins
WHERE PWDCOMPARE('password', password_hash) = 1 ;

Hope you can search for blank SQL Server login passwords or common weak passwords with above query.

Wednesday, June 27, 2012

SSRS Configuration step by step(SQL SERVER REPORTING SERVICES)

Step1.Click Start->All Programs->Microsoft SQL Server->Configuration Tools->Reporting Service Configuration Manager.

Reporting service configuartion dialog box opens(Below Figure )




Step2.Specify the Server Name and report server instance.Click Connect buttton.

Reporting Service Configuaration Manager window opens to show the status of  current report server instance.




Step3.Select Service account tab from the left pane of RSCM window to open the service account page.



You can get an option choose service account by choosing the radio button in the above figuure and Click Apply Button.

Step4.Select the Web Service URl tab from the left pane of RSCM window to open the Web Service URL Page as shown in the below figure.



Configure URL to access the report server and also you can specify more than one URL to access the same Report Server.
Then Click Apply Button.

Step5.Select Database tab from the left pane of RSCM window to open the Database page as shown in the below figure.



Above figure shows the report server database and report server database credentials.Here we can able to sepcify a new database for the report server and can also change the credentials.

Step6.Select Report Manager URL tab from the left pane of the RSCM window to open the Report Manager URL page as shown in the below figure



Here we can configure a URL to access Report Manager.You can also specify multiple URLs for Report Manager by clicking the advanced button.

Step7.Email Account as per below figure allows us to configure the Email settings for a report server.Here we can able to specify an existing SMTP server and an email account for sending emails from that server.




Next Execution Account tab allows us to specify an account to enable us to use report datasources that do not require credentials.
This account allows to store external images used in reports.

Next Encryption Keys Tab allows us to backup,restore and change the symmetric key that is used by SSRS to encrypt credentials and connection strings stored in the report server database.

Finally Scale Out Deployment tab allows you to specify settings enables to multiple report servers to use a single,shared report server database.

Happy SSRS Configuration setup.

Tuesday, May 29, 2012

Three different ways to find blocking in SQL



SP_WHO2
It shows blocking by the presence of blkby column value.
Sysprocesses
select * from master..sysprocesses where blocked >0
Activity Monitor
Open Activity Monitor at any time by pressing CTRL+ALT A.

Difference between 3 types of Restoring modes.


S.No
With Recovery
With Norecovery
Standby\Readonly
1
Leave the database in online mode.
Leave the database in restoring mode.
Leave the database in
 readonly mode.
2
No further restore is possible.
Further restore is possible.
Further restore is possible.
3
User can able to access
Users can’t.
Users can able to read the
 databases.
4
Rollback the uncommitted transactions
Does not rollback the uncommitted transactions
It undoes uncommitted transactions
, but saves the undo actions in a
 standby file so that recovery effects
 can be reverted.


MYSQL::Setting Validate_Password componet for MySQL Database to ensure password policy settings

Inadequate Password Settings for MySQL Database We observed that the `validate_password%` settings on hostname `<insert hostname>` a...