How to insert NULL char values in SQLSERVER with a SQL sentence?

Hi everyone!
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have null
values.
What do i have to write in the SQL sentence to insert a null value in
those fields?
I tried with '', an empty String, but it doesnt work, the tables
stores an empty String (logical :-)).
In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
value, but how can i tell this to the SQLServer through a SQL
Sentence?

Well, thank you very much.On 29 Jul 2004 04:15:15 -0700, schumacker wrote:

>Hi everyone!
>I am working with Delphi v7 and MS SQLServer.
>I am trying to insert data in a table with a SQL sentence. Some of the
>fields of my table are type char or varchar, and they can have null
>values.
>What do i have to write in the SQL sentence to insert a null value in
>those fields?
>I tried with '', an empty String, but it doesnt work, the tables
>stores an empty String (logical :-)).
>In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
>value, but how can i tell this to the SQLServer through a SQL
>Sentence?
>Well, thank you very much.

Hi schumacker,

INSERT INTO MyTable (Col1, Col2, Col3)
VALUES (1, NULL, 3)

or

INSERT INTO MyTable (Col1, Col2, Col3)
SELECT 1, NULL, 3

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Given:

create table foo
(col1 char(1) not null,
col2 char(1) null)

you can insert nulls to col2 by either explicitly specifying a null for the
content:

insert foo (col1, col2) values ('a',null)

or by skipping it in the column list and SQL Server will automatically
insert null:

insert foo (col1) values ('b')

Check it:

select * from foo

outputs:

col1 col2
-- --
a NULL
b NULL

"schumacker" <miguelcampoy@.hotmail.com> wrote in message
news:e1eadaf3.0407290315.99cd0d2@.posting.google.co m...
> Hi everyone!
> I am working with Delphi v7 and MS SQLServer.
> I am trying to insert data in a table with a SQL sentence. Some of the
> fields of my table are type char or varchar, and they can have null
> values.
> What do i have to write in the SQL sentence to insert a null value in
> those fields?
> I tried with '', an empty String, but it doesnt work, the tables
> stores an empty String (logical :-)).
> In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
> value, but how can i tell this to the SQLServer through a SQL
> Sentence?
> Well, thank you very much.

how to insert data in to two tables?

hi,

iam creating an asp.net application the database is created on the SQLSERVER, but i have a problem, i pretend to insert data in to 2 tables and i have no ideia how to do this. Can you help me to solve this problem? Please....

you would need 2 insert statements. check out books online for syntax on INSERT. You could also use stored procedures to have all the inserts in it so you can get your job done in one trip to the server.sql

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
--
Thomas
"steve.c.thompson@.gmail.com" wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve|||why didnt u use the text datatype..any reason|||i need to be able to put a unique index on this field, text fields do
not allow that.

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.datawarehouse:21642
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Tom
You can use BCP or BULK INSERT with a format file.
In BOL, click Go, then URL, then paste this in:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom" <tom@.killspam.com> wrote in message
news:l2UGd.14507$W33.456705@.news20.bellglobal.com. ..
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Tom
>

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.datawarehouse:21642
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
TomYou can use BCP or BULK INSERT with a format file.
In BOL, click Go, then URL, then paste this in:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom" <tom@.killspam.com> wrote in message
news:l2UGd.14507$W33.456705@.news20.bellglobal.com...
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Tom
>

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:376852
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
LesThis can be done through the DTS import wizard in Enterprise Manager under
Tools --> Data Transformation Services --> Import Data.
In the wizard select the source Data Source as 'Text File'. The wizard will
guide you through the steps.
Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
command.
--
Stuart Fish
www.technologic.co.nz
"Tom" <tom@.killspam.com> wrote in message
news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
> necessary fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>|||I've tried that and it and I found that through the wizard you have to
define the length of the fields.
But I have already a table with the fields. I don't want to go through 50
fields every time I want to import data.
Is that all what we pay MS for?
Les
"Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
> This can be done through the DTS import wizard in Enterprise Manager under
> Tools --> Data Transformation Services --> Import Data.
> In the wizard select the source Data Source as 'Text File'. The wizard
> will guide you through the steps.
> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
> command.
>
> --
> --
> Stuart Fish
> www.technologic.co.nz
>
> "Tom" <tom@.killspam.com> wrote in message
> news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
>|||At the end of the DTS Import/Export wizard there are options that allow you
to save the DTS package instead of running immediately. That way the DTS
package can then be executed or scheduled as you need it. You don't need to
redefine it each time, as long as your file and table structure remain the
same.
If the package is saved to the SQL Server it can be viewed in Enterprise
Manager under the Data Transformations Services --> Local Packages folder.
"Tom" <tom@.killspam.com> wrote in message
news:sOUGd.15394$W33.466935@.news20.bellglobal.com...
> I've tried that and it and I found that through the wizard you have to
> define the length of the fields.
> But I have already a table with the fields. I don't want to go through 50
> fields every time I want to import data.
> Is that all what we pay MS for?
> Les
>
> "Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
> news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
>|||Les,
Most of people choose to loading the data from import file in to a temporary
table then doing manipulation on that temp table, this way is useful when d
uring the import progress, you could also maintain your database integrity.
to solve your problem, you could you DTS or SQL stored procedure. i was sele
cted stored procedure to manage this process.
e.g:
you have list of invoice need to be imported to invoice table every day. thi
s file is a flat fixed length file storing in following desc
Invoice#ProductCategory
xxxx-xxxAAAAAAAACCCCCCCC
length of row was 24, since Invoice# was 8, Product 8, Category 8. This file
name is invoice.txt
you could define a table as follow
INVOICE_TEMP(
ROWTMP VARCHAR(24)
)
In you stored procedure, you would have this command
BULK INSERT INVOICE_TEMP FROM invoice.txt WITH (BATCHSIZE = 10000, CODEPAGE
='RAW', ROWTERMINATOR = '\n')
Next: You could be able to insert the rows from this temp file to destinatio
n table.
INSERT INVOICE (InvoiceNo, Product, Category)
SELECT SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1
, 8) FROM INVOICE_TEMP
*done*.
Next: your stored procedure could be called any time you want, or it could b
e called from DTS and have it running in a defined schedule.
Good luck!
Doan
Message posted via http://www.droptable.com

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.tools:27686
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Les
Already answered in numerous other groups. Please refrain from
multi-posting in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom" <tom@.killspam.com> wrote in message
news:_0UGd.14493$W33.456187@.news20.bellglobal.com. ..
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:376852
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Les
This can be done through the DTS import wizard in Enterprise Manager under
Tools --> Data Transformation Services --> Import Data.
In the wizard select the source Data Source as 'Text File'. The wizard will
guide you through the steps.
Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
command.
--
Stuart Fish
www.technologic.co.nz
"Tom" <tom@.killspam.com> wrote in message
news:M0UGd.14491$W33.456289@.news20.bellglobal.com. ..
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
> necessary fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>
|||I've tried that and it and I found that through the wizard you have to
define the length of the fields.
But I have already a table with the fields. I don't want to go through 50
fields every time I want to import data.
Is that all what we pay MS for?
Les
"Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
> This can be done through the DTS import wizard in Enterprise Manager under
> Tools --> Data Transformation Services --> Import Data.
> In the wizard select the source Data Source as 'Text File'. The wizard
> will guide you through the steps.
> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
> command.
>
> --
> --
> Stuart Fish
> www.technologic.co.nz
>
> "Tom" <tom@.killspam.com> wrote in message
> news:M0UGd.14491$W33.456289@.news20.bellglobal.com. ..
>
|||At the end of the DTS Import/Export wizard there are options that allow you
to save the DTS package instead of running immediately. That way the DTS
package can then be executed or scheduled as you need it. You don't need to
redefine it each time, as long as your file and table structure remain the
same.
If the package is saved to the SQL Server it can be viewed in Enterprise
Manager under the Data Transformations Services --> Local Packages folder.
"Tom" <tom@.killspam.com> wrote in message
news:sOUGd.15394$W33.466935@.news20.bellglobal.com. ..
> I've tried that and it and I found that through the wizard you have to
> define the length of the fields.
> But I have already a table with the fields. I don't want to go through 50
> fields every time I want to import data.
> Is that all what we pay MS for?
> Les
>
> "Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
> news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
>
|||Les,
Most of people choose to loading the data from import file in to a temporary table then doing manipulation on that temp table, this way is useful when during the import progress, you could also maintain your database integrity.
to solve your problem, you could you DTS or SQL stored procedure. i was selected stored procedure to manage this process.
e.g:
you have list of invoice need to be imported to invoice table every day. this file is a flat fixed length file storing in following desc
Invoice#ProductCategory
xxxx-xxxAAAAAAAACCCCCCCC
length of row was 24, since Invoice# was 8, Product 8, Category 8. This file name is invoice.txt
you could define a table as follow
INVOICE_TEMP(
ROWTMP VARCHAR(24)
)
In you stored procedure, you would have this command
BULK INSERT INVOICE_TEMP FROM invoice.txt WITH (BATCHSIZE = 10000, CODEPAGE ='RAW', ROWTERMINATOR = '\n')
Next: You could be able to insert the rows from this temp file to destination table.
INSERT INVOICE (InvoiceNo, Product, Category)
SELECT SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8) FROM INVOICE_TEMP
*done*.
Next: your stored procedure could be called any time you want, or it could be called from DTS and have it running in a defined schedule.
Good luck!
Doan
Message posted via http://www.sqlmonster.com

How To Import Access to SQLServer with Parameter from SQLServer, Help Pls!

Hello Expert!

I have 2 Database – Access & SQLServer(ver 7)

I need to Import Data TblShift from Access to SQLServer – using DTS I’ve done this successfully!

Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)

In SQLServer, I have created table to store the date range as following:

TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006

How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?

Is this possible or any better solution for this?

TIA

Regards,

have you tried using the ole db source component? If you use a parameterized query, you can map variables to parameters to specify the values for individual parameters in the SQL statements.|||

Hi Douglas,

I newbie to sqlserver dts,

I don't know how to do as u have suggested

Can u explain step by step?

Or can u guide me to the place where I can see some sample

TIA

Regards

|||

Try doing this:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

How to implement the sqlserver ReportServices in ASP Page

Is it possible to use SQL Server Reporting Service in ASP Page? Anybody can help me.

No. It's a .net control. ASP can only work with ActiveX components, not .Net bases pages. It is possible to include an aspx rendered page inside asp, but then you would still need to work with asp.net. You can directly link to the reporting services web application.

How to implement the sqlserver ReportServices in ASP Page

Is it possible to use SQL Server Reporting Service in ASP Page? Anybody can help me.

No. It's a .net control. ASP can only work with ActiveX components, not .Net bases pages. It is possible to include an aspx rendered page inside asp, but then you would still need to work with asp.net. You can directly link to the reporting services web application.

How to implement credit card encryption

Has anyone had to encrypt the credit card numbers for storage in a SQL
Server 2000 database?
We have credit card numbers stored in several tables and these values need
to be encrypted. The values are used by many different stored procs that
need the credit card number in the clear.. I have an algorithm to encrypt
and decrypt the values but I am not sure of the best way to employ it.
I would like to implement encryption while re-writing as little code as
possible. I was hoping someone could share a solution that worked for them.
Thanks
DaveHi
Our middle tier encrypts the data before it gets stored in the DB. The
problem with encrypting it in SQL Server 2000, in the DB, is that the hacker
has access to the Stored Procedure that does the encryption/decryption if he
is in the DB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave" wrote:

> Has anyone had to encrypt the credit card numbers for storage in a SQL
> Server 2000 database?
> We have credit card numbers stored in several tables and these values need
> to be encrypted. The values are used by many different stored procs that
> need the credit card number in the clear.. I have an algorithm to encrypt
> and decrypt the values but I am not sure of the best way to employ it.
> I would like to implement encryption while re-writing as little code as
> possible. I was hoping someone could share a solution that worked for the
m.
> Thanks
> Dave
>
>|||I agree with Mike, there are plenty solutions for the middle tier (perhaps i
n
the .NET security namespace) but only a few and expensive for sql server, I
would′nt do that on the db, but on the middle tier.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Our middle tier encrypts the data before it gets stored in the DB. The
> problem with encrypting it in SQL Server 2000, in the DB, is that the hack
er
> has access to the Stored Procedure that does the encryption/decryption if
he
> is in the DB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Dave" wrote:
>|||Hi Dave,
Thanks for your post.
From your descriptions, I understood you would like to know how to encypt
data in the SQL Server. If I have misunderstood your concern, please feel
free to point it out.
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Based on my knowledge, SQL Server 2000 does not support data encryption
internal. You will have to find third party tools or build the applicaiton
to implement the algorithm yourself and use network encryption.
Check MSDN Online and KB article below for more information about network
encryption.
INF: Network Encryption Available Using the Multi-Protocol Net Library
http://support.microsoft.com/kb/132224
Net-Library Encryption
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_cs_6fu6.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys.
But I am under the impression that we must store the values in an encrypted
form. I believe it has something to do with the SOX (Sarbanes Oxley)
requirements. I will double check on this.
Are you saying that you store everything in the database as unencrypted and
then decrypt as necessary through the middle tier?
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.
de>
wrote in message news:36C7F7D0-F4EC-4299-A5FA-092A1EF7671D@.microsoft.com...[vbcol=seagreen]
>I agree with Mike, there are plenty solutions for the middle tier (perhaps
>in
> the .NET security namespace) but only a few and expensive for sql server,
> I
> wouldnt do that on the db, but on the middle tier.
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||We store it as encrypted and use the mid tier to decrypt it.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dave" wrote:

> Thanks guys.
> But I am under the impression that we must store the values in an encrypte
d
> form. I believe it has something to do with the SOX (Sarbanes Oxley)
> requirements. I will double check on this.
> Are you saying that you store everything in the database as unencrypted an
d
> then decrypt as necessary through the middle tier?
>
> "Jens Sü?meyer" <Jens@.[Remove_that][for contacting me]sqlserver2
005.de>
> wrote in message news:36C7F7D0-F4EC-4299-A5FA-092A1EF7671D@.microsoft.com..
.
>
>|||You should check with your security people, but the card number encryption i
s
maybe more due to Visa/Mastercard requirements than SarbOx.
Anyway, 2000 doesn't have the encryption internals needed (hence the middle
tier approach), but for what is required by the CISP etc, look at 2003 (the
OS) file/drive encryption options. They may be enough until Yukon/2005
becomes official (it looks like that may have the tools.)
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to know how to encypt
> data in the SQL Server. If I have misunderstood your concern, please feel
> free to point it out.
> Since this is a consultation type issue, you can contact Advisory Services
> (AS) . Microsoft Advisory Services provides short-term advice and guidance
> for problems not covered by Problem Resolution Service as well as requests
> for consultative assistance for design, development and deployment issues.
> You may call this number to get Advisory Services: (800) 936-5200.
> Based on my knowledge, SQL Server 2000 does not support data encryption
> internal. You will have to find third party tools or build the applicaiton
> to implement the algorithm yourself and use network encryption.
> Check MSDN Online and KB article below for more information about network
> encryption.
> INF: Network Encryption Available Using the Multi-Protocol Net Library
> http://support.microsoft.com/kb/132224
> Net-Library Encryption
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_[/ur
l]
> ar_cs_6fu6.asp
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>

How to implement credit card encryption

Has anyone had to encrypt the credit card numbers for storage in a SQL
Server 2000 database?
We have credit card numbers stored in several tables and these values need
to be encrypted. The values are used by many different stored procs that
need the credit card number in the clear.. I have an algorithm to encrypt
and decrypt the values but I am not sure of the best way to employ it.
I would like to implement encryption while re-writing as little code as
possible. I was hoping someone could share a solution that worked for them.
Thanks
Dave
Hi
Our middle tier encrypts the data before it gets stored in the DB. The
problem with encrypting it in SQL Server 2000, in the DB, is that the hacker
has access to the Stored Procedure that does the encryption/decryption if he
is in the DB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave" wrote:

> Has anyone had to encrypt the credit card numbers for storage in a SQL
> Server 2000 database?
> We have credit card numbers stored in several tables and these values need
> to be encrypted. The values are used by many different stored procs that
> need the credit card number in the clear.. I have an algorithm to encrypt
> and decrypt the values but I am not sure of the best way to employ it.
> I would like to implement encryption while re-writing as little code as
> possible. I was hoping someone could share a solution that worked for them.
> Thanks
> Dave
>
>
|||I agree with Mike, there are plenty solutions for the middle tier (perhaps in
the .NET security namespace) but only a few and expensive for sql server, I
would′nt do that on the db, but on the middle tier.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Our middle tier encrypts the data before it gets stored in the DB. The
> problem with encrypting it in SQL Server 2000, in the DB, is that the hacker
> has access to the Stored Procedure that does the encryption/decryption if he
> is in the DB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Dave" wrote:
|||Hi Dave,
Thanks for your post.
From your descriptions, I understood you would like to know how to encypt
data in the SQL Server. If I have misunderstood your concern, please feel
free to point it out.
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Based on my knowledge, SQL Server 2000 does not support data encryption
internal. You will have to find third party tools or build the applicaiton
to implement the algorithm yourself and use network encryption.
Check MSDN Online and KB article below for more information about network
encryption.
INF: Network Encryption Available Using the Multi-Protocol Net Library
http://support.microsoft.com/kb/132224
Net-Library Encryption
http://msdn.microsoft.com/library/de...us/architec/8_
ar_cs_6fu6.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks guys.
But I am under the impression that we must store the values in an encrypted
form. I believe it has something to do with the SOX (Sarbanes Oxley)
requirements. I will double check on this.
Are you saying that you store everything in the database as unencrypted and
then decrypt as necessary through the middle tier?
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:36C7F7D0-F4EC-4299-A5FA-092A1EF7671D@.microsoft.com...[vbcol=seagreen]
>I agree with Mike, there are plenty solutions for the middle tier (perhaps
>in
> the .NET security namespace) but only a few and expensive for sql server,
> I
> wouldnt do that on the db, but on the middle tier.
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Mike Epprecht (SQL MVP)" wrote:
|||We store it as encrypted and use the mid tier to decrypt it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Dave" wrote:

> Thanks guys.
> But I am under the impression that we must store the values in an encrypted
> form. I believe it has something to do with the SOX (Sarbanes Oxley)
> requirements. I will double check on this.
> Are you saying that you store everything in the database as unencrypted and
> then decrypt as necessary through the middle tier?
>
> "Jens Sü?meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message news:36C7F7D0-F4EC-4299-A5FA-092A1EF7671D@.microsoft.com...
>
>
|||You should check with your security people, but the card number encryption is
maybe more due to Visa/Mastercard requirements than SarbOx.
Anyway, 2000 doesn't have the encryption internals needed (hence the middle
tier approach), but for what is required by the CISP etc, look at 2003 (the
OS) file/drive encryption options. They may be enough until Yukon/2005
becomes official (it looks like that may have the tools.)
Joseph R.P. Maloney, CSP,CCP,CDP
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to know how to encypt
> data in the SQL Server. If I have misunderstood your concern, please feel
> free to point it out.
> Since this is a consultation type issue, you can contact Advisory Services
> (AS) . Microsoft Advisory Services provides short-term advice and guidance
> for problems not covered by Problem Resolution Service as well as requests
> for consultative assistance for design, development and deployment issues.
> You may call this number to get Advisory Services: (800) 936-5200.
> Based on my knowledge, SQL Server 2000 does not support data encryption
> internal. You will have to find third party tools or build the applicaiton
> to implement the algorithm yourself and use network encryption.
> Check MSDN Online and KB article below for more information about network
> encryption.
> INF: Network Encryption Available Using the Multi-Protocol Net Library
> http://support.microsoft.com/kb/132224
> Net-Library Encryption
> http://msdn.microsoft.com/library/de...us/architec/8_
> ar_cs_6fu6.asp
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>

How to Identify user defined data types

How can you tell which datatypes in a given database are user defined
(with a query, not by looking in Enterprise Manager)? This is for SQL
Server 2000.SELECT domain_name
FROM information_schema.domains

--
David Portas
SQL Server MVP
--|||Bruce (sandell@.pacbell.net) writes:
> How can you tell which datatypes in a given database are user defined
> (with a query, not by looking in Enterprise Manager)? This is for SQL
> Server 2000.

SELECT * FROM systypes WHERE xusertype > 255

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp