Create database using an existing data files and no log file

If you only have a data file and do not have a log file, you can use the following command to create the database.

This  command will create a log file in the default log file location for the instance.

CREATE DATABASE "DB_NAME" ON (FILENAME = 'FILE_LOCATION') FOR ATTACH_REBUILD_LOG

  

Query to get the List of all Foreign Keys in a database

Please change the name of the database and run it.

USE "database name"

go

SELECT

      OBJECT_NAME(constraint_object_id) FOREIGN_KEY_NAME,

      OBJECT_NAME(parent_object_id)     TABLE_NAME,

      (SELECT name FROM sys.columns WHERE object_id = parent_object_id AND column_id = parent_column_id) COLUMN_NAME,

      OBJECT_NAME(referenced_object_id) REFERENCED_TABLE_NAME,

      (SELECT name FROM sys.columns WHERE object_id = referenced_object_id AND referenced_column_id = column_id) REFERENCED_COLUMN_NAME

FROM sys.foreign_key_columns

ORDER BY REFERENCED_TABLE_NAME

go

Changing SQL Server Instance Name after renaming the physical machine or virtual machine

After a long timeout, I have decided to register something that I learned today.

Here is the scenario.

There is a named SQL Server instance running on a virtual machine. An Image of the entire machine was taken and restored with a different virtual machine name. (This was done as an exercise to create a new staging environment as a look a like copy of the production environment).

After the new virtual machine was created, there is a  named SQL Server instance is now running on the new virtual machine. But the @@SERVERNAME property is still displaying the old machine name.

Here is what you do to change the @@SERVERNAME property.

sp_dropserver  'OLD_HOST_NAME\INSTANCE_NAME'

sp_addserver @server = 'NEW_HOST_NAME\INSTANCE_NAME', @local = 'local'

Restart the SQL Server Instance using SQL Server configuration Manager.

Now execute SELECT @@SERVERNAME or 
                    SELECT SERVERPROPERTY('SERVERNAME')

You will notice that the new host name is displayed correctly.

JDBC Connectivity for SQL Server 2008 R2 Express Named Instances

I have a SQL Server 2008 R2 Express Server running on a development environment. It only had a default instance. I got a request from the development manager to create a named instance on that server.

I created a named instance, tested connectivity from the server and from a remote client using SSMS. It worked fine..

I got an email saying that the dev manager wasn't able to connect to the named instance using "MyEclipse" and his app is also having the same connectivity issue.

I was confused..because I was able to connect the named instance from my laptop using SSMS. I installed SSMS on the Dev Manager's laptop and was able to connect to the named instance as well.. So I wasn't sure where to go.

I also installed SQL Server 2008 R2 Standard on the same server and created a named instance..It did not have any issues with JDBC Connectivity.

After some research through MSDN website..I found out the following solution.


Go to Registry
--------------
HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> 100 ->

Add a New Key value (If not found)
SQLBrowser

Add a String value inside SQLBrowser
Ssrplistener -  "1"

Restart the SQL Browser Service.

It worked....!!

Select MON-YYYY from DATETIME and GROUP and ORDER BY MON-YYYY

One of my Developers needed my help to write a query.

Here is the requirement.
Table has two columns COL_A (DATETIME) and COL_B (INT)
Data in the table looks like this.

COL_A                                 COL_B
01/01/2012 04:30:000             5
01/04/2012 05:30:000             9
02/05/2012 06:50:000           10
02/14/2012 07:50:000           15
05/14/2012 08:50:000           17
05/22/2012 09:45:000             1
12/12/2012 07:45:000             2

We had to generate a report that would get the maximum value of COL_B for every month and the result should not have any date value for COL_A as the requirement was for month. The result should also be ordered by MONTH.

Here is a query that would work for this scenario.

SELECT substring(convert(varchar(24), dateadd(mm,datediff(mm,0,COL_A),0), 113),4,8),
               max(COL_B)    
FROM 
               TABLE
GROUP BY
              dateadd(mm,datediff(mm,0,COL_A),0)
ORDER BY
              dateadd(mm,datediff(mm,0,COL_A),0)          


So, the result should look like this.

Result:
Month         Max(COL_B)  
Jan-2012       9
Feb-2012     15
May-2012   17
Dec-2012      2

MCTS - 70 432 - Certified

I have been trying to pass the MCTS - 70-432 exam for a long time. I bought the Microsoft Press book and started preparing. But was not confident enough to take it for a while. I used measureup practice tests as well. Finally, I went ahead and took the exam on 01/14/2012 and surprise...I passed with a score of 940 (Minimum Passing score is 700).
I thought of sharing my experience about the MCTS - 70-432 exam..and here I am. I will explain about my preparation methods and areas that need special interest in my next blogs..