Posts Tagged 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!
Accessing the data from the database Or some other external data source and display on a SharePoint site is not a big deal these days. As developers we can create a web part and deploy it on the SharePoint but without writing any code, how we can display? BDC is the way to do so…!
Business Data Catalog
I am going to use very basic steps to create the BDC on my SharePoint 2007. If you want to create from the scratch without using any tools such as Application Definition Designer then you can follow this post.
These are the simple 4 steps I am going to use:
- Create a table and insert some values in SQL Server database
- Create an ADF using Application Definition Designer
- Import the ADF file into the Shared Service Provider(SSP)
- Configure the BDC on SharePoint
CREATE A TABLE AND INSERT SOME VALUES IN SQL SERVER DATABASE
You can see this post to create a sample database and we will retrieve this data using BDC. I have named Database1 and Table1
CREATE AN APPLICATION DEFINITION FILE (ADF) USING APPLICATION DEFINITION DESIGNER (ADD)
You can see this post to create an ADF sample and the file name is ADF_Table1
IMPORT THE ADF FILE INTO THE SHARED SERVICE PROVIDER (SSP)
Go to Central Administration (CA) and click your shared service provider (SSP) (Mine is SharedServices2)
Click Import application definition in the Business Data Catalog section
Click Browse, and select the ADF which we have created in the previous step
Click OK to finish the process!
If you want to manage the security just click the manage permissions and do so, that’s all we are done with the step 3! 🙂
CONFIGURE THE BDC ON SHAREPOINT
The last step is to use the Business Data List Web Part to show our data, go to any site and add a Business Data List Web Part to the page.
Open the tool pane
In the Business Data List task pane, click the browse button
The following Business Data Type Picker popup window will appear, you could see our Application Data File, Select the ADF_Table1_Instance
Click OK and publish the page, you can see the data on the web part!
That’s all guys! Happy BDC ing, we will see next BCS on SharePoint 2010! Ensoy..!!
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
I was working on BDC (Business Data Catalog), to display the user profile data from SQL Server in SharePoint 2007. I was using “Application Definition Designer” (A Microsoft tool that comes with the SharePoint Server SDK, you can see the installation and configuration here) to generate the ADF (Application Definition File). I tried to access the Business Data Types on the SharePoint page and then I have got the following wired error message. 🙂
BDC is not a really new stuff, so I tried to find the solution on the web but I didn’t get any direct solution immediately. Most of them are suggested to check the permission but I have adequate permission for BDC. Finally I have got the solution form this thread. Actually this is a bug in the Application Definition Designer and I don’t really know why it’s still there!
There might be two reasons to getting this error.
1. Not having enough permission to access the BDC
2. The “Finder” method is not generating properly in the “MethodInstanceType” property.
In my case I had adequate permisioion for the BDC, so the “Finder” method was my problem.
You can see the “MethodInstanceType” property has “IdEnumerator” method instead of the “Finder”. So just click the drop down list and select the Finder method.
Then just export the ADF again, the Business Data Type Picker is showing the entries now huh? 🙂