I am trying to dynamically create the errorfile name with the date the error occured. The error occurs at ERRORFILE = @errorfilename
Is this possible?
Here is the script I have so far...
-------------------------------------------------------------------------------------------------------------------
USE Temp
GO
DECLARE @errorfilename text
SET @errorfilename = 'c:\rx_data_rep os_ERROR_' + CONVERT(text, GETDATE())
BULK INSERT downloaded
FROM 'c:\rx_data_rep os.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
CHECK_CONSTRAIN TS,
FIRE_TRIGGERS,
TABLOCK,
ERRORFILE = @errorfilename)
)
2 5563 ck9663 2,878
Recognized Expert Specialist
I am trying to dynamically create the errorfile name with the date the error occured. The error occurs at ERRORFILE = @errorfilename
Is this possible?
Here is the script I have so far...
-------------------------------------------------------------------------------------------------------------------
USE Temp
GO
DECLARE @errorfilename text
SET @errorfilename = 'c:\rx_data_rep os_ERROR_' + CONVERT(text, GETDATE())
BULK INSERT downloaded
FROM 'c:\rx_data_rep os.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
CHECK_CONSTRAIN TS,
FIRE_TRIGGERS,
TABLOCK,
ERRORFILE = @errorfilename)
)
You might need to create a dynamic t-sql for this one
declare @sqlstmt varchar(150)
set @sqlstmt = 'BULK INSERT downloaded
FROM ''c:\rx_data_re pos.txt''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n'',
CHECK_CONSTRAIN TS,
FIRE_TRIGGERS,
TABLOCK,
ERRORFILE = ' + @errorfilename + ')'
exec @sqlstmt
I did not check if you BULK INSERT syntax is correct. But I hope you got the idea of what am trying to suggest.
-- CK
Thanks CK,
This is what I ended up with.
DECLARE @sqlstmt varchar(500),
@errorfilename varchar(150),
@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu as varchar(40)
SELECT @TodayDate = CONVERT(varchar (10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GET DATE())
SELECT @TodayMinu = DATEPART(mi,GET DATE())
SET @errorfilename = 'c:\rx_data_rep os_Error_' +
@TodayDate +
'_' +
@TodayHour +
@TodayMinu +
'.txt'
SET @sqlstmt = 'BULK INSERT tmp_Rx_Data_Rep os
FROM ''c:\rx_data_re pos.txt''
WITH (FIELDTERMINATO R = ''|'',
ROWTERMINATOR = ''\n'',
ERRORFILE = ' + '''' +@errorfilename + '''' +')'
EXECUTE (@sqlstmt)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: iqbal |
last post by:
Hi all,
We have an application through which we are bulk inserting rows into a
view. The definition of the view is such that it selects columns from
a table on a remote server. I have added the servers using
sp_addlinkedserver on both database servers.
When I call the Commit API of oledb I get the following error:
Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:
|
by: Mike Husler |
last post by:
We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp
to our SQL Server machine file store to load large amounts for data using
the BULK INSERT command. This is the command:
BULK INSERT db..table FROM 'S:\path\filename.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
|
by: Mike |
last post by:
I am trying to use the 'Bulk Insert' command to load a data file into a
MS-SQL db. The line I am using is:
Bulk Insert SVC_Details From "C:\XFILE.TXT" With (FieldTerminator = ',')
I have tried the file name with " around it and with ' around it and both
the " and ', but every time the Query Analyzer comes back with the following
error:
---Begin Error Msg---
|
by: moonriver |
last post by:
Right now I develop an application to retrieve over 30,000 records from a binary file and then load them into a SQL Server DB. So far I load those records one by one, but the performance is very poor: it takes more than 6 hours to finish the loading.
So could I make use of the bulk-insert mechanism of SQL Server to reduce the loading time in C# codes? or other performance improvement solutions?
|
by: m31hu1 |
last post by:
Hi all, I'm new to this forum...
I'm trying to let sql store data from a text file, but the data won't display...
what am i missing here? Your help is greatly appreciated.
create table
<?php
$con = mysql_connect("localhost","root","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
| |
by: Jason |
last post by:
Hi
I wanted to bounce an Idea off you guys. I'm not sure if it's possible
or even how to implement it .. or possibly if I'm trying to re-invent
the wheel.
Our users currently use excel and a file upload feature through an
asp.net intranet ap to bulk insert data into our databases - the
function works fine provided the file does not have format issues. The
process also requires some knowledge of excel and that users don't get
|
by: teddymeu |
last post by:
Hi Guys, since I've done nothing but ask questions these last few weeks to get my first application up and running I thought it was about time to share the wealth and help out a newbie like me since you all did the same, thanks for all that replied to me.
This code will save a csv file to a dir, you can then use this to bulk insert the information to a specific table in your DB, this is done with asp.net vb and sql, I'm using an mdf for this....
|
by: NickW |
last post by:
I have written a stored procedure that is used to load data into a SQL Server 2000 and 2005 databases from some 40ish different text files. One of the text files is loading all of the rows apart from the last one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH ( FORMATFILE =...
|
by: John A Grandy |
last post by:
Since I want BULK INSERT to fail on unique key violations , I set
MAXERRORS=0
But how to determine which row of the flat-file contains the duplicate ?
Apparently, ERRORFILE is not created for MAXERRORS=0
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |