Sql Stop Logging: February 2012

Friday, February 24, 2012

how to store image in image field in sql server 2000

hi all,

i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.

how can i store any image ? what are the steps?

thanx in advance

Actually, the following statement will only work in sql2005. Sorry.

Try something like this:

Code Snippet

create table #bob

(id int, jpeg image)

insert into #bob

select 1, bulkcolumn

from openrowset(BULK 'c:\Winter.jpg', SINGLE_BLOB) AS table

HTH

|||

how to add using enterprise manager. I don't want to create a stored procedure for that or any sql statement.

Can i directly insert image as i can enter text into any text field?

|||

I don't think you're able to add them in via EM.

I think you can use textcopy (see Books Online) and there shouild an example of how this works in the pubimage.bat file in your install directory c:\program files\microsoft sql server\mssql\install\

HTH.

|||No way to do this in SSSM or EM.

If you favour doing this in SSMS in the next version of SQL Server, feel free to post a connect bug / feature request in the connect forums at www.connect.microsoft.com.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to store image datatype value to a variable?

I have image type col.
I'm trying to do the following,
DECLARE @.Data varbinary(16)
SET @.Data = (select imageCol from Table1 where id=3)
As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,
========================================
Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
========================================
Is there anyway to store image datatype value to a variable?
Cheers.

Try this:
select @.Data=imageCol from Table1 where id=3

|||

mbanavige wrote:
Try this:
select @.Data=imageCol from Table1 where id=3

Thanks a lot.

How to store image (in the folder) that linked by staffno?

Hi..
I have a folder which store staff picture in my C drive. Is it possible for me to link that staff picture into my report? It linked by staffno..something like this..
"C:\picture\" + {tblStaff.StaffNo} + ".jpg"
but..where do i put this code? any idea? thanx in advance..;)Try looking in Crystal's Help under Images (dynamic)...

How to store HTML in database?

How to store HTML in database?Why not store it in a blob.
In Informix we store it into TEXT large Objects.
WIth the web datablade, the server is then able to generate dynamic web pages, depending on the contents of the tables.
I don't know what BLOB types SQL-Server has, but try to store it in a character-like datatype, so you can still do searches on it.

How to store HTML data in SQL

Hi friends

I want to add HTML to the SQL server as data please can anyone help me. which data type . & how to store the data.

Thanks for all your support

Thanks & regards

ASPFreak

You can use a TEXT type or BLOB. I would personally use gzip to compress the string before storing it and uncompressed when retrieving.

Hope this helps

|||

Do you know or expect the amount of HTML you will have? If yes, then user varchar or nvarchar. If not, use text or ntext.

How to store them? Treat them as notmal text.

Example:

1Insert into MyTable (PageNumber, PageName , HTML)2 Values (1 ,'Index.html','<p>This is a sample</p>')3

Good luck.

|||

What approach did you take?

How to store HTML code in SQL server table

Hello,
I'm creating something like a web site builder in ASP.Net, and I need to
store an HTML code in the SQL server table. If my customer just enters an
HTML code, it is stored in the SQL server table like this: <table
cellSpacing="0" cellPadding="0" width="760"
border="0">, instead of <table cellSpacing="0" cellPadding="0"
width="760" border="0">
This significantly increases the size of the stored code.
Are there any conversion functions or something like this to avoid it? Where
this conversion should be done - in ASP.Net or SQL server? How do I convert
it back to create an HTML page?
Please note that I must use the Unicode data type - ntext, nchar etc, since
I'm using some Cyrillic characters.
I would appreciate your advice very much.
Thank you,
--
Peter AfoninPeter,
ntext in SQL Server will store Html markup just fine. You are encoding
your html before storing it in the database. Find out where you are
encoding the markup and change that code. Debug your code and make sure
that you are indeed sending pure html to the database. SQL Server will not
automatically encode html, so your code is doing it somewhere.
Best regards,
Jeffrey Palermo
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>|||What is the code you use to store the string you get in the SQL database?
"Peter Afonin" <pva@.speakeasy.net> a écrit dans le message de
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>
>|||As the other poster states, it is not SQL that is doing the encoding, it is
something else...
Also, if the html pages will be less than 4000 unicode characters, you may
prefer to strore the data in a varchar column.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>|||Thank you everyone for your suggestions.
So far when I use ntext it works OK, then I use the Literal control and
Server.Decode to write the data in ASP.Net.
Peter
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I'm creating something like a web site builder in ASP.Net, and I need to
> > store an HTML code in the SQL server table. If my customer just enters
an
> > HTML code, it is stored in the SQL server table like this: <table
> > cellSpacing="0" cellPadding="0"
width="760"
> > border="0">, instead of <table cellSpacing="0"
> cellPadding="0"
> > width="760" border="0">
> >
> > This significantly increases the size of the stored code.
> >
> > Are there any conversion functions or something like this to avoid it?
> Where
> > this conversion should be done - in ASP.Net or SQL server? How do I
> convert
> > it back to create an HTML page?
> >
> > Please note that I must use the Unicode data type - ntext, nchar etc,
> since
> > I'm using some Cyrillic characters.
> >
> > I would appreciate your advice very much.
> >
> > Thank you,
> >
> > --
> > Peter Afonin
> >
> >
>|||Wayne,
Maybe it's just a typo, but varchar cannot be properly used with unicode
and has a maximum length of 8000 characters. nvarchar has a limit of 4000
characters and should be used for all applications for maintenance purposes
(since most applications will have to support unicode in the future).
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I'm creating something like a web site builder in ASP.Net, and I need to
> > store an HTML code in the SQL server table. If my customer just enters
an
> > HTML code, it is stored in the SQL server table like this: <table
> > cellSpacing="0" cellPadding="0"
width="760"
> > border="0">, instead of <table cellSpacing="0"
> cellPadding="0"
> > width="760" border="0">
> >
> > This significantly increases the size of the stored code.
> >
> > Are there any conversion functions or something like this to avoid it?
> Where
> > this conversion should be done - in ASP.Net or SQL server? How do I
> convert
> > it back to create an HTML page?
> >
> > Please note that I must use the Unicode data type - ntext, nchar etc,
> since
> > I'm using some Cyrillic characters.
> >
> > I would appreciate your advice very much.
> >
> > Thank you,
> >
> > --
> > Peter Afonin
> >
> >
>|||Peter, I'm glad you have it working now.
--
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Peter Afonin" <peter@.gudzon.net> wrote in message
news:OAMi7Mk1EHA.1124@.tk2msftngp13.phx.gbl...
> Thank you everyone for your suggestions.
> So far when I use ntext it works OK, then I use the Literal control and
> Server.Decode to write the data in ASP.Net.
> Peter
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> > As the other poster states, it is not SQL that is doing the encoding, it
> is
> > something else...
> >
> > Also, if the html pages will be less than 4000 unicode characters, you
may
> > prefer to strore the data in a varchar column.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Peter Afonin" <pva@.speakeasy.net> wrote in message
> > news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > Hello,
> > >
> > > I'm creating something like a web site builder in ASP.Net, and I need
to
> > > store an HTML code in the SQL server table. If my customer just enters
> an
> > > HTML code, it is stored in the SQL server table like this: <table
> > > cellSpacing="0" cellPadding="0"
> width="760"
> > > border="0">, instead of <table cellSpacing="0"
> > cellPadding="0"
> > > width="760" border="0">
> > >
> > > This significantly increases the size of the stored code.
> > >
> > > Are there any conversion functions or something like this to avoid it?
> > Where
> > > this conversion should be done - in ASP.Net or SQL server? How do I
> > convert
> > > it back to create an HTML page?
> > >
> > > Please note that I must use the Unicode data type - ntext, nchar etc,
> > since
> > > I'm using some Cyrillic characters.
> > >
> > > I would appreciate your advice very much.
> > >
> > > Thank you,
> > >
> > > --
> > > Peter Afonin
> > >
> > >
> >
> >
>

How to store GPS Coordinates in database, Which Data Type?

I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.

Like LAT ( N38 28.025' ) and LONG (W105 52.098' )

The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.

I hope I have explained this right. The numbers in BOLD are what I need to maintain.

Thanks for any help in advance.

Steve

Hi Steve,

are you using SQL Server 2005 ? YOu could create your own data type for that ! I am not quite sure if since now anybody did already one for that reason but it would be worth a try searching in google for CLR +data type +sql server 2005 (and then the appropiate buzz words you probably know for your problem, like special names for the coordinates etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You could look at a SQL CLR User defined data type.The other option is to store as integers (or numerics) and then have a function that converts from the raw numeric value to the format above.

With a UDT you implement a parse method that allows you to interpret text into a value that you can store.

Be careful with UDTs though as changing them once in place is very difficult, as it is in use and so you can't just upgrade the base assembly.

How to Store Font and Size Information

Hi,

I am trying to store RichTexBox information, such as the formating of the paragraph, color, font and etc. Is that possible to store this kind of information in Sql Server 2005 Compact Edition without stripping down the properties one by one?

I am trying to store a sentence which contain of multiple color or font for different words. Is there anyway to store those information?

thanks.
You can store the entire RTF contents of the control to a ntext field in SQL CE.|||

ErikEJ wrote:
You can store the entire RTF contents of the control to a ntext field in SQL CE.

But what "container" should we use in C# ? a stream?

thx!

|||

SQL CE’s ntext maps to "string" in C#. That's what RTF is - a string with special markup.

You can, however, use Image type in the database and a byte array in C# which you can access via MemoryStream if string is not good enough.

|||

Ilya Tumanov wrote:

SQL CE’s ntext maps to "string" in C#. That's what RTF is - a string with special markup.

You can, however, use Image type in the database and a byte array in C# which you can access via MemoryStream if string is not good enough.

thanks

How to Store Font and Size Information

Hi,

I am trying to store a sentence which contain of multiple color or font for different words. Is there anyway to store those information?

thanks.
You can store the entire RTF contents of the control to a ntext field in SQL CE.|||

ErikEJ wrote:
You can store the entire RTF contents of the control to a ntext field in SQL CE.

But what "container" should we use in C# ? a stream?

thx!

|||

SQL CE’s ntext maps to "string" in C#. That's what RTF is - a string with special markup.

You can, however, use Image type in the database and a byte array in C# which you can access via MemoryStream if string is not good enough.

|||

Ilya Tumanov wrote:

SQL CE’s ntext maps to "string" in C#. That's what RTF is - a string with special markup.

You can, however, use Image type in the database and a byte array in C# which you can access via MemoryStream if string is not good enough.

thanks

How to store File in SQL DataBase

please tell the steps
How to store File in SQL DataBase

Hi,

please take a look at this article:Storing Binary Files Directly in the Database Using ASP.NET 2.0.

Grz, Kris.

How to store file attachments (text and binary).

I need to store several different types of documents (text, MS Word, PDF, etc.) in SQL Server 2005. What is the best way to store file attachments that have different MIME types?

Currently I'm using a table with a varbinary(max) column which works for binary files but not for text files. Do I need to have multiple columns in the table for different file types? Thanks in advance for your help.

CREATE TABLE dbo.[CM_Attachment] (
[Id] int IDENTITY (1, 1) NOT NULL ,
[Change_Request_Id] int NOT NULL ,
[Attachment] varbinary(max) not null,
[Created_Date] datetime NOT NULL DEFAULT GetDate() ,
[Created_By] varchar(30) NOT NULL ,
[Modified_Date] datetime NOT NULL ,
[Modified_By] varchar(30) NOT NULL
)
GO

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB) -- Does not work
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob) -- Works
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob) -- Works
as ChangeRequestAttachment

I believe the datatype to use is "image" and it will store any file type.

Check out the link below:
http://www.aspnettutorials.com/tutorials/database/Save-Img-ToDB-Csharp.aspx

|||

2 quotes from "Microsoft SQL SERVER 2005 Implementation and maintenance Training Kit":

[

Binary Data Types

The most popular data type within this group is the image data type. This data type has an unfortunate name; it is not used exclusively to store images, such as a library of pictures from a recent vacation. Although you can store pictures in an image data type, you can also use this data type to store Word, Excel, PDF, and Visio documents. You can store any file that is 2 GB or less in size in an image data type. One of the most famous implementations of this data type is the TerraServer project, which is a multiterabyte database of terrestrial images that you can access at www.terraserver.com.

The varbinary(max) data type is new to SQL Server 2005. It can store the same amount of data as an image data type, and you can use it with all the operations and functions that you can use with binary/varbinary data types.

]

... and ...

[

Microsoft intends to drop support for the text, ntext, and image data types in upcoming SQL Server versions. For this reason, Microsoft recommends that you stop using these data types.

]

|||Can you please explain what you mean when you say it doesn't work for text? You would simply read it as SINGLE_BLOG and insert into varbinary(max) column. For text files, the data will be a binary stream so you should be able to simply cast it to varchar(max) or nvarchar(max) to read or see the text data. SQL Server doesn't care what you put inside varbinary(max) column or the nature of the content. The server simply sees the data as a stream of bytes.|||

Thank you everyone for your help thus far. Unfortunately I'm not quite there yet.

When trying to store a text file using SINGLE_BLOB my result is
(0 row(s) affected)

When trying to store a text file using SINGLE_CLOB my result is
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

SQL

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
--Results in the following:
--(0 row(s) affected)

-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
-- Results in this error:
-- Msg 257, Level 16, State 3, Line 1
-- Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

-- These two work.
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob)
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob)
as ChangeRequestAttachment

|||

I found my mistake! The text file I was testing with was a zero byte file (I forgot to put some text in the test file). Once I added some text to the test Text file the following two insert statements worked correctly.

-- This works using SINGLE_CLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, Convert(varbinary(max), BulkColumn) , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
as ChangeRequestAttachment

-- This works using SINGLE_BLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
as ChangeRequestAttachment

Thank you again everyone for your help; what a great resource this form is.

How to store euro currency symbol?

I have a problem to store Euro symbol "â?¬" in the MS SQL 2000 database: it is
stored as "?".
The database default collation used is 'SQL_latin1_General_CP1_CI_AS (code
page 1252)". This doesn't support euro symbol. I found the only standard
character set that contains euro is "Latin-9 (ISO-8859-15)", Latin1 doesn't.
There's also an article describes the international feature of MS SQL server
200
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp>
However I didn't find which specific collation or code page support Euro
symbol from this article, all it says is any column of 'money' or
'smallmoney' type can include a currency symbol such as euro "â?¬"
Does any body know which collation of MS SQL 2000 support euro symbol?
Does the column type has to be "money" to store it?
How do I store it?
ThxHi Tina,
I do not have any problems in storing the Euro symbol in my database with
the SQL_latin1_General_CP1_CI_AS collation.
How do you insert the euro symbol into your record?
--
HTH
Karl Gram
"Tina" <Tina@.discussions.microsoft.com> wrote in message
news:3E881A78-5D70-4319-9FEB-B0C91795370D@.microsoft.com...
>I have a problem to store Euro symbol "?" in the MS SQL 2000 database: it
>is
> stored as "?".
> The database default collation used is 'SQL_latin1_General_CP1_CI_AS (code
> page 1252)". This doesn't support euro symbol. I found the only standard
> character set that contains euro is "Latin-9 (ISO-8859-15)", Latin1
> doesn't.
> There's also an article describes the international feature of MS SQL
> server
> 2000
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp>
> However I didn't find which specific collation or code page support Euro
> symbol from this article, all it says is any column of 'money' or
> 'smallmoney' type can include a currency symbol such as euro "?"
> Does any body know which collation of MS SQL 2000 support euro symbol?
> Does the column type has to be "money" to store it?
> How do I store it?
> Thx
>

How to store euro currency symbol?

I have a problem to store Euro symbol "€" in the MS SQL 2000 database: it is
stored as "?".
The database default collation used is 'SQL_latin1_General_CP1_CI_AS (code
page 1252)". This doesn't support euro symbol. I found the only standard
character set that contains euro is "Latin-9 (ISO-8859-15)", Latin1 doesn't.
There's also an article describes the international feature of MS SQL server
2000
<http://msdn.microsoft.com/library/de...server2000.asp>
However I didn't find which specific collation or code page support Euro
symbol from this article, all it says is any column of 'money' or
'smallmoney' type can include a currency symbol such as euro "€"
Does any body know which collation of MS SQL 2000 support euro symbol?
Does the column type has to be "money" to store it?
How do I store it?
Thx
Hi Tina,
I do not have any problems in storing the Euro symbol in my database with
the SQL_latin1_General_CP1_CI_AS collation.
How do you insert the euro symbol into your record?
HTH
Karl Gram
"Tina" <Tina@.discussions.microsoft.com> wrote in message
news:3E881A78-5D70-4319-9FEB-B0C91795370D@.microsoft.com...
>I have a problem to store Euro symbol "?" in the MS SQL 2000 database: it
>is
> stored as "?".
> The database default collation used is 'SQL_latin1_General_CP1_CI_AS (code
> page 1252)". This doesn't support euro symbol. I found the only standard
> character set that contains euro is "Latin-9 (ISO-8859-15)", Latin1
> doesn't.
> There's also an article describes the international feature of MS SQL
> server
> 2000
> <http://msdn.microsoft.com/library/de...server2000.asp>
> However I didn't find which specific collation or code page support Euro
> symbol from this article, all it says is any column of 'money' or
> 'smallmoney' type can include a currency symbol such as euro "?"
> Does any body know which collation of MS SQL 2000 support euro symbol?
> Does the column type has to be "money" to store it?
> How do I store it?
> Thx
>

How to store euro currency symbol?

I have a problem to store Euro symbol "€" in the MS SQL 2000 database: it
is
stored as "?".
The database default collation used is 'SQL_latin1_General_CP1_CI_AS (code
page 1252)". This doesn't support euro symbol. I found the only standard
character set that contains euro is "Latin-9 (ISO-8859-15)", Latin1 doesn't.
There's also an article describes the international feature of MS SQL server
2000
<lfeaturesinsqlserver2000.asp" target="_blank">http://msdn.microsoft.com/library/d...lserver2000.asp>
However I didn't find which specific collation or code page support Euro
symbol from this article, all it says is any column of 'money' or
'smallmoney' type can include a currency symbol such as euro "€"
Does any body know which collation of MS SQL 2000 support euro symbol?
Does the column type has to be "money" to store it?
How do I store it?
ThxHi Tina,
I do not have any problems in storing the Euro symbol in my database with
the SQL_latin1_General_CP1_CI_AS collation.
How do you insert the euro symbol into your record?
HTH
Karl Gram
"Tina" <Tina@.discussions.microsoft.com> wrote in message
news:3E881A78-5D70-4319-9FEB-B0C91795370D@.microsoft.com...
>I have a problem to store Euro symbol "?" in the MS SQL 2000 database: it
>is
> stored as "?".
> The database default collation used is 'SQL_latin1_General_CP1_CI_AS (code
> page 1252)". This doesn't support euro symbol. I found the only standard
> character set that contains euro is "Latin-9 (ISO-8859-15)", Latin1
> doesn't.
> There's also an article describes the international feature of MS SQL
> server
> 2000
> <ntlfeaturesinsqlserver2000.asp" target="_blank">http://msdn.microsoft.com/library/d...lserver2000.asp>
> However I didn't find which specific collation or code page support Euro
> symbol from this article, all it says is any column of 'money' or
> 'smallmoney' type can include a currency symbol such as euro "?"
> Does any body know which collation of MS SQL 2000 support euro symbol?
> Does the column type has to be "money" to store it?
> How do I store it?
> Thx
>

how to store encrypted data in sql database

hai everybody,

I have a login page in asp.net with the details being stored in the database. In the table at present i am storing the password in plain text field.

Is there any possible for me to store the data in any encrypted format or some format so that no one will be able to view the password.

And while returning back to the webpage i want them in the plain text . . . !

Is there any possibility for this

kindly help at the earliest

thanks in advance

sasidar::Is there any possibility for this

Sure. Just encrypt the data before writing it into the server, decrypt it after reading it.

note, though, that what you do here is NOT safe. Why do you ever want to decrypt the password? Use a hash and compare the hashed values.|||The best solution is to use a one way hash created in ASP.NET code, and then whenever you want to check the entered password you create a hashed copy of the password entered, and then compare it to the hash stored in the database.


private string CreatePasswordHash(string pwd,string salt)
{
string saltAndPwd=string.Concat(pwd,salt);
string hashedPwd=FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd,"SHA1");
return hashedPwd;
}

The salt string is just a string used to ensure that each password when encrypted will be unique (so if two people have a password of "password" the hashes will not be the same.

This way, really no one, not even you as the administrator of teh system, will be able to read the password (this is a good thing). You can of course create a method to reset the password.|||Steve Wyncoop of theSQL Server Worldwide User's Group is ga-ga overXP_CRYPT. I've not used the product, but it looks like it simplifies saving encrypted data to SQL Server.

Don|||Thanks for those who replied me,

But there is one more doubt for me. If i am using the hashing technique then we cannot reveal the password in the plain text format. Suppose if there is a situation where i require the password to known how to get back the plain text format. ?

Through my readings i came to conclusion that it cannot be done. Is it So ? In that case is there any other technique so that i can encrypt the password and then decrypt the password whenever required

Awaiting for the solutions :

Thanks in advance

Sasidar|||You should work out a way that you never need to get to the decrypted password. People often use the same password for lots of things. If a user uses their one password on your site, it is better that even you never be able to get to that password.

In systems I write, we have a system in place where a password can be reset, but we have no way to reverse the one way hash. This way, even if the database is compromised, user's passwords will not be exposed. Your users will appreciate it.|||Thanks for ur reply ,

but still i have a doubt , say suppose my client or user forget his pasword , then what should i do so that i can change the password . If i am going to change contecnts directly into the database then the plain text will be changed and i hope that will not be fine. Is there any solution for this

Awaiting for the solution

Thanks

Bye

Sasidar|||The way tou handle that is to create an administrative page that allows you to reset the password (you have a page that encrypts a default password EXACTLY the same way that your normal password hashing works).|||sorry mr.douglas

i am not able to understand

can u give me more details please

Thanking u

sasidar|||I really can't, without providing all the code to do it, and I do not have time for that.

All you need to do is do the exact same one-way hashing on a default password (for instance, "password") and save that as the users new password, and in some way notify the user of the new password.

This book:

http://www.microsoft.com/mspress/books/6501.asp

has lots of information on how to do this kind of thing.

How to store duration in database?

Hi all,

I want to find working duration between two datetimes in c#.
i'm using following code...

DateTime starttime = Convert.ToDateTime(Session["StartTime"]);
DateTime endtime = DateTime.Now;

TimeSpan duration = endtime - starttime;
DateTime period = new DateTime(duration.Ticks);

i want to store this duration in database through stored procedure, i've give datetime datatype to duration but it is giving error in conversion of TimeSpan to DateTime..

Please help... Thanks

You don't need to store this information in your database. You can retrieve this value by using DateDiff between two dates in SELECT statement, for example in seconds.

Datediff(second,startdate, enddate) as duration

Here is a link to how to get the duration and format it afterwards.

http://forums.asp.net/t/1185449.aspx

How to store and use xqueries in sql2005?

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.

How to store and use xqueries in sql2005?

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

but this doesn't:

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.

how to store and retrive .DOC file in sql

Hi frds,

My question is how should i store a .DOC file in sql 2005 as image field and how to retrive the Doc stored in Image format

Thanks & regards

Sithender.s

Have a look at the technique for storing images in http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp (Storing and retrieving images from SQL Server using Microsoft .NET.)
http://www.codeproject.com/aspnet/PicManager.asp (How to upload files to Web pages in ASP.NET? How to read an image from a database using ADO.NET and display it in a Web page?)
http://www.codeproject.com/aspnet/NetPix.asp (This article explains an ASP.NET application to view and share photos online.)

This should give the basis for your intended document storage and retrieval.

|||

My question to you is why you want to store the .doc in Image data type. This data type is basically for backward compatibility with SQL Server 2000 and will not be available in next version of SQL Server. Use Varbinary data type instead of Image. However by using the following methods you can upload it to image data type as well. I have used file upload control of ASP.Net 2.0 to upload files in following example.

protectedvoid btnFileUpload_Click(object sender,EventArgs e) { if (FileUpload1.FileName.Length > 0) { int len = FileUpload1.PostedFile.ContentLength; String strDes = txtDescription.Text; byte[] file =newbyte[len]; FileUpload1.PostedFile.InputStream.Read(file, 0, len); clsDAL objDal =newclsDAL(); objDal.UploadFile(file,strDes); } } publicvoid UploadFile(Byte[] pFile,String pDesc) { SqlConnection cnnConn; SqlCommand cmmCommand; cnnConn =newSqlConnection(ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString); cnnConn.Open(); cmmCommand =newSqlCommand(); cmmCommand.Connection = cnnConn; cmmCommand.CommandType =CommandType.StoredProcedure; cmmCommand.CommandText ="sp_UploadFile"; cmmCommand.Parameters.AddWithValue("@.pFile", pFile); cmmCommand.Parameters.AddWithValue("@.pDesc", pDesc); cmmCommand.ExecuteNonQuery(); }|||

Sorry, i am newbie in upload file.

May i know that where do i replace my sql statement and where do i should fill in the user is, password and server path like 192.xx.xxxx.xxx??

thanks.........

how to store and retrieve image files in sql server

hi like to knw the steps for storing 1000+ image files in an sql server.....
i/p: 1000+ image files

o/p: should be displayed in an vb6 environment in an pic box.....I would recommend you storing full path to picture file and not file itself then get path from a database and load picture from file into a picture box.
It is extremely slow and complicated to do it on the database side. You should be able to convert picture file into a binary stream to store it on the database and then be able to decode this binary stream back to picture.

Good Luck.

how to store and read .doc file in a table

I would like to store and read .doc file in a table of sql server using C#.

Can some body help me on this? my id ispadam76@.gmail.com.

Thanks & Regard,
Padam Kumar Tripathi

you could use binary, image or varbinary datatypes. it will drag the database, queries could be slow.

On the other hand, you could save the file on a shared drive and save the path of the file location in a table. if you need code, please google.

how to store and access reservation data (was "small project")

I have a project need to store the information of reservation. For example, somebody wants to reserve a laptop. She will sign in the date from calender to use. I would like to let the administrator to access the database and update the database in the intranet. Also the user who want to reserve a resource can access the intranet to fill a form, after approving it, she can borrow the resource. Of course, this is small database. And we also have a report on this.Do you have any idea how to do it? I appreciate.This in a database? What's wrong with excel/plain text file?
A bit of asp/jsp and a simple database? Guess it depends on what's been used for other applications.

How to store an MS Word doc in a sql 2005 table

Hello,

I want to store the contents of an MS Word document (or a brief Word extract) in a database field. This document could contain carriage returns, boldface, etc. I have tried storing the Word doc in fields of type nchar, text, and sql_variant but they do not keep the Word formatting. I found that the "Extended Properties" attribute of a field seems to retain these properties. (Though if I recall right, Extended Properties has a length limitation and is a pain in the #*&_*^! to access)

What is the "right" way to do what I'm trying? Keep in mind that we may want to view this document under either "Query Analyzer" or print a hardcopy version. And we'd like both to be intelligible!

TIA,

Barkingdog

hello,

looks like an EDMS to me
1. you have to OCR(optical character recognition) the document to obtain the text content
that you want to view in sql query analyzer. OCR is a free feature of msoffice 2003.
2. Or. run an application that do office automation and read the text line by line.Save it as a string
then save to sql server. that is if you dont trust the OCR
3. to keep the document intact as it is. Save the document as a doc file then stream it in to a
sql server blob data type
4. stream out the doc and open it with msoffice when you want to view it.
5. you may want to apply fulltext feature on the result of no 1 and 2

regards,

|||In comment to joey, I would recommend the 3 +4 as you can store the data as a Blob and enable SQL Server to use fulltext search on that blob with the appropiate settings. You wont′need to store the data in "cleartext", binary will be enough to enable full-text capability.

HTH, Jens Suessmeyer.

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

i see.

they need to read the docs through QA so i recommended an OCR

to store clear text.

thanks for the comment anyway

|||You are right, seems that I didnt′read that part in detail:-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to store an image on to my SQL database?

How to store an image on to my SQL database from the web form? Please guide me through. Thank you.http://www.dotnetspider.com/Question117.aspx

How to store an email on SQL server 2000?

Is it possible to store an email in SQL server 2000? Do we need to define our own data type for that, if yes then how? or do we have to store it as an object, again how? I want to save email after sending it by my ASP .NET application and then retrieve it at a later stage......Anyone please help?

You could create a new email table and just log to it every time you send. That is what we do. Below is our table schema.

Email_Log
-----
ID int Not Null
From varchar(100) Not Null
To Text Not Null
CC Text
BCC Text
Subject varchar(250)
Body Text
CreatedDate DateTime

|||

Email_Log
-----
ID int Not Null
From varchar(100) Not Null
To Text Not Null
CC Text
BCC Text
Subject varchar(250)
Body Text
CreatedDate DateTime

We can only use this schema when the email body is plain text without pictures or formatting information. I would suggest storing the email body using binary files. Or we can store the email body on a file server, while keeping a link in the table which points to the location where the email is stored.

Hope my suggestion helps

how to store an "" value in my database instead of null

I have a problem I 'get stuck on it. i hope someone can help me.

I have a SQLServer table with an ID-field with a primary key and identity on it, and a couple of other fields with defaultvalue "" and NOT nullable

When i like to update an record in an formview of ASP 2.0 and fill in all fields everything is updated and everything goes fine

but

when I leave one field blank then I get the message 'can not enter null value in column X'

even when I place in the Updateparameter in my code

<asp:Parameter Name="Name" DefaultValue="" /> ASP tries to update an Null value

when i place an space as default value like

<asp:Parameter Name="Name" DefaultValue=" " />

everything goes fine exept there is an space in my database.

How can I update my record, and leave some textboxes emtpy AND where my fields are NOT nullable

I'm getting desparate of this.

make my fields nullable is not an option because this is a 'customerswish'

Please help

There is another property of the parameter, something like nullempty, or emptynull, nullifyemtpy. Because all form variables are encoded as strings, you can't really represent the null value, and in most cases where you are doing form entry from the web, leaving a field blank should enter a null value (As if the value is unknown/unspecified), so the default for this property of the parameter is ON.|||

Thanks a lot, that's the one.

Shame on me, why haven't I see this setting.

is this new in 2005 (I've previously working with Beta 2) and this occur in my 2005 projects

Thanks

How to store actual millisecond in datetime?

Hello. I've seached the this forum and others for this question of mine but couldn't get any thing. Everyone know that in MSSQL, the precision of datetime is currently limited to 1/300th of a second. My question is, is there any way to store the actual millisecond in the a table? Also, later, able to pull it up from the table? What are the options?

Please advise.

Thanks,

Rick..

Running the following script worked for me in storing milliseconds. You can review BOL under "Date and Time Formats" for a bit more clarity.

create table test1..t2(c1 datetime, c2 dec(17,12));

insert t2(c1)
select convert(DATETIME, '12/21/07 03:04:05.300');

insert t2(c1)
select getdate();

select c1, convert(dec(17,12), c1) from t2;

update t2
set c2 = convert(dec(17,12), c1);

|||

Hi Zach. Thank for replying. Actually, the problem is still there. If you replace the date you are storing to '10/25/2006 23:59:59.991' and run the script. You will get '10/25/2006 23:59:59.990'. I need to be able to store the exact millisecond being written. This rounding being done to the millisecond is causing use a lot of headache.

Any other idea?

Thanks again.

Rick..

|||Your problem is the Windows clock, which is what you are reading, is only updated every 55 ms. You will never get the EXACT ms something ran. The time is not that acurate.

Whatever you are trying to do, you will need to find another method to determine the order or timing.|||

Hi. I am not sure if your statement was true. I don't think it has anything to do with windows clock. Reason I am saying that was, the date time value I am saving into the table is hardcoded. Everyone know its how Microsoft implemented it. But I need to save the actual value without MSSQL truncating the value before storing it in the database.

Thanks for your comment though.

Rick..

|||I think that it is more of an issue of how Sybase originally defined datetime in SQL Server -and Microsoft has never corrected the 'mistake'.|||

The problem is that you never see the clock value but only the value converted to the binary format of a SQL datetime (which has the level of precision inherent in its structure).

If you really want to get at the unaltered clock value then I think that you need to use code to access it directly. If you are using SQL Server 2005 then you could write CLR code to access the System.DateTime structure. You could probably even create a new data type. Of course you need some way to hold and retrieve the information you want (possibly the ISO 8601 string format yyyy-mm-ddThh:mm:ss.mmm). I don't know the details of attempting this but those in the ".NET Framework inside SQL Server" forum could probably help.

If you are using an earlier version of SQL Server then you probably need to look at extended stored procedures.

|||Open a query window and repeat this statement 500 times:

PRINT CONVERT(varchar(30),getdate(),109)

Run this and see what you get.

You will get the exact same ms until it instantly changes to another set.

It is also a problem, someone mentioned, the significant digits of the time does not allow you to get proper ms.

You still need to find another way to do what you are trying to do.|||

Unfortunately, SQL Server is doing rounding with the datetime data type. Therefore, you won't be able to use this data type. Even if you store the value in a decimal(p,s) data type, you probably won't be able to cast it into a datetime data type without incurring problems.

Oracle implemented true precision, user configurable up to nine (9) decimal places, when it released its TIMESTAMP data type in 9i.

|||

Oddly enough try running the following entries:

select convert(DATETIME, '12/21/07 03:04:05.302')

select convert(DATETIME, '12/21/07 03:04:05.303')

select convert(DATETIME, '12/21/07 03:04:05.304')

select convert(DATETIME, '12/21/07 03:04:05.305')

You will find the following results:

2007-12-21 03:04:05.303

2007-12-21 03:04:05.307

So the next question is, what is going on? This is some mighty weird rounding problems. What we believe to be happening is milliseconds are translated into ticks at 3 ms increments.

Moral of the story, if you really need to save that low then you need to create an int column separate of the datetime column and save the milliseconds there.

|||That is not strange at all. That is caused by the precision of the time in the datetime field.

See this very good explanation:

http://www.sql-server-performance.com/bs_date_time.asp

You are NEVER going to get 1 ms resolution in a datetime field.|||

This MSDN article specifically states an accuracy of 3.33 milliseconds. I only meant it was strange in that it happens because of converting from varchar like '3/28/2007 11:30:00:001' to a datetime value.

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

How to store actual millisecond in datetime?

Please advise.

Thanks,

Rick..

Running the following script worked for me in storing milliseconds. You can review BOL under "Date and Time Formats" for a bit more clarity.

create table test1..t2(c1 datetime, c2 dec(17,12));

insert t2(c1)
select convert(DATETIME, '12/21/07 03:04:05.300');

insert t2(c1)
select getdate();

select c1, convert(dec(17,12), c1) from t2;

update t2
set c2 = convert(dec(17,12), c1);

|||

Any other idea?

Thanks again.

Rick..

|||Your problem is the Windows clock, which is what you are reading, is only updated every 55 ms. You will never get the EXACT ms something ran. The time is not that acurate.

Whatever you are trying to do, you will need to find another method to determine the order or timing.

|||

Thanks for your comment though.

Rick..

|||I think that it is more of an issue of how Sybase originally defined datetime in SQL Server -and Microsoft has never corrected the 'mistake'.|||

The problem is that you never see the clock value but only the value converted to the binary format of a SQL datetime (which has the level of precision inherent in its structure).

If you are using an earlier version of SQL Server then you probably need to look at extended stored procedures.

|||Open a query window and repeat this statement 500 times:

PRINT CONVERT(varchar(30),getdate(),109)

Run this and see what you get.

You will get the exact same ms until it instantly changes to another set.

It is also a problem, someone mentioned, the significant digits of the time does not allow you to get proper ms.

You still need to find another way to do what you are trying to do.

|||

Oracle implemented true precision, user configurable up to nine (9) decimal places, when it released its TIMESTAMP data type in 9i.

|||

Oddly enough try running the following entries:

select convert(DATETIME, '12/21/07 03:04:05.302')

select convert(DATETIME, '12/21/07 03:04:05.303')

select convert(DATETIME, '12/21/07 03:04:05.304')

select convert(DATETIME, '12/21/07 03:04:05.305')

You will find the following results:

2007-12-21 03:04:05.303

2007-12-21 03:04:05.307

So the next question is, what is going on? This is some mighty weird rounding problems. What we believe to be happening is milliseconds are translated into ticks at 3 ms increments.

Moral of the story, if you really need to save that low then you need to create an int column separate of the datetime column and save the milliseconds there.

|||That is not strange at all. That is caused by the precision of the time in the datetime field.

See this very good explanation:

http://www.sql-server-performance.com/bs_date_time.asp

You are NEVER going to get 1 ms resolution in a datetime field.
|||

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

how to store a word document in sql server

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank youAsked and answered in .server -- please refrain from multiposting.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:eEbLTwOGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I Need help on how to store a word document in sql server.what datatype
and
> how to convert it to store it.
> Pleas Help
> Thank you
>

how to store a word document in sql server

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you
Asked and answered in .server -- please refrain from multiposting.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:eEbLTwOGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I Need help on how to store a word document in sql server.what datatype
and
> how to convert it to store it.
> Pleas Help
> Thank you
>

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you
See if this points you in the right direction:
http://vyaskn.tripod.com/programming_faq.htm#q5
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:O6Ja0wOGFHA.208@.TK2MSFTNGP12.phx.gbl...
I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank youSee if this points you in the right direction:
http://vyaskn.tripod.com/programming_faq.htm#q5
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:O6Ja0wOGFHA.208@.TK2MSFTNGP12.phx.gbl...
I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you
Use the IMAGE data type to store the document. In general you would need to
store the document in memory and pass a reference to that memory as a
parameter to a stored procedure that deals with inserting or updating the
information. The implementation details on the application side would be
language specific.
Jim
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:OxWajwOGFHA.3908@.TK2MSFTNGP12.phx.gbl...
>I Need help on how to store a word document in sql server.what datatype
>and how to convert it to store it.
> Pleas Help
> Thank you
>

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank youSee if this points you in the right direction:
http://vyaskn.tripod.com/programming_faq.htm#q5
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:O6Ja0wOGFHA.208@.TK2MSFTNGP12.phx.gbl...
I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you

how to store a record before deleting it using triggers

hey ,
im new to SQL. i wanted to save a particular record before deleting it usin
g a delete trigger can anyone help me on this .. thnxIO you want to store the whole row and therefore the data you could
consider leaving the data in the table and marking the row as "deleted"
by flagging the row with a bit column as deleted. If you don=B4t want to
maintain the whole row you could do something like this:
CREATE Trigger DELTrg_SomeTable ON SomeTable
FOR DELETE
AS
BEGIN
INSERT INTO SOmeTable
SELECT * from Deleted
/*
Rather than using the asterix to Select the column and data I would
namer the columns you want to select and insert therefore lgiving you
the opputunity to fill in additional fields which are extended int he
archive (something like an audit data, who deleted what row from what
host, etc.)
*/
END
HTH, Jens Suessmeyer.

How to store a content into database

Hi everyone,

I'm just using TEXTBOX control to save data into my DB. My question is, if I have multiple lines of sentences, it does not store as what it looks like. For example;

Hello,
Mike

It stores into the database as "Hello, Mike" and if I read from the database, it does not have seperate lines and shows it like this;

Hello, Mike

How can I store the contents into database and make the contents as original?
(Sorry for the poor English, but I hope you understand it.)

One way of doing this will be to replace all new lines with <BR> before saving it to database.

do something like that:

TextBox1.Text.Replace(vbCrLf,

"<br>")|||

SQL Server dose accept carriage returns, but note it stores carriage returns as 2 characters:1st for line feed (ascii code 10)and 2nd for carriage return (ascii code 13). How did you push the lines in the textbox into database? If you use a single string which contains multiple lines, then you can get the lines back from database. For example:

using (SqlConnection conn = new SqlConnection(@."Data Source=.\iori2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("CustOrderHist", conn);
string s1 = textBox1.Text;

cmd.CommandText = "INSERT INTO testString SELECT @.v";
cmd.Parameters.Add("@.v", SqlDbType.VarChar).Value = s1;

cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT s FROM testString";
SqlDataReader sda = cmd.ExecuteReader();
sda.Read();
s1 = sda.GetString(0);
textBox1.Text = "Retrieved:\n" + s1;
}

How to store .wav or .mp3 file in SQL Server 2000

Hi,

I have a problem to store .wav and .mp3 file to store in SQL Server
2000.

How can I store this?

Can any one give me code regarding this. I have little bit idea. But
I need some technical help in Code.

Thanks in advance,

PratikHere's some code to get you started:

CREATE TABLE MyMp3s
(
Mp3Image image
)
GO

CREATE PROC InsertMp3Image
@.Mp3Image image
AS
INSERT INTO MyMp3s VALUES(@.Mp3Image)
GO

// C# code snippet

Int32 m_MaxFileSize = 10240000;

SqlCommand storedProcedure =
new SqlCommand("InsertMp3Image" , SqlConnection);
storedProcedure.CommandTimeout = 30;
storedProcedure.CommandType = CommandType.StoredProcedure;

System.IO.BinaryReader binaryReader =
new BinaryReader(
new FileStream (@."C:\Mp3\MyMp3.mp3",
FileMode.Open,
FileAccess.Read));

byte[] buffer = binaryReader.ReadBytes(m_MaxFileSize);

binaryReader.Close();

storedProcedure.Parameters.Add( "@.Mp3Image", buffer );

storedProcedure.ExecuteNonQuery();

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

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

"Pratik Kothari" <pratik.kothari@.dadomain.com> wrote in message
news:d025254b.0311210330.1fcb8ef3@.posting.google.c om...
> Hi,
> I have a problem to store .wav and .mp3 file to store in SQL Server
> 2000.
> How can I store this?
> Can any one give me code regarding this. I have little bit idea. But
> I need some technical help in Code.
> Thanks in advance,
> Pratik|||pratik.kothari@.dadomain.com (Pratik Kothari) wrote in message news:<d025254b.0311210330.1fcb8ef3@.posting.google.com>...
> Hi,
> I have a problem to store .wav and .mp3 file to store in SQL Server
> 2000.
> How can I store this?
> Can any one give me code regarding this. I have little bit idea. But
> I need some technical help in Code.
> Thanks in advance,
> Pratik

You sure you want to? Why not store the file on the hard drive and
name of the file in the database?|||>>>>>
You sure you want to? Why not store the file on the hard drive and
name of the file in the database?
<<<<<

While this approach is much easier to implement, there is a host of
security and data integrity issues that vitiates its use.

1) What if someone deletes the file? You end up with a dangling
reference.

2) What if someone, innocently or otherwise, overwrites the file? Bad
voodoo.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanx Dan,

This is that what i want.

Pratik

How to stop transaction logging in SQL Server 7

quote:

> I have a lengthy atomic action in a SQL script that
> generates a huge transaction log volume. How can I
> essentially stop the transaction logging for SQL Server
> 7? I know I can use ALTER DATABASE for SQL Server 2000
> to change the recovery model to Simple, but SQL Server 7
> doesn't have these recovery models. The script action
> happens in one call to ALTER TABLE which operates on a
> huge table, so I can't break it apart and truncate on
> checkpoints inside the action. Any suggestions?
> Thanks!

How to stop transaction logging in SQL Server 7

I have a lengthy atomic action in a SQL script that
generates a huge transaction log volume. How can I
essentially stop the transaction logging for SQL Server
7? I know I can use ALTER DATABASE for SQL Server 2000
to change the recovery model to Simple, but SQL Server 7
doesn't have these recovery models. The script action
happens in one call to ALTER TABLE which operates on a
huge table, so I can't break it apart and truncate on
checkpoints inside the action. Any suggestions?
Thanks!The SIMPLE recovery model in SQL 2000 is essentially the same as 'select
into/bulk copy' and 'trunc. log on chkpt.' in SQL 7. In both cases,
committed data are removed from the log when a checkpoint occurs.
Transaction logging is required in order to ensure logical and physical
integrity. You may find it significantly faster to recreate the table with
a minimally logged SELECT ... INTO instead of a fully-logged ALTER TABLE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Clapis" <pclapis@.earthlinkdotnet> wrote in message
news:2bf101c3e13f$ac14f630$a501280a@.phx.gbl...
> I have a lengthy atomic action in a SQL script that
> generates a huge transaction log volume. How can I
> essentially stop the transaction logging for SQL Server
> 7? I know I can use ALTER DATABASE for SQL Server 2000
> to change the recovery model to Simple, but SQL Server 7
> doesn't have these recovery models. The script action
> happens in one call to ALTER TABLE which operates on a
> huge table, so I can't break it apart and truncate on
> checkpoints inside the action. Any suggestions?
> Thanks!

How to stop the TOP clause being added to query when defining a view

I've just switched over to SQL2005 Server Management Studio although most of my databases are SQL2000. Apart from noticing that everything takes longer than with Enterprise Manager I have one specific problem.

When I save a view that I have created in the visual toolbox it gets saved with the TOP 100 Percent clause added, even if I take the clause out of the SQL statement it gets put right back in when I save it. This is causing an application which worked fine before to fail with the error "cannot update a view with TOP clause"

Anyone any ideas how I can stop this clause being added?

Many thanks

? Hi Ian, >>Anyone any ideas how I can stop this clause being added? Take out the ORDER BY clause as well. An ORDER BY in a view is only allowed if there is also a TOP clause. Also, the only guaranteed effect the ORDER BY in the view will have is to define what rows to select and what rows to discard for satisfying the TOP requirement. No guarantee is made wrt the order of rows returned, unless you add an ORDER BY to the final SELECT statement you use to query your data. SQL Server 2000 did, usually, return the rows in the order that was specified in the view. New optimizing techniques in SQL Server 2005 have changed that behaviour. -- Hugo Kornelis, SQL Server MVP

how to stop the query

Hi All,
I am executing one query in sql analyzer. It was taking too much of time.I am not able to stop the query using stop button,it was not responding.
Many users are working with that database at the same time,not possible to stop services(agent or server) also.

Any help from you.

Regards
Bharat

Quote:

Originally Posted by bharadwaj

Any help from you.

Regards
Bharat

You could try to disconnect... FILE >> Disconnect

Friday, February 24, 2012

Blog Archive

About Me