BULK INSERT, setting static data using the format file

Hello dbforums,

I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file?

The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid.

ALTER TRIGGER DICastRaw1hrInsertGuid
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK))

More Questions:

- The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ...

ALTER TRIGGER DICastRaw1hrInsertDate
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT
AS
DECLARE @.ID as integer
SELECT @.ID = i.recordid from inserted i
UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE recordid = @.ID

- I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this?

Thanks,
ChrisOr create a table that matches your file and load it...then transfer the data to it's final destination with a sproc...|||Loading into one table and then moving data into another? How about adding one more for fun?

Just look for this topic in BOL:

Using a Data File with Fewer Fields|||Originally posted by rdjabarov
Loading into one table and then moving data into another? How about adding one more for fun?

Just look for this topic in BOL:

Using a Data File with Fewer Fields

Why not...a staging table works great, especially when you need to audit data, add stuff ect.

Yes, he can use a format file...just set the server column order to 0

Lots of posts seem to deal with "unknown" file structures or missing data...or data types that are "iffy"..

Me, I prefer to let the feeder know there file is garbage, and have them resend it and have a locked down process...

Not always the case though...|||i can help with the Bulk Insert / ADO problem
but give me an example of what you are trying to load
in what kind of table|||Originally posted by Karolyn
i can help with the Bulk Insert / ADO problem
but give me an example of what you are trying to load
in what kind of table

Thanks to all for your responses.

I think I have a solution. I will create a sql function and use it to set the GUID in the Default Field attribute of the ProductInstanceId column.

Before I call the BULK INSERT from ADO I will get the new GUID (in C++) and then ALTER the FUNCTION using ADO. I will then call BULK INSERT and the ProductInstanceId will be set to the same Guid for all the rows that are inserted during the BULK INSERT.

The next time ... I will ALTER the FUNCTION again getting a new Guid and then do the same thing. This should be VERY fast.

I have only done the prototype. When I get all the code I will post it.

Off to my Neighborhood Caucus to remove Bush from office :--)

Thanks Again,
Peace and Love,
Chris|||Hi ...

Below is the code in C++ / ADO to achieve what I did. Again, During and BULK INSERT operation we want to set a column in the table that does not exist in the source file to a specific value for all the rows that will be inserted into the table for the BULK INSERT.

To do this we are using the ALTER TABLE ADD CONTRAINT to add a static string to the default value of a specific field of the table. We will set this to a GUID and then do the BULK INSERT. When we are done with the BULK INSERT we will ALTER TABLE DROP CONSTRAINT just be be safe.

Below is the code:

UINT CDICastLoadProcess::ThreadProcessLongTermWithBulkI nsert(LPVOID pParam)
{

// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;

// Set the stored procedure name
csProcedureNameForecast6Hr = "DICastForecast6HrInsert";

try
{

// Get the starting tick count to do some duration measurment
nStart = GetTickCount();

// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));

csConnect.Format("Provider='%s';Data Source='%s';",
pDICastLoadProcess->GetProvider(),
pDICastLoadProcess->GetDataSource());
csTemp.Format("Initial Catalog='%s';",
pDICastLoadProcess->GetInitialCatalog());
csConnect += csTemp;
csTemp.Format("User Id=%s;Password=%s",
pDICastLoadProcess->GetUserId(),
pDICastLoadProcess->GetPassword());
csConnect += csTemp;

bstrConnect = csConnect.AllocSysString();

pConnection->Open(bstrConnect,"","",adConnectUnspecified);

pConnection->CommandTimeout = DICL_ADO_CONNECTION_TIMEOUT;

// Create the GUID object and then create the GUID using the
// CoCreateGuid() method
pguidProductInstanceId = new GUID;
hrReturn = CoCreateGuid(pguidProductInstanceId);

// Convert the GUID to a string
// must link in Rpcrt4.lib for UuidToString
UuidToString(pguidProductInstanceId, &strProductInstanceId);
delete pguidProductInstanceId;

// We are going to drop the constraint just in case that it
// exists.
try
{
// Drop the guid constrain to the table, incase it exisits
csSql = "ALTER TABLE Alphanumericdata.dbo.DICastRaw6hr";
csSql += " DROP CONSTRAINT DF_DICastRaw6hr_ProductInstanceId";
bstrSql = csSql.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);
}
catch(...)
{
// If the contraint doe not exist, as it should not then we will
// fall into here
csMessage.Format("Guid contraint does not exist prior to alter, normal status. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_DEBUG, csMessage, csMethodName);
}

// Add the guid constrain to the table
csMessage.Format("Add guid constraint to table. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csMessage, csMethodName);
csMessage.Format("Guid value strProductInstanceId: %s Thread:%d",strProductInstanceId, nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_DEBUG, csMessage, csMethodName);

csSql = "ALTER TABLE Alphanumericdata.dbo.DICastRaw6hr";
csSql += " ADD CONSTRAINT DF_DICastRaw6hr_ProductInstanceId";
// csSql += " DEFAULT 'AA9a44eb-092b-445b-a224-cc7cdd207092' FOR ProductInstanceId";
csTemp.Format(" DEFAULT '%s' FOR ProductInstanceId", strProductInstanceId);
csSql += csTemp;
bstrSql = csSql.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);

// Set the ProductId (GUID) in the recordset
// sval.SetString((const char *)strProductInstanceId);
// pRecordset->Fields->GetItem(L"ProductInstanceId")->PutValue(sval);

// pDICastLoadProcess->GetInputFilename()

// Typically the filename will come to use with a path. We only want
// the filename, we will remove the path if we find one. We are adding
// one to the position as ReverseFind() returned the position of
// the '\\', and we want the position of the next char
csInputFilename = pDICastLoadProcess->GetInputFilename();
nPos = csInputFilename.ReverseFind('\\');
nPos++;
csInputFilename = csInputFilename.Mid(nPos);

csMessage.Format("Begin sql bulk insert: %s Thread:%d", csInputFilename, nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csMessage, csMethodName);

// Create the SQL string using
csTemp = "BULK INSERT [Alphanumericdata].[dbo].[DICastRaw6Hr]";
//csTemp += " FROM '\\\\filer1\\DATA.TEST\\csv\\DICASTST.CSV'";
csTemp += " FROM '\\\\filer1\\DATA.TEST\\csv\\";
csTemp += csInputFilename;
csTemp += "' WITH (FIELDTERMINATOR = ',',";
csTemp += " FIRSTROW = 2,";
// csTemp += " LASTROW = 5000,";
csTemp += " ROWTERMINATOR = '\n',";
csTemp += " FIRE_TRIGGERS,";
csTemp += " FORMATFILE = '\\\\filer1\\DATA.TEST\\csv\\DICASTLTF.CSV')";

// Set the bstr and then execute the sql
bstrSql = csTemp.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);

csMessage.Format("Bulk insert completed successfully: %s Thread:%d", csInputFilename, nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csMessage, csMethodName);

nEnd = GetTickCount();
nElapsedTime = (float)(nEnd - nStart)/(float)1000;

// Set the end time
COleDateTime oledtEndDateTime = COleDateTime::GetCurrentTime();

// Create the time span object using the values passed
COleDateTimeSpan oledtDuration = oledtEndDateTime - oledtStartDateTime;

// Format the elapse time
CString csDuration = oledtDuration.Format("%H:%M:%S");

// write message to introduce the Method
csLogMessage.Format("Bulk insert stats: %s Count: %d Error: %d Elapsed time: %s Thread:%d",
csInputFilename,
nCount,
nError,
csDuration,
nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csLogMessage, csMethodName);

// Drop the guid constrain to the table
csMessage.Format("Drop guid constraint from table. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csMessage, csMethodName);
csSql = "ALTER TABLE Alphanumericdata.dbo.DICastRaw6hr";
csSql += " DROP CONSTRAINT DF_DICastRaw6hr_ProductInstanceId";
bstrSql = csSql.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);


// write message to introduce the Method
csLogMessage.Format("ThreadProcessLongTermWithBulkInsert() thread ending normally. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csLogMessage, csMethodName);

}
catch(_com_error *e)
{
}
catch(...)
{
}

// Clean up objects before exit the thread.
if (pConnection)
{
if (pConnection->State == adStateOpen)
{
pConnection->Close();
}
}

delete pvTemp;

if (pDICastLoadProcess->GetOwner())
{
// Send a message that we are done
::PostMessage(pDICastLoadProcess->GetOwner()->m_hWnd,
WM_PROCESS_DICAST_THREAD,
(WPARAM) 103,
(LPARAM) nRowCount);
}

// We need these messages at the end.
// We need a flush in the Logging Class ?
csLogMessage.Format("Posting message to calling application. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csLogMessage, csMethodName);

csLogMessage.Format("Ending ThreadProcessLongTermWithBulkInsert(). Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csLogMessage, csMethodName);

// Decrement the thread count on the way out
pDICastLoadProcess->SetThreadCountDec();

return 0;
}

BULK INSERT SYNTAX

I am setting up a new database using a shopping cart SW, when I create the DB
using there script, I get the following error

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '('.

The line syntax is as follows.

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ','

)

GO

What is wrong?

hi,

ShaneShowers wrote:

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

you have the semicolon (;) in the wrong place... at the end of the first line after the file to be imported.. the semicolon indicates a statement termination so that the next statement just becomes

WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
)

which makes no sense.... remove it and place it at the end of the statement

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);

regards

bulk insert fails

I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).

Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):

BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)

If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.

If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.

My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.

I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.

I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.

Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).

Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).

I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.

Can someone provide instructions?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

bulk insert fails

I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).

Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):

BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)

If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.

If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.

My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.

I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.

I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.

Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).

Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).

I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.

Can someone provide instructions?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

BULK INSERT

hello,
i want to import a flat file in the sql server.
Is it possible that when setting up the bulk insert command
example
BULK INSERT TFB_EasyCashCustomerFTemp FROM 'e:\mycustomer.csv'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '|\n'
)
also a table with the number of fields shoud be created (the name should be
for example mycustomer)?
If this table exists - all works ok!
All fields in the table could be of type varchar(50).The table has more then
80 fields.
thanks
is it possible that the bulk insert command>> .. also a table with the number of fields shoud be created
Without knowing the structure of the file, you cannot do that.
Of course you can create a staging table and do the BULK INSERT and move it
to the destination table which has got the right constraints and keys to
prevent data corruption.
Anith|||thanks Anith
"Anith Sen" wrote:

> Without knowing the structure of the file, you cannot do that.
>
> Of course you can create a staging table and do the BULK INSERT and move i
t
> to the destination table which has got the right constraints and keys to
> prevent data corruption.
> --
> Anith
>
>

Built in report tool returns dollar symbol not pound symbol.

I can't be the only one to of noticed this but my local setting are all set to United Kingdom, but when using the in built reporting tool I always get dollar symbols.
The command I used to format the text box was the formatcurrency() express which states it will format the text in accordance with the settings in control panel. The only thing I can think of is that it is either using a system default setting (were on a domain with roaming profiles) or it uses the language as set on the SQL server (which I haven't checked yet).

Anyone else noticed this odd behavior or know where you type the expression pattern to make a custom currency format for UK?

I use a custom format entered in the Format code: box

￡#,##0.00

|||Yeah thats, the method I used in the end. Shame the formatcurrency() function doesn't do what it should out of the box.
Thank you. |||

Yep, I'd be interested to know why we can't default to local currency settings as with say Office programs...

Let me know if you find out :)

|||Yes, would be useful. I might google once I have some spare time. Just hope one of the developers of that section who might know passes over this.|||

You may want to read this: http://msdn2.microsoft.com/en-us/library/ms156493.aspx

The Report has a Language property which could be set to a static value, such as en-UK. Or you could set it to the current user's language by using an expression: =User!Language. Note: you can also override the report's language on individual textboxes by explicitly setting the textbox.Language property.

-- Robert

|||I can confirm that setting the language of the report does in fact fix the local symbol's. I never noticed this property as it is tucked away. To get to it you have to select it from the drop down menu in properties ( http://www.devstuff.eu/images/stories/msdn/ReportSelecter.JPG ). Then you can set report wide settings.

Interesting points about this :
Text boxes local settings does not override form default.|||Thanks guys.