-
SQLState = 08001, NativeError = 2
This Proc was running in 2000 and I'm now moving it to 2005 and I'm getting
all sorts of errors. Xp_getfiledetails seems not to work.
--IF OBJECT_ID('prcFtpData') IS NOT NULL
--DROP PROC prcFtpSData
--GO
--CREATE PROC prcFtpData
--AS
SET NOCOUNT ON
--Declare Working Variables For The Bulk Insert Statement
DECLARE
@Filepath VARCHAR(200)
,@FNSFilename VARCHAR(200)
,@BulkInsertSQL VARCHAR(200)
SET @Filepath = '\\Poto\daily$\INFILES\'
SET @FNSFilename = 'Fin.' + RTRIM(CONVERT(CHAR(10),GETDATE()-1,112))+'.txt'
--Check if the Ftp commands Table Exists. If it doesn't then create it
IF OBJECT_ID('tbftpComm') IS NOT NULL
DROP TABLE tbftpComm
CREATE TABLE tbftpComm
(
RowId Int
, FtpComm Text
)
--Insert The Ftp commands into the Input Table
INSERT tbftpComm(RowId,FtpComm) VALUES (1,'OPEN 000.000.000.11')
INSERT tbftpComm(RowId,FtpComm) VALUES (2,'xxxxxx' )
INSERT tbftpComm(RowId,FtpComm) VALUES (3,'dddddd' )
INSERT tbftpComm(RowId,FtpComm) VALUES (4,'cd Test' )
INSERT tbftpComm(RowId,FtpComm) VALUES (5,'cd IN' )
INSERT tbftpComm(RowId,FtpComm) VALUES (6,'Prompt' )
INSERT tbftpComm(RowId,FtpComm
)
VALUES (7,'GET' + SPACE(1)+ 'Fin.' + RTRIM(CONVERT(CHAR(10),GETDATE()-1,112))+'.txt'
)
INSERT tbftpComm(RowId,FtpComm) VALUES (8 ,'dir' )
INSERT tbftpComm(RowId,FtpComm) VALUES (9 ,'quit' )
--Move Old File to Archive Folder
--EXEC master.dbo.xp_cmdshell 'Move \\Poto\Tmandaily$\FNS_INFILES\*.txt \\Poto\Tmandaily$\FNS_ARCHIVE'
---generate the text file with the ftp commands
DECLARE @FileName VARCHAR(50),@bcpCommand VARCHAR(2000)
--Concatenate the filename with today's Date
SET @FileName = ('\\Poto\daily$\FILES\ftpfiles.txt')
SET @bcpCommand = 'bcp "SELECT FtpComm FROM STAGE..tbftpComm" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -T'
EXEC master..xp_cmdshell @bcpCommand
EXEC master..xp_cmdshell '\\Poto\daily$\INFILES\Loadpath.bat'
--Create the temp table to hold the file details
IF OBJECT_ID('tbfileexists') IS NOT NULL
DROP TABLE tbfileexists
CREATE TABLE tbfileexists
(
Altname VARCHAR(32)
,[Size] VARCHAR(12)
,Creatdt VARCHAR(13)
,Creatti VARCHAR(13)
,LstWridt VARCHAR(17)
,LstWriti VARCHAR(17)
,LstAccdt VARCHAR(18)
,LstAccti VARCHAR(18)
,Attrib VARCHAR(11)
)
DECLARE @File VARCHAR(100)
SET @File =(SELECT CONVERT(CHAR(8),GETDATE()-1,112) + '.txt')
INSERT FNS_tbfileexists EXEC('master..xp_getfiledetails ' + '''\\Poto\daily$\NFILES\Fin.' + @File+'''')
These are the errors I'm getting. Can't find the problem.
output
---------------
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
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 connecti
ons.
NULL
output
---------------
NULL
-
Is sql server browser service running on sql2k5 box? Is named pipes enabled on sql2k5 server? Tried connect with tcp/ip?
-
xp_getfiledetails does not exist in SQL 2005.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|