how to insert null value into database?

hi guys. i'm using vb.net with vs 2003 and MSSQL Managment Studio Express as my database server.
i want to insert null value into database, so i use System.DBNull.Value
but it doesn't show NULL, but a empty field. I tried System.DBNull.Value.ToString, it gives me same result.
what can i do to have NULL in that field?
thanks for any advise, i would appreciate it very much.

Hello,

If you use an INSERT statement and don't mention the columns you want null values in then they will contain null values for the inserted row.

--Bonnie

|||Could you please show us your query ? I am not sure, from which application you want to insert the NULL value, either through code (then we need your code to investigate your problem) or through the GUI (then you will simply have to press STRG+0 staying inside the actual data cell)

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to input and output simultaneously in SQL 2005 Express?

I want to process data of an old VS6 project in VisualStudio2005.
First part is done. With SQL Management Studio I created the database file 'dbTest4' and attached it to SQL 2005 Express server (.\SQLEXPRESS).
Then I recoded the VS6_C++ project feeding its data into the database file using the ConnectionString:
"Provider='sqloledb';Data Source='.';Initial Catalog='dbTest4';Integrated Security='SSPI')"
Until here its working fine.

Assecond part I want to catch these data with VisualStudio2005 for processing there.
In VisualStudio 2005 I created a Windows Application as new project added the database file 'dbTest4' as data source, waited two minutes till VisualStudio2005 had created the DataGridTools bound to the tabbles of my database file and dropped a grid tool on to the surface of the new Form.
Great, within 5 minutes I had created a working database solution without writing a single line of code and it worked well. Starting the new build exe as standlolone it showed the data of my database file in a nice grid view.
But it worked as standolone only. When I tried to combine both parts one part was blockedalways producing error messages as ' ..error 32 (The process cannot access the file because it is being used by another process.) while attempting to open the file '...\dbTest4'.

Does anybody know
how to get this input/output combination running
or is ist impossible to reach my target with SQL 2005 Express?

After reading through Roger Wolters November 2005 article SQL Server 2005 Express Edition User Instances and some hours of testing I came to the conclusion:
When using Visual Studio not exclusively but simultaneously with other clients as SQLcmd, SQL Managent Studio or what else, as shown in my screenshot 325 you should consider to
set 'User Instance=False'
if you are struck by connection problems.
For details you are welcome to visit my sreenshot folder. By clicking the numbered screenshots, enlarging them and using your browsers back button you can follow a detailed installation and see why, where and how to set 'User Instance=False'.
Martin

How to increase the data file size?

Hi All,

Is it possible to increase the data file size in SQL Server 2005 Express edition?

If yes then how?

Thanks,

Varun

Hi Varun

If you have MSSQL Management Studio installed then from there select Database's properties > Files

There you can setup initial maximum size for Data and Log file.

Thanks,

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 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 from Excel to SQL Server Express

Hi,
please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .

Your query is not clear.. you wish to copy data from Excel to SQL Server Express or You wish to read Excel data from ASP.Net application:

If you want to know, how to read Excel data in ASP.Net Application, visit the follocing web link:

http://weblogs.asp.net/scottgu/archive/2006/05/29/Reading_2F00_Writing-Excel-Spreadsheets-with-ADO.NET.aspx

|||

Hi,

You may try this SQL fonction OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

INSERT INTO MyTable

SELECT Column1, Column2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
 'Excel 8.0;Database=C:\MyTable.xls',
 'SELECT * FROM [Sheet1$]')

The first row of your Excel file should coutains the colums names.

 Hope this answer your question. 

Dominic 

|||

hi, exactly i would like to copy two columns of data from Excel file instead of two columns of data in sql express,thank u

How to import an mdf file into SQL Server Management Studio Express?

Hi

I had to recover my computer but before I did that I grabbed a new version of my mdf file but I don't know how to actually import it into SQL Server Management Studio Express evertime I try to open the file up it just crashes.

I rather not have to redue that whole database again. I am a noob so step by step instructions are needed.

Thanks

Open SQL Management Studio Express and log in to the server to which you want to attach the database. In the 'Object Explorer' window, right-click on the 'Databases' folder and select 'Attach...' The 'Attach Databases' window will open; inside that window click 'Add...' and then navigate to your .MDF file and click 'OK'. Click 'OK' once more to finish attaching the database and you are done. The database should be available for use.

|||

Thanks but I get an error.

|||

I think I got it working.

|||

aggiekevin:

Open SQL Management Studio Express and log in to the server to which you want to attach the database. In the 'Object Explorer' window, right-click on the 'Databases' folder and select 'Attach...' The 'Attach Databases' window will open; inside that window click 'Add...' and then navigate to your .MDF file and click 'OK'. Click 'OK' once more to finish attaching the database and you are done. The database should be available for use.

This is true. One important note I would like to menion is, it is highly recomended to take a copy of .mdf file bedore attaching it to the new database. Why? Because if this .mdf file was one of the SQL Server 2000 databases and you attached it to one of the SQL Server 2005 databases. There is no way -if you wish in futute- to detach that .mdf file from SQL Server 2005 and attached it again in SQL Server 2000 or eailer version.

Good luck.

|||

Thanks

how to import an excel table into sql server express?

Hi,
i have a table in an excel file. How can i import it into sql server
express?
Thanks
Ben
Ben
1) Linked Servers
2) OPENDATASOURCE command
INSERT INTO tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ben" <b@.bn> wrote in message
news:%23hK40fbvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have a table in an excel file. How can i import it into sql server
> express?
> Thanks
> Ben
>
>

how to import an excel table into sql server express?

Hi,
i have a table in an excel file. How can i import it into sql server
express?
Thanks
BenBen
1) Linked Servers
2) OPENDATASOURCE command
INSERT INTO tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ben" <b@.bn> wrote in message
news:%23hK40fbvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have a table in an excel file. How can i import it into sql server
> express?
> Thanks
> Ben
>
>

how to import an excel table into sql server express?

Hi,
i have a table in an excel file. How can i import it into sql server
express?
Thanks
BenBen
1) Linked Servers
2) OPENDATASOURCE command
INSERT INTO tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ben" <b@.bn> wrote in message
news:%23hK40fbvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have a table in an excel file. How can i import it into sql server
> express?
> Thanks
> Ben
>
>

How to Import access table into SQL express 2005 database?

Can anyone help me import a ms access (97) table into a sql 2005 express database? The sql database already contains other tables but I would like to add some of my older access tables to this database without having to re-enter all the data. Please help! Thanks!SQL Server Management Studio for SQL Server 2005 (or Enterprise Manager for 2000) has a lovely Data Transformation Service (DTS) package builder.

If you set up a table in your SQL database you can just map the data from access straight to the data source.

DTS package -> look it up :)|||Do you know if this DTS package is present with management studio express? Because I am running sql 2005 express? I'm not having much luck finding it, sorry I'm pretty new with databases.|||maybe this will help: http://www.microsoft.com/sql/solutions/migration/access/default.mspx|||Thanks ! I will give that a try. :)

how to import a text file in sql express 2005

I didn't find any option of IMPORT to import a TEXT FILE in sql express 2005.

Check out the OPENROWSET with the BULK option statement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Check also the BCP command if you want to do it from the commandline.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||If you are still looking for an answer, the following worked for me:

BULK INSERT music.dbo.song
FROM 'c:/songs.csv' WITH (FIELDTERMINATOR = ',',MAXERRORS=1000)
GO

of course replace the music.dbo.song with your table name and the FROM 'c:/songs.csv' with your filename|||

Thanks it works.

Can u just tell me one more thing.

if the fieldterminator is a tab then how to represent it.

and

if the text file depend upon no. of character then how to saperate it

for eg.

user name of 30 character & password of 10 character then text file contain password exactly after 30 character/spaces.

|||

how to do so.

can u explain

How to import a text database file into SQL Server Management Studio Express

I have a text file with a bunch of zip codes:

99546,"AK","Adak",162.70,55.20,.00
99551,"AK","Akiachak",161.39,60.89,.00
99552,"AK","Akiak",161.19,60.89,.00
99553,"AK","Akutan",165.78,54.14,.00

...............

I want to import this in Sql Server Express. Is it possible to do this using Management Studio Express? If not, does anyone know a script that would import the data?

Hi Banks,

the easiest way to do it is to convert your text files to .csv file. and in the first row of the csv file insert relevent column names. You can yous DTS service to populate the data by specifying the csv file as a datasource. the DTS service identifies 1st row of the csv file as column names and you can do the mapping.

Cheers,

Raj

|||

HI

A single query will import the text file content into the Table.

BULK INSERT [TableName]
FROM 'c:\ZipDetails.txt'
WITH (FIELDTERMINATOR = ',')

You can run this SQL Query directly from the Query Analyzer

|||

Both seem like good answers but didn't work for my situation. I tried the Bulk upload but my web host provider disabled that command and SQL Server Express doesn't come with DTS.

What I did instead was I ran find and replace on the text file and inserted "insert into zips values " at the beginning of each line. I then copied it into query analyzer and ran about 40,000 of the insert commands on my database and that did the trick.

How to hide triggers from SQL Server?

Hi,
I wonder whether there is a way to temporarily hide triggers from SQL Server
2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want t
o
do this is because even though I disable triggers, SQL Server will take time
s
to evaluate whether the trigger is disabled or not. Based on my own testing
,
an alter table statement can be 2 to 3 times faster without trigger comparin
g
to disabled triggers.
Thanks for any help,
PeterHi Peter
Disabling the triggers is the best you can do unless you drop and re-create
them.
Out of interest Is there alot of code in these triggers or are they nested?
John
"Peter" wrote:

> Hi,
> I wonder whether there is a way to temporarily hide triggers from SQL Serv
er
> 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want
to
> do this is because even though I disable triggers, SQL Server will take ti
mes
> to evaluate whether the trigger is disabled or not. Based on my own testi
ng,
> an alter table statement can be 2 to 3 times faster without trigger compar
ing
> to disabled triggers.
>
> Thanks for any help,
> Peter|||Hi Peter
Disabling the triggers is the best you can do unless you drop and re-create
them.
Out of interest Is there alot of code in these triggers or are they nested?
John
"Peter" wrote:

> Hi,
> I wonder whether there is a way to temporarily hide triggers from SQL Serv
er
> 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want
to
> do this is because even though I disable triggers, SQL Server will take ti
mes
> to evaluate whether the trigger is disabled or not. Based on my own testi
ng,
> an alter table statement can be 2 to 3 times faster without trigger compar
ing
> to disabled triggers.
>
> Thanks for any help,
> Peter

How to hide triggers from SQL Server?

Hi,
I wonder whether there is a way to temporarily hide triggers from SQL Server
2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want to
do this is because even though I disable triggers, SQL Server will take times
to evaluate whether the trigger is disabled or not. Based on my own testing,
an alter table statement can be 2 to 3 times faster without trigger comparing
to disabled triggers.
Thanks for any help,
PeterHi Peter
Disabling the triggers is the best you can do unless you drop and re-create
them.
Out of interest Is there alot of code in these triggers or are they nested?
John
"Peter" wrote:
> Hi,
> I wonder whether there is a way to temporarily hide triggers from SQL Server
> 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want to
> do this is because even though I disable triggers, SQL Server will take times
> to evaluate whether the trigger is disabled or not. Based on my own testing,
> an alter table statement can be 2 to 3 times faster without trigger comparing
> to disabled triggers.
>
> Thanks for any help,
> Peter