Implicit queries: March 2012

Friday, March 30, 2012

Import script

Hello All,
I need import some data from one db in one server to another db in another
server. I can do it by import/export wizard, but I need do that by script
for deployment time. Do you know how to do that?
Thanks,
HUwhich database server are you using? 2000 or 2005|||2000
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
> which database server are you using? 2000 or 2005
>|||Hello,
Vysa's have some good utility procedures to generate insert statements, its
all pretty handy.
http://vyaskn.tripod.com/code.htm#inserts
Thanks
Hari
"italic" <hugur@.hotmail.com> wrote in message
news:%23eFv013eHHA.4136@.TK2MSFTNGP02.phx.gbl...
> 2000
> "Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
> news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
>

Import script

Import schema into SqlExpress

Hi all,

I've got a PostgreSQL schema, how can I import it in SqlExpress?

What's the best tool for administering Sql Express.

Thanks,
LorenzoHi

I am not a Postgres expert, but if there is a way to generate the DDL
(CREATE TABLE scripts) you could use that and modify any syntax that is not
SQLExpress compliant. Alternatively you may be able to reverse engineer the
database with a CASE/design tool such as Visio/Erwin/ER_studio etc... and
use that.

For specific questions on SQL Express you should ask in the express
newsgroup
http://communities.microsoft.com/ne...ver2005.express

John

<lbolognini@.gmail.com> wrote in message
news:1117710946.423920.164950@.g44g2000cwa.googlegr oups.com...
> Hi all,
> I've got a PostgreSQL schema, how can I import it in SqlExpress?
> What's the best tool for administering Sql Express.
> Thanks,
> Lorenzo|||John Bell wrote:
> Hi
> I am not a Postgres expert, but if there is a way to generate the DDL

Hi John,

yeah I got that. found out there are some command line tools that
should do the job just fine, then I could mantain it allright from
Visual Web Developer

> For specific questions on SQL Express you should ask in the express
> newsgroup

Thanks, didn't know about that, I'll post there from now on

Lorenzo

import rpt file to SQL Server 2005

Hi,

I know how to import the .rpt file into Access database using import.

can some body tell about how to import to SQL Server 2005 database, both front end (using some utility or interface) and back end (directly accessing the server) solution.

Thanks,

Fahim.

hi,

I'm not sure I understood you question...

.rpt file could stand for a "report file", but SQL Server has nothing to do with client issues like reports.. so, the question is, where do you want to import those .rpt files? do you like to store them into a database table? and if this is correct, what do you like to do with that files, as SQL Server can not use them to render reports?..

a "branch" of SQL Server, available in SQLExpress edition as well, is devoted to reporting, using Reporting Services, an additional "engine" responsible for the purpose..

regards

|||

In addition to Andrewa, if you want to convert your rpt file into a report in reporting services you will have to do manual work as the queries within the Report will have to be trasnfered to SQL Server syntax. In addition, the migraiton assistant for Report wont transfer the underlying tables to SQL Server, you will have to do that on your own.
Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for your reply,

I am currently doing like this,

-- take comma delimited file.

-- convert into rpt file using Excel

-- manuall insert to Access database table.

now I want to upgrade this process to

-- SQL Server 2005 ( i know how to upgrade database to this engine)

-- Inert rpt file to SQL table now ( from front end ),

i hope my question is clear.

thanks for your help.

|||

Thanks for your reply,

I am currently doing like this,

-- take comma delimited file.

-- convert into rpt file using Excel

-- manuall insert to Access database table.

now I want to upgrade this process to

-- SQL Server 2005 ( i know how to upgrade database to this engine)

-- Insert rpt file to SQL table now ( from front end ),

i hope my question is clear.

thanks for your help.

|||

hi,

FahimatMicrosoftForum wrote:

Thanks for your reply,

I am currently doing like this,

-- take comma delimited file.

-- convert into rpt file using Excel

-- manuall insert to Access database table.

now I want to upgrade this process to

-- SQL Server 2005 ( i know how to upgrade database to this engine)

-- Insert rpt file to SQL table now ( from front end ),

if you want to insert "files" into a database table, you can perhaps bulk insert them... or you can deal the task via client code, reading the files into memory and passing the resulting byte array to the Transact-SQL command specified for the INSERT INTO operation..

i hope my question is clear.

absolutely not... at least not to me

regards

|||

FahimatMicrosoftForum wrote:

Hi,

I know how to import the .rpt file into Access database using import.

can some body tell about how to import to SQL Server 2005 database, both front end (using some utility or interface) and back end (directly accessing the server) solution.

Thanks,

Fahim.

Hi,

I also looking for this solution. I am import excel, txt, pdf file to sql2005. Are you found the solution?

|||

Search Books Online for the bcp utility, it should allow you to import various types of files.

Mike

|||

Excel and Textfiles are supported by bcp, bcp does not support imporintg pdf files as their file structure is not bulk-importable.

Jens K. Suessmeyer

http://www.sqlserver2005.de

import rpt file to SQL Server 2005

Hi,

I know how to import the .rpt file into Access database using import.

can some body tell about how to import to SQL Server 2005 database, both front end (using some utility or interface) and back end (directly accessing the server) solution.

Thanks,

Fahim.

hi,

I'm not sure I understood you question...

a "branch" of SQL Server, available in SQLExpress edition as well, is devoted to reporting, using Reporting Services, an additional "engine" responsible for the purpose..

regards

|||

http://www.sqlserver2005.de

|||

Thanks for your reply,

I am currently doing like this,

-- take comma delimited file.

-- convert into rpt file using Excel

-- manuall insert to Access database table.

now I want to upgrade this process to

-- SQL Server 2005 ( i know how to upgrade database to this engine)

-- Inert rpt file to SQL table now ( from front end ),

i hope my question is clear.

thanks for your help.

|||

Thanks for your reply,

I am currently doing like this,

-- take comma delimited file.

-- convert into rpt file using Excel

-- manuall insert to Access database table.

now I want to upgrade this process to

-- SQL Server 2005 ( i know how to upgrade database to this engine)

-- Insert rpt file to SQL table now ( from front end ),

i hope my question is clear.

thanks for your help.

|||

hi,

FahimatMicrosoftForum wrote:

Thanks for your reply,

I am currently doing like this,

-- take comma delimited file.

-- convert into rpt file using Excel

-- manuall insert to Access database table.

now I want to upgrade this process to

-- SQL Server 2005 ( i know how to upgrade database to this engine)

-- Insert rpt file to SQL table now ( from front end ),

i hope my question is clear.

absolutely not... at least not to me

regards

|||

FahimatMicrosoftForum wrote:

Hi,

I know how to import the .rpt file into Access database using import.

can some body tell about how to import to SQL Server 2005 database, both front end (using some utility or interface) and back end (directly accessing the server) solution.

Thanks,

Fahim.

Hi,

I also looking for this solution. I am import excel, txt, pdf file to sql2005. Are you found the solution?

|||

Search Books Online for the bcp utility, it should allow you to import various types of files.

Mike

|||

Excel and Textfiles are supported by bcp, bcp does not support imporintg pdf files as their file structure is not bulk-importable.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Import RIS format to SQL Server?

Hello!

I have data exported from a Reference Manager 11, and need to import it
into and SQL database.

Each record has different number of fields. It is used to cite journal
articles.
(more about the format at
http://www.adeptscience.co.uk/kb/article/A626)

The format is very strange:

TY - RPRT
A1 - Esparza,J.
T1 - Report of a WHO workshop on the measurement and significance of
neutralizing antibody to HIV and SIV, London, 3-5 October 1988
Y1 - 1990
VL - 4
SP - 269
EP - 275
RP - Not In File
CY - San Francisco CA
PB - UC Berkeley
KW - HIV
KW - SIV
KW - AIDS
T3 - World Health Organisation Global Programme on AIDS
ER -

TY - CHAP
A1 - Franks,L.M.
T1 - Preface by an AIDS Victim
Y1 - 1991
VL - 10
SP - vii
EP - viii
RP - Not In File
T2 - Cancer, HIV and AIDS.
CY - Berkeley CA
PB - Berkeley Press
KW - HIV
KW - AIDS
M1 - 1
M2 - 1
SN - 0-679-40110-5
ER -

TY - CASE
A1 - Cary,A.
A1 - Friedenrich,W.
T1 - Redman v. State of California
Y1 - 1988/10/7
VL - 201
IS - 32
SP - 220
EP - 240
RP - Not In File
CY - ATLA Law Reporter
PB - San Diego County 45th Judicial District, California
KW - AIDS
KW - litigation
KW - AIDS litigation
KW - rape
U1 - ISSN 0456-8125
N1 - Raped inmate can press case against officials for contracting
AIDS
ER -

It looks like some of the columns are separted by CR but part of the
same colum. For instance 'KW' is seen multiple times per record but
should be one field called 'KW'.

Any idea how I would import this to SQL Server with the DTS?"Tmuld" <tmuldoon@.spliced.com> wrote in message
news:1113921439.658252.91360@.g14g2000cwa.googlegro ups.com...
> Hello!
> I have data exported from a Reference Manager 11, and need to import it
> into and SQL database.
> Each record has different number of fields. It is used to cite journal
> articles.
> (more about the format at
> http://www.adeptscience.co.uk/kb/article/A626)
> The format is very strange:

<snip
> It looks like some of the columns are separted by CR but part of the
> same colum. For instance 'KW' is seen multiple times per record but
> should be one field called 'KW'.
> Any idea how I would import this to SQL Server with the DTS?

The format looks rather awkward to handle in DTS transformations, so you'll
probably need some other solution. One would be to load the data into a
staging table, then use stored procedures to clean it up and INSERT it into
the destination tables. A better option is probably to parse the data
outside the database, using C#/Perl or whatever your preferred language is,
then load it. You might be able to transform it into XML, for example, then
use OPENXML or SQLXML Bulk Load to import it:

http://www.sqlxml.org/faqs.aspx?faq=13

Whatever solution you decide on, you could still use a DTS package to manage
all the steps, whether they are stored procedures or external programs.

Simon|||I think that your best bet would be to loop through the file in a VB
application which handles the logic. Usually, files that hold data in a
"record" format have the columns going across the file and rows going
down. They also usually have a set number of columns. While this format
isn't unheard of (I've worked with similar formats in the past) most of
the standard tools for importing files (BCP, DTS, BULK INSERT) are not
designed to deal with it. BCP might be able to get around it if you had
a fixed number of columns, but the variable number of columns would
present a problem there.

If you have no skill in a programming language like VB and you don't
have access to a resource who has those skills then you could BCP the
file into a staging table with an IDENTITY column. Make sure that the
ordering of the records in the file is preserved in the rows of the
table. From there you can loop through the rows sequentially with a
cursor. This is one of those VERY rare instances where I think a cursor
is called for. As I said though, I think you're better off using VB or
something similar if you can.

By looping through the records you can create a new row in your base
table every time that you hit a new record tag in the file then you can
create rows in children tables for each repeatable column type.

Good luck,
-Tom.

import question

I have a excel comma seperated file with 2 columns.

Column A contains names and Column B contains zips.

The problem is Column A is not consistent in it's structure. Some columns only contain one name where others contain two names just by last name seperated by a space or a slash.

For example

BOWERING WILKENSON
GEERS/DOODS

I'm trying to clean the data before importing into a sql table. I want to seperate the records for column A where there are two names and move the second name to a new row. Is there a method to do this during the import? Or, within Excel prior to the import using replace? I want to find instances where there is a space or slash and move the name after the space or slash and move to a new row?

Thanks,
-D-I think in Excel you can do a text to column and put "/" or space as the delimitter|||Instead of leaning up just import the fist column in a temporary table and clean it by using substring and charindex.

Import Question

I am running an Import from an Access database into my SQL Server 2000
database.
The problem is that when I run this process it Appends the Access details to
the SQL Server when I want to replace the SQL Server data.
Can anyone tell me how to get around this. I'm pretty new to this sort of
thing.
TIAPoppy,
You can make use of DTS (SQL Server) which will have an option to "drop existing table" or "replace
existing date". For instance if you open DTS import/export wizard. choose source and destination of
which one will be access. On the next screen select "copy tables/views from source database" . on
the next screen select the required tables to transfer and click on button of "transform" you will
get relevant options here to transfer the data. The one which will be relevent to you will be
"delete rows in destination table"
- Vishal|||Thanks
"Vishal Parkar" <_vgparkar@.yahoo.co.in> wrote in message
news:#mdDhckmDHA.2200@.TK2MSFTNGP12.phx.gbl...
> Poppy,
> You can make use of DTS (SQL Server) which will have an option to "drop
existing table" or "replace
> existing date". For instance if you open DTS import/export wizard. choose
source and destination of
> which one will be access. On the next screen select "copy tables/views
from source database" . on
> the next screen select the required tables to transfer and click on button
of "transform" you will
> get relevant options here to transfer the data. The one which will be
relevent to you will be
> "delete rows in destination table"
>
> --
> - Vishal
>
>

Import queries

I am copying data from an Excel workbook/worksheet to a SQL Server 2005
data-mart database table (not previously existing) using the SQL Server 2005
Import/Export Wizard. I would like to do it using an import query that
recasts the data types, as opposed to copying the Excel data and letting the
Wizard set the data types. Below is a small sample of the code I'm trying t
o
use. I always get an error message (also below). What do I do to make this
work?
select
cast(REPORT_NAME as char(10)) as [Report_Name],
cast(SYSTEM as char(10)) as [System],
cast(PRIN as char(10)) as [Prin],
cast(AGENT as char(10)) as [Agent],
cast(BANK_NAME as char(50)) as [Bank_Name],
Report_Date as [Report_Date]
TITLE: SQL Server Import and Export Wizard
--
The statement could not be parsed.
ADDITIONAL INFORMATION:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005). (System.Data)You will get this error if you use a keyword or a reserved word as the table
or column name you are using.
Check it out with the list of keyowrds. You may want to try this link..
http://msdn.microsoft.com/library/d...getkeywords.asp
or try googling to get the latest list.
Hope this helps.|||No reserved words were column headings or table names. It still doesn't
work.
"Omnibuzz" wrote:

> You will get this error if you use a keyword or a reserved word as the tab
le
> or column name you are using.
> Check it out with the list of keyowrds. You may want to try this link..
> http://msdn.microsoft.com/library/d...getkeywords.asp
> or try googling to get the latest list.
> Hope this helps.|||Is all the data within the ranges specified.
if importing to char(10) does all data fit (only have 10 chars)?
"Mitch" wrote:
> No reserved words were column headings or table names. It still doesn't
> work.
> "Omnibuzz" wrote:
>|||Yes... I have been very careful with the cast statements.
"MattB" wrote:
> Is all the data within the ranges specified.
> if importing to char(10) does all data fit (only have 10 chars)?
> "Mitch" wrote:
>|||I discovered an easy solution to this problem. In the Import/Export Wizard
do not use a query to transfer the table. The wizard brings you to a point
where you can click on the EDIT button for the sheet or table selected. Thi
s
bring up a form that allows you to recast each column. It works beautifully
!
"Mitch" wrote:

> I am copying data from an Excel workbook/worksheet to a SQL Server 2005
> data-mart database table (not previously existing) using the SQL Server 20
05
> Import/Export Wizard. I would like to do it using an import query that
> recasts the data types, as opposed to copying the Excel data and letting t
he
> Wizard set the data types. Below is a small sample of the code I'm trying
to
> use. I always get an error message (also below). What do I do to make th
is
> work?
> select
> cast(REPORT_NAME as char(10)) as [Report_Name],
> cast(SYSTEM as char(10)) as [System],
> cast(PRIN as char(10)) as [Prin],
> cast(AGENT as char(10)) as [Agent],
> cast(BANK_NAME as char(50)) as [Bank_Name],
> Report_Date as [Report_Date]
> TITLE: SQL Server Import and Export Wizard
> --
> The statement could not be parsed.
> --
> ADDITIONAL INFORMATION:
> IErrorInfo.GetDescription failed with E_FAIL(0x80004005). (System.Data)
>

Import Puzzle

Hello -

I have three feeds from sources around the world, each coming in at a
separate time.
These feeds move into a large table (3GB) that is queried by managers.
The feeds are loaded sequentially, and then the previous day's feed
rows are deleted from the table (this is done so that the user's
application is never without data).

The issue is that the import takes a lot of time, as do the deletes.
These is hurting performance significantly. I attempted to fix the
problem by creating separate tables for each feed. I then created a
view with the original table's name and used UNION ALL's. My intention
was that as each feed came in, I'd alter the view with the new table's
name, and then truncate the older table. This met both goals of
concurrency and import/delete speed.

Unfortunately, this view seems to ignore the indexes on the underlying
tables, which devastates performance. I can't index the view, since
altering it makes the index less useful.

I'm looking for a different strategy for loading and deleting the
data, all without disruption to the applications. I'd appreciate any
suggestions...woodyb@.hotmail.com (Buck Woody) wrote in message news:<d4e6e94d.0311250951.76030a77@.posting.google.com>...
> Hello -
> I have three feeds from sources around the world, each coming in at a
> separate time.
> These feeds move into a large table (3GB) that is queried by managers.
> The feeds are loaded sequentially, and then the previous day's feed
> rows are deleted from the table (this is done so that the user's
> application is never without data).
? delete so you never be w/o data ?

> The issue is that the import takes a lot of time, as do the deletes.
> These is hurting performance significantly. I attempted to fix the
This is probably due to your managers querying the data and applying
shared read locks -- while you're trying to insert/delete.

> problem by creating separate tables for each feed. I then created a
> view with the original table's name and used UNION ALL's. My intention
> was that as each feed came in, I'd alter the view with the new table's
> name, and then truncate the older table. This met both goals of
> concurrency and import/delete speed.
Did you put NOLOCK on the view?

> Unfortunately, this view seems to ignore the indexes on the underlying
> tables, which devastates performance. I can't index the view, since
> altering it makes the index less useful.
Which version of MSSQLSERVER are you running? Only 2000 has indexed
views.

> I'm looking for a different strategy for loading and deleting the
> data, all without disruption to the applications. I'd appreciate any
> suggestions...
Ideally you would have 2 separate tables: "real data" and a nice
pretty one for your managers. But if you don't have space etc...,
make sure you put NOLOCKS or "set transaction isolation level read
uncommitted" on every stored procedure and view your managers use.

Hey don't sweat the small stuff. Everything is small stuff. Good
luck.
-- Louis|||"Buck Woody" <woodyb@.hotmail.com> wrote in message
news:d4e6e94d.0311250951.76030a77@.posting.google.c om...
> Hello -
> I have three feeds from sources around the world, each coming in at a
> separate time.
> These feeds move into a large table (3GB) that is queried by managers.
> The feeds are loaded sequentially, and then the previous day's feed
> rows are deleted from the table (this is done so that the user's
> application is never without data).
> The issue is that the import takes a lot of time, as do the deletes.
> These is hurting performance significantly. I attempted to fix the
> problem by creating separate tables for each feed. I then created a
> view with the original table's name and used UNION ALL's. My intention
> was that as each feed came in, I'd alter the view with the new table's
> name, and then truncate the older table. This met both goals of
> concurrency and import/delete speed.
> Unfortunately, this view seems to ignore the indexes on the underlying
> tables, which devastates performance. I can't index the view, since
> altering it makes the index less useful.
> I'm looking for a different strategy for loading and deleting the
> data, all without disruption to the applications. I'd appreciate any
> suggestions...

The two parts that often slow down inserts, updates and deletes are the
indexes and transaction log.

For transactions. If data protection is not required, since you can just
import the feeds again and they are useless after one day, then maybe you
could use a # temp table. The tempdb is more ram based so it works much
faster and has less transaction logs.

For indexes, why not drop the indexes before you do the import and delete
and put them back later. This is much faster than having the (b-tree)
indexes do their balancing tricks after each row is added.

If you can't drop the indexes then consider a low fill factor on the indexes
so they have plenty of space the grow without rebalancing.|||Quick thought off the top of my head.

Generate each table (x3). Create a view of these and generate a new
table from this to combine the data. Then create a final table for
your guys to work with. You can fully control the indexes that is on
the final table. You can mess about with the data in each of the three
sets. You will see the performance issues when creating the combined
table, but the users won't. The move from that into the one to be
reported on should be reasonably OK in comparison as you are simply
moving bulk data, not combining three sets and moving them at the same
time.

HTH

louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311251501.3e74e293@.posting.google.com>...
> woodyb@.hotmail.com (Buck Woody) wrote in message news:<d4e6e94d.0311250951.76030a77@.posting.google.com>...
> > Hello -
> > I have three feeds from sources around the world, each coming in at a
> > separate time.
> > These feeds move into a large table (3GB) that is queried by managers.
> > The feeds are loaded sequentially, and then the previous day's feed
> > rows are deleted from the table (this is done so that the user's
> > application is never without data).
> ? delete so you never be w/o data ?
> > The issue is that the import takes a lot of time, as do the deletes.
> > These is hurting performance significantly. I attempted to fix the
> This is probably due to your managers querying the data and applying
> shared read locks -- while you're trying to insert/delete.
> > problem by creating separate tables for each feed. I then created a
> > view with the original table's name and used UNION ALL's. My intention
> > was that as each feed came in, I'd alter the view with the new table's
> > name, and then truncate the older table. This met both goals of
> > concurrency and import/delete speed.
> Did you put NOLOCK on the view?
> > Unfortunately, this view seems to ignore the indexes on the underlying
> > tables, which devastates performance. I can't index the view, since
> > altering it makes the index less useful.
> Which version of MSSQLSERVER are you running? Only 2000 has indexed
> views.
> > I'm looking for a different strategy for loading and deleting the
> > data, all without disruption to the applications. I'd appreciate any
> > suggestions...
> Ideally you would have 2 separate tables: "real data" and a nice
> pretty one for your managers. But if you don't have space etc...,
> make sure you put NOLOCKS or "set transaction isolation level read
> uncommitted" on every stored procedure and view your managers use.
> Hey don't sweat the small stuff. Everything is small stuff. Good
> luck.
> -- Louis

Import Prs

Hey guys,

I was curious how other people create several 100 SQL objects on a
new database. Example: I have each procedure in its own text file (IE
100 .sql files), so I just check them out of source control, then run
a perl script that just dumps them into one txt file. Then I paste
that mess into Query Analyzer.

So to reiterate my question, how do other people get sql objects
into a database. Obviously I'd do an object copy if they resided in
some other database.

While my solution works, there is always a better way. Thanks for
your suggestions.

- CptI don't know Perl but I assume you could execute OSQL from your script.
Another method is to invoke a Windows FOR command in a command prompt
window to execute OSQL. For example:

CD C:\SQLScripts
FOR %v in (*.sql) DO OSQL -i "%v" -o "%v.out" -E

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"CptVorpal" <cptvorpal@.hotmail.com> wrote in message
news:76cffbe0.0310081530.6c7a034b@.posting.google.c om...
> Hey guys,
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.
> - Cpt|||cptvorpal@.hotmail.com (CptVorpal) wrote in message news:<76cffbe0.0310081530.6c7a034b@.posting.google.com>...
> Hey guys,
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.
> - Cpt

You could start by looking at using OSQL.EXE to run your .sql scripts
from the command line - it should be straightforward to wrap that in a
script of some sort which gets the list of scripts, then executes them
one by one. You can trap the output from OSQL and use it for error
handling as well.

Simon|||[posted and mailed]

CptVorpal (cptvorpal@.hotmail.com) writes:
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.

The suggestion from Dan and Simon to use OSQL is a good one. I'll
add that you can invoke OSQL for each file. This could be help to
track any errors.

For a faster execution you could look into to connect to the database
from Perl using some interface. (There is a short overview on my
web site at: http://www.algonet.se/~sommar/mssql...ternatives.html.

And if you want a ton of bells and whistles, you can look at
http://www.abaris.se/abaperls/. This is the load tool that we use
in our shop, and as the name indicates it's all Perl. You could
say that I started where you are now, and this is what I have seven
years later. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Import problem with varchar(max) field

I'm trying to import some Assessor data from a text file into a table and the field for Legal Description (column 2 in the source text file) in the table is of data type varchar(max) because some of the data goes over the 8K size. I get an error on the first row of importing that refers to column 2 (see 'Initial errors' below). I read the related post and changed the size of input column 2 to 8000 and got this error. Finally I set the size of the of input column 2 to 4000 and it ran. So I'm thinking there is a limit on the size of varchar data that can be imported. Just want to clarify what that limit is and how I might go about importing this data.

Thanks, John

Error with input column 2 set to size of 8000:

Setting Destination Connection (Error)

Messages

Error 0xc0204016: DTS.Pipeline: The "output column "Column 2" (388)" has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)

Initial errors:

Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Column 2" (18)" failed because truncation occurred, and the truncation row disposition on "output column "Column 2" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "\\Scux00\assrdumps\SQLServerDB\exportsql.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - exportsql_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

There is a limit on the size of unicode character strings at 4000. Non-unicode character strings have a limit of 8000. Ensure that you don't have type conversions between varchar and nvarchar.|||

I'm trying the SQL Server Import and Export Wizard. I've set the DataType to most of the fields as 'string[DT_STR]' and the Unicode box is unchecked, but when I look at column mappings after clicking 'Edit Mappings', all the columns say they're type nvarchar on the screen and it doesn't appear to be something I can change. If I highlight a column on that screen it shows the setting that I set in the Advanced screen for the text file, ie .. Column 2 string[DT_STR](4000). Since I'm getting the limit of 4k it must be because of this. I'm not sure how to modify it. I'll save the package as a file and open it up in BID Studio ... john

Import Performance file Run Command

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Dan
you could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>

Import Performance file Run Command

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Danyou could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>

Import Performance file Run Command

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Danyou could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx
.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx
.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>

Import Performance file into SQL Server table

Import Password Protected File Via DTS

I am trying to import a password-protected Excel file into SQL Server
using DTS. I am getting an error that it can't decrypt file. Does
anyone know how I can pass the password to the file during the DTS
execution. Please help.

Thanks,
MichelleThe Excel connection object has a Password property - you can set it
using a dynamic properties task or an ActiveX task.

Simon

Import partial Sql table from Excel spreadsheet

I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql.

Does anyone have any suggestion what I did wrong or what is the right way of doing this?

Thanks a lot.

Mia

Try the thread below for all you need, post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1442470.aspx

|||Thank you. I will take a look at the posts/articles and let you know.

Import PARADOX Problems

Hello,

I've the following problem. I've to read out the data of a custom
application. I think (I'm not sure) this application is using a
Paradox DB to store it's information. I don't know it exactly cause
I'm not familar with paradox at all, but I've found files like *.db,
*.mb, *.px, *.xg*, *.yg* !!!
I need the infomation from this files to combine it with other data
stored in SQL 2000. I'm tried DTS to import the Data but I've got an
"Microsoft JET Database" Error telling me that the table could not be
found or that the table has not the asumed format. It depends on which
*.db File i try to open. Description: Error calling the OPENROWSET
method on the provider.

My Questions, are there some special drivers i need, not included in
MS MDAC Components or must I use a complete Databaseserver in the
Backgroud hosting the DB Files. My developer station has the custom
application NOT installed. The db files seems to have no password set.
I've got the same error when using MS Access to import the data. I
only set the path to the dictionary where the files are in but leave
username and password blank.

HOW TO GET THIS DATA OUT ?

Thank you

TonyTony,

Before wasting a lot more time, get a copy of Paradox and see if
you can open these files. If not, they probably aren't Paradox files.
The only evidence you give that these are unprotected Paradox files
is that they have Paradox extensions and "seem to have no password
set", whatever that means.

The evidence that they are not unprotected Paradox files seems
stronger - dts doesn't understand them.

I've used the Import/Export Data wizard with the Microsoft Driver
for Paradox successfully, but if you still think these may be Paradox
files, see if Paradox agrees with you.

-- Steve Kass
-- Drew University
-- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104

TonyMontana wrote:
> Hello,
> I've the following problem. I've to read out the data of a custom
> application. I think (I'm not sure) this application is using a
> Paradox DB to store it's information. I don't know it exactly cause
> I'm not familar with paradox at all, but I've found files like *.db,
> *.mb, *.px, *.xg*, *.yg* !!!
> I need the infomation from this files to combine it with other data
> stored in SQL 2000. I'm tried DTS to import the Data but I've got an
> "Microsoft JET Database" Error telling me that the table could not be
> found or that the table has not the asumed format. It depends on which
> *.db File i try to open. Description: Error calling the OPENROWSET
> method on the provider.
> My Questions, are there some special drivers i need, not included in
> MS MDAC Components or must I use a complete Databaseserver in the
> Backgroud hosting the DB Files. My developer station has the custom
> application NOT installed. The db files seems to have no password set.
> I've got the same error when using MS Access to import the data. I
> only set the path to the dictionary where the files are in but leave
> username and password blank.
> HOW TO GET THIS DATA OUT ?
> Thank you
> Tony|||Hello Steve,

thanks for your help. I've found a bde installation and now i can
access the tables via the SQL Tool for BDE by Stefan Bodingh www.bodingh.se
so I can assume that the data is not encrypted and not currupt.
Nevertheless i can't
access them via ODBC or the MS PAradox driver. I've tried it on 2 machines.
Is the a command line tool out there which i can use to export the tables to
text [csv] Files? Pherhaps a special one for bde without so much
incompability as the ODBC connection seems to have.

regards

Tony

"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:yGt5b.27082$Om1.16634@.newsread2.news.atl.eart hlink.net...
> Tony,
> Before wasting a lot more time, get a copy of Paradox and see if
> you can open these files. If not, they probably aren't Paradox files.
> The only evidence you give that these are unprotected Paradox files
> is that they have Paradox extensions and "seem to have no password
> set", whatever that means.
> The evidence that they are not unprotected Paradox files seems
> stronger - dts doesn't understand them.
> I've used the Import/Export Data wizard with the Microsoft Driver
> for Paradox successfully, but if you still think these may be Paradox
> files, see if Paradox agrees with you.
> -- Steve Kass
> -- Drew University
> -- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104
> TonyMontana wrote:
> > Hello,
> > I've the following problem. I've to read out the data of a custom
> > application. I think (I'm not sure) this application is using a
> > Paradox DB to store it's information. I don't know it exactly cause
> > I'm not familar with paradox at all, but I've found files like *.db,
> > *.mb, *.px, *.xg*, *.yg* !!!
> > I need the infomation from this files to combine it with other data
> > stored in SQL 2000. I'm tried DTS to import the Data but I've got an
> > "Microsoft JET Database" Error telling me that the table could not be
> > found or that the table has not the asumed format. It depends on which
> > *.db File i try to open. Description: Error calling the OPENROWSET
> > method on the provider.
> > My Questions, are there some special drivers i need, not included in
> > MS MDAC Components or must I use a complete Databaseserver in the
> > Backgroud hosting the DB Files. My developer station has the custom
> > application NOT installed. The db files seems to have no password set.
> > I've got the same error when using MS Access to import the data. I
> > only set the path to the dictionary where the files are in but leave
> > username and password blank.
> > HOW TO GET THIS DATA OUT ?
> > Thank you
> > Tony|||Tony,

I think you might find some people who know in a Paradox
newsgroup. I'm not sure why the MS driver doesn't work.
It only lists Paradox versions up to 5.0, though, so perhaps
can't handle recent versions.

Tony.Montana wrote:
> Hello Steve,
> thanks for your help. I've found a bde installation and now i can
> access the tables via the SQL Tool for BDE by Stefan Bodingh www.bodingh.se
> so I can assume that the data is not encrypted and not currupt.
> Nevertheless i can't
> access them via ODBC or the MS PAradox driver. I've tried it on 2 machines.
> Is the a command line tool out there which i can use to export the tables to
> text [csv] Files? Pherhaps a special one for bde without so much
> incompability as the ODBC connection seems to have.
> regards
> Tony
> "Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
> news:yGt5b.27082$Om1.16634@.newsread2.news.atl.eart hlink.net...
>>Tony,
>>
>> Before wasting a lot more time, get a copy of Paradox and see if
>>you can open these files. If not, they probably aren't Paradox files.
>> The only evidence you give that these are unprotected Paradox files
>>is that they have Paradox extensions and "seem to have no password
>>set", whatever that means.
>>
>> The evidence that they are not unprotected Paradox files seems
>>stronger - dts doesn't understand them.
>>
>> I've used the Import/Export Data wizard with the Microsoft Driver
>>for Paradox successfully, but if you still think these may be Paradox
>>files, see if Paradox agrees with you.
>>
>>-- Steve Kass
>>-- Drew University
>>-- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104
>>
>>TonyMontana wrote:
>>
>>>Hello,
>>>
>>>I've the following problem. I've to read out the data of a custom
>>>application. I think (I'm not sure) this application is using a
>>>Paradox DB to store it's information. I don't know it exactly cause
>>>I'm not familar with paradox at all, but I've found files like *.db,
>>>*.mb, *.px, *.xg*, *.yg* !!!
>>>I need the infomation from this files to combine it with other data
>>>stored in SQL 2000. I'm tried DTS to import the Data but I've got an
>>>"Microsoft JET Database" Error telling me that the table could not be
>>>found or that the table has not the asumed format. It depends on which
>>>*.db File i try to open. Description: Error calling the OPENROWSET
>>>method on the provider.
>>>
>>>My Questions, are there some special drivers i need, not included in
>>>MS MDAC Components or must I use a complete Databaseserver in the
>>>Backgroud hosting the DB Files. My developer station has the custom
>>>application NOT installed. The db files seems to have no password set.
>>>I've got the same error when using MS Access to import the data. I
>>>only set the path to the dictionary where the files are in but leave
>>>username and password blank.
>>>
>>>HOW TO GET THIS DATA OUT ?
>>>
>>>Thank you
>>>
>>>Tony
>>|||Steve,

I am almost sure the Microsoft stuff read only to what are called LEVEL 5
databases (basically Paradox Version 5 and below). There was Paradox 7, 9,
and 10. Which I think are LEVEL 7 databases. Probably what happend was
that the developer put some field types that are only supported in LEVEL 7
and up.

Oscar...
"Steve Kass" <skass@.drew.edu> wrote in message
news:mpN5b.30725$Om1.4521@.newsread2.news.atl.earth link.net...
> Tony,
> I think you might find some people who know in a Paradox
> newsgroup. I'm not sure why the MS driver doesn't work.
> It only lists Paradox versions up to 5.0, though, so perhaps
> can't handle recent versions.
> SK
> Tony.Montana wrote:
> > Hello Steve,
> > thanks for your help. I've found a bde installation and now i can
> > access the tables via the SQL Tool for BDE by Stefan Bodingh
www.bodingh.se
> > so I can assume that the data is not encrypted and not currupt.
> > Nevertheless i can't
> > access them via ODBC or the MS PAradox driver. I've tried it on 2
machines.
> > Is the a command line tool out there which i can use to export the
tables to
> > text [csv] Files? Pherhaps a special one for bde without so much
> > incompability as the ODBC connection seems to have.
> > regards
> > Tony
> > "Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
> > news:yGt5b.27082$Om1.16634@.newsread2.news.atl.eart hlink.net...
> >>Tony,
> >>
> >> Before wasting a lot more time, get a copy of Paradox and see if
> >>you can open these files. If not, they probably aren't Paradox files.
> >> The only evidence you give that these are unprotected Paradox files
> >>is that they have Paradox extensions and "seem to have no password
> >>set", whatever that means.
> >>
> >> The evidence that they are not unprotected Paradox files seems
> >>stronger - dts doesn't understand them.
> >>
> >> I've used the Import/Export Data wizard with the Microsoft Driver
> >>for Paradox successfully, but if you still think these may be Paradox
> >>files, see if Paradox agrees with you.
> >>
> >>-- Steve Kass
> >>-- Drew University
> >>-- Ref: 5F4FF6F4-815B-4EE8-AE48-5C556CF24104
> >>
> >>TonyMontana wrote:
> >>
> >>>Hello,
> >>>
> >>>I've the following problem. I've to read out the data of a custom
> >>>application. I think (I'm not sure) this application is using a
> >>>Paradox DB to store it's information. I don't know it exactly cause
> >>>I'm not familar with paradox at all, but I've found files like *.db,
> >>>*.mb, *.px, *.xg*, *.yg* !!!
> >>>I need the infomation from this files to combine it with other data
> >>>stored in SQL 2000. I'm tried DTS to import the Data but I've got an
> >>>"Microsoft JET Database" Error telling me that the table could not be
> >>>found or that the table has not the asumed format. It depends on which
> >>>*.db File i try to open. Description: Error calling the OPENROWSET
> >>>method on the provider.
> >>>
> >>>My Questions, are there some special drivers i need, not included in
> >>>MS MDAC Components or must I use a complete Databaseserver in the
> >>>Backgroud hosting the DB Files. My developer station has the custom
> >>>application NOT installed. The db files seems to have no password set.
> >>>I've got the same error when using MS Access to import the data. I
> >>>only set the path to the dictionary where the files are in but leave
> >>>username and password blank.
> >>>
> >>>HOW TO GET THIS DATA OUT ?
> >>>
> >>>Thank you
> >>>
> >>>Tony
> >sql

Import Paradox 8 DB into SQL Server 2000 using DTS

Hi,

I'm trying to import a Paradox 8 db into SQL Server 2000 using DTS but the list of data sources in the connections dialog only caters for version 5.x or older. Does anybody know how to get the data source for v8?

If not, is there any other quick and efficient way to import a paradox 8 db as I have to import around 15 of them to do.

ThanksHave you tried the highest version available to see if it works ?|||You can also purchase odbc software from datadirect:

link (http://www.datadirect-technologies.com/products/odbc/matrix/connectodbc.htm)|||Cheers for your help. I've tried the highest version, but it doesn't work. I've since discovered that you can save Paradox 8 tables as v5 and then import them. It's time consuming though as it appears you can only save them as v5 table by table, not the whole database as it would work with something like MS Access.|||No problem. Either spend the time (and money) to find a solution or purchase the driver - normally it is cheaper to purchase the driver, however, to convince those above you may be more difficult.

Import Outlook/ACT etc contacts to SQL Server table

Guys
Does anyone know of a 3rd party module/plug-in for VS or even some
downloadable code that enables the import of Outlook/ACT and similar contact
information through an ASP.NET page into a SQL Server table with ongoing
1-click button sync.

I need to replicate something similar to the Intellisync tool used in Yahoo
Mail.

regards
AndyOutlook OWA is implemented with RPC(Remote Procedure Call), ANSI SQL has been on RPC since 1969 so if you look into .NET System .NET which deals with Networking and Sockets you can write the code. In C# The complete reference has a chapter on System .NET. I would also run a search on MSDN for OWA Web implementation sample code. SQL Server Ports are TCP 1433 and UDP port 1434. Hope this helps.

Kind regards,
Gift Peddie|||Andy,
I need to do the same thing. Maybe we can team up on something?

ScAndal|||Similar thread:http://forums.asp.net/thread/44602.aspx

Import Oracle Tables in SQL 2L

Hi all,
I am trying to import Oracle tables+data into SQL Server. I can get the data
+ tables but not the relationships and keys. Any easy way to do a complete
import (So I can dump oracle )
Thanks
RohitTo be able to dump Oracle, you'll have to do a complete migration. Most of
the times, this is a manual process. But I am aware of a company that
automated it: http://www.dbbest.com
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rohit Sharma" <lt_rohit_sharma@.hotmail.com> wrote in message
news:OziHQaD3DHA.1392@.TK2MSFTNGP11.phx.gbl...
Hi all,
I am trying to import Oracle tables+data into SQL Server. I can get the data
+ tables but not the relationships and keys. Any easy way to do a complete
import (So I can dump oracle )
Thanks
Rohit

Import Oracle Tables in SQL 2L

Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.
Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools
sql

Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools

Import or add Active Directory users into SQL2000 ?

Is there any way to import or add Active Directory users into a SQL2000
Database ?
Hi
You can query active directory through ADSI, but I think you would be better
off creating a windows group and add the users to that group as part of your
user creation process.
John
"tptools" wrote:

> Is there any way to import or add Active Directory users into a SQL2000
> Database ?

Import or add Active Directory users into SQL2000 ?

Is there any way possible to import or add Active Directory users into a
SQL2000 database ?
maybe Something like this would help u
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
go
sp_addlinkedSrvlogin 'ADSI',false, 'sa','USERNAME','PASSWORD'
go
select * from openquery(ADSI,'select GUID from
''LDAP://192.168.33.11:1003/o=e-enterprise/ou=members'' ')
"tptools" <tptools@.discussions.microsoft.com> wrote in message
news:4524D5FA-0115-4707-8674-9463C36E672C@.microsoft.com...
> Is there any way possible to import or add Active Directory users into a
> SQL2000 database ?

Import or add Active Directory users into SQL2000 ?

Is there any way possible to import or add Active Directory users into a
SQL2000 database ?maybe Something like this would help u
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
go
sp_addlinkedSrvlogin 'ADSI',false, 'sa','USERNAME','PASSWORD'
go
select * from openquery(ADSI,'select GUID from
''LDAP://192.168.33.11:1003/o=e-enterprise/ou=members'' ')
"tptools" <tptools@.discussions.microsoft.com> wrote in message
news:4524D5FA-0115-4707-8674-9463C36E672C@.microsoft.com...
> Is there any way possible to import or add Active Directory users into a
> SQL2000 database ?

Import or add Active Directory users into SQL2000 ?

Is there any way to import or add Active Directory users into a SQL2000
Database ?Hi
You can query active directory through ADSI, but I think you would be better
off creating a windows group and add the users to that group as part of your
user creation process.
John
"tptools" wrote:

> Is there any way to import or add Active Directory users into a SQL2000
> Database ?sql

Import or add Active Directory users into SQL2000 ?

Is there any way possible to import or add Active Directory users into a
SQL2000 database ?maybe Something like this would help u
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
go
sp_addlinkedSrvlogin 'ADSI',false, 'sa','USERNAME','PASSWORD'
go
select * from openquery(ADSI,'select GUID from
''LDAP://192.168.33.11:1003/o=e-enterprise/ou=members'' ')
"tptools" <tptools@.discussions.microsoft.com> wrote in message
news:4524D5FA-0115-4707-8674-9463C36E672C@.microsoft.com...
> Is there any way possible to import or add Active Directory users into a
> SQL2000 database ?

Import or add Active Directory users into SQL2000 ?

Is there any way to import or add Active Directory users into a SQL2000
Database ?Hi
You can query active directory through ADSI, but I think you would be better
off creating a windows group and add the users to that group as part of your
user creation process.
John
"tptools" wrote:
> Is there any way to import or add Active Directory users into a SQL2000
> Database ?

import OpenSSL certificate with private key into sql server 2005 express edtion

hey,

i have a problem, to import a self signed openssl certificate into the sql server 2005.

my final idea is to get encrypted columns from the database over an jdbc connection in a java client.

when i use a certificate generated by the sql server 2005, i can encryt columns of a table. then i catch the

the result in my java client. but in java, i need a keystore with the private key of the certificate.

ok. i have export the the certificate and the private key of the sql server 2005.

problem: in a keystore i can only import the certificate (signed public key) but not the private key.

my new idea is to import an openssl certificate or an certificate generated by the keytool (java) into the

sql server 2005 and encypt the data with the imported certificate. Problem: The SQL Server give me an

Exception: (Sorry i drag & drop the exception, is written in german)

Msg 15208, Level 16, State 1, Line 2

Die Datei für das Zertifikat, den asymmetrischen Schlüssel oder den privaten Schlüssel ist nicht vorhanden oder weist ein ungültiges Format auf.

My Import Statement is:

CREATE CERTIFICATE InsuranceCertOpenSSL

FROM

FILE = 'E:\master\keys\insuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630\insuranceservice_tomcat_x509_certificate_with_openssl.crt'

WITH PRIVATE KEY (

FILE = 'E:\master\keys\insuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630\insuranceservice_tomcat_x509_certificate_with_openssl_private.key',

DECRYPTION BY PASSWORD = 'testit2_',

ENCRYPTION BY PASSWORD = 'testit2_'

)

I use password encryption, and not the internal master key (or service master key)

Hope for help :)

nils

Hi Nils,

Could you try creating a certificate in keytool with a private key that is not encrypted? Then when you create it in SQL Server, you can omit the "DECRYPTION BY PASSWORD..." line.

It may simply be that the decryption of the certificate is failing.

Sung

|||

HI Sung,

thank you for answering.

i create a test certificate with private key in openssl and with keytool too.

i bring the certificate an the private key outside the sql server 2005 in the DER Format, because i read

that the certificate in SQL Server use this format.

OK.

i can import the certificate from openssl. (without the WITH PRIVATE KEY option).

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\CertSqlServer2005InsuranceDatabaseOpenSSL.crt'

it work. but when i encrypt and decrypt somthing the sql server have no private key.

SELECT Cert_ID('InsuranceCertOenSSL')

DECLARE @.NAMEENC NVARCHAR(100)
DECLARE @.PWD NVARCHAR(100)
SELECT @.PWD = 'testit2_'
SELECT @.NAMEENC = EncryptByCert(Cert_ID('InsuranceCertOpenSSL'),N'O12345678901234MMM5678XXX901234567890 O12345678901234567')
SELECT @.NAMEENC as encode
SELECT CAST(DecryptByCert(Cert_ID('InsuranceCertOpenSSL'),@.NAMEENC, @.PWD) AS NVARCHAR(MAX)) as decode
GO

Result is null.

OK:

When i import the certificate with an private key.

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'
WITH PRIVATE KEY (
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key',
DECRYPTION BY PASSWORD = 'testit2_',
ENCRYPTION BY PASSWORD = 'testit2_'
)

it doesn' t work.

my problem is that the sql server 2005 enrcypt the private key by export. also the sql server 2005 import only a encrypted private key.

when the key is encrypted i can not convert into PEM. (with openssl)

My destination is to have encrpted data in my java application. and there i will decrypt.

two ways...

1.) import an expernal generated certificate and private key. this must be imported into the sql server. PROBLEM: can not import the private key (must by encrypted, i think so)

2.) create an certificate in sql server 2005 with private key. export both. (DER-Format). To convert the certificate into PEM is no problem. but to convert the private key into pem, openssl give me an exception:

$ openssl rsa -inform DER -in CertSqlServer2005InsuranceDatabase_private.key -o
ut CertSqlServer2005InsuranceDatabase_private_PEM.key -outform PEM
unable to load Private Key
6944:error:0D07207B:asn1 encoding routines:ASN1_get_object:header too long:asn1_
lib.c:150:

I will map this to import the certificate and the key into an keystore and opened in java.

3.) a new idea: encrpt by a symmetric key. but these keys can not be exported from the database. (but recreate in the database)

in the moment i try to create a equivalent symmetric key with openssl .

i don't know the best way. but i need one way. please help

thank you

nils

|||

I looked through the keytool documentation and it does not seem to support the private key file format that SQL Server expects.

How exactly did you create the private key files that you were trying to import in SQL Server?

You may want to use another tool to generate your certificate. Have you tried using makecert to generate the certificates?

Thanks
Laurentiu

|||

Hi Nils,

[Edit - see Laurentiu's comment above]

We do recommend using symmetric keys for encryption as the performance gain is significant, but you are right that they currently cannot be exported.

Do you have to do decryption on both the application and the database? It kind of sounds like you want the database to be able to encrypt and then your application will handle the decryption? If this is the case, you can possibly get around this by storing the full certificate (public/private key pair) with the application and storing only the public key of the ceriticate on the database. You should not need the private key to encrypt data, you only need the private key to decrypt.

Hope this helps,

Sung

|||

Hi Sung,

thanks for your help.

yes you are right. i create an application with web services an database connects. in the application will have the chance to distribute my "moduls". i know i can you ssl. in some parts i use it. but to connect to an database i dont't know to connect to the database over ssl. ok, i can programm a Listener-Thread on the database site, but for many connection i need a connection pool. this is not what i want.

you are right, i can create an external certificate/private key and use it to encrpt and decrypt outside the database. and the database encrypt only data. (i must think about, for a solution that i realy need the decryption in the database. i can encrypt only data-column, and not the searching-keys, and so on.)

soloution 1 (your):

create external certificate with openssl (for example) and encrypt on in the database with the imported certificate (without the private key).

solution 2:

create a symmetirc key in the application and the same in the database:

2.a.) in the database

CREATE SYMMETRIC KEY InsuranceSymDESKey
WITH
ALGORITHM = DES,
KEY_SOURCE = 'testit2_',
IDENTITY_VALUE = 'id_insurance_service_center'
ENCRYPTION BY CERTIFICATE InsuranceCert
go

2.b.) with openssl

openssl des -out insurance_sql_server_symmetric_key_openssl.key - ....

sorry i don't know the right syntax, to generated the same key. is this possible?

nils

|||

HI Cristofor,

>> I looked through the keytool documentation and it does not seem to support the private key file format that SQL Server expects.

The exported SQL Server Certificates in DER-Format. With

openssl x509 -in CertSqlServer2005InsuranceDatabase.crt -inform DER -out CertSqlServer2005InsuranceDatabase_PEM.crt -outform PEM
you can transform the certificate. Is that realy a differnet format for the primary key or is the exported key from the sql server only additional encrypted? In the sql server documentation is only reported that the key is encrypted, but no information about the algorithm or so.

>> How exactly did you create the private key files that you were trying to import in SQL Server?

1.) generate key

$ openssl genrsa -out insuranceservice_tomcat_x509_certificate_with_openssl_private.key -des3 2048

2.) create request

$ openssl req -new -in insuranceservice_tomcat_x509_certificate_with_openssl_private.key -out insuranceservice_tomcat_x509_certificate_with_openssl.csr

3.) create certificate

$ openssl x509 -in insuranceservice_tomcat_x509_certificate_with_openssl.csr -out insuranceservice_tomcat_x509_certificate_with_openssl.crt -req -signkey insuranceservice_tomcat_x509_certificate_with_openssl_private.key -days 365

4.) map private key from PEM into DER

$ openssl rsa -inform PEM -in insuranceservice_tomcat_x509_certificate_with_openssl_private.key -outform DER -out insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key -des3

5.) map crt from PEM into DER

$ openssl x509 -in insuranceservice_tomcat_x509_certificate_with_openssl.crt -inform PEM -out insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt -outform DER

IMPORT into the sql server:

1.) (works, but without the private key)

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'

2.) doesn't work

When i remove the decryption, it doesn't work also.

>> You may want to use another tool to generate your certificate. Have you tried using makecert to generate the certificates?

i don't know the tool. but often the tools using in the background the openssl. You think this tool make another format the the sql server can understand (only the private key is needed).

i don't know the best way. but i will only send encrypted data over the network.

On which site i create the certificate (openssl or sql server) an then import and use each other is not imported.

also is not imported which tool i use to create the certificate/private key. i use all what give me an solution.

The problems are:

1.) import an certificate and the private key into the sql server 2005, generates by another software like the sql server 2005

2.) use the exported certificate and the private key from a sql server 2005.

In an microsoft document (http://www.microsoft.com/germany/technet/itsolutions/msit/security/sqldatsec.mspx) (german) i found in the last paragraph an information, that only certificates and private key can exchange between two or more sql server 2005. is this right?

Nils

|||

Hey Nils,

In regards to the connection problem, I will have to research that further.

I was trying to figure out a good solution for your second approach and I ran across two problems:

1) SQL Server and OpenSSL are probably generating the keys differently, therefore the keys created in 2a and 2b probably won't work with each other

2) I was talking about this with Raul and he mentioned that beyond that, the encrypted data is stored with extra headers and other info so it's not easy to extract the raw cipher text.

I'm not sure how to get around these two issues. The other alternative is to use JCE to handle encryption and decryption completely externally?

Sung

|||

It seems to me like you are trying to protect the data in transit, not only at rest. I strongly recommend against using the secret storage features in SQL Server 2005 for data in transit protection, instead, I would recommend using SSL between you server and client. You can find more information on this topic in MSDN (Encrypting Connections to SQL Server http://msdn2.microsoft.com/en-us/library/ms189067.aspx).

If my assumption is incorrect and/or you still need to be able to encrypt data in both your client application and SQL Server your best option would be to use certificates generated by makecert as Laurentiu mentioned.

Unfortunately the OpenSSL format for private keys and PVK are not compatible, but you can generate the certificate (public CER file and private key in PVK) using makecert, then to use it on OpenSSL you can use pvk2pfx tool to generate a PFX, as far as I remember, OpenSSL should be able to use DER encoded PFX files.

For the second solution you mentioned, creating the same symmetric key outside SQL Server. While in theory it is possible (KEY_SOURCE uses CAPI CryptDeriveKey to generate a 3DES key), you will face with the SQL Server specific headers that are not properly part of the ciphertext as well as the encrypted header once you are able to decrypt the data. I would strongly recommend against this approach.

I also include a link to a different discussion in the forum with a similar scenario (using an asymmetric key in a client), but in this case the client was using .NET code; while not exactly your scenario, it may be useful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information was useful,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Nils,

makecert is a Microsoft certificate creation utility. It can generate certificates in the format that SQL Server 2005 understands. You can convert those certificates to other formats, such as PFX, using other tools (see http://www.kinook.com/blog/?p=10). I think openssl should know how to read PFX files, but I amy not familiar with openssl or keytool. My suggestion is to generate a certificate using makecert in the format understood by SQL Server, then convert it to PFX and use it with your application.

Thanks
Laurentiu

PS: My name is Laurentiu, Cristofor is my last name.

|||

Thanks to all for help. (and sorry for the mistake with your name laurentiu.)

a.) i use the pvktool to change the header of the certificates. transform the private key.

pvk -in CertSqlServer2005InsuranceDatabase_PVK_private.key -out CertSqlServer2005InsuranceDatabase_PEM_private.key

b.) transform the certificate

openssl x509 -in CertSqlServer2005InsuranceDatabase_DER.crt -inform DER -out CertSqlServer2005InsuranceDatabase_PEM.crt -outform PEM

c.) produce a keystore (pkcs12 format)

openssl pkcs12 -export -in CertSqlServer2005InsuranceDatabase_PEM.crt -inkey CertSqlServer2005InsuranceDatabase_PEM_private.key -out insuranceservice_sql_server_keystore_PKCS12.jks -name insurance_sql_server_cert

d) from java it is easer to work with jks-format keystore

set ksFileIn=insuranceservice_sql_server_keystore_PKCS12.jks
set ksFileOut=insuranceservice_sql_server_keystore_JKS.jks
set keyAlias=insurance_sql_server_cert
set ksPass=testit2_

java testclient.CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore %ksFileIn% %ksFileOut% %keyAlias% %ksPass%

e.) for this i write a small java programm (testclient.CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore)

package testclient;

import java.io.*;
import java.util.*;
import java.security.UnrecoverableKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.KeyStoreException;
import java.security.KeyStore;
import java.security.KeyPair;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.security.Key;
import java.security.cert.Certificate;
import java.security.spec.*;

public class CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore {
public static void main(String[] args) {
try {
System.out.println("Usage: CreateSQLServerKeyStore <pkcs12_keystore_in> <jks_keystore_out>"
+ "<key alias> <keystore password>");

System.out.print("\nstart");

if (args.length != 4) {
System.exit(-1);
}

String ksFileIn = args[0];
String ksFileOutJava = args[1];
String keyAlias = args[2];
char[] ksPass = args[3].toCharArray();

KeyStore ksJKS = KeyStore.getInstance("JKS");
ksJKS.load(null, ksPass);

KeyStore ksPKCS12 = KeyStore.getInstance("PKCS12");
java.io.FileInputStream fis = new FileInputStream(ksFileIn);
ksPKCS12.load(fis, ksPass);

java.security.Key key = null;
java.security.PublicKey publicKey = null;
java.security.cert.Certificate cert = null;
java.security.cert.Certificate[] certChain = new java.security.cert.Certificate[1];

key = ksPKCS12.getKey(keyAlias, ksPass);
if (key instanceof java.security.PrivateKey) {
cert = ksPKCS12.getCertificate(keyAlias);
certChain = ksPKCS12.getCertificateChain(keyAlias);
publicKey = cert.getPublicKey();
}

ksJKS.setKeyEntry(keyAlias, key, ksPass, certChain);

FileOutputStream fos = new FileOutputStream(ksFileOutJava);
ksJKS.store(fos, ksPass);
fos.close();

System.out.print("\nend");
} catch (Exception e) {
System.out.print(e);
}
}
}

e.) for testing (it works also) (makecert)

makecert -r -cy end -sky exchange -n "CN=Nils" -b 01/01/2006 -e 01/01/2011 -a sha1 -sv CertSqlServer2005InsuranceDatabase_makecert_x509_private_key.pvk CertSqlServer2005InsuranceDatabase_makecert_x509.cer

f.) after this you can import the certificate and the private key into the sql server 2005. in java you can also open the

private key and the certificate from the keystore.

...

FileInputStream fis = null;
KeyStore ks = null;
Key key = null;
PrivateKey privateKey = null;
PublicKey publicKey = null;
Certificate cert = null;
Cipher cipher = null;
String encryptedstring = null;
String decryptedstring = null;
byte[] encryptedtext = null;
byte[] decryptedtext = null;

fis = new FileInputStream(dbKeyStore);
ks = KeyStore.getInstance(dbKeyStoreTyp);
ks.load(fis, dbKeyStorePass);
key = ks.getKey(dbKeyStoreAlias, dbKeyStorePass);
if (key instanceof PrivateKey) {
privateKey = (PrivateKey) key;
cert = ks.getCertificate(dbKeyStoreAlias);
publicKey = cert.getPublicKey();
}

Security.addProvider(new BouncyCastleProvider());
cipher = Cipher.getInstance("RSA/1/NoPadding", "BC");
...

Now i can encrypt an decrpyt. BUT i have another problem.

In the sql server 2005 the DecryptByCert and the EnrcyptByCert Method give as the result the datatype varbinary(128).

ok. But the SQL Server doesn't give me the data. for example i write some sql statements.

drop table dbo.TestSec
go

CREATE TABLE dbo.TestSec
( MandantenVerbindungID nvarchar(5) NOT NULL,
MandantenBezeichnungKurz1 varbinary(128) NOT NULL
);
GO

INSERT into dbo.TestSec (MandantenVerbindungID,MandantenBezeichnungKurz1)
values ('NBIT1', EncryptByCert(Cert_ID('InsuranceCert'), N'NBIT1'))
GO

--(1. First SELECT STATEMENT)

SELECT MandantenVerbindungID,
DecryptByCert(Cert_ID('InsuranceCert'), MandantenBezeichnungKurz1, N'testit2_')
FROM dbo.TestSec

Result:

-- 1.Column: NBIT1
-- 2.Column: 0x4E004200490054003100

--(2. Second SELECT STATEMENT)

Select MandantenVerbindungID, MandantenBezeichnungKurz1 from dbo.TestSec

-- 1.Column: NBIT1
-- 2.Column: 0x6FF1AD39946A3C92E16E035446F66930852CA086BECEE05FE1AD95DD578A8281169DF696E3528580B35C9CCEB7AB
5DBBEE4C7F9FB34F75460A2A0DECAA146FD8F8CF9A258D49C2B1A8012918609F9C4CDF53A1439CEEFF21CCE2076DE
166BB123AD2788D2B8BE3BD1F4E967FFEFC84D839C485596CC8556641387D891224914

--(3. Third SELECT STATEMENT) (ONLY for Test)
Select MandantenVerbindungID, convert(varbinary(128),MandantenBezeichnungKurz1) from dbo.TestSec

When i select the data with the first select statement i get the needed HEX-Values to decrypt. But when i make a

normal select (2. statement) i get 128 Byte Hex Code. But i need the result of the first statement.

OK: i can write the same statement in java an start. but then goes the password in plaintext across the network. Me idea was to get the encrypted data.

now i have the chance to encrypt but i have no data :)))))) sorry i must smile.

nils

|||

You cannot retrieve the cleartext data without decrypting it, so statement (1) will get the cleartext data, while statement (2) will get the ciphertext - that is to be expected. If you do not want to specify a password in statement (1), then you should create the SQL Server certificate such that it is encrypted by the database master key (that is the behavior if you don't specify a password during the creation of the certificate) - then you won't need to specify any password to use it for decryption.

Thanks
Laurentiu

|||

hi Laurentiu,

you are right. in this moment i see it. but i am unhappy. i have on both site (sql server and java) the keys and the certificate. i print out the key store, and have a look at it. i think i have the same certificate. but it doesn't work.

i test it and test it ... nothing.

i get the enrypted data from the ResultSet with getString (0x...) or by getBytes(). i convert the data into UTF-8 and so on. no result.

now i think i must change my way and create an ssl connection. but i have test it in the sql server configuration manager. change the protocol to required encryption, but i can not select an certificate.

then i start the mmc and import an certificate for the current user (admin). no result.

have you any idea to import the certificate. and after this must i connect from my programm an sslSocket and tunnel through this my jdbc connect? or on which way it works. (i have an other example found, where an ssl client and ssl server is realized and only bethween this is the ssl conncet. then the ssl server connect to the sql server with a normal jdbc connect.. is this right?

thanks for all ideas

nils

|||

Wait a second, the results you were getting before were correct, yo did succeed to decrypt. Maybe you're getting confused because the result of the decryption is binary - you need to add a convert call to cast the binary to nvarchar: 0x4E004200490054003100 is NBIT1. Try executing:

select convert(nvarchar(20), 0x4E004200490054003100)

Or do you mean that you cannot decrypt on the Java application side?

For the SSL questions, you should ask on the SQL Server Data Access Forum.

Thanks
Laurentiu

|||

Encryption/Decryption using SQL Server 2005 key management performing cryptography operations in .NET

Thought this might help. I wasn't able to find documentation on this anywhere.

But I came up with a way to do this.

--SETUP--

makecert -r -pe -n "CN=crossmatch.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -sky exchange -sy 12 -sv {PRIVATE KEY}.pvk {CERTIFICATE}.cer
cert2spc {CERTIFICATE}.cer {CERTIFICATE}.spc
pvk2pfx -pvk {PRIVATE KEY}.pvk -pi {PASSWORD - create at this point} -spc {CERTIFICATE}.spc -pfx {CERTIFICATE}.pfx

CREATE CERTIFICATE {CERTIFICATE_NAME}
FROM FILE = '{CERTIFICATE}.cer'
WITH PRIVATE KEY (FILE = '{PRIVATE KEY}.pvk',
DECRYPTION BY PASSWORD = '{PASSWORD}');

CLR

public class SqlCryptography
{
string m_FileName;
string m_PKeyPassword;
X509Certificate2 m_Certificate;
RSACryptoServiceProvider m_Encryptor;
RSACryptoServiceProvider m_Decryptor;

public string FileName
{
get{ return m_FileName; }
set{ m_FileName = value; }
}

public string PKeyPassword
{
get{ return m_PKeyPassword; }
set{ m_PKeyPassword = value; }
}

X509Certificate2 Certificate
{
get{

if( m_Certificate == null )
m_Certificate = new X509Certificate2( m_FileName, m_PKeyPassword );
return m_Certificate;
}
}

RSACryptoServiceProvider Encryptor
{
get{
if( m_Encryptor == null )
m_Encryptor = ( RSACryptoServiceProvider ) Certificate.PublicKey.Key;
return m_Encryptor;
}
}

RSACryptoServiceProvider Decryptor
{
get{
if( m_Decryptor == null )
m_Decryptor = ( RSACryptoServiceProvider ) Certificate.PrivateKey;
return m_Decryptor;
}
}

public SqlCryptography( ) : this( string.Empty, null )
{

}

public SqlCryptography( string certificateFileName, string password )
{
this.m_FileName = certificateFileName;
this.m_PKeyPassword = password;
}

public byte[] Encrypt( byte[] data )
{
return Encryptor.Encrypt( data, false );
}

public byte[] Decrypt( byte[] encryptedData )
{
return Decryptor.Decrypt( encryptedData, false );
}
}

IMPLEMENT

MAXIMUM BYTE[] LENGTH IS 117

crypt.SqlCryptography crypto = new crypt.SqlCryptography( @."C:\Users\Matt\CrossMatch.Security.pfx", "crossmatch" );
Encoding.Default.GetString( crypto.Decrypt( crypto.Encrypt( Encoding.Default.GetBytes( "TEST" ) ) ) );
Encoding.Default.GetString( crypto.Decrypt( crypto.Encrypt( Encoding.Default.GetBytes( "TEST TEST" ) ) ) );

Note: Importing the pfx into a certificate store you will lose the private key on the cert.

Friday, March 30, 2012

Import script

Import script

Import schema into SqlExpress

import rpt file to SQL Server 2005

import rpt file to SQL Server 2005

Import RIS format to SQL Server?

Import question with imbedded tags

import question

Import Question

Import queries

Import Puzzle

Import Prs

Import problem with varchar(max) field

Import Performance file Run Command

Import Performance file Run Command

Import Performance file Run Command

Import Performance file into SQL Server table

Import Performance file into SQL Server table

Import Performance file into SQL Server table

Import Password Protected File Via DTS

Import partial Sql table from Excel spreadsheet

Import PARADOX Problems

Import Paradox 8 DB into SQL Server 2000 using DTS

Import Outlook/ACT etc contacts to SQL Server table

Import Oracle Tables in SQL 2L

Import Oracle Tables in SQL 2L

Import Oracle CLOB to SQL Server

Import Oracle CLOB to SQL Server

Import or add Active Directory users into SQL2000 ?

Import or add Active Directory users into SQL2000 ?

Import or add Active Directory users into SQL2000 ?

Import or add Active Directory users into SQL2000 ?

Import or add Active Directory users into SQL2000 ?

Import or add Active Directory users into SQL2000 ?

import OpenSSL certificate with private key into sql server 2005 express edtion

a.) i use the pvktool to change the header of the certificates. transform the private key.

Implicit queries

Blog Archive

About Me