Incorrect values in RestoreHistory table

We have a SQL Server which is setting the wrong recovery bit in the table
msdb..restorehistory
i.e.
when databases restored WITH RECOVERY the recovery field has a value of 0
when databases restored WITH NORECOVERY the recovery field has a value of 1
I cannot find out why this is happening. Please assist
ThanksThe BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
>--Original Message--
>We have a SQL Server which is setting the wrong recovery
bit in the table
>msdb..restorehistory
>i.e.
>when databases restored WITH RECOVERY the recovery field
has a value of 0
>when databases restored WITH NORECOVERY the recovery
field has a value of 1
>I cannot find out why this is happening. Please assist
>Thanks
>
>.
>|||Thanks,
I thought I had verified this with the other servers, but
after double checking with the scritped test below, I
notice that you are correct
Is there any way to send feedbacks to Microsoft about
this, as I frequently find such things
/************Test RestoreHistory Entries******************/
create database test
backup database test to disk = '%temp%\t'
restore database test from disk = 't'
restore database test from disk = 't' with norecovery
restore database test from disk = 't' with recovery
select * from msdb..restorehistory where
destination_database_name = 'test' order by restore_date
drop database test
declare @.bdir varchar(255)
exec
master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\MSSQLServer',
'BackupDirectory', @.bdir OUTPUT
set @.bdir = 'del "'+@.bdir+'\t"'
exec master..xp_cmdshell @.bdir
/*********************************************************/
>--Original Message--
>The BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
>>--Original Message--
>>We have a SQL Server which is setting the wrong recovery
>bit in the table
>>msdb..restorehistory
>>i.e.
>>when databases restored WITH RECOVERY the recovery field
>has a value of 0
>>when databases restored WITH NORECOVERY the recovery
>field has a value of 1
>>I cannot find out why this is happening. Please assist
>>Thanks
>>
>>.
>.
>|||Mike,
> Is there any way to send feedbacks to Microsoft about
> this, as I frequently find such things
Yes, there's a feedback option in Books Online. Top left of the right pane.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:0ea801c3b2d4$cbe095d0$a001280a@.phx.gbl...
> Thanks,
> I thought I had verified this with the other servers, but
> after double checking with the scritped test below, I
> notice that you are correct
> Is there any way to send feedbacks to Microsoft about
> this, as I frequently find such things
> /************Test RestoreHistory Entries******************/
> create database test
> backup database test to disk = '%temp%\t'
> restore database test from disk = 't'
> restore database test from disk = 't' with norecovery
> restore database test from disk = 't' with recovery
> select * from msdb..restorehistory where
> destination_database_name = 'test' order by restore_date
> drop database test
> declare @.bdir varchar(255)
> exec
> master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
> \MSSQLServer\MSSQLServer',
> 'BackupDirectory', @.bdir OUTPUT
> set @.bdir = 'del "'+@.bdir+'\t"'
> exec master..xp_cmdshell @.bdir
> /*********************************************************/
>
> >--Original Message--
> >The BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
> >>--Original Message--
> >>We have a SQL Server which is setting the wrong recovery
> >bit in the table
> >>msdb..restorehistory
> >>
> >>i.e.
> >>when databases restored WITH RECOVERY the recovery field
> >has a value of 0
> >>when databases restored WITH NORECOVERY the recovery
> >field has a value of 1
> >>
> >>I cannot find out why this is happening. Please assist
> >>
> >>Thanks
> >>
> >>
> >>.
> >>
> >.
> >sql

Incorrect syntax near the keyword 'CONVERT'

What in the name of all creation is wrong with this query? I just can't seem to find the trouble...

EXEC OppdaterForetak 900000000,'NO NON MOD DUNS Test 1','abcdveien 145','110','OSLO',NULL,'118','OSLO','22222222','22222222',CONVERT(datetime,'2006-07-18 00:00'), 101,'OSLO', 3,'Oslo','AS', 99999,'NB! TESTFORETAK',NULL,NULL,NULL, 345000001,CONVERT(datetime,'2006-07-17 00:00'),CONVERT(datetime,'2006-07-19 00:00'),'A',NULL,'Daglig leder alene.'

You can't use any function when you call the Procedure. It should be predefined.

Here you can use the variable to assign the values then reuse the varibale on the procedure call.

But for your query convertion is not nessasary.. since you are using the valid dateformat..

Code Snippet

EXEC OppdaterForetak 900000000,

'NO NON MOD DUNS Test 1',

'abcdveien 145',

'110',

'OSLO',

NULL,

'118',

'OSLO',

'22222222',

'22222222',

'2006-07-18 00:00:00',

101,

'OSLO',

3,

'Oslo',

'AS',

99999,

'NB! TESTFORETAK',

NULL,

NULL,

NULL,

345000001,

'2006-07-17 00:00:00',

'2006-07-19 00:00:00',

'A',

NULL,

'Daglig leder alene.'

Incorrect syntax near [SQL UPDATE COMMAND] > cmd.ExecuteNonQuery()

Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot.

-------------------------------------

public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

using (cn)
{
cn.Open();
return cmd.ExecuteNonQuery() > 1;
}
}

capture the return value to a variable and return that. You are trying to use a shortcut that doesn't exist.

|||

I added a variable that will hold the INTEGER value of the rows updated, but it still generates the same error.
-----------------------------------------
public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

int rowsAffected;

using (cn)
{
cn.Open();
rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected > 1;
}
}

|||

try this

If (rowsAffected >1){

return true;}

else {

return false;}

|||

No luck... It still generates the same error even with the IF condition in place. Here is a copy of the error trace from the browser:

--------------------------------

Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Source Error:

Line 215: {
Line 216: cn.Open();
Line 217: rowsAffected = cmd.ExecuteNonQuery();
Line 218:
Line 219: if (rowsAffected > 1)

|||

post your stored procedure. This is where your problem is.

|||

I don't see the point of using rowsaffected >1

Why don't you just use return rowsAffected?

Isn't that going to give you the count anyway?

|||

Thanks for the quick reply.:

------------------------------

Create procedure UpdateInfoByAccountAndFullName(
@.newaccount varchar(50),
@.newfullname varchar(100),
@.rep varchar(10),
@.zip varchar(10),
@.comments varchar(2000),
@.oldaccount varchar(50),
@.oldfullname varchar(100)
)
as

update Info
Set ACCOUNT = @.newaccount,
FULL_NAME = @.newfullname,
REP = @.rep,
ZIP = @.zip,
COMMENTS = @.comments
where
ACCOUNT = @.oldaccount and
FULL_NAME = @.oldfullname

|||

Duduvi,

Why don't you just use return rowsAffected? Isn't that going to give you the count anyway? > I could use rowsAffected, but I don't really see the need of taking into account how many records where updated. This is why I used the boolean rowsAffected > 1. I just need to know that more than or 1 record(s) were updated.

|||

I don't see a problem. Try removing the return value. Just try executing the query and see if it works then you can eliminate the stored procedure as the problem.

|||

You are right. I dont see the problem neither. This is what freaks me out too. I already tried running the StoredProc and the SQL String from within SQL2005, and it did update the record. The only time it throws an exception is on my .NET code when I do a ExecuteNonQuery() with or without a return value.

|||

HA! I can't believe i missed it. you forgot to specify that your command is a stored procedure using the commandtype enum.

|||

Yep you're right. I forgot to specify the command type. Thanks again.

Incorrect syntax

I'm getting a pop up error "Incorrect syntax near the keyword 'ORDER'.

Can someone please tell me what is wrong with my code? It worked before I added the underlined part.

If FoundChecked =TrueThen

Dim SQLStringAsString

SQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "

ForEach ItemIn LocationList.Items

If Item.SelectedThen

SQLString &=" Location = '" & Item.Value &"' OR " &""&" ORDER BY Location" -------This is the problem

EndIf

The sql is going to look something like this (which is wrong):

SELECT ID, Bedrooms, Bathrooms, Location, RentFROM ListingsTableWHERE Location ='Item1'ORORDER BY LocationLocation ='Item2'ORORDER BY Location

|||I believe the problem is with the OR before ORDER BY. OR should be used if you want to say something like Location = "5" OR Location = "10" ORDER BY Location.|||

SGWellens:

The sql is going to look something like this (which is wrong):

SELECT ID, Bedrooms, Bathrooms, Location, RentFROM ListingsTableWHERE Location ='Item1'ORORDER BY LocationLocation ='Item2'ORORDER BY Location

What do you suggest?

I know this works:

SQLString &=" Location = '" & Item.Value &"' OR " &""
 
but I would like to have the ORDER added in somehow. Thanks.

|||

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

|||

SGWellens:

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

I'm sorry, but that isn't helping me. I need to know how to changemy code which is:

SQLString &=" Location = '" & Item.Value &"' OR " &""

to incorporate ORDER BY Location.

|||

You said it works before you added the ORDER BY Location to the end of the string.

Go ahead and run the For Loop that works and just after the For Loop, after NEXT, rebuild the string.

I use parameterized statements so I haven't concatenated statements for a while. Any way, tack the "ORDER BY Location" on the end of SQLString after the Loop is finished.

You'll have to clean up the syntax (comments). Because I've probaly got it wrong.

For blah
IF blah Then
SQLString = "blah"
End If
Next

SQLString ="SQLString" &"ORDER BY Location"

|||

Hi prk72,

You cannot use ORDER BY that way in your query clause. The solution has been given bySGWellens

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

You can modify your code based on the solutionSGWellens has suggested you (Actually our community memberhypercode has already told you how to modify you code)

CODE EXAMPLE:

 
If FoundChecked = True Then Dim SQLString As StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE " For Each Item In LocationList.Items If Item.Selected ThenSQLString &=" Location = '" & Item.Value &"'OR" End If
 
SQLString & =" 1=2 ORDER BY Location"
 

|||

You can use,

Dim SQLStringAs StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "For Each ItemIn LocationList.ItemsIf Item.SelectedThen SQLString &=" Location ='" & Item.Value & "' OR "End IfSqlString &= " 1=2 ORDER BY Location"

or

Dim SQLStringAs StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE Location in ("For Each ItemIn LocationList.ItemsIf Item.SelectedThen SQLString &="'" & Item.Value & "', "End IfSqlString &= "'') ORDER BY Location"

Incorrect Information in SQL Agent\Operator History

Apologies if this is in the wrong forum.

I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.

Not a major bug for me, but does anyone else have this problem?

--

DDL statement for operator.

EXEC msdb.dbo.sp_add_operator @.name=N'Rich',

@.enabled=1,

@.weekday_pager_start_time=90000,

@.weekday_pager_end_time=180000,

@.saturday_pager_start_time=90000,

@.saturday_pager_end_time=180000,

@.sunday_pager_start_time=90000,

@.sunday_pager_end_time=180000,

@.pager_days=0,

@.email_address=N'rich@.rich.net,

@.category_name=N'[Uncategorized]'

Yup.

Same problem here.

Rob

|||

There is a hotfix for this.

I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.

|||

This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.

Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.

(Please move to the Tools forum)

Incorrect Information in SQL Agent\Operator History

Apologies if this is in the wrong forum.

I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.

Not a major bug for me, but does anyone else have this problem?

--

DDL statement for operator.

EXEC msdb.dbo.sp_add_operator @.name=N'Rich',

@.enabled=1,

@.weekday_pager_start_time=90000,

@.weekday_pager_end_time=180000,

@.saturday_pager_start_time=90000,

@.saturday_pager_end_time=180000,

@.sunday_pager_start_time=90000,

@.sunday_pager_end_time=180000,

@.pager_days=0,

@.email_address=N'rich@.rich.net,

@.category_name=N'[Uncategorized]'

Yup.

Same problem here.

Rob

|||

There is a hotfix for this.

I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.

|||

This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.

Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.

(Please move to the Tools forum)

Incorrect Information in SQL Agent\Operator History

Apologies if this is in the wrong forum.

I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.

Not a major bug for me, but does anyone else have this problem?

--

DDL statement for operator.

EXEC msdb.dbo.sp_add_operator @.name=N'Rich',

@.enabled=1,

@.weekday_pager_start_time=90000,

@.weekday_pager_end_time=180000,

@.saturday_pager_start_time=90000,

@.saturday_pager_end_time=180000,

@.sunday_pager_start_time=90000,

@.sunday_pager_end_time=180000,

@.pager_days=0,

@.email_address=N'rich@.rich.net,

@.category_name=N'[Uncategorized]'

Yup.

Same problem here.

Rob

|||

There is a hotfix for this.

I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.

|||

This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.

Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.

(Please move to the Tools forum)

Incorrect host-column number found in BCP format-file

Hi guys, would appreciate if you can shed some light on this.

Sorry to be a pain, can you tell me what is wrong with the following:

for /F %%i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in
%%i -fc:\bcp\bcp.fmt -T -S CHICKYy
where CHICKYy is the server

bcp.fmt

8.00.194
6
1 SQLCHAR 0 20 ", " 0 filler_1 ""
2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
3 SQLCHAR 0 20 ", " 0 filler_2 ""
4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
5 SQLCHAR 0 20 ", " 0 filler_3 ""
6 SQLCHAR 0 60 "\r\n" 3 operating_system ""

pc1.txt and other *.txt format is:

JW_193801,
192.168.1.1,
Windows XP,

when I run it I get:

C:\bcp>for /F %i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in
%i -fc:\bcp\bcp.fmt -T -S CHICKYy

C:\bcp>bcp Inventory..pc in pc1.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc2.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc3.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc4.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc5.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

The sql table has 3 columns:

Sorry to be a pain.

--

---------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.comMichelle Hillard (mhillard@.craized.tv) writes:
> bcp.fmt
> 8.00.194
> 6
> 1 SQLCHAR 0 20 ", " 0 filler_1 ""
> 2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
> 3 SQLCHAR 0 20 ", " 0 filler_2 ""
> 4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
> 5 SQLCHAR 0 20 ", " 0 filler_3 ""
> 6 SQLCHAR 0 60 "\r\n" 3 operating_system ""
>...
> C:\bcp>for /F %i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in
> %i -fc:\bcp\bcp.fmt -T -S CHICKYy
> C:\bcp>bcp Inventory..pc in pc1.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> found in BCP format-file

BCP's is not famous for its self-explanatory messages.

My guess goes to the version number. It should say 8.0, not 8.00.194.

(And if you are running 8.00.194 somewhere, you should download and
install SP3 for SQL Server to get a couple of important bug and
security fixes.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns th
e
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:

> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if th
is
> problem might be Windows or hardware based. I did this same test on sever
al
> different machines, (dual Xeons and P4 laptops) and every time it returns
the
> incorrect result.

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.
This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.
|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:

> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 â'1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.

Include "/" in select statement

I wanted to create something like this:
Select (FirstItem + "/" + SecondItem) AS Item

but I get error. Is there anything wrong with this code?

P.S. I'm using mssql 2000

Hi,

in case FirstItem and SecondItem field are varchar type themselves, just replace double quotes ("/") with single quotes ('/')

e.g Select (FirstItem +'/' + SecondItem) AS Item

|||

Adding tojoteke's solution, if the columns are not of same datatype, you would do a CONVERT.

SELECT CONVERT(varchar(50),FirstItem ) +'/' + CONVERT(varchar(50),secondItem) AS Item

In the server explorer "generate create script" is greyed out, whats wrong?

In the server explorer "generate create script" is greyed out, what am I doing wrong?
I want to generate a script that will re-create my DB schema on another machine.
Any help would be appreciated.
ThanksFirst of all you have to use the proper names otherwise we dont know what you want. "enterprise manager no server explorer"

ok that was my anal retentive dba!!!
now

Books online {Documenting and Scripting Databases}

at the bottom of this doc are instructions on how to do it.|||First of all you have to use the proper names otherwise we dont know what you want. "enterprise manager no server explorer"

ok that was my anal retentive dba!!!
now

Books online {Documenting and Scripting Databases}

at the bottom of this doc are instructions on how to do it.

Ill be more clear, Im Using visual studio 2003, In my server explorer window, when I right click on my DB the "generate create script" option is greyed out. I would like to know why its greyed out?
Im guessing by your answer that this faeture is only available on "enterprise manager" edition? Can someone please confirm this for me?
Thankyou|||first of all sorry i thought that you were in the SQL tool directly , it didnt occur to me that you were in VS7

nope, object scripting is a feature that is avilable in every version of SQL Server. this might be a specific trait of VS7 and i dont currently have it installed anywhere to check.
sorry

RDjabarov can help here, he uses VS7