Archive for the ‘Development’ Category

As you may be knowing, you can use Windows PowerShell to change registry values. In this article I am going to do five things.

I have created few registry entries to use in this example as seen below. In real world you can use whatever entries in your registry. It is always advisable to backup your registry before changing it.

clip_image002[13]

1. Set a registry key value.

To set a value you need to use the “Set-ItemProperty” cmdlet as below.

Set-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue2" –Value “TestData2”

Above command will put “TestData2” in the registry key “TestValue2” located in HKEY_LOCAL_MACHINE\Software\Test\Live.

2. Read a registry key value.

Reading from the registry can be done by using the cmdlet “Get-ItemProperty”.

Below command will get the value in the “TestValue1” key.

Get-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue1"

3. Using variables in PowerShell.

Here I am going to read a registry key value and put it to another registry key. This can be done using a variable. First you need to read the value into a variable using the “Get-ItemProperty” cmdlet and that value can be saved using the “Set-ItemProperty” cmdlet.

  1. # Check for the existance of the registry key.
  2. IF (Get-ItemProperty -Path “HKLM:\Software\Test\Live” -Name “TestValue1” -ea 0)
  3. {
  4.     # Fetching the value from TestValue1.
  5.     $OldValue = Get-ItemProperty -Path “HKLM:\Software\Test\Live” -Name “TestValue1”
  6. }
  7. ELSE
  8. {
  9. # Inserting a blank, if the registry key is not present.
  10.     $OldValue = “”
  11. }
  12. # Printing the value in the variable.
  13. Write-Host $OldValue.TestValue1
  14. # Setting the value to TestValue2.
  15. Set-ItemProperty -Path “HKLM:\Software\Test\Live” -Name “TestValue2” -Value $OldValue.TestValue1

4. Working with registry keys with spaces.

In case your registry keys contain spaces, you need to use double quotes in your script as seen below.

  1. # Check for the existance of the registry key.
  2. IF (Get-ItemProperty -Path “HKLM:\Software\Test\Live” -Name “Test Value 1” -ea 0)
  3. {
  4.     # Fetching the value from Test Value 1.
  5.     $OldValue = Get-ItemProperty -Path “HKLM:\Software\Test\Live” -Name “Test Value 1”
  6. }
  7. ELSE
  8. {
  9.     # Inserting a blank, if the registry key is not present.
  10.     $OldValue = “”
  11. }
  12. # Printing the value in the variable.
  13. Write-Host $OldValue.“Test Value 1”
  14. # Setting the value to Test Value 2.
  15. Set-ItemProperty -Path “HKLM:\Software\Test\Live” -Name “Test Value 2” -Value $OldValue.“Test Value 1”

 

5. Saving PowerShell commands as scripts and running them.

Both above can be saved as a PowerShell script by saving it in a file with the extension ps1. For example I did save it as “ChangeReg.ps1” in my C drive inside the folder “new”. Then the script can be run by browsing to the folder and using the command “.\ChangeReg.ps1”.

clip_image002[10]

After the script is run my registry keys looked like this.

clip_image002[3]

In case you need to retrieve values from other registry hives (locations), following table may be helpful.

 

Registry Hive

Abbreviation

1. HKEY_CLASSES_ROOT HKCR
2. HKEY_CURRENT-USER HKCU
3. HKEY_LOCAL_MACHINE HKLM
4. HKEY_USERS HKU
5. HKEY_CURRENT_CONFIG HKCC

 

In case you need to read more on “Get-ItemProperty” and “Set-ItemProperty”, use the links to visit official documentation from Microsoft TechNet.

Recently while deleting a service application my system got unresponsive and while trying to create the service application again i constantly got the following error.

“An unhandled exception occurred in the user interface.Exception Information: An object of the type Microsoft.SharePoint.Administration.SPIisWebServiceApplicationPool named "SearchServiceApplication" already exists under the parent Microsoft.SharePoint.Administration.SPIisWebServiceSettings named "SharePoint Web Services". Rename your object or delete the existing object.”

This seems to be happening because one of the service application pools were not removed while deleting the service application. This can be removed by using PowerShell.

First load the SharePoint 2010 Management Shell by navigating to Start –> Microsoft SharePoint 2010 Products –> SharePoint 2010 Management Shell.

SPStartMenu

Then use the “Get-SPServiceApplicationPool” command to view all the existing service application pools.

Get-SPServiceApplicationPool

Now use the “Remove-SPServiceApplicationPool -Identity SearchServiceApplicationPoolName” command to remove the service application you want removed. If the service application pool’s name is having spaces remember to use double quotes.

Remove-SPServiceApplicationPool -Identity SearchServiceApplicationPool1

PowerShell will ask to confirm the deletion, after confirming the action, it will remove the service application pool.

clip_image002

Recently while trying to import some data from Excel onto SQL on a new machine I got the following error at SQL Server Import and Export Wizard Step 2. After doing some searching found the solution is to install Office 2007 Data Connectivity Components System Driver.

image

If you need to  get it fixed use the following link to get it from Microsoft Download Center.

http://www.microsoft.com/en-us/download/details.aspx?id=23734

By using the following query you will be able to get all the tables with at least one record in the selected database. It will be handy when you needed to get an idea of the number of records in a database. This uses two SQL Server system views, namely SysObjects and SysIndexes.

  1. SELECT sysobjects.name AS [Table Name],
  2.         MAX(sysindexes.rows) AS [Number of Records],
  3.         sysobjects.crdate AS [Created Date],
  4.         sysobjects.refdate AS [Referenced Date]
  5. FROM sysobjects
  6. INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
  7. WHERE sysobjects.xtype = ‘U’ — Filtering all the User Tables.
  8.     AND sysindexes.rows > 0 — Getting all the tables having at least one reoord.
  9. GROUP BY sysobjects.name,
  10.     sysobjects.crdate,
  11.     sysobjects.refdate
  12. ORDER BY 2 DESC — Ordering by the number of records in table.

 

Above query will bring the following results on the Northwind database.

image

Request Filtering

I recently got this error in one of my applications. When troubleshooting, I found my application is configured to have the default value of 30000000 Bytes for the content length, which was not sufficient for me in some of the file uploading functionalities.

Simply adding / increasing the number in the configuration file corrected this error.

Request Content Length in Bytes.

Default Value – 30000000 Bytes (~28MB)

Maximum Value – 4284229877 Bytes (3.99GB)

  1. <system.webServer>
  2.   <security>
  3.     <requestFiltering>
  4.       <requestLimits maxAllowedContentLength=100000000></requestLimits>
  5.     </requestFiltering>
  6.   </security>
  7.   …
  8. </system.webServer>

 

To obtain more information read this article.

By now as you know, in this post I am going to discuss about two SQL commands we use regularly to clear data in our tables.

Usage

  1. DELETE FROM TABLENAME
  2. TRUNCATE TABLE TABLENAME

Even though they do a similar thing, there are some differences which makes them unique commands.

 

DELETE

TRUNCATE

1. Considered as Data Mining Language (DML) statement. Considered as Data Definition Language (DDL) statement.
2. Can be used to delete all or part of data in a table. Will delete all the data in a table.
3. Will log the actions. There will be no logging on record deletion.
4. Will use more locks. Will use fewer locks.
5. Will use more resources. Will use less resources.
6. Slow. Fast.
7. Will not reset the identity seed. Will reset the identity seed to 0.
8. Can be used to delete data in tables having relationships. Cannot use in tables having relationships.
9. Can be used in tables involved in log shipping or replication. Cannot be used in tables involved in log shipping or replication.
10. Transaction can be rolled back. No rollback.
11. Table may keep the empty pages. Can be released by running, SHRINKDATABASE (Database Name). Data pages related to the table will be de allocated and returned to the system.
12. Related Triggers are fired. Triggers are not fired.

 

If you used DELETE to fully remove all the records, you can reset the identity value by running the following command.

  1. DBCC CHECKIDENT (TABLENAME, RESEED, 0)

If you do have some rows left in table, simply replace 0 with the last identity column value. For example if you put 5 instead of 0 then the next record inserted into the table will have an identity column value of 6.

If you jus need to check the current identity value just use the following command.

  1. DBCC CHECKIDENT (TABLENAME, NORESEED)

Importing Data into SQL

Posted: September 30, 2012 in SQL Server
Tags: , , ,

If you need to import data from a file, this can be achieved by using SQL Bulk Insert command. Recently I did use this method to import some 500 000 data from few comma separated value (CSV) files and thought to share it with you. What you need to remember is, if you are importing data from more than one file the data should have the same format through out the files.

This is a sample set of data I imported into my table from the file named File1.csv.

Login,Name,Date,Result,Pass

U0001,Roman Silva,1/10/2010 17:23,100,TRUE

U0002,Anthony Don,28/09/2010 10:01,70,TRUE

U0003,Saman Perera,16/09/2010 11:31,90,TRUE

U0004,Silvia Raz,26/09/2010 22:11,40,FALSE

U0005,Rebecca Maine,18/09/2010 11:30,100,TRUE

I used the following script to create a temporary table for my imported data.

  1. SET ANSI_NULLS ON
  2. GO
  3.  
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6.  
  7. CREATE TABLE [dbo].[TABLENAME_ImportedData](
  8.     [LoginId] [NVARCHAR](50) NOT NULL,
  9.     [Name] [NVARCHAR](200) NULL,
  10.     [Date] [DATETIME] NULL,
  11.     [Result] [INT] NULL,
  12.     [Pass] [NVARCHAR](8) NULL
  13. ) ON [PRIMARY]
  14.  
  15. GO

To fetch data from the file I used the following script.

  1. BULK INSERT [TABLENAME_ImportedData]
  2. FROM 'D:\DataFiles\File1.csv'
  3. WITH (
  4.          FIELDTERMINATOR =',', — Since my columns are seperated using commas (,).
  5.          ROWTERMINATOR ='\n',  — Since each data row is in its own line.
  6.          FIRSTROW = 2          — Since my first row is having column names. Please note FIRSTROW is not recommended to skip the column names.
  7.       )

While running the script I faced an issue with the date since my server was set to US English as the default language. In US English the dates should be in MDY format. So I had 3 choices, either to change the date formats on my data files, change the server default language to another language which has its date format as DMY or change the date format of the server. I used the easy way to change the date format of the server by running the following command.

  1. SET DATEFORMAT dmy

If you like to change SQL server default language and need help please read my article on that.