1

I've been trying to import data from an Excel file to SQL Server DB. Strangely, I have been successful 2 times since yesterday and unsuccessful hundreds of other times. I thought playing with the connection string in SQL Server Import and Export Wizard can solve my problem but it doesn't work now. It seems like this error can be related to the language and I changed the language of Microsoft SQL Server Management Studio to match my windows language in this path: Tools--> options --> Environment --> International Setting --> "Same as Microsoft Windows" and then restart the MSS Management Studio and didn't work. Also connecting to MSS Management Studio with windows authentication and SQL Server Authentication are all tried. When I connect with windows authentication, the result of select @@language is Deutsch and when I connect with SQL Server authentication the result is us-english. I tried are different combinations and it doesn't work after all. The language settings of my system is super complicated. It seems like they have changed it to English for me but in its core you can still see German as the language in some situations.

Also I have tried set language English; in a SQL Server query and it didn't help because it is not directly related to import/export wizard. There's a field for language in the wizard itself (when making connection) and I played with that, too; didn't help either.

Any idea how I can pass this error? Also I don't have a clue how it worked 2 times.

The connection string that I use in the wizard and I think worked once is like this:

Data Source=MyServerName;Initial Catalog=MyDestinationDB;Integrated Security=True;
User ID=MySQLUser;Password=*********;MultipleActiveResultSets=True;ApplicationIntent=ReadWrite

And the error that I get is as follows (I have get many other errors but this one is the most serious one)

Error

Iraj
  • 319
  • 3
  • 17
  • Please provide the query you are running. – Andrew O'Brien Jul 13 '21 at 17:38
  • 1
    It is likely that you have different default languages for your SQL login vs your Windows login. You can ensure that they are the same by checking them in the `sys.server_principals`. Something like `SELECT default_language_name FROM sys.server_principals WHERE name = 'YourLoginName';`. Also you should provide some sample data from your Excel sheet along with the definition to the table that you are trying to import it in to. – trenton-ftw Jul 13 '21 at 23:29
  • 1
    @AndrewO'Brien Thank you for your message. Actually I don't write a query. In the wizard, after choosing source and destination and connection properties, there's a page for ```Specify Table Copy or Query``` and in this page there are two options: ```Copy data from one or more table or views``` and ```Write query to specify the data to transfer```. I choose the first one because I don't know what exact query should I write to copy data from Excel to SQL Server. But I know some query is automatically written in the background that I can't see. – Iraj Jul 14 '21 at 08:08
  • @trenton-ftw Thank you for your comment. I tried ```SELECT default_language_name FROM sys.server_principals``` and it returned me a list of mainly NULL values, only the first and last entry were **us-english** when connection with SQL Server authentication. But when connected with Windows authentication one of those two was **us_english** and the other one was **Deutsch**. How can I now use this info to solve the issue? Also I didn't get how to "provide sample data from Excel" and how it can help. – Iraj Jul 14 '21 at 08:21
  • @Iraj you can also just manually check the default language for each user by viewing the properties in the Object Explorer of SSMS. RIght click on the login, select 'Properties' and on the bottom the 'General' tab there will be the default language for that login. – trenton-ftw Jul 15 '21 at 21:47
  • @trenton-ftw I couldn't find on which login should I click to see that. In the very beginning that you're going to login to the server? I tried all logins in SSMS and different properties, but didn't see language setting. Anyway, the important question is that how this can solve that conflict in regard to "culture"? – Iraj Jul 16 '21 at 08:04
  • @Iraj within Sql Server, your culture is essentially your language and collation. So my initial thought was a mismatch on the language between your session for the import and the server\database could lead to this error. After a bit more research it looks like its actually probably related to your OS locale. See [this](https://dba.stackexchange.com/questions/172903/microsoft-sql-server-local-report-culture-is-not-supported-3072-is-invalide-cul) question as an example. What is your OS locale? Based on this error, it is likely that your SQL Server does not support that the culture for that local – trenton-ftw Jul 16 '21 at 20:06
  • Dear @trenton-ftw, sorry for delayed response. I think you're right about the conflict of my OS with SQL Server serssion, but I don't know how this conflict can be solved. On the surface, my windows display language is English (United States) according to windows and in SQL Server is as I wrote previously. I would suggest you write your opinion in a post, it might attract more comments and help others as well. – Iraj Jul 21 '21 at 12:18

1 Answers1

0

Had the same issue with SQL Server Import/Export wizard.

In my case, the solution was to set English (United States) in Time & Language -> Region Settings -> Regional format.

mrsodja
  • 1
  • 1