Hi, I have a record set that is bound to a table in MS SQL Server. One
filed in the table is bound to a "long" type member variable in the
RecordSet. What will happen to the "long" variable when the field is
NULL in the table?
Thanks!
-Yi
how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.
Example Data
create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),
[code]....
What is the equivalent to a long data type in SQL Server 2005?
View 3 Replies View RelatedHello,
I would like to know as to what data type is the best if I want to store MP3's and large amount of text in a SQL server.
Please let me know about the data type for both the tasks.
The table for MP3 is different than the table for large text (eg . saving somebody's resume)
Please do let me know.
Thank you,
John
Dear friends,
I want to know that is the alternative data type of long in sqlserver
regards,
asad
Hai Every one
i am facing a werid problem it is related to storing a long text data in SQL 2000 the text data is some thing like the following
"dshjfsjlksdjakdjlksadjfeidkadflkdsajfieawirfjalkdfjsakdfjaiekdvnmckaumnmmmmmmmmmmmmmmmoadifdjsakdjfauiereoweiiiiiiiiiiiiiiiiiiiiiiiiiiidalfkjdsa,mlfdsdflvmsaldifsdjfskladfakdfjakladkalfkfadkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkadlkfjaidfajfkamcmmmmmmmmmmmmmmmmmmmmmmmmmmmmmiadlmfalierfmaerjeaiaelelllllllllllllllllllll..."
in short it is really long so i opted to go with TEXT data type as it states that it can store more than 8 kb but when i try to insert this data it gives me error stating that Text data type cannot be of length more than 128...........?
What am i doing wrong........if Text is not the proper datatype to store such a data then can anyone suggest some thing better...............
Thanks in advance
Austin
Dear All,
the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.
the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.
i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero
i hope someone help me coz i need to handle it soon.
best wishes
Maylo
I am using the following approach to enable dynamic date calculations: [URL] ...
However, some measures I work with are percentages (not numeric values) and demand a different formula.
See example snippet bellow (first IIF statement)
/*Calendar*/
[Date Calculation].[Calculation].[Calendar Actual versus PY WK %] =
IIF
(
[measures].Name = "HSP2 %", -- < ThIs is where I am trying to check if the measure is a percetgae type
0, -- < When this is the case then do something different
[Code] ....
I am trying to access the metadata. I am aware of $system.mdschema_measures but I am unable to utulize this for my case.
Hi All,
Is there a way to check the data type and the maximum length of a field?
Hi,
I created a function class in "Script Components" to convert an Integer to a Date data type. It works good except that it converts NULL value to the date 1988-12-31; but I want to return NULL when the value is NULL. Any idea on this?
Here is my function:
Public Class ScriptMain
Inherits UserComponent
Private Function ConvertToDate(ByVal dateIn As Double) As Date
Dim DateStr as String
...........
...........
If DateStr Is Nothing Then
Return Nothing
Else
Return CDate(DateStr)
End If
End Function
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.ReportDte = ConvertToDate(Row.RPRTDT)
End Sub
End Class
hi,
when I use the expression
1==1? 5:12
in the derived column expression it works fine, but when I try to make the "else return"=null I get a parsing error
I am using the syntax that Jamie posted but I don't know how to write the value for the null part and what should (for example) in the <data-type> place
thanks,
Aref
so i have a table with 25million rows. this table has an image data type column and i want to set this column to null. what is the most efficient way to archive my goal. SQL SERVER 2008
View 3 Replies View RelatedWhat is the syntax to pass in null value for boolean data type in VB in the stored procedure?
This is what I tried and it doesn't work.
.Parameters.Append .CreateParameter("@disposition", adBoolean, adParamInput, 1, vbNull)
Thank you.
Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
Is it possible through SSRS designer?
Hello Gurus
I am using sql 2005 and one job status is executing in job monitor in 2005,How can i check since how long this job is running?
Please advice
Nitin
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies View RelatedHi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi
I have two tables. one MasterDetail and second is countrydetail
In master detail i have MasterDetailId (Primary) and countryId.
In CountryDetail table I have Countrid (Primary),CountryName.
I don't want to give relationship because i can insert null value in countryid in MasterDetail table.
So i have wriiten query like this
-> select c.CountryName,m.MasterDetailId from MasterDetail m,CountryDetail c where c.CountryId=m.CountryId and m.MasterDetailId= '2'
If In MasterDetail table if CountryId is null then it will not show me any record. So I want that record and its value with this query and checking null values. Help me about solving this query.
Thank You
I am trying to create a stored proc.
I have to check a parameter for null value and if null then set it to zero
Here is the stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UPDATENOA]
(
@Accepted char(10),
@NOAId bigint,
@Court char(30),
@RetainerType char(30),
@PendingInTaxCourt char(1)
)
AS
begin
UPDATE NOA set StageCode = Accepted,Court=@Court,RetainerType=@RetainerType,PendingInTaxCourt=@PendingInTaxCourt where NOAId = @NOAId;
Declare @Sequence int
Select @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAId;
if @Sequence IS NULL
{
@Sequence=0
}
Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)
values (@NOAId,@Accepted,@Sequence+1)
But this is giving me error. What is the correct way to do this.
Thanks
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedHow can I check on NULL values in a Select statement?
SELECT ID FROM TabelWHERE somecolumn <> NULL??
SELECT whatever_field, CASE LEN(DrAccount) WHEN 12 THEN DrAccount ELSE CASE (Note1) WHEN NULL THEN Location + DrAccount ELSE Note1 + DrAccount END END AS Account FROM Table1 The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount. The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount. The problem seems to reside in validating the null value in WHEN NULL How to check for null in CASE(fieldname) WHEN ???
View 1 Replies View Relatedthe SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.
"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"
Any ideas how I could implement this more robustly?
cheers
M
Content table
deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null
I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update
deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null
I started off as below, But I am getting incorrect values.
Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime
[code]....
hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:
declare @number int
set @number = (select number from numbers where number_id = 1)
if (@number = null)
begin
set @number = 12
end
Thank you very much
Okay, I have the following statement for my store procedure:
Code Snippet
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook
I wanted to put an ELSE IF statement right before the ELSE statement to check if the value is null. Something like this:
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE IF (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) != null
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook
Or if this is possible, it's much better:
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) AND (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) != null)
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook
I don't quite get how to write the SQL statement that I wanted so any help is much appreciated.
Hi,
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);
this.mycourtsDataSet1.AcceptChanges();
this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);
This did not happen with VS2005/CE3.01.
I have tried changing all references to decimal (or money in CE3.5) without luck.
I'm beginning to think that string may be the way to go!!!!!!!
Can someone shed some light on my problem.
Thanks,
Later:
It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Regards
RH
I am using Visual Web Developer Express 2005 and SQL Server Express 2005.
I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine.
I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed.
How can I check for null values and skip a block of code within my Transact Sql trigger.
Thanks.....
I'm about ready to pull my hair out. I have a repeater control, and a date field. If the field is a valid date, I'll use it to calculate and display the person's age. Otherwise, I want to display "N/A". My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date. It makes sense for these records to leave the date Null. This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label) Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If If inmate.DATE_OF_BIRTH Is DBNull.Value Then If IsDate(inmate.DATE_OF_BIRTH) Then LblAge.Text = "Age: N/A" Else LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH) End If End If End IfEnd Sub How can I resolve this?Diane
View 7 Replies View Relateddeclare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.