April 7, 2011 at 8:55 am
Hi all,
we have server from this we have linked servers created to all the SQL Servers in our environment. Recently I have developed a script and that needs to deployed on all the SQL Servers in our environment so below is the script that I wrote and I'm trying to deploy the script but I have been getting errors can any one help with this code and what changes do I need to do it If I were change and the .sql file is on the CENTRAL Server.
The Error message I get here is,
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
NULL
DECLARE @SQL NVARCHAR(4000),@hostinstance varchar(200),@XP_VAL int
DECLARE DEPLY_CURS CURSOR FOR
SELECT 'XXXX'
OPEN DEPLY_CURS
FETCH NEXT FROM DEPLY_CURS INTO @HOSTINSTANCE
SET @SQL='"SQLCMD -S ['+@hostinstance+'] -E -i C:\xxxx.sql"'
print @SQL
While @@FETCH_STATUS=0
BEGIN
SELECT @XP_VAL=CAST(VALUE_IN_USE as INT)
FROM SYS.CONFIGURATIONS
WHERE NAME='XP_CMDSHELL'
IF @XP_VAL=0
BEGIN
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', '1';
RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'XP_CMDSHELL', '1';
RECONFIGURE WITH OVERRIDE;
EXEC master.dbo.xp_cmdshell @SQL
EXEC SP_CONFIGURE 'XP_CMDSHELL', '0';
RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', '0';
RECONFIGURE WITH OVERRIDE;
END
ELSE
BEGIN
EXEC master.dbo.xp_cmdshell @SQL
END
FETCH NEXT FROM DEPLY_CURS INTO @HOSTINSTANCE
END
CLOSE DEPLY_CURS
DEALLOCATE DEPLY_CURS
April 7, 2011 at 12:55 pm
Hi,
Review list of possible scenarios you should check that maybe causing this error, with executing sqlcmd command.
http://msdn.microsoft.com/en-us/library/ms190181.aspx
Just to name few:
* Server name was typed incorrectly.
* The SQL Server service on the server is not running.
etc...
@hostinstance is the problem here, before running xp_cmdshell execute PRINT which will tell you the name of the SQL Server sqlcmd cannot connect to.
PRINT @SQL
EXEC master.dbo.xp_cmdshell @SQL
Try connecting using SQL Management Studio to problematic SQL server in the context of the NT account that is running your local SQL Server.
April 7, 2011 at 2:47 pm
Thanks Marks,I messed up with the @hostinstance name. open braces and closed braces are the reason why I got the error. appreciate your help
Thanks,
Ravi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply