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.

-- Truncate the log by changing the database recovery model to SIMPLE.
-- Shrink the truncated log file to 1 MB.
-- Reset the database recovery model.

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.


	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.


	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


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


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.


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 에서 최신버전으로 업데이트

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

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

Importing and Exporting Data with MS-SQL Server 2005 Express

Reference: Data import / export with SQL Server Express using DTS Wizard

Microsoft provides a free DBMS software called MS-SQL Server 2005 Express (SQLExpress). It is almost same as the commercial version of MS-SQL Server 2005 (SQL). Developers can use SQLExpress without restriction, so it is quite convenient. However, there is a deficiency SQLExpress has. It doesn’t provide a tool helps import and export data, which is critical.

While using DBMS, data migration from one server to another must be happening. It’s inevitable. However, SQLExpress doesn’t seem to provide this functionality. Well, actually, the fact is that such function is hidden, not exposed.

In order to use this data transferring, do the following steps:

  1. Check if DTSWizard.exe exists or not. It should be located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory.
  2. If it doesn’t exist, a toolkit must be downloaded and installed. To install the toolkit, click HERE.
  3. While installing, use full install option or, at least, install the "Business Intelligence Development Studio" option, to install the DTSWizard.exe.

SQL Server Management Studio Express (SSMSE)는 MS에서 무료로 제공하는 DBMS 이다. MS-SQL Server 2005 의 기능들 대부분을 사용할 수 있기 때문에 개인 개발자 입장에서는 상당히 편리한 툴이라 할 수 있다. 그런데, 한가지 단점이 있다면, 이 SSMSE 자체적으로 Import!/Export 툴을 제공하지 않는다는데 있다. Express 버전이 아닌 일반 버전에서는 들어있는 기능이 없는 것이다.

DBMS 에서 가장 중요한 기능 중에 하나는 현재 서버의 데이타를 다른 서버로 이전하거나 그 반대의 경우에 해당하는 작업을 하는것일텐데, 그 기능이 없다는 것은 반쪽짜리 DBMS 엔진일 뿐이다. 그러나, 기본적으로 내장되어 있지 않을 뿐 실제로 존재하기는 한다.

우선 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe 파일이 있는지 먼저 확인을 해보자. 만약 DTS 디렉토리가 없다면, SSMSE Toolkit 파일을 별도로 설치해야 한다. 설치에 필요한 링크는 [이곳을 클릭]

설치할 때 풀옵션으로 다 설치해도 되고, 최소한 "Business Intelligence Development Studio" 옵션은 선택해야만 한다. 그래야지 DTS 디렉토리가 설치가 된다. 이렇게 설치가 끝난 다음에 보면 해당 디렉토리가 존재하고 그 안에 DTSwizard.exe 파일을 찾을 수 있을 것이다. 이것이 바로 Import/Export 마법사가 되겠다.

개인적인 생각으로는, Express 버전 자체가 개발자가 자신의 로컬 컴퓨터에서 개발을 위한 목적으로 돌리는 것인지라 굳이 Import/Export 기능을 필요로 하지 않는다고 간주하고 빼버렸을 것이라고 추측한다만, 그래도 어디서 더미데이터를 받아오려면 당근 있어야 되는 기능 아니겠어? ㅡㅡ? 다행히 그 많은 데이타들을 일일히 수작업으로 안돌리게 되니 다행이로구만. ㅎㅎ

Written by Justin Yoo

23/05/2009 at 14:16

Setup connectionStrings in web.config for MS-SQL Server 2005 Express

Reference: http://purusworld.blogspot.com/2007/12/sql-server-fix-error-40-could-not-open.html

One of the most important files for ASP.NET web sites is "web.config". As we know through its name, it configures the ASP.NET web sites. Of course, connection between a web site application and database servers can be set up via this file.

   1: <web.config>

   2:     <connectionStrings>

   3:         <add name="dev" connectionString="Server=[SERVERNAME]\SQLEXPRESS;Database=dev;UID=sa;Password=;" />

   4:         <add name="live" connectionString="Server=localhost;Database=live;UID=sa;Password=;" />

   5:     </connectionStrings>

   6: </web.config>

Like above, if MS-SQL Server 2005 Express is used, developers should identify not only the database server, but also name of the instance. Otherwise, error message is the only output.

From the example above, dev uses MS-SQL Server 2005 Express (SQLExpress) and live uses MS-SQL Server 2005 (SQL). In order to connect to SQLExpress, both server name and instance are used, while, only server name is used for the SQL connection.

닷넷 프로젝트에서 중요한 역할을 하는 파일 중 하나가 web.config 이다. 이 파일을 통해서 여러가지 설정들을 할 수 있는 데, 그중 하나가 DB서버와의 커넥션을 설정하는 것이다.

위와 같은 형태로 보통 세팅을 하게 되는데, 여기서 중요한 것은 서버이름을 지정할 때, 로컬PC에 세팅해 놓은 DB를 사용하려면 인스턴스의 이름을 적어야지, 그 밑처럼 localhost 이렇게 적었다간 맨날 에러메시지만 보게 된다. 참고할 것.

Written by Justin Yoo

23/05/2009 at 09:16

Connection Error in MS-SQL Server 2005 Express

Reference: http://forums.asp.net/p/909569/1015432.aspx

Even though source codes don’t have any error, when developers install MS-SQL Server Express on their local PC, they often face an error message like:

Server user ‘[SERVERNAME]\ASPNET’ is not a valid user in database ‘[DATABASE]’. Cannot open database requested in login ‘[DATABASE]’. Login fails. Login failed for user ‘[SERVERNAME]\ASPNET’.

This error is, of course, not the source code problem. Rather, it is the problem on Windows permission. In order to work this out, ASPNET account must be registered on the MS-SQL server. Detailed processes are:

  1. Open SQL Server Management Studio or SQL Server Management Studio Express.
  2. Add ASPNET account on the server and assign db_owner permission with this account.
  3. Open "Computer Management".
  4. Add ASPNET account onto the appropriate SQL server user group.

로컬PC에 SQL Server Express 버전을 설치해서 테스트용으로 쓸 때 종종 나오는 메시지가 있다. 소스코드상으로는 아무런 문제도 없는데, 계속해서 연결이 안된다고 할 때 오는 메시지.

Server user ‘[서버이름]\ASPNET’ is not a valid user in database ‘[DB이름]’. Cannot open database requested in login ‘[DB이름]’. Login fails. Login failed for user ‘[서버이름]\ASPNET’.

당연하게도, ASPNET 어카운트를 SQL 서버에 등록을 시켜줘야 한다. SQL Server Management Studio Express를 실행시켜서 일단 SQL 서버에 해당 ASPNET 어카운트를 등록시켜놓고 사용하고자 하는 DB에 db_owner 권한을 준다.

그다음에 제어판 > 관리도구 > 컴퓨터 관리 로 가서 해당 SQL Server 관리 사용자그룹에 ASPNET 어카운트를 추가해주면 끝.

맨날 DB팀에서 세팅만 해주는거 갖고 쓰다가 혼자 해볼라니까 별걸 다 가지고 삽질을… ㅡㅡ;

Written by Justin Yoo

23/05/2009 at 08:11