build sql script: express

Showing posts with label express. Show all posts

Thursday, March 29, 2012

Bulk Load XML file to SQL Server (Express) Table

Hi All,

I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?

Thanks,

Claude.

Here is a working sample from Microsoft. Hope this helps.

http://support.microsoft.com/default.aspx/kb/316005/en-us

|||

Thanks for your reply.

Are there any examples of doing this in asp.net 2.0 using version 2 of the .Net framework? I am currently writing my app in c#.

TIA,

Claude.

|||

The data is going into SQL Server so you have to use XML SQL Server will accept. The links below covers SQL Server 2005 XML. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

http://msdn2.microsoft.com/en-us/library/ms191184.aspx

sql

Sunday, March 11, 2012

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.

The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "\r\n" 17 AGE_KEY ""

However... actually running this gives the following error...

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

There are two different ways you can do this, I believe, and both involve a staging table.

The reason you need a staging table is that you need to import the file into char/varchar columns, else (I believe, not tested, though) that your spaces would be converted into zeroes.
Anyhow, imo a staging table is always the preferred destination of bulk loads, never directly into the production table. =:o)

Ok, you can do this with a table that has the correct number of columns, or in a table with just a single large column wide enough for the entire row.

For the first case, the column lengths should be the #chars of data + number of trailing spaces until the next column's data starting position, assuming that's how your 'fixed length' file is supposed to work. You need a format file for this to work (like the one you have), but change it so that all columns are specified as SQLCHAR instead, and the respective length includes the trailing spaces

For the second variant, the actual load is much simpler. It's really easy to bulk the file into a single row table, no formatfile is needed. Once it's loaded, you insert into a 'proper' table with the intended datatypes etc with a INSERT newTable (col1, col2...) SELECT SUBSTRING(col1, 1, n), SUBSTRING(col2, n, n)..... etc
construct. You can also do inline casting if you'd like, The point is that it's here you do the actual separation of the 'one-liner' into it's respective columns.

Hope it helps some
=;o)
/Kenneth

|||What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes. -- Thanks!!!

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

However... actually running this gives the following error...

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Any thoughts on this would be greatly appreciated...

Thanks!

This isn't really depend on Express so I'm going to punt to the SQL Engine guys. Redirecting post...

Mike

|||What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes.

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

However... actually running this gives the following error...

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Also - there was the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes. . .

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

However... actually running this gives the following error...

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Any thoughts on this would be greatly appreciated...

Thanks!

What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes. . .

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

However... actually running this gives the following error...

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Any thoughts on this would be greatly appreciated...

Thanks!

What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes. .

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

However... actually running this gives the following error...

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Any thoughts on this would be greatly appreciated...

Thanks!

What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes. .

Thursday, March 8, 2012

Bulk Insert fails

I am using SQL Server 2005 Management Studio Express.I am trying to insert records in bulk into "students" table.

Students:
uniqueid varchar(9) (primary Key)
lname varchar(35)
fname varchar(35)

My problem is when one record from the bulk record already exists in Students table all the bulk insert fails. I dont want that. I want rest of the records to be inserted smoothly with no error.

How can I do it....Is there any disadvantage if i do this?

You could bulk insert into a staging table and then do the logic to insert only non-duplicated keys. I rarely (if ever) insert directly into a "master table". Once data is in the staging table you can do a simple insert.

Insert into Students
Select uniqueid, lname, fname, ....
from staging_Students
where uniqueid not in (select uniqueid from Students)

Drop the staging table after you are done, or leave it there to determine why you are getting duplicated keys from the insert file...

|||

I am using staging table and inserting using not in condition...but i am trying to see if there is a way around...the reason is bc i think the performance of query will come down if i do again select....will the performance go down?students table will have 60000 records...

|||

You could also use Openrowset with the BULK option (see books online for details). You would have to define a format file to use for the BULK option.

Insert into Students
Select uniqueid, lname, fname, ....
From Openrowset(BULK bulk parameters) as A
Where uniqueid not in (select uniqueid from Students)

|||

Yes, the performance depends on the number of rows in the main table and the inserted rwos. But another option is to remove the primary key on the uniqueid column and create an unique index with the ignore_dup_key option ON. This will result in the duplicate rows from being ignored during BULK INSERT. This will eliminate the additional join using staging table approach. In any case, you should use the staging table if you have more complex data cleaning tasks to perform.

So do the following:

1. Drop primary key on uniqueid column

2. Add unique index like:

create unique index ix_uq_uniqueid on Students(uniqueid) with ignore_dup_key = on

3. Now, bulk insert as before and the duplicate rows will be ignored automatically.

|||

umachandar...students table can have 60000 records. At a time I can insert 300 records into that. This is my scenario...can you tell which approach is better to follow( using ignore_dup_key or use not in condition to check duplicates). Do I need staging table if i user ignore_dup_key option.? I am having clustered index on uniqueid column in students table rite now.

Scenario:

60 users will be using my application. each user can upload a .csv file with 300 records at maximum.

This is what i am doing rite now....when the user uploads a .csv file from front end the .net application will invoke sqlbulkcopy class built into .net 2.0 and does a bulk insert ot 300 records into staging table. After that i invoke a stored procedure which puts the data into 3 different tables. so my procedure will have 3 insert statements(with not in conditions). After successful insertion i delete the data from the staging table for that user. the number of users and records in students table can grow up further in furture...

or should I user the ignore_dup_key option and avoid the staging table?

Thanks...

Sunday, February 19, 2012

Bulk Copy not with MSDE ?

With the following I try to save the content of an excel-sheet to a sql table. This works perfectly with SQL Server Express but not with MSDE, which I would need also. Here the code:

String rootPath1 = Request.MapPath("~/Kontoauszug.xls");

String strConn ="Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + rootPath1 +";Extended Properties=Excel 8.0;";

OleDbDataAdapter da =newOleDbDataAdapter

("SELECT * FROM [Mappe1$]", strConn);DataTable dtCustomers =newDataTable();

da.Fill(dtCustomers);

string ConnectionString =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();using (SqlConnection destinationConnection =newSqlConnection(ConnectionString))

{

// open the connection

destinationConnection.Open();

using (SqlBulkCopy bulkCopy =newSqlBulkCopy(destinationConnection.ConnectionString))

{

bulkCopy.BatchSize = 500;

bulkCopy.BulkCopyTimeout = 90;

bulkCopy.DestinationTableName ="dbo.Auszug";

bulkCopy.WriteToServer(dtCustomers);

bulkCopy.Close();

}

With MSDE I see the following error-message

Fehler bei der Anmeldung für den Benutzer 'sa'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Fehler bei der Anmeldung für den Benutzer 'sa'.

Source Error:

Line 148: Line 149: // Write from the source to the destination.Line 150: bulkCopy.WriteToServer(dtCustomers);Line 151: Line 152:

Source File:d:\Inetpub\Www_root\XXXXXXXXXXXXXXXXXXXXXXXX.cs Line:150

Stack Trace:

[SqlException (0x80131904): Fehler bei der Anmeldung für den Benutzer 'sa'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) +64 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +42 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) +176 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) +6 admin_Default.Button4_Click(Object sender, EventArgs e) in d:\Inetpub\Www_root\netzregister.de\consumeropinion\admin\Default.aspx.cs:150 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

The connection to the database works perfect also on MSDE on all other parts of the project.

Can anyone help me wit a solution or an idea?

Perhaps I should add, that when I change the password in the web config to a wrong entry I get the same error message, but in another line:

Line 127: destinationConnection.Open();

Tuesday, February 14, 2012

Built-in data types in SQL Express: best practices?

Greetings,

I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes? Why so many? Also, what is the "text" datatype and when is it most likely to be used? There is very little about the text type that I can find in the msdn or SQL Server docs...aside from the fact that it's text. On top of all this, there's numerous binary types as well. I'm really not getting the reason behind all these different basic types and why I would want to use one over the other in any specific instance.

TIA,

Mark

Hi,

there are sveeral things to keep in mind if you choose a datatype:

-Size: In your example CHAR / NCHAR / VARCHAR /NVARCHAR. THe NTypes requires twice the storage capacity than the other types, because they are unicode ready and are able to store unicode which the others aren′t. So, if you have unicode you should take them, otherwise you can leave them out. THE VAR types are variable in size, which means that not the whole length is reserved in SQL Server at storage time if its not used yet. Sample: You have a VARCHAR(4000) and a CHAR(4000) column. They both can store up to 4000 characters, but the CHAR column will reserve the space even if you enter only 1 character. The other one will only store the one character. if additional data is inserted in that column a pointer will point to the rest of the string. So you will have to decide between storage capacity or access speed. In the numeric section you might have noticed TINYINT / SMALLINT / INT /BIGINT. If you only want to store for example state of a specific thing like "Enrolled" with the ID 1, "Approved" with the ID 2 and so on for about 10 entried int he table you would probably take TINYINT because this is capaple for 256 entries (range is 0-255). For number above 255 you will take the next appropiate type like SMALLINT with a range of 0-32768. So in conclusion you will take the data type that will fit your problem but not waste any space in your database, becausse bigger datatypes will need more space for reservation.

-Functionality: For some Datatype you will have a lack of functionality. Let me explain: VARCHAR and Text both stores strings. TEXT can store up to 2GB, so why not always take TEXT. Beside that TEXT will be deprected in further version as there is a new data type which is called VARCHAR(MAX) text can support only some string operations in SQL Server. Also the client haviour of text is different, which forces you to specially handle it within your client code. So use VARCHAR(MAX) to store text bigger than 8000 character in a row. Some datatype like the TEXT and IMAGE won′t even allow Grouping whereas VARCHAR(MAX) will allow it.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you Jens. This helps clear up some things for me. I now know how I want to proceed with my project.|||

This is the most helpful information I've come across since I started working with SQL Express last year. I've ALWAYS just selected TEXT and INT datatypes because I couldn't find anything anywhere that laid bare the functionality of SQL datatypes as well as these 2 paragraphs do.

So, are you saying the IMAGE datatype is being phased out and the VARCHAR(MAX) is a preferrable alternative? Thank gawd for that bit of info, because not even an hour ago, I embarked upon my first adventure into storing images in a datatable and had selected (of course) IMAGE because it appeared to be the ideal datatype.

Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.

I hereby nominate Jens post for inclusion in the actual SQL documentation.

If someone will second the nomination, the motion will carry, and then be ignored entirely by the author of the actual documentation (who I believe is Gavin from the HBO series Kids In The Hall - http://en.wikipedia.org/wiki/Kids_in_the_Hall#Gavin - someone might be able to refute that though.)

|||

Thanks for that

If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.

Image is sort of a misunderstanding word as you don′t actually only store images in there :-D

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?

I've assigned the Image Column's datatype to VARBINARY(MAX).

In Visual Basic Express 2005, after saving the Table Definition, I open the Table Data to enter my records. Of course, entering the typed data is no problem, but I can't copy and paste specific images into individual records.

Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.

If there is no method that can be summarized in 3 or 4 lines of text, don't even waste your time posting it....maybe a link to the tome that offers the instructions (other than the 'official' documentation)

|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||

Another quick question about the datatypes:

What exactly does the 'SQL_VARIANT' type do?

|||Actually thats pretty simple:

Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html

The method getPhoto will get the byte array which can be stored in SQL Server. You can′t copy & paste the file into SQL Server. The same for getting data back, you retrieve the data as a blob / byte array and either assign that to a control or write the stream to a file.

So actually it can be summarized to 3/4 lines of code like the following:

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

//Clean the house
br.Close();
fs.Close();

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||SQL Variant is a untyped datatype which can store every data type in SQL Server exept thise one mentioned in the BOL (like Text, Image etc.) You probably use it if you don′t know the expected type which is entered. I would not recommend using this type and always use stongly type data.

More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks alot for your time...

I guess I should clarify:

I was actually suggesting that if your reply required more than 3 or 4 sentences in actual post itself, then you needn't waste your valuable time* on my frivolous, n00b questions. If that was the case, I hoped you might could provide a hyperlink to where I could read up on it.

But here you not only replied with a concise, plethora of lines in the post itself, but you provided example code (limited to a concise 3 or 4 lines) AND you posted a link to more detailed information....

I'm not worthy....

Again, thanks alot for the help!

*Time that might be better spent working on important projects like The Jens Suessmeyer Annotated SQL Reference Guide

|||

Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!

Craig

Built-in data types in SQL Express: best practices?

Greetings,

TIA,

Mark

Hi,

there are sveeral things to keep in mind if you choose a datatype:

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you Jens. This helps clear up some things for me. I now know how I want to proceed with my project.|||

Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.

I hereby nominate Jens post for inclusion in the actual SQL documentation.

|||

Thanks for that

If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.

Image is sort of a misunderstanding word as you don′t actually only store images in there :-D

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?

I've assigned the Image Column's datatype to VARBINARY(MAX).

Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.

|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||

Another quick question about the datatypes:

What exactly does the 'SQL_VARIANT' type do?

|||Actually thats pretty simple:

Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html

So actually it can be summarized to 3/4 lines of code like the following:

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

//Clean the house
br.Close();
fs.Close();

HTH, Jens Suessmeyer.

More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks alot for your time...

I guess I should clarify:

I'm not worthy....

Again, thanks alot for the help!

*Time that might be better spent working on important projects like The Jens Suessmeyer Annotated SQL Reference Guide

|||

Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!

Craig

Built-in data types in SQL Express: best practices?

Greetings,

TIA,

Mark

Hi,

there are sveeral things to keep in mind if you choose a datatype:

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you Jens. This helps clear up some things for me. I now know how I want to proceed with my project.|||

Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.

I hereby nominate Jens post for inclusion in the actual SQL documentation.

|||

Thanks for that

If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.

Image is sort of a misunderstanding word as you don′t actually only store images in there :-D

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?

I've assigned the Image Column's datatype to VARBINARY(MAX).

Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.

|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||

Another quick question about the datatypes:

What exactly does the 'SQL_VARIANT' type do?

|||Actually thats pretty simple:

Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html

So actually it can be summarized to 3/4 lines of code like the following:

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

//Clean the house
br.Close();
fs.Close();

HTH, Jens Suessmeyer.

More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks alot for your time...

I guess I should clarify:

I'm not worthy....

Again, thanks alot for the help!

*Time that might be better spent working on important projects like The Jens Suessmeyer Annotated SQL Reference Guide

|||

Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!

Craig

Built database with SQL Server Management Studio Express, how can I quikly add test data?

I've built my SQL Server Express database with SQL Serevr Management Studio Express, and now I want to enter some seed data to assist in building tha app around it. I cannot find an option to manage the data in SQL SMSX, like I used to with Enterprise Manager.

I don't want to have to write an app just to get test data in. Seems like this should be a common need. Am I missing something obvious here? Can't find any reference to this in a search of the forums.

Please help.

Hi, did you see 'New Query' button in Management Studio? Click it, and a editor window will appear, just like in Query Analyzer.

Or you can press F8 to open 'Object Explorer' (under View menu), then you can manipulate database objects like you do in Enterprise Manager.

Thursday, March 29, 2012

Bulk Load XML file to SQL Server (Express) Table

Sunday, March 11, 2012

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

Thursday, March 8, 2012

Bulk Insert fails

Sunday, February 19, 2012

Bulk Copy not with MSDE ?

Fehler bei der Anmeldung für den Benutzer 'sa'.

Tuesday, February 14, 2012

Built-in data types in SQL Express: best practices?

Built-in data types in SQL Express: best practices?

Built-in data types in SQL Express: best practices?

Built database with SQL Server Management Studio Express, how can I quikly add test data?

build sql script

Blog Archive

About Me