InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 20:14:12 GMT20http://www.sqlservercentral.com/Forums/Topic680602-5-1.aspxWhen executing dynamic SQL, you need to use EXEC (@SQLStatement)Those brackets tell SQL Server it is not a store procedure call.Thanks.Mon, 23 Mar 2009 11:18:51 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic680602-5-1.aspxHi, I have tried to do the dinamyc SQL Statement, like this:Create proc BULK_INSERT_DATA(@PATH varchar(500))asdeclare @SQLStatement as varchar(200) set nocount ontruncate table bulk_1SET @SQLStatement = 'BULK INSERT bulk_1 FROM '''+@PATH+'\1.txt'' WITH ( FIRSTROW =2, FIELDTERMINATOR =''|'', ROWTERMINATOR ='''', tablock, DATAFILETYPE =''widechar'' )'execute @SQLStatementset nocount offgoBUT This raises an error: (Could not find stored procedure 'BULK INSERT bulk_Contribuintesest FROM 'C:\bulk\PastaExportacao SGCTCentral - 20090320 121402_CS\ContribuintesTipo21.txt' WITH ( FIRSTROW =2, FIELDTERMINATOR ='|', '.)what is rong with this procedure? can you help please?In my case i have other BULK Inserts on the same procedure. how can i do it , so that it will all execute? can i do like this?Create proc BULK_INSERT_DATA(@PATH varchar(500))asdeclare @SQLStatement as varchar(200) set nocount ontruncate table bulk_1SET @SQLStatement = 'BULK INSERT bulk_1 FROM '''+@PATH+'\1.txt'' WITH ( FIRSTROW =2, FIELDTERMINATOR =''|'', ROWTERMINATOR ='''', tablock, DATAFILETYPE =''widechar'' )'execute @SQLStatementtruncate table bulk_2SET @SQLStatement = 'BULK INSERT bulk_2 FROM '''+@PATH+'\2.txt'' WITH ( FIRSTROW =2, FIELDTERMINATOR =''|'', ROWTERMINATOR ='''', tablock, DATAFILETYPE =''widechar'' )'execute @SQLStatementTks, Pedroset nocount offgoMon, 23 Mar 2009 06:05:20 GMTpedro.ribeirohttp://www.sqlservercentral.com/Forums/Topic680602-5-1.aspx1) Yes if first one fails second one will execute. If you begin transaction when there is an error it rolls back so your transaction cannot wrap against both bulk inserts.2) Right it will not fail but the @@error value changes so you can use that to see if you should continue with your process after the bulk process failed or was successful.3) You dynamic SQL ...SET @SQLStatement = 'BULK INSERT bulk_table2 FROM ''' + @path1 + '\2.txt'' WITH(FIRSTROW =2,FIELDTERMINATOR =''|'',ROWTERMINATOR ='''',tablock,DATAFILETYPE =''widechar'')'Thanks.Sun, 22 Mar 2009 17:14:20 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic680602-5-1.aspxHi masters, Can you please help me understanding this bulk questions? i'm implementing bulk, and this information is very important for me.Thank you in advance.Question 1)If i have a stored procedure, that as this two BULK Insert Statements:create proc test as set nocount ontruncate table bulk_table1BULK INSERT bulk_table1FROM '..................1.txt' WITH ( FIRSTROW =2, FIELDTERMINATOR ='|', ROWTERMINATOR ='', tablock, DATAFILETYPE ='widechar' )truncate table bulk_table2BULK INSERT bulk_table2 FROM '.....................2.txt' WITH ( FIRSTROW =2, FIELDTERMINATOR ='|', ROWTERMINATOR ='', tablock, DATAFILETYPE ='widechar' )set nocount offgoIf the first bulk statement fails (because of errors) will the second one, be executed and commited if it as no errors? I what that only if the two bulk statements execute successfully, they can be commited to the database.I don't know if it's allright as it is right now (in the above proc), or if i have to put in the beginning of the procedure the expression "Begin Transaction" and on the end of the procedure, the expressions "IF @@ERROR <>0 Commit Transaction"Question 2) if i do not especify the option "MAXERRORS " on the Bulk statement, i have been reading that the max errors permited by the Bulk Insert are 10. But does this mean that if i have for instance 8 errors, during the bulk from the file to the database will the command be successfully executed and commited to the database?Suppose the proc above (question1) . If the first bulk raises 8 errors , will the two BULK commands be executed successfully?Question 3) Suppose the procedure below:create proc test (@path1)as set nocount ontruncate table bulk_table1BULK INSERT bulk_table1FROM @path1 + '\1.txt' WITH ( FIRSTROW =2, FIELDTERMINATOR ='|', ROWTERMINATOR ='', tablock, DATAFILETYPE ='widechar' )truncate table bulk_table2BULK INSERT bulk_table2 FROM '@path1 + '\2.txt' WITH ( FIRSTROW =2, FIELDTERMINATOR ='|', ROWTERMINATOR ='', tablock, DATAFILETYPE ='widechar' )set nocount offgoThis dont work because of the variable @path1.I need to receive part of the path from ho is calling the procedure. how can i code the procedure, so that it works well with a variable path?tks again, PedroFri, 20 Mar 2009 11:25:01 GMTpedro.ribeiro