build sql script: number

Showing posts with label number. Show all posts

Tuesday, March 27, 2012

Bulk insertion

Hi,

I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.

You can make use of SQL BulkCopy feature of ADO.NET. Load your XML into a DataSet/DataTable and run SQL Bulk copy into your table. Very few lines of code.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
foreach (string tableName in tableNames)
{
SqlBulkCopy(dataSet.Tables[tableName], tableName, bulkCopy);
}
}

private static void SqlBulkCopy(DataTable dataTable, string tableName, SqlBulkCopy bulkCopy)
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);
}

|||

Thanks for the reply Raghu. I don't need all the data elements in the XML files, only a selected few. This is an example XML file:

I only need CIF/Input and RTS/INPUT from this particular file. There will be one of this file for each day of the month and I will have to process a month's worth of xml files. There are tens of different xml files but they all follow the same schema.

Thanks.

Tuesday, March 20, 2012

Bulk insert problem, any ideas?

Hello, i am trying to get this to work, i made a SP that send internalmessages to x number of users, the users is located in a variable called @.To, they are seperated by commas.

INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, w, a)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

the function iter_charlist_to_table takes the usernames inside of @.To and returns a table of usernames, i then want to insert a record for each of these users.

When i try to run this:

EXEC SendInternalMessageToUsers
@.From= N'nouser',
@.To= N'Dirk,piffo,Steve',
@.Subject= N'Test',
@.Message= N'This is to test message'

I get the following result:

Msg 512, Level 16, State 1, Procedure LaberMail_SendInternalMessageToUsers, Line 36

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

any ideas?

|||

Well, the error was returned from a select statement (a subquery in one to be precise), but you did not show us the code for the query.

My idea is to show us the select statement... :)

|||

The SP contains, one insert, one update and one select that returns the results back to my program, the insert statement is solved, that one works and inserts the correct values when i remove the update and select. The same message appears for both the update statement and the select statement.

-- Works
INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, weight, adminmessage)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

-- Not working
UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

-- Not working
SELECT profile_publicinfo.username, profile_publicinfo.emailFROM profile_publicinfoINNERJOIN settings_settingsON(settings_settings.username= profile_publicinfo.username)WHERE(profile_publicinfo.username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))AND(settings_settings.post_newmailemail= 1)

these statements calls this function:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERFUNCTION [dbo].[iter_charlist_to_table]
(@.listntext,
@.delimiternchar(1)= N',')
RETURNS @.tblTABLE(listposintIDENTITY(1, 1)NOTNULL,
strvarchar(4000),
nstrnvarchar(2000))AS
BEGIN
DECLARE @.posint,
@.textposint,
@.chunklensmallint,
@.tmpstrnvarchar(4000),
@.leftovernvarchar(4000),
@.tmpvalnvarchar(4000)
SET @.textpos= 1
SET @.leftover=''
WHILE @.textpos<=datalength(@.list)/ 2
BEGIN
SET @.chunklen= 4000-datalength(@.leftover)/ 2
SET @.tmpstr= @.leftover+substring(@.list, @.textpos, @.chunklen)
SET @.textpos= @.textpos+ @.chunklen
SET @.pos=charindex(@.delimiter, @.tmpstr)
WHILE @.pos> 0
BEGIN
SET @.tmpval=ltrim(rtrim(left(@.tmpstr, @.pos- 1)))
INSERT @.tbl(str, nstr)VALUES(@.tmpval, @.tmpval)
SET @.tmpstr=substring(@.tmpstr, @.pos+ 1,len(@.tmpstr))
SET @.pos=charindex(@.delimiter, @.tmpstr)
END
SET @.leftover= @.tmpstr
END
INSERT @.tbl(str, nstr)VALUES(ltrim(rtrim(@.leftover)),ltrim(rtrim(@.leftover)))
RETURN

@.From, @.Subject, @.Message, @.To are sent as parameters to the program, the @.To contains the usernames seperated by commas, ex. "John,Steve,Andrew,Patrick,"
(It is always a extra comma after the last username in the @.To parameter)

Patrick

|||

You are doing a basic no-no in this statement:

UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

When you state that username must = the result of a subquery (that's the select s.nstr etc. is), the subquery can only return 1 row. If it returns more than one row, how would sql server know which one you meant?

I think you may want to change to

...WHERE (username IN (SELECT ...

The IN operator works of a list of items, which can be hard-coded or supplied via query. (I work in several flavors of sql databases and my test database is down at the moment, so I can't double check the syntax.)

|||

You have the same problem in the select statement. I don't have time to work thru that one, but I'm wondering why you just don't join the table function results instead of doing a subquery. It will run faster and be easier to understand.

|||

How do join that function table into the select statement?

I solved the problem, with the IN instead of =, like you said, it worked for both the select and the update

Monday, March 19, 2012

Bulk insert of bit values

Hi Gang:
I've inherited a database that contains a number of tables with bit value
columns. I am trying to write a script with bulk insert statements to
reconstruct the database. When I export the table data, the bit fields get
converted to string equivalents (True and False.) When the bulk insert
statement for the same table executes, I get errors saying that these values
can't be converted to bits.
Can you tell me how to export the data from my tables into text files so
that the bit values can be re-imported / bulk inserted correctly?Hi Robert,
Should be 1,0 or NULL
In what format did you export the data, if you export them to excel its
getting converted to True or False...
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||Hi
Valid BIT values are 0, 1 and NULL.
True and False are not supported.
VB.NET, VB, VBScript regard True as -1, so that is not convertible. Most
other languages regard True as +1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||I exported it from SQL Server using the import / export wizard. I exported
it as a comma delimited text file. Are there other export options I need to
check to make this work?
"Jens Sü?meyer" wrote:

> Hi Robert,
> Should be 1,0 or NULL
> In what format did you export the data, if you export them to excel its
> getting converted to True or False...
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
> schrieb im Newsbeitrag
> news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
>
>|||Hi
I don't have any issue with the following:
CREATE TABLE [MyBits] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Bit1] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit1] DEFAULT (0),
[Bit2] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit2] DEFAULT (0),
[Bit3] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit3] DEFAULT (1),
[Bit4] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit4] DEFAULT (1),
CONSTRAINT [PK_MyBits] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
)
GO
INSERT INTO MYBits ( Bit1 )
SELECT 1
UNION ALL SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 0
SELECT * FROM MyBits ORDER BY ID
/*
Id Bit1 Bit2 Bit3 Bit4
-- -- -- -- --
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
(6 row(s) affected)
*/
-- Create new table
SELECT * INTO MyBits2 FROM MyBits WHERE 1 = 0
-- Command Prompt
-- bcp test..mybits2 IN mybits.txt -c -T
/* MyBits.txt
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
*/
-- At command prompt import
-- bcp test..mybits2 IN mybits.txt -c -T
SELECT * FROM MyBits2 ORDER BY ID
/* Output
Id Bit1 Bit2 Bit3 Bit4
-- -- -- -- --
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
(6 row(s) affected)
*/
Can you post DDL, example data and your statements
http://www.aspfaq.com/etiquett_e.asp?id=5006
John
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||If it wont work for you, you can cast the values to int, thatll work.
Jens Suessmeyer.
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:A1EF625E-F6AA-4C3F-AD0F-BFCF8CA98375@.microsoft.com...
> I exported it from SQL Server using the import / export wizard. I
> exported
> it as a comma delimited text file. Are there other export options I need
> to
> check to make this work?
> "Jens Smeyer" wrote:
>|||Hi
It seems that this may be feature of the text file driver! As Jens says you
can cast as an int and it will work. To do this choose the option to specify
a query as the source of your export and then put in a statement like:
SELECT [Id],
CAST([Bit1] AS INT) AS [Bit1],
CAST([Bit2] AS INT) AS [Bit2],
CAST([Bit3] AS INT) AS [Bit3],
CAST([Bit4] AS INT) AS [Bit4]
FROM [MyBits]
Alternatively use BCP as in my other post.
John
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:A1EF625E-F6AA-4C3F-AD0F-BFCF8CA98375@.microsoft.com...
> I exported it from SQL Server using the import / export wizard. I
> exported
> it as a comma delimited text file. Are there other export options I need
> to
> check to make this work?
> "Jens Smeyer" wrote:
>|||Robert,
Ignoring why you have the strings True and False in your text
file, you can import your data into a staging table that matches
your ultimate destination except for the bit column, which in the
staging table should be varchar(5). Then once the 'True' and 'False'
strings are in the staging table,
insert into Destination
select
nonBitcolA,
nonBitcolB,
cast(case when preBit1 = 'True' then 1 when 'False' then 0 end as bit)
as BitCol1,
..
from Staging
Anything from your text file that is not 'True' or 'False' in the bit
columns will import as NULL. You can check for that either in
the staging table or the destination table with
select * from Staging
where preBit1 not in ('True','False')
or preBit2 not in ('True','False')
...
or
select * from Destination
where BitCol1 is null
or BitCol1 is null
...
Steve Kass
Drew University
Robert Burdick [eMVP] wrote:

>Hi Gang:
>I've inherited a database that contains a number of tables with bit value
>columns. I am trying to write a script with bulk insert statements to
>reconstruct the database. When I export the table data, the bit fields get
>converted to string equivalents (True and False.) When the bulk insert
>statement for the same table executes, I get errors saying that these value
s
>can't be converted to bits.
>Can you tell me how to export the data from my tables into text files so
>that the bit values can be re-imported / bulk inserted correctly?
>
>|||Thanks, I've almost got it. SQL Server Books Online doesn't explain how to
tell BCP to comma dellimit the fields. Any ideas?
"Steve Kass" wrote:

> Robert,
> Ignoring why you have the strings True and False in your text
> file, you can import your data into a staging table that matches
> your ultimate destination except for the bit column, which in the
> staging table should be varchar(5). Then once the 'True' and 'False'
> strings are in the staging table,
> insert into Destination
> select
> nonBitcolA,
> nonBitcolB,
> cast(case when preBit1 = 'True' then 1 when 'False' then 0 end as bit)
> as BitCol1,
> ...
> from Staging
> Anything from your text file that is not 'True' or 'False' in the bit
> columns will import as NULL. You can check for that either in
> the staging table or the destination table with
> select * from Staging
> where preBit1 not in ('True','False')
> or preBit2 not in ('True','False')
> ...
> or
> select * from Destination
> where BitCol1 is null
> or BitCol1 is null
> ...
> Steve Kass
> Drew University
>
> Robert Burdick [eMVP] wrote:
>
>|||>> I've inherited a database that contains a number of tables with bit
value columns. <<
The right answer is to re-design the database from an assembly language
model of data to an RDBMS and get rid of the proprietary BIT data.
reconstruct the database. When I export the table data, the bit fields
[sic] get converted to string equivalents (True and False.) <<
Why do you think that (0,1) mapping to (FALSE, TRUE) is a correct model
for casting BITs. Some host languages use (0,-1) and other use (1,0).
And there are no BOOLEANs in SQL-92 for very good reasons that have
to do with 3VL, NULLs and the data model in SQL.
The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job. Maybe you
will be lucky, too.

Wednesday, March 7, 2012

Bulk Insert and Data Aggregation

I manage a legacy system that dumps it's data into a number of different
databases (same schema) on a nightly basis using bulk insert. I need to
formulate a strategy for efficiently aggregating that data into a single
database right after these nightly extractions complete. Here is my current
stategy:
1. Duplicate the legacy system's database schema and add an identifier column
to specify which database the data loaded from.
2. Each night, delete all records in the table.
3. Each night, for each database:
3a. Set each table's default value to a value that references the current
database being loaded.
3b. Use the legacy system's flat files and format files to bulk insert into
the database.
3c. Clear the default value.
What other steps would faciliate performance? Dropping and recreating the
indexes? Does anyone forsee faults in this strategy?
Thanks,
Matt
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Hi
You could add a view with your extra "column" or use a query to export from
each database and then you don't have to worry about the defaults as the
value is in the flat files. This would mean that you could load in parrallel
although having separate tables for each database may allow you to reduce
contention when doing this. If you had separate tables then you could use a
partitioned view to "join" them all, this view could identify the source if
necessary.
John
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7f0a31b9b1f67@.uwe...
>I manage a legacy system that dumps it's data into a number of different
> databases (same schema) on a nightly basis using bulk insert. I need to
> formulate a strategy for efficiently aggregating that data into a single
> database right after these nightly extractions complete. Here is my
> current
> stategy:
> 1. Duplicate the legacy system's database schema and add an identifier
> column
> to specify which database the data loaded from.
> 2. Each night, delete all records in the table.
> 3. Each night, for each database:
> 3a. Set each table's default value to a value that references the current
> database being loaded.
> 3b. Use the legacy system's flat files and format files to bulk insert
> into
> the database.
> 3c. Clear the default value.
>
> What other steps would faciliate performance? Dropping and recreating the
> indexes? Does anyone forsee faults in this strategy?
> Thanks,
> Matt
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1
>

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

Friday, February 10, 2012

build random phone number.

I'm building a test database and I need to randomly create phone numbers in the format xxxxxxxxxx

I have 5000 contacts and need to generate fake phone numbers for them. anyone know how I can write an update query to do this?

ScAndalThe way I can think to do this is:

1. create a View that will return a random number (this is needed because the RAND() function cannot be called from another function (the one we are creating in step 2 below))


CREATE VIEW dbo.vRandNumber
AS
SELECT RAND() AS RandomNumber

2. write a GenerateRandomNumber UDF:


CREATE FUNCTION GenerateRandomNumber(@.Min int, @.Max int)
RETURNS float
AS
BEGIN
 RETURN @.Min + (select RandomNumber from vRandNumber) * (@.Max-@.Min)
END

3. write a GenerateRandomPhoneNumber UDF:


CREATE FUNCTION dbo.GenerateRandomPhoneNumber ()
RETURNS varchar(10) AS
BEGIN DECLARE @.myNumber int, @.myPhoneNumber varchar(9), @.X smallint
 SELECT @.X = 0, @.myPhoneNumber =''
 WHILE @.X < 10
BEGIN
SELECT @.myNumber=dbo.generaterandomnumber(0,9)
SELECT @.myPhoneNumber = @.myPhoneNumber + CAST(@.myNumber AS CHAR(1))
SELECT @.X = @.X + 1
END
 RETURN @.myPhoneNumber
END

4. populate the fake phone numbers like this:


UPDATE contacts SET PhoneNumber = GenerateRandomPhoneNumber()

This is just my first thought. Someone else might come along and do it in 2 steps ;-)
Terri|||I can do it in 1 step! Assuming that table has a identity column you can use it as a seed for the rand() function:


update Contact
 set PhoneNumber = cast(cast(rand(ContactID * 12345)*90000 as int) + 10000 as varchar)
 + cast(cast(rand(ContactID * 54321)*90000 as int) + 10000 as varchar)

I had to do 5 digits at a time to avoid the int size problem, and I had to multiply the identity column with a largeish number to make the phone numbers look random.

For some reason
rand(X) - rand(X + 1) = -1.8633e-005
for all values of X

Just noticed it. Seems wrong to me. Ah well. Just don't use this to randomize lotto numbers and you'll be fine.|||Here is a variation of Terri's:

--create a view to expose NEWID() for randomness
create view dbo.vwRandomNumGenerator
as
select top 10 n
from
(
select 0 n union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 9 union all
select 9
 ) d
order by newid()
go--create udf
create function dbo.udfPhoneNumberGenerator ()
returns char(10)
AS
begin
declare @.phoneNumber char(10)
set @.phoneNumber = ''
Select @.phoneNumber = n + @.phoneNumber
from
(
select convert(char(1),n) n
from dbo.vwRandomNumGenerator
) d
return @.phoneNumber
end
go
--call our rnd phone generator inline
select dbo.udfPhoneNumberGenerator() RandomPhoneNumber
--drop view vwRandomNumGenerator
--drop function udfPhoneNumberGenerator

|||Thanks guys! That worked perfectly.

Great advice!

ScAndal

Build numbers

I am trying to restore a master database after a server rebuild and need to
get the build number back to 8.00.818. I have installed (or so I thought) t
he SP3a and the 8-11-03 patch to get it to that build number but when I chec
k the build number in Enter
prise Manager it still shows 8.00.194. Help?How are you getting the build number from Enterprise Manager? You should
connect to your SQL Server using Query Analyzer and run the following
command to get the SQL Server's build number:
SELECT @.@.VERSION
GO
May be you haven't installed the service pack on the client machines.
This might be helpful: http://vyaskn.tripod.com/sqlsps.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:819D85D5-070C-4150-87FB-B9A0C5A2B2FB@.microsoft.com...
I am trying to restore a master database after a server rebuild and need to
get the build number back to 8.00.818. I have installed (or so I thought)
the SP3a and the 8-11-03 patch to get it to that build number but when I
check the build number in Enterprise Manager it still shows 8.00.194. Help?|||I have done that as well to get the build number but under properties of the
sql instance name is where I was getting the information from Enterprise Ma
nager (is that wrong?). And after applying SP3a and the 8-11-03 patch it ha
sn't changed the build info
rmation. I have taken the defaults for installation of the SP and patch. I
Have completely rebuilt the machine and reinstalled SQL so there souldn't b
e anything strange with the installation (all default).|||I wouldn't use Enterprise Manager for this. As I posted earlier, query the
server directly. What do you get when you run SELECT @.@.VERSION?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:D18A75A7-4AC6-4917-9334-F056D3E0159F@.microsoft.com...
I have done that as well to get the build number but under properties of the
sql instance name is where I was getting the information from Enterprise
Manager (is that wrong?). And after applying SP3a and the 8-11-03 patch it
hasn't changed the build information. I have taken the defaults for
installation of the SP and patch. I Have completely rebuilt the machine and
reinstalled SQL so there souldn't be anything strange with the installation
(all default).|||I get the original build number of the system (8.00.194). The patches don't
seem to be applying or at least don't seem to be affecting the number at al
l and so the restore of the Master Database failes.|||Can't be right :-) I did this on various servers and I see updated build
numbers. You either applied the service pack to the wrong server, or you
haven't applied the service pack at all.
When you run the service pack exe first it extracts itself into a folder.
Many people mistake that and think they've applied the service pack, just by
extracting the service pack. Hope that's not the case with you. If it is,
you have to run the setup from the extracted folder.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:E97EEE10-091F-4DD8-99EB-B9513B38F1B5@.microsoft.com...
I get the original build number of the system (8.00.194). The patches don't
seem to be applying or at least don't seem to be affecting the number at all
and so the restore of the Master Database failes.|||Narayana Vyas Kondreddi wrote:

>Can't be right :-) I did this on various servers and I see updated build
>numbers. You either applied the service pack to the wrong server, or you
>haven't applied the service pack at all.
>When you run the service pack exe first it extracts itself into a folder.
>Many people mistake that and think they've applied the service pack, just b
y
>extracting the service pack. Hope that's not the case with you. If it is,
>you have to run the setup from the extracted folder.
>
Vyas,
Whether or not this is what happened, it never hurts to remind people
how easy it is not to install the update:
How *not* to install sp3a for Microsoft SQL Server 2000
1. Download the service pack file, SQL2KSP3.exe, from Microsoft
2. Run the service pack file SQL2KSP3.exe
3. Click OK when you see the message "the package has been successfully
delivered"
Guess what? The service pack has not been installed! The download page
says you just need to "Install Database Components SP3a (SQL2KSP3.exe),"
so what's going on?
Youi wouldn't know it from the download page, but you now have to go to
the location where you extracted the files and run setup (and if I
recall, there are many files containing the word setup).
SK

>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Chad" <anonymous@.discussions.microsoft.com> wrote in message
>news:E97EEE10-091F-4DD8-99EB-B9513B38F1B5@.microsoft.com...
>I get the original build number of the system (8.00.194). The patches don'
t
>seem to be applying or at least don't seem to be affecting the number at al
l
>and so the restore of the Master Database failes.
>
>

Build numbers

I am trying to restore a master database after a server rebuild and need to get the build number back to 8.00.818. I have installed (or so I thought) the SP3a and the 8-11-03 patch to get it to that build number but when I check the build number in Enterprise Manager it still shows 8.00.194. Help?How are you getting the build number from Enterprise Manager? You should
connect to your SQL Server using Query Analyzer and run the following
command to get the SQL Server's build number:
SELECT @.@.VERSION
GO
May be you haven't installed the service pack on the client machines.
This might be helpful: http://vyaskn.tripod.com/sqlsps.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:819D85D5-070C-4150-87FB-B9A0C5A2B2FB@.microsoft.com...
I am trying to restore a master database after a server rebuild and need to
get the build number back to 8.00.818. I have installed (or so I thought)
the SP3a and the 8-11-03 patch to get it to that build number but when I
check the build number in Enterprise Manager it still shows 8.00.194. Help?|||I have done that as well to get the build number but under properties of the sql instance name is where I was getting the information from Enterprise Manager (is that wrong?). And after applying SP3a and the 8-11-03 patch it hasn't changed the build information. I have taken the defaults for installation of the SP and patch. I Have completely rebuilt the machine and reinstalled SQL so there souldn't be anything strange with the installation (all default).|||I wouldn't use Enterprise Manager for this. As I posted earlier, query the
server directly. What do you get when you run SELECT @.@.VERSION?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:D18A75A7-4AC6-4917-9334-F056D3E0159F@.microsoft.com...
I have done that as well to get the build number but under properties of the
sql instance name is where I was getting the information from Enterprise
Manager (is that wrong?). And after applying SP3a and the 8-11-03 patch it
hasn't changed the build information. I have taken the defaults for
installation of the SP and patch. I Have completely rebuilt the machine and
reinstalled SQL so there souldn't be anything strange with the installation
(all default).|||I get the original build number of the system (8.00.194). The patches don't seem to be applying or at least don't seem to be affecting the number at all and so the restore of the Master Database failes.|||Can't be right :-) I did this on various servers and I see updated build
numbers. You either applied the service pack to the wrong server, or you
haven't applied the service pack at all.
When you run the service pack exe first it extracts itself into a folder.
Many people mistake that and think they've applied the service pack, just by
extracting the service pack. Hope that's not the case with you. If it is,
you have to run the setup from the extracted folder.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:E97EEE10-091F-4DD8-99EB-B9513B38F1B5@.microsoft.com...
I get the original build number of the system (8.00.194). The patches don't
seem to be applying or at least don't seem to be affecting the number at all
and so the restore of the Master Database failes.|||Narayana Vyas Kondreddi wrote:
>Can't be right :-) I did this on various servers and I see updated build
>numbers. You either applied the service pack to the wrong server, or you
>haven't applied the service pack at all.
>When you run the service pack exe first it extracts itself into a folder.
>Many people mistake that and think they've applied the service pack, just by
>extracting the service pack. Hope that's not the case with you. If it is,
>you have to run the setup from the extracted folder.
>
Vyas,
Whether or not this is what happened, it never hurts to remind people
how easy it is not to install the update:
How *not* to install sp3a for Microsoft SQL Server 2000
1. Download the service pack file, SQL2KSP3.exe, from Microsoft
2. Run the service pack file SQL2KSP3.exe
3. Click OK when you see the message "the package has been successfully
delivered"
Guess what? The service pack has not been installed! The download page
says you just need to "Install Database Components SP3a (SQL2KSP3.exe),"
so what's going on?
Youi wouldn't know it from the download page, but you now have to go to
the location where you extracted the files and run setup (and if I
recall, there are many files containing the word setup).
SK
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Chad" <anonymous@.discussions.microsoft.com> wrote in message
>news:E97EEE10-091F-4DD8-99EB-B9513B38F1B5@.microsoft.com...
>I get the original build number of the system (8.00.194). The patches don't
>seem to be applying or at least don't seem to be affecting the number at all
>and so the restore of the Master Database failes.
>
>

Build a Data Cube?

Hi,
I am a new comers in the fieled of SQL server 2000. I have to buid a data cube having the dimensions year,application number and student details.Please give me the suggestion with the query. I have create just one table with the attributes year,application number and studen details(name, address,marks ). I request you all please help me.

Pranjal,
Gauhati UniversityHey u have analysis services in sql server 2000 which helps u to build data cubes over the table(s). Please check that if u have analysis service installed in ur machine.

Tuesday, March 27, 2012

Bulk insertion

Tuesday, March 20, 2012

Bulk insert problem, any ideas?

Monday, March 19, 2012

Bulk insert of bit values

Wednesday, March 7, 2012

Bulk Insert and Data Aggregation

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?

Friday, February 10, 2012

build random phone number.

Build numbers

Build numbers

Build a Data Cube?

build sql script

Blog Archive

About Me