显示标签为“key”的博文。显示所有博文
显示标签为“key”的博文。显示所有博文

2012年3月8日星期四

bcp has primary key contraint error

When running a bcp to insert data into a table, I get a violation of primary key contraint. Is there any way to tell what row is causing the problem? The error I'm getting is:
SQLState = 23000, NativeError = 2627
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in object 'table_name'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
BCP copy in failed
Hi
Try the -e option. From Books online:
-e err_file
Specifies the full path of an error file used to store any rows bcp is
unable to transfer from the file to the database. Error messages from bcp go
to the user's workstation. If this option is not used, an error file is not
created.
John
"erika" <anonymous@.discussions.microsoft.com> wrote in message
news:9888AE94-88DC-499B-8EA9-E61C2A72F546@.microsoft.com...
> When running a bcp to insert data into a table, I get a violation of
primary key contraint. Is there any way to tell what row is causing the
problem? The error I'm getting is:
> SQLState = 23000, NativeError = 2627
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of
PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in object
'table_name'.
> SQLState = 01000, NativeError = 3621
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been terminated.
> BCP copy in failed
>

bcp has primary key contraint error

When running a bcp to insert data into a table, I get a violation of primary
key contraint. Is there any way to tell what row is causing the problem? Th
e error I'm getting is:
SQLState = 23000, NativeError = 2627
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation
of PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in obje
ct 'table_name'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The sta
tement has been terminated.
BCP copy in failedHi
Try the -e option. From Books online:
-e err_file
Specifies the full path of an error file used to store any rows bcp is
unable to transfer from the file to the database. Error messages from bcp go
to the user's workstation. If this option is not used, an error file is not
created.
John
"erika" <anonymous@.discussions.microsoft.com> wrote in message
news:9888AE94-88DC-499B-8EA9-E61C2A72F546@.microsoft.com...
> When running a bcp to insert data into a table, I get a violation of
primary key contraint. Is there any way to tell what row is causing the
problem? The error I'm getting is:
> SQLState = 23000, NativeError = 2627
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of[/v
bcol]
PRIMARY KEY constraint 'table_name'. Cannot insert duplicate key in object
'table_name'.[vbcol=seagreen]
> SQLState = 01000, NativeError = 3621
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement
has
been terminated.
> BCP copy in failed
>

2012年3月6日星期二

BCP Error with Key Field Name

Hi I have a table with the following structure

ID Int,
Key nVarchar2(50),
Text nText,
upsizeTS TimeStamp

I want to use BCP to import some data,

it always gives me the following error:
SQLSTATE = 37000, native error 156
Incorrect syntax near the keyword 'Key'

There is no way I can change the name of the field to something else.

Could anybody tell me why am I getting this error ?
Thank you in advance.You have chosen two column names, which are also reserved words: key and text. You will have to put [] around any occurence of these two column names in your script, e.g. [key] instead of key, and [text] instead of text.|||How do I do that, what script are you talking about. I am using a BCP command.

Originally posted by DoktorBlue
You have chosen two column names, which are also reserved words: key and text. You will have to put [] around any occurence of these two column names in your script, e.g. [key] instead of key, and [text] instead of text.|||Post your BCP command.

2012年2月23日星期四

BCP and keyword column names

When trying to import data from a text file using BCP (where one of the
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names... :)
"Dan Guzman" wrote:
> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> > When trying to import data from a text file using BCP (where one of the
> > column names of the table is the keyword 'Key'), I get a syntax error
> > which
> > is to be expected. For keyword table names, you use [tablename] in the
> > bcp
> > command. Is there a special command that I can use that will do something
> > similar for keyword column names?
>
>

BCP and keyword column names

When trying to import data from a text file using BCP (where one of the
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?
You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?
|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names...
"Dan Guzman" wrote:

> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
>
>

2012年2月9日星期四

Basic questions on Service Master Key

Hi. I'm reading about encryption and not totally up-to-speed on what the Service Master Key is all about...

Let me paraphrase this book: Microsoft SQL Server 2005 Implementation and Maintenance Training Kit by Microsoft Press, while I ask questions...
__

The database engine uses the Service Master Key to encrypt...
- Linked server passwords (example?)
- Connection Strings (example?)
- Account credentials (example?)
- All database master keys

You should back up the Service Master Key and store it offsite.
(Even if I never plan to use encryption?)

ALTER SERVICE MASTER KEY REGENERATE regenerates the service master key.
(Is it building a completely different key, or regenerating the original?)
(Why would I want to regenerate it?)
(Does this void all database master keys encrypted by the original Service Master Key?)
(If so, do I regenerate all database master keys voided by the regeneration?)

A database master key is optional.
(So if I have Server A with encrypted database A, that I want to restore to Server B,
I need the following items to perform restore?
- a backup of database A
- the Service Master Key of A at time of backup
- associated password of Service Master Key at time of backup)

Just repeating what it is mentioned in BOL:

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy. Unless the key has been compromised, this resource-intensive operation should be scheduled during a period of low demand.

Also http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx fyi for more information.

|||

A few corrections:

The Service Master Key (SMK) has two encryptions, both made using DPAPI - one uses the service account credentials, the other one uses the machine credentials. Note that neither of these encryptions is necessarily protecting the SMK from a machine administrator.

Regenerating the SMK will only require re-encrypting the entities encrypted by it: linked server login passwords (the ones you specify when you set up a linked server), credentials (as created by CREATE CREDENTIAL), and database master keys.

Coming back to the original questions:

It is not necessary to backup the SMK if you do not plan to use it.

REGENERATE creates a new key. It can be used as part of a key rotation policy or if you have reason to believe the old key was compromised. You can also use this to generate a new key if the old one was corrupted and you have no backup around. Regenerating the SMK doesn't invalidate any database master key (DbMK) - it just re-encrypts them with the new key.

For restoring a database to another server, see last paragraphs of http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx. You don't need to move the SMK to the new server, you just need to open and reencrypt the DbMK with the new server's SMK, and you only need to do this if you had the same setup on the original server.

Thanks
Laurentiu

|||

Thanks for taking the time to explain some concepts. I haven't had any experience with encryption, and now feel further behind with SQL 2005's new concepts.

I'm having problems understanding the extent the SMK is capable of.... you wrote about the SMK being compromised, but I don't see how you could compromise it, or what you could do with it once you have it?

- You have to be a SysAdmin to restore a SMK
- You would somehow have to get the SMK backup file onto the server
- You would have to have local access to the SMK backup file to restore it
- You would have to know the password of the restored SMK

And even if all that occurs:
- The linked servers all still show "********" for a password
- You can't decrypt data with a SMK (can you?)
- You can't decrypt a Database Master Key with the SMK (can you?)

|||

The SMK allows you to decrypt all that is encrypted beginning with it - database master keys, initially, then anything encrypted by the database master keys, and so on, towards the data itself.

As you've noticed, there are good protections set up around accessing the SMK, so my mention of "compromising" is more theoretical than practical - you could "loose" the SMK by loosing an SMK backup and the password protecting it, for example.

Having the SMK and a copy of a database with encrypted data (where the encryption protection is rooted at the SMK) allows you to decrypt the data.

Have a look at the encryption hierarchy in Books Online, to understand what key protects what information. The main point is that this is a hierarchy, so the key at its root unlocks everything below. You can detach protection of some data from this hierarchy by using password encryption for certain keys, which uses TripleDES encryption to protect those keys.

http://msdn2.microsoft.com/en-us/library/ms189586.aspx

Also listen to the presentation of encryption in SQL Server 2005 that Rob Walters gave at PASS this year. You can access Rob's presentation here:

http://cmcgc.com/Media/WMP/261115/

Also, there were no encryption features in SQL Server 2000. These features have been introduced in SQL Server 2005.

Thanks
Laurentiu