MySQL Forums - Microsoft SQL Server

Migration from Microsoft SQL Server to MySQL (no replies)

Edwin Desouza — Fri, 27 Sep 2024 17:26:56 +0000

https://medium.com/@henriqueleandro/unlocking-database-freedom-migration-from-microsoft-sql-server-to-mysql-using-generative-ai-288cc7d5c865

https://medium.com/@henriqueleandro/migrating-sql-server-t-sql-to-mysql-using-javascript-stored-programs-35a8cb962e35

what conftest.py should look like? (1 reply)

ravi kumar — Wed, 17 Jul 2024 16:00:32 +0000

How should I configure conftest.py to test my async endpoints in fastapi? With access to the test postgres database. Does anyone have an example? I'm looking at different people's implementations and I'm confused.

pytest, fastapi, postgres, sqlalchemy

I looked on github and youtube

PostgreSQL wildcard search query (no replies)

ravi kumar — Thu, 13 Jun 2024 10:31:15 +0000

I am trying to optimize 2 slow SQL queries where the search should go across multiple columns.

First query is used to get total number of records to display on the UI.
Second query is used to get actual records.
Where statement is identical for both of them.

Migrating from MySQL to MS SQL (1 reply)

Michael Feuti — Tue, 01 Oct 2024 16:09:01 +0000

I have been tasked with migrating a legacy building monitoring system from MySQL to SQL server. I have tried unsuccessfully many times to use the the Microsoft SQL Server Migration Assistant for MYSQL. The migration always fails. I have increased every timeout option I can find and it has helped but it still fails. The reason is most likely that the is database is basically only two tables. One has about 50 million rows of data but the second has over 2 trillion rows of data. The connection does not stay open for some reason in the application and at most I get maybe 300million rows migrated.

Is there a better process to do this? I'm do not have a MySQL background but have a fail amount of SLQ knowledge. Any suggestions are most appreciated. Thank you.

error database (no replies)

Sergio Cruz — Sun, 03 Mar 2024 02:28:42 +0000

Hello Friends, can someone help me, when I enable tcp/ip, it does not open the sql database and gives an error

workbench migration error (1 reply)

ken zvokel — Fri, 08 Dec 2023 08:54:03 +0000

HELP! Been working thru this issue for 3 or 4 days and have been going in circles.

We are looking at converting our MS Sql database to MySql. I am trying to use the Migration Tool.

when I choose Database -> Migration Wizard start Migration, I get a Dependency Warning "Could not import the pyodbc python module"....

I have confirmed that pyOdbc is installed (5.0.1) using pip list

when i go past the waring to the screen to select the dsn for my MS SQL db I get an error "an error occured while retrieving values for option dsn.... DbMssqlRE.getDataSourceNames

Any help would be appreciated

thanks

kenZ

DbMssqlRE.getDataSourcesNames (no replies)

mohsin qadir — Sun, 24 Sep 2023 16:22:35 +0000

an error occurred while retrieving values for options 'dsn' from 'MssqlOdbcDSN'. error calling Python module function DbMssqlRE.getDataSourcesNames. When i migrate the database from MSSqlserver to MYSql Workbench i created a ODBC it was created successfully but in workbench it giving me the following Error Please any body have solution ?

Converting MSSQL to MySQL (no replies)

Mohammed Samiuddin — Fri, 16 Jun 2023 17:11:40 +0000

Dear community members,

I hope this message finds you all well. I am reaching out to seek your valuable insights and guidance regarding MySQL migration for optimizing website performance. As we strive to enhance the efficiency and speed of our website, we have decided to migrate our database from its current platform (MS SQL Server) to MySQL.

In this regard, we have a few questions and concerns that we would greatly appreciate your expertise on:

What are the key benefits of migrating from our current database platform to MySQL? Are there any specific features or optimizations we should consider?

Could you please provide some best practices and recommendations for executing a successful MySQL migration? Are there any potential pitfalls or challenges we should be aware of?

How can we ensure a smooth and seamless transition of our data from the existing platform to MySQL? Are there any tools or methodologies that you would recommend?

Are there any specific considerations or precautions we should take to minimize potential downtime during the migration process? How can we ensure minimal disruption to our website and its functionality?

After the migration, what are the recommended steps for optimizing MySQL performance? Are there any specific configurations or settings that we should focus on?

We greatly appreciate any insights, suggestions, or experiences you can share regarding MySQL migration. Your expertise will not only help us improve our website performance but also contribute to the larger community knowledge base.

Additionally, we have decided to use MySQL Workbench and
Data Loader for migration. We believe in fostering collaboration and sharing knowledge, and we are excited to be a part of this community.

Thank you in advance for your valuable input and support. We look forward to your responses and engaging in meaningful discussions.

Warm regards,

M Sami
Database Analyst

MSSQL Migration MYSQL (1 reply)

David Carpenter — Fri, 12 Aug 2022 15:05:19 +0000

Hello,

I've been given the task of migrating a large (2TB) MSSQL database to MYSQL, I am total newbie to MYSQL so I was looking for some general tips and advice on how to quickly as possible migrate the the data.

I am using the work bench and the database migration wizzard tool, what settings on the MYSQL side should I be looking to tweek in terms of meory settigns and how do I quickly disable all the indexes on the tables, I assume as per MSSQL this will speed the inserts up.

Thanks

Dave

Migrate MSSQL Database (10+GB database size) to MySQL community (6 replies)

Shamshad Ali — Wed, 10 Aug 2022 00:18:46 +0000

I'm trying to migrate MSSQL database to MySQL, using Workbench 8.0.29, but it seems it won't migrate all tables' all rows. Few tables have limitation with row size 65,535 and few having billions of records which it won't migrate at all. Is there any way to easily manage this migration?
Also auto_increment is not updated during migration. What is the best way to migrate with auto_increment too?

I have gone through multiple google suggestions but they are not satisfactorily providing solution.
Please share if one has practically done migration successfully, and had any proper documentation/tool available. We have several large databases in queue. Also debate if migration is a better step or what?

Regards.

kaprekar number check function not working on T-SQL (3 replies)

Святослав Тарадай — Mon, 09 May 2022 16:31:02 +0000

Hi! i made an T-SQL Function which checks if a number is a kaprekar number or not. here is the code

```
CREATE FUNCTION [dbo].[kaprekar](@n int)
RETURNS bit
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @result bit;
DECLARE @sq_n INT;
DECLARE @count_digits INT;
DECLARE @r_digits INT;
DECLARE @eq_parts INT;
DECLARE @sum INT;

SET @sq_n = @n * @n;
SET @count_digits=0;
WHILE @sq_n <> 0
BEGIN
SET @count_digits= @count_digits + @count_digits;
SET @sq_n=@sq_n/10;
END;
SET @sq_n = @n*@n;

WHILE @r_digits<@count_digits
BEGIN
SET @eq_parts=POWER(10,@r_digits);
SET @sum = @sq_n/@eq_parts + @sq_n% @eq_parts;
END;

IF @n = 1
BEGIN
SET @result=1;
END;
ELSE IF @eq_parts = @n
BEGIN
SET @result=0;

END;
ELSE IF @sum=@n
BEGIN
SET @result=1;

END;
ELSE
BEGIN
SET @result=0;
END;

RETURN @result;
END;

```

but it doesn't work. it only works with the number 1, not every other one. the inspection i made showed that the problem is the main part of code, not with the IF ELSE block. Can you help me?

crash when test sql server connection (1 reply)

jim liu — Thu, 07 Apr 2022 02:43:43 +0000

os : mac monterey 12.3.1（21E258）
mysql workbench : 8.0.28

No matter what you set, it crashes immediately when testing sql server connection

Binary_checksum() of MSSQL in MySQL (1 reply)

Trung Tran — Wed, 23 Mar 2022 16:22:30 +0000

Is there any function that similar to Binary_checksum() of MSSQL in MySQL?

TIA

Local-name() in XML from MSSQL to MySQL (2 replies)

Trung Tran — Fri, 18 Mar 2022 14:55:00 +0000

Hi experts,

I'm working on migrating some codes from MSSQL to MySQL, but I don't know the equivalent of local-name()in MySQL.

The original MSSQL code is as followed:

true

The output is:
AttrKey ||AttrValue ||ValueType
Trigger ||Action ||Map

1. How we can apply similar to local-name() in MySQL to return the ValueType = "Map"? When I tried something like "//*[local-name()]" in MySQL, it returned error.

2.And what is the "." in "(.)" meaning?

Really appreciate your support.

TIA

How to import data from Microsoft SQL Server to MySQL Database Service (no replies)

Edwin Desouza — Wed, 25 Aug 2021 17:41:17 +0000

https://blogs.oracle.com/mysql/post/how-to-import-data-from-microsoft-sql-server-to-mysql-heatwave-database-service

https://blogs.oracle.com/mysql/post/mysql-shell-generate-table-csv

Workbench throws error in Data migration with online transfer option (4 replies)

kamesh bogg — Thu, 17 Jun 2021 08:03:07 +0000

Dear Team,

I am facing following issue when database migration happening from SQL server to MySQL with large sets of data .

Error:

19:01:55 [INF][ copytable]: Re-enabling triggers for schema 'test'

19:01:55 [ERR][ copytable]: Exception: Querying Trigger Definitions: Lost connection to MySQL server during query
ERROOR: Copy helper exited with an error: Worker exited with status.

All the tables data copied successfully to Mysql. Not able to finish tool getting hanged and not able to get the report summary for the migration.

Kindly help on how to solve this issue.

I tried with 1.5 ,8 GB of database size ,In both the scenarios I am facing the same issue.

I tried following settings .

increase the threads in online transfer to 10 threads still same issue.

I tried to update the connection-live time still the same issue.

Please help on the same how to proceed.

Thanks.

SQL Server 2000 code RRS feed (1 reply)

ridhiman alloys — Mon, 17 May 2021 15:43:19 +0000

Hi,

My name is Ajay owner of Ridhimanalloys.com my question is

Anyone could help me to correct the script on SQL Server 2000? Thanks in advance

--------------------

USE [Data_ADM]
GO
/****** Object: StoredProcedure [dbo].[CTRL_Spaceused] Script Date: 11/02/2021 10:59:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CTRL_Spaceused]
AS
BEGIN
/*
Title : CTRL_Spaceused

*/
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1
DECLARE @drive varchar(100);
CREATE TABLE #Temp1
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size] FLOAT NULL,
[File Used Space] FLOAT NULL,
[Max_size] Int,
[physical_name] nvarchar(260) NOT NULL
)
DECLARE @Rundate datetime = getdate()
EXEC sp_MSforeachdb ' USE [?];
INSERT INTO #Temp1
SELECT
DB_NAME() [Database] ,
''FileGroupName''= CASE
WHEN fg.name IS NULL THEN MF.type_desc
ELSE fg.name
END ,
MF.size / 128.0 ,
MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ),
MF.Max_size,
MF.physical_name
FROM sys.database_files MF
LEFT JOIN sys.filegroups fg
ON MF.data_space_id = fg.data_space_id
LEFT JOIN sys.data_spaces ds
ON ds.data_space_id = MF.data_space_id
WHERE MF.Type in (1,0)
'
DECLARE @temp2 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size] FLOAT NULL,
[File Used Space] FLOAT NULL,
[Max_size] Int,
[physical_name] nvarchar(260) NOT NULL
)
INSERT INTO @temp2
SELECT
[Database] ,
[FileGroupName] ,
Sum([File Size]) as [File Size] ,
Sum([File Used Space]) AS [File Used Space],
[Max_size],
[physical_name]
FROM #temp1
WHERE [Database] NOT IN ('distribution', 'master', 'model', 'msdb')
GROUP BY
[Database] ,
[FileGroupName],
[Max_size],
[physical_name]
DECLARE @temp4 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size_MB] FLOAT NULL,
[File Used Space_MB] FLOAT NULL,
[Percent_Free_%] nvarchar (6),
[Autogrowth] nvarchar(260) NOT NULL,
[Drive_Letter] Nvarchar(2) NOT NULL
)

INSERT INTO @temp4
SELECT
[Database] ,
[FileGroupName] ,
[File Size] as [File Size_MB] ,
[File Used Space] AS [File Used Space_MB],
100-(CONVERT(decimal(5,2),(CONVERT(DECIMAL(10, 2),[File Used Space])/CONVERT(DECIMAL(10, 2),[File Size])*100))) AS [% Free Space],
"Autogrowth" = CASE
WHEN (Max_size) <0 THEN 'File will grow until the disk is full'
WHEN (Max_size) = 0 THEN 'No growth is allowed'
ELSE cast(Max_size/128 as varchar (255))
END,
LEFT([physical_name] ,1) AS Drive_Letter
FROM @Temp2
DROP TABLE #Temp1
DECLARE @temp3 TABLE
(
[Drive] varchar(100) NOT NULL,
[Total MB] varchar(1000) NULL,
[Total Free MB] varchar(1000) NULL,
[Total Available MB] varchar(1000) NULL,
[Percent Free] varchar(32)
)

INSERT INTO @temp3
SELECT distinct LEFT ((volume_mount_point),1),
total_bytes/1048576 as Size_in_MB,
available_bytes/1048576 as Free_in_MB,
available_bytes/1048576 as Total_Available_MB,
(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1

DECLARE @Temp5 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size_MB] FLOAT NULL,
[File Used Space_MB] FLOAT NULL,
[Percent_Free_%] nvarchar (6),
[Autogrowth] nvarchar(260) NOT NULL,
[Drive_Letter] Nvarchar(2) NOT NULL,
[Total MB] varchar(1000) NULL,
[Total Free MB] varchar(1000) NULL,
[Total Available MB] varchar(1000) NULL,
[Percent Free] varchar(32)
)
INSERT INTO @Temp5
SELECT T4.*,T3.[Total MB] ,T3.[Total Free MB] ,T3.[Total Available MB] ,T3.[Percent Free] FROM @temp3 T3
INNER JOIN @temp4 T4
ON T3.Drive=T4.Drive_Letter
DECLARE @Temp6 TABLE
(
[Msg] varchar(256) NOT NULL,
[Status] varchar(1000) NULL
)
INSERT INTO @Temp6
SELECT [Database] +' '+[FileGroupName],
"STATUS" = CASE
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) >10 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 10) THEN '0'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) > 10 THEN '0'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) < 90 THEN '0'
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <10 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) > 5 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 5) THEN '1'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),LEFT([Percent Free],Charindex('%',[Percent Free])-1)) >5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 THEN '1'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 90 AND ([File Used Space_MB]/[Autogrowth]*100)< 95 THEN '1'
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <5 THEN '2'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) < '5' THEN '2'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 95 THEN '2'
END
FROM @Temp5
SELECT top (1) [Status] as State,[Msg] FROM @temp6
ORDER BY STATUS DESC
END

Result :

Server: Msg 139, Level 15, State 1, Procedure Check_Spaceused, Line 29
Cannot assign a default value to a local variable.
Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 125
Line 125: Incorrect syntax near 'APPLY'.
Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 174
Line 174: Incorrect syntax near '('.

Issues/Doubts while doing Migration from SQL Server to MySQL DataBase using MySQL Workbench (5 replies)

kamesh bogg — Fri, 28 May 2021 08:15:40 +0000

Dear Team,

I am trying to explore on Database Migration from SQL server to MySQL . I am facing the following issues can you please clarify .

MySQL workbench version : 8.0.23CE

SQL server version: 2017

MySQL version : 8.0

Getting error message when I Select Multiple schemas from different catalogs. Is this feature not available ? How to handle Multiple schemas migration using workbench.?

Can we Pause and Resume the Database Migration .Is there any option from MySQL Workbench?

Is there any command line interface for MySQL workbench to work on with Database migration .If yes can you provide any helpful Link for the same.

How to handle Incremental Database Migration using workbench?

MSSQL to MySQL source selection failure (1 reply)

Cynthia Sidlauskas — Fri, 15 Jan 2021 13:15:09 +0000

Hello,

I've been trying to work through this for about 2 days now, and I've tried everything.
I'm trying to get my MSSQL database to migrate to MySQL. I have created a ODBC Data Source using the windows ODBC Administrator. And the connection is successful. However, when i go into the Migration wizard, I select Start Migration and I change the Database System under Source Selection to Microsoft SQL Server. Instantly after changing it to Microsoft SQL Server, an error message pops up saying:

"An error occurred while retrieving values for option DSN from MssqlOdbcDSN - error calling Python module function DbMssqlIRE.getDataSourceNames"

This error pops up instantly upon making the database system Microsoft SQL Server, without even setting up the Connection Method. I don't understand whats going on here. It seems i'm the only one who has had this issue since a few hundred google searches revealed nothing. I tried recreating my database. I tried uninstalling and reinstalling mySql. It's up to date.

Please help!

cross apply (2 replies)

JK Heydt — Fri, 02 Oct 2020 10:07:21 +0000

I was a big fan of CROSS APPLY in mssql. I can't find a similar function in MySQL. Any suggestions on how to do the same thing in MySQL?

How can I connect my local mysql server 2000 database to my website? (1 reply)

Dustin Jourdan — Tue, 15 Sep 2020 15:00:24 +0000

I currently host a mysql server 2000 database locally. I have a desktop application operating with this database. I would like to show some of my data from this database over the internet, using a website. Do you have any idea how I could make this work?

Connecting to MS SQL server from MySql database (1 reply)

Jaideep Vinakota — Fri, 07 Feb 2020 19:18:18 +0000

Hi,
One of my applications use MySql database. Now I need to retrieve data from a vendor app which uses MS SQL Server. I need to change some of the stored procedures to connect to the MS SQL Server and retrieve data from SQL Server into my MySql database.

I could not find any documentation on even if this is possible and supported by Mysql.

My questions:
1) Does mysql support connecting to a remote database
2) If yes to #1 how can I connect to SQL Server?

I appreciate any/all help.

Thanks
Jaideep

migration of mysql db to sql db by using bash scripts or power shell (2 replies)

sreekar b — Tue, 29 Oct 2019 11:03:47 +0000

I need to migrate the data from mysql database to sql database with powershell or by using bash scripts.How can be it achieved.

Microsoft SQL Migration Schemas Selection Empty (no replies)

Michael Peters — Tue, 23 Apr 2019 09:59:17 +0000

trying to migrate SQLServer to MySQL. There seems to be a problem with migrating remote databases. The SQLServer is on a different server. ODBC works, I can write programs to select and display data. The Workbench migration wizard accepts the ODBC connection as well but the Schema Selection is empty (yes, I omitted the database name).
I have a local copy of the SQLServer (with example data), and when I try to migrate that, the Schema Selection page shows databases to choose from.
Is it possible that the wizard only accepts local databases? then I'm fucked.

-Michael

Equivalent collation for SQL_Latin1_General_CP1_CI_AS (1 reply)

Arumugaperumal M — Sun, 14 Jul 2019 07:36:56 +0000

I'm migrating my MS SQL databases to MySQL. We use SQL_Latin1_General_CP1_CI_AS as database collation. Would like to know equivalent collation in MySQL.

SQL Server Migration Asistant (SSMA) doesn't recognise MySQL Columns (no replies)

mehmet barut — Tue, 29 Jan 2019 07:14:39 +0000

I am trying to migrate my MySQL DB to MsSQL.However, as you can see in the picture SQL Server Management Assistant cant recognise MySQL table columns.

https://ibb.co/HXxKMBx

How to connect my application to MySQL from MS SQL (5 replies)

Christopher Angulo-Bertram — Fri, 25 Jan 2019 01:36:05 +0000

I am the owner of ManagePro software, I just took over the company in April 2018, currently, the application requires an MSSQL instance to work, I want to use MySQL instead, but the app does not connect to the SQL server in the usual way that I have used in the past.

When I start the app, and it can't find the default instance name it is looking for I get a settings box, to fix the issue I just change this .\MANAGEPRO to .\SQLEXPRESS

This, of course, is because the local machine has the server on it, and then I just point it to the instance name.

If the server was remote it would be this servername\instance

So my question is how do I mimic this with MySQL?

migration from sql server (no replies)

med ben — Tue, 04 Dec 2018 08:41:21 +0000

Hello
I'm trying to migrate from SQL server to MySQL with MySql migration tool but it takes over than 30 hours to migrate :/ is there any other solution to do the work faster?!

Convert from sql server to MySql (no replies)

DeAndra Hayes — Tue, 13 Nov 2018 22:11:54 +0000

Hello all, can someone explain to me what the following line of code means in MS Sql and how i can convert it to MySql.

declare @sources nvarchar(4000) = REPLACE(N'insert into #sourceprts select src.* from ({{STATEMENT}}) src', N'{{STATEMENT}}', @SourcePryStat)

Problem with migrating from SQL Server to MySQL (1 reply)

Michał Więckowski — Fri, 21 Sep 2018 12:50:39 +0000

I've got a problem during migration. To migrate data between SQL Server 2017 and MySQL I use MySQL workbench (version 8.0.12). When script for new tables generation is completed I can find that there are many places where int is set to default value of '', for example:

CREATE TABLE IF NOT EXISTS `myname`.`Table1` (
`version` INT NOT NULL DEFAULT '',
...
)
And this, of course, is not working in next steps. Since I've got 'version' column in each table, is there any way that I can change this globally?

I know that I can go to each table and change it manually but it will be a lot of clicking...