odbcbcp error loading data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

odbcbcp error loading data

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-09-05 : 14:32:17

I'm receiving the error, "The Microsoft Jet database engine could not find the object 'tri_actfile.txt'. Make sure the object exists and that you spell its name and the path name correctly."

How do I call a table name in sql after the -Q?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter Procedure dbo.sp_cmdshell_test
(@ClientAbbrev nchar(4), @FileDate nvarchar(8), @SessionId nvarchar(50) )
AS

DECLARE @DataUpdates nvarchar(255)
DECLARE @Common nvarchar(255)
DECLARE @UServer nvarchar(255)
DECLARE @FullFileName nvarchar(68)
DECLARE @FileName nvarchar(64)
DECLARE @FileExt nvarchar(3)
DECLARE @SQL varchar(3500)
DECLARE @triFileName nvarchar(255)
Declare @DataArchive nvarchar(255)

Declare @Ip_FileName nvarchar(255)
Declare @Ip_Active char(1)

Set @UServer = '\\AO3\D$'

select @Common = sd_value from Msystem.dbo.Msysdata WHERE SD_Property = 'CommonDir'
select @DataUpdates = sd_value from Msystem.dbo.Msysdata WHERE SD_Property = 'DataUpdate'

Declare ActiveFile Cursor For
Select Ip_FileName, Ip_Active from tbl_InputFiles
where Ip_Active = 'Y'

Open ActiveFile
Fetch Next From ActiveFile
Into @Ip_FileName, @Ip_Active

While @@Fetch_Status = 0
Begin

Exec('truncate table tri_'+ @Ip_FileName)

Set @FullFileName = @Ip_Filename + '.csv'

Set @sql = 'master..xp_cmdshell ''' + @UServer + @Common + '\scripts\odbcbcp -D CCBM_TEST -i ' + @UServer + @DataUpdates + '\' + @ClientAbbrev + '\' + @FullFileName + ' -Q tri_' + @Ip_FileName +''''

Print(@sql)
Exec(@SQL)

Fetch Next From ActiveFile
Into @Ip_FileName, @Ip_Active

End

CLOSE ActiveFile
DEALLOCATE ActiveFile

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-09-05 : 14:43:23

Can I set a file extension to the end of @Ip_FileName so it recognizes it as a table name?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources