SQL Server Template Explorer

Today while working I created a new Stored Procedure using SSMS 2008, through 

Database => Programmabilityv=> Procedures => Right Click Select New procedures 

This returned me a well defined new SP template as usual. Here I was supposed to add some extra lines of comment like modified by, date, purpose etc. This prompted me to think how nice it would have been if I could get these automatically written for me every time I create a new SP. So I googled and straightaway found a stack-overflow link telling about Template Explorer in SSMS. From template explorer you can view all the default SSMS templates and can edit any of them. 

I edited template for new stored procedure to add new comment lines as per my requirement and also changed number and type of default parameters.

You can also use it to add some other commonly used lines like TRY/CATCH block :)

Shortcut to open template explorer: CTRL + ALT + T

These templates are stored in your file system as plain SQL files which you can edit without SSMS as well. Location for it is below:

On my XP machine having SQL Server 2005 Express:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql

On my windows 7 machine having SQL Server 2008 R2 Developer Edition:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

SQL Server Temp table vs Table variable

Hi All,

Some time back I was in an interview where one of the question was "Whats the difference between Temporary tables and Table variables".

What I knew at that time was:

• Table variables are a newly introduced concept  and are faster.
• Table variable exists only exist in current session.

So after interview I googled a bit and as-usual found answer on SQL Authority spread in couple of articles.  These articles have a lot of comments and some interesting points, so I thought to summarize them here for those who want it quick:

• In case of large amount of data temp table is advised because SQL Server creates, maintains and uses the statistics of temp table while generating the execution plan. Besides this we can create index in temp tables if that is needed.
• Table variables performs good only for small record-set. Because there data statistics is not used while creating the execution plan and because of that sometimes fully optimized plan are not created. Table variable should be used where they are joined with small tables only.
• Temp table has the global option which is very useful too when having two connections open and running multiple tests at the same time.
• Table variables are not transactional and do not roll back. Temp tables are transactional and do roll back.

For more details, please visit below links:

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

Report Server Model generation error: Table has a non-primary data source

A lil bit unorthodox solution!!

I faced this error (Report Server Model generation error: Table has a non-primary data source) yesterday while working on SSRS. I was trying to create a Report Model (SMDL) on SQL Server Buisness Development Studio and was getting this error repeatedly in final step of “Report Model Wizard”.

Query which I was using in Data Source View was somewhat like below:

SELECT Table1.Col1, Table1.Col2, Table2.Col2, Table2.Col3

FROM Table1

JOIN Table2

ON Table1.Col1 = Table2.col1

I googled this error a bit but found no solution and hence I adopted this lil bit unorthodox option.

My solution was to add an artificial column to my resultset and then using it as logical primary key. So I changed my query as below:

SELECT Table1.Col1, Table1.Col2, Table2.Col2, Table2.Col3 ', NEWID()AS Id

FROM Table1

JOIN Table2

ON Table1.Col1 = Table2.col1

And then I used this new column as logical primary key, as shown in below image. 

This is working like charm for me but I am yet to test this fully so use this solution for you after through testing. Hope this helps.

Display DNN user profile properties in column format

Hi All,

A few days back in one of the DNN portals that I manage I was required to provide functionality to download user info including user profile properties in column format. Initially what I did was using DNNs built in API for getting users and then iterate through their profile properties by using UserInfo.Profile.ProfileProperties

This worked fine initially but once the number of users started growing, this started tooking great deal of time and ultimately started timing out. So I thought that I should design my own procedure for doing this and hence I came up with below solution. Probably this may save a few hours of your time.

            SELECT u.UserID, u.Username,

            upo2.PropertyValue as [ContactType],

upo3.PropertyValue AS [Title],

upo4.PropertyValue AS [FirstName],

            upo5.PropertyValue AS [LastName],

upo6.PropertyValue  AS [Position],

UPO7.PropertyValue  AS [Company],

UPO8.PropertyValue  AS [MembershipPeriod], UPO9.PropertyValue  AS [MembershipJoinDate],

UPO10.PropertyValue  AS [MembershipExpiryDate], UPO11.PropertyValue  AS [Address1],

            UPO12.PropertyValue  AS [Address2],

UPO13.PropertyValue  AS [Address3],

UPO14.PropertyValue  AS [Town],

            UPO15.PropertyValue  AS [County],

UPO16.PropertyValue  AS [Post Code],

UPO17.PropertyValue  AS [Country],

            UPO18.PropertyValue  AS [MainPhone],

UPO19.PropertyValue  AS [AlternativePhone],

            UPO20.PropertyValue  AS [Email],

UPO21.PropertyValue  AS [MembershipNumber]

            FROM Users u

            LEFT OUTER JOIN UserProfile UPO1

            ON upo1.UserID = u.UserID AND upo1.PropertyDefinitionID = 394

            LEFT OUTER JOIN UserProfile UPO2

            ON upo2.UserID = u.UserID AND upo2.PropertyDefinitionID = 398

            LEFT OUTER JOIN UserProfile UPO3

            ON upo3.UserID = u.UserID AND upo3.PropertyDefinitionID = 393

            LEFT OUTER JOIN UserProfile UPO4

            ON upo4.UserID = u.UserID AND upo4.PropertyDefinitionID = 362

            LEFT OUTER JOIN UserProfile UPO5

            ON upo5.UserID = u.UserID AND upo5.PropertyDefinitionID = 364

            LEFT OUTER JOIN UserProfile UPO6

            ON upo6.UserID = u.UserID AND upo6.PropertyDefinitionID = 382

            LEFT OUTER JOIN UserProfile UPO7

            ON upo7.UserID = u.UserID AND upo7.PropertyDefinitionID = 381

            LEFT OUTER JOIN UserProfile UPO8

            ON upo8.UserID = u.UserID AND upo8.PropertyDefinitionID = 395

            LEFT OUTER JOIN UserProfile UPO9

            ON upo9.UserID = u.UserID AND upo9.PropertyDefinitionID = 396

            LEFT OUTER JOIN UserProfile UPO10

            ON UPO10.UserID = u.UserID AND UPO10.PropertyDefinitionID = 397

            LEFT OUTER JOIN UserProfile UPO11

            ON upo11.UserID = u.UserID AND UPO11.PropertyDefinitionID = 383

            LEFT OUTER JOIN UserProfile UPO12

            ON UPO12.UserID = u.UserID AND UPO12.PropertyDefinitionID = 384

            LEFT OUTER JOIN UserProfile UPO13

            ON UPO13.UserID = u.UserID AND UPO13.PropertyDefinitionID = 385

            LEFT OUTER JOIN UserProfile UPO14

            ON UPO14.UserID = u.UserID AND UPO14.PropertyDefinitionID = 386

            LEFT OUTER JOIN UserProfile UPO15

            ON UPO15.UserID = u.UserID AND UPO15.PropertyDefinitionID = 502

            LEFT OUTER JOIN UserProfile UPO16

            ON UPO16.UserID = u.UserID AND UPO16.PropertyDefinitionID = 391

            LEFT OUTER JOIN UserProfile UPO17

            ON UPO17.UserID = u.UserID AND UPO17.PropertyDefinitionID = 370

            LEFT OUTER JOIN UserProfile UPO18

            ON UPO18.UserID = u.UserID AND UPO18.PropertyDefinitionID = 392

            LEFT OUTER JOIN UserProfile UPO19

            ON UPO19.UserID = u.UserID AND UPO19.PropertyDefinitionID = 388

            LEFT OUTER JOIN UserProfile UPO20

            ON UPO20.UserID = u.UserID AND UPO20.PropertyDefinitionID = 389

            LEFT OUTER JOIN UserProfile UPO21

            ON UPO21.UserID = u.UserID AND UPO21.PropertyDefinitionID = 399

In above query, numbers in red are PropertyDefinitionIds for respective profile properties. You will need to change these numbers as per your database. To get PropertyDefinitionID, you can use below query in your dnn database.

select * from profilepropertydefinition where portalid= order by propertyName

Take special care of PortalId while retrieving propertiesDefinitionIds as their can be several versions of profile property, i.e., one for each portal.

Store complete data table in database

Hi All,

Below is a simple method that can store complete datatable to database in a go.

private static bool StoreInDataBase(DataTable dtDetails, string connectionString, string tableName)
    {
        if (dtDetails != null && dtDetails.Rows.Count > 0)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                {
                    bulkcopy.DestinationTableName = tableName;
                    try
                    {
                        bulkcopy.WriteToServer(dtDetails);
                    }
                    catch (Exception ex)
                    {

                                        //Log Exception
                        return false;
                    }
                }
                connection.Close();
            }
        }
        return true;
    }

Its better than iterating complete dataTable and writing one record at a time.

Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Hi All,

Few days back I was stuck a bit with a SQL problem, I got it resolved with the suggestion of one of my colleagues(Anup). I have to access record  from SQL SERVER 2008 based on an aggregate function. But I got error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”

Structure of my table was somewhat like below:

What I wanted from this table was records per UserId where CreatedDate is equal to minimum CreatedDate. So first I framed a query as below:

SELECT UserID, MIN(CreatedDate)

FROM RenewalHistory

Group By UserID

           (QUERY  1)

This worked fine giving me the expected results and then I added column SubscriptionType to select list, making my query like below:

SELECT UserID, MIN(CreatedDate), SubscriptionType

FROM RenewalHistory

Group By UserID

           (QUERY  2)

This started giving me error saying “Column 'RenewalHistory.SubscriptionType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

To overcome this, I used table produced in query 1 as a sub-table and reused it to get desired record as shown in below query:

SELECT rh.Id, rh.UserId, rh.SubscriptionType, rh.Createddate

FROM RenewalHistory rh, (

      SELECT UserId, MIN(CreatedDate) AS CreatedDate

      FROM RenewalHistory

      Group By UserID) subRH

WHERE rh.UserId = subRH.UserId AND

rh.CreatedDate = subRH.CreatedDate
           (QUERY  3)

This was how I got my problem solved. But I feel this is not the best way to sort out this problem, so if you have some better solution for this problem. Then kindly share it here.

Foreign key not working in MySQL.

Why can I INSERT a value that's not in the foreign column?

I have created two tables on MYSQL using phpMyAdmin and assigned primary and foreign key constraint( as in below queries). I didn't got any error but still I was able to insert wrong values in it. This frustrated me quite a lot. I tried my queries several times on phpMyAdmin, I also tried those queries on SQL SERVER and they worked as they should do.

After googling sometime for this problem I got the reason and the culprit was 'storage engine MyISAM'. I altered it to InnoDB and it worked like a magic immediately.

Query used for changing storage engine:

ALTER TABLE actions ENGINE=InnoDB;

Create table queries I used:

CREATE TABLE IF NOT EXISTS `emp` (
  `id` int(11) NOT NULL,
  `emp_name` varchar(255) NOT NULL,
  `role` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

CREATE TABLE IF NOT EXISTS `emp_expertise` (
  `emp_id` int(11) NOT NULL,
  `speciality` varchar(50) NOT NULL,
  KEY `emp_id` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `emp_expertise`
  ADD CONSTRAINT `emp_expertise_ibfk_1` FOREIGN KEY (`emp_id`) 

  REFERENCES `emp` (`id`) 

  ON DELETE CASCADE 

  ON UPDATE CASCADE;

INSERT INTO `emp` (`id`, `emp_name`, `role`) VALUES
(1, 'Ravi', b'1'),
(2, 'Vishwesh', b'1'),
(3, 'Ajay', b'0'),
(4, 'Ramesh', b'0');

Below query was previously inserting but after I fired above 

ALTER query it stopped.
 

INSERT INTO emp_expertise (emp_id, speciality)
VALUES (55,'XYZ')

Drop failed for database. Currently in use. Error 3702

Hi All,

Yesterday while working on DNN I needed to restore my website to a previous state. So I thought to drop my current working database and restore a backup copy.

I use MSSQL SERVER 2005. While dropping my working database I got error as below

Drop failed for database {database name}

cannot drop database because it is currently in use. Error 3702



To resolve this problem I googled a bit and then found below as a solution. This worked as a magic to my problem.

USE Master;
GO

ALTER DATABASE {database name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE {database name};
GO



Hope it helps you guys also.