I am using a SQLDataSource with the following Select query. If the "spouse" values are not in the database, I get the HTML non-breaking-space character back, rather than an empty string.
SelectCommand="select applicant_id, (applicant_last_name + ', ' + applicant_first_name) as applicant_name, CONVERT(varchar(10), applicant_dob, 101) as applicant_dob, applicant_ssn, (spouse_last + ', ' + spouse_first) as spouse_name, CONVERT(varchar(10), spouse_dob, 101) as spouse_dob, spouse_ssn from applicant where applicant_last_name like '%'+@last_name+'%' order by applicant_last_name"
I have had at least 2 occurences of a DFT OLEDB source returning the correct number of rows but all rows are empty (they contain zeros or ''). This has happened in two different places in different SSIS packages within an ETL task. In one case the source was on a different server running SQLServer and in the other it was a different database on the same server as the SSIS package. This occured running on different servers, one with SQL 2005 SP2 and the other without. Both are 64 bit AMD systems running 64 bit SQL 2005. As there is a derived column transformation that has performed the derivation on the blank columns I assume the problem is with the OLEDB source but it could be with the data stream from the OLEDB source to to whatever follows. Has anyone else had this problem and does anyone know of a fix?
I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
hi,I am using sql server database. In my table i am having three columns namely Name, age and salary. I am having three textboxes in my webform to retrieve data from the database. The column salary has no value in the database. When i retrieve the values in to textbox controls, i am getting in my textbox for the salary field. How to overcome this.? pls help
I have a SQL Server table with nvarchar type column which has not null constraint. I am inserting empty string ("") from Java to the table column. When I export this table into .csv file using bcp tool, empty string gets written as NUL character. Is there any way I can bcp out empty string as empty string itself instead of NUL to the file?
Hi ng.I have a varchar field in my table, called Name.I wanna do a selection, which is ordered by whether this field is empty ornot.E.g. something like:SELECTUserIDORDER BYName <> '';- - -How can I accomplish this?TIA.Klaus.
For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operators. The expression "...see above..." has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.
The expression works if I replace the NULL(DT_STR, 0, 1252) with say "A" and the expression works on other non-string columns. (As in "NULL(DT_I1) : (DT_I1)100")
I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.
Hi: Trying to insert null value into sql table, but not working, if I use: if (strMyText.Length == 0) command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else command.Parameters.AddWithValue("@Text", strMyText); When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL Where is the problem? Thanks a lot. Jt
Hi all, I have some columns in my database which allows null. I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)
I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.
I developed an SSIS package and I'm using the dtexec utility to run it. The package has a variable RunDate (datetime) and i want to set this value to "" (empty string) when i try to run this package using the dtexec.
Currently I'm getting an error Argument option not valid. Thanks in advance
Can we have blank value in Primary key field? Two fields are chosen as Primary Key for this table but the issue is at any given time one value will be blank when other is populated. Does sql server still order the records based on these keys? (Clustered index). Or should I just go about adding ID (identity)?
I'm doing a bcp out of a table to a file. Some of the fields in arecord may have an empty string.When I bcp out to the file and examine it, the fields that have anempty string in the database now show up in the file as having oneblank character.Why is bcp doing this? I don't want the blank character in my output.Thanks,Eric
Hello, I have a problem where I need to include data from a table where one of the joined columns can contain empty strings. The emptry string column and its related columns need to be included in the results of the query.
The Join looks like this:
SalesData.dbo.tbl_CYProcessedSales ps INNER JOIN SalesData.dbo.vw_Product_CYProcessedSalesXref xr ON ps.Prod = xr.Prod AND ps.Acct = xr.AcctCode INNER JOIN TxnRptg.dbo.vw_NetNewRevenueUnion nn ON xr.BillingType = nn.BillingType AND xr.ProdGroup = nn.Product AND xr.AcctCode = nn.AcctCode
The problem occurs on the xr.BillingType = nn.BillingType portion of the join. the nn.BillingType can contain empty strings. I've tried a LEFT join to vw_NetNewRevenueUnion, but this has not worked.
In vw_NetNewRevenueUnion, BillingType becomes an empty string within a CASE statement if BillingType is NULL. I've thought about using something like 'All' in the ELSE condition, and doing the same in the vw_Product_CYProcessedSales view just so I don't have to deal with an empty string.
If I were not to do that, is there some way I can handle the empty string issue within the join so that empty string records will be included in the query results?
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
Hi all,I have this code that I use for my Search function:SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM t_musicWHERE (@MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @MUSIC_TITLE + '%') AND (@MUSIC_ARTIST IS NULL OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @MUSIC_ARTIST + '%')) But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.) I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet. Thank you all,Kenny.
I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.
Has anyone find solution for this problem. i also checked http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=298056&SiteID=1 and http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx
Suppose have a Dimension table
DimColor ---------------------------- ColorKeyPK(smallint) ColorAlternateKey(nvarchar(30)) -1 UnknownMember 1 2 Blue 3 Red 4 Black
Color with the ID 1 is empty string
FactOrders --------------------------- OrderID Date Color Quantity
OrderID = 1 Color = 'Black' Quantity = 10 OrderID = 2 Color = 'Red' Quantity = 20 OrderID = 3 Color = '' Quantity = 10 OrderID = 4 Color = 'Blue' Quantity = 5 OrderID = 5 Color = Black Quantity = 10
When i use the Lookup transform it cannot find the ColorKeyPK
The result of the Lookup transform is. ------------------------------ OrderID = 1 Color='Black' ColorKey=4 OrderID = 2 Color='Black' ColorKey=3 OrderID = 3 Color='Black' ColorKey=NULL ----> This is the problem Lookup cannot find empty string. It should be 1. OrderID = 4 Color='Black' ColorKey=2 OrderID = 5 Color='Black' ColorKey=4
This seems to be a rather old problem (http://www.themssforum.com/SQLServer/Does-empty/) but I couldn't find an answer yet.
The problem is: I have two tables t1 and t2 where t1 is a staging area of t2. t1: (id int not null, phone varchar(30)) t2: (id int not null, phone varchar(30))
Data in t1: (1, '') <- empty string Data in t2: (1, ' ') <- a blank
Comparing t1.phone with t2.phone results in equality which in my opinion isn't correct.
The question ist: How can I change the behaviour of SQL-Server to result in inequality so that the change in my staging table is detected correctly?
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script: Select * from StringTable WHERE (LongString = '' OR LongString IS NULL)
My script: The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END Else Begin
-- update nvarchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END Else Begin
-- update nvarchar ShortString UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
where @serilializedReturnCode is locally declared as varchar(250)
This is the error I get:
Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.
I need to write a query that tells me which string values are empty orblank in a table.Is it possible to return the length of the string contained in characterfield?
