« » 2016.4
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

'2016/04'에 해당되는 글 8건

2016.04.28 mysql odbc
2016.04.27 dynamic pivot
2016.04.20 edition upgrade
2016.04.12 adam sp_whoisactive plus tempdb.....usages
2016.04.12 nul 백업
2016.04.08 jTDS vs MS JDBC
2016.04.06 Not every extended event is suited for all situations
2016.04.05 ssma mysql

mysql odbc

카테고리 없음 / 2016. 4. 28. 15:57

드라이버 32, 64 둘 다 깔고

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'BOA', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'BOA', @provstr=N'BOA', @catalog=N'dbname'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BOA',@useself=N'False',@locallogin=NULL,@rmtuser=N'',@rmtpassword=''

GO
EXEC master.dbo.sp_addlinkedserver @server = N'wmman', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'wmman', @provstr=N'wmman', @catalog=N'wmman'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'wmman',@useself=N'False',@locallogin=NULL,@rmtuser=N'id',@rmtpassword='pass'

select * from openquery(wmman,'select * from tb_ticket')

alter proc usp_loader
(
@sourceFolderName nvarchar(4000)
, @filename nvarchar(4000)
)
as

if object_id ('tempdb..##tMdsApiResultsTemp') is not null
drop table ##tMdsApiResultsTemp

create table ##tMdsApiResultsTemp
(cIp varchar(100)
,cApi varchar(100)
,cVersion varchar(100)
,cLastAccess varchar(100)
)

declare @sql nvarchar(4000)
=
'
BULK INSERT ##tMdsApiResultsTemp
FROM '''+@sourceFolderName+@fileName+'''
WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''0x0a'',
TABLOCK
)
'

print (@sql)
exec (@sql)

insert into tMdsApiResults (cFilename, cIp, cApiComponent, cApiPlatform, cVersion, cLastAccess)
select
@filename
, cIp
, substring(cApi, 1, CHARINDEX(':', cApi)-1)
, substring(cApi, CHARINDEX(':', cApi)+1, len(cApi))
, cVersion
, cLastAccess
from ##tMdsApiResultsTemp
go

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

dynamic pivot

카테고리 없음 / 2016. 4. 27. 18:09

과거에는 아래 방법으로 많이 했는데, 요즘은 CLR 로 하면 더 멋지게 가능할 듯 한데.....

언제 함 만들어 봐야겠다.....예전에 내가 만든건 어디 있는지도 모르겠네...쩝...

원문 : http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

http://ceusee.tistory.com/188

동적으로 PIVOT을 만들 수 있는 방법입니다.

아직도 case 문이 익숙하긴 하지만, 가끔은 오히려 더 복잡해지는 case 문 때문에 PIVOT을 사용해야 한다고 느껴질 때가 있습니다.

아직 PIVOT 익숙하지는 않은데 하다보니 컬럼 고정이 되서 불편한 점이 있는데 이 방법으로 사용하면 좋을 것 같네요.

PIVOT 은 행 형태의 반환을 열로 만들어 주는 것입니다.

아래로 테스트할 데이터를 생성 합니다.

CREATE TABLE dbo.Products
(
ProductID INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL UNIQUE
/* other columns */
);

INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');

CREATE TABLE dbo.OrderDetails
(
OrderID INT,
ProductID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT
/* other columns */
);

INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);

SELECT p.Name, Quantity = SUM(o.Quantity)
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
GROUP BY p.Name;

결과는 아래 처럼 행으로 나 옵니다. 그러나 열로 표시하고 싶어요. 하면 PIVOT 처리 합니다.

SELECT p.[foo], p.[bar], p.[kin]
FROM
(
SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
SUM(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;

원하는 결과가 나왔습니다. ~

다른 데이터를 추가 해 보죠.

INSERT dbo.Products SELECT 4, N'blat';
INSERT dbo.OrderDetails SELECT 4,4,5;

그리고 나서 다시 윗 쿼리를 실행 해 보면 결과는 동일하게 3개의 데이터의 집계만 나타납니다.

당연한 결과 입니다. 지금 추가된 blat는 보이지 않습니다. PIVOT는 집계해서 보여줄 컬럼을 명시해야 하기 때문입니다. 쿼리를 그럼 고쳐야 하는데 좀 더 쉽게 동적으로 할 수 있게 아래는 도와 줍니다.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(Name) -- += 는 컬럼을 합쳐서 한 문자로 만들어 주죠.
FROM (SELECT p.Name FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
GROUP BY p.Name) AS x;

print @columns

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
   SELECT p.Name, o.Quantity
    FROM dbo.Products AS p
    INNER JOIN dbo.OrderDetails AS o
    ON p.ProductID = o.ProductID
) AS j
PIVOT
(
   SUM(Quantity) FOR Name IN ('
   + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
   + ')
) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

-- @sql의 문은 컬럼을 만들어서 PIVOT 를 생성해 줍니다.

/*결과)
SELECT p.[foo], p.[bar], p.[kin], p.[blat]
FROM
(
   SELECT p.Name, o.Quantity
    FROM dbo.Products AS p
    INNER JOIN dbo.OrderDetails AS o
    ON p.ProductID = o.ProductID
) AS j
PIVOT
(
   SUM(Quantity) FOR Name IN ([foo],[bar],[kin],[blat])
) AS p;

원하는 결과가 도출.

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

edition upgrade

카테고리 없음 / 2016. 4. 20. 17:21

Command prompt installation is supported in the following scenarios:

Upgrading from one SQL Server edition to another edition of SQL Server.

SQL Server Setup Control

/ACTION

Required

Required to indicate the installation workflow.

Supported values:

Upgrade

EditionUpgrade

The value EditionUpgrade is used to upgrade an existing edition of SQL Server 2014 to a different edition. For more information about the supported version and edition upgrades, see Supported Version and Edition Upgrades.

https://msdn.microsoft.com/en-us/library/ms144259(v=sql.120).aspx

http://optimizer.tistory.com/entry/%ED%8F%89%EA%B0%80-%EA%B8%B0%EA%B0%84%EC%9D%B4-%EB%A7%8C%EB%A3%8C%EB%90%9C-SQL-Server-%EC%97%85%EA%B7%B8%EB%A0%88%EC%9D%B4%EB%93%9C-%ED%95%98%EA%B8%B0

setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid>/SkipRules= Engine_SqlEngineHealthCheck

평가기간이 끝난 경우 /SkipRules= Engine_SqlEngineHealthCheck 추가

https://blog.brankovucinec.com/2014/07/23/upgrade-from-sql-server-2014-express-to-standard-edition/

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

adam sp_whoisactive plus tempdb.....usages

카테고리 없음 / 2016. 4. 12. 10:48

sp_whoisactive2.txt

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

nul 백업

카테고리 없음 / 2016. 4. 12. 10:36

디스크 읽기 최대성능을 볼 때 쓸만함

BACKUP DATABASE tt

TO DISK = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
WITH NO_COMPRESSION
,BUFFERCOUNT = 2200

backup log tt to
disk ='NUL'
, disk = 'NUL'

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

jTDS vs MS JDBC

카테고리 없음 / 2016. 4. 8. 11:14

1) call pattern

jTDS 로 PreparedStatement 를 호출하면 sp_prepare sp_execute 를 호출한다.

MS JDBC 는 sp_prepexec 로 위 두 단계를 하나로 줄여서 실행한다. 한마디로 서버 클라이언트간 round-trip 횟수를 줄여 성능을 높일 수 있다. connectionString 에 prepareSQL=2 라고 옵션을 주면 sp_executesql 로 동작해 한번에 돌릴 수 있고 parameter snipping 도 가능하다. 그런데, 이건 좀 다른 동작 방식이다. 항상 sql 이 날아가야 한다.

2) parameter snipping

jTDS 로 PreparedStatement 를 사용하면 파라메터 스니핑을 사용하지 못하고

JDBC 를 사용하면 파라메터 스니핑이 가능하다.....

그런데 감당하지 못하겠으면 SQL Server 레벨에서 disable 가능하다

Parameter sniffing can be disabled using the documented and supported trace flag 4136. This trace flag is also supported for per-query use via the QUERYTRACEON hint. Both apply from SQL Server 2005 Service Pack 4 onward (and slightly earlier if you are willing to apply cumulative updates to Service Pack 3).

sql server 에서 파라메터 스니핑을 하는 경우(MS JDCB)

</ParameterList>

파라메터 스니핑을 못하는 경우 (jTDS)

</ParameterList>

3) applicationIntent 기능을 MS JDBC 만 지원한다. 4.0 이상부터

고 가용성 기능을 쓰려면 JDBC 로 가야한다.

기타)

unicode false 옵션은 주의해서 셋팅해라

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

Not every extended event is suited for all situations

카테고리 없음 / 2016. 4. 6. 11:44

SQL Server Extended Events (xevent) are great to troubleshoot many issues including performance issues or other targeted scenarios. But we keep seeing users misusing them to negatively impact their systems.

Here is a latest example. We had a customer who called our support for an issue where the same query ran fast in test but ran slow in production. In fact, the query ‘never’ finished in production in a sense that they waited for 30 minutes or more but couldn’t get it to finish. But the same query finished in seconds in test.

The query was actually a large batch that contains over 90k lines of code with many statements with size of about 800k. Our initial effort focused on comparing the differences between the two servers. But there weren’t many differences. We even eliminated database as a factor. They restored database from production to test and issue went away in test.

Through some troubleshooting, we discovered even parsing the query took a long time in production. But we simply couldn’t figure out what was going on because the statements in the batch were very simple inserts. So we took some user dumps and analyzed the call stacks. Finally we realized that the XEvent was involved. Every dump we got showed the server was producing XEvent. It turned out their developers enabled the some xevents which can cause high overhead by accident inproduction.

So we got their Xevents being captured (screen shot below). Among those, there were scan_started, scan_stopped, wait_info etc. It was generate a million events every minute without anyone else running the system. In addition to that, this customer captured sql_text for all the events. Basically the same 800k batch text would be captured for every event including wait_info etc

None of the events mentioned (scan_started, scan_stopped, wait_info ) are suited for long term capture. For this specific scenario, it was wait_info that hurt them most (combined with sql_text being included). Wait_info is produced whenever there is a scheduler yield or wait is finished. Because customer’s batch is very large, SQL needs to play nice and yields frequently. so the event gets triggered very frequently. That’s why so many events were generated and overhead led to slowdown.

Demo (tested on SQL Server 2012, 2016)

create and enable Xevents

CREATE EVENT SESSION [test_xevent] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) ),
ADD EVENT sqlos.wait_info_external(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) )
ADD TARGET package0.event_counter,
ADD TARGET package0.event_file(SET filename=N’c:\temp\test_xevent.xel’)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

alter event session [test_xevent] on server state = start

in SSMS, duplicate “INSERT INTO t VALUES (null,null,null)” 50,000 times and try parsing the query. it will take several minutes. but if you disable the Xevent session, the entire batch will parse in seconds.

If you have to capture wait_info for large batch like this one, consider taking out sql_text. It will be hard to look at the trace afterwards without it. But hopefully, it is a controlled environment and you know which session you are troubleshooting and filter on that.

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

ssma mysql

카테고리 없음 / 2016. 4. 5. 17:48

https://blogs.msdn.microsoft.com/ssma/2011/02/07/mysql-to-sql-server-migration-how-to-use-ssma/

MySQL to SQL Server Migration: How to Use SSMA

★★★★★

★★★★

★★★

★★

★

February 7, 2011By Bill Ramos

Bill Ramos

Indigo Slate

Partner

12,865 Points 12 2 2

Recent Achievements

First Marked Answer First Helpful Vote Blog Party Starter

By Bill Ramos, Advaiya

[Updated 2/6/2012 Han Wong – Microsoft SQL Server Migration Assistant (SSMA) for MySQL v5.2. The information provided below is still valid for SSMA for MySQL v5.2. Users should download the lastest SSMA for MySQL]

In this blog, I’m going to walk you through the process of converting the MySQL Sakila-DB sample database to SQL Server 2008 R2 Express using the SQL Server Migration Assistant for MySQL v1.0 [Updated: Please obtain the lastest SSMA for MySQL] (SSMA). The Sakila-DB database has tables, views, stored procedures, functions and triggers that make the conversion interesting. The sample is based on the Inno-DB example, but does have one MyISAM table. SSMA also allows you to migrate your MySQL databases to SQL Azure, but we’ll save that topic for another post.

Downloading SQL Server 2008 R2 Express and SSMA

The easiest way to download SQL Server 2008 R2 Express, SQL Server Management Studio and SSMA is through the Microsoft Web Platform Installer (WPI). Once you’ve downloaded WPI, you can select from a variety of tools and products that can get you up and running using IIS, PHP, and SQL Server in no time.

I’ll focus on the minimum set of tools you need to get SQL Server 2008 R2 Express and SSMA up and running. once you launch WPI, click on the Products tab at the top tool and then select Database in the navigation page. In the image above, I’ve already installed the tools, but for the new install, you’ll click on the Add buttons to the right of the circled products to get you up and running. If you are running your application under PHP, you might also want to select one of the two PHP drivers for SQL Server as well. Once you’ve selected your tools, just click on the install button to start the process.

Downloading the MySQL ODBC Driver

WPI is not without flaws. SSMA requires the “MySQL OSBC Driver 5.1 or above” download to connect to your MySQL instance that comes from the MySQL downloads site. Once at the Download Connector/ODBC page, your need to download either the x32 or x64 version of the driver based on the machine architecture for the system you are running the SSMA client. Just follow the installation instructions from the installer. The default installation settings will be good enough to get you going.

Other Helpful Downloads for SSMA and this Blog

You’ll also want to download the “Guide to Migrating from MySQL to SQL Server 2008” white paper, though this blog and others to follow will keep you on track.

If you don’t already have the Sakila-DB database for MySQL installed, the link to the download and instructions for installing it can be found at the blog post titled “Learn MySQL With Sakila sample Mysql Database”

Using SSMA for MySQL

SQL Server Migration Assistant (SSMA) 2008 for MySQL lets you quickly convert MySQL database schemas to SQL Server 2008, SQL Server 2008 R2 or SQL Azure schemas, upload the resulting schemas the target instance and migrate the data using a single tool.

Licensing SSMA

SSMA is a free tool, but does require you to associate a Microsoft Live ID for identification purposes. You must download a registration key. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program. Use the following instructions to download a license key and associate the key with SSMA.

To license SSMA

Click Start, point to All Programs, point to Microsoft SQL Server Migration Assistant 2008 for MySQL, and then select Microsoft SQL Server Migration Assistant 2008 for MySQL.
In the License Management dialog box, click the license registration page link.
On the Sign In Web page, enter your Windows Live ID user name and password, and click Sign In.
A Windows Live ID is a Hotmail e-mail address, MSN e-mail address, or Microsoft Passport account. If you do not have one of these accounts, you will have to create a new account. To create a new account, click the Sign up now button.
On the SQL Server Migration Assistant for MySQL License Registration Web page, fill in at least the required fields, which are marked with a red asterisk, and then click Finish.
In the File Download dialog box, click Save.
In the Save As dialog box, locate the folder that is shown in the License Management dialog box, and then click Save.
The default location is C:\Documents and Settings\user name\Application Data\Microsoft SQL Server Migration Assistant\m2ss.
In the License Management dialog box, click Refresh License.

SSMA for MySQL User Interface

After SSMA is installed and licensed, you can use SSMA to migrate MySQL databases to SQL Server 2008 or SQL Azure. It helps to become familiar with the SSMA user interface before you start. The following diagram shows the user interface for SSMA, including the metadata explorers, metadata, toolbars, output pane, and error list pane:

Basic Steps for Migration of MySQL to SQL Server

To start a migration, you’ll need to perform the following high level steps:

Create a new project.
Connect to a MySQL database.
After a successful connection, MySQL schemas will appear in MySQL Metadata Explorer. Right-click objects in MySQL Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server 2008 R2 Express. You can also perform these tasks by using the toolbars and menus.

You’ll then connect to your instance of SQL Server 2008 R2 Express. After a successful connection, a hierarchy of your existing databases will appear in SQL Server Metadata Explorer. After you convert MySQL schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

After you synchronize converted schemas with SQL Server 2008 R2 Express, you can return to MySQL Metadata Explorer and migrate data from MySQL schemas into target database.

Let’s walk through the specifics.

Create a MySQL Migration Project

To get started, you’ll create your new project using the File | New Project command.

You’ll enter in your project name and then confirm that you are migrating to SQL Server. The Migrate To dropdown also allows you to choose SQL Azure, but that’s for another post. Once you make your selection, you are locked into the target backend.

Connect to a MySQL Database

To Connect to your MySQL instance, you’ll issue the File | Connect to MySQL command or click on the tool bar button that launches the following dialog:

If you forgot to to install the MySQL ODBC driver mentioned at the beginning of this blog, simply go to the download site, install the driver, and then issue the Connect to MySQL command.

Create Report of Potential Conversion Issues

Once you are connected, you’ll see the MySQL instance in the MySQL Metadata Explorer. You’ll want to expand the Databases node along with the Sakila database node and then check the box next to Sakila. This selects the database you want to migrate. Next, right click on the Sakila database and select the Create Report command or press the Create Report command on the toolbar as shown below.

Here is an example of the Assessment Report for the Sakila database.

The Assessment Report window contains three panes:

The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy, and select objects and categories of objects to view conversion statistics and code.
The content of the right pane depends on the item that is selected in the left pane.
If a group of objects is selected, such as schema, the right pane contains a Conversion statistics pane and Objects by Categories pane. The Conversion Statistics pane shows the conversion statistics for the selected objects. The Objects by Categories pane shows the conversion statistics for the object or categories of objects.
If a function, procedure, table or view is selected, the right pane contains statistics, source code, and target code.
- The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.
- The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.
- The Target area shows the converted code. Red text shows problematic code and error messages.
The bottom pane shows conversion messages, grouped by message number. You can click Errors, Warnings, or Info to view categories of messages, and then expand a group of messages. Click an individual message to select the object in the left pane and display the details in the right pane.

In future blog posts, we’ll work through the specific problems that are in this report. For now, we’ll ignore the problematic objects for the schema and data migration steps. For now, close the report and then uncheck Functions, Procedures and Views nodes to take them out of the conversion. Then uncheck the tables with errors as shown below.

Go ahead and click on the Create Reports command to verify that there are no errors.

Connect to SQL Server

It’s time to connect SSMA to your SQL Server 2008 R2 Express instance. For the Server name, you’ll need the server name and instance for the target server. Since we are using the WPI installation of SQL Server 2008 R2 Express, you’ll enter in the server name as .\SQLEXPRESS.

You can select an existing database to migrate to using the Database control. You can also type in the name of a new database. In this case, use Sakila as shown below.

Once you click connect, SSMA prompts you if you want to create the database. Choose Yes to create the new database. When connecting to SQL Server Express instances, you’ll receive the following warning indicating that you won’t be able to use the server-side data migration engine. This engine is used for larger migration projects.

You can Continue from this dialog to start the actual migration process.

Convert Schema

Now that you’ve connected to the target SQL Server instance, SSMA enables the Convert Schema command. Click the Convert Schema command. Once the conversion is finished, you should see the SQL Server Metadata Explorer populated with the tables listed in bold as shown below.

Synchronize with Database

To write the tables to the target, select the dbo node in the SQL Server Metadata Explorer and then issue the Tools | Synchronize with Database command. SSMA displays the Synchronize with Database dialog as shown below. In this example, the Tables node was manually expanded to show that no tables are actually on the database at this time.

When you click OK, SSMA issues the CREATE TABLE statements to create the objects on the SQL Server target. There are some errors in this example because many of the tables selected have foreign key relationships to some of the tables that we excluded earlier. These errors can be ignored for now.

Migrate Data

The last step is to migrate the data into the tables. To complete the migration, select the Tables node within the MySQL Metadata Explorer for the Sakila database. Then issue the Tools | Migrate Data command or press the command on the toolbar. The Data Migration process requires you to connect to the MySQL database and to the SQL Server database again. SSMA then proceeds with the data migration process and displays the Data Migration Reports as shown below.

Using SQL Server Management Studio

The migrated tables are now ready on the target SQL Server instance. To see the results, launch SQL Server Management Studio (SSMS) and connect using the server name as .\SQLEXPRESS. Expand out the Databases node to see the Sakila database. Expand the out the Sakila database tables and then right click on the actor table and issue the Select Top 1000 Rows command to view the data as shown below.

SQL Server Management Studio that is part of the WPI is a free rich Windows client tool from Microsoft that offers a rich development and management experience like SQLyog and MONyog.

BIO

Bill Ramos is the SQL Server Work Stream Manager for Advaiya. During his 15 years at Microsoft as a program manager, he has been on teams that have shipped the following products: Project Houston, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, SQL Server 2000, SQL Server 7.0, SQL Server 6.5, Ashton-Tate/Microsoft SQL Server for OS2 (at Ashton-Tate), Excel 2003, Access 2003, Access XP, Access 2000. You can find his personal blog at http://blogs.msdn.com/billramo and on Twitter at http://twitter.com/billramo.

Comments (29)

Cancel reply

Curtis says:
March 7, 2011 at 7:46 am
Thank you for this. I've been banging my head against SSMA on and off for a few days. I followed your walk-through and data migrated perfectly.
Reply
wie setzt man andere Verzeichnisse bei der migration says:
April 25, 2012 at 8:19 am
leider wird bei der migration alles in das standart verzeichnis des sql servers geschrieben, wie ändere ich die Ziele pro datenbank?
Reply
jugalpanchal@live.com says:
July 12, 2012 at 2:35 am
Thank you for your valuable article, I want to ask you about a issue is that when I give “Migrate Data” Command. it gives a problem. As you have put a screen where we can see Data Migration Report. I have got same report but without "To and other Column data". it shows only "From" column data.
Reply
Fernando says:
March 14, 2013 at 8:51 pm
una duda se puende migrar los procedimiento de mysql a sql server 2008 ? es que necesito migrar la base de datos pero tengo mas de 200 procedimientos y quiero cambiarme a sql server
Reply
Atit says:
April 24, 2013 at 1:56 am
Hello,
I am unable to create convert schema after connecting to SQL Server.The convert schema option is deactivated..Should the name of MYSQL database and SQL Server database be the same?? Pls help
Reply
PDS says:
July 3, 2013 at 7:16 am
Hi,
I am trying convert SQl statements from MySql to MS Sql using SSMA but getting error that SSMA ERROR: Unparsed Sql.
I am adding sql in Statements and then generating report and getting thsi error.
Thanks
Reply
GMM says:
July 23, 2013 at 3:21 am
PDS
I got the unparsed sql error when I tried to migrate some views. The account I was using on MySQL did not have access to the SQL statements in the view in the schema, so all that showed for those views in SSMA on the SQL tab was a blank screen. I started a new project using a different account which had access to the SQL, then it worked.
I guess you have to check that the account has access to the SQL statements, and also that the SQL can be parsed. I did this in MySQL Query browser, open the schema, check you can run the SQL.
Reply
ravi says:
July 23, 2013 at 5:16 am
Hi,
Newbie Question! Once the data is migrated does it mean that the data will not be available in Mysql Database ?
Reply
Kingmhar says:
August 14, 2013 at 8:04 pm
I will try this =D im off to mysql due to limitation =D
Reply
Soner CAKIR says:
August 28, 2013 at 2:17 am
Hi,
Thanks for this article it's really helpfull, but when i try to create a report software returns this error "An unexpected error occurred. Please send the log file to product support. For more information, see "Getting SSMA Assistance" in the product documentation." Windows 7 32 Bit and ODBC Version is 5.1.8, any idea about how i can solve it ?
Reply
Doug says:
September 6, 2013 at 2:36 pm
Is it possible to get a license for SSMA for a machine without an internet connection?
Reply
Martin says:
September 8, 2013 at 6:29 am
Try ESF Database Migration Toolkit
http://www.easyfrom.net/
Reply
dnarizhnyh@hotmail.com says:
September 9, 2013 at 11:20 am
Alternatively you can automate your migration/ sync job using
convertdb.com/…/mssql
Reply
sean_worthington@hotmail.com says:
September 16, 2013 at 2:14 pm
When I try to connect to a MySQL database, I put 'root' or some other account name for the username. But when I try to connect, SSMA adds an '@192.168.1. to the end of my username and then access is denied. How do you stop it from adding the dang @my-local computer info? I can't seem to stop if from doing this. Does this thing allow you to connect to remote computers?
Reply
Tim McKay says:
October 20, 2013 at 11:45 am
The link in the SSMA app to the page to get the license key is not found… 404 error.
Reply
Christopher Deutsch says:
October 28, 2013 at 9:42 am
The registration page is gone. I hope who ever came up with the stupid idea to get a license this way got fired.
Reply
Ajay says:
October 28, 2013 at 9:48 am
Hey Chris,
I understand your FRUSTRATION. Please reach out to Microsoft Support via the link :
support.microsoft.com/…/default.aspx
I bet you will get excellent response from this team !
Regards,
Ajay
Reply
Paul says:
November 3, 2013 at 2:33 am
Do, any of you guys have a license have a license file that can be used since the page is down and Microsoft is a nightmare to get support from?
Cheers
Reply
Mohamed Mamdoun says:
November 7, 2013 at 8:41 am
Hello ,
Thanks for the post but
There is a big problem in the license in the SSMA for MYSQL which Microsoft didn't fix until now .
The link to the license refer to a page that doesn't exist .
social.msdn.microsoft.com/…/licence-page-for-microsoft-sql-server-migration-assistant-mysql-missing
Reply
Jill McClenahan says:
November 8, 2013 at 10:36 am
Thanks for bringing this to our attention. We have fixed the fwlink to point to a download for the license file. The direct download link is http://www.microsoft.com/…/details.aspx.
Reply
Jagadeesh says:
January 8, 2014 at 5:18 am
Thanks much….. it really helped me to complete the migration from mysql to sql server
Reply
mmniet says:
July 15, 2014 at 2:17 am
Came u with a problem which was hard to search for on the internet (and didn't find a solution for). We have a lot of comment in the tables for fields to describe the columns, but SSMA isn't able to migrate them to sql-server. How to fix this problem? Manually is a hell of a job. Please send me a gmail, my account is my used name for this post. Thank you very much!
Reply
RJ says:
August 26, 2014 at 3:32 pm
When I covert schema the foreign key relationships are maintained, but after I migrate data the foreign keys gets lost.. Any ideas on why this might be happening?
Thanks
Reply
Naveed says:
September 23, 2014 at 11:32 am
i have installed Mysql Administrator v1.2.2 beta. Now when I start to make the new Schema it give me the message that you cannot create the database and shows an error 404 ; Please help me How can I solve it.
Reply
muyideen says:
January 25, 2015 at 10:48 pm
please i have downloaded the migration assistant, but it will not recognise the odbc for 5.1,5.2 0r 5.3 that i downloaded in various form 0f 32 or 64 bits, please what can i do, i am using windows 7 64 bit thanks
Reply
mir safi says:
March 11, 2015 at 11:50 pm
Hi I am currently migrating data from mysql to SQL and i am using SSMA.
I have successfully connected MySQL and SQL and when i am trying to convert the Schema i get an error stating " An unexpected error occurred. Please send the log file to product support. For more information, see "Getting SSMA Assistance" in the product documentation. Object reference not set to an instance of an object."
Please help me unable to sort this and I am stuck in this from past few days.
Reply
Victor says:
August 8, 2015 at 10:08 pm
I had good experience with http://www.spectralcore.com/fullconvert
Reply
iain says:
October 20, 2015 at 11:36 am
I followed all the steps and the data failed to migrate at the very last step – telling me in the report that it, well, that it failed to migrate.
Reply
Klein says:
October 23, 2015 at 8:53 pm
Newbie Question! Once the data is migrated does it mean that the data will not be available in Mysql Database ? http://www.dauthu-dvbt2.com
Reply

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

«이전 1 다음»

보미네

카테고리

달력

공지사항

태그목록

최근에 올라온 글

'2016/04'에 해당되는 글 8건

mysql odbc

dynamic pivot

edition upgrade

adam sp_whoisactive plus tempdb.....usages

nul 백업

jTDS vs MS JDBC

Not every extended event is suited for all situations

Demo (tested on SQL Server 2012, 2016)

ssma mysql

MySQL to SQL Server Migration: How to Use SSMA

Downloading SQL Server 2008 R2 Express and SSMA

Downloading the MySQL ODBC Driver

Other Helpful Downloads for SSMA and this Blog

Using SSMA for MySQL

Licensing SSMA

SSMA for MySQL User Interface

Basic Steps for Migration of MySQL to SQL Server

Create a MySQL Migration Project

Connect to a MySQL Database

Create Report of Potential Conversion Issues

Connect to SQL Server

Convert Schema

Synchronize with Database

Migrate Data

Using SQL Server Management Studio

BIO

Cancel reply

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

링크

티스토리툴바