Improve your work efficiency #1

If you would like to improve your work efficiency, the management of information become the key.
Many part of your task is spent to search some information.
Even if you are incredible skilled engineer, you will need the information in web.

To improve the work efficiency, you should install Firefox which you can use instead of Internet Explorer. Firefox provide the smooth and safe browsing.

Firefox have strong relationship with Google services. You can search and find the information which you want to know by Google toolbar easily.

Download Firefox -Free

You can add various Add-on to add the function to Firefox.
I'll introduce those Add-on someday.

SQLServer : How to count the number of records at each table

This is the sample SQL to count the number of records at each table on current database.

SQL:Table for the result

CREATE TABLE [dbo].[TEST](
  [TableName] VARCHAR(255) NOT NULL,
  [NrOfRecords] [int] NOT NULL
) ON [PRIMARY]

SQL:Count the number of records

DECLARE @TABLE_NAME VARCHAR(255)
DECLARE @SQL        VARCHAR(8000)

DECLARE C_TABLE CURSOR FOR 
SELECT 
  name 
FROM 
  sysobjects 
WHERE 
      OBJECTPROPERTY(id, N'IsUserTable') = 1 
  AND status >= 0 
ORDER BY 
  name

OPEN C_TABLE
FETCH NEXT FROM C_TABLE INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM C_TABLE INTO @TABLE_NAME
  PRINT @TABLE_NAME
  SET @SQL = 
      '
        INSERT INTO TEST
        SELECT ''' +
          @TABLE_NAME + ''' AS TableName
          ,COUNT(*) AS NrOfRecords
        FROM ' +
          @TABLE_NAME
      
      EXEC (@SQL)
END

CLOSE C_TABLE

SQLServer : Tables and Columns list

This SQL returns userr tables in current database and their columns.

SELECT
   O.name AS TableName
  ,C.name AS ColumnName
FROM
  sysobjects O
    INNER JOIN syscolumns C
    on 
      O.id = C.ID
WHERE
      OBJECTPROPERTY(O.id, N'IsUserTable') = 1 
  AND O.status >= 0   
ORDER BY
   O.name
  ,C.name

ReportNet : Relationship between ReportView and source

This SQL returns the relationship between ReportView and their source.
Run on content store database.
This is for SQLServer.

SELECT
  N.Name AS ReportViewName
 ,P.BASE AS ReportSource
FROM
 CMOBJECTS O
  INNER JOIN CMOBJPROPS6 P
   ON
    O.CMID = P.CMID 
  INNER JOIN CMOBJNAMES N
    ON
    O.CMID = N.CMID 
WHERE
     O.CLASSID = 19
 AND N.LOCALEID = 122 

SQLServer : Tables list

This SQL returns the table list on current database.

SELECT 
  name AS TableName
FROM 
  sysobjects 
WHERE 
      OBJECTPROPERTY(id, N'IsUserTable') = 1 
  AND status >= 0 
ORDER BY 
  name

ReportNet : Stored Procedure as a data source #1

This is the example of how to use stored procedure as a data source. This sample stored procedure has one parameter and returns the result of select statement.
This is the sample for SQLServer.

Run this SQL on the database

CREATE TABLE [dbo].[USER](
[USER_NO] [tinyint] NULL,
[USER_NAME] [varchar](20) COLLATE Japanese_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[USER]
([USER_NO] ,[USER_NAME])
VALUES
(1,'MAHESH')

INSERT INTO [dbo].[USER]
([USER_NO] ,[USER_NAME])
VALUES
(2,'DAVID')

INSERT INTO [dbo].[USER]
([USER_NO] ,[USER_NAME])
VALUES
(3,'KOICHI')

INSERT INTO [dbo].[USER]
([USER_NO] ,[USER_NAME])
VALUES
(4,'BRAD')
GO

CREATE PROCEDURE SP_SELECT_USER
(@USER_NO TINYINT)
AS

SELECT [USER_NO]
,[USER_NAME]
FROM [dbo].[USER]
WHERE
[USER_NO] = @USER_NO
GO

Import stored procedure as a Metadata on Framework Manager

Edit definition of stored procedure

If ths stored procedure has a parameter, you have to set the value in this form.

Click Test before setting the value and Set 1 in value on Prompt Values form

Select the parameter and click Edit

Set the value on Edit Value form

?PROMPT_NAME?
You can use user prompt here with this expression. When user runs a report which uses this data source, this prompt is shown. The stored procedure is imported as a data source. Then it is be able to used in Report Studio/Query Studio.

SQLSerer 2000 : Job History

This SQL return the job history of SQLServer Agent in yesterday.

SELECT
  JobHistroy.run_status,
  Jobs.name,
  JobHistroy.step_id,
  JobHistroy.step_name,
  JobHistroy.message,
  JobHistroy.run_date,
  JobHistroy.run_time,
  JobHistroy.server
FROM
  msdb.dbo.sysjobs Jobs

    INNER JOIN
      msdb.dbo.sysjobhistory JobHistroy
      ON
        Jobs.job_id = JobHistroy.job_id
WHERE
  run_date = CAST(
      CAST(YEAR(GETDATE()-1) AS CHAR(4)) +
      RIGHT('00' + CAST(MONTH(GETDATE()-1) AS VARCHAR(2)),2) +
      RIGHT('00' + CAST(DAY(GETDATE()-1) AS VARCHAR(2)),2)
             AS INT) and
  step_id = 0
order by
  jobs.name

ReportNet : Prompt Customize - Default Value

You can use default value in a dropdown list with Java script. It is also possible to change first word in that.

• Set ID on the property of the dropdown list
• Add two 'HTML Item'd after the dropdown list
• Edit first HTML Item as follows

<script type="text/javascript">
function go(){
 var combo1 = document.getElementsByName("_oLstChoicesC0")[0];
 combo1(0).text="All Customer";
 combo1(1).text="------------------------------";
 combo1.selectedIndex="0";
}
</script>

_oLstChoices means a dropdown list. C0 is ID which is set in the property. Change the number combo1.selectedIndex="0" to the default index. You can change the first word if you change the strings in combo1(0).text.

• Edit second HTML Item as follows

<img src="http://www.blogger.com/pat/images/blank.gif" onload="go()" />

This is to execute Java script on that report. Then this is the result.

SQLServer2000 : Job list

You can get a job list on SQLServer as a table by this SQL.

SELECT TOP 100 PERCENT
  t1.name
 ,t2.step_id
 ,t2.step_name
 ,t2.command
 ,t4.plan_name
 ,t3.enabled
,CAST(LEFT(t3.active_start_date,4) + '/' + SUBSTRING(CAST(t3.active_start_date AS varchar), 5,2) + '/' + RIGHT(t3.active_start_date,2) AS datetime) AS active_start_date
 ,t3.active_start_time
,CAST(LEFT(t3.next_run_date, 4) + '/' + SUBSTRING(CAST(t3.next_run_date AS varchar), 5,2) + '/' + RIGHT(t3.next_run_date,2) AS datetime) AS next_run_date
 ,t3.next_run_time
FROM
 dbo.sysjobs t1
  
  INNER JOIN dbo.sysjobsteps t2
  ON
   t1.job_id = t2.job_id
   
  INNER JOIN dbo.sysjobschedules t3
  ON
   t1.job_id = t3.job_id
   
  LEFT OUTER JOIN dbo.sysdbmaintplans t4
  ON
   SUBSTRING(t2.command,42,36) = CAST(t4.plan_id AS char(36))
ORDER BY
 t1.name