Copy one column to another

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

Quote:

Originally Posted by NamelessNumberheadMan

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

try:
update Mapping Set SID = SMappingID
where SMappingID IS NOT NULL|||

Quote:

Originally Posted by NamelessNumberheadMan

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

The query is simple:

UPDATE TABLENAME
SET NEWCOLUMN = SOURCECOLUMN WHERE SOURCECOLUMN IS NOT NULL.

Thanks.

copy field from one table to another

I am trying to copy the values of one table to another one:
Select ProdNum, Name,NYUSAPrice,MPN
into ProductsQuartz [ProdNum,Name,Price,MPN]
from productswithprice
Both table already exist, trying to map the fields but I get an error '
AAlso tried:
Insert into
ProductsQuartz ('ProdNum','Name','Price','MPN')
values ('productswithprice.ProdNum',
'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice.M
PN')
But I get an error, this are fields from another table in the database, how
can I accoplish this ?
A
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:e4Lgt10BFHA.1392@.tk2msftngp13.phx.gbl...
>I am trying to copy the values of one table to another one:
> Select ProdNum, Name,NYUSAPrice,MPN
> into ProductsQuartz [ProdNum,Name,Price,MPN]
> from productswithprice
> Both table already exist, trying to map the fields but I get an error '
> A
>|||Hi
Insert into
ProductsQuartz ('ProdNum','Name','Price','MPN')
Select productswithprice.ProdNum,
productswithprice.Name,productswithprice.NYUSAPrice,productswithprice.MPN
from Productswithprice
Hth
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:O8mV950BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Also tried:
> Insert into
> ProductsQuartz ('ProdNum','Name','Price','MPN')
> values ('productswithprice.ProdNum',
>
'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice.M
PN')
> But I get an error, this are fields from another table in the database,
how
> can I accoplish this ?
> A
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:e4Lgt10BFHA.1392@.tk2msftngp13.phx.gbl...
>|||thx
"AM" <shahdharti@.gmail.com> wrote in message
news:uwvPX$0BFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> Insert into
> ProductsQuartz ('ProdNum','Name','Price','MPN')
> Select productswithprice.ProdNum,
> productswithprice.Name,productswithprice.NYUSAPrice,productswithprice.MPN
> from Productswithprice
> Hth
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:O8mV950BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> 'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice
.M
> PN')
> how
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye
1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>
|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:
> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I would like to copy the structure of a DB (tables,relations, indexes,
> > default values, ecc...) into a new DB on the same server.
> >
> > I don't want to copy the data in the tables. Is this possible? How?
> >
> > Thank you very much
> >
> > Bye
> >
> >
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options
).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

copy datasets between reports

i have a lil query that gets the values for my parameters..
what is the easiest way to copy this whole dataset into another report?
I wish that there were 'shared datasets' so that we could reuse business
logici just ended up opening the XML and cutting and pasting..
it didn't work correctly.. said that the 'aaron' datasource wasn't available
(it is a shared datasource, so that confused me)
I just ended up doing it by hand.. it would just be a LOT nicer to be able
to copy datasets without going into the source
-aaron
<aaron_kempf@.hotmail.com> wrote in message
news:%2378CAEv5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> i have a lil query that gets the values for my parameters..
> what is the easiest way to copy this whole dataset into another report?
> I wish that there were 'shared datasets' so that we could reuse business
> logic
>
>|||Thanks for the suggestion.
The new report controls in the upcoming VS2005 Beta 2 will just bind to
ADO.NET datasets, which would provide a client side solution to "sharing"
datasets. Shared datasets on the server side are on the wishlist for a
future release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<aaron_kempf@.hotmail.com> wrote in message
news:ekPLfWv5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i just ended up opening the XML and cutting and pasting..
> it didn't work correctly.. said that the 'aaron' datasource wasn't
available
> (it is a shared datasource, so that confused me)
> I just ended up doing it by hand.. it would just be a LOT nicer to be able
> to copy datasets without going into the source
> -aaron
>
> <aaron_kempf@.hotmail.com> wrote in message
> news:%2378CAEv5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > i have a lil query that gets the values for my parameters..
> >
> > what is the easiest way to copy this whole dataset into another report?
> >
> > I wish that there were 'shared datasets' so that we could reuse business
> > logic
> >
> >
> >
>

Copy data from one table to another table with change in identity column values

Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

If I understand your question, you want to insert the data from table
test into an already existing table.
>
INSERT existingtable
SELECT a, b
FROM test

I guess it should be:

INSERT existingtable
SELECT 10 + a, b
FROM test

since Salish wanted to change the values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxOn Mon, 20 Aug 2007 21:54:50 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>since Salish wanted to change the values.

Thanks for catching that, Erland.

Roy

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
Why are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegrou ps.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
--
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert
(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), conve
rt(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting varbinary to varchar

I have a password field which is of varbinary. Since its a varbinary Icannot see the password in the database I only see hexadecimal values.Now my question is that how can I convert those hexadecimal values tostring or varchar so I can read the password.
any ideas ??

Not sure if you seen this or not, but it talks about ways to convert binary fields.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Nick

converting to numeric (6,3)

Hey guys,

I am trying to achive the following:

Truncate the values 1680.390000000000000000

to numeric (6,3)

All works ok until i hit a value over 1000, (as above)

The code i was using is:

cos1 = 1680.390000000000000000000

CONVERT (NUMERIC(6 , 3), ROUND(COS1, 3))

I have tried a few others, but nothing seems to work..

I get the following error. "Arithmatic Overflow"

Has anyone got any suggestions?

Thanks guys,

Scotty

A numeric(6,3) would only accept a number as large as 999.999 -six digits total, three digits to the right of the decimal.

Your value, 1680.xxxx exceeds the maximum size and creates an overflow condition -resulting in an error.

Perhaps you need a larger number, like maybe, just guessing here, numeric(8,3)?

|||

Brilliant!!!!

thanks vry much Archie.. this has been confusing me all day,, the way they explain it looks to me as 6 to the left, and 3 to the right...

Thanks again

Scotty

Converting text to numeric

I have a database table with 2 numeric fields. The values to be inserted into these fields come from text boxes in an ASP.NET page. So in the parameters to add into these fields I simply use 'textbox1.text' & textbox2.text'.

One of the fields accepts the data within the text box and adds it into the appropriate numeric data field in the table. However on the other text box I get an error message 'Error converting data type nvarchar to numeric.'

Does anyone know why this occurs just on the one entry and not the other?, and if anyone has any suggestions on how to get around this please let me know.

Many ThanksWhat are the values of textbox1 and textbox2? If you have "fred" in textbox2, of course it cannot be converted to a numeric value.

Converting string to int

Hi,

This probably is a basic question but I can't figure it out...

Because SQL Server's ISNUMERIC function allows some strange values to count as numeric (such as '\'), I want to create my own function that will only return an integer if the value is able to be converted (otherwise it will return 0). I have created the following function to do this:

CREATE FUNCTION [dbo].[fnConvertToInt]
(
@.str nvarchar(30)
)
RETURNS int
AS
BEGIN
DECLARE @.s int

BEGIN TRY
SET @.s = convert(int, @.str)
END TRY
BEGIN CATCH
SET @.s = 0
END CATCH
-- Return the result of the function
RETURN @.s
END

When I run this however, I get errors from having the TRY CATCH in a function:

Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

How can I convert a string to an integer without getting an error? I am using SQL Server 2005.

Hi,

the isnumeric function is hazle, but you can implement some isreallynumeric, like aaron did this on his website:

http://www.aspfaq.com/show.asp?id=2390

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

converting stored procedure

CREATE PROCEDURE procCreateBasket

@.ShopperID int,

@.BasketID int OUTPUT

AS

INSERT INTO Basket(ShopperID)

VALUES (@.ShopperID)

SELECT @.BasketID = @.@.IDENTITY
I don't want to use this as a stored procedure I want to convert it as a string an use in that way in my asp.net application how can I do that?

Simply just take out the procedure declaration. For example, your string will be everything after the AS keyword.|||Adding to what master4eva said, you should be using SCOPE_IDENTITY()instead of @.@.IDENTITY. The former returns a more accurate value of theId just inserted as compared to @.@.IDENTITY. Check out books on line for more info.

Converting report input dates to UTC

I have created a report in reporting services and it takes a number of
DateTime values as input parameters. These values will be passed on to
queries in my database; the problem is that my database stores DateTime
values as UTC.
Is there a way I can convert them to UTC before the values are sent to the
query?Hello,
You could try this:
DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @.LocalDate)
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Converting report input dates to UTC
>thread-index: AcYtdvuTa+Zu+2dZQC6M3qdl1xmC3Q==>X-WBNR-Posting-Host: 195.139.24.170
>From: "=?Utf-8?B?Q2hyaXN0b3BoZXIgS2ltYmVsbA==?="
<c_kimbell@.newsgroup.nospam>
>Subject: Converting report input dates to UTC
>Date: Thu, 9 Feb 2006 04:47:27 -0800
>Lines: 7
>Message-ID: <920BBD92-A5C6-4A43-B965-8D1AE0151687@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68388
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I have created a report in reporting services and it takes a number of
>DateTime values as input parameters. These values will be passed on to
>queries in my database; the problem is that my database stores DateTime
>values as UTC.
>Is there a way I can convert them to UTC before the values are sent to the
>query?
>

Converting numeric values to String in MDX

Is it possible to convert a numeric value to a string using MDX functions? I am defining a goal for my KPI and i need to display two values for the goal, (a percentage value like 75% and an actual value e.g. ￡200K) so i was thinking of converting both values to a string and then concatenate the two strings for the KPI goal. Is it possible to do this OR is there a better way of doing it?

Thanks in advance.

I'm not sure if this is the best way of handling the issue but it is technically possible. Here is a code sample illustrating one string assembly:

Code Snippet

with member [Measures].[Category Sales String] as

"*** " + CSTR([Measures].[Reseller Sales Amount].Value) + " ***"

select

[Measures].[Category Sales String] on 0,

[Product].[Category].Memberson 1

from [Adventure Works]

|||

Yes, certainly - just use the Cstr function (which is, by the way, native MDX although it behaves the same as the VBA function of the same name - see http://www.e-tservice.com/Files/vba_functions_in_as2005.doc) to cast the values to a string. For example:

Code Snippet

with member measures.test as

cstr([Measures].[Internet Sales Amount]) + " Hello!"

select measures.test on 0 from

[Adventure Works]

However, I'm not sure that concatenating these values is really a good idea. Why not create an extra dimension, or an attribute on a dimension, which has one real member and one calculated member and then allow the users to switch between slicing these members to display actual values and percentages? This is what's referred to as a shell dimension or a time utility dimension, and you can find out more about how to implement one of these here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

HTH,

Chris

|||Thanks a lot this is a real life saver for me. I want to concatenate because, I am displaying these values on a dashboard and i just want to display everything on the same screen.

Converting negative value

Hi!

I have a large table width values like this:

000024634300
000-37500000
002783868891
000009603857
000-60000000
000001672396
000000195200
000010315112
000017000000

I need to convert the numbers into an integer-type field. Is this
possible with just native sql-commands? I don't want to make an
active-x script because it will be very slow on the large table.

EspenSELECT col,
CASE WHEN col LIKE '%-%' THEN -1 ELSE 1 END*
CAST(REPLACE(col,'-','') AS NUMERIC(12))
FROM SomeTable

The value 2,783,868,891 exceeds the maximum for an INTEGER column so I've
used NUMERIC here. You could also use BIGINT.

--
David Portas
SQL Server MVP
--