Tuesday, August 27, 2013

Getting all the records from a table and displaying them in a DataGridView (Visual Basic.Net Version)

Written by: Biniam Asnake


Assuming you have already took the pleasure of creating a Visual Basic Windows Application and you have a database in SQL Server. For this case let’s call the database “Industry”


1st. Create app.config file (By right clicking on your project’s name in Solution Explorer)
2nd. Paste the following code


<connectionStrings>
   <add name="connectionString"
       connectionString="SERVER=(local);; DATABASE = Industry; Integrated Security=True;
Trusted_Connection= Yes;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

Tuesday, August 20, 2013

Script To Migrate Data from Excel to SQL Server

Simply, here is a script to insert data from your xls or xlsx data to SQL Server.

-- This script Migrates data from Ms. Excel to Ms. SQL Server
-- GLOBAL CONFIGURATIONS For ALL Senarios
sp_configure 'show advanced option', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Solving mySql exception: ERROR 1396 (HY000): Operation CREATE USER failed for 'somebody'@'%'

To solve the problem of creating using which raises an error "ERROR 1396 (HY000): Operation CREATE USER failed for 'somebody'@'%'", run the following script with the proper changes(user name and password).


grant all privileges on *.* to somebody@localhost identified by 'password' with grant option; 

Wednesday, August 7, 2013

Solving com.microsoft.sqlserver.jdbc.sqlserverexception the result set was generated for update

To solve the following issue,
"com.microsoft.sqlserver.jdbc.sqlserverexception the result set was generated for update"

add the following code to your Stored Procedure/SQL script.

SET COUNT OFF

Monday, August 5, 2013

Solving Exception: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

If such error is thrown in SQL Server 2012 Management Studio, 
"Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online."


  1. Right Click on your Server 
  2. Select Facets
  3. Select "Surface Area Configuration" from the combo box 
  4. Change the "Ad Hoc Distributed Queries" to True.