sql server programming:
Hello,
I have this sproc to insert csv files in my tables. First, It truncates the
table. Afther that, it inserts the csv file.
CREATE PROCEDURE [dbo].[I_TDE_Imports]
@FilePath VARCHAR(500),
@FileName VARCHAR(40),
@TableName VARCHAR(6)
AS
DECLARE @sql NVARCHAR(2000)
DECLARE @Delete NVARCHAR(40)
SET @Delete = 'TRUNCATE TABLE ' + @TableName
EXECUTE sp_executesql @Delete
SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM
OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt;
*.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName +
''')'
EXECUTE sp_executesql @sq
What I need is to know how to handle errors? When I pass a wrong filepath,
filename or a table name, I get this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any
information about the error.
So I tried this
CREATE PROCEDURE [dbo].[I_TDE_Imports]
@FilePath VARCHAR(500),
@FileName VARCHAR(40),
@TableName VARCHAR(6)
AS
DECLARE @sql NVARCHAR(2000)
DECLARE @Delete NVARCHAR(40)
DECLARE @err INT
SET @Delete = 'TRUNCATE TABLE ' + @TableName
EXECUTE sp_executesql @Delete
SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM
OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt;
*.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName +
''')'
EXECUTE sp_executesql @sq
SET @err = @@ERROR
--EXECUTE @err = sp_executesql @sq
IF @err <>o
RETURN @err
ELSE
RETURN 0
This is how I call my sproc. The file does not exists
DECLARE @RC int
EXEC @RC = I_TDE_Imports 'C:\', 'A.csv', 'TDE001'
PRINT @RC
When I try the first or the second version of my sproc, I allways have this
error. I am unable to print the result of the call...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any
information about the error.
What do I have to do to return the error correctly from my sproc?
Thank you
Marc R.
