Just in Chronicles

Life as a Voyage

Archive for the ‘For Database’ Category

Downsizing MS-SQL Server 2008 R2 Database Log

leave a comment »

When MS-SQL database files need to do backup and restore, we need to shrink the size of log file as it’s rarely necessary. In order to downsize the log file, try the following script on your MS-SQL Server Management Studio.

USE [YOUR_DATABASE_NAME];
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [YOUR_DATABASE_NAME]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([YOUR_DATABASE_LOG_NAME], 1);
GO
-- Reset the database recovery model.
ALTER DATABASE [YOUR_DATABASE_NAME]
SET RECOVERY FULL;
GO

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx

Written by Justin Yoo

05/01/2013 at 21:35

How to Restore Data to MS-SQL Server 2008 from MS-SQL Server 2008 R2

Like other DBMS, MS-SQL Server doesn’t support backward compatibility, in regards to the database backup and restore function. So, it’s not possible to restore data to MS-SQL Server 2008 (SQL 2008) from the MS-SQL Server 2008 R2 (SQL 2008 R2) backup. Officially SQL 2008 has its version number as 10.0 and SQL 2008 R2 has 10.5. However, instead of using the backup and restore function, we can simply generate SQL script to handle the database schema and its data. This is how we make this possible.

Step #0. Open the MS SQL Server Management Studio (MS-SSMS).

Step #1. Select the database you’d like to create script and right mouse click, and you’ll see the menu below.

Step #2. Now, click the “Generate Scripts…”, and “Generate and Publish Scripts” wizard will be popped up. Click “Next”.

Step #3. Select appropriate option. Here, we’ll choose the first option, “Script entire database and all database objects”. Click “Next”

Step #4. Choose the output option. We can simply select to save the script to our local machine or screen. Click “Advanced”.

Step #5. This is the most important step. Select the red boxed options accordingly. For fresh restore, all objects must be dropped and re-created. Therefore,  choose “Script DROP and CREATE” option. Server version must be “SQL Server 2008” as we’ll run this script on SQL 2008. Finally, “Schema and data” must be chosen as we’ll restore all objects and their data. Then click “OK”.

Step #6. Once the “Advanced” option is set, you’ll see the Step #4 screen again. Click “Next” and you’ll see the summary screen. Click “Next” again.

Step #7. The wizard creates the script we need. Click “Finish” and you’ll see the generated script on your screen or in your specific location.

That’s it! It’s not the perfect or desirable way to restore data to SQL 2008 from SQL 2008 R2, but useful workaround. If you get some error while you are running the generated script on SQL 2008, you might need to modify running order in the script.

Written by Justin Yoo

01/03/2012 at 11:05

Posted in For Database

Tagged with , , , ,

How to Connect MS-SQL Server from Virtual Machine

Let’s say there is a server environment up and running on a virtual machine – regardless that it is MS Virtual PC, VM Player or whatever. The server environment is only for web sites. Let’s say that a database development server is installed on a local machine, not on a virtual machine. The web server needs to have a connection to the database. However, sometimes the database won’t allow the VM’s access, even though “ping” is sent from VM to the local.

In this case, the following is worth checking.

  1. Run SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server 2008 R2 (or other versions) > Configuration Tools
  2. Find SQL Server Network Configuration > Protocols for MSSQLSERVER (or SQLEXPRESS, or another instance)
  3. Set the protocol – TCP/IP – to “Enabled”

That’s it! Now try to connect the database from your virtual machine. It should be working. All good, mate!

Written by Justin Yoo

04/07/2011 at 16:17

State Fields Explicitly to Avoid Fetch Error in PHP

Reference: String returned to Output Parameter from Stored Procedure is truncated

In PHP scripts, calling stored procedures from MS-SQL is necessary depending on a web application. In this case, if you are using MSSQL driver for PHP instead of the MSSQL module provided as a default driver, the following error message is sometimes seen.

01004 – [Microsoft][SQL Native Client]String data, right truncation

This is caused because the SELECT query does not specify fields explicitly like:

SELECT * FROM dbo.TableName

In order to avoid this error, just state field name explicitly like:

SELECT Field1, Field2 FROM dbo.TableName

Then, the error message above will no longer come out.

Written by Justin Yoo

08/02/2010 at 23:46

Converting Date Format from String to DateTime with No Hassles

Reference: CAST and CONVERT (Transact-SQL)

For I18N or L10N, there are always problems on currency, date and time formatting. All different countries or cultures have their own format to represent their currency, date and time.

In order to overcome these hassles, regardless of country or culture settings, developers should consider two things – one is for the presentation part and the other is for the business logic part.

Any application regarding to support multiple languages or countries, must follow the convention belong to the culture. However, for the business logic which happens behind the scene doesn’t need to follow the convention but follow developers’ convention.

Therefore, the internal data conversion job that is independent from any culture, should be explicitly conducted. The following SQL query is as an example.

CASE#1

SELECT
	CONVERT(DATETIME, '10/09/2009')         AS en-US,
	CONVERT(DATETIME, '10/09/2009', 103)    AS en-GB

The case above convert a string-formatted date to DATETIME format. The query will show the result following:

en-US			en-GB
----------------------- -----------------------
2009-10-09 00:00:00.000 2009-09-10 00:00:00.000

What’s the difference between two fields? In the American culture, the string is considered as 9th of October, 2009, while in the British culture, the same string is regarded as 10th of September, 2009. Now, let’s see the other case.

CASE#2

SELECT
	CONVERT(DATETIME, '15/09/2009')         AS en-US,
	CONVERT(DATETIME, '15/09/2009', 103)    AS en-GB

This case also tries to convert a string-formatted date to DATETIME format. However, it returns the error like:

en-US			en-GB
----------------------- -----------------------
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Because of the same reason above, in the US culture, the string is regarded as 9th of ???, 2009. However, there is no 15th month of a year. That’s why the error above is shown.

To sum up, data conversion must be careful, if it’s related to a certain cultural difference. Otherwise, it will be misunderstood.


화폐단위나 날짜포맷 바꿀 때 반드시 고려해야 할 사항.

Written by Justin Yoo

11/09/2009 at 01:51

Handling Large Data Values for System.Data.SqlClient.SqlParameter

References

Data manipulation in web applications, regardless of whatever languages used for their development, is inevitable. In order to handle data through ADO.NET, SqlParameter class is the key object to define data types. It requires both data type and data length.

Defining data length is not necessary when its ParameterDirection is either ParameterDirection.Input or ParameterDirection.ReturnValue. However, if the ParameterDirection is set as ParameterDirection.Output or ParameterDirection.InputOutput, data length must be defined. Otherwise, it shows the following error.

the Size property has an invalid size of 0.

Since MS-SQL 2005, VARCHAR, NVARCHAR and VARBINARY data types have had MAX as their data length. In order to set a particular data length for these data types, the following code lines are typical.

...
SqlParameter param = new SqlParameter("@value", SqlDbType.NVarChar, 100);
param.Direction = ParameterDirection.Input;
param.Value = "Certain Value";
...

But, how can it set its data length, if the NVARCHAR parameter has MAX data length? Simply set it as –1. –1 is the magic number for this data length. Therefore, the following code lines are possible.

...
SqlParameter param = new SqlParameter("@value", SqlDbType.NVarChar, -1);
...

데이타 길이 처리를 할 때 MAX 인 경우에는 -1로 세팅하면 된다. -1이 만능일세~

Written by Justin Yoo

15/08/2009 at 12:41

MSXML6 Errors while Installing MS-SQL Server 2005 on WindowsXP SP3

References:

After developers update their WindowsXP to SP3, installing MS-SQL Server 2005 (SQL) or MS-SQL Server 2005 Express (SQLExpress) causes MSXML6.0 error that wouldn’t happen on the WindowsXP SP2 environment. According to the first article, Answer SQL Server 2005 Express – error during installation on Windows XP Service Pack 3, the MSXML6 component is protected from external access by SP3 update. Therefore, in order to figure this out, related information stored in the Windows Registry should be removed. After that, the latest version of MSXML6 must be updated again.

msxml6

Detailed steps to setup this are:

  1. Download msxml6.msi from HERE or HERE (Service Pack 1). The former one is recommended to install.
  2. Open regedit.exe.
  3. Go to HKEY_CLASSES_ROOT > Installer > Products.
  4. Search MSXML6 in the ProductName field under the directory above.
  5. Delete the field.
  6. Go to HKEY_LOCAL_MACHINE > Software > Classes > Installer > Products.
  7. Search MSXML6 in the ProductName field under the directory above.
  8. Delete the field.
  9. Find and delete all registry items referring to the Product Key ID.
  10. Reinstall msxml6.msi.
  11. Reinstall SQL or SQLExpress
  12. Update MSXML6 from http://windowsupdate.microsoft.com.

개발자의 WindowsXP 환경이 SP3로 업데이트된 상태에서 MS SQL Server 2005 또는 MS SQL Server 2005 Express를 설치할 경우 MSXML 6.0 때문에 에러가 발생한다. WindowsXP SP2 까지는 아무런 문제가 없던 것인데, SP3로 바뀌면서 시스템 설정이 살짝 바뀌었기 때문에 SP3 이전에 출시한 MS SQL Server 2005 버전과 약간 충돌이 생기는 것이다. 위의 글 (Answer SQL Server 2005 Express – error during installation on Windows XP Service Pack 3)에 따르면, MSXML6 콤포넌트가 SP3 부터는 외부에서 세팅을 변경하지 못하게끔 프로텍션이 걸려서 그렇단다. 그래서 이걸 해결하려면 윈도우 레지스트리를 건드려야 한다. 레지스트리에서 해당 부분을 제거하고 난 뒤 설치를 하면 아무런 문제가 없다는 거. 물론, http://windowsupdate.microsoft.com으로 가서 맨 나중에 다시 MSXML6 를 최신으로 업데이트 해야겠지. 순서는 아래와 같다.

  1. 위의 다운로드 사이트로부터 msxml6.msi 다운로드. 둘 중 아무거나 다운받아도 됨. 가급적이면 msxml6.msi 파일을 다운받기를 권장. (msxml6_x86.msi, 즉 sp1 버전은 에러 가능성 있음)
  2. 레지스트리 에디터 실행: 시작 > 실행 > RegEdit
  3. HKEY_CLASSES_ROOT > Installer > Products로 이동
  4. ProductName 항목에 MSXML6가 들어있는 키를 검색
  5. 해당 키 삭제
  6. HKEY_LOCAL_MACHINE > Software > Classes > Installer > Products로 이동
  7. ProductName 항목에 MSXML6가 들어있는 키를 검색
  8. 해당 키 삭제
  9. 위의 Product Key ID를 참조하는 모든 항목들을 삭제
  10. msxml6.msi 재인스톨
  11. MS-SQL Server 2005 (개발자버전, 익스프레스버전 포함) 재인스톨
  12. http://windowsupdate.microsoft.com 에서 최신버전으로 업데이트

요런 순서를 따라주면 된다. 역시나 이거 알아내느라 고스트 이미지를 몇번 덮었네. ㅡㅡㅋ

야튼, 알아두면 도움이 다~ 된다. ㅋ