1

I have a folder that I generated about 100 .sql files in to make table changes to various schemas in a database.

I am trying to use a the method in this post to run my 100 sql files, but I keep getting the errors. It works for the first few files, and then I start getting

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I know its configured for remote connections, another application on another VM uses it, and I checked the servers properties as well. The first 5-6 SQL files DO run, but then it stalls out and I start getting this issue. Any thoughts on what is causing it and how to mitigate it? Am I just trying to do too much at once?

UPDATE: The code in my batch file

for %%G in (*.sql) do sqlcmd /S ServerName /d MyDatabase -U MyUser -P "MyPassword" -i"%%G"
jarlh
  • 42,561
  • 8
  • 45
  • 63
Brian Karabinchak
  • 207
  • 1
  • 9
  • 1
    Please provide relevant code you've tried here. What I imagine is happening is you are creating a new connection on each attempt instead of reusing a persistent connection, which is overwhelming the database and resulting in connection timeouts. – h0r53 Sep 23 '21 at 18:01
  • My fault I thought I did. Just attached the batch file code that I am running. – Brian Karabinchak Sep 23 '21 at 18:02
  • If you add some sleeps in your loop does the error still occur? I'd start by ruling out whether or not the issue is timing related. I imagine it IS timing related since you stated the first few commands work then timeouts occur. If the issue is indeed timing related then you either need to add some time between your requests or use an alternative to sqlcmd so a persistent connection can be established. – h0r53 Sep 23 '21 at 18:13
  • Did you try the -l option to set the login timeout to a much bigger value? I think the default is 8 seconds. Try setting it to 120. – Martin Brown Sep 23 '21 at 19:18
  • That -l setting really helped me out. Thanks! – Brian Karabinchak Sep 23 '21 at 19:38

0 Answers0