Anyone know how I can null-out the returned values of the PrjMgr field? I have bolded the field in question.
Here is the code:
select AgencyId
,ControlNumber
,ISNULL(LLA_AUDITS.PrjMgr,Projects.PrjMgr) as PrjMgr,ISNULL(CASE LEN(datepart(mm, PeriodFinish)) WHEN 1 THEN '0' + CAST(datepart(mm, PeriodFinish) as varchar(2)) ELSE CAST(datepart(mm, PeriodFinish) as varchar(2)) END ,'')As month
,ISNULL(CAST(datepart(dd, PeriodFinish) as varchar), '') As day
,ISNULL(CAST(right(datepart(yy, PeriodFinish),2) as varchar),'') As year
,ISNULL(CAST(left(datepart(yy, PeriodFinish),2) as varchar),'') As century
,custName
from LLA_Audits Left Outer Join Projects
on controlnumber = prjcode Left Outer Join Customers on Agencyid = CustCode
where AgencyId is not null
and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null)
and left(controlnumber,2)<>'72'
and left(controlnumber,2) <>'13'
and (CPAFirmId IN ('0','')or CPAFirmId is null)
order by ControlNumber
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output. When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok. When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out. I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null).... {
// I'm here!! I thought the watch says this is null??? }
Sorry if this is obvious, but I can't work this one out!!
I've got a query on a particular table returning an odd result:
SELECT DISTINCT WorkStation FROM Invoice WHERE WorkStation Is Not Null ORDER BY WorkStation
This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.
SELECT DISTINCT AccountNumber FROM Suppliers WHERE AccountNumber Is Not Null ORDER BY AccountNumber
I can't reproduce these results on another site on a table of the same structure, or on another table at this site.
Any suggestions as to what might be going on?
Pertinent info: --- select @@Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) --- dbcc checkdb Abridged result: CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'. --- SELECT * INTO #Inv FROM Invoice
SELECT DISTINCT WorkStation FROM #Inv WHERE WorkStation Is Not Null ORDER BY WorkStation
Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?
Hi Folx, I am new to SQL Server. I am rolling summed values from table profile to two columns in table txn. When the value for one (not necessarily both...) column is null, can that value be translated to zero? Or must I write a procedure or use a cursor? Versions: Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
Microsoft SQL Server Management Studio 9.00.1399.00
For example, when version_count for a particular transaction_id is null in the profile table but profile_count has summable integers, how do I update the version_count to zero in the same pass that I update the profile_count?
update txn set version_count = ( select sum(a.version_count) from profile a where a.transaction_id=txn.transaction_id and a.extraction_date=txn.extraction_date and txn.version_count is null ), profile_count = ( select count(*) from profile a where a.transaction_id=txn.transaction_id and a.extraction_date=txn.extraction_date and txn.profile_count is null )
This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:
We are in the process of converting Access database to MSSQL with web form front ends.
I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.
I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.
Here is a sample of the data:
Code: SQL> select * from test;
COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is not this is test too is test too is too is too
7 rows selected.
Now, if I have this SQL run, it will return only rows that have no nulls in any columns:
Code: select col1, col2, col3, col4from test where col1 like'th%' and col2 like '%' and col3 like '%' and col4 like '%';
COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is test too
Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:
Code: select col1, col2, col3, col4from test where (col1 like'th%' OR col1 is null) and (col2 like '%' OR col2 is null) and (col3 like '%' OR col3 is null) and (col4 like '%' OR col4 is null); COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is not this is test too is test too is too is too
The idea is to only select the first 4 rows in the above example.
I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.
Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer. Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16). Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned. If someone is able to help me figure this out, I would appreciate it. 1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList); 13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
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.
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim Line 90: sContact.Email = sEmail.Text.Trim Line 91: sContact.Save() Line 92: Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3)) RETURNS nvarchar(1000) WITH RETURNS NULL ON NULL INPUT AS EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case when col>'20150901' then 'NULL' else col end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
I have two columns A (which allows nulls) and B( which does not allow nulls). How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.
The result of A+B concatanation will be stored in a column, C.
I have 595 default constraints in my database. I can return a list of them using the following:
select * from sys.default_constraints
Is there a way I can return a list of just the ones where NULL is still allowed? I want to update all of the columns with a default value to not allow NULLs.
I've NEVER EVER had this issue, I have always done this and it works great both locally and remotely.
I have a table, which is a "customers" table.
I have a stored procedure which takes in parameters (name, address, password etc...) and returns me back, via a parameter declared as output - the customer ID.
I've always done this, and works great.
Now, this works fine locally.
IF I try to run this/do the exact same execution of commands (create a customer) remotely (where the database is stored, so copying everything from local to "over there") it does not work.
I get a DBNULL value back from the parameter, declared as an int output.
IF I copy and paste this Stored procedure Query/command into Query Analyzer and execute it (on the remote connection) and run it, giving it the exact same values as I do, it works perfect and returns me the correct value.
I've even dropped and recreated the stored procedure but makes no difference.
Any ideas why?
here is the proc....
CREATE PROCEDURE [dbo].[DoCreateNewCustomer] ( @theFirstName nvarchar(25), @theLastName nvarchar(25), @theAddress nvarchar(50), @theCity nvarchar(15), @thePostCode nvarchar(9), @thePhoneNumber nvarchar(21), @theMobilePhoneNumber nvarchar(21), @theIPAddress nvarchar(25), @thePassword nvarchar(50), @theEmailAddress nvarchar(30), @result int output ) AS SET @result = (SELECT [ID] FROM Customers WHERE emailAddress = @theEmailAddress) IF @result IS NOT NULL SET @result = -1 ELSE BEGIN SET @result = @@IDENTITY INSERT INTO Customers (
Firstname, Lastname, Address, City, Postcode, PhoneNumber, MobileNumber, IPAddress, [Password], EmailAddress
Any ideas why it does not work when calling the stored proc from either a web app, or through QA remotely but works fine when I run that command in QA or locally?
Again, if I do:
EXEC DoCreateNewCustomer { params } - the output result it gives me is DBNULL (if on remote connection)
If I do this locally, perfect
If I copy and paste pretty much the SP into QA and execute it on the remote connection, works great, and also locally.
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner] @BannerPage nvarchar(50), @MagazineID intASBEGIN SET NOCOUNT ON; DECLARE @BannerID int SELECT TOP 1 @BannerID = BannerID FROM Banners WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID AND StartDate <= GetDate() AND EndDate >=GetDate() ORDER BY Views SELECT BannerID, BannerFileName, AltText, URL, Views FROM Banners WHERE BannerID = @BannerID UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; Response.Write(RecordCount); } This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc. Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell me what I need to change to get the proper value? Thanks
I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%' UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks
I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query: SELECTEvents.*, Agencies.A_NAME AS A_NAME, (Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME, (Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME FROM Events INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID WHEREE_ID = 89
Even if all the inner join id's are zero, I would at least like to get back the event information. Could anyone help me out here, or do I have to do 4 queries?
The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?
---------------------------------------------------------------------------------------------------- CREATE PROCEDURE spUnitsbyUnitID @unitid int AS
INNER JOIN tbl_radios as B ON A.unitid = B.unitid INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid INNER JOIN tbl_contacts as D on A.unitid = D.unitid INNER JOIN tbl_cameras as E on A.unitid = E.unitid
WHERE A.UnitID = @unitID GO -----------------------------------------------------------------------------------------------------
can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
update purchaseorders set orderstatusID = 4 where purchaseorderid in ( select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null )
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
