Hi,
Could you tell me the syntax to set primary key on two columns when I create
a table? I can't find it in the books. The syntax I found is
CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
How can put c2 as part of PRIMARY KEY?
I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
Thanks.Chrissi wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I
> create a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls
> tell me.
> Thanks.
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL
PRIMARY KEY (c1, c2) )
or
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL )
Alter Table MyTable
ADD PRIMARY KEY (c1, c2)
David Gugick
Imceda Software
www.imceda.com|||Create Table MyTable
(c1 INT Not Null,
c2 INT Not Null,
Primary Key (C1, c2))
"§Chrissi§" wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I crea
te
> a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me
.
> Thanks.
>
>|||CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
CONSTRAINT pk_MyTable PRIMARY KEY(c1 ,c2)
)
or
CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY(c1 ,c2)
)
It's technically a constraint in both cases, but you aren't
required to give it a name.
Steve Kass
Drew University
Chrissi wrote:
>Hi,
>Could you tell me the syntax to set primary key on two columns when I creat
e
>a table? I can't find it in the books. The syntax I found is
>CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
>How can put c2 as part of PRIMARY KEY?
>I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
>Thanks.
>
>|||Server: Msg 1911, Level 16, State 1, Line 1
Column name 'C1' does not exist in the target table.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Watch your spelling! Some of us choose a case-sensitive
collation now and then. ;)
SK
CBretana wrote:
>Create Table MyTable
> (c1 INT Not Null,
> c2 INT Not Null,
> Primary Key (C1, c2))
>"§Chrissi§" wrote:
>
>|||Oops! My typing is never good (two finger hint n pec) but I noticed the
upper case C and left it that way anyway... Out of curiousity, why are you
using case-sensitive collation?
"Steve Kass" wrote:
> Server: Msg 1911, Level 16, State 1, Line 1
> Column name 'C1' does not exist in the target table.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> Watch your spelling! Some of us choose a case-sensitive
> collation now and then. ;)
> SK
> CBretana wrote:
>
>|||
CBretana wrote:
> Oops! My typing is never good (two finger hint n pec) but I noticed the
> upper case C and left it that way anyway... Out of curiousity, why are you
> using case-sensitive collation?
Mostly so I can generate the appropriate error messages to include in posts
like this one. ;)
I didn't used to pay attention to this, and it didn't matter as much when
keypunch machines were uppercase-only, or with case-insensitive languages
like Pascal. I had to break sloppy habits when C came along, and though I
slipped into old habits when I started using SQL, I've found more and more
reasons not to be sloppy lately, such as keeping Erland from bugging me
if I put "northwind"."orders" in examples I post. ;)
There are plenty of things you can write that will behave differently
according to collation and language settings, and forcing myself to
be careful about case helps me see and avoid them.
SK
> "Steve Kass" wrote:
>|||>> Out of curiousity, why are you using case-sensitive collation? <<
Because Standard SQL is case-sensitive.
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Thursday, March 29, 2012
CREATE TABLE with multiple-column primary key?
Is it possible to issue the CREATE TABLE command and specify a multiple-colu
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:
> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:
> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...
Tuesday, March 27, 2012
Create table on multiple Filegroups .. is it possible ?
Can i create a table/index that spans multilple FGs such as
CREATE TABLE T1
( cola int PRIMARY KEY,
colb char(8) )
ON FG1,FG2,FG3Are you confused between files and filegroups? Have a read in bol about them.
The answer to the question you have posed is no - you have a clustered index
which resides on the data filegroup.
But I don't think it's the question you wanted to ask.
"Hassan" wrote:
> Can i create a table/index that spans multilple FGs such as
> CREATE TABLE T1
> ( cola int PRIMARY KEY,
> colb char(8) )
> ON FG1,FG2,FG3
>
>|||As Nigel says, it isn't possible. Why do you ask? The purpose of
filegroups is to provide a logical entity on which to place data. The
PHYSICAL placement of data is determined by the location of files,
rather than filegroups. So it should be possible to achieve whatever
configuration you need using a single filegroup per object.
--
David Portas
SQL Server MVP
--
CREATE TABLE T1
( cola int PRIMARY KEY,
colb char(8) )
ON FG1,FG2,FG3Are you confused between files and filegroups? Have a read in bol about them.
The answer to the question you have posed is no - you have a clustered index
which resides on the data filegroup.
But I don't think it's the question you wanted to ask.
"Hassan" wrote:
> Can i create a table/index that spans multilple FGs such as
> CREATE TABLE T1
> ( cola int PRIMARY KEY,
> colb char(8) )
> ON FG1,FG2,FG3
>
>|||As Nigel says, it isn't possible. Why do you ask? The purpose of
filegroups is to provide a logical entity on which to place data. The
PHYSICAL placement of data is determined by the location of files,
rather than filegroups. So it should be possible to achieve whatever
configuration you need using a single filegroup per object.
--
David Portas
SQL Server MVP
--
Sunday, March 25, 2012
Create Table and Alter table:
Hi All,
I am using SQl server studio management to create table.
How to set two attributes as a primary key: composite key. like ( proj_id, emp id) both as primary key.
How to specify the forign key constraint. using alter table
Please give me the example: don't syntax which msdn gives.
Thanks and Regards
Abdul M.G
Hi,
Look at this.
1CREATE TABLE Menu2(3 MenuIdint,4 Titlevarchar(50),5 Urlvarchar(256),6 ParentIdintNULL7)89ALTER TABLE MenuADD CONSTRAINT MenuIdPRIMARY KEY1011ALTER TABLE MenuADD CONSTRAINT ParentIdREFERENCES Menu(MenuId)sql
CREATE TABLE - two fields combine to make the primary key
Perhaps I'm dating myself.
In a table, I want to create a primary key that consists
of two columns (patrolId and incidentId). From the
documentation I have, it looks like you can only have one
field that is a primary key.
Can you help?
hi Ed,
Ed H wrote:
> Perhaps I'm dating myself.
> In a table, I want to create a primary key that consists
> of two columns (patrolId and incidentId). From the
> documentation I have, it looks like you can only have one
> field that is a primary key.
> Can you help?
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE test_table (
patrolId INT NOT NULL ,
incidentId INT NOT NULL ,
Data VARCHAR(10) NOT NULL ,
CONSTRAINT pk_test_table
PRIMARY KEY ( patrolId , incidentId )
)
GO
DROP TABLE test_table
CREATE TABLE synopsis is available at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
In a table, I want to create a primary key that consists
of two columns (patrolId and incidentId). From the
documentation I have, it looks like you can only have one
field that is a primary key.
Can you help?
hi Ed,
Ed H wrote:
> Perhaps I'm dating myself.
> In a table, I want to create a primary key that consists
> of two columns (patrolId and incidentId). From the
> documentation I have, it looks like you can only have one
> field that is a primary key.
> Can you help?
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE test_table (
patrolId INT NOT NULL ,
incidentId INT NOT NULL ,
Data VARCHAR(10) NOT NULL ,
CONSTRAINT pk_test_table
PRIMARY KEY ( patrolId , incidentId )
)
GO
DROP TABLE test_table
CREATE TABLE synopsis is available at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
CREATE SYMMETRIC KEY
Hi,
I am in the process of switching an application from SQL Server 2000 to SQL
Server 2005, with the main purpose to use the encryption capabilities of SQL
Server 2005.
To test it out using encryption, I created a database, TestEncrypt, using
all the defaults.
I then worked with the script from the help file in encryption[SQL Serve
r] /
columns / Simple Symmetric Encryption.
When I run
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
from the script, I get the following error:
Msg 15314, Level 16, State 1, Line 2
Either no algorithm has been specified or the bitlength and the algorithm
specified for the key are not available in this installation of Windows.
When I change this to
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
it completes successfully.
However, the Decrypted ID (here is the output):
NationalIDNumber: 002020002
Decrypted ID Number: 2
This does not make sense (the decrypted value should be the same as the
original value).
Full script is below.
Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine) and
why the decrypted value is different from the original value?
Thanks.
Bob
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
Use TestEncrypt
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Employee
(
NationalIDNumber varchar(50) NULL
) ON [PRIMARY]
GO
COMMIT
Use TestEncrypt
GO
INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'
GO
SELECT * FROM dbo.Employee
GO
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [TestEncrypt];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM Employee;
GOI found the problem on the on the encryption inconsistency, still would like
to know about the AES_256. Thanks.
"Gerhard" wrote:
> Hi,
> I am in the process of switching an application from SQL Server 2000 to SQ
L
> Server 2005, with the main purpose to use the encryption capabilities of S
QL
> Server 2005.
> To test it out using encryption, I created a database, TestEncrypt, using
> all the defaults.
> I then worked with the script from the help file in encryption[SQL Ser
ver] /
> columns / Simple Symmetric Encryption.
> When I run
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = AES_256
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> from the script, I get the following error:
> Msg 15314, Level 16, State 1, Line 2
> Either no algorithm has been specified or the bitlength and the algorithm
> specified for the key are not available in this installation of Windows.
> When I change this to
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> it completes successfully.
> However, the Decrypted ID (here is the output):
> NationalIDNumber: 002020002
> Decrypted ID Number: 2
> This does not make sense (the decrypted value should be the same as the
> original value).
> Full script is below.
> Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine) an
d
> why the decrypted value is different from the original value?
> Thanks.
> Bob
>
> /* To prevent any potential data loss issues, you should review this scrip
t
> in detail before running it outside the context of the database designer.*
/
> BEGIN TRANSACTION
> Use TestEncrypt
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> GO
> CREATE TABLE dbo.Employee
> (
> NationalIDNumber varchar(50) NULL
> ) ON [PRIMARY]
> GO
> COMMIT
> Use TestEncrypt
> GO
> INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'
> GO
> SELECT * FROM dbo.Employee
> GO
> --If there is no master key, create one now
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd1
17r$$#1946kcj$n44ncjhdlj'
> GO
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> USE [TestEncrypt];
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM Employee;
> GO
>
>
>
>
>|||Depends on the version of Windows you're running. Different versions have
different variations of CryptoAPI. I believe all versions of CryptoAPI have
some basic algorithms available (RC2, DES), but AES is not available on all
platforms.
"Gerhard" <acsla@.community.nospam> wrote in message
news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...[vbcol=seagreen]
>I found the problem on the on the encryption inconsistency, still would
>like
> to know about the AES_256. Thanks.
> "Gerhard" wrote:
>|||AES is only supported by SQL Server on Windows 2003.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uYZddkbhGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Depends on the version of Windows you're running. Different versions have
> different variations of CryptoAPI. I believe all versions of CryptoAPI
> have some basic algorithms available (RC2, DES), but AES is not available
> on all platforms.
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...
>sql
I am in the process of switching an application from SQL Server 2000 to SQL
Server 2005, with the main purpose to use the encryption capabilities of SQL
Server 2005.
To test it out using encryption, I created a database, TestEncrypt, using
all the defaults.
I then worked with the script from the help file in encryption[SQL Serve
r] /
columns / Simple Symmetric Encryption.
When I run
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
from the script, I get the following error:
Msg 15314, Level 16, State 1, Line 2
Either no algorithm has been specified or the bitlength and the algorithm
specified for the key are not available in this installation of Windows.
When I change this to
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
it completes successfully.
However, the Decrypted ID (here is the output):
NationalIDNumber: 002020002
Decrypted ID Number: 2
This does not make sense (the decrypted value should be the same as the
original value).
Full script is below.
Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine) and
why the decrypted value is different from the original value?
Thanks.
Bob
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
Use TestEncrypt
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Employee
(
NationalIDNumber varchar(50) NULL
) ON [PRIMARY]
GO
COMMIT
Use TestEncrypt
GO
INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'
GO
SELECT * FROM dbo.Employee
GO
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [TestEncrypt];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM Employee;
GOI found the problem on the on the encryption inconsistency, still would like
to know about the AES_256. Thanks.
"Gerhard" wrote:
> Hi,
> I am in the process of switching an application from SQL Server 2000 to SQ
L
> Server 2005, with the main purpose to use the encryption capabilities of S
QL
> Server 2005.
> To test it out using encryption, I created a database, TestEncrypt, using
> all the defaults.
> I then worked with the script from the help file in encryption[SQL Ser
ver] /
> columns / Simple Symmetric Encryption.
> When I run
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = AES_256
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> from the script, I get the following error:
> Msg 15314, Level 16, State 1, Line 2
> Either no algorithm has been specified or the bitlength and the algorithm
> specified for the key are not available in this installation of Windows.
> When I change this to
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> it completes successfully.
> However, the Decrypted ID (here is the output):
> NationalIDNumber: 002020002
> Decrypted ID Number: 2
> This does not make sense (the decrypted value should be the same as the
> original value).
> Full script is below.
> Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine) an
d
> why the decrypted value is different from the original value?
> Thanks.
> Bob
>
> /* To prevent any potential data loss issues, you should review this scrip
t
> in detail before running it outside the context of the database designer.*
/
> BEGIN TRANSACTION
> Use TestEncrypt
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> GO
> CREATE TABLE dbo.Employee
> (
> NationalIDNumber varchar(50) NULL
> ) ON [PRIMARY]
> GO
> COMMIT
> Use TestEncrypt
> GO
> INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'
> GO
> SELECT * FROM dbo.Employee
> GO
> --If there is no master key, create one now
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd1
17r$$#1946kcj$n44ncjhdlj'
> GO
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> USE [TestEncrypt];
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM Employee;
> GO
>
>
>
>
>|||Depends on the version of Windows you're running. Different versions have
different variations of CryptoAPI. I believe all versions of CryptoAPI have
some basic algorithms available (RC2, DES), but AES is not available on all
platforms.
"Gerhard" <acsla@.community.nospam> wrote in message
news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...[vbcol=seagreen]
>I found the problem on the on the encryption inconsistency, still would
>like
> to know about the AES_256. Thanks.
> "Gerhard" wrote:
>|||AES is only supported by SQL Server on Windows 2003.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uYZddkbhGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Depends on the version of Windows you're running. Different versions have
> different variations of CryptoAPI. I believe all versions of CryptoAPI
> have some basic algorithms available (RC2, DES), but AES is not available
> on all platforms.
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...
>sql
Wednesday, March 21, 2012
Create sequential numbers in a column
I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0
There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:
INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent
If this does answer your question, please provide the following:
o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, March 11, 2012
Create Primary Key with increment and format?
I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.
And, importantly, the value is set to "increment".
The format is "phd"000 - so it starts outphd001,phd002, and so on...
How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?In SQL Server you do Unique constraint or Unique index for no duplicates the former allow nulls the later not null being primary key and set IDENTITY property on the column for auto increament. Run a search for Unique constraint and Unique index and the IDENTITY property in SQL Server BOL(books online). Hope this helps.
And, importantly, the value is set to "increment".
The format is "phd"000 - so it starts outphd001,phd002, and so on...
How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?In SQL Server you do Unique constraint or Unique index for no duplicates the former allow nulls the later not null being primary key and set IDENTITY property on the column for auto increament. Run a search for Unique constraint and Unique index and the IDENTITY property in SQL Server BOL(books online). Hope this helps.
create other index
Hello:
I create a key in one table, the field is uniqueidentifier, but I search
very frequently for one [date] field, and I like to index this field with
datetime datatype to optimize the search, how can I do that?
Best regards,
Owen."Owen" <anibal@.prensa-latina.cu> wrote in message
news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello:
> I create a key in one table, the field is uniqueidentifier, but I search
> very frequently for one [date] field, and I like to index this field with
> datetime datatype to optimize the search, how can I do that?
> Best regards,
> Owen.
>
CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
Example:
CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
As a side note.. If you are frequently searching on a range of dates using
statements like BETWEEN, then you may find a CLUSTERED index on this date
column to be far more effective. Clustered indexes on GUIDs can be clumsy
at best.
Rick Sawtell
MCT, MCSD, MCDBA|||hi, thanks for answer:
the problem is that I need keep this two index on the same table, the guid
and the dates, but only one can be CLUSTERED, how can optimize this to all
index work faster?
Best regards,
Owen.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> "Owen" <anibal@.prensa-latina.cu> wrote in message
> news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
with
> CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
> Example:
> CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
>
> As a side note.. If you are frequently searching on a range of dates
using
> statements like BETWEEN, then you may find a CLUSTERED index on this date
> column to be far more effective. Clustered indexes on GUIDs can be
clumsy
> at best.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Owen as you say only one index can be clustered, but one of the significant
benefits of a clustered index is for querioes based on a range of values
since all those rows will be found near each other (all else being equal).
Now a GUID is *extremely unlikely* (probably nearly safe to say never)
likely to be used in a query like
WHERE guidcol between 'yuckyguidvalue1' and 'yuckyguidvalue2'
So makeing the CI on the date will have the benefit (if you retrieve rows
based on a date range) of being faster than retrieving the same range when
the supporting index is non-clustered. Hence Rick's suggestion that you
consider making the CI on the date column.
To go much further needs an understanding of the type of queries on the
table, and the approx size of it, and is frequently best checked by running
testst on your configuration.
You may have guessed I fall in to the camp of disliking guids for any sort
of identifiers unless there is an absolute cast-iron reason for needing them
(like a distributed db that has to have surrogate keys generated uniquely,
or complex replication) .
Mike John
"Owen" <anibal@.prensa-latina.cu> wrote in message
news:eNcVeIwEGHA.3064@.TK2MSFTNGP10.phx.gbl...
> hi, thanks for answer:
> the problem is that I need keep this two index on the same table, the guid
> and the dates, but only one can be CLUSTERED, how can optimize this to all
> index work faster?
> Best regards,
> Owen.
>
> "Rick Sawtell" <Quickening@.msn.com> wrote in message
> news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> with
> using
> clumsy
>|||Just don't forget that the GUID may be an FK where the "=" searches would
return many rows. In that case depending on how many queries against each
column and what columns you fetch we still may consider using the GUID as
the clustered index.
/ Tobias|||True - apologies - i was falling into an assumption that the guid was going
to be unique!
Mike
"Tobias Thernstrm" <ttnospam@.rbam.se> wrote in message
news:O%23qfmNCFGHA.3632@.TK2MSFTNGP10.phx.gbl...
> Just don't forget that the GUID may be an FK where the "=" searches would
> return many rows. In that case depending on how many queries against each
> column and what columns you fetch we still may consider using the GUID as
> the clustered index.
> / Tobias
>
I create a key in one table, the field is uniqueidentifier, but I search
very frequently for one [date] field, and I like to index this field with
datetime datatype to optimize the search, how can I do that?
Best regards,
Owen."Owen" <anibal@.prensa-latina.cu> wrote in message
news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello:
> I create a key in one table, the field is uniqueidentifier, but I search
> very frequently for one [date] field, and I like to index this field with
> datetime datatype to optimize the search, how can I do that?
> Best regards,
> Owen.
>
CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
Example:
CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
As a side note.. If you are frequently searching on a range of dates using
statements like BETWEEN, then you may find a CLUSTERED index on this date
column to be far more effective. Clustered indexes on GUIDs can be clumsy
at best.
Rick Sawtell
MCT, MCSD, MCDBA|||hi, thanks for answer:
the problem is that I need keep this two index on the same table, the guid
and the dates, but only one can be CLUSTERED, how can optimize this to all
index work faster?
Best regards,
Owen.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> "Owen" <anibal@.prensa-latina.cu> wrote in message
> news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
with
> CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
> Example:
> CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
>
> As a side note.. If you are frequently searching on a range of dates
using
> statements like BETWEEN, then you may find a CLUSTERED index on this date
> column to be far more effective. Clustered indexes on GUIDs can be
clumsy
> at best.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Owen as you say only one index can be clustered, but one of the significant
benefits of a clustered index is for querioes based on a range of values
since all those rows will be found near each other (all else being equal).
Now a GUID is *extremely unlikely* (probably nearly safe to say never)
likely to be used in a query like
WHERE guidcol between 'yuckyguidvalue1' and 'yuckyguidvalue2'
So makeing the CI on the date will have the benefit (if you retrieve rows
based on a date range) of being faster than retrieving the same range when
the supporting index is non-clustered. Hence Rick's suggestion that you
consider making the CI on the date column.
To go much further needs an understanding of the type of queries on the
table, and the approx size of it, and is frequently best checked by running
testst on your configuration.
You may have guessed I fall in to the camp of disliking guids for any sort
of identifiers unless there is an absolute cast-iron reason for needing them
(like a distributed db that has to have surrogate keys generated uniquely,
or complex replication) .
Mike John
"Owen" <anibal@.prensa-latina.cu> wrote in message
news:eNcVeIwEGHA.3064@.TK2MSFTNGP10.phx.gbl...
> hi, thanks for answer:
> the problem is that I need keep this two index on the same table, the guid
> and the dates, but only one can be CLUSTERED, how can optimize this to all
> index work faster?
> Best regards,
> Owen.
>
> "Rick Sawtell" <Quickening@.msn.com> wrote in message
> news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> with
> using
> clumsy
>|||Just don't forget that the GUID may be an FK where the "=" searches would
return many rows. In that case depending on how many queries against each
column and what columns you fetch we still may consider using the GUID as
the clustered index.
/ Tobias|||True - apologies - i was falling into an assumption that the guid was going
to be unique!
Mike
"Tobias Thernstrm" <ttnospam@.rbam.se> wrote in message
news:O%23qfmNCFGHA.3632@.TK2MSFTNGP10.phx.gbl...
> Just don't forget that the GUID may be an FK where the "=" searches would
> return many rows. In that case depending on how many queries against each
> column and what columns you fetch we still may consider using the GUID as
> the clustered index.
> / Tobias
>
Wednesday, March 7, 2012
Create linked server in SQL 2005 from Excel spreadsheet and have primary key?
Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?
Thanks,
--Stan
This was for a Report Builder issue that I've resolved another way, but it's still an interesting question for other uses...
|||
No, Excel has no idea of Primary Keys, using Report Builder you probably would create a logical primary key to accomplish the creation of relationships.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Saturday, February 25, 2012
Create INDEX within CREATE TABLE DDL
Hi
Minor and inconsequential but sometimes you just gotta know:
Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement.
e.g. I have:
CREATE TABLE MyT
(MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered,
MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])
which creates a table with a PK and unique constraint.
I would like (pseudo SQL):
CREATE TABLE MyT
(MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered,
MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT])
No big deal - just curious :D Once I know I can stop scouring BOL for clues.
Tks in advanceI don't think so. I don't recall seeing any syntax that allows this. Non-clustered indexes are separate objects from the table, and that is probably why they need to be created separately, and can be dropped separately as well.|||I don't think so. I don't recall seeing any syntax that allows this. Non-clustered indexes are separate objects from the table, and that is probably why they need to be created separately, and can be dropped separately as well.
Cheers BM - didn't think of it like that - that does kind of make sense - you can't create an object dependent on another object before the first object exists. Or something similar but more felicitous ;)
Minor and inconsequential but sometimes you just gotta know:
Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement.
e.g. I have:
CREATE TABLE MyT
(MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered,
MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])
which creates a table with a PK and unique constraint.
I would like (pseudo SQL):
CREATE TABLE MyT
(MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered,
MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT])
No big deal - just curious :D Once I know I can stop scouring BOL for clues.
Tks in advanceI don't think so. I don't recall seeing any syntax that allows this. Non-clustered indexes are separate objects from the table, and that is probably why they need to be created separately, and can be dropped separately as well.|||I don't think so. I don't recall seeing any syntax that allows this. Non-clustered indexes are separate objects from the table, and that is probably why they need to be created separately, and can be dropped separately as well.
Cheers BM - didn't think of it like that - that does kind of make sense - you can't create an object dependent on another object before the first object exists. Or something similar but more felicitous ;)
Create Index Causes EXCEPTION_ACCESS_VIOLATION
If we try and create an index on the topics table with domain_id as the key it errors out. The error generated is:
â'SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.â'
I have DBCC CHECKDB, CHECKALLOC and CHECKCATALOG the database. DBCC has not reported any errors.
Any help is appreciated.
Thanks
JLHi Ranjini.
Any time you see a run time dump like that it's likely you've run into a SQL
bug.
However, it may be one that's already fixed by a service pack or hot fix.
What version of sql server are you running? Execute "SELECT @.@.VERSION" so we
get a precise answer please.
If you feel that your service packs (SQL and Windows) are up to date, than
you might want to get in touch with Microsoft PSS for more help.
Regards,
Greg Linwood
SQL Server MVP
"Rajini" <anonymous@.discussions.microsoft.com> wrote in message
news:6D8397D5-ECF8-4338-9BD2-859F2758E271@.microsoft.com...
> If we try and create an index on the topics table with domain_id as the
key it errors out. The error generated is:
> "SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> I have DBCC CHECKDB, CHECKALLOC and CHECKCATALOG the database. DBCC has
not reported any errors.
> Any help is appreciated.
> Thanks
> JL
â'SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.â'
I have DBCC CHECKDB, CHECKALLOC and CHECKCATALOG the database. DBCC has not reported any errors.
Any help is appreciated.
Thanks
JLHi Ranjini.
Any time you see a run time dump like that it's likely you've run into a SQL
bug.
However, it may be one that's already fixed by a service pack or hot fix.
What version of sql server are you running? Execute "SELECT @.@.VERSION" so we
get a precise answer please.
If you feel that your service packs (SQL and Windows) are up to date, than
you might want to get in touch with Microsoft PSS for more help.
Regards,
Greg Linwood
SQL Server MVP
"Rajini" <anonymous@.discussions.microsoft.com> wrote in message
news:6D8397D5-ECF8-4338-9BD2-859F2758E271@.microsoft.com...
> If we try and create an index on the topics table with domain_id as the
key it errors out. The error generated is:
> "SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> I have DBCC CHECKDB, CHECKALLOC and CHECKCATALOG the database. DBCC has
not reported any errors.
> Any help is appreciated.
> Thanks
> JL
Friday, February 24, 2012
CREATE FTC Failing for
Trying to create a catalog as seen below:
CREATE FULLTEXT INDEX ON [dbo].[AttachFiles](
[BinFile])
KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC]
WITH CHANGE_TRACKING OFF
GO
Getting error of:
Msg 7655, Level 16, State 1, Line 1
TYPE COLUMN option must be specified with column of image or varbinary(max) type.
BinFile is an Image datatype.
What do I need to update on my CREATE statement above to make this work?
When you use an IMAGE or VARBINARY(MAX) field for a full-text index, you have to tell the service how to read the binary image. It has a set of extensions installed with which it can read these. You can see the list by executing a "SELECT * FROM sys.fulltext_document_types" query in the full-text enabled database. To tell the service what image type is stored in the field, you need another field that holds the type name.
So, let's assume Dossiers_FTC holds MS Word Document files. Let's also assume you have a field in your table named "BinType" that contains the string ".doc". Your statement would look like this:
CREATE FULLTEXT INDEX ON [dbo].[AttachFiles]
([BinFile] TYPE COLUMN [BinType]) KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC]
WITH CHANGE_TRACKING OFF
GO
Subscribe to:
Posts (Atom)