Monday, September 04, 2017

My presentation at SQL Saturday Auckland #655

I had an awesome day presenting at #sqlSaturday Auckland on 2nd September 2017. My topic was on Predictive Analytics using R in SQL Server.

Link for my presentation below

Predicitve Analytics using R in SQL Server 2016

Friday, September 01, 2017

Rerun failed SSRS subscriptions

Below is the script I have used to rerun the SSRS Report subscriptions that have failed today.

use ReportServer
go
DECLARE @ScheduledReportName varchar(200)
DECLARE @JobID uniqueidentifier
DECLARE @LastRunTime datetime
Declare @JobStatus Varchar(100)
--------------------------------------------------------
DECLARE @RunAllReport CURSOR
SET @RunAllReport = CURSOR FAST_FORWARD
FOR
SELECT 
CAT.[Name] AS RptName
, res.ScheduleID AS JobID
, sub.LastRuntime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus

FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON CAT.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON CAT.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
WHERE
sub.LastRunTime > GETDATE() - 1 and  Cat.Name LIKE 'reportname%' 
>convert(date,GETDATE()-2)
ORDER BY U.UserName, RptName 
OPEN @RunAllReport
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ScheduledReportName --&' ' & @JobID
EXEC msdb.dbo.sp_start_job @job_name =@JobID
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
END
CLOSE @RunAllReport
DEALLOCATE @RunAllReport

Wednesday, August 30, 2017

Node.js MSSQL ConnectionError: Failed to connect to localhost:1433 - connect ECONNREFUSED

Today I was trying Node.js to connect to SQL Server, the connection to the sql server failed with the below error.

name: 'ConnectionError',

message: 'Failed to conncet to localhost

Below are the steps I have followed to resolve this. Step 1 Ensure that the TCP/IP protocol is Enabled as shown below

Step 2

Ensure that the sql server browser is running as shown below.

Step 3

Node.js connections only supports authenticating with SQL Server accounts.

This is not that clear in the documentation. It took me a while to figure this.

To ensure that the SQL SERVER is configured for sql server authentication do the following

· In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

· On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

You might be prompted to restart the sql server. Right click the sql server and restart. The SQL Server Agent should also be restarted.

Thursday, August 24, 2017

SQL Server was unable to communicate with the LaunchPad service.

Today I was trying to run some R scripts against SQL Server.

All of a sudden the "R" scripts no longer worked, they returned the below error:

SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

To fix this error, I went to Administrative Tools -- Services. Looked whether the LaunchPad Service is running. I had to start this service as shown below.

This fixed the error.

Friday, July 21, 2017

Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of RevolveScaleR functions -- RxSqlServerData and RxImport.in Rstudio. These are my learnings as part of this exercise.

The following are the steps that need to be taken for successful connection to the SQLExpress database

Ensure that the SQL Server database has permission for the user that you are using as shown below.

Ensure that the NamedPipes and TCP/IP protocols are Enabled using the SQL Server Configuration Manager.

Otherwise you will receive an error as --

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

When you are enabling the TCP/IP protocol ensure that you specify the port 1433 at the appropriate location. Otherwise you will receive an error as --

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

Restart the SQL SERVER service after configuring the above step.
Ensure that the firewall is open to the port 1433.
Ensure that you can run a Telnet session to your sql server IP address.
There is a default user group created named SQLRUserGroupSQLEXPRESS when you install SQLEXPRESS with R Ensure that the user connecting to the SQL server from R Studio belongs to this group. Otherwise you will receive an error as -- Error in doTryCatch(return(expr), name, parentenv, handler) : Could not open data source.

Now coming to the code I have used in R Studio to import the data from the SQL Server Database into R is as follows:

#Connection string to connect to SQL Express
connStr <- font=""> paste("Driver=SQL Server; Server=", "SQLEXPRESS",
                 ";Database=", "TestDB", ";Trusted_Connection=true;", sep = "");

#Get the data from the Table
SQL_testdata <- font=""> RxSqlServerData(table = "dbo.rental_data",
                                  connectionString = connStr, returnDataFrame = TRUE);

#Import the data into a data frame
testdata <- font=""> rxImport(SQL_testdata);

#See the structure of the data 
head(testdata);

#See the top rows
str(testdata);

Hope this is useful for those who would like to connect to sql server using RStudio.

Tuesday, July 04, 2017

Finding Parameter values for subscriptions in a SSRS report

Today I had to look at about 100 subscriptions for a SSRS report, and find if a particular region has been used as a parameter in the subscription,

I tried opening each subscription to see the parameter. After opening a couple I thought this parameter should be saved in the database so started writing a query to find out all the values of the parameter used in all the subscirptions in the subscriptions database.

The following is the query that I came up with to find out what values were used for the parameters in the subscriptions for a report.

WITH
[CParameters] AS
(
SELECT 
[SubscriptionID],
[Parameters] = CONVERT(XML,a.[Parameters])
FROM [Subscriptions] a
),
[MySubscriptions] AS
(
SELECT DISTINCT
[SubscriptionID],
[ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),
[ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)')
FROM 
[CParameters] a
CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
),
[SubscriptionsAnalysis] AS
(
SELECT
a.[SubscriptionID],
a.[ParameterName],
[ParameterValue] = 
(SELECT
STUFF(( 
SELECT [ParameterValue] + ', ' as [text()]
FROM [MySubscriptions]
WHERE 
[SubscriptionID] = a.[SubscriptionID]
AND [ParameterName] = a.[ParameterName]
FOR XML PATH('')
),1, 0, '')
+'')
FROM [MySubscriptions] a
GROUP BY a.[SubscriptionID],a.[ParameterName]
)
SELECT
a.[SubscriptionID],
c.[UserName] AS Owner, 
b.Name,
b.Path,
a.[Locale], 
a.[InactiveFlags], 
d.[UserName] AS Modified_by, 
a.[ModifiedDate], 
a.[Description], 
a.[LastStatus], 
a.[EventType], 
a.[LastRunTime], 
a.[DeliveryExtension],
a.[Version],
e.[ParameterName],
LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName
FROM 
[Subscriptions] a 
INNER JOIN [Catalog] AS b
ON a.[Report_OID] = b.[ItemID]
LEFT OUTER JOIN [Users] AS c
ON a.[OwnerID] = c.[UserID]
LEFT OUTER JOIN [Users] AS d
ON a.MODIFIEDBYID = d.Userid
LEFT OUTER JOIN [SubscriptionsAnalysis] AS e 
ON a.SubscriptionID = e.SubscriptionID
where name like '%Report%'

Tuesday, June 27, 2017

Rerunning all subscriptions for a particular report that have failed.

Today I had a situation where a few of the subscriptions for a particular report have failed. I needed a script to run only these subscriptions manually. Basically I am extending my previous blog post to achieve this by passing the result of my query into a cursor and executing the subscriptions using the sp_start_job stored procedure as follows:

Do not forget to deallocate the cursor.

use ReportServer
go
DECLARE @ScheduledReportName varchar(200)
DECLARE @JobID uniqueidentifier
DECLARE @LastRunTime datetime
Declare @JobStatus Varchar(100)
--------------------------------------------------------
DECLARE @RunAllReport CURSOR
SET @RunAllReport = CURSOR FAST_FORWARD
FOR
SELECT 
CAT.[Name] AS RptName
, res.ScheduleID AS JobID
, sub.LastRuntime
, LastStatus
FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON CAT.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON CAT.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
where res.ReportID ='3AA8BDD0456' 
AND  LastRunTime > GETDATE() - 1 AND LastStatus not like 'Mail sent%' AND LastStatus not like '%has been saved to%' AND LastStatus not like '%0 errors%' --AND LastStatus not like 'Pending%' 

OPEN @RunAllReport
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ScheduledReportName --&' ' & @JobID
EXEC msdb.dbo.sp_start_job @job_name =@JobID
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
END
CLOSE @RunAllReport
DEALLOCATE @RunAllReport

Thursday, June 22, 2017

Rerunning a SSRS subscription report

Yesterday I had a situation where a couple of SSRS reports failed due to a network upgrade.

The SSRS report was not able to access the SQL server that was being used in the stored procedure.

So I had to rerun the report subscription manually.

To re-run the SSRS report subsciption I followed two steps.

Step 1:

I have used the below query to identify the job id of the failed reports.

SELECT
     S.ScheduleID AS SQLAgent_Job_Name, Sub.LastStatus, sub.LastRunTime, LastRunStatus
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath

FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE
sub.LastRunTime > GETDATE() - 1 and  C.Name LIKE 'Reportname'

Step 2:

I have copied the scheduleid and used a filter in the Jobs of the SSRS server as shown below.

Then Right click the Job and click 'Start Job at Step'

There are several other practices to achieve the same result.but I find that this is easier to follow.

Monday, June 12, 2017

Find the stored procedures and agents that use Linked Server

Today I had a scenario where I had to identify all the stored procedures and agents that used a particular Linked Server. We are thinking of getting rid of these multiple Linked Servers and combining them into one server.

So I have used the sys.sql_modules table to find out what stored procedures are using these linked servers.

The code I have written is as follows.

SELECT OBJECT_NAME(object_id) object , *
FROM sys.sql_modules
WHERE
Definition LIKE '%linkedsrv1%'
OR
Definition Like '%linkedsrv2%'
OR
Definition Like '%linkedsrv3%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;

I have also used the sysjobsteps and sysjobs tables from msdb database to find out what jobs are using these Linked Servers,

The code I have written is as follows:

SELECT j.name AS JobName,js.command
FROM msdb.dbo.sysjobsteps jsteps
INNER JOIN msdb.dbo.sysjobs jobs
ON jobs.job_id = jsteps.job_id
WHERE
jsteps.command LIKE '%linkedsrv1%'
OR
jssteps.command Like '%linkedsrv2%'
OR
jsteps.command Like '%linkedsrv3%'

Next question that I am pondering is if we consolidate these servers into one server, what are the considerations I need to be aware of.

I will be covering this in a future post. Meanwhile appreciate some feedback from the readers.

Tuesday, June 06, 2017

Sending Automatic Emails from SQL server

I am excited to reactivate my blog after 2.5 years.

I have written a stored procedure last week to send automated emails to customers based on certain criteria.

To identify this there is a stored procedure that requires some parameters.

The steps followed for creating the stored procedure are as follows:

There is already an existing stored procedure that creates a temp table and inserts data into that temp table.
In the new stored procedure define a new temp table.
Insert data into this temp table by executing the existing stored procedure
Declare new variables to hold the needed for the email.
In a while loop assign the data to the above variables for each record and send the email one by one using sp_send_dbmail built in stored procedure.

Here is the skeleton code I came up with for the procedure. I have configured a HTML email

[code]

Declare @temp_table table
(
ID INT IDENTITY(1, 1) primary key ,
field1 varchar(60) null,
AccountID VARCHAR(25) null,
AccountName varchar(200) null,
AccountEMail varchar(128) null
)

--
-- Insert into the temp table variable -- the list to be emailed by running the stored procedure Proc1
--

Insert into @temp_table
(

field1,
AccountID,
AccountName,
AccountEMail

)
Exec [Proc1]
@Parameter1 = 'NZIO',
@Parameter2 = 1,
@Parameter3=0

--
-- Declare new variables
--

DECLARE @count INT
DECLARE @accountemail varchar(60)
DECLARE @accountname varchar(60)
DECLARE @rowcount INT
DECLARE @href1 varchar(256)

--setting count to start from first row
SET @count = 1
SET @rowcount = (SELECT count(*) FROM @temp_table)
--Print @rowcount

--
--loop to send email from temp table
--

WHILE(@count < @rowcount)
BEGIN
SET @accountemail = (SELECT AccountEMail FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @accountname = (SELECT AccountName FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @href1 = (SELECT CASE When ClientMode = 'UNATTENDED (HANDS-FREE)' then 'http://link1.pdf' else 'http://link.pdf' END FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')

--
--Configure Email body
--

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'

Automatic Notification -- Please do not reply

' +
N'Good Morning ' +
cast(@accountname as nvarchar(max)) +
N' We are contacting you to let you know that blah blah (EDI: ' +
cast(@field1 as nvarchar(max)) +
N')
is not working. Please have a look at your account+ cast(@accountname as nvarchar(max)) +
N' Please click on the below link to view user guide on starting
' + N' Userguide for Windows ' +
N' Regards BI Support '

;

--
-- Send email using Database Mail
--

--USE msdb
--GO
EXEC MSDB.dbo.sp_send_dbmail @profile_name='Testreport',
@copy_recipients = @accountemail,
@recipients = 'iemail@emailadd.com',
@subject='Test message',
@body= @tableHTML,
@body_format = 'HTML' ;

SET @count= @count +1

END

[/code]

Wednesday, February 26, 2014

Getting the first date of the month in SQL without any functions

One of the date columns in my database is an integer as it is a dimension key
I was trying to get some results grouped for the whole month by this date integer column.

This is how I went about getting it in an easier manner without using any DATEADD and DATDIFF functions

Let us say that the date column name is dim_date_key which is an integer data type

So I used the following SQL code to get the sum of the amount for the month

select sum(amount), dim_date_key/100*100 + 1 month_start_Date from table_name
where dim_date_key/100*100 + 1 >= 20130401
group by dim_date_key/100*100 + 1

Here if we just look at the expression dim_process_date_key/100*100 +1 in mathematical terms it is confusing as to how this can get the start of the month.

But the key here is the integer data type of the date column.

When the expression is evaluated, the expression dim_process_date_key/100 gets evaluated first. This gives the answer as 201304 since the data type is an integer.
Then the expression 201304 * 100 is evaluated which yeilds the result as 20130400 which is an integer.
Then the expression 20130400 + 1 is evaluated which yeilds the start date of the month which is 20130401

Wednesday, January 15, 2014

Import/Export data wizard in MSSQL 2008 R2 Errors -- Resolved

Today I was using the import export wizard to import data from one of the database in the production system to the database in the development system.

To do this -- Right click on the database (in the development system) choose -- tasks -- Import data
Follow the steps in the data wizard for choosing the source and destination tables. I used the write a query to copy the data option.

Checked the mappings
and clicked Finish

I expected the wizard to run without any problems. But................ the following errors were thrown.

- Copying to [dbo].[FACT_ACCOUNT_TRANSACTION] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The INSERT permission was denied on the object 'FACT_ACCOUNT_TRANSACTION', database 'BIW', schema 'dbo'.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (128)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (128)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - FACT_ACCOUNT_TRANSACTION" (115) failed with error code 0xC0209029 while processing input "Destination Input" (128). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Finally I resolved this error by dropping the column store index on the table and rerunning the wizard.

And then recreated the column store index after the data has been copied.

Monday, December 23, 2013

Work Directory in ODBC Connection is empty

"Work Directory in ODBC Connection is empty, unable to proceed."

That was the error I was getting today when I was trying to load a table from MDS using Wherescape Red. At first I didnot understand why there is a work directory needed. When I looked at the MDS cnnection settings The default Load was set to Native ODBC and the work directory was not set as shown below.

As soon as I have set the directory to c:\ the loading of the table worked.

Because the Native ODBC load reads all the data and creates temporary tables, this method of data loading requires a work directory to be specified so that the data is written in batch mode rather than one row at a time similar to ODBC load. When the reading a writing takes place one row at a time we need not specifiy the work directory. But the native ODBC load is faster because of a batch write process.

Ensure that a process for deleting the temporary tables is in place if you are loading using Native ODBC.

If you are using the ODBC Load there is no need to specify the work directory.

Tuesday, August 20, 2013

ERROR 22-322: Syntax Error is SAS code

I have been in hibernation for too long now. I have changed jobs and now hjaving an opportunity to work on SAS code after nearly 11 years. Today I was running a SAS program and got the following error.

ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, FORMAT, INFORMAT, LABEL, LABEL, LENGTH,

To resolve this as the error suggested there was a syntax error in my code where I was not using the proper syntax in the ATTRIB statement I was using

Name label 'Employee Name';

Here the label statement needs an '=' so when I change this to the following statement

Name label = 'Employee Name' ;

the sas code was executed successfully.

Thursday, June 20, 2013

Free ebooks from Microsoft

Here is a link to a huge collection of free ebooks posted by Eric Ligman from Microsoft. Hope you will find these books useful in your career development.

Monday, February 04, 2013

I thought I will start updating my blog after a bit hibernation.

So here is a tip for getting rid of the blank page that appears if you use a Next Page After setting in the group section as shown below.

Use the formula Not Onlastrecord

To do this click on the formula section against the Next PAge After setting and type in Not Onlastrecord

Save the report and the last blank page will disappear.

Friday, July 13, 2012

Sql Server Error 3154

I created a new database today and was wanting to restore this new database from another exisitng database using the restore option available in the wizard.

But I got the 3154 error as below.

So I had to restore to the restore database command in the SSMS. So here is the first option I tried

restore database databasename
from disk 'filepath.bak'
with replace

but this gave an error because the database and log files were in use for the exisitng database and had to use the with move command as follows.

restore database databasename
from disk 'filepath.bak'
with move data file to 'file path',
move log file to 'file path',
replace

The database was restored successfully.

Tuesday, May 15, 2012

Workbook is larger than the maximum workbook size

I use sharepoint 2010 to display the data analysis tool I have created in excel 2010 to manage the Microsoft licenses as well as to utilise the excel 2010 capabilities of slicers and dicers. This has been going pretty smoothly until yesterday when I stumbled with this error below.

"Workbook is larger than the maximum workbook size"

So I had to go through the Excel web services properties on the sharepoint server and increase the maximum file size.

Here are the steps that I followed.

Logged into the Central Administration of the Sharepoint on the relevant server.
Clicked on Application Management
Clicked on Manage Service Applications
Cicked on Excel Service Application which is a hyperlink in blue
Clicked on Trusted File Locations and clicked on the relevant link as shown below

Under the file size properties I have changed from 10 to 15 as shown below. Once this is done the page needs to be reloaded and the excel web part works beautifully.

Tuesday, March 06, 2012

Scroll Lock problems in Excel

Yesterday I had this problem in moving between the cells within excel. This problem usually occurs if the scroll lock is on. I tried to find the scroll lock button on my keyboard but it was not there. So I got the idea of using the On screen keyboard function to diable the scroll lock.

So I clciked on Start button -- All Programs -- Accessories -- Ease of Access --On Screen keyboard.

The following screen appears and I click on the scroll lock as shown below.

This same funcationlity can be used when we use remote desktop to access the keyboard of the remote computer.

Friday, March 02, 2012

Remove the compatibility mode from excel files

As you all know Excel 2007 has lot more features compared to Excel 2003. The extension with which the excel files are saved alos differs based on the version of excel.

For excel 2003 the file is saved as .xls and for Excel 2007 the file is saved as .xlsx

The other day I realised that when I opened a file in Excel 2007 all the features of Excel 2007 are not available for use.

So when I tried to investigate what was happening I found that the file was saved as .xls. So I saved the file as excel 2007 workbook and tried to use all the features.

I was not able to use all the features until I reopened the file in excel 2007.

I thought I would mention this tip here for everyone's benefit.

Thursday, February 23, 2012

Resolving #NUM error in excel

I was using excel to display data from a cube. I tried to do a caluclated cell in excel to display number of months between two dates. Here is the formula I used to do this.

=Datedif(H1,G5,"m")

But I received an error -- #NUM

Then I again looked at the dates entered. To make sure that the datedif function works you need to give the earlier date as the first parameter and the later date as the second parameter to avoid the #NUM error. You also need to ensure that the dates are in the correct format otherwise you will receive a #VALUE error.

So to avoid the #NUM error I changed the formula to

=Datedif(G5,H1,"m"). This retunrs the difference between the two dates in months.

Friday, February 17, 2012

24 Hours of PASS --March 2012 Registrations Open

This morning I recieved the email of 24 hrs of PASS registration. Below are the details.

Nonstop SQL Server Training Don't miss the best 24 hours of free, online SQL Server training in the industry with 24 Hours of PASS: SQL Server 2012 March 21, featuring closed captioning in 15 languages. Join us for an exceptional lineup of the world's top SQL Server and BI experts, who will be presenting 24 back-to-back technical webcasts with a special focus on SQL Server 2012.

Go ahead and register and benefit from these sessions.

Monday, January 23, 2012

Have you heard of Prezi?

Late last year one of my colleagues Mark Crosby has introduced the Prezi tool in our company to do presentations. Prezi is a presentation software that can be used in creating more visual presentations. It is different to the traditional powerpoint software as it has a Zooming user interface and uses a vector based illustration and text which creates big visual impact.

The main differences between PRezi and Powerpoint that I have found are as follows:

The first difference I found is that Prezi is an online application that does not need any installation of software whereas Powerpoint is an installed software application.

Prezi is more like a canvas based presentation whereas powerpoint is more based on slides that are arranged in a sequntial manner.

In Prezi you cannot just copy and paste pictures like we do in powerpoint. The pictures need to be uploaded and then included in the Prezi creations.

In Prezi you can use only flash objects and embed youtube videos whereas in powerpoint you can include a wide variety of video formats.

You cannot print the sides as a handout in Prezi like you do in powerpoint.

There are many other differences, strengths and weaknesses in both the tools and for now I will start using PRezi more and more and see as it is easy to learn.

Let me know what your thoughts are

Monday, December 19, 2011

Automatcially download pictures in Outlook 2007

Today I was asked by my colleague as to how to change outlook 2007 so that she does not manually need to download the pictures every time an email comes in.

Here is what I have suggested to her.

Go to the menu item Tools in Outlook and then click on Trust Center

Then choose Advanced settings

Untick the "Don't Download pictures automatically' option as shown below

Tuesday, December 13, 2011

Have you heard of space function?

Today I had a unique scenario where I had to introduce 2 spaces in between two fields.

I came across the space function which I used in my scenario.

The syntax is space(integer_expression)

The integer expression is an integer. IF a negative value is supplied a null is returned.

So for example look at the following syntax:

select First_name, + space(4) + Last_Name from test_table

The above select statement will generate 4 spaces in between the first name and last name

The space function will generate a miximum of 8000 spaces.
For more information click here

Monday, December 05, 2011

Multiply and Divide a group of cells in Excel

I just now realised that it has been more than a month since I have posted anything on my blog. So here I am with the most recent tip I learnt in Excel.

If you have a column of cells that need to be divided by 1000 for example. Here are the steps that you need to follow.

Type 1000 in a cell lets say A2

Copy the cell A2 using Ctrl+C

Select the cells in the column where you would like to let us say multiply the values by 1000.

Right click and select Paste Special

Click on Multiply as shown below.

You can do the same for adding or subtracting a specific value from a group of cells.

This tip is particularly useful if you want to show the data that is in millions but want to reduce the number of digits by dividing them by 1000.

Hope this heps.

Tuesday, October 11, 2011

Increasing the number of recent files in SSMS

In Sql Server Management Studio, I usually access my sql query files that I save -- using the File -- Recent Files option. The default setting of the number of recent files to be displayed in SSMS. Today I wanted to access a file that I had used last week and I could not find that file in the recent list.

So I thought of displaying more than the normal 4 files that are displayed in the Recent Files option. So I set out to find where the settings are (as in the microsoft office programs) -- I clicked on Tools -- Options -- General -- The option reads as

Display 4 files in the recently used list. I changed the number 4 to 10.

Now it is starting to display 10 recently used files.

Friday, October 07, 2011

Export directory list in excel

Yesterday I wanted to export the contents of a folder into excel to provide it to the users for input.

Here are the steps I followed.

Go to Start -- Run -- CMD

Change to the drive letter of the drive in which the folder you want the contents listed is if you are not already there by typing the drive letter followed by a colon. Eg: d: for going to the d drive.

Change to the folder of which you want the contents to be exported by using the chane directory command cd. Eg: cd shared/test for changing into the a directory called test which in turn is in the shared direcotry on the d drive.

Then list the directories using the dir/d comman. This gives the list.

Right click and click on MAark and select the contents you want to copy.

After selecting right click again.

Then paste this into a new excel document.

Your list of directories is ready.

Wednesday, September 14, 2011

Save SQL Query results into another SQL database

Today I had a requirement to save the results of an SQL query into a sql table. Usually it is easy to export to excel by using the option of copying the results and pasting them to an excel file.

But this time I had over a million rows and I didnot want to use excel to store that huge amount of data. So I followed the following 2 simple steps.

1. Create a new table in a new database:

Use the SSMS to create the database and use the create table query to create the new table as follows:

create table databasename.dbo.tablename

([col1] [varchar(20)], [col2] [int], [col3] [datetime])

2. Create a SQL query using the Insert into command as follows:

Insert into databasename.dbo.tablename

(col1, col2, col3)

select a.col1, b.col2, c.col3 from tab1 a, tab2 b, tab3 c

where a.col1=b.col1 and b.col2=c.col3 and col1='zzz'

This query has inserted data into the new table directly.

Thursday, September 08, 2011

Add months to date in excell

Yesterday I had this requirement to add number of months (m) to a cell that contains start date to estimate the end date. In 2003 I had to use a complex formula like below where A1 is the cell that contians the start date.

=DATE(YEAR(A1),MONTH(A1)+m,DAY(A1))

Well in Excel 2007 it is even more easy to add months with the Edate function like below.

=Edate(A1, m).