How to include SQL Express in Installation Package and change some option?

Hi, there,
I'm working on a windows application project which uses SQL Express as
database.
I found I can include SQL Server 2005 Express Edition in the installation
project as Prerequests,
that's cool.
But I need do more things, I want to change the "Authentication Mode" from
"Windows Authentication mode"
(which is default) to "SQL Server Authentication mode" and assign a "default
password" to user "sa", and
I want to attach a database file which will be include on the installation
disc.
So what shall I do? Write some script in the installation project?
Does SQL Express provide any program interface to do that?
It seems there is no help content for this in the MSDN and I did some search
job and got nothing.
Help needed. Thank you.
Best wishes!
I could write a chapter on this but here it is in a nutshell.
You need to use the Unattended Install tenplate.ini file along with the
LOGName and Datasource.xml.
Here is a fantastic article.
http://www.devx.com/dbzone/Article/31648
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi, there,
> I'm working on a windows application project which uses SQL Express as
> database.
> I found I can include SQL Server 2005 Express Edition in the installation
> project as Prerequests,
> that's cool.
> But I need do more things, I want to change the "Authentication Mode" from
> "Windows Authentication mode"
> (which is default) to "SQL Server Authentication mode" and assign a
> "default password" to user "sa", and
> I want to attach a database file which will be include on the installation
> disc.
> So what shall I do? Write some script in the installation project?
> Does SQL Express provide any program interface to do that?
> It seems there is no help content for this in the MSDN and I did some
> search job and got nothing.
> Help needed. Thank you.
> Best wishes!
>
|||Hmm.....
Thank you.
I have read it, and there is really something interesting.
But there is another thing I can not do with this article.
I want to open Name Pipe and TCP connection of the SQL EXPRESS during the
setup,
but there is no such setting in the template.ini file.
Any other help?
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uFytSE%235GHA.3592@.TK2MSFTNGP05.phx.gbl...
>I could write a chapter on this but here it is in a nutshell.
> You need to use the Unattended Install tenplate.ini file along with the
> LOGName and Datasource.xml.
> Here is a fantastic article.
> http://www.devx.com/dbzone/Article/31648
>
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
> news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||Philip,
All you need to do is edit the package.xml file - you can specify the
command line parameters you want to use for installing SQLExpress. The file
is located in
C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
The default setup is
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1'
You might want to change it to something like
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
DISABLENETWORKPROTOCOLS=0'
See the readme file for SQLExpress for the command line options. That will
get SQLExpress installed the way you want it. Use a custom action dll to
install your db, attach it, set up the user accounts you want, etc.
Russ Stevens
|||Ah haaaaaa!
This is right the solution I am looking for!
Powerful!
Thank you very much!
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:OkMqQxy8GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Philip,
> All you need to do is edit the package.xml file - you can specify the
> command line parameters you want to use for installing SQLExpress. The
> file is located in
> C:\Program Files\Microsoft Visual Studio
> 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
> The default setup is
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1'
> You might want to change it to something like
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
> DISABLENETWORKPROTOCOLS=0'
> See the readme file for SQLExpress for the command line options. That will
> get SQLExpress installed the way you want it. Use a custom action dll to
> install your db, attach it, set up the user accounts you want, etc.
> Russ Stevens
>

How to Improve SQL Server performance with web application

Hi all,
I am facing some difficulties with web application performance. I have
web application in front end and SQL server 2000 Enterprise Edition in
Backend. Now , some web pages in application takes very long time
while getting data from SQL server. I want to improve the speed with
which the some web pages load.
Do anyone has idea of any third-party tools that can help me with
this situation. So, that I can constantly monitor performance of
Database with Web application.
Any help will be appriated.
Thanks in Advance.
alps
Hi
"alps" wrote:

> Hi all,
> I am facing some difficulties with web application performance. I have
> web application in front end and SQL server 2000 Enterprise Edition in
> Backend. Now , some web pages in application takes very long time
> while getting data from SQL server. I want to improve the speed with
> which the some web pages load.
> Do anyone has idea of any third-party tools that can help me with
> this situation. So, that I can constantly monitor performance of
> Database with Web application.
>
> Any help will be appriated.
> Thanks in Advance.
> alps
>
SQL Server profile will be able to monitor your database, but you may want
to control when it is running. From the profile you can analyse what queries
are running slowly. This analysis can be done by hand or using a tool such as
cleartrace http://www.cleardata.biz/cleartrace/default.aspx You should also
check for queries that return large amounts of data as this can take time to
render. Techniques such as paging may help to improve performance and only
return a manageable amount of data. You can analyses your web logs to find
pages that take a long time to load, you should be able to find free tools on
the internet that help you to do this.
Perfmon will also help you monitor system resources.
John

How to Improve SQL Server performance with web application

Hi all,
I am facing some difficulties with web application performance. I have
web application in front end and SQL server 2000 Enterprise Edition in
Backend. Now , some web pages in application takes very long time
while getting data from SQL server. I want to improve the speed with
which the some web pages load.
Do anyone has idea of any third-party tools that can help me with
this situation. So, that I can constantly monitor performance of
Database with Web application.
Any help will be appriated.
Thanks in Advance.
alpsHi
"alps" wrote:
> Hi all,
> I am facing some difficulties with web application performance. I have
> web application in front end and SQL server 2000 Enterprise Edition in
> Backend. Now , some web pages in application takes very long time
> while getting data from SQL server. I want to improve the speed with
> which the some web pages load.
> Do anyone has idea of any third-party tools that can help me with
> this situation. So, that I can constantly monitor performance of
> Database with Web application.
>
> Any help will be appriated.
> Thanks in Advance.
> alps
>
SQL Server profile will be able to monitor your database, but you may want
to control when it is running. From the profile you can analyse what queries
are running slowly. This analysis can be done by hand or using a tool such as
cleartrace http://www.cleardata.biz/cleartrace/default.aspx You should also
check for queries that return large amounts of data as this can take time to
render. Techniques such as paging may help to improve performance and only
return a manageable amount of data. You can analyses your web logs to find
pages that take a long time to load, you should be able to find free tools on
the internet that help you to do this.
Perfmon will also help you monitor system resources.
John

How to Improve SQL Server performance with web application

Hi all,
I am facing some difficulties with web application performance. I have
web application in front end and SQL server 2000 Enterprise Edition in
Backend. Now , some web pages in application takes very long time
while getting data from SQL server. I want to improve the speed with
which the some web pages load.
Do anyone has idea of any third-party tools that can help me with
this situation. So, that I can constantly monitor performance of
Database with Web application.
Any help will be appriated.
Thanks in Advance.
alpsHi
"alps" wrote:

> Hi all,
> I am facing some difficulties with web application performance. I have
> web application in front end and SQL server 2000 Enterprise Edition in
> Backend. Now , some web pages in application takes very long time
> while getting data from SQL server. I want to improve the speed with
> which the some web pages load.
> Do anyone has idea of any third-party tools that can help me with
> this situation. So, that I can constantly monitor performance of
> Database with Web application.
>
> Any help will be appriated.
> Thanks in Advance.
> alps
>
SQL Server profile will be able to monitor your database, but you may want
to control when it is running. From the profile you can analyse what queries
are running slowly. This analysis can be done by hand or using a tool such a
s
cleartrace http://www.cleardata.biz/cleartrace/default.aspx You should also
check for queries that return large amounts of data as this can take time to
render. Techniques such as paging may help to improve performance and only
return a manageable amount of data. You can analyses your web logs to find
pages that take a long time to load, you should be able to find free tools o
n
the internet that help you to do this.
Perfmon will also help you monitor system resources.
John

How to import/export databases from remote server to local SQL 2005 Express

I've successfully installed SQL2005 express edition and SQL Server Management Studio Express. All seems well except there does not appear to be any way to import databases from other servers to my local server. The remote servers are running SQL Server 7.

In the Management Studio Express I can connect to the remote databases and work with them, but I can neither import them into my local machine, nor can I export from remote to local (the option doesn't seem to exist). By contrast, using the Enterprise Manager in SQL 7 there has always been the option to do this under the All Tasks menu.

If this cannot be done, is there a way to backup a remote database to my local disk and then restore it into my local DB?

Is this by design? Can anyone help with this?

Thanks in advance.

HD

First, you can always detach the databases on the old server using sp_detachdb, move the files to the new server, and attatch them using sp_attachdb. Check BOL for detailed documentation on these stored procedures.

Second, I've moved this thread to the tools forum where experts can tell you if there's a way to do this through Management Studio Express (I'm guessing not).

Paul

How to import MS Excel data into SQL Server 2005 Express Ed.

I am using SQL Server 2005 Express Edition for testing and developping my website. How can i import MS Excel Data into a SQL Server table?

The easy way?

Open your excel spreadsheet, hit control-a (Select All), control-c (Copy).

Open Management Studio, right click the table you want to "import" to, and select "Open Table". Then hit control-v (Paste).

So long as the table has the same number of columns as your spreadsheet, you're done.

|||

Thans for this solution! :) I can use that!

I also want to know what kind of other options can be used.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import data into SQL Server 2005 Mobile Edition database?

There are any import/export utilities for SQL Server 2005 Mobile Edition database? Which edition of SQL Server 2005 can do this?

Or we must use publication/subscription to transfer data to the mobile database?

Thank you for your help!

I am waiting for a solution to this .. :)|||You should be able to use DTS for this (other than using your own home-bew code for that). I have developed a set of tools (commercial) that will help you to do that. You can get them from here.|||I had very little luck with DTS. There are so many disconnects in regards to datatype conversions that it was easier to roll my own solution. I understand this is being addressed in a future SP.|||

Thank you for your help!

But I am still finding some methods which use microsoft utilities is enough to do the import/export for the SQL Server 2005 Mobile Edition database.

|||

Hello Jo?o Paulo

Can you point any link in Microsoft (or other site) website for us to create a DTS that perform this export / import information?

I don't have the resources to buy your tools then I have to create something like that.

Can you help me?

Regards
Paulo Aboim Pinto
Odivelas - Portugal

how to implement the sql server mirroring?

i have two computers with Sql server 2000 std edition(SP4) and windows 2003 std edition(SP1).

how should i do to balance the loading of sql server and sync these two servers?

The sql server mirroring means that if one server is down, then the other one will replace it?

Database Mirroring is a feature in SQL Server 2005 SP1.|||can i use sql server 2005 to mirror a database of sql server 2000 ? or, both of these two servers must run the 2005.|||both the servers must run 2005. By default db mirroring is disabled. You need to enable it using trace flag 1400

How to identify which version we have?

How do I tell if I am running SQL2K Enterprise Edition or Standard Edition? We have a 2-node cluster. Does that imply Enterprise Edition, or could Standard do that as well? Thanks for any info.

If it's SQL 2000, only Enterprise is supported in a cluster configuration...if SQL 2005, Standard is also supported.

You can run the following to find your the edition of the server you are running...just connect via Query Analyzer to the instance you want to verify and execute:

select SERVERPROPERTY('Edition')

|||Thanks, that was very helpful. We are kicking around upgrade questions. The price for 2005 Enterprise is very much higher than Standard, but because we have 2000 Enterprise we were wondering if we needed that version in 2005 flavor as well. It looks like, with just a dual-node cluster, 2005 Standard fills the functionality that 2000 Enterprise had at least for clustering. Of course we don't get online recovery, online indexing, etc, but at least we could move to SQL 2005 Standard, get similar clustering functionality to what we have now and save $50K. That would take a very big bite out of our budget.

How to identify which edition im i running?

Good day,
I would like to identify which EDITION of reporting services im running,
enterprise or standard. I know that you can check the version by going to
http://localhost/reportserver. but I want to know the edition.
thanks in advance
Oren ZipporiHi Oren,
The edition you are running is similar to the edition of SQL Server ou
are running it on.
I=2Ee., if you are running SQL Server Standard Edition, then your
Reporting Services Edition is Standard.
Kind regards,
Bj=F6rn|||Hello Oren,
You can do it programmatically like this using the webservice API:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
-Chris
> Good day,
> I would like to identify which EDITION of reporting services im
> running,
> enterprise or standard. I know that you can check the version by going
> to
> http://localhost/reportserver. but I want to know the edition.
> thanks in advance
> Oren Zippori

How to identify the Reporting Services Edition

Hi all, I need to know the reporting services, if it is Enterprise, Standard
or Developer. I found only documents that tell me how to identify the
version, but not the edition.
Anyone knows how to do this'
Thank you very much!!!
Alexandre Calderaro
Avanade Italy
MSCDBASorry,
After post the question I found the answer in the most obvious place:
Control Panel, Add Remove Programs...
Anyway
Thanks
"Alex" wrote:
> Hi all, I need to know the reporting services, if it is Enterprise, Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>|||For 2000, the version information is located at the bottom of the main page
of your Report Server
RTM: 8.00.743.00
SP1 : 8.00.878.00
SP2 : 8.00.1038.00
The edition information is stored in the following registry key:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Reporting
Services\ProductCode
Standard: {B19FEFE7-069D-4FC4-8FDF-19661EAB6CE4}
Enterprise: {33FE9EED-1976-4A51-A7AF-332D9BBB9400}
Developer: {2879CA50-1599-4F4B-B9EC-1110C1094C16}
Evaluation: {7C93251A-BFB4-4EB8-A57C-81B875BB12E4}
Med Bouchenafa
"Alex" <Alex@.discussions.microsoft.com> a écrit dans le message de news:
92C3C825-D8BB-43C5-9342-F4807D12D157@.microsoft.com...
> Hi all, I need to know the reporting services, if it is Enterprise,
> Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>