Showing posts with label value. Show all posts
Showing posts with label value. Show all posts

Friday, March 30, 2012

Problems With Sum

Dear All
We have a table with around 100000 records, this table
contains an Int value that can be >= 1 <= 999999.
Whenever we use the command
Select sum(MYINT) from tblTEST
We get the error 'Arithmetic overflow error converting
expression to data type int.'
I know that the result of the sum is bigger than an int
however I was wondering if there is a trick I can do so I
don't need to create my own loop to add them together.
Thanks for your time
JTry this
Select convert(decimal(12,2),sum(MYINT)) from tblTEST
Madhivanan|||SELECT SUM(CAST(myint AS BIGINT))
FROM tblTest
David Portas
SQL Server MVP
--|||You need to put CONVERT inside the SUM otherwise the SUM will still
overflow.
David Portas
SQL Server MVP
--|||Madhivanan,
It will still give you an Arithmetic Overflow error!
Try the code below
SELECT IDENTITY(INT) AS Number
INTO #TMP
FROM sysobjects s1
CROSS JOIN sysobjects s2
CROSS JOIN sysobjects s3
Select convert(decimal(12,2),sum(Number)) from #TMP
If you dont feel offended, I strongly recommend you to test
your solutions before you post them
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109256055.787979.288100@.z14g2000cwz.googlegroups.com...
> Try this
> Select convert(decimal(12,2),sum(MYINT)) from tblTEST
> Madhivanan
>|||Thanks Thomas
It should be
Select sum(convert(decimal(12,2),Number)) from tblTEST
Madhivanan

Wednesday, March 28, 2012

Problems with string value "one space"

Hello,
I've been discovering weird problems in my application, and after thorough
research, going to lower and lower levels, until I've found the following:
The setup:
MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
collation). The problems are when there is an entry with one character, code
0x20 (ASCII 32; space).
The first surprise hit me when I discovered that LEN function gives the
length of the value, NOT INCLUDING the trailing spaces. Since there is no
function that takes into account the trailing spaces, the only way to get
the length of the field seems to be something like Len(myField+'X')-1.
Awkward, if you ask me. Is there a more preferred way?
The other, which actually caused the problems in my system, was that
querying for zero-length value, it returned the record with the single
space!
SELECT * FROM MyTable WHERE myField=''
So, to avoid this, I can use the following query:
SELECT * FROM MyTable WHERE myField+'X'='X'
I assume that this behaviour might be connected with collation, but I'd hate
to select binary sort for this language-related value. What is happening
here, and how to fix it?
Thanks,
Pavils"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23SFUuWKXGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I've been discovering weird problems in my application, and after thorough
> research, going to lower and lower levels, until I've found the following:
> The setup:
> MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
> collation). The problems are when there is an entry with one character,
> code 0x20 (ASCII 32; space).
> The first surprise hit me when I discovered that LEN function gives the
> length of the value, NOT INCLUDING the trailing spaces. Since there is no
> function that takes into account the trailing spaces, the only way to get
> the length of the field seems to be something like Len(myField+'X')-1.
> Awkward, if you ask me. Is there a more preferred way?
>
datalength

> The other, which actually caused the problems in my system, was that
> querying for zero-length value, it returned the record with the single
> space!
> SELECT * FROM MyTable WHERE myField=''
> So, to avoid this, I can use the following query:
> SELECT * FROM MyTable WHERE myField+'X'='X'
> I assume that this behaviour might be connected with collation, but I'd
> hate to select binary sort for this language-related value. What is
> happening here, and how to fix it?
This is unrelated to collation. Trailing spaces are ignored in string
comparisons in SQL Server. To compare strings without ignoring trailing
spaces use LIKE.
EG:
select datalength('a ')
select 1 where '' = ' '
select 1 where '' like ' '
David

Monday, March 26, 2012

Problems with RS Unattended Execution Account

HI all
I have RS2000 Report Server with a report that contains an image control.
The image control value is a UNC path to an image (ie
\\<server>\images\image.jpg. The <server> is a different server to the
Report Server. I have setup the unattended execution account to be the
domain administrator (for testing purposes). WHen running the report on my
machine in Visual Studio, I can see the images, however when running it off
the server the images do not appear. I setup object access auditing on the
server containing the images and it seems like the report server is not even
attemting to access the images.
Anyone know what I may be missing here.
Thanks in advance
GeorgeOn Aug 29, 2:15 am, "_george" <none@.nojne@.none> wrote:
> HI all
> I have RS2000 Report Server with a report that contains an image control.
> The image control value is a UNC path to an image (ie
> \\<server>\images\image.jpg. The <server> is a different server to the
> Report Server. I have setup the unattended execution account to be the
> domain administrator (for testing purposes). WHen running the report on my
> machine in Visual Studio, I can see the images, however when running it off
> the server the images do not appear. I setup object access auditing on the
> server containing the images and it seems like the report server is not even
> attemting to access the images.
> Anyone know what I may be missing here.
> Thanks in advance
> George
It might be that since you are accessing the image through the Report
Server, which is basically a website, you may be using the default
ASPNET account that has minimal permissions in the system. On
occasion, explicit settings are overridden in ASP.NET and IIS.
Depending on your circumstances, you could try embedding the image in
the report. Also, you might want to check the settings in one of
the .config files in the path: C:\Program Files\Microsoft SQL Server
\MSSQL.x\Reporting Services\ReportManager it may be overriding your
domain admin account. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique
I had swapped the default ASP.Net account used in the Out of process
components for IIS to a Domain Administrator account but still no go. As for
the config files, what exactly should I be looking for' I was under the
impression that all usernames and pwds in the .config files are encrypted.
Thanks again
George
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1188435094.798326.100950@.22g2000hsm.googlegroups.com...
> On Aug 29, 2:15 am, "_george" <none@.nojne@.none> wrote:
>> HI all
>> I have RS2000 Report Server with a report that contains an image control.
>> The image control value is a UNC path to an image (ie
>> \\<server>\images\image.jpg. The <server> is a different server to the
>> Report Server. I have setup the unattended execution account to be the
>> domain administrator (for testing purposes). WHen running the report on
>> my
>> machine in Visual Studio, I can see the images, however when running it
>> off
>> the server the images do not appear. I setup object access auditing on
>> the
>> server containing the images and it seems like the report server is not
>> even
>> attemting to access the images.
>> Anyone know what I may be missing here.
>> Thanks in advance
>> George
>
> It might be that since you are accessing the image through the Report
> Server, which is basically a website, you may be using the default
> ASPNET account that has minimal permissions in the system. On
> occasion, explicit settings are overridden in ASP.NET and IIS.
> Depending on your circumstances, you could try embedding the image in
> the report. Also, you might want to check the settings in one of
> the .config files in the path: C:\Program Files\Microsoft SQL Server
> \MSSQL.x\Reporting Services\ReportManager it may be overriding your
> domain admin account. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Friday, March 23, 2012

Problems with Return Value with Stored Procedure

Hi,

I have facing this problem, and i don't know what is going wrong.

The SP when tested in the Query Analyser works fine.

the resultant is either a 0 or PID where 0 is returned when the insert doesn't happen

I might have some extra declaration, which might be causing this error , or not defining the right thing.

Any suggestion is welcomed and greatly appreciated.

Thanks


Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim sSQLConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim daInsertSku As SqlDataAdapter = New SqlDataAdapter()
Try
sSQLConnection.Open()
Dim cmdInsert As New SqlCommand("sp_request", sSQLConnection)
cmdInsert.CommandType = CommandType.StoredProcedure

cmdInsert.Parameters.Add("@.Username", SqlDbType.Int, 4)
cmdInsert.Parameters("@.Username").Value = "0"

Dim PID As SqlParameter = New SqlParameter("@.identity", SqlDbType.Int)
PID.Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(PID)

daInsertSku.InsertCommand = cmdInsert
cmdInsert.ExecuteNonQuery()
Response.Write(PID.Value)

Catch Exc As Exception
' error handling
Finally
' close connection
End Try
End Sub

Is PID a return value, or an output parameter? You should use an output parameter to return an ident from a sproc.

And what is wrong? Are you getting an error?|||I did get it working, i changed to Output Parameter instead or Return

thank yousql

Tuesday, March 20, 2012

Problems with mimimum value for DateTime Package Variable since SP1

Since I've installed SP1 the minimum value for a package variable has changed.

The former version used 12/30/1899 as the minimum value that can be entered.
Now the min value has changed to something about 1960.

During package execution I'd like to distinguish between two states:

1. The Variable has not yet been written to.
2. Any of my executable has written to it.

At the moment I check the value of it to be the minimum value (see above) or not.
So if I develop packages under SP1 I get into trouble.

Any way out of it?
Thanks
Fridtjof

[EDIT] The mindate in C# differs from both date values mentioned above! Consistency? Hum![/EDIT]

How did you determine that you can't put values below 1960 in there? I (or more accurately Darren who is sat right next to me) has just typed the value 01/01/1900 into the variables window and it sems to take it OK.

What specifically are you not able to do?

-Jamie

|||

Typing works fine, and the following code worked too.

Public Sub Main()

Dts.Variables("Variable").Value = DateTime.MaxValue
System.Windows.Forms.MessageBox.Show(Dts.Variables("Variable").Value.ToString())

Dts.Variables("Variable").Value = DateTime.MinValue
System.Windows.Forms.MessageBox.Show(Dts.Variables("Variable").Value.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||Oops, I don't really know what went wrong but it works as you mentioned.

Sorry for posting too quick...

Monday, March 12, 2012

Problems with INSERT parameter

Hi again,

how do I tell a DropDownList, that its value is supposed to be aparameter for a SQLDataSource's InsertParameters? Following is part ofmy aspx code:

<asp:SqlDataSource
ID="srcPerson"
runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="System.Data.SqlClient"
InsertCommand="InsertPerson"
InsertCommandType="StoredProcedure"
OldValuesParameterFormatString="{0}">
<InsertParameters>
<asp:Parameter Name="PersonLastName" Type="String" />
<asp:Parameter Name="PersonFirstName" Type="String" />
<asp:Parameter Name="RoomID" Type="String" />
<asp:Parameter Name="PersonPhone" Type="String" />
<asp:Parameter Name="PersonPhonePrivate" Type="String" />
<asp:Parameter Name="PersonPhoneMobile" Type="String" />
<asp:Parameter Name="PersonEmail" Type="String" />
</InsertParameters>
</asp:SqlDataSource
<asp:FormView
ID="fvStaff"
cssClass="person"
runat="server"
DataSourceID="srcPerson">
<InsertItemTemplate>
<!--DataSource srcRooms gets all rooms from room table in database. -->
<asp:SqlDataSource
ID="srcRoomsInsert"
runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SelectRoomsByBuildingID"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter
ControlID="ddlBuildingsInsert"
DefaultValue="1"
Name="ID"
PropertyName="SelectedValue"
Type="Int32"
/>
</SelectParameters>
</asp:SqlDataSource>
<asp:LabelcssClass="lblIdentifier" ID="lblLastName" runat="server"meta:resourcekey="LastName" />
<asp:TextBox ID="PersonLastName" runat="server" Text='<%#Bind("PersonLastName") %>'></asp:TextBox><br />

<asp:LabelcssClass="lblIdentifier" ID="lblFirstName" runat="server"meta:resourcekey="FirstName" />
<asp:TextBox ID="PersonFirstName" runat="server" Text='<%#Bind("PersonFirstName") %>'></asp:TextBox><br />

<asp:LabelCssClass="lblIdentifier" ID="lblBuilding" runat="server"meta:resourcekey="Building"></asp:Label>
<asp:DropDownList
ID="ddlBuildingsInsert"
cssClass="buildings"
runat="server"
DataSourceID="srcBuildings"
DataTextField="BuildingAbbreviation"
DataValueField="BuildingID"
AutoPostBack="True"
AppendDataBoundItems="true"
OnSelectedIndexChanged="ddlBuildingsInsert_SelectedIndexChanged">
<asp:ListItem enabled="true" value="0"meta:resourcekey="NoBuilding"></asp:ListItem>
</asp:DropDownList><br />

<asp:LabelcssClass="lblIdentifier" ID="lblRoom" runat="server"meta:resourcekey="Room" />
<asp:DropDownList
ID="ddlRoomsInsert"
cssClass="rooms"
runat="server"
Enabled="false"
DataSourceID="srcRoomsInsert"
DataTextField="RoomName"
DataValueField="RoomID"
AutoPostBack="true"
SelectedValue='<%# Bind("PersonRoomID") %>'>
</asp:DropDownList><br /
<asp:LabelcssClass="lblIdentifier" ID="lblPhone" runat="server"meta:resourcekey="Phone" />
<asp:TextBox cssClass="staff" ID="Phone" runat="server" Text='<%#Bind("PersonPhone") %>' /><br />

<asp:LabelcssClass="lblIdentifier" ID="lblPhonePrivate" runat="server"meta:resourcekey="Private" />
<asp:TextBox cssClass="staff" ID="PhonePrivate" runat="server"Text='<%# Bind("PersonPhonePrivate") %>' /><br />

<asp:LabelcssClass="lblIdentifier" ID="lblPhoneMobile" runat="server"meta:resourcekey="Mobile" />
<asp:TextBox cssClass="staff" ID="PhoneMobile" runat="server"Text='<%# Bind("PersonPhoneMobile") %>' /><br /
<asp:LabelcssClass="lblIdentifier" ID="lblEmail" runat="server"meta:resourcekey="Email" />
<asp:TextBox cssClass="staff" ID="Email" runat="server" Text='<%#Bind("PersonEmail") %>' /><br />

<asp:LinkButton cssClass="lnkButton" ID="InsertCancelButton"runat="server" CausesValidation="False" CommandName="Cancel"Text="<%$resources:translations,Cancel%>"></asp:LinkButton>
<asp:LinkButton cssClass="lnkButton" ID="InsertButton"runat="server" CausesValidation="True" CommandName="Insert"Text="<%$resources:translations,Insert%>"></asp:LinkButton>
</InsertItemTemplate>
</asp:FormView
This is what I tried in the first place, because the "Text='<%#Bind("PersonEmail") %>' " thing worked fine with the textboxes. Butafter selecting some value in the DropDownList ddlBuildingsInsert I getthe error:

Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control.

pointing to the DropDownList ddlRoomsInsert. Deleting the"SelectedValue='<%# Bind("PersonRoomID") %>'" line from theDropDownList results in a missing insert parameter "PersonRoomID".
Can you tell me the mistake I'm making here?

Thanks a lot, ToobThis one actually stumped me for a while too. The not so nice workaround I did for a similiar situation was to have a hidden value control, then during the dropdown change event I set the hidden value control to the selectedvalue from the dropdown, then link the insert sqldatasource to use the hidden value control instead. Bad, and requires a server postback for the dropdown change event, but for my particular app, I needed to do the postback anyhow.|||

in the data source control :

<SelectParameters>
<asp:ControlParameter Name="State" ControlID="statesDDL" PropertyName="SelectedValue" />
</SelectParameters>

Use a different bind method.

http://eramseur.blogspot.com/2005/11/dropdownlist-with-gridview.html

Hope that helps.

|||Thanks, the ControlParameter made it work. The next problem I wasfacing was the ControlID argument, it would only work if I used theddl's UniqueID. I'd like to put it in there somehow programmaticly(like <%# =statesDDL.UniqueID %>) but didn't find a way. Is therea way to do this?

Thanks again,

Toob

Problems with Identity column

Got this error:

"An explicit value for the identity column in table 'sdk_appsettings' can only be specified when a column list is used and IDENTITY_INSER is on"

Any idea?

Also what is MAX in nvarchar(max)?

If you have Identity column in your table and you supply a value when you are inserting a record, then above error will be thrown. Identity column has its own rules - they are normally incremented each time by a specific value for example 1. When inserting identity column value should not be supplied.|||

i understand that identity columns have its own problems but i am facing problem while inserting data into such columns.

i did not supply the identity column while inserting the data. and it works quite well. but when i upload my project on the web server then it says that the value of my identity column cannot be null as i am not specifying it in my identity column.

can you suggest me anything to find the solution. i am using asp.net 2003 and SQL Server 2000

|||I would look at your production database and make sure that column is an identity column. It probably isn't.

Wednesday, March 7, 2012

Problems with DateTime

Hello all,
I have a problem when I try to insert with a SQL Command a value obtained from a Calendar Control in a SQL Server table. I get the date from the calendar and I put it in a TextBox, but when I try to insert this value in a DateTime defined field I have the following error:
The string or binary data would be truncated. The instruction was finished.

Could you help me?

Thanks.You need to convert the text into a c#/vb date type first. You're prob' hitting a localization issue with the date format.|||I'm certainly no SQL server pro but from my limited experience, SQL server is persnickety about datetime values. I avoid the problem altogether by passing the date in your SQL statement as a string (wrapping it w/in single quotes) or, if you are using sprocs, converting it to datetime w/in the sproc in SQL Server.

good luck.|||No don't wrap it as a string that's exactly where you start getting the problems. If you wrap it as a string you need to either know the database format (and be able to convert to it) or you have to convert to a universal format. Much better to use a param.

Saturday, February 25, 2012

problems with average calculations that do not give correct value

I am currently building a cube and I have two measures that I need to calculate the average for (i.e. columns measureA and measureB).

measureA is a Sum of a value and measureB is the count of the number of occurences of the value.

I define a calculated Member that employs the calculation of measureA/measureB. This gives me an average that I want to roll up.

At the lowest level the calulation is correct, however as the rollups happen the calcultion becomes invalid. At the (all) level the calculation is quite far out.

The calculated member simply gives me the (All)measureA/(All)measureB.

Can anyone point me in the direction of how I can get a calculation of the individual measureA/measureB rolled up correctly giving me an overall average?

COUNT takes into account empty cells, so when you rollup, you are considering more cells than you should.

Use AVG, or filter out empty cells when using COUNT

See

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1

Hope this helps,

Santi

|||

Thanks Santi That did help me.

What I was trying to do was more complex than simply averaging a single column data and after studying the problem I found that it was far too complex and that using Avg was much better.

Many thanks

Rob

problems with < or > string values

I have a query that is trying to find all of the names that fall between a low and high value. I'm using CHR(10) for the low value and this seems to work, but using a '{' for the high value does not. I chose '{' because it is one higher then 'z' in the ASCII charts. So the query looks something like this.

SELECT * FROM People where LastName > @.LastNameLow and LastName < @.LastNameHigh

The parameter values are built in .Net code and passed to the stored proceedure. @.LastNameLow is a string of CHR(10) values whose length is equal to the column length. @.LastNameHigh is a string of '{' characters whose length is equal to the column length.

When I run this query, I get no rows returned. However if I change the '{' character to 'z' character, it does return rows. However I don't want to use 'z' because the query is <, not <=. And before you ask, no I cant change it. I've tried '|', '}' and '~', all of which are valid ASCII values above 'z' and valid printable characters, but none of them seem to work in this case.

Any help would be appreciated.

Bill

you want a default LOWEST and HIGHEST value when user does not specify any value is it ?

why not pass in NULL value to @.LastNameLow or @.LastName High if user does not specify any Low or High value

SELECT *
FROM People
WHERE LastName >= coalesce(@.LastNameLow , LastName)
AND LastName <= coalesce(@.LastNameHigh, LastName)|||

A variation on K H Tan's solution (passing in Null when there is no upper or lower limit) is:

SELECT *
FROM People
WHERE ((LastName > @.LastNameLow) OR (@.LastNameLow IS NULL)) AND
((LastName < @.LastNameHigh) OR (@.LastNameHigh IS NULL))

Or if you have to take a string value instead then assuming you use say '@.#@.' as the no limit token then you can use the query:

SELECT *
FROM People
WHERE ((LastName > @.LastNameLow) OR (@.LastNameLow = '@.#@.')) AND
((LastName < @.LastNameHigh) OR (@.LastNameHigh = '@.#@.'))

You can use any token that would not be provided as a legitimate range value. I hope this helps.

|||

Not exactly. This kind of query is essentially being used as a LIKE statement. For instance if the user wants to search last names and enters 'SM' for the value, the parameters would code up to be 'SM'+chr(10)+chr(10)...+chr(10) for @.LastNAme Low and 'SM{{{{...{{{' for @.LastNameHigh. the intent is to return all last names that begin with 'SM'. Like I've said though, I don't really have control over the query and can't change it to a LIKE, so I'm trying to find values that will work. If @.LastNAmeHigh is 'SM{{{{...{{{' we get 0 rows returned, but if @.LastNameHigh is 'SMzzzzzz...zzzz' it does get some rows.

I know this must have something to do with the coallation and sort order set on the sql server. As near as I can tell we are using the default coallation which is Latin 1252 with sort order 52. The LAtin 1252 code page seems to match a traditional ASCII chart, but I have no idea what sort order 52 means or how to tell specifically how it is sorting.

Bill