BAT/SQL Query

Good evening,

I hope you can assist with a strange problem we're having, I'll try and describe as best I can:

Old server 2008 R2 + SQL 2008 (not express) + DB called "DATABASE"
A BAT ran on this old decommissioned server via scheduled task which basically ran the following set of variables and some other items which I don't think is relevant to the problem (I'll add them if needed) I can also add the SQL script being called, but again I'm not sure this is relevant.

################
:SettingVaribles

Set Z_SQL_Script=C:\Batch\"sqlscriptname.sql"
Set Z_SQL_Server=tcp:"oldsqlserver\sql2008"
Set Z_SQL_Database="DATABASE"

SET Z_DataName=CCH SQL Infamous Tick
SET Z_emailServer="Mailserver"
SET Z_emailRecipient="email@email.com"
######################

Now on the new server (2012 R2 + SQL 2012 + DB called "DATABASE") I thought it would be as easy as just adjusting the variables so it can query the new sql instance, however this is where my troubles are coming in. As you can see in the new script, there is no "\instance" - only the server name. I cannot see a "\instance" on 2012 so suspect it was not setup.

New BAT:

################
:SettingVaribles

Set Z_SQL_Script=C:\Batch\"sqlscriptname.sql"
Set Z_SQL_Server=tcp:"newsqlserver"
Set Z_SQL_Database="DATABASE"

SET Z_DataName=CCH SQL Infamous Tick
SET Z_emailServer="Mailserver"
SET Z_emailRecipient="email@email.com"
######################

When running the new script it reports:

###################
Setting Varibles

The system cannot find the path specified.
The system cannot find the path specified.
Ticking the Infamous Ticks
The system cannot find the path specified.
The system cannot find the path specified.
The system cannot find the path specified.
The system cannot find the path specified.
#####################

My best guess is that the problem is coming in with the new server name or instance hence it failing immediately. I've tried using various combinations of:
servername
servername\mssqlserver
ip address
pipe string (\\.\pipe\sql\query)

SQLCMD - L only lists the server name (again no \instance) and the old server\instance

Hope this makes sense, feel free to let me know if you require any further information.
benthomasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

F PCommented:
MSSQLSERVER or MyInstance is the name of the default instance, meaning you didn't set one during configuration.

https://msdn.microsoft.com/en-us/library/ms143547.aspx
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did you check if you allowed remote connections to the new MSSQL?
Check if  netstat -an | find ":1433"   in a command prompt lists the default port for on the server.
0
benthomasAuthor Commented:
Hi Qlemo, I'm able to telnet to the new server on 1433, results of the netstat is:

 TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
 TCP    10.35.1.247:1433       10.35.1.21:49294       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.21:50646       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.21:50680       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.22:49609       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.39:51723       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.42:50214       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.42:50215       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.53:63824       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.53:63831       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.53:63832       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.53:63835       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.87:49318       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.87:49496       ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.122:52743      ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.131:49550      ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.222:62170      ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.247:61610      ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.247:64739      ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.247:64740      ESTABLISHED
 TCP    10.35.1.247:1433       10.35.1.247:64827      ESTABLISHED
 TCP    10.35.1.247:61610      10.35.1.247:1433       ESTABLISHED
 TCP    10.35.1.247:64597      10.35.1.247:1433       TIME_WAIT
 TCP    10.35.1.247:64739      10.35.1.247:1433       ESTABLISHED
 TCP    10.35.1.247:64740      10.35.1.247:1433       ESTABLISHED
 TCP    10.35.1.247:64827      10.35.1.247:1433       ESTABLISHED
 TCP    [::]:1433              [::]:0                 LISTENING
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The new server is already in use, as there are a lot of connections established. Is this is a different MSSQL instance in use?
0
benthomasAuthor Commented:
The new server is already in use, yes.
I cannot see a new "instance" on the new server, it only shows the server name - so I'm assuming its using the default instance name of MSSQLServer (or My Instance as Frank mentioned) - however I've tried those and still getting the same error.

As I mentioned I tried:

servername
servername\mssqlserver
ip address
pipe string (\\.\pipe\sql\query)
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
So am I correct that the connection to the instance works in general, but just not in that script?
The error messages you get are from non-sql parts.
The vars should be set like this:
Set Z_SQL_Script="C:\Batch\sqlscriptname.sql"
Set Z_SQL_Server="tcp:newsqlserver"

Open in new window

but that should not make a difference here.
As we cannot see code performing actions, I cannot tell what is wrong. Check for file or folder names used.
0
benthomasAuthor Commented:
That's correct, I've tried setting the variables like that but still getting the same error. The folder locations are deffinitely correct.
Would you like me to upload the entire script?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you can do so, yes. We should have at least the part up to "Ticking the Infamous Ticks".
0
benthomasAuthor Commented:
Here is the script, I'll upload the SQL script it calls below this.
:SettingVaribles

Set Z_SQL_Script=C:\Batch\InfamousTick\SetAssignmentsAndInvoicesRFP.sql
Set Z_SQL_Server=tcp:SQLSRV
Set Z_SQL_Database=CENT

SET Z_DataName=SQL Infamous Tick
SET Z_emailServer=EXCH
SET Z_emailRecipient="emailaddressremoved"


:StartingScript
cls
@echo off
set Z_VER=v 0.4
color 2f
title SQL Infamous Tick Script %Z_VER% 
cls
echo.
echo 		*****************************************
echo 		*                                       *
echo 		*       Infamous Tick Script %Z_VER%    *
echo 		*                                       *
echo 		*                                       *
echo 		*****************************************
echo.
echo Setting Varibles
echo.

:SettingLog
Set Z_InfamousTick=C:\batch\InfamousTick\Logs\InfamousTick_%Date%.txt
echo %COMPUTERNAME% - %Z_DataName% Report %VER% > %Z_InfamousTick%
echo. >> %Z_InfamousTick%

:InfamousTickTime
echo Ticking the Infamous Ticks
echo Ticking the Infamous Ticks on the SQL server: %Z_SQL_Server%. >> %Z_InfamousTick%
echo          ...using the updated script: %Z_SQL_Script%.            >> %Z_InfamousTick%
echo          ...in the database: %Z_SQL_Database%.            >> %Z_InfamousTick%
echo. >> %Z_InfamousTick%
echo.
echo Creating Time and Date stamp
echo.
echo. >> %Z_InfamousTick%
echo Time:                         %time% >> %Z_InfamousTick%
echo Date:                         %date% >> %Z_InfamousTick%
call sqlcmd -i %Z_SQL_Script% -S %Z_SQL_Server% -d %Z_SQL_Database% -E   >> %Z_InfamousTick%
echo. >> %Z_InfamousTick%
echo End Time:                %time%
echo.
echo End Time:                %time% >> %Z_InfamousTick%
echo. >> %Z_InfamousTick%

:SendingRemote
echo Sending Robocopy Report 
echo.
C:\batch\mailsend\mailsend -f InfamousTick@%USERDNSDOMAIN% -d %USERDNSDOMAIN% -smtp %Z_emailServer% -p 25 -t %Z_emailRecipient% -sub "%COMPUTERNAME% - %Z_DataName% Report" -a %Z_InfamousTick%,text/rtf,i -v > c:\batch\mailsend\SQLInfamousTickMailsendLog.txt
::C:\batch\mailsend\mailsend -f InfamousTick@%USERDNSDOMAIN% -d %USERDNSDOMAIN% -smtp charter8 -p 25 -t "emailaddressremoved" -sub "%COMPUTERNAME% - %Z_DataName% Report" -a %Z_InfamousTick%,text/rtf,i -v >> c:\batch\mailsend\SQLInfamousTickMailsendLog.txt
::C:\batch\mailsend\mailsend -f InfamousTick@%USERDNSDOMAIN% -d %USERDNSDOMAIN% -smtp charter8 -p 25 -t "emailaddressremoved" -sub "%COMPUTERNAME% - %Z_DataName% Report" -a %Z_InfamousTick%,text/rtf,i -v >> c:\batch\mailsend\SQLInfamousTickMailsendLog.txt

:UpdatingBGInfo
echo Updating BGInfo...
c:\batch\bginfo\bginfo /ic:\batch\bginfo\info.bgi /timer:0

Open in new window

0
benthomasAuthor Commented:
SQL Script (SetAssignmentsAndInvoicesRFP.sql)
SET QUOTED_IDENTIFIER ON
GO

update assignment
set
Assignment.RFP=1
from
assignment
inner join
assignmenttype at on at.AssignmentTypeID=Assignment.AssignmentTypeID
inner join
Client on Client.ClientID=Assignment.clientid
where
At.chargeable=1
and
at.[External]=1
and
at.IncludedTime=1
and
Assignment.AssignmentStatusID in (1,2)
and
(assignment.rfp is null or assignment.rfp=0)
GO


update
	Bill
set
	IsRequestForPayment=1
where
	IsRequestForPayment=0
and
	Bill.DoneID=2
and
	InvoiceDate >='20000101'
and
	Bill.TransactionTypeId=1
GO

Open in new window

0
benthomasAuthor Commented:
Sorry about that, and thank you.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This is the culprit line:
Set Z_InfamousTick=C:\batch\InfamousTick\Logs\InfamousTick_%Date%.txt

Open in new window

Since this is setting the log file name for all results, you'll get a lot of error messages if the path is invalid.
%date% can contain spaces, depending on the local date format settings and the OS. With US date format, it might contain the weekday.
It is much better to assemble something out of the expected date format, like
Set Z_InfamousTick=C:\batch\InfamousTick\Logs\InfamousTick_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%.txt

Open in new window

which should result in *_yyyymmdd.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
benthomasAuthor Commented:
Yay that did the trick!!
Thank you so much - must be the new server 2012 that doesn't like that date format?
Very odd - thank you.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It's the different representation of the date. You can run following on both machines to see the difference:
echo %date%

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.