Problems instancing Reporting Services

I set up Reporting Services as a Web Reference and when I try to instance it, I get a "type expected" error message. Let's say the web reference is named SQLRS. When I do a DIM rs as NEW SQLRS, a blue squiggly appears under SQLRS and it displays the type expected error message.

What am I missing? Anyone know or have a link to an example I could look at? Thanks for any help.Additional information:

When I bring up the Reporting Services as a web reference, I don't get a drop down list of the methods and properties that are listed in the Add a Web Reference dialogue box and from what I've seen in examples online. For instance, there should be a ListChildren method, but I don't see it when intellisense kicks in.

Example: Let's say I name the web service ReportingService. If I try to instance it by going DIM rs as NEW ReportingService, I get the blue squiggly line complaining that "Type expected". If I type ReportingService. intellisense kicks in, but what appears in the drop down box is not what I expect. There are no ListChildren, ListEvents, ListJobs, etc.

What I suspect is that I need to DIM the ReportingService with one of the selections from the intellisense dropdown box, but I have no idea which one as all the examples I've seen online show differently. Has there been changes to this with service pack 2 that I'm not aware of? Any help would be appreciated.|||Ok, I just love answering my own questions.

It turns out that all the examples I've been trying to follow are a bit off. To create an instance of the Reporting Services as a web service, first add the web service as a web reference to your project. (i.e., right click on references in the solution explorer and type in the web address of the reporting services: http://myserver/ReportServer/ReportService.asmx)

Then, to create an instance, use something like:

DIM rs as NEW ReportingService.ReportingService

Now you will have an object called rs that will have the properties and methods of the ReportingService web service such as ListChildren, ListJobs, Render, etc.

Problems inserting datetime object into sql server 2005

hey everyone,

Im trying run a stored procedure, but I am getting the following message when trying to execute it.

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.

It should also be noted that when I execute this sproc from within sql management studio, it executes fine.

This is the stored procedure im trying to execute...

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

@.request_id INT,
@.request_client_id INT,
@.request_description VARCHAR(150), @.request_date DATETIME,
@.request_edit_date DATETIME, @.request_status VARCHAR(25), @.request_approve_date DATETIME,
@.request_activity_code VARCHAR(7), @.request_department_code VARCHAR(3), @.request_participants INT,
@.request_activity_date DATETIME, @.request_activity_due_date DATETIME,
@.request_company_id INT, @.request_company_code INT,
@.request_notes VARCHAR(2000)

AS
BEGIN

UPDATE invoice_requests

SET request_client_id = @.request_client_id, request_description = @.request_description,
request_date = @.request_date, request_edit_date = @.request_edit_date,
request_status = @.request_status, request_approve_date = @.request_approve_date,
request_activity_code = @.request_activity_code,
request_department_code = @.request_department_code,
request_participants = @.request_participants,
request_activity_date = @.request_activity_date,
request_activity_due_date = @.request_activity_due_date,
request_company_code = @.request_company_code, request_company_id = @.request_company_id,
request_notes = @.request_notes

WHERE request_id = @.request_id

END
GO

in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...

please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...

exec sp_executesql
N'EXECUTE sp_UpdateRequest
@.request_id,
@.request_client_id,
@.request_description,
@.request_date,
@.request_edit_date,
@.request_status,
@.request_approve_date,
@.request_activity_code,
@.request_participants,
@.request_activity_date,
@.request_activity_due_date,
@.request_company_id,
@.request_company_code,
@.request_notes',

N'@.request_id int,
@.request_client_id int,
@.request_description varchar(8000),
@.request_date datetime,
@.request_edit_date datetime,
@.request_status varchar(5),
@.request_approve_date datetime,
@.request_activity_code varchar(8000),
@.request_department_code varchar(8000),
@.request_participants int,
@.request_activity_date datetime,
@.request_activity_due_date datetime,
@.request_company_id int,
@.request_company_code int,
@.request_notes varchar(8000)',

@.request_id=5,
@.request_client_id=1,
@.request_description='',
@.request_date=''2007-11-22 16:34:32:997'',
@.request_edit_date=''2007-11-22 16:34:32:997'',
@.request_status='Draft',
@.request_approve_date=''1970-01-01 00:00:00:000'',
@.request_activity_code='',
@.request_department_code='',
@.request_participants=0,
@.request_activity_date=''2007-11-29 00:00:00:000'',
@.request_activity_due_date=''2007-11-23 00:00:00:000'',
@.request_company_id=0,
@.request_company_code=1,
@.request_notes=''

any help on this would be greatly appreciated.
Cheershey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

exec sp_executesql N'EXECUTE sp_UpdateRequest @.request_id, @.request_client_id, @.request_description, @.request_date, @.request_edit_date, @.request_status, @.request_approve_date, @.request_activity_code, @.request_participants,
@.request_activity_date, @.request_activity_due_date, @.request_company_id, @.request_company_code, @.request_notes',N'@.request_id int,@.request_client_id int,@.request_description varchar(6),@.request_date datetime,@.request_edit_date
datetime,@.request_status varchar(5),@.request_approve_date datetime,@.request_activity_code varchar(4),@.request_department_code varchar(3),@.request_participants int,@.request_activity_date datetime,@.request_activity_due_date
datetime,@.request_company_id int,@.request_company_code int,@.request_notes varchar(11)',@.request_id=1,@.request_client_id=1,@.r equest_description='Justin',@.request_date=''2007-11-23 08:59:04:250'',@.request_edit_date=''2007-11-23
08:59:04:250'',@.request_status='Draft',@.request_ap prove_date=''1970-01-01 00:00:00:000'',@.request_activity_code='0000',@.requ est_department_code='022',@.request_participants=50 ,@.request_activity_date=''2007-11-23
00:00:00:000'',@.request_activity_due_date=''2007-11-30 00:00:00:000'',@.request_company_id=5292,@.request_c ompany_code=1,@.request_notes='Other Notes'

are there meant to be the double single quotes('') before and after the date?

Cheers,|||are there meant to be the double single quotes('') before and after the date?No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP|||No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP

I'm not sure because it's already surrounded by single quotes.

maybe you can try '''2007-11-23 08:59:04:250'''|||Sorry, I should have looked at your whole example.

There are multiple ways that I could parse what you've posted. The one that makes the most sense to me at the moment is:exec sp_executesql N'EXECUTE sp_UpdateRequest
@.request_id, @.request_client_id, @.request_description
, @.request_date, @.request_edit_date, @.request_status
, @.request_approve_date, @.request_activity_code, @.request_participants
, @.request_activity_date, @.request_activity_due_date, @.request_company_id
, @.request_company_code, @.request_notes'', N''@.request_id int
, @.request_client_id int, @.request_description varchar(6), @.request_date datetime
, @.request_edit_date datetime, @.request_status varchar(5), @.request_approve_date datetime
, @.request_activity_code varchar(4), @.request_department_code varchar(3), @.request_participants int
, @.request_activity_date datetime, @.request_activity_due_date datetime, @.request_company_id int
, @.request_company_code int, @.request_notes varchar(11)''
, @.request_id = 1, @.request_client_id = 1, @.request_description = ''Justin''
, @.request_date = ''2007-11-23 08:59:04:250'', @.request_edit_date = ''2007-11-23 08:59:04:250''
, @.request_status = ''Draft'', @.request_approve_date = ''1970-01-01 00:00:00:000''
, @.request_activity_code = ''0000'', @.request_department_code = ''022'', @.request_participants = 50
, @.request_activity_date = ''2007-11-23 00:00:00:000'', @.request_activity_due_date = ''2007-11-30 00:00:00:000''
, @.request_company_id = 5292, @.request_company_code = 1, @.request_notes = ''Other Notes''
'See if that represents what you think you meant, and let me know how it works for you.

-PatPsql

Problems in selecting record.

I have a table A which has the following structure:
Column Name(Type)
-- --
ID(int)
Repair Code(varchar)
Damage Code(varchar)
Location Code (varchar)
I also have a the master code table
Column Name(Type)
-- --
Code Type (int)
Code Name (varchar)
Code Description ( varchar)
and have a stored procedure called GetCodeDescription which input the Code
Type and Code Name , display the Code Description found in the code master
table.
I want to select the record from TabelA by inputting a ID , then select the
record which have the follwong structure
Column Name(Type)
--
ID (int)
Repair Code (varchar)
Repair Code Description (varchar)
Damage Code (varchar)
Damage Code Description (varchar)
Location Code (varchar)
Location Code Description (varchar)
How can I do it by using the GetCodeDescription stored procedure?
Thnak you very much !Trying to use the stored procedure is only going to make things much,
much more complicated than necessary. How about just doing a query?
SELECT A.ID,
A.RepairCode, B.COdeDescription as RepairDescription,
A.DamagerCode, C.COdeDescription as DamageDescription,
A.LocationCode, D.COdeDescription as LocationDescription
FROM TableA as A
JOIN MasterCodes as B
ON A.RepairCode = B.CodeName
AND B.CodeType = 1
JOIN MasterCodes as C
ON A.DamageCode = C.CodeName
AND C.CodeType = 2
JOIN MasterCodes as D
ON A.LocationrCode = D.CodeName
AND D.CodeType = 3
Roy
On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
<BallBall@.discussions.microsoft.com> wrote:

>I have a table A which has the following structure:
>Column Name(Type)
>-- --
>ID(int)
>Repair Code(varchar)
>Damage Code(varchar)
>Location Code (varchar)
>I also have a the master code table
>Column Name(Type)
>-- --
>Code Type (int)
>Code Name (varchar)
>Code Description ( varchar)
>and have a stored procedure called GetCodeDescription which input the Code
>Type and Code Name , display the Code Description found in the code maste
r
>table.
>I want to select the record from TabelA by inputting a ID , then select the
>record which have the follwong structure
>Column Name(Type)
>--
>ID (int)
>Repair Code (varchar)
>Repair Code Description (varchar)
>Damage Code (varchar)
>Damage Code Description (varchar)
>Location Code (varchar)
>Location Code Description (varchar)
>How can I do it by using the GetCodeDescription stored procedure?
>Thnak you very much !
>|||Thank for the answer , but because my code table container about 12000
records, if i join many times , i think the performance will be affected
"Roy Harvey" wrote:

> Trying to use the stored procedure is only going to make things much,
> much more complicated than necessary. How about just doing a query?
> SELECT A.ID,
> A.RepairCode, B.COdeDescription as RepairDescription,
> A.DamagerCode, C.COdeDescription as DamageDescription,
> A.LocationCode, D.COdeDescription as LocationDescription
> FROM TableA as A
> JOIN MasterCodes as B
> ON A.RepairCode = B.CodeName
> AND B.CodeType = 1
> JOIN MasterCodes as C
> ON A.DamageCode = C.CodeName
> AND C.CodeType = 2
> JOIN MasterCodes as D
> ON A.LocationrCode = D.CodeName
> AND D.CodeType = 3
> Roy
>
> On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
> <BallBall@.discussions.microsoft.com> wrote:
>
>|||Try the join first. You will probably find the performance is just fine,
and simpler to manage.
"BallBall" <BallBall@.discussions.microsoft.com> wrote in message
news:36B2D0B0-6201-42BE-933B-079D4FCE44DE@.microsoft.com...
> Thank for the answer , but because my code table container about 12000
> records, if i join many times , i think the performance will be affected
> "Roy Harvey" wrote:
>
Code
master
the|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your personal narrative and pseudo-code are useless.
That would be a horrible design error! Go back to the foundations; a
table is made up of one and only one kind of entity. There is no such
thing as "master code table" in a valid schema.
Next, a data element can be a type or a code but not a "type_code";
read ISO-11179. Next you confuse rows with records and columns with
fields and do not bother to give us table names.
Finally, how do you get enough codes into a VARCHAR(1) column? In a
good design, codes are fixed length and have a validation rule. There
is no such thing as a magical universal "id" in RDBMS; you are not
actually using IDENTITY in an RDBMS, are you'
I have to make a wild guess about the key
CREATE TABLE RepairRequests
(repair_nbr INTEGER NOT NULL,
repair_code INTEGER NOT NULL
REFERENCES Repairs (repair_code)
ON UPDATE CASCADE,
PRIMARY KEY (repair_nbr, repair_code),
damage_code INTEGER NOT NULL
REFERENCES Damages(damage_code)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations (location_code)
ON UPDATE CASCADE);
CREATE TABLE Repairs
(repair_code INTEGER NOT NULL PRIMARY KEY,
repair_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Damages
(damage_code INTEGER NOT NULL PRIMARY KEY,
damage_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Locations
(location_code INTEGER NOT NULL PRIMARY KEY,
location_description VARCHAR(20) NOT NULL,
.) ;
I have no idea; where is the code for this stored procedure?
I see that you also do not understand what a repeated group is and how
to program in a tiered architecture. This should be done as a simple
query. But assuming that you really do hate RDBMS, how do you handle
a repair request with more or less than two damages on it?|||>Thank for the answer , but because my code table container about 12000
>records, if i join many times , i think the performance will be affected
If performance is bad the table is not indexed correctly. Also it is
not necessary to guess what performance will be, all you have to do is
run the queries in Query Analyzer to see what it is. Easy, quick, and
you can learn a lot.
Twelve thousand rows is really all that large, by the way. With
proper indexing performance should be fine with ten or a hundred times
as many rows.
Roy

problems converting dates as varchar to datetime data type

Hi all,

I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.

I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.

Someone can help me? PLEASE

Thanks

In what format do you have your dates at the moment?

You can try using SET DATEFORMAT dmy before running the statment.

Or look at using CONVERT with the relevant style.

eg CONVERT(DATETIME, YourDateField, 103)

Check Books Online for more details

HTH!

|||Try CONVERT function, make sure the style parameter matches your varchar date format
|||

If your date is in a recognizable format SQL Server uses an implicit conversion from varchar to datetime, so no CONVERT or CAST function is necessary. If the date is in an unrecognizable format you can use the T-SQL string functions (listed in BOL) to reconstruct it into a recognizable format and you should get no errors.

|||

Hi,

I will post some more information about my problem.

In the source DB i have the "Beginning_Date" column, as varchar, with values with this format "dd/mm/yyyy HH.MM.SS".

In the target DB i have the "Beginning_Date" column again, but must be only with the the date part (dd/mm/yyyy) as datetime type, and the "Beginning_Time" column, storing the time part (HH:MMS) as nchar type.

I have no problems with the time part of the records, but the date part always fail.

I use the substring function in order to get only the date part of the records and try to insert it to the target DB both with "dd/mm/yyyy" and "mm/dd/yyyy" date formats. I always get the same error (out of range). I have tried also the "set datetime dmy" .......

Thanks for your help,

Bob

|||
Are all your dates in Beginng_Date in the same format? Are you sure there isn't a rogue value in there which is causing the whole statement to crash?

Can you post your DDL and DML statements and some sample data for us to help troubleshoot further?

I had a guess at what you were trying to do:

Code Snippet

CREATE TABLE tblDateTime
(d1 DATETIME, t1 NCHAR(10))

DECLARE @.String NVARCHAR(30)
SET @.String = '21/12/2007 14:15:15'

SET DATEFORMAT dmy

INSERT INTO tblDateTime
SELECT SUBSTRING(@.String, 1, 10), REVERSE(SUBSTRING(REVERSE(@.String), 0, 9))

Is this anything like what you're doing?

|||

Hi,

The problem is solved now.

richbrownesq, you were right. My problem was only one (is enough...) record wrong.

Thanks a lot for ALL!!!!

problems about displaying chinese and mislocation of controls

1. I have problems on displaying chinese on reporting services.

e.g. §Cˉ×a′oà-J′?3??h?o(¤??)

The data type of the fields is char. Is it possible to display chinese using this database setting but not setting the data type to nvarchar?

2. On the other hand, there is mislocation of the controls. If I put a textbox on another textbox, one of the textbox will not be in the orginal location but in somewhere nearby. The same situation appears for the case of images. How can I "lock" the location of the controls?

I almost forgot I am assuming you know the Chinese alphabet is more than 2000 characters compared to the 26 characters Latin. I don't see how you can render Chinese with Char, you need Nvarchar or Nvarchar(max) with the version of Chinese you need because there are about six defined in SQL Server. You can use collation for the database table and column, the report dataset data option property and the international section of the table and text box. The later just change the collation of the ReportServerDB and the ReportServerTempDB. But clean collation in your tables and columns with the stored procedure will take care of it. Hope this helps.

|||

There is nothing happen when I set any collation in MS VS. As other applications are using the same database, I can't change the datatype into nvarchar.

Is nvarchar the only solution to the problem?

|||

(Is nvarchar the only solution to the problem?)

Nvarchar only tells SQL Server the data is not ASCII but Unicode it is the collation that tells SQL Server what code page to use and sort order which is very important with Chinese. You can create a View with Chinese collation and Nvarchar, then add the collation in the stored procedure run a search for collation precedence in the BOL.

Problem: sqlCom.ExecuteNonQuery()

What can be reason for this message?

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

There could be a lot of reasons for that message. You need to trap the error (either while debugging or with a custom error handling routine that displays errors) and look at it's inner message. This is thrown when there was some form of database error, i.e. a specified column doesn't exist, you tried to insert a duplicate primary key, etc.|||reading from sql database is working fine but when i try to save something it throws that exception|||

You still need to debug in order to narrow down the problem more effectively. Once you have the actual message (SqlException.Message), then we could give you direction. Or, you could post your code; the error message would be helpful, too, though.

|||

I solve the problem - mistakes in the database

thank you for your time

greetings.

Prole

|||

I solve the problem - mistakes in the database

thank you for your time

Prole

Problem with variable of type money?

Here's what I want to do: I've got a table with orders, each order has
a specific discountrate (an int, which represents a percentage). Each
order consists of 1 or more items in another table, each item in that
table has a price. Now I want to return the full price and the
discounted price (or the discounted amount).

Here's a relevant excerpt of the code:

--------------------
CREATE TABLE #tmp (OrderID Integer,
Price money,
Discount money)

DECLARE @.Discount money

SELECT @.Discount =
(
(
(SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
FROM OrderDetails
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
+
(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
OrderDetailsAmount),0)
FROM OrderDetailsSupplement
INNER JOIN OrderDetails ON
OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
)
*
( @.DiscountRate / 100 )
)

SELECT CustomerFull,
SUM(Price) As Price,
SUM(Discount) As Discount,
SUM (Products) As Products,
COUNT(@.orderID) As Orders
FROM #tmp
GROUP BY CustomerFull
ORDER BY CustomerFull
--------------------

The problem: instead of getting a low number (like 0.57 for instance),
I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
code, but I'd really like to know what I'm doing wrong.

--
BVHAm 2 Mar 2006 07:23:09 -0800 schrieb bartvanhemelen@.gmail.com:

> Here's what I want to do: I've got a table with orders, each order has
> a specific discountrate (an int, which represents a percentage). Each
> order consists of 1 or more items in another table, each item in that
> table has a price. Now I want to return the full price and the
> discounted price (or the discounted amount).
> Here's a relevant excerpt of the code:
> --------------------
> CREATE TABLE #tmp (OrderID Integer,
> Price money,
> Discount money)
> DECLARE @.Discount money
> SELECT @.Discount =
> (
> (
> (SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
> FROM OrderDetails
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> +
> (SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
> OrderDetailsAmount),0)
> FROM OrderDetailsSupplement
> INNER JOIN OrderDetails ON
> OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> )
> *
> ( @.DiscountRate / 100 )
> )
> SELECT CustomerFull,
> SUM(Price) As Price,
> SUM(Discount) As Discount,
> SUM (Products) As Products,
> COUNT(@.orderID) As Orders
> FROM #tmp
> GROUP BY CustomerFull
> ORDER BY CustomerFull
> --------------------
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

In your example i can't see where @.DiscountRate is declared or set. From
where should the value for @.DiscountRate come?

bye,
Helmut|||BVH,

You are probably the victim of integer arithmetic.

Change @.DiscountRate / 100 to @.DiscountRate / 100.0.

That "point zero" tells the system that you want float division,
instead of integer division. The better solution would be to cast the
integer to a float but adding .0 will work.|||(bartvanhemelen@.gmail.com) writes:
> *
> ( @.DiscountRate / 100 )
> )
>...
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

@.DiscountRate was integer, correct?

You should have left out the parentheses above. This mandates SQL Server
to compute this expression before it gets mixed with the rest. But if
you divide two integers, you get integer division, which is not what you
want at all.

Assuming that what is before the * is money, leaving out the parenthesis,
transforms the division to money division.

--
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.mspx|||what happens next week when the boss wants to give a five and a half
percent discount???

percentages are ALWAYS better stored as real, and multiplied by 100 for
display to the users.|||I agree with Doug. We usually use reals to hold our percentages. In the
db, a 5.5% discount would look like .055.......

Because, in effect, that's really what 5.5% represents.|||figital (mharen@.gmail.com) writes:
> I agree with Doug. We usually use reals to hold our percentages. In the
> db, a 5.5% discount would look like .055.......
> Because, in effect, that's really what 5.5% represents.

In our shop we can never make up our mind... So some of the percentages
are stored as aba_percent, others as aba_fraction and yet others as float.
aba_percent is just an alias for "float" but the name implies that it is
a percentage, and that you should divide with 100 before use. aba_fraction
is float, and constrained to be between 0 and 1. Multiply with 100 before
display, and divide by 100 before storing. Those that just float, can
hold values outside the range [0..1]. (I have considered a constraint
to keep them between -10 and 10, but that is a risky business, as one
day 1200% may be a correct value.)

The problem with storing percentages as fraction, is that some developers
make the entry forms a carbon of the data model, so they don't display
the fraction as a percentage, but as a fraction...

--
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.mspx|||>ba_fraction is float, and constrained to be between 0 and 1

Well, actually, I've used percentages to keep track of growth rates, or
percentage growths where the percentage can greatly exceed one.

For instance, 2.00 means 200 percent, which means multiply by 200
percent.

Another really good thing about storing 5 percetn as .05 is that the
very first time the programmer/UI dweeb displays it that way, the
programmer dweeb sees it.

At the VERY worst, the end user sees it, and pretty much ANYONE used to
dealing with money, growths, or numbers will report it as a pretty
minor bug, and realize what is going on.
Again, differing points of view arrived at by rational people, but IMO
float is a much better solution.
regards,
doug

Problem with varchar(max) in SQL Server 2005

Hi All,
I am having a small problem with varchar(max) type variable. Please look at
the code below.
DECLARE @.varcharmax varchar(max), @.varchar varchar(8000)
SET @.varcharmax = REPLICATE(‘D’, 10000) – this will end up with 10000
lengthy string
SET @.varchar = REPLICATE(‘D’, 10000) – this will end up with 8000 leng
thy
string
When I run the a code like;
SET @.varcharmax = @.varchar + @.varchar + @.varcharmax
SELECT LEN(@.varcharMAX) AS Length
The result is 18000
But when I take the varcharmax front;
SET @.varcharmax = @.varcharmax + @.varchar + @.varchar
SELECT LEN(@.varcharMAX) AS Length
The result is 26000
What is the problem with my code? I made a small experiment
(http://spaces.msn.com/members/dineshpriyankara/Blog/cns!1p-RgJ7SV2D2-6Y3lW0
UC8uA!217.entry) on this, if want have a look please. I am using April CTP.
Highly appreciate your reply.
Dinesh Priyankarahttp://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dinesh Priyankara" wrote:

> Hi All,
> I am having a small problem with varchar(max) type variable. Please look a
t
> the code below.
> DECLARE @.varcharmax varchar(max), @.varchar varchar(8000)
> SET @.varcharmax = REPLICATE(‘D’, 10000) – this will end up with 1000
0
> lengthy string
> SET @.varchar = REPLICATE(‘D’, 10000) – this will end up with 8000 le
ngthy
> string
> When I run the a code like;
> SET @.varcharmax = @.varchar + @.varchar + @.varcharmax
> SELECT LEN(@.varcharMAX) AS Length
> The result is 18000
> But when I take the varcharmax front;
> SET @.varcharmax = @.varcharmax + @.varchar + @.varchar
> SELECT LEN(@.varcharMAX) AS Length
> The result is 26000
> What is the problem with my code? I made a small experiment
> (http://spaces.msn.com/members/dineshpriyankara/Blog/cns!1p-RgJ7SV2D2-6Y3l
W0UC8uA!217.entry) on this, if want have a look please. I am using April CTP
.
> Highly appreciate your reply.
> Dinesh Priyankara
>
>
>|||Posted same at
http://communities.microsoft.com/ne...p=sqlserver2005
thanks
"Mike Epprecht (SQL MVP)" wrote:
> http://communities.microsoft.com/ne...p=sqlserver2005
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Dinesh Priyankara" wrote:
>

Problem with using a text data type.

I am using a 'text' data type in a table and am having trouble adding data to it. I'm using an MS Access ADP project front end for my forms. I want to copy and paste from a text editor such as NotePad to a 'text' data type field in a form. There appears to be a limit that the field will accept which is much below the 2GB size that is allowed for the field. I get the following error message: "The text is too long to be edited." Once I get this error I can add characters from the keyboard but eventually the field will not accept any more characters. Typing or pasting does nothing. I can delete. So I've hit a limit but I'm not sure if it is in Access or SQL Server.

Any help would be appreciated.

Dennis GeasanGo to design view on the form. Right click on your text box. Look at the properties. I believe there's a property there to set the max length.|||No such property exists on a 'text' data type field. I did some further testing. The field will take up to 64000 characters and then quit accepting any more. It looks like I will have to use special SQL commands to add additional data to this field. It does not behave the same as an MS Access Memo field and directly allow a large amount of text to be added. Further research indicates the SQL Server 'text' data type is a linked list and I will have to start working with pointers to be able to add more data. Ugly.

Dennis Geasan

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
Ragnar
On Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.
|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>