1

I need to execute this command:

SQLCMD -d dbname -i sc.sql

to run some queries. Since I have both version 2019 and 2016, the query is executed with 2019, but actually I need that on 2016.

I have the same name of database on both version moreover, when I add an instance name:

SQLCMD -S .\MSSQLSERVER -d dbname -i sc.sql

I get an error :

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lady
  • 356
  • 1
  • 2
  • 14
  • try to load the sql into ssms and see if you can identify there the problem, 2008 is very old – nbk Jul 08 '23 at 14:07
  • the problen not from 2008. I forced to run queries by the command sqlcmd because I have 1 million insert. ssms rejected excution because of "No suffecient memory" – lady Jul 08 '23 at 14:12
  • install a vm with sql server 2008 import the file make a backup .bak file, that can be used without problem in later versions – nbk Jul 08 '23 at 14:14
  • from newer to older – lady Jul 08 '23 at 14:36
  • I updated the version to 2016 – lady Jul 08 '23 at 14:40

2 Answers2

3

SQLCMD -S .\MSSQLSERVER -d dbname -i sc.sql

One of these instances is the "default" instance, which has special rules for naming. The default instance is never referenced by instance name*. So for the default instance use

SQLCMD -S . -d dbname -i sc.sql

And for the other instance use

SQLCMD -S .\<OtherInstanceName> -d dbname -i sc.sql

*Before SQL 2000 only one instance was allowed. And to shoe-horn multiple instances in the single instance became the "default" instance, but existing apps refer to this instance just by the server name. Additional "named" instances require the instance name or explicit port number.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • I'm not sure these 2 instances *can* be on the same host. If I recall correctly 2008 and 2019 do not share an OS they both support. – Thom A Jul 08 '23 at 14:17
  • 1
    Oh it _works_ it's just not supported. And SQL 2008 is completely out of support. Even if there isn't a single Windows version that you could install both on, Windows could have been upgraded. – David Browne - Microsoft Jul 08 '23 at 14:21
  • Seem that is [the case](https://learn.microsoft.com/en-us/troubleshoot/sql/general/use-sql-server-in-windows). 2019 needs Windows Server 2016+ while 2008 is only supported on Windows Server 2012R2 (and obviously some prior). – Thom A Jul 08 '23 at 14:21
  • But that doesn't mean that SQL Server 2008 won't install and run on later versions of Windows. Only that it's not a supported scenario. And in any case, Windows Server 2012 with SQL Server 2008 can be upgraded to Windows Server 2016. – David Browne - Microsoft Jul 08 '23 at 14:24
  • So it could be a case of the OP is using a *completely* unsupported version of SQL Server (for 4 years) on an OS that doesn't even support it; sounds like a hook line and sinker to stop using that instance and it's an effective ticking time bomb. – Thom A Jul 08 '23 at 14:25
  • 1
    Yes, hopefully OP is in the process of upgrading from 2008 to 2019. – David Browne - Microsoft Jul 08 '23 at 14:27
  • In general how to migrate data from version to another, same question from 2019 to 2016 for e.g. – lady Jul 08 '23 at 14:30
  • without direct run script on ssms – lady Jul 08 '23 at 14:31
  • Backup the databases from SQL Server 2008 and restore them on SQL Server 2019. Super simple. The databases will be upgraded as part of the restore. See https://learn.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2019?view=sql-server-ver15#migrate-to-sql-server-2019 – David Browne - Microsoft Jul 08 '23 at 14:34
  • I think there are some companies have legacy systems ..... I need from newer to older – lady Jul 08 '23 at 14:37
  • 1
    You would have to script everything out, @lady . The *real* solution is to get off a version that has had no support for *4 years*; using such a version is a significant security vulnerability. – Thom A Jul 08 '23 at 14:40
  • If the database is not too large, you could use SQL Server 2019 Express Edition, which is free. https://www.microsoft.com/en-us/Download/details.aspx?id=101064 – David Browne - Microsoft Jul 08 '23 at 14:42
0

Regardless of the version used (because it is illogical to stop legacy systems due to not finding a solution, the upgrade takes place when possible) I found a simple workaround solution and I am sure there is a better one, I am executing the script on SSMS in batches which is an Insert query. It includes ~1+ million insert.

lady
  • 356
  • 1
  • 2
  • 14