Bulk insert from Access to SQL Server

Hi,
Can anyone give me some advise on how to do a bulk insert into an SQL Server DB from Access using DTS.
Thanksif you currently have data in the table try makeing a copy of the table and practice importing into that. just use the wizard and when you get to the select source table and view take time to look in the transforms and all the tabs. perhaps you should check the enable identity insert.
best way to learn it is to practice it a few times.

Bulk insert data conversion error (truncation) for row 1

Hi i have a problem with MS SQL server 2000 and hope to seek for some advise.

i have the following samples
aa|0|abcdefg|
b|0|abcdefg|

i used the bulk insert in the query analyser..

BULK INSERT adl_ntid from 'C:/abc.unl with
(

FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

but they prompt me

Bulk insert data conversion error (truncation) for row 1, column 3 (NAME).

when i open in notepad, there is a square icon seperating my data. and i think this is the reason for the unsucessful insertion into my database.

i had looked into UNICODE DATA. but could not solve the problem

hope i can get some helpMy first piece of advice would be to analyze the text file using a hex editor to try to determine what character is getting put there.

My first thought is that the file was saved on a Unix system and that you may have incompatibilities with the different style line breaks.|||hI
thnx for your help. i had tried my textfile using the hex editor and it shows that my rows terminator is a linefeed.
i can insert the data using DTS import export file but when i tried implmenting in query analyser it still prompt me the same error

BULK INSERT abc FROM 'C:/abc.unl'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR =' "+CHAR(10)+" '
)

BULK INSERT abc FROM 'C:/abc.unl'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR =' {LF}'
)

once again thnx for your help|||I overlooked the error message...What is the data type for the field that is giving the problem?|||Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 3 (name).

the data types for the field names i am using are vchar.
thnx!|||

Quote:

Originally Posted by girl

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 3 (name).

the data types for the field names i am using are vchar.
thnx!

What size? This is a truncation error. That typically means that the data you are trying to insert will not fit in the field you are putting it in: you may have a varchar(4) and be inserting 8 characters.|||when i insert with the help of DTS, my information went in successfully. i used between CHAR with length 50 and VARCHAR length 50 and it prompt this same truncation error.|||

Quote:

Originally Posted by girl

when i insert with the help of DTS, my information went in successfully. i used between CHAR with length 50 and VARCHAR length 50 and it prompt this same truncation error.

What settings have you enabled for you DTS package? Perhaps you could run the SQL Profiler and see what the DTS package is calling for the INSERT?

BUILTIN/Administrators

Hello All,
SQL Server 2k SP3
Can anyone advise me on pros and cons of removing BUILTIN/Administrators ?
Thanks,
BivaDon't forget to check if there are any apps mapped with
administrator accounts under your environment. Removing
BUILTIN\Administrators you will get extra security
>--Original Message--
>Hello All,
>SQL Server 2k SP3
>Can anyone advise me on pros and cons of removing
BUILTIN/Administrators ?
>Thanks,
>Biva
>
>.
>|||Microsoft published a white paper or knowledge base
article on how to remove this group but the number
escapes me at the moment. I can tell you that you will
need to modify your sqlagent and any apps using this
group, also it is better to completely remove this group
rather than disable or demote its permissions.
I have been at several differant companies and we have
always removed this group to tighten security and it is
not very hard to do but produces a major security benefit.
>--Original Message--
>Don't forget to check if there are any apps mapped with
>administrator accounts under your environment. Removing
>BUILTIN\Administrators you will get extra security
>
>>--Original Message--
>>Hello All,
>>SQL Server 2k SP3
>>Can anyone advise me on pros and cons of removing
>BUILTIN/Administrators ?
>>Thanks,
>>Biva
>>
>>.
>.
>|||The SQL Server 2000 BUILTIN\Administrators login account
maps to the local Administrators group on the local
computer where SQL Server 2000 is installed. The local
Administrators group includes the Domain Administrators
group. The BUILTIN\Administrators group is by default a
member of the sysadmin fixed server role, to which the sa
login account maps in SQL Server 2000. Thus, anyone who
is a Domain Administrator or a local Administrator will
automatically be a member of the sysadmin fixed server
role for that SQL Server installation and possibly other
installations. The DBA can resolve this by removing the
BUILTIN\Administrators account from the sysadmin fixed
server role.
This practice is recommended by Microsoft.
-George Jiang
Microsoft MCDBA
>--Original Message--
>Don't forget to check if there are any apps mapped with
>administrator accounts under your environment. Removing
>BUILTIN\Administrators you will get extra security
>
>>--Original Message--
>>Hello All,
>>SQL Server 2k SP3
>>Can anyone advise me on pros and cons of removing
>BUILTIN/Administrators ?
>>Thanks,
>>Biva
>>
>>.
>.
>|||Hi Biva,
Thank you for using MSDN Newsgroup! My name is Billy and it's my pleasure
to assist you with this issue.
From your description, I understand you want to know what's the advantages
and disadvantages of removing the BUILTIN/Administrators Login in SQL
Server. Have I fully understood you? If there is anything I misunderstood,
please feel free to let me know.
==========Removing this Login IMPEDEs Windows NT system administrators from having
system administrator (sa) privileges in SQL Server, and this is for the
security benefits.
By default, the SQL Server setup creates the "BUILTIN\Administrators"
login, which gives any account in the Local Administrators group system
administrator (sa) privileges.
However, in some environments, you might not want to allow Microsoft
Windows NT system administrators to have this kind of access to SQL Server.
On a stand-alone server that is running SQL Server, you can remove the
"BUILTIN\Administrators" login from SQL Server to limit this type of access.
==========Before you removing this login, as other community member reminded you,
it's better to ensure that there are no services and application
using/mapping with the administrator accounts. Otherwise, you may meet with
some possible problems due to the removal of this login:
237604 PRB: SQL Server Agent Does Not Start and Displays Error 18456
http://support.microsoft.com/?id=237604
291255 BUG: IsAlive Check Does Not Run Under the Context of the
http://support.microsoft.com/?id=291255
295034 FIX: Microsoft Search Service May Cause 100% CPU Usage if
http://support.microsoft.com/?id=295034
274446 INF: Upgrade to SQL Server 2000 Failover Solution Recommended for All
http://support.microsoft.com/?id=274446
================On a clustered SQL Server, the removal of the "BUILTIN\Administrators"
login might cause problems. If the server is clustered and you remove the
"BUILTIN\Administrators" login, the SQL Server resource goes into an online
pending state and eventually fails.
However, if you remove the "BUILTIN\Administrators" login before you run
the Failover Cluster Wizard, the process completes successfully because the
Failover Cluster Wizard explicitly adds a SQL Server login for the accounts
that need access.
=================In SQL Server 6.5 and SQL Server 7.0
To limit Windows NT Administrators access as system administrator (sa) on a
SQL Server cluster, follow these steps:
1. Explicitly add the account that is being used for the Cluster Service as
a SQL Server login. You must give this login the System Administrator role.
Note if the server that is running SQL Server is unclustered and
reclustered, you must repeat this process.
2. Remove the "BUILTIN\Administrators" login from SQL Server after the SQL
Server Failover Cluster Wizard successfully clusters the SQL Server
installation.
---
-- To remove BUILTIN\Administrators.
sp_revokelogin [BUILTIN\Administrators]
---
===============In SQL Server 2000
To limit Windows NT Administrators access as system administrator (sa) on
a SQL Server cluster, follow these steps:
1. Explicitly add the account that is being used for the Cluster Service as
a SQL Server login. You must give this login the System Administrator role.
If full-text search is to be used on the cluster, you must add the [NT
Authority\System] account to the server sysadmin group.
For example:
---
-- Grant [NT Authority\System] a logon to SQL Server:
EXEC sp_grantlogin [NT Authority\System]
-- Add that account to the sysadmins role:
EXEC sp_addsrvrolemember @.loginame = [NT Authority\System], @.rolename ='sysadmin'
---
2. Remove the "BUILTIN\Administrators" login from SQL Server after you
install the virtual server.
---
-- To remove BUILTIN\Administrators.
EXEC sp_revokelogin [BUILTIN\Administrators]
---
Biva, does this answer your question?
If there is anything more I can do to assist you, please feel free to post
it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.