problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Problem with 2 Dataset Sharing Parameters

I have a report that will pull out a list of bugs assigned to users for a
period of time (@.StartDate, @.EndDate). Optionally the report has option to
select a single user (@.UserName) or just all of them. There are 2 datasets.
First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate, @.UserName.
Second dataset: UserName. At first I have a query to list all users. Since
the list has hundreds of users while the result set from dataset Bugs mostly
has less than 10 users, I built another query to list only users having bugs
assigned from @.StartDate to @.EndDate. So it requires 2 parameters @.StartDate
and @.Enddate.
Now both datasets use @.StartDate and @.EndDate. The list to select for
@.UserName is produced after @.StartDate and @.EndDate are entered. I expected
the report to let me select the username after I enter @.StartDate and
@.EndDate. However in the preview tab right at the moment when I clicked on
the box for @.EndDate to enter a value the program hang up (VS 2003). I
uploaded it to a report server to see how it behaves then I got this error
after I click on the EndDate box: The value provided for the report
parameter 'EndDate' is not valid for its type.
(rsReportParameterTypeMismatch).
I don't know what's wrong. If I change the dataset UserName to list all
users (no parameter required) then the report runs fine. I use Visual
Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP on
the O/S, SQL, and SQL RS.
An thought?
Thanks.On May 2, 5:31 pm, "ME" <M...@.mail.com> wrote:
> I have a report that will pull out a list of bugs assigned to users for a
> period of time (@.StartDate, @.EndDate). Optionally the report has option to
> select a single user (@.UserName) or just all of them. There are 2 datasets.
> First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate, @.UserName.
> Second dataset: UserName. At first I have a query to list all users. Since
> the list has hundreds of users while the result set from dataset Bugs mostly
> has less than 10 users, I built another query to list only users having bugs
> assigned from @.StartDate to @.EndDate. So it requires 2 parameters @.StartDate
> and @.Enddate.
> Now both datasets use @.StartDate and @.EndDate. The list to select for
> @.UserName is produced after @.StartDate and @.EndDate are entered. I expected
> the report to let me select the username after I enter @.StartDate and
> @.EndDate. However in the preview tab right at the moment when I clicked on
> the box for @.EndDate to enter a value the program hang up (VS 2003). I
> uploaded it to a report server to see how it behaves then I got this error
> after I click on the EndDate box: The value provided for the report
> parameter 'EndDate' is not valid for its type.
> (rsReportParameterTypeMismatch).
> I don't know what's wrong. If I change the dataset UserName to list all
> users (no parameter required) then the report runs fine. I use Visual
> Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP on
> the O/S, SQL, and SQL RS.
> An thought?
> Thanks.
The issue sounds kind of strange. Maybe you should check to make sure
that you have the correct @.EndDate mapping as part of the UserName
dataset (via the Data tab -> Edit Selected Dataset button [...] ->
Parameters tab). Also, make sure that the stored procedure/query that
sources the UserName dataset is not expecting a different data type
for @.EndDate (for whatever reason). Sorry I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the reply. I use datatime as data type for @.StartDate, @.EndDate
in both datasets. Double checked everything but still have no clue why.
When I learned and practiced SQL 2005 I had no problem doing similar thing.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178160515.514988.169100@.e65g2000hsc.googlegroups.com...
> On May 2, 5:31 pm, "ME" <M...@.mail.com> wrote:
>> I have a report that will pull out a list of bugs assigned to users for a
>> period of time (@.StartDate, @.EndDate). Optionally the report has option
>> to
>> select a single user (@.UserName) or just all of them. There are 2
>> datasets.
>> First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate,
>> @.UserName.
>> Second dataset: UserName. At first I have a query to list all users.
>> Since
>> the list has hundreds of users while the result set from dataset Bugs
>> mostly
>> has less than 10 users, I built another query to list only users having
>> bugs
>> assigned from @.StartDate to @.EndDate. So it requires 2 parameters
>> @.StartDate
>> and @.Enddate.
>> Now both datasets use @.StartDate and @.EndDate. The list to select for
>> @.UserName is produced after @.StartDate and @.EndDate are entered. I
>> expected
>> the report to let me select the username after I enter @.StartDate and
>> @.EndDate. However in the preview tab right at the moment when I clicked
>> on
>> the box for @.EndDate to enter a value the program hang up (VS 2003). I
>> uploaded it to a report server to see how it behaves then I got this
>> error
>> after I click on the EndDate box: The value provided for the report
>> parameter 'EndDate' is not valid for its type.
>> (rsReportParameterTypeMismatch).
>> I don't know what's wrong. If I change the dataset UserName to list all
>> users (no parameter required) then the report runs fine. I use Visual
>> Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP
>> on
>> the O/S, SQL, and SQL RS.
>> An thought?
>> Thanks.
>
> The issue sounds kind of strange. Maybe you should check to make sure
> that you have the correct @.EndDate mapping as part of the UserName
> dataset (via the Data tab -> Edit Selected Dataset button [...] ->
> Parameters tab). Also, make sure that the stored procedure/query that
> sources the UserName dataset is not expecting a different data type
> for @.EndDate (for whatever reason). Sorry I could not be of greater
> assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Problem wiht merge replication using pull subscription across internet

Hi,
I want to use merge replication will pull subscription across
internet.
I have 2 computers with SQL Server 2005.
Computer A is the main computer that will have the publication.
Computer B will have a subscription to the Computer A publication.
The distributor will be in Computer B.
Computer A is behind a firewall. The firewall has the following rules
for incoming traffic:
everything denied for all IPs except for the following ports:
20 TCP
21 TCP
22 TCP
23 TCP
25 TCP
53 TCP, UDP
80 TCP
81 TCP
110 TCP
143 TCP
443 TCP
444 TCP
554 TCP
1433 TCP
1755 TCP,UDP
3306 TCP
3389 TCP
5432 TCP
5005 UDP
5800 TCP
5900 TCP
8443 TCP
10000 TCP
25112 TCP
And the following rules for outgoing traffic:
everything denied for all IPs except for the following ports:
20 TCP
21 TCP
22 TCP
23 TCP
25 TCP
53 TCP,UDP
80 TCP
110 TCP
123 UDP
143 TCP
443 TCP
1433 TCP
3306 TCP
5432 TCP
Before trying to set up replication I tried to connect from SSMS from
computer B to A and it worked.
However, trying to connect from SSMS in computer A to computer B
didn't worked.
If I try to connect from another computer (that is not behind the
firewall) to computer B it works.
My question is what outgoing ports need to be opened in the firewall
that protects computer A in order to connect to the SQL Server
instance in computer B?
Will I need to modify my firewall setup if I used push subscription
instead of pull subscription?
Thanks
You would need to open port 1433 for outbound to connect to server b. For a
push server A would connect to its own snapshot share, so this should work
over part 1433 as well.
I would recommend using pull however.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manu" <manunews@.gmail.com> wrote in message
news:1171023660.026202.308300@.a75g2000cwd.googlegr oups.com...
> Hi,
> I want to use merge replication will pull subscription across
> internet.
> I have 2 computers with SQL Server 2005.
> Computer A is the main computer that will have the publication.
> Computer B will have a subscription to the Computer A publication.
> The distributor will be in Computer B.
> Computer A is behind a firewall. The firewall has the following rules
> for incoming traffic:
> everything denied for all IPs except for the following ports:
> 20 TCP
> 21 TCP
> 22 TCP
> 23 TCP
> 25 TCP
> 53 TCP, UDP
> 80 TCP
> 81 TCP
> 110 TCP
> 143 TCP
> 443 TCP
> 444 TCP
> 554 TCP
> 1433 TCP
> 1755 TCP,UDP
> 3306 TCP
> 3389 TCP
> 5432 TCP
> 5005 UDP
> 5800 TCP
> 5900 TCP
> 8443 TCP
> 10000 TCP
> 25112 TCP
> And the following rules for outgoing traffic:
> everything denied for all IPs except for the following ports:
> 20 TCP
> 21 TCP
> 22 TCP
> 23 TCP
> 25 TCP
> 53 TCP,UDP
> 80 TCP
> 110 TCP
> 123 UDP
> 143 TCP
> 443 TCP
> 1433 TCP
> 3306 TCP
> 5432 TCP
> Before trying to set up replication I tried to connect from SSMS from
> computer B to A and it worked.
> However, trying to connect from SSMS in computer A to computer B
> didn't worked.
> If I try to connect from another computer (that is not behind the
> firewall) to computer B it works.
> My question is what outgoing ports need to be opened in the firewall
> that protects computer A in order to connect to the SQL Server
> instance in computer B?
> Will I need to modify my firewall setup if I used push subscription
> instead of pull subscription?
> Thanks
>
|||Hilary,
I had the port 1433 open for outbound connections in server A but it
wasn't working. I was using a named instance in server B with dynamic
ports so after setting the server B to use the port 1433 I can connect
from server A to server B.
Now I'm trying to configure the merge replication (fingers crossed).
Thanks for the help,
Manu
Hilary Cotter wrote:[vbcol=seagreen]
> You would need to open port 1433 for outbound to connect to server b. For a
> push server A would connect to its own snapshot share, so this should work
> over part 1433 as well.
> I would recommend using pull however.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Manu" <manunews@.gmail.com> wrote in message
> news:1171023660.026202.308300@.a75g2000cwd.googlegr oups.com...