Archive for category SQL Server
TF255507: The security identifier (SID) for the following SQL Server login conflicts with a specified domain or workgroup account
When I was configuring the TFS 2010 after completing the installation, I have got the following error message!
This is the brief error message and you can see the details results and solution here
"TF255507: The security identifier (SID) for the following SQL Server login conflicts with a specified domain or workgroup account"
Basically the workgroup account I have choosen to host the TFS databases ( SP2010\STEPBISTEPSQL) is SP2010\Administrator but the original SQL server login account is WIN-5DWGQLQJNRV\Administrator so both conflicting!
To solve this issue, we need to rename the conflicts login and we will see the steps below!
Solution 1: Open the command prompt and type the following command and then hit Enter key!
“sqlcmd -E -S “””
Actually this command is altering the login id with the correct account.
Solution 2: Add the account using the SQL server UI! Open the SQL server and select the Security–>Logins, you could see the conflict login id, just remove this id.
Right click on the logins and then select the New Login..
You will get the following window, enter the Login name and hit OK
That’s all, we are good to go! Here is the renamed login screen!
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!
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)
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Database1') CREATE DATABASE Database1 GO
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!
Everybody knows about these statements but I just wanted to post this for quick references and also there is a new DML statement (MERGE) available in SQL Server 2008.
SQL commands are instructions which are normally used to communicate with the database to perform specific task and various other functions. Depending on the functionalities we can divide into 4 groups. They are DDL, DML, DCL and TCL.
DDL (Data Definition Language): is a vocabulary used to define data structures in SQL Server. Use these statements to create, alter, drop and truncate data structures in an instance of SQL Server. (MSDN Definition, you can read more here)
- CREATE : creates an object (a table, for example) in the database.
- ALTER : database modifies the structure of an existing object in various ways (Eg: adding a column to an existing table)
- DROP : deletes an object in the database, usually irretrievably.
- TRUNCATE : remove all records from a table, including all spaces allocated for the records are removed
DML (Data Manipulation Language): is a vocabulary used to retrieve and work with data in SQL Server. Use these statements to add, modify, query or remove data from a SQL Server database. (MSDN Definition, you can read more here)
- SELECT : retrieves data from one or more tables, or expressions
- INSERT : adds rows (formally tuples) to an existing table
- UPDATE : modifies a set of existing table rows
- DELETE : removes existing rows from a table, the space for the records remain
- MERGE : insert, update or delete operations on a target table. Normally called UPSERT operation
DCL (Data Control Language): is a vocabulary used to provide security such as roles and permissions in SQL Server. Use these statements to grant and revoke permissions to SQL Server database.
- GRANT : gives user’s access privileges to database
- REVOKE : withdraw access privileges given with the GRANT command
TCL (Transaction Control Language): is a vocabulary used to manage the changes made by DML statements in SQL Server. Use these statements to commit or rollback the transaction in a SQL Server database. (Read more here)
- COMMIT : causes all data changes in a transaction to be made permanent
- ROLLBACK : causes all data changes since the last COMMIT or ROLLBACK to be discarded, leaving the state of the data as it was prior to those changes.
- SAVE TRANSACTION : save the state of the database at the current point in transaction
Please Note: I didn’t get a chance to work with ‘Save Transaction’, if you want to read more about this please click here
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
That’s all! These are pretty easy methods, if we know about these then we can answer quickly.
SSRS Error 3: The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element.
1. SSRS 2005 64bit
2. Windows 2008 R2 64bit
3. IIS 7.5
Then I tried to access the Reports url http://localhost/reports and I have got the following error message!
And the Source Error and Stack trace are:
To fix this issue, just follow these steps, the same modification of the “RSWebApplication.config” will solve this issue too.
SSRS Error 2: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
After configuring the Reporting Services (SSRS 2005), I tried to access the Report url http://localhost/reports and I have got an error saying “The ReportServerVirtualDirectory element is missing” then I fixed the issue you can check the steps in my previous post. After resolving this issue, I could access the ReportManager page but with the following error:
This means that I have configured the Reporting Server but cannot be reachable via http://localhost/ and I tried in several ways to fix this issue and finally end-up with the following solution:
Step 1: Open the “RSWebApplication.config” file in a Notepad or any other editor. (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 whether <ReportServerUrl> and <ReportServerVirtualDirectory> elements having any values.
Step 3: If <ReportServerUrl> tag’s element is empty then add the full url of the ReportServer (Not Reports).
Please note: I have tried with server IP, Server Name and localhost, all are working fine for me and if you want to use server name and it ‘s using different port number other than 80, then the ReportServerUrl should be: http://servername:portnumber/ReportServer.
Step 4: If <ReportServerVirtualDirectory> tag’s element has any value (for me ReportServer is there, which was added to fix the previous error) then just delete it and leave it empty.
Step 5: Recycle the ReportServer’s App-Pool to reload the “RSWebApplication.config” file. (Just go to the IIS –> Application Pool and click the Recycling on the right hand panel)
That’s all, hope you would get the following page! 🙂
SSRS Error 1: An error occurred while parsing the configuration file. The ReportServerVirtualDirectory element is missing
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.