Posts Tagged SQL

“Input string was not in a correct format.” while installing SQL Server 2008

Posted by Rajanihanth in SQL Server on September 22, 2012

I have re-installed Windows 7 and other applications on my laptop this morning. While installing the SQL Server 2008, I have got the following error message.

I have selected all  the features including SSRS and SSIS, and the event log was showing like below.

After spending few minutes on the web, I realized the problem with my performance counters and I wanted to rebuild it on my laptop. Here is the steps to rebuild the performance counters!

Step 1: Open the command prompt (Make sure to Run As Administrator otherwise you will get an error :))

Step 2: Enter the “LODCTR /R” command and hit Enter key

You will be getting the following success message!

That’s all, try to install the SQL Server again, No problem at all!

Thanks /R

References:

http://blogs.technet.com/b/yongrhee/archive/2009/10/06/how-to-rebuild-performance-counters-on-windows-vista-server2008-7-server2008r2.aspx

Advertisement

, , , , , ,

4 Comments

Create a simple Database and Table with some data in SQL Server

Posted by Rajanihanth in SQL Server, Tech Tips-SQL on September 3, 2012

This is  for my testing and re-blogging purpose, I just want to create a database table and then insert few values. The reason I need this database, to create a BDC (Business Data Catalog) in SharePoint 2007. If anyone need to create a sample database then you can simply execute these scripts. 🙂

Step 1: Create a database

CREATE DATABASE Database1

Step 2: Create a Table

CREATE TABLE Table1(
Column1 int Primary Key NOT NULL,
Column2 varchar(10)
)

Step 3: Insert values into the table

INSERT INTO Table1 (Column1, Column2) VALUES (1, 'A')
INSERT INTO Table1 (Column1, Column2) VALUES (2, 'B')
INSERT INTO Table1 (Column1, Column2) VALUES (3, 'C')
INSERT INTO Table1 (Column1, Column2) VALUES (4, 'D')
INSERT INTO Table1 (Column1, Column2) VALUES (5, 'E')
INSERT INTO Table1 (Column1, Column2) VALUES (6, 'F')

That’s all, we will execute these two scripts (1 and then 2)

Script 1:

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Database1')
CREATE DATABASE Database1
GO

Script 2:

Use Database1
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Table1'))
CREATE TABLE Table1(
Column1 int Primary Key NOT NULL,
Column2 varchar(10)
)
GO

INSERT INTO Table1 (Column1, Column2) VALUES (1, 'A')
INSERT INTO Table1 (Column1, Column2) VALUES (2, 'B')
INSERT INTO Table1 (Column1, Column2) VALUES (3, 'C')
INSERT INTO Table1 (Column1, Column2) VALUES (4, 'D')
INSERT INTO Table1 (Column1, Column2) VALUES (5, 'E')
INSERT INTO Table1 (Column1, Column2) VALUES (6, 'F')
GO

Copy and Paste these scripts in a Query Analyzer and execute from the menu or just press F5 to run the script!


Thanks. R./

,

4 Comments

Sorting a column alphabetically and display the ordered number in the next column – SQL Server

Posted by Rajanihanth in SQL Server on April 29, 2012

Last week, I have faced an interview and I was asked to write a SQL query to display an ordered column in an unsorted table and the next column should be displayed the ranking number. See below:

Unsorted table:                                                                  Sorted table:

                                                     

Looks like pretty simple no? but in an interview with 3 Interviewers little  bit difficult! I did answer in a different way but there are pretty straight forward methods available in the SQL Server. We will see all the methods here:

Step 1: Create a table

CREATE TABLE [dbo].[Table1](
[Column1] [varchar](10) NOT NULL
)

Step 2: Insert the values

INSERT INTO [dbo].[Table1]([Column1]) VALUES ('C')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('D')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('B')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('E')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('A')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('F')

Method 1: Getting the ASCII value of the character and then display the number. Eg: ASCII of ‘A’ is 65, so the value is 65-64=1.

SELECT Column1,
 ASCII(Column1)-64 AS Column2
FROM Table1
ORDER BY Column1

Method 2: Using the ROW_NUMBER ( ) function and order by the column. You can check the function here.

SELECT Column1,
 ROW_NUMBER()OVER (ORDER BY Column1) AS Column2
FROM Table1

Method 3: Using the RANK ( ) function and order by the column. You can check the function here.

SELECT Column1,
 RANK() OVER (ORDER BY Column1) AS Column2
FROM Table1

Output:

That’s all! These are pretty easy methods, if we know about these then we can answer quickly.

Thanks. R./

, , , , ,

3 Comments

SSRS Error 1: An error occurred while parsing the configuration file. The ReportServerVirtualDirectory element is missing

Posted by Rajanihanth in SQL Server, SSRS on February 25, 2012

I am installing a 3rd party Auditing Software for our SharePoint 2007 farm and it needs SQL Reporting Services (SSRS) to generates reports. When I was configuring the SSRS 2005 (It is already installed but not configured) on our production server which is having Windows 2008 R2 OS and IIS 7.5. After completing the Reporting services set-up, I have got all necessary green mark on the configuration manager window.

Then I tried to access the Report server http://localhost/reportservers (Normally ReportServer but I configured as ReportServers, you can check the step-bi-step configuration here) and I have got the following page! Great!!

After this I tried to access the Reports url http://localhost/reports and I have got the following wired error message!

And the Source Error and Stack trace are:

I did Google about this and found many suggestios & solutions but this Msdn thread help me to solve this issue, here are the steps:

Step 1: Find out the “RSWebApplication.config”file. (Normally it is located “C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\Reports” but for me under “D:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager”)

Step 2: Check the <ReportServerVirtualDirectory> tag element is empty (Check the error message now “element is missing” no?)

Step 3: If so, just add the ReportServer  value in the ReportServerVirtualDirectory tag.

That’s all, we are safe and this problem solved, but you might be getting an other error message at this point (I have resolved this here).

I don’t really know why this element is missing, while configuring “Web Service Identity” it should be added in the config file. Anyway this is a known issue but I wanted to keep for my future reference and this UI steps would save some time of yours too.

References:

1. http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/48a1e733-1a47-4c79-b01e-d84fafa779b4/

2. http://www.sqlservercentral.com/Forums/Topic697167-146-1.aspx#bm697180 

, , , , , , , ,

1 Comment