Sql Stop Logging: format

Showing posts with label format. Show all posts

Friday, March 30, 2012

How to trim leading zeros from a varchar column?

My table has a column named [Liability] varchar datatype which has the data in the format

(

3535.00,

00393.99,

00Loan,

0.00,

.00

*.00

)

I want to trim the leading zeros so that the output should be(trim only the leading zeros)

(

3535.00,

393.99,

Loan,

0.00,

.00

*.00

)

Can someone show my the sql statement for this?

Thanks.

Code Snippet

--To ignore leading '0' and <SPACE> characters:

select substring(Liability,patindex('%[^0 ]%',Liability),8000)

from <MyTable>

--To ignore ONLY leading '0' characters:

select substring(Liability,patindex('%[^0]%',Liability),8000)

from <MyTable>

|||

rusag2,

I think that your suggested solution will also remove the leading zero from [ 0.00 ] which 'should' be retained.

Perhaps something like this would be closer to the desired output:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( '3535.00' )
INSERT INTO @.MyTable VALUES ( '00393.99' )
INSERT INTO @.MyTable VALUES ( '00Loan' )
INSERT INTO @.MyTable VALUES ( '0.00' )
INSERT INTO @.MyTable VALUES ( '00.00' )
INSERT INTO @.MyTable VALUES ( '.00' )
INSERT INTO @.MyTable VALUES ( '*.00' )

SELECT

CASE
WHEN isnumeric( MyValue ) = 1 THEN cast( cast( MyValue AS decimal(18,2)) AS varchar(20))
ELSE substring( MyValue, patindex('%[^0]%', MyValue ), 20 )
END
FROM @.MyTable

3535.00
393.99
Loan
0.00
0.00
0.00
*.00

|||

Thanks Arnie.

That worked!!!!!

sql

Wednesday, March 28, 2012

how to transfer gdb format data to SQL server

My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??Assuming that you have an ODBC or OLEDB driver that allows you to use the GDB file, you could use that.

-PatP|||I feel this .GDB is from mainframe or unix if so as suggested use the compatible odbc driver.|||Thank you everybody
I found ODBC driver and everythink's working well (for the time being at least:))

Monday, March 12, 2012

how to sync PDA data (in XML format)with remote database (SQL)

hello everyone,
i am developing a application using C#and .NEt compact framework.
for that i need to sync pda data (in xml format) with the remote database server(SQL) and vice versa.
please help me out
thanks in advance.I think using CE version of windows & SQL you can workaround this issue. I suggest you to refer to http://www.microsoft.com/sql (SQL homepage) website for more details about CE products.

HTH|||satya i am using pocket pc 2002/2003 not win ce and on pocket pc i am using xml as a database and on my central server i am using sql serve.
and thanks for the reply. please guide me in this direction.
regards|||May try this SDK download (http://www.microsoft.com/windowsmobile/information/devprograms/default.mspx?gssnb=1) site for any reference.

Have you gone thru MSSQL homepage earlier?|||no i havent tried mssql before that. should i go through with or not.
one more problem how can i embed the IE (internet explorer) in my application so that from within my application i would be able to see reports generated by the server.
please|||I think you would be better of to go thru the referred websites to get more information.

I don't have exp. with other tools so cannot offer any discussion.:(

How to switch off the toolbar params

This is a multi-part message in MIME format.
--=_NextPart_000_001C_01C56790.2E2B5330
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have a report that is launched from a windows app using URL access. Is = it possible to turn off the parameters section of the toolbar without = turning off the other options. I have tried using &rc:Toolbar=3DFalse = but tis turns off the entire toolbar and not just the paramater input = boxes. Any ideas?
--=_NextPart_000_001C_01C56790.2E2B5330
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have a report that is launched from a = windows app using URL access. Is it possible to turn off the parameters section of = the toolbar without turning off the other options. I have tried using &rc:Toolbar=3DFalse but tis turns off the entire toolbar and not = just the paramater input boxes. Any ideas?
--=_NextPart_000_001C_01C56790.2E2B5330--This is a multi-part message in MIME format.
--=_NextPart_000_00BD_01C567BD.51B651B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What about rc:Parameters=3Dfalse ?
-- HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im =Newsbeitrag news:uvrO5e4ZFHA.3492@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched from a windows app using URL access. =Is it possible to turn off the parameters section of the toolbar without =turning off the other options. I have tried using &rc:Toolbar=3DFalse =but tis turns off the entire toolbar and not just the paramater input =boxes. Any ideas?
--=_NextPart_000_00BD_01C567BD.51B651B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

What about rc:Parameters=3Dfalse =?
-- HTH, Jens Suessmeyer.
--http://www.sqlserver2005.de">http://www.sqlserver2005.de-=--
"Aidan Garnish" schrieb im Newsbeitrag news:uvrO5e4ZFHA.3492=@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched from =a windows app using URL access. Is it possible to turn off the parameters =section of the toolbar without turning off the other options. I have tried using &rc:Toolbar=3DFalse but tis turns off the entire toolbar and not =just the paramater input boxes. Any =ideas?

--=_NextPart_000_00BD_01C567BD.51B651B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0042_01C5681F.AD1CFC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perfect...thank you
"Jens S=FC=DFmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> =wrote in message news:ucBZOy6ZFHA.3976@.TK2MSFTNGP15.phx.gbl...
What about rc:Parameters=3Dfalse ?
-- HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im =Newsbeitrag news:uvrO5e4ZFHA.3492@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched from a windows app using URL =access. Is it possible to turn off the parameters section of the toolbar =without turning off the other options. I have tried using =&rc:Toolbar=3DFalse but tis turns off the entire toolbar and not just =the paramater input boxes. Any ideas?
--=_NextPart_000_0042_01C5681F.AD1CFC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Perfect...thank you
"Jens S=FC=DFmeyer" wrote in message news:ucBZOy6ZFHA.3976=@.TK2MSFTNGP15.phx.gbl...
What about rc:Parameters=3Dfalse ?
-- HTH, Jens Suessmeyer.

--http://www.sqlserver2005.de">http://www.sqlserver2005.de-=--
"Aidan Garnish" schrieb im Newsbeitrag news:uvrO5e4ZFHA.3492=@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched =from a windows app using URL access. Is it possible to turn off the parameters =section of the toolbar without turning off the other options. I have tried =using &rc:Toolbar=3DFalse but tis turns off the entire toolbar and not =just the paramater input boxes. Any ideas?

--=_NextPart_000_0042_01C5681F.AD1CFC40--|||rc:parameters=collapsed can work,too
(sp1 later version)
"Aidan Garnish" wrote:
> Perfect...thank you
> "Jens SÃ¼Ã?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message news:ucBZOy6ZFHA.3976@.TK2MSFTNGP15.phx.gbl...
> What about rc:Parameters=false ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im Newsbeitrag news:uvrO5e4ZFHA.3492@.TK2MSFTNGP14.phx.gbl...
> I have a report that is launched from a windows app using URL access. Is it possible to turn off the parameters section of the toolbar without turning off the other options. I have tried using &rc:Toolbar=False but tis turns off the entire toolbar and not just the paramater input boxes. Any ideas?

Friday, February 24, 2012

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.........