Showing posts with label write. Show all posts
Showing posts with label write. Show all posts

Wednesday, March 28, 2012

how to assigned float number to DB ?

how can i assigned a float number with only one digit after the dot to the DB ?
i mean that i want to write this number 0.1 and not this 0.100000000 (that
what the sql does for me now..)
im using a storage procedure for this purpuse, so maybe the problem is there..
thanks !!
Hello,
Use Numeric instead of FLOAT datatype.
Thanks
Hari
"guy" <guy@.discussions.microsoft.com> wrote in message
news:04D80E57-189C-4C31-8729-1511B073B490@.microsoft.com...
> how can i assigned a float number with only one digit after the dot to the
> DB ?
> i mean that i want to write this number 0.1 and not this 0.100000000 (that
> what the sql does for me now..)
> im using a storage procedure for this purpuse, so maybe the problem is
> there..
> thanks !!
>
|||but if its Numeric it cant save me the 0.1 ... (or maybe i dont know how to
do this..) it save it like it was zero..
"Hari Prasad" wrote:

> Hello,
> Use Numeric instead of FLOAT datatype.
> Thanks
> Hari
> "guy" <guy@.discussions.microsoft.com> wrote in message
> news:04D80E57-189C-4C31-8729-1511B073B490@.microsoft.com...
>
>
|||Hello,
Use the below sample:-
declare @.t numeric(2,1)
set @.t = 0.1
select @.t
Thanks
Hari
"guy" <guy@.discussions.microsoft.com> wrote in message
news:590D1DF2-C360-45BB-9675-52533F59AABC@.microsoft.com...[vbcol=seagreen]
> but if its Numeric it cant save me the 0.1 ... (or maybe i dont know how
> to
> do this..) it save it like it was zero..
> "Hari Prasad" wrote:
|||On Sat, 23 Jun 2007 01:38:00 -0700, guy wrote:

>how can i assigned a float number with only one digit after the dot to the DB ?
>i mean that i want to write this number 0.1 and not this 0.100000000 (that
>what the sql does for me now..)
>im using a storage procedure for this purpuse, so maybe the problem is there..
>thanks !!
Hi Guy,
The only difference between 0.1 and 0.100000 is representation; their
values are exactyl equal. Since the internal representation for numeric
datatypes is different from what you see anyway, this question really
can't be answered. Whether you assign 0.1, 0.10000 or 0.2/2 to a float
variable or column, the result will always be the same.
So, if you want to STORE numbers with only one decimal place, then
Hari's answer is correct - you can't do that with float numbers, but you
can with the datatype numeric. If, on the other hand, you want to store
float numbers but REPRESENT them with one digit after the decimal place,
check out the subjects STR and CONVERT in Books Online. Note that these
functions convert a float value to a string representation, so the value
is no longer considered a number after this conversion!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Friday, March 23, 2012

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- Charlie
Update table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>
|||Update table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- CharlieUpdate table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- CharlieUpdate table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming Lu
EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>
|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
to
> the
>
sql

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming LuEXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
to[vbcol=seagreen]
> the
>

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming LuEXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> > I am trying to write a script to change a column name (from "User_Name"
to
> > "Customer_Name") with the same datatype, etc.
> > Is there a stored procedure that I can use to do this? or I have to drop
> the
> > old column, adding the new column and copy over the data?
> >
> > Thanks.
> >
> > --C. Ming Lu
> >
> >
>

Sunday, February 19, 2012

How to accomplish what I am trying to do?

Hello,

I need to write a package the does the following, and not sure the best approach:

The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.

That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.

Also, each time I create the new output file, I need to delete the old named file.

I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.

Any help appreciated!!

Thank you

You can use an expression on your flat file connection manager to change the file name at runtime. Just set the ConnectionString property. To replace the file, make sure your Flat File destination has Overwrite data enabled.

Do all your stored procs return the same resultset? If not, using a data flow will probably not work for you, so you wouldn't be able to use a Flat File destination.

|||

Hi John,

You say that if my procs do not return the same result set a data flow will probably not work?

What can I use instead?

I am having serious problems trying to get the output from my stored procedure loops to flat files.

That is, I added a data flow task to the foreach loop, so that each execsql task will run a stored proc then write the output to a file. But it seems this is not possible?

I find it hard to believe... but is there a workaround?

|||You could use a custom script task inside your loop to execute the procedures, read the results, and write them to a file.

How to access to excel,csv or other formatted file using OLE DB(SQL Native Client)?

Hi~.

First, is it possible to access(read/write) to the excel,csv or my-own-formatted file using OLE DB(SQL Native Client)?

If possible, how?

You can create linked server to the excel spreadsheet as shown in : http://msdn2.microsoft.com/en-us/library/ms190479.aspx

You can use OpenRowset (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

or OpenQuery( http://msdn2.microsoft.com/en-us/library/ms188427.aspx) to retrieve data.

Providing an example using VB6 / ADO using SQLNCLI

Sub main()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=SQLNCLI;Data Source=<datasourcename>;Integrated Security=SSPI;"

'Refer to table using linked server
rs.Open "select * from <linkedsrvname>...TestTable", cn

'OpenRowset
Set rs = cn.Execute("select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\TestBook.xls','select * from [TestTable]')")

'OpenQuery
Set rs = cn.Execute("select * from OpenQuery(ExcelShare,'select * from TestTable')")

While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields.Item(i)
Next i
rs.MoveNext
Wend

Set rs = Nothing
cn.Close

End Sub